13: openpyxl 读写 xlsx文件
1.1 openpyxl 基本使用
1、openpyxl 将xlsx读成json格式
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#! /usr/bin/env python # -*- coding: utf-8 -*- # -*- coding: utf-8 -*- import json from openpyxl import load_workbook def read_xlsx_to_json(file_home): wb = load_workbook(filename= file_home) sheet_ranges = wb['Sheet1'] ws = wb['Sheet1'] rows = ws.rows #获取表格所有行和列,两者都是可迭代的 thead = [col.value for col in rows.next()] # 表格中第一行作为key data = [] for row in rows: line = [col.value for col in row] tmp_dic = {} for index, val in enumerate(thead): tmp_dic[val] = line[index] data.append(tmp_dic) return json.dumps(data, ensure_ascii=False,indent=4) # print read_xlsx_to_json('dd.xlsx') ''' [ { "员工编号": null, "部门名称": "HLT集团", "手机号": 1393999934, "角色": null, "直属上级": null, "职位": "CEO", "企业微信唯一标识": null, "相关部门": null, "姓名": "王五", "邮箱": "zhangsan@qq.com", "职能": null, "性别": "男" }, { "员工编号": null, "部门名称": "政府事业部", "手机号": 61616116161616, "角色": null, "直属上级": "王五", "职位": "首席运营官", "企业微信唯一标识": null, "相关部门": null, "姓名": "李四", "邮箱": "lisi@qq.com", "职能": null, "性别": "女" } ] '''
2、openpyxl 创建一个 xlsx 文件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#! /usr/bin/env python # -*- coding: utf-8 -*- import json from openpyxl import Workbook # 创建 一个 xlsx 文件 def create_xlsx(data_list): wb = Workbook() # 创建工作簿 ws = wb.active # 激活工作表 ws1 = wb.create_sheet("Mysheet") # 创建mysheet表 ws.title = "New Title" # 表明改为New Title ws.sheet_properties.tabColor = "1072BA" # 颜色 for row_index, row in enumerate(data_list): thead_list = data_list[0] # ['id', 'name', 'sex', 'age'] row_index += 1 # excel中 行是从 1 开始计算的 for column_index, thead in enumerate(thead_list): column_index += 1 # excel中 行是从 1 开始计算的 if row_index == 1: #第一步: 填充第一行数据,表头(id name sex age) d = ws.cell(row=1, column=column_index, value=thead) else: #第二步: 新建表中数据 d = ws.cell(row=row_index, column=column_index, value=row[column_index-1]) wb.save('test.xlsx')#保存 data_list = [ ['id','name','sex','age'], ['1','张三','男','18'], ['2','李四','男','19'], ['3','王五','女','20'], ] create_xlsx(data_list)
3、openpyxl 修改 第十列的值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#! /usr/bin/env python # -*- coding: utf-8 -*- import json from openpyxl import load_workbook # 将表中第十行的值统一修改为 ‘new val’ def change_xlsx(file_home): wb = load_workbook(filename= file_home) sheet_ranges = wb['Sheet1'] ws = wb['Sheet1'] rows = ws.rows #获取表格所有行和列,两者都是可迭代的 for row, line in enumerate(rows): if row != 0: row += 1 ws.cell(row=row, column=10).value = 'new val' wb.save('f5.xlsx') change_xlsx('dd.xlsx')
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#! /usr/bin/env python # -*- coding: utf-8 -*- from openpyxl import load_workbook file_home = 'f5.xlsx' wb = load_workbook(filename= file_home) sheet_ranges = wb['Sheet1'] ws = wb['Sheet1'] # 将第二行,第二列的值修改成 '修改后的值' ws.cell(row=2, column=2).value = '修改后的值' wb.save('f6.xlsx')
4、在xlsx文件后面添加新的内容(插入时按照数据顺序)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
#! /usr/bin/env python # -*- coding: utf-8 -*- import json, sys from openpyxl import load_workbook # 在xlsx文件最后添加新的数据,会按照顺序插入到xlsx文件对应位置 def append_xlsx(file_home, data_list): ''' :param file_home: xlsx文件路径 :param data_list: 插入的数据 ''' wb = load_workbook(filename= file_home) sheet_ranges = wb['Sheet1'] ws = wb['Sheet1'] print data_list ws.append(data_list) wb.save('f7.xlsx') data_list = ['邮箱', '', '手机号', '性别'] append_xlsx('dd.xlsx',data_list)
作者:学无止境
出处:https://www.cnblogs.com/xiaonq
生活不只是眼前的苟且,还有诗和远方。