python 对Excel操作常用的主要有xlwt、xlrd、openpyxl ,前者xlwt主要适合于对后缀为xls比较进行写入,而openpyxl主要是针对于Excel 2007 以上版本进行操作,也就是对后缀为xlsx进行操作。
Excel 主要有三大元素,工作簿,Sheet 页,单元格,一个工作簿可以包含多个Sheet页面,而Sheet页由N多个单元格组成,而单元格主要用来存储数据;
一、安装插件
pip install openpyxl
二、创建Excel文件
操作excel之前,首先我们需要创建一个excel ,创建的途径很多,我们主要通过openpyxl的方式创建;
# 首先需要我们导入Workbook包
from openpyxl import Workbook
excel = Workbook() # 创建excel对象
excel.save("sample.xlsx") # 保存excel
创建方式很简单,其实就是把没有的东西保存在指定的位置即可;
二、创建Sheet页
1.excel默认创建成功以后会自动创建一个Sheet页面
from openpyxl import Workbook
excel = Workbook() # 创建excel对象
excel.create_sheet(title="Sheet1", index=1) # 创建sheet页面,默认index 的值为1,但是index是从0开始
excel.save("sample.xlsx") # 保存excel
注意:
新建sheet 完成以后需要保存,如果不保存是无法创建成功
三、新建excel时写入测试数据
from openpyxl import Workbook
# 写入操作之前首先我们需要知道在那个Sheet页写入
# 打开默认的Sheet页面
excel = Workbook()
sheet = excel.active # 激活第一个Sheet页
# excel写入数据的三种方式
# 指定单元格写入数据
ws["A1"] = "添加数据方式1" # 指定单元格进行添加数据
# 在当前的sheet页面中追加一行数据
ws.append([1, 2, 3, 4]) # 在当前sheet页面追加
# 指定行号,列号,写入的值
ws.cell(row=1, column=2, value="第一行第二列添加测试数据")
# 最重要的写入数据以后需要保存数据
wk.save("sample.xlsx")
四、操作excel
1.获取所有的Sheet页面,修改Sheet名称,删除Sheet页面
excel = load_workbook('sample.xlsx') # 打开文件
# 获取当前excel的所有sheet 页
print(excel.sheetnames)
# 获取指定的Sheet页面
sheet = excel['Sheet'] # 通过sheet_name获取,通过sheet名称获取表
# 获取当前sheet页的名称
print(sheet.title)
# 修改 Sheet 名称
sheet.title = "Sheet_new"
# 删除Sheet页
excel.remove_sheet(sheet) # 这个方法已经弃用,推荐使用remove 或者del excel["Sheet1"] # 指定删除的Sheet名称
excel.remove(sheet) # 先获取Sheet,然后在删除
excel.save("sample.xlsx") # 只要修改完成,必须保存excel
2.读取excel数据
# 首先读取excel数据的时候我们需要导入load_workbook
from openpyxl import load_workbook
# 打开文件
excel = load_workbook('bid.xlsx')
# 获取操作的sheet页面
sheet1 = excel['Sheet1'] # 通过sheet_name获取,通过sheet名称获取表
sheet2 = excel.active() # 打开默认的sheet页面
# 读取单元格的数据
data = table.cell(row=1, column=1).value # 指定行列获取单元格的数据,还可以通过循环的方式获取多个单元格的值
data = table["A1"].value # 指定单元格获取数据
dataw = table["A1":"C2"] # 获取多个单元格数据
# 修改单元格数据
data = table["A1"].value = "修改数据方式" # 重新指定单元格的数据
3.获取表的一些属性
wb = load_workbook("sample.xlsx")
ws = wb.active
# 通过返回最大的行数和列数,获取excel有多少行多少列
rows = ws.max_row # 获取行数
cols = ws.max_column # 获取列数
# 直接获取行数和列数,返回的结果是一个生成器,需要通过元组转换下
len(tuple(ws.columns)) # 获取列数
len(tuple(ws.rows)) # 获取行数
四、实际案例
工作中,需要测试数据需要支持多个数据源,但是我不想改后端的代码,所以考虑将excel数据处理为json数据,然后进行处理工作。
def excel2json(file_name):
wb = load_workbook(file_name)
ws = wb.active
result_list = []
head_list = []
for k in tuple(ws.rows)[0]: # excel的第一行数据设置为字典的key
head_list.append(k.value)
for rv in tuple(ws.rows)[1:]: # excel 其他的行为字典的value
value_list = []
for v in rv:
value_list.append(v.value)
result_list.append(dict(zip(head_list, value_list))) # 两个列表合并为字典
return result_list