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

　　本章给大家演示一下在实际工作中如何结合Pandas库和openpyxl库来自动化生成报表。假设我们现在有如图1所示的数据集。
　　（图1）
　　现在需要根据这份数据集来制作每天的日报情况，主要包含以下3个方面。当日各项指标的同环比情况。当日各省份创建订单量情况。最近一段时间创建订单量趋势。
　　接下来分别实现。01、当日各项指标的同环比情况
　　我们先用Pandas库对数据进行计算处理，得到各指标的同环比情况，具体实现代码如下。导入文件importpandasaspddfpd。readexcel（rD：DataScienceshareexcelpython报表自动化saledata。xlsx）构造同时获取不同指标的函数defgetdata（date）：createcntdf〔df〔创建日期〕date〕〔orderid〕。count（）paycntdf〔df〔付款日期〕date〕〔orderid〕。count（）receivecntdf〔df〔收货日期〕date〕〔orderid〕。count（）returncntdf〔df〔退款日期〕date〕〔orderid〕。count（）returncreatecnt，paycnt，receivecnt，returncnt假设当日是20210411获取不同时间段的各指标值dfviewpd。DataFrame（〔getdata（20210411），getdata（20210410），getdata（20210404）〕，columns〔创建订单量，付款订单量，收货订单量，退款订单量〕，index〔当日，昨日，上周同期〕）。Tdfview〔环比〕dfview〔当日〕dfview〔昨日〕1dfview〔同比〕dfview〔当日〕dfview〔上周同期〕1dfview
　　运行上面代码会得到如图2所示结果。
　　（图2）
　　上面只是得到了各指标的同环比绝对数值，但是日报在发出去之前一般都要做一些格式调整，比如调整字体。而格式调整需要用到openpyxl库，我们将Pandas库中DataFrame格式的数据转化为适用openpyxl库的数据格式，具体实现代码如下。fromopenpyxlimportWorkbookfromopenpyxl。utils。dataframeimportdataframetorows创建空工作簿wbWorkbook（）wswb。active将DataFrame格式数据转化为openpyxl格式forrindataframetorows（dfview，indexTrue，headerTrue）：ws。append（r）wb。save（rD：DataScienceshareexcelpython报表自动化核心指标原始。xlsx）
　　运行上面代码会得到如图3所示结果，可以看到原始的数据文件看起来是很混乱的。
　　（图3）
　　接下来，对上面的原始数据文件进行格式调整，具体调整代码如下。fromopenpyxlimportWorkbookfromopenpyxl。utils。dataframeimportdataframetorowsfromopenpyxl。stylesimportcolorsfromopenpyxl。stylesimportFontfromopenpyxl。stylesimportPatternFillfromopenpyxl。stylesimportBorder，Sidefromopenpyxl。stylesimportAlignmentwbWorkbook（）wswb。activeforrindataframetorows（dfview，indexTrue，headerTrue）：ws。append（r）第2行是空的，删除第2行ws。deleterows（2）给A1单元格进行赋值ws〔A1〕指标插入一行作为标题行ws。insertrows（1）ws〔A1〕电商业务方向2021411日报将标题行的单元格进行合并ws。mergecells（A1：F1）合并单元格对第1行至第6行的单元格进行格式设置forrowinws〔1：6〕：forcinrow：字体设置c。fontFont（name微软雅黑，size12）对齐方式设置c。alignmentAlignment（horizontalcenter）边框线设置c。borderBorder（leftSide（borderstylethin，colorFF000000），rightSide（borderstylethin，colorFF000000），topSide（borderstylethin，colorFF000000），bottomSide（borderstylethin，colorFF000000））对标题行和表头行进行特殊设置forrowinws〔1：2〕：forcinrow：c。fontFont（name微软雅黑，size12，boldTrue，colorFFFFFFFF）c。fillPatternFill（filltypesolid，startcolorFFFF6100）将环比和同比设置成百分比格式forcolinws〔E：F〕：forrincol：r。numberformat0。00调整列宽ws。columndimensions〔A〕。width13ws。columndimensions〔E〕。width10保存调整后的文件wb。save（rD：DataScienceshareexcelpython报表自动化核心指标。xlsx）
　　运行上面代码会得到如图4所示结果。
　　（图4）
　　可以看到各项均已设置成功。02、当日各省份创建订单量情况
　　我们同样先利用Pandas库处理得到当日各省份创建订单量的情况，具体实现代码如下。dfprovincepd。DataFrame（df〔df〔创建日期〕20210411〕。groupby（省份）〔orderid〕。count（））dfprovincedfprovince。resetindex（）dfprovincedfprovince。sortvalues（byorderid，ascendingFalse）dfprovincedfprovince。rename（columns｛orderid：创建订单量｝）dfprovince
　　运行上面代码会得到如图5所示结果。
　　（图5）
　　在得到各省份当日创建订单量的绝对数值之后，同样对其进行格式设置，具体设置代码如下。fromopenpyxlimportWorkbookfromopenpyxl。utils。dataframeimportdataframetorowsfromopenpyxl。stylesimportcolorsfromopenpyxl。stylesimportFontfromopenpyxl。stylesimportPatternFillfromopenpyxl。stylesimportBorder，Sidefromopenpyxl。stylesimportAlignmentfromopenpyxl。formatting。ruleimportDataBarRulewbWorkbook（）wswb。activeforrindataframetorows（dfprovince，indexFalse，headerTrue）：ws。append（r）对第1行至第11行的单元格进行设置forrowinws〔1：11〕：forcinrow：字体设置c。fontFont（name微软雅黑，size12）对齐方式设置c。alignmentAlignment（horizontalcenter）边框线设置c。borderBorder（leftSide（borderstylethin，colorFF000000），rightSide（borderstylethin，colorFF000000），topSide（borderstylethin，colorFF000000），bottomSide（borderstylethin，colorFF000000））设置进度条条件格式ruleDataBarRule（starttypemin，endtypemax，colorFF638EC6，showValueTrue，minLengthNone，maxLengthNone）ws。conditionalformatting。add（B1：B11，rule）对第1行标题行进行设置forcinws〔1〕：c。fontFont（name微软雅黑，size12，boldTrue，colorFFFFFFFF）c。fillPatternFill（filltypesolid，startcolorFFFF6100）调整列宽ws。columndimensions〔A〕。width17ws。columndimensions〔B〕。width13保存调整后的文件wb。save（rD：DataScienceshareexcelpython报表自动化各省份销量情况。xlsx）
　　运行上面代码会得到如图6所示结果。
　　（图6）03、最近一段时间创建订单量趋势
　　一般用折线图反映某个指标的趋势情况，我们前面也讲过，在实际工作中一般用matplotlib库或者其他可视化库进行图表绘制，并将其保存，然后利用openpyxl库将图表插入Excel中。
　　先利用matplotlib库进行绘图，具体实现代码如下。matplotlibinlineimportmatplotlib。pyplotaspltplt。rcParams〔font。sansserif〕SimHei解决中文乱码设置图表大小plt。figure（figsize（10，6））df。groupby（创建日期）〔orderid〕。count（）。plot（）plt。title（4。24。11创建订单量分日趋势）plt。xlabel（日期）plt。ylabel（订单量）将图表保存到本地plt。savefig（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。png）
　　将保存到本地的图表插入Excel中，具体实现代码如下。fromopenpyxlimportWorkbookfromopenpyxl。drawing。imageimportImagewbWorkbook（）wswb。activeimgImage（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。png）ws。addimage（img，A1）wb。save（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。xlsx）
　　运行上面代码会得到如图7所示结果，可以看到图表已经被成功插入Excel中。
　　（图7）04、将不同的结果进行合并
　　上面我们是把每一部分都单独拆开来实现的，最后存储在了不同的Excel文件中。
　　当然，有时放在不同文件中会比较麻烦，就需要把这些结果合并在同一个Excel的相同Sheet或者不同Sheet中。
　　将不同的结果合并到同一个Sheet中
　　将不同的结果合并到同一个Sheet中的难点在于不同表结果的结构不一样，而且需要在不同结果之间进行留白。
　　首先，插入核心指标表dfreview，插入方式与单独插入是一样的，具体代码如下。forrindataframetorows（dfview，indexTrue，headerTrue）：ws。append（r）
　　然后，插入各省份情况表dfprovince，因为append（）方法默认是从第1行开始插入的，而我们前面几行已经有dfview表的数据了，所以就不能用append（）方法插入，而只能通过遍历每一个单元格的方式。
　　那我们怎么知道要遍历哪些单元格呢？核心是需要知道遍历开始的行列和遍历结束的行列。
　　遍历开始的行dfview表占据的行留白的行（一般表与表之间留2行）1
　　遍历结束的行遍历开始的行dfprovince表占据的行
　　遍历开始的列1
　　遍历结束的列dfprovince表占据的列
　　又因为DataFrame中获取列名的方式和获取具体值的方式不太一样，所以我们需要分别插入，先插入列名，具体代码如下。forjinrange（dfprovince。shape〔1〕）：ws。cell（rowdfview。shape〔0〕5，column1j）。valuedfprovince。columns〔r〕dfprovince。shape〔1〕表示获取dfprovince表有多少列，dfview。shape〔0〕表示获取dfview表有多少行。
　　前面说过，遍历开始的行是表占据的行加上留白的行再加1，一般留白的行是2，
　　可是这里为什么是dfview。shape〔0〕5呢？因为dfview。shape〔0〕是不包括列名行的，而且在插入Excel中时会默认增加1行空行，所以需要在留白行的基础上再增加2行，
　　即2215。
　　因为range（）函数默认是从0开始的，而Excel中的列是从1开始的，所以column需要加1。
　　上面的代码只是把dfprovince表的列名插入进来，接下来插入具体的值，方式与插入列名的方式一致，只不过需要在列名的下一行开始插入，具体代码如下。foriinrange（dfprovince。shape〔0〕）：forjinrange（dfprovince。shape〔1〕）：ws。cell（rowdfview。shape〔0〕6i，column1j）。valuedfprovince。iloc〔i，j〕
　　接下来，插入图片，插入图片的方式与前面的单独插入方法是一致的，具体代码如下。插入图片imgImage（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。png）ws。addimage（img，G1）
　　将所有的数据插入以后就该对这些数据进行格式设置了，因为不同表的结构不一样，所以我们没法直接批量对所有单元格进行格式设置，只能按范围分别进行设置，而不同范围的格式可能是一样的，所以我们先预设一些格式变量，这样后面用到的时候直接调取这些变量即可，减少代码冗余，具体代码如下。格式预设表头字体设置titleFontstyleFont（name微软雅黑，size12，boldTrue，colorFFFFFFFF）普通内容字体设置plainFontstyleFont（name微软雅黑，size12）AlignmentstyleAlignment（horizontalcenter）BorderstyleBorder（leftSide（borderstylethin，colorFF000000），rightSide（borderstylethin，colorFF000000），topSide（borderstylethin，colorFF000000），bottomSide（borderstylethin，colorFF000000））PatternFillstylePatternFill（filltypesolid，startcolorFFFF6100）
　　格式预设完之后就可以对各个范围分别进行格式设置了，具体代码如下。对A1至F6范围内的单元格进行设置forrowinws〔A1：F6〕：forcinrow：c。fontplainFontstylec。alignmentAlignmentstylec。borderBorderstyle对第1行和第2行的单元格进行设置forrowinws〔1：2〕：forcinrow：c。fonttitleFontstylec。fillPatternFillstyle对E列和F列的单元格进行设置forcolinws〔E：F〕：forrincol：r。numberformat0。00对A9至B19范围内的单元格进行设置forrowinws〔A9：B19〕：forcinrow：c。fontplainFontstylec。alignmentAlignmentstylec。borderBorderstyle对A9至B9范围内的单元格进行设置forrowinws〔A9：B9〕：forcinrow：c。fonttitleFontstylec。fillPatternFillstyle设置进度条ruleDataBarRule（starttypemin，endtypemax，colorFF638EC6，showValueTrue，minLengthNone，maxLengthNone）ws。conditionalformatting。add（B10：B19，rule）调整列宽ws。columndimensions〔A〕。width17ws。columndimensions〔B〕。width13ws。columndimensions〔E〕。width10
　　最后，将上面所有代码片段合并在一起，就是将不同的结果文件合并到同一个Sheet中的完整代码，具体如下。Sheet中的完整代码，具体如下。fromopenpyxlimportWorkbookfromopenpyxl。utils。dataframeimportdataframetorowsfromopenpyxl。stylesimportcolorsfromopenpyxl。stylesimportFontfromopenpyxl。stylesimportPatternFillfromopenpyxl。stylesimportBorder，Sidefromopenpyxl。stylesimportAlignmentfromopenpyxl。formatting。ruleimportDataBarRulewbWorkbook（）wswb。active先将核心指标dfview表插入进去forrindataframetorows（dfview，indexTrue，headerTrue）：ws。append（r）再将各省份情况dfprovince表插入进去先将表头插入forjinrange（dfprovince。shape〔1〕）：ws。cell（rowdfview。shape〔0〕5，column1j）。valuedfprovince。columns〔r〕再把具体的值插入先遍历行foriinrange（dfprovince。shape〔0〕）：再遍历列forjinrange（dfprovince。shape〔1〕）：ws。cell（rowdfview。shape〔0〕6i，column1j）。valuedfprovince。iloc〔i，j〕插入图片imgImage（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。png）ws。addimage（img，G1）格式调整ws。deleterows（2）ws〔A1〕指标ws。insertrows（1）ws〔A1〕电商业务方向2021411日报ws。mergecells（A1：F1）合并单元格格式预设表头字体设置titleFontstyleFont（name微软雅黑，size12，boldTrue，colorFFFFFFFF）普通内容字体设置plainFontstyleFont（name微软雅黑，size12）AlignmentstyleAlignment（horizontalcenter）BorderstyleBorder（leftSide（borderstylethin，colorFF000000），rightSide（borderstylethin，colorFF000000），topSide（borderstylethin，colorFF000000），bottomSide（borderstylethin，colorFF000000））PatternFillstylePatternFill（filltypesolid，startcolorFFFF6100）对A1至F6范围内的单元格进行设置forrowinws〔A1：F6〕：forcinrow：c。fontplainFontstylec。alignmentAlignmentstylec。borderBorderstyle对第1行和第2行的单元格进行设置forrowinws〔1：2〕：forcinrow：c。fonttitleFontstylec。fillPatternFillstyle对E列和F列的单元格进行设置forcolinws〔E：F〕：forrincol：r。numberformat0。00对A9至B19范围内的单元格进行设置forrowinws〔A9：B19〕：forcinrow：c。fontplainFontstylec。alignmentAlignmentstylec。borderBorderstyle对A9至B9范围内的单元格进行设置forrowinws〔A9：B9〕：forcinrow：c。fonttitleFontstylec。fillPatternFillstyle设置进度条ruleDataBarRule（starttypemin，endtypemax，colorFF638EC6，showValueTrue，minLengthNone，maxLengthNone）ws。conditionalformatting。add（B10：B19，rule）调整列宽ws。columndimensions〔A〕。width17ws。columndimensions〔B〕。width13ws。columndimensions〔E〕。width10将结果文件进行保存wb。save（rD：DataScienceshareexcelpython报表自动化多结果合并。xlsx）
　　运行上面代码，会得到如图8所示结果，可以看到不同结果文件合并在了一起，并且各自的格式设置完好。
　　（图8）
　　将不同的结果合并到同一工作簿的不同Sheet中
　　将不同的结果合并到同一工作簿的不同Sheet中比较好实现，只需要新建几个Sheet，然后对不同的Sheet插入数据即可，具体实现代码如下。fromopenpyxlimportWorkbookfromopenpyxl。utils。dataframeimportdataframetorowswbWorkbook（）wswb。activews1wb。createsheet（）ws2wb。createsheet（）更改sheet的名称ws。title核心指标ws1。title各省份销情况ws2。title分日趋势forr1indataframetorows（dfview，indexTrue，headerTrue）：ws。append（r1）forr2indataframetorows（dfprovince，indexFalse，headerTrue）：ws1。append（r2）imgImage（rD：DataScienceshareexcelpython报表自动化4。24。11创建订单量分日趋势。png）ws2。addimage（img，A1）wb。save（rD：DataScienceshareexcelpython报表自动化多结果合并多Sheet。xlsx）
　　运行上面代码，会得到如图9所示结果，可以看到创建了3个Sheet，且不同的内容被保存到了不同Sheet中。
　　（图9）
　　本文节选自《对比Excel，轻松学习Python报表自动化》一书，更多关于使用Python进行报表自动化的内容，欢迎阅读本书！