实战如何制作数据报表并实现自动化？

　　本章给大家演示一下在实际工作中如何结合 Pandas 库和 openpyxl 库来自动化生成报表。假设我们现在有如图 1 所示的数据集。
　　（图1）
　　现在需要根据这份数据集来制作每天的日报情况，主要包含以下 3 个方面。  当日各项指标的同/环比情况。  当日各省份创建订单量情况。  最近一段时间创建订单量趋势。
　　接下来分别实现。  01、当日各项指标的同/环比情况
　　我们先用 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）
　　可以看到各项均已设置成功。  02、当日各省份创建订单量情况
　　我们同样先利用 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）  03、最近一段时间创建订单量趋势
　　一般用折线图反映某个指标的趋势情况，我们前面也讲过，在实际工作中一般用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）  04、将不同的结果进行合并
　　上面我们是把每一部分都单独拆开来实现的，最后存储在了不同的 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进行报表自动化的内容，欢迎阅读本书！