本章给大家演示一下在实际工作中如何结合 Pandas 库和 openpyxl 库来自动化生成报表。假设我们现在有如图 1 所示的数据集。
（图1）
现在需要根据这份数据集来制作每天的日报情况，主要包含以下 3 个方面。
接下来分别实现。
我们先用 Pandas 库对数据进行计算处理，得到各指标的同/环比情况，具体实现代码如下。
#导入文件 import pandas as pd
df = pd.read_excel(r'D:Data-Scienceshareexcel-python 报表自动化
sale_data.xlsx') #构造同时获取不同指标的函数 def get_data(date): create_cnt = df[df['创建日期'] == date]['order_id'].count()
pay_cnt = df[df['付款日期'] == date]['order_id'].count()
receive_cnt = df[df['收货日期'] == date]['order_id'].count()
return_cnt = df[df['退款日期'] == date]['order_id'].count() return create_cnt,pay_cnt,receive_cnt,return_cnt #假设当日是 2021-04-11 #获取不同时间段的各指标值 df_view = pd.DataFrame([get_data('2021-04-11')
,get_data('2021-04-10')
,get_data('2021-04-04')]
,columns = ['创建订单量','付款订单量','收货订单量','退款订单量']
,index = ['当日','昨日','上周同期']).T
df_view['环比'] = df_view['当日'] / df_view['昨日'] - 1 df_view['同比'] = df_view['当日'] / df_view['上周同期'] - 1 df_view
运行上面代码会得到如图 2 所示结果。
（图2）
上面只是得到了各指标的同/环比绝对数值，但是日报在发出去之前一般都要做一些格式调整，比如调整字体。而格式调整需要用到 openpyxl 库，我们将 Pandas 库中DataFrame 格式的数据转化为适用 openpyxl 库的数据格式，具体实现代码如下。
from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows #创建空工作簿 wb = Workbook()
ws = wb.active #将 DataFrame 格式数据转化为 openpyxl 格式 for r in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r)
wb.save(r'D:Data-Scienceshareexcel-python 报表自动化核心指标_原始.xlsx')
运行上面代码会得到如图 3 所示结果，可以看到原始的数据文件看起来是很混乱的。
（图3）
接下来，对上面的原始数据文件进行格式调整，具体调整代码如下。
from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.styles import colors from openpyxl.styles import Font from openpyxl.styles import PatternFill from openpyxl.styles import Border, Side from openpyxl.styles import Alignment
wb = Workbook()
ws = wb.active for r in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r) #第 2 行是空的，删除第 2 行 ws.delete_rows(2) #给 A1 单元格进行赋值 ws['A1'] = '指标' #插入一行作为标题行 ws.insert_rows(1)
ws['A1'] = '电商业务方向 2021/4/11 日报' #将标题行的单元格进行合并 ws.merge_cells('A1:F1') #合并单元格 #对第 1 行至第 6 行的单元格进行格式设置 for row in ws[1:6]: for c in row: #字体设置 c.font = Font(name = '微软雅黑',size = 12) #对齐方式设置 c.alignment = Alignment(horizontal = "center") #边框线设置 c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
right = Side(border_style = "thin",color = "FF000000"),
top = Side(border_style = "thin",color = "FF000000"),
bottom = Side(border_style = "thin",color = "FF000000")) #对标题行和表头行进行特殊设置 for row in ws[1:2]: for c in row:
c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
c.fill = PatternFill(fill_type = 'solid',start_color ='FFFF6100') #将环比和同比设置成百分比格式 for col in ws["E":"F"]: for r in col:
r.number_format = '0.00%' #调整列宽 ws.column_dimensions['A'].width = 13 ws.column_dimensions['E'].width = 10 #保存调整后的文件 wb.save(r'D:Data-Scienceshareexcel-python 报表自动化核心指标.xlsx')
运行上面代码会得到如图 4 所示结果。
（图4）
可以看到各项均已设置成功。
我们同样先利用 Pandas 库处理得到当日各省份创建订单量的情况，具体实现代码如下。
df_province = pd.DataFrame(df[df['创建日期'] == '2021-04-11'].groupby('省份
')['order_id'].count())
df_province = df_province.reset_index()
df_province = df_province.sort_values(by = 'order_id',ascending = False)
df_province = df_province.rename(columns = {'order_id':'创建订单量'})
df_province
运行上面代码会得到如图 5 所示结果。
（图5）
在得到各省份当日创建订单量的绝对数值之后，同样对其进行格式设置，具体设置代码如下。
from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.styles import colors from openpyxl.styles import Font from openpyxl.styles import PatternFill from openpyxl.styles import Border, Side from openpyxl.styles import Alignment from openpyxl.formatting.rule import DataBarRule
wb = Workbook()
ws = wb.active for r in dataframe_to_rows(df_province,index = False,header = True):
ws.append(r) #对第 1 行至第 11 行的单元格进行设置 for row in ws[1:11]: for c in row: #字体设置 c.font = Font(name = '微软雅黑',size = 12) #对齐方式设置 c.alignment = Alignment(horizontal = "center") #边框线设置 c.border = Border(left = Side(border_style = "thin",color = "FF000000"),
right = Side(border_style = "thin",color = "FF000000"),
top = Side(border_style = "thin",color = "FF000000"),
bottom = Side(border_style = "thin",color = "FF000000")) #设置进度条条件格式 rule = DataBarRule(start_type = 'min',end_type = 'max',
color="FF638EC6", showValue=True, minLength=None, maxLength= None)
ws.conditional_formatting.add('B1:B11',rule) #对第 1 行标题行进行设置 for c in ws[1]:
c.font = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF")
c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100') #调整列宽 ws.column_dimensions['A'].width = 17 ws.column_dimensions['B'].width = 13 #保存调整后的文件 wb.save(r'D:Data-Scienceshareexcel-python 报表自动化各省份销量情况.xlsx')
运行上面代码会得到如图6所示结果。
（图6）
一般用折线图反映某个指标的趋势情况，我们前面也讲过，在实际工作中一般用matplotlib 库或者其他可视化库进行图表绘制，并将其保存，然后利用 openpyxl 库将图表插入 Excel 中。
先利用 matplotlib 库进行绘图，具体实现代码如下。
%matplotlib inline import matplotlib.pyplot as plt
plt.rcParams["font.sans-serif"]='SimHei'#解决中文乱码 #设置图表大小 plt.figure(figsize = (10,6))
df.groupby('创建日期')['order_id'].count().plot()
plt.title('4.2 - 4.11 创建订单量分日趋势')
plt.xlabel('日期')
plt.ylabel('订单量') #将图表保存到本地 plt.savefig(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量
分日趋势.png')
将保存到本地的图表插入 Excel 中，具体实现代码如下。
from openpyxl import Workbook from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
img = Image(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量
分日趋势.png')
ws.add_image(img, 'A1')
wb.save(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量分日
趋势.xlsx')
运行上面代码会得到如图 7 所示结果，可以看到图表已经被成功插入 Excel 中。
（图7）
上面我们是把每一部分都单独拆开来实现的，最后存储在了不同的 Excel 文件中。
当然，有时放在不同文件中会比较麻烦，就需要把这些结果合并在同一个 Excel 的相同 Sheet 或者不同 Sheet 中。
将不同的结果合并到同一个 Sheet 中
将不同的结果合并到同一个 Sheet 中的难点在于不同表结果的结构不一样，而且需要在不同结果之间进行留白。
首先，插入核心指标表 df_review，插入方式与单独插入是一样的，具体代码如下。
for r in dataframe_to_rows(df_view,index = True,header = True): ws.append(r)
然后，插入各省份情况表 df_province，因为 append()方法默认是从第 1 行开始插入的，而我们前面几行已经有 df_view 表的数据了，所以就不能用 append()方法插入，而只能通过遍历每一个单元格的方式。
那我们怎么知道要遍历哪些单元格呢？核心是需要知道遍历开始的行/列和遍历结束的行/列。
遍历开始的行 = df_view 表占据的行 + 留白的行(一般表与表之间留 2 行) + 1
遍历结束的行 = 遍历开始的行 + df_province 表占据的行
遍历开始的列 = 1
遍历结束的列 = df_province 表占据的列
又因为 DataFrame 中获取列名的方式和获取具体值的方式不太一样，所以我们需要分别插入，先插入列名，具体代码如下。
for j in range(df_province.shape[1]):
ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r]
df_province.shape[1]表示获取 df_province 表有多少列，df_view.shape[0]表示获取
df_view 表有多少行。
前面说过，遍历开始的行是表占据的行加上留白的行再加 1，一般留白的行是 2，
可是这里为什么是 df_view.shape[0] + 5 呢？因为 df_view.shape[0]是不包括列名行的，而且在插入 Excel 中时会默认增加 1 行空行，所以需要在留白行的基础上再增加 2 行，
即 2 + 2 + 1 = 5。
因为 range()函数默认是从 0 开始的，而 Excel 中的列是从 1 开始的，所以 column需要加 1。
上面的代码只是把 df_province 表的列名插入进来，接下来插入具体的值，方式与插入列名的方式一致，只不过需要在列名的下一行开始插入，具体代码如下。
for i in range(df_province.shape[0]): for j in range(df_province.shape[1]):
ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value =
df_province.iloc[i,j]
接下来，插入图片，插入图片的方式与前面的单独插入方法是一致的，具体代码如下。
#插入图片 img = Image(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量
分日趋势.png')
ws.add_image(img, 'G1')
将所有的数据插入以后就该对这些数据进行格式设置了，因为不同表的结构不一样，所以我们没法直接批量对所有单元格进行格式设置，只能按范围分别进行设置，而不同范围的格式可能是一样的，所以我们先预设一些格式变量，这样后面用到的时候直接调取这些变量即可，减少代码冗余，具体代码如下。
#格式预设 #表头字体设置 title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF") #普通内容字体设置 plain_Font_style = Font(name = '微软雅黑',size = 12) Alignment_style = Alignment(horizontal = "center") Border_style = Border(left = Side(border_style = "thin",color = "FF000000"), right = Side(border_style = "thin",color = "FF000000"), top = Side(border_style = "thin",color = "FF000000"), bottom = Side(border_style = "thin",color = "FF000000")) PatternFill_style = PatternFill(fill_type = 'solid',start_color ='FFFF6100')
格式预设完之后就可以对各个范围分别进行格式设置了，具体代码如下。
#对 A1 至 F6 范围内的单元格进行设置 for row in ws['A1':'F6']: for c in row:
c.font = plain_Font_style
c.alignment = Alignment_style
c.border = Border_style #对第 1 行和第 2 行的单元格进行设置 for row in ws[1:2]: for c in row:
c.font = title_Font_style
c.fill = PatternFill_style #对 E 列和 F 列的单元格进行设置 for col in ws["E":"F"]: for r in col:
r.number_format = '0.00%' #对 A9 至 B19 范围内的单元格进行设置 for row in ws['A9':'B19']: for c in row:
c.font = plain_Font_style
c.alignment = Alignment_style
c.border = Border_style #对 A9 至 B9 范围内的单元格进行设置 for row in ws['A9':'B9']: for c in row:
c.font = title_Font_style
c.fill = PatternFill_style #设置进度条 rule = DataBarRule(start_type = 'min',end_type = 'max',
color="FF638EC6", showValue=True, minLength=None,
maxLength=None)
ws.conditional_formatting.add('B10:B19',rule) #调整列宽 ws.column_dimensions['A'].width = 17 ws.column_dimensions['B'].width = 13 ws.column_dimensions['E'].width = 10
最后，将上面所有代码片段合并在一起，就是将不同的结果文件合并到同一个Sheet 中的完整代码，具体如下。
Sheet 中的完整代码，具体如下。 from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows from openpyxl.styles import colors from openpyxl.styles import Font from openpyxl.styles import PatternFill from openpyxl.styles import Border, Side from openpyxl.styles import Alignment from openpyxl.formatting.rule import DataBarRule
wb = Workbook()
ws = wb.active #先将核心指标 df_view 表插入进去 for r in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r) #再将各省份情况 df_province 表插入进去 #先将表头插入 for j in range(df_province.shape[1]):
ws.cell(row = df_view.shape[0] + 5,column = 1 + j).value = df_province.columns[r] #再把具体的值插入 #先遍历行 for i in range(df_province.shape[0]): #再遍历列 for j in range(df_province.shape[1]):
ws.cell(row = df_view.shape[0] + 6 + i,column = 1 + j).value = df_province.
iloc[i,j] #插入图片 img = Image(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量
分日趋势.png')
ws.add_image(img, 'G1') ##---格式调整--- ws.delete_rows(2)
ws['A1'] = '指标' ws.insert_rows(1)
ws['A1'] = '电商业务方向 2021/4/11 日报' ws.merge_cells('A1:F1') #合并单元格 #格式预设 #表头字体设置 title_Font_style = Font(name = '微软雅黑',size = 12,bold = True,color = "FFFFFFFF") #普通内容字体设置 plain_Font_style = Font(name = '微软雅黑',size = 12)
Alignment_style = Alignment(horizontal = "center")
Border_style = Border(left = Side(border_style = "thin",color = "FF000000"),
right = Side(border_style = "thin",color = "FF000000"),
top = Side(border_style = "thin",color = "FF000000"),
bottom = Side(border_style = "thin",color = "FF000000"))
PatternFill_style = PatternFill(fill_type = 'solid',start_color='FFFF6100') #对 A1 至 F6 范围内的单元格进行设置 for row in ws['A1':'F6']: for c in row:
c.font = plain_Font_style
c.alignment = Alignment_style
c.border = Border_style #对第 1 行和第 2 行的单元格进行设置 for row in ws[1:2]: for c in row:
c.font = title_Font_style
c.fill = PatternFill_style #对 E 列和 F 列的单元格进行设置 for col in ws["E":"F"]: for r in col:
r.number_format = '0.00%' #对 A9 至 B19 范围内的单元格进行设置 for row in ws['A9':'B19']: for c in row:
c.font = plain_Font_style
c.alignment = Alignment_style
c.border = Border_style #对 A9 至 B9 范围内的单元格进行设置 for row in ws['A9':'B9']: for c in row:
c.font = title_Font_style
c.fill = PatternFill_style #设置进度条 rule = DataBarRule(start_type = 'min',end_type = 'max',
color="FF638EC6", showValue=True, minLength=None, maxLength= None)
ws.conditional_formatting.add('B10:B19',rule) #调整列宽 ws.column_dimensions['A'].width = 17 ws.column_dimensions['B'].width = 13 ws.column_dimensions['E'].width = 10 #将结果文件进行保存 wb.save(r'D:Data-Scienceshareexcel-python 报表自动化多结果合并.xlsx')
运行上面代码，会得到如图 8 所示结果，可以看到不同结果文件合并在了一起，并且各自的格式设置完好。
（图8）
将不同的结果合并到同一工作簿的不同 Sheet 中
将不同的结果合并到同一工作簿的不同 Sheet 中比较好实现，只需要新建几个Sheet，然后对不同的 Sheet 插入数据即可，具体实现代码如下。
from openpyxl import Workbook from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
ws = wb.active
ws1 = wb.create_sheet()
ws2 = wb.create_sheet() #更改 sheet 的名称 ws.title = "核心指标" ws1.title = "各省份销情况" ws2.title = "分日趋势" for r1 in dataframe_to_rows(df_view,index = True,header = True):
ws.append(r1) for r2 in dataframe_to_rows(df_province,index = False,header = True):
ws1.append(r2)
img = Image(r'D:Data-Scienceshareexcel-python 报表自动化4.2 - 4.11 创建订单量
分日趋势.png')
ws2.add_image(img, 'A1')
wb.save(r'D:Data-Scienceshareexcel-python 报表自动化多结果合并_多 Sheet.xlsx')
运行上面代码，会得到如图 9 所示结果，可以看到创建了 3 个 Sheet，且不同的内容被保存到了不同 Sheet 中。
（图9）
本文节选自《对比Excel，轻松学习Python报表自动化》一书，更多关于使用Python进行报表自动化的内容，欢迎阅读本书！
CDA学员免费下载查看报告全文：2026全球数智化人才指数报告【CDA数据科学研究院】.pdf
数据分析咨询请扫描二维码
若不方便扫码，搜微信号：CDAshujufenxi
在中介效应分析中，人口统计学变量（如年龄、性别、学历、收入、职业等）是常见的控制变量或调节变量，其处理方式直接影响分析结 ...
2026-04-29在SQL数据库实操中，日期数据的存储与显示是高频需求，而“数字日期”（如20240520、20241231、45321）是很多开发者、数据分析师 ...
2026-04-29 很多分析师在设计标签时思路清晰，但真到落地环节却面临“数据在手，不知如何转化为可用标签”的困境：或因加工方式选择不当 ...
2026-04-29在手游行业竞争日趋白热化的当下，“流量为王”早已升级为“留存为王”，而付费用户留存率更是衡量一款手游盈利能力、运营质量的 ...
2026-04-28在日常MySQL数据库运维与开发中，经常会遇到“同一台服务器上，两个不同数据库（以下简称“源库”“目标库”）的表数据需要保持 ...
2026-04-28 很多分析师每天和数据打交道，但当被问到“标签是什么”“标签和指标有什么区别”“标签体系如何设计”时，却常常答不上来。 ...
2026-04-28箱线图（Box Plot）作为一种经典的数据可视化工具，广泛应用于统计学、数据分析、科研实证等领域，核心价值在于直观呈现数据的集 ...
2026-04-27实证分析是社会科学、自然科学、经济管理等领域开展研究的核心范式，其核心逻辑是通过对多维度数据的收集、分析与解读，揭示变量 ...
2026-04-27 很多数据分析师精通Excel函数和数据透视表，但当被问到“数据从哪里来”“表和视图有什么区别”“数据库管理系统和SQL是什么 ...
2026-04-27在大数据技术飞速迭代、数字营销竞争日趋激烈的今天，“精准触达、高效转化、成本可控”已成为企业营销的核心诉求。传统广告投放 ...
2026-04-24在游戏行业竞争白热化的当下，用户流失已成为制约游戏生命周期、影响营收增长的核心痛点。据行业报告显示，2024年移动游戏平均次 ...
2026-04-24 很多业务负责人开会常说“我们要数据驱动”，最后却变成“看哪张报表数据多就用哪个”，往往因为缺乏一套结构性的方法去搭建 ...
2026-04-24在Power BI数据可视化分析中，切片器是连接用户与数据的核心交互工具，其核心价值在于帮助使用者快速筛选目标数据、聚焦分析重点 ...
2026-04-23以数为据，以析促优——数据分析结果指导临床技术改进的实践路径 临床技术是医疗服务的核心载体，其水平直接决定患者诊疗效果、 ...
2026-04-23很多数据分析师每天盯着GMV、DAU、转化率，但当被问到“哪些指标是所有企业都需要的”“哪些指标是因行业而异的”“北极星指标和 ...
2026-04-23近日，由 CDA 数据科学研究院重磅发布的《2026 全球数智化人才指数报告》，被中国教育科学研究院官方账号正式收录， ...
2026-04-22在数字化时代，客户每一次点击、浏览、下单、咨询等行为，都在传递其潜在需求与决策倾向——这些按时间顺序串联的行为轨迹，构成 ...
2026-04-22数据是数据分析、建模与业务决策的核心基石，而“数据清洗”作为数据预处理的核心环节，是打通数据从“原始杂乱”到“干净可用” ...
2026-04-22 很多数据分析师每天盯着GMV、转化率、DAU等数字看，但当被问到“什么是指标”“指标和维度有什么区别”“如何搭建一套完整的 ...
2026-04-22在数据分析与业务决策中，数据并非静止不变的数值，而是始终处于动态波动之中——股市收盘价的每日涨跌、企业月度销售额的起伏、 ...
2026-04-21
京公网安备 11010802034615号
经营许可证编号：京B2-20210330
