1.1 openpyxl 基本使用
1、openpyxl 将xlsx读成json格式
#! /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,
"性别": "女"
}
]
'''
openpyxl 将xlsx读成json格式
2、openpyxl 创建一个 xlsx 文件
#! /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'],
['','张三','男',''],
['','李四','男',''],
['','王五','女',''],
]
create_xlsx(data_list)
openpyxl 创建一个 xlsx 文件
3、openpyxl 修改 第十列的值
#! /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')
openpyxl修改第四列的值
#! /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')
openpyxl修改基本用法
4、在xlsx文件后面添加新的内容（插入时按照数据顺序）
#! /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)
在xlsx文件后面添加新的内容