只要是和数据打交道的人，就避免不了接触 Excel。对于大量的重复性工作，靠人工往往会耗费大量的时间。借助 Python 编程，可以批量自动化完成这些操作，起到事半功倍的效果。我们知道 Pandas 模块它能够方便快捷地处理 Excel 数据，但是对于 Excel 格式的设置，借助 `openpyxl` 模块是一个很好的选择，这也是本文的重点内容。实战项目：Excel 报表自动化
假如你是公司里的一位数据分析师，老板发给你公司近 10 年各分区的销售额情况（如图 4-46 所示），需要你汇总出公司 2011 年至 2020 年这 10 年的总销售情况，并绘制出一条折线图，用于老板做报告。此时，你应该怎么做呢？1 导入相关模块
首先，导入本案例需要用到的所有 Python 模块。import osimport pandas as pdfrom openpyxl import Workbookfrom openpyxl.chart import LineChart, Referencefrom openpyxl.utils.dataframe import dataframe_to_rows
2 获取文件列表
在案例中有该公司 2011 年至 2020 年近 10 年的数据，一共 10 个 Excel 文件。我们需要获取文件列表，便于做后续的处理。file_list = os.listdir("./项目案例原始数据/")file_list = [i for i in file_list if i.endswith(".xlsx")]file_list# 输出：['2010.xlsx', '2011.xlsx', '2012.xlsx', '2013.xlsx', '2014.xlsx', '2015.xlsx', '2016.xlsx', '2017.xlsx', '2018.xlsx', '2019.xlsx', '2020.xlsx']
在 1 处，调用 os 模块中的 listdir() 方法，可以打印出当前工作目录下的所有文件。然后，利用列表解析式筛选出 .xlsx 结尾的文件，得到我们需要的数据文件列表 file_list（见 2）。小贴士
"./项目案例原始数据/" 这种写法表示这里用到的项目数据，存放在当前工作目录下的 "项目案例原始数据" 文件夹中。
3 计算每一年的总销售额
在获取文件列表后，我们需要依次读取每个文件，统计汇总出每一年的总销售额，并将其存储到 DataFrame 数据框中。x = []for index, value in enumerate(file_list): y = [] df = pd.read_excel(f"./项目案例原始数据/{value}") total = df["销售额（万元）"].sum() y.append(value[:4]) y.append(total) x.append(y)x# 输出：['2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019', '2020']y# 输出：[23722, 22434, 22943, 24310, 21576, 23755, 21980, 23200, 22554, 22019, 22436]df = pd.DataFrame(x, columns=["年份", "总销售额"])df# 输出：# 年份 总销售额# 0 2010 23722# 1 2011 22434# 2 2012 22943# 3 2013 24310# 4 2014 21576# 5 2015 23755# 6 2016 21980# 7 2017 23200# 8 2018 22554# 9 2019 22019# 10 2020 22436
在 1 处，我们利用 for 循环去遍历数据文件列表 file_list。每循环一次，就用 Pandas 模块读取文件中的数据（见 2），并计算出当年的总销售额（见 3）。这段代码还定义了两个空列表 x 和 y，用于帮助我们组织数据，可以看出最终的列表 x 是一个列表嵌套（见 4）。最后，调用 Pandas 模块的 DataFrame() 方法，即可将列表 x 转换为一个 DataFrame 数据框（见 5）。4 将 DataFrame 对象转换为工作簿对象
前面我们已经统计出了每一年的总销售额，并将其存储在数据框 df 中。由于后续需要利用 openpyxl 模块进行折线图绘制，因此，这里必须将 Pandas 中的 DataFrame 数据框对象，转换为 openpyxl 中的工作簿对象。在交互式环境中输入如下命令：wb = Workbook()ws = wb.activefor row in dataframe_to_rows(df, index=False, header=True): ws.append(row)
首先，我们新建了一个新的工作簿，用于存储对象转换后的数据（见 1）。直接调用 openpyxl.utils.dataframe 模块中的 dataframe_to_rows() 方法，将数据框对象转换为工作簿对象（见 2）。此时，这个工作簿对象 wb 就拥有了数据框 df 中的所有数据。5 绘制折线图
当我们获取了用于绘图的数据源 ws 后，绘图就变得很简单了。在交互式环境中输入如下命令：ws = wb.activechart = LineChart()max_row = len(file_list) + 1data = Reference(ws, min_row=1, max_row=max_row, min_col=2, max_col=2)chart.add_data(data, titles_from_data=True)chart.title = "某公司2011-2020年销售额折线图"chart.y_axis.title = "销售额"chart.x_axis.title = "年份"ws.add_chart(chart, "D1")wb.save("2011至2020_销售折线图.xlsx")
自动化excel的完整版文章pdf以及示例的excel源文件和jupyter源文件，我打包上传到网盘，可以私信回复“自动化excel”获取下载地址
