写点什么

软件测试|Python 操作 Excel 制作报表,不要太方便

  • 2023-05-24
    北京
  • 本文字数:2505 字

    阅读完需:约 8 分钟

获取更多技术知识


前言

今天我们介绍的是 Python 操作 Excel 制作报表,我们需要用到的库是 openpyxl,我们主要使用的功能有下列几个功能


  • 插入与查询数据

  • 分类数据统计

  • 数据可视化

  • 字体颜色修改

基本操作

表格初始数据如下图所示:



我们先熟悉一下 openpyxl 库,方便我们后续更高级的用法


  1. 安装环境我们需要安装 openpyxl 库,安装也很简单,一条命令解决问题,在 pycharm 中安装也可以,具体方法和安装其他库一致,这里不做赘述


pip install openpyxl
复制代码


  1. 导入 Excel 数据集,代码如下


# 导入模块from openpyxl import Workbook, load_workbook# 导入Excel数据集wb = load_workbook(r"sales.xlsx")# 得到正在运行的工作表sheet = wb.active# 工作表的名称叫做print(sheet)
-----------# 输出 <Worksheet "auto_sales">
复制代码


  1. 打印出工作表中的值我们通过传入单元格的位置来打印其中的数值,代码如下


print(sheet["A1"].value)print(sheet["A2"].value)print(sheet["B3"].value)----------------# 输出BrandVwJapan
复制代码


  1. 修改单元格的值


sheet["C10"] =75 # 保存wb.save(r"sales.xlsx")
复制代码


在保存过之后,我们来看一下是否成功修改了单元格的值,如下图所示:



我们看到单元格的值被成功修改了。


  1. 添加一个工作表我们可以在现有的 Excel 文件中添加一个 sheet,代码如下:


# 添加一个新的工作表wb.create_sheet("new_energy_cars_sales")# 返回以列表形式带有工作表名称print(wb.sheetnames)
# 输出['auto_sales', 'new_energy_cars_sales']
复制代码


注:此处我们没有保存,所以文件中不会出现新的 sheet


  1. 插入数据我们通过代码新建一个 Excel 文件,并在文件中写入部分数据


from openpyxl import Workbook, load_workbook
new_wb = Workbook()ws = new_wb.active# 重命名工作表的名称ws.title = "contry"# 插入数据到新建的工作表中ws.append(["PRC","is","a","great","contry"])new_wb.save("contry.xlsx")
复制代码


我们来看一下最后出来的结果,如下图所示:



我们尝试来多插入几条数据,代码如下:


# 插入更多的数据ws.append(["USA","Is","a","dirty", "contry"])ws.append(["UK","is","a","deep", "contry"])ws.append(["End"])# 保存new_wb.save("contry.xlsx")
复制代码


结果如下:



  1. 插入行与删除行插入某一行的话,调用的则是 insert_rows()方法,具体代码如下:


# 前面的步骤一样,导入工作簿和数据ws.insert_rows(1)new_wb.save("contry.xlsx")
复制代码


出来的结果如下图所示



同理,如果是想要去删除某一行的数据的话,调用的则是 delete_rows()方法,具体代码如下:


ws.delete_rows(1)# 保存new_wb.save("contry.xlsx")
复制代码


查看结果,可以看到插入的行已经被删除



  1. 插入列与删除列插入列删除列的操作与插入行删除行的操作类似,插入列用到的方式是 insert_cols(),代码如下


# 新插入一列ws.insert_cols(6)
复制代码


删除列的方法是 delete_cols()


ws.delete_cols(6)
复制代码

数据分析与可视化

openpyxl 是一个非常强大的第三方库,结合 Python,Excel 能实现的操作,python+openpyxl 都可以实现,现在我们就来使用一下稍微高端一些的功能,实现数据的分析和可视化。


1.数据准备因为手头没有实际的数据,所以现在只能自己随便生成一批数据,不严谨,请大家见谅。


数据如下:


brand_data = {    "大众":{"A级":1200000, "B级":500000, "C级":400000, "D级":30000, "SUV":480000},    "丰田":{"A级":900000, "B级":450000, "C级":240000, "D级":2160, "SUV":300000},    "本田":{"A级":600000, "B级":240000, "C级":160, "D级":0, "SUV":240000},    "比亚迪":{"A级":1080000, "B级":280000, "C级":0, "D级":0, "SUV":300000},    "奔驰":{"A级":0, "B级":180000, "C级": 216000, "D级":60000, "SUV": 120000 }}
复制代码


将数据写入 Excel,代码如下:


# 创建一个新的工作簿sales_wb = Workbook()ws = sales_wb.active# 重命名工作表的名称ws.title = "Sales"# 创建列名column_names = ["Brand Name"] + list(brand_data["大众"].keys())ws.append(column_names)# 将一系列的数值都放置到工作表当中去for product in brand_data:    sales = list(brand_data[product].values())    ws.append([product] + sales)sales_wb.save("brand_data.xlsx")
复制代码


表格如图所示:



  1. 平均值的计算实现我们来指定某一列,例如我们要计算各品牌 B 级车的平均销量,代码如下:


ws['C7'] = '=AVERAGE(C2:C6)'sales_wb.save("brand_data.xlsx")
复制代码


结果如下图:



3. 求和的计算实现我们现在要计算各个品牌的各自的销量总和,该怎样实现,代码如下


min_row = ws.min_rowmax_row = ws.max_rowmin_col = ws.min_columnmax_col = ws.max_column
for row in range(min_row+1,max_row+1): key=ws.cell(row=row,column=max_col+1).coordinate #求和的开始单元格地址 start = ws.cell(row=row,column=min_col+1).coordinate #求和的结束单元格地址 end = ws.cell(row=row,column=max_col-1).coordinate ws[key]=f'=SUM({start}:{end})'
sales_wb.save('brand_data.xlsx')
复制代码


结果如下图:


绘制柱状图

来绘制一张柱状图,来看一下不同的品牌不同级别的汽车的销售数据如何,横坐标对应的品牌,而纵坐标对应的则是销量,另外我们根据不同的级别会用不同的颜色来标注出来,代码如下:


from openpyxl.chart import BarChart, Reference# 新建一个柱状图实例barchart = BarChart()# 确定数据的范围data = Reference(ws, min_col=ws.min_column+1, max_col=ws.max_column, min_row=ws.min_row, max_row=ws.max_row)categories = Reference(ws, min_col=ws.min_column, max_col=ws.min_column, min_row=ws.min_row+1, max_row=ws.max_row)# 添加数据以及类目barchart.add_data(data, titles_from_data=True)barchart.set_categories(categories)# 绘制的数据放在哪个位置ws.add_chart(barchart, "A10")# 添加标题barchart.title = '每个品牌的分级别产品销售数据'# 图表的类型barchart.style = sales_wb.save("brand_data.xlsx")
复制代码


结果如图所示:


总结

本文只是对 openpyxl 强大功能的部分演示,如果大家想学习其他技巧,欢迎评论区留言!


搜索微信公众号: 霍格沃兹测试学院,学习更多测试开发前沿技术

用户头像

社区:ceshiren.com 微信:ceshiren2021 2019-10-23 加入

微信公众号:霍格沃兹测试开发 提供性能测试、自动化测试、测试开发等资料,实时更新一线互联网大厂测试岗位内推需求,共享测试行业动态及资讯,更可零距离接触众多业内大佬。

评论

发布
暂无评论
软件测试|Python操作Excel制作报表,不要太方便_霍格沃兹测试开发学社_InfoQ写作社区