提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  Python

当前栏目

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

Python Excel 数据 代码
2023-03-07 09:07:09 时间

前言

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

  • 插入与查询数据
  • 分类数据统计
  • 数据可视化
  • 字体颜色修改

基本操作

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

在这里插入图片描述

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

  1. 安装环境 我们需要安装openpyxl库，安装也很简单，一条命令解决问题，在pycharm中安装也可以，具体方法和安装其他库一致，这里不做赘述pip install openpyxl
  2. 导入Excel数据集，代码如下# 导入模块 from openpyxl import Workbook, load_workbook # 导入Excel数据集 wb = load_workbook(r"sales.xlsx") # 得到正在运行的工作表 sheet = wb.active # 工作表的名称叫做 print(sheet) ----------- # 输出 <Worksheet "auto_sales">
  3. 打印出工作表中的值 我们通过传入单元格的位置来打印其中的数值，代码如下print(sheet["A1"].value) print(sheet["A2"].value) print(sheet["B3"].value) ---------------- # 输出 Brand Vw Japansheet["C10"] =75 # 保存 wb.save(r"sales.xlsx")在保存过之后，我们来看一下是否成功修改了单元格的值，如下图所示：
  4. 修改单元格的值
在这里插入图片描述

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

  1. 添加一个工作表 我们可以在现有的Excel文件中添加一个sheet，代码如下：# 添加一个新的工作表 wb.create_sheet("new_energy_cars_sales") # 返回以列表形式带有工作表名称 print(wb.sheetnames) # 输出 ['auto_sales', 'new_energy_cars_sales']注：此处我们没有保存，所以文件中不会出现新的sheet
  2. 插入数据 我们通过代码新建一个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")结果如下图:
    在这里插入图片描述
    实现 我们现在要计算各个品牌的各自的销量总和，该怎样实现，代码如下
min_row = ws.min_row
max_row = ws.max_row
min_col = ws.min_column
max_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强大功能的部分演示，如果大家想学习其他技巧，欢迎评论区留言！

相关文章