全文共2391字，预计学习时长14分钟




图源：morioh

 

工作中，无穷无尽的表格有时会令人抓狂。Excel无处不在，即便有着像Python这样的强大工具任你使用，你也难以从中逃脱。

 

也许你的老板和同事仍然需要这种简单的方法来访问重要数据。但没关系！那并不意味着你不能通过使用Python来简化Excel工作。

 

全程无需使用Excel，逃离报表魔爪！

 

用Python将Excel报表自动化

 

你还在天天做Excel报表吗？而且还是为不同的客户做着四五次同样的报表吗？不如让Python代劳吧！

 

通过使用笔者在数据透视表教程中的数据（

https://towardsdatascience.com/a-step-by-step-guide-to-pandas-pivot-tables-e0641d0c6c70），概览一下我们将要自动化到底是什么。

 

数据格式将使你期望从公司数据库中获得的内容与客户销售数据相匹配，与你期望从公司数据库中获得的数据相匹配。它按区域细分这些数据，并且创建两个带有格式和图表的汇总表。无需使用Excel！

 



最终的数据流—一个工作簿转化为四个格式化报告 | 图源：Nik Piepenbreier

 

加载库

 

使用Pandas和Openpyxl。笔者的“用Python自动化这三项（无聊！！！）Excel任务”（

https://towardsdatascience.com/automate-these-3-boring-excel-tasks-with-python-666b4ded101b）一文中涵盖了许多关于Openpyxl的内容，对其如何运行提供了详细介绍。

 

#Section 1 - Loading our Libraries
              import pandas as pd
                  fromopenpyxl import load_workbook
              from openpyxl.styles importFont
              from openpyxl.chart importBarChart, Reference

 

你使用如下两个Librariy:

 

1. Pandas负责转化数据，并创建初始Excel文件

2. Openpyxl将工作薄格式化，并插入图表

 

加载数据

 

加载数据，并概览正在处理的内容。正如笔者所提到的，这些数据与读者从公司数据库系统获得的数据相似。

 

#Section 2 - Loading our Data
             df = pd.read_excel('https://github.com/datagy/pivot_table_pandas/raw/master/sample_pivot.xlsx', parse_dates=['Date'])
             print(df.head())
                  #        Date Region                 Type  Units Sales
             #0 2020-07-11   East Children's Clothing   18.0    306
             #1 2020-09-23  North Children's Clothing   14.0    448
             #2 2020-04-02  South    Women's Clothing   17.0    425
             #3 2020-02-28   East Children's Clothing   26.0    832
             #4 2020-03-19   West    Women's Clothing    3.0     33

 

在这里，使用Pandas读取Excel文件，并读取Date/日期列的日期。

 

创建数据透视表

 

接着我们要创建最终报告中所需要的汇总表。这里将创建一个数据透视表以作为例子，其中仅使用东部地区进行代码的实验。

 

#Section 3 - Testing Pivot Tables
            filtered= df[df['Region'] =='East']
            quarterly_sales= pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')
                  print("Quarterly Sales Pivot Table:")
            print(quarterly_sales.head())
                  #Quarterly Sales Pivot Table:
            #Type Children's Clothing  Men'sClothing  Women's Clothing
            #Date                                                      
            #1                   12274           13293             16729
            #2                    5496           17817             22384
            #3                   14463            9622             15065
            #4                  13616           10953             16051

 

创建第一个Excel文件

 

有了数据透视表后，将其导入到一个Excel文件中，我们将用pandas来导入：

 

#Section 04 - Creating and Excel Workbook
                 file_path=#Path to where you want your file saved
                 quarterly_sales.to_excel(file_path, sheet_name ='Quarterly Sales', startrow=3)

 

这一步是在做什么：

 

· 创建一个文件路径变量，以确定要将文件存储在何处，

· 使用ExcelWriter保存文件

· 将两个透视表保存到单独的工作表中，从第3行开始（稍后从中保留以用于页眉）

 

使报表更漂亮

 

Pandas有助于将数据导入到Excel中。既然数据已经导入Excel，不妨将其美化一下，来添加一些可视化效果。

 

#Section 05 - Loading the Workbook
                 wb =load_workbook(file_path)
                 sheet1= wb['Quarterly Sales']
                  # Section 06 - Formatting the First Sheet
                 sheet1['A1'] ='Quarterly Sales'
                 sheet1['A2'] ='datagy.io'
                 sheet1['A4'] ='Quarter'
                  sheet1['A1'].style ='Title'
                 sheet1['A2'].style ='Headline 2'
                  for i inrange(5, 9):
                     sheet1[f'B{i}'].style='Currency'
                     sheet1[f'C{i}'].style='Currency'
                     sheet1[f'D{i}'].style='Currency'
                  # Section 07 - Adding a Bar Chart
                 bar_chart=BarChart()
                 data=Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)
                 categories=Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)
                 bar_chart.add_data(data, titles_from_data=True)
                 bar_chart.set_categories(categories)
                 sheet1.add_chart(bar_chart, "F4")
                  bar_chart.title ='Sales by Type'
                 bar_chart.style=3
                 wb.save(filename = file_path)

在Section 5中，将工作簿和工作表加载到Openpyxl可以处理的单独对象中。

 

而Section 6中操作更多：

 

· 在表一的A1和A2单元格中添加标题和副标题。

· 更改“quarters”列的标题，使其更能反映数据。

· 对标题和副标题应用样式。

· 将金融领域的单元格转换为货币。这需要对每单个单元格进行单独处理。因此使用了for循环。

 

在Section 7中，添加了条形图：

 

· 创建一个条形图对象，并识别存储数据和类别的字段。

· 随后将数据和类别应用于对象。

· 最后，添加描述性标题和样式。使用许多不同的样式都试试！

 

这就是工作簿现在的样子：

 



所得工作簿之一 | 图源: Nik Piepenbreier

 

对多个工作簿执行工作流自动化

 

虽然已经很方便了，但是仅在一个区域执行这样的操作只能节约一点点的时间。我们可使用for循环，对所有的区域执行此操作。

 

#Section 08 - Getting Region Names
            regions =list(df['Region'].unique())
                  # Section 09 - Looping Over All Regions
            folder_path=#Insert the path to the folder you want tosave the reports in
                  for region in regions:
                filtered =df[df['Region'] ==f'{region}']
                quarterly_sales = pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')
                file_path =f"{path to your folder}{region}.xlsx"
                quarterly_sales.to_excel(file_path,sheet_name ='QuarterlySales', startrow=3)
                            wb =load_workbook(file_path)
                sheet1 = wb['Quarterly Sales']
                            sheet1['A1'] ='Quarterly Sales'
                sheet1['A2'] ='datagy.io'
                sheet1['A4'] ='Quarter'
                      sheet1['A1'].style ='Title'
                sheet1['A2'].style='Headline 2'
                      for i inrange(5, 10):
                    sheet1[f'B{i}'].style='Currency'
                    sheet1[f'C{i}'].style='Currency'
                    sheet1[f'D{i}'].style='Currency'
                      bar_chart =BarChart()
                data =Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)
                categories =Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)
                bar_chart.add_data(data,titles_from_data=True)
                bar_chart.set_categories(categories)
                sheet1.add_chart(bar_chart,"F4")
                      bar_chart.title='Sales by Type'
                bar_chart.style =3

 

在Section 8中，创建了一个列表，其中包含了想要覆盖的不同区域的所有唯一值。

 

在Section 9中，在for循环中重复先前的代码：

 

 

· 创建一个新变量，该变量用于保存文件所在文件夹的路径

· 接下来，使用f-strings将区域名插入到脚本中，使得脚本对每个区域都是动态的。

 



图源：unsplash

 

Python的好处在于，它可使重复的任务具有可伸缩性。

 

想象一下，如果你每天都会收到这份文件，并且每天都要创建这些工作簿。这种方法能帮你节省多少时间！



我们一起分享AI学习与发展的干货

欢迎关注全平台AI垂类自媒体 “读芯术”



（添加小编微信：dxsxbb，加入读者圈，一起讨论最新鲜的人工智能科技哦～）

全文共2391字，预计学习时长14分钟
图源：morioh
工作中，无穷无尽的表格有时会令人抓狂。Excel无处不在，即便有着像Python这样的强大工具任你使用，你也难以从中逃脱。
也许你的老板和同事仍然需要这种简单的方法来访问重要数据。但没关系！那并不意味着你不能通过使用Python来简化Excel工作。
全程无需使用Excel，逃离报表魔爪！
用Python将Excel报表自动化
你还在天天做Excel报表吗？而且还是为不同的客户做着四五次同样的报表吗？不如让Python代劳吧！
通过使用笔者在数据透视表教程中的数据(https://towardsdatascience.com/a-step-by-step-guide-to-pandas-pivot-tables-e0641d0c6c70)，概览一下我们将要自动化到底是什么。
数据格式将使你期望从公司数据库中获得的内容与客户销售数据相匹配，与你期望从公司数据库中获得的数据相匹配。它按区域细分这些数据，并且创建两个带有格式和图表的汇总表。无需使用Excel！
最终的数据流—一个工作簿转化为四个格式化报告 | 图源：Nik Piepenbreier
加载库
使用Pandas和Openpyxl。笔者的“用Python自动化这三项(无聊！！！)Excel任务”( https://towardsdatascience.com/automate-these-3-boring-excel-tasks-with-python-666b4ded101b)一文中涵盖了许多关于Openpyxl的内容，对其如何运行提供了详细介绍。
#Section 1 - Loading our Libraries              import pandas as pd                  fromopenpyxl import load_workbook              from openpyxl.styles importFont              from openpyxl.chart importBarChart, Reference
你使用如下两个Librariy:
1. Pandas负责转化数据，并创建初始Excel文件
2. Openpyxl将工作薄格式化，并插入图表
加载数据
加载数据，并概览正在处理的内容。正如笔者所提到的，这些数据与读者从公司数据库系统获得的数据相似。
#Section 2 - Loading our Data             df = pd.read_excel('https://github.com/datagy/pivot_table_pandas/raw/master/sample_pivot.xlsx', parse_dates=['Date'])             print(df.head())                  #        Date Region                 Type  Units Sales             #0 2020-07-11   East Children's Clothing   18.0    306             #1 2020-09-23  North Children's Clothing   14.0    448             #2 2020-04-02  South    Women's Clothing   17.0    425             #3 2020-02-28   East Children's Clothing   26.0    832             #4 2020-03-19   West    Women's Clothing    3.0     33
在这里，使用Pandas读取Excel文件，并读取Date/日期列的日期。
创建数据透视表
接着我们要创建最终报告中所需要的汇总表。这里将创建一个数据透视表以作为例子，其中仅使用东部地区进行代码的实验。
#Section 3 - Testing Pivot Tables            filtered= df[df['Region'] =='East']            quarterly_sales= pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')                  print("Quarterly Sales Pivot Table:")            print(quarterly_sales.head())                  #Quarterly Sales Pivot Table:            #Type Children's Clothing  Men'sClothing  Women's Clothing            #Date                                                                  #1                   12274           13293             16729            #2                    5496           17817             22384            #3                   14463            9622             15065            #4                  13616           10953             16051
创建第一个Excel文件
有了数据透视表后，将其导入到一个Excel文件中，我们将用pandas来导入：
#Section 04 - Creating and Excel Workbook                 file_path=#Path to where you want your file saved                 quarterly_sales.to_excel(file_path, sheet_name ='Quarterly Sales', startrow=3)
这一步是在做什么：
· 创建一个文件路径变量，以确定要将文件存储在何处，
· 使用ExcelWriter保存文件
· 将两个透视表保存到单独的工作表中，从第3行开始(稍后从中保留以用于页眉)
使报表更漂亮
Pandas有助于将数据导入到Excel中。既然数据已经导入Excel，不妨将其美化一下，来添加一些可视化效果。
#Section 05 - Loading the Workbook                 wb =load_workbook(file_path)                 sheet1= wb['Quarterly Sales']                  # Section 06 - Formatting the First Sheet                 sheet1['A1'] ='Quarterly Sales'                 sheet1['A2'] ='datagy.io'                 sheet1['A4'] ='Quarter'                  sheet1['A1'].style ='Title'                 sheet1['A2'].style ='Headline 2'                  for i inrange(5, 9):                     sheet1[f'B{i}'].style='Currency'                     sheet1[f'C{i}'].style='Currency'                     sheet1[f'D{i}'].style='Currency'                  # Section 07 - Adding a Bar Chart                 bar_chart=BarChart()                 data=Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)                 categories=Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)                 bar_chart.add_data(data, titles_from_data=True)                 bar_chart.set_categories(categories)                 sheet1.add_chart(bar_chart, "F4")                  bar_chart.title ='Sales by Type'                 bar_chart.style=3                 wb.save(filename = file_path)
在Section 5中，将工作簿和工作表加载到Openpyxl可以处理的单独对象中。
而Section 6中操作更多：
· 在表一的A1和A2单元格中添加标题和副标题。
· 更改“quarters”列的标题，使其更能反映数据。
· 对标题和副标题应用样式。
· 将金融领域的单元格转换为货币。这需要对每单个单元格进行单独处理。因此使用了for循环。
在Section 7中，添加了条形图：
· 创建一个条形图对象，并识别存储数据和类别的字段。
· 随后将数据和类别应用于对象。
· 最后，添加描述性标题和样式。使用许多不同的样式都试试！
这就是工作簿现在的样子：
所得工作簿之一 | 图源: Nik Piepenbreier
对多个工作簿执行工作流自动化
虽然已经很方便了，但是仅在一个区域执行这样的操作只能节约一点点的时间。我们可使用for循环，对所有的区域执行此操作。
#Section 08 - Getting Region Names            regions =list(df['Region'].unique())                  # Section 09 - Looping Over All Regions            folder_path=#Insert the path to the folder you want tosave the reports in                  for region in regions:                filtered =df[df['Region'] ==f'{region}']                quarterly_sales = pd.pivot_table(filtered, index =filtered['Date'].dt.quarter, columns ='Type', values ='Sales', aggfunc='sum')                file_path =f"{path to your folder}{region}.xlsx"                quarterly_sales.to_excel(file_path,sheet_name ='QuarterlySales', startrow=3)                            wb =load_workbook(file_path)                sheet1 = wb['Quarterly Sales']                            sheet1['A1'] ='Quarterly Sales'                sheet1['A2'] ='datagy.io'                sheet1['A4'] ='Quarter'                      sheet1['A1'].style ='Title'                sheet1['A2'].style='Headline 2'                      for i inrange(5, 10):                    sheet1[f'B{i}'].style='Currency'                    sheet1[f'C{i}'].style='Currency'                    sheet1[f'D{i}'].style='Currency'                      bar_chart =BarChart()                data =Reference(sheet1, min_col=2, max_col=4, min_row=4, max_row=8)                categories =Reference(sheet1, min_col=1, max_col=1, min_row=5, max_row=8)                bar_chart.add_data(data,titles_from_data=True)                bar_chart.set_categories(categories)                sheet1.add_chart(bar_chart,"F4")                      bar_chart.                bar_chart.style =3
在Section 8中，创建了一个列表，其中包含了想要覆盖的不同区域的所有唯一值。
在Section 9中，在for循环中重复先前的代码：
· 创建一个新变量，该变量用于保存文件所在文件夹的路径
· 接下来，使用f-strings将区域名插入到脚本中，使得脚本对每个区域都是动态的。
图源：unsplash
Python的好处在于，它可使重复的任务具有可伸缩性。
想象一下，如果你每天都会收到这份文件，并且每天都要创建这些工作簿。这种方法能帮你节省多少时间！
留言点赞关注
我们一起分享AI学习与发展的干货
如转载，请后台留言，遵守转载规范

点点写在前面：
欧阳山东 @我最帅 作为小数点Python第二期的学员又双叒叕来分享干货啦！自东哥上次分享完关于咖啡馆的分析后，后台突然冒出各种灵魂拷问，如何学习Python的鸭？还有没有别的分析案例鸭？这次他给大家带来一个新分享，关于如何用Python做报表自动化。
记得我在上次分享的时候，说了Python可以实现很多自动化设计，有些还很高端。
当然了，东哥目前的功力，高端的还是做不来，但是简单的还是可以的。
所以今天我教你做两个简单而且实用的自动化，第一，报表生成自动化；第二，邮件发送自动化。
但我想说明的是，并不是所有报表都是需要或者适合做自动化的。
我们做自动化的目的是为了减少工作量，把一些重复性的工作交给程序处理。
在数据分析实践中，日报，周报，这种频率性很强的东西，就需要自动化；而那些不同活动的效果数据，每次都不一样，这样的做自动化就没有太大意义。
—1—报表自动化
我还是拿上次那个咖啡馆的例子来做案例。数据如下：
如果我想实现下面这个效果，怎么做自动化呢？
如果你每个月或者每周要这样一张表，而且这张表的数据都放在SQL里或者excel文件里，那么你就要告诉Python，怎么从这些表里做数据，这个做的过程，也就是自动化实现的过程。
解释一下：同比是指和去年同月相比；环比是指和上个月相比。
1、写本月（8月）数据代码
s代表销售额，t代表销售量（客流量），k代表件均销售额。
2、写上月（7月）数据代码
3、写去年（8月）数据代码
4、将上面三段代码算出的数据综合为一张表，然后算出同比环比，且将数据保存为“本月数据同比环比情况”表格到桌面备用，代码如下：
至此，我们想要的数据已经全部拿到了，以后你想要这样的表格，稍微改一下然后运行一下这段代码就好了。
我举例的这个表格还很简单，主要是为了演示更清晰，所以还看不出自动化的威力。但是如果是大量的日报数据指标，自动化的便捷就出来了。
所以，是不是很省事呢？
—2—代码函数化
细心的你，可能此刻已经看出我上面三段代码几乎是一模一样的了，只是改了一下时间而已。
对的，问题就在这里。在Python里，你记住我的一句话，只要涉及到重复的东西，就必然可以优化代码，这个优化过程，可以用自定义函数来解决。
定义好函数后，你就可以重复用这段函数帮你算数据了。
所以我先定义了一个monthly_data(df)函数用来算每个月的数据，就是黑色方框里的。这样你想要哪个月的数据，你就用这个月的数据去套用函数就行。
我把函数运行了3次，分别算出本月，上月，去年同月的数据，完整代码如下：
当然，结果也一模一样：
你会发现，运用了函数后，代码可以写得更少，而且可读性更强。另外说一嘴，会不会函数编程是区分菜鸟和熟手的重要区别喽。
—3—邮件自动化
我不知道大家都用的是什么邮箱，反正我一直用的是QQ邮箱。不过不管什么邮箱，自动化流程都是差不多的，下面我用自己的QQ邮箱来做个例子。
这个代码会用到很多大家并不熟悉的第三方库，没办法，我今天也是弄了很久，百度了很多东西才搞懂的。
不过你不需要为此多费心了，因为我帮你搞通了，你只要按照我的代码去做，就OK。
首先要在邮箱里进行设置。
红色方框里的东西打开，这样可以获得授权码用来在Python中执行登录发送等动作。
好，接下来就是自动化发邮件的代码实现了。完整代码如下：
代码写好了，运行后，即可收到邮件。
哎，这个过程我其实花了很多时间，中间遇到好几个bug没办法解决，比如设置附件啊，服务器端口啊地址啊，出现了索引错误啊，这些都是通过百度一个一个找别人的经验然后复制代码试错最后完成的。
好了，不说了，我们来看看结果吧：
看到没，红色方框里的东西都是我在上面代码中写好的。所以邮件自动化发送还是很简单的对不对？
嗯，今天的分享就到这里，我希望以后每周周末都能做个数据分析的小案例分享。大家可以一起玩啊，我还只是小菜鸟。
END
文章作者小数点python数据分析课程第二期学员 @我最帅 
更多python数据分析干货，欢迎关注我们的微信公众号：POINT小数点数据

人人都可以简单入门Python、爬虫、数据分析简说Python严选
来源：python数据分析之禅作者：小dull鸟One old watch, like brief python大家好，我是老表～今天和大家分享的内容是关于Python自动化操作excel一些方法和技巧，学习了记得点赞、留言、转发，三连哦～
公司财务小姐姐每月需要根据如下报表统计出本月、上月、去年同期的销售额、客流量、客单价数据：
每个月都要手动计算，非常耗费时间，本着乐于助人的原则，我用python写了个小程序，能够实现自动生成财务报表，现把具体过程分享给大家：
一、pandas导入数据源
import pandas as pd
data=pd.read_csv('数据模板.csv',encoding='gbk',parse_dates=['成交时间'])
data
parse_dates可以将成交时间列转换成时间格式
二、pandas计算相关指标
先给大家介绍一下指标的计算方式：销售额=单价*销量客流量=订单id去重后的次数客单价=销售额/客流量
1.计算上月数据
通过pandas比较功能，取出本月数据，&为比较运算符，相当于“与”
from datetime import datetime
current_m=data[(data['成交时间']>=datetime(2020,2,1))&(data['成交时间']<=datetime(2020,2,28))]
current_m
销售额计算current_s=(current_m['销量']*current_m['单价']).sum()
current_ssum是pandas中的求和函数，用于返回用户所请求轴的值之和。
客流量计算current_t=current_m['订单ID'].drop_duplicates().count()
current_tdrop_duplicatesop:去重函数count:计数函数
客单价计算curent_s_t=round(current_s/current_t,2)
curent_s_t
2.计算上月数据
计算方式是一样的，只不过把日期范围调整一下，代码如下：
from datetime import datetime
last_m=data[(data['成交时间']>=datetime(2020,1,1))&(data['成交时间']<=datetime(2020,1,31))]
last_s=(last_m['销量']*last_m['单价']).sum()
last_t=last_m['订单ID'].drop_duplicates().count()
last_s_t=round(last_s/last_t,2)
3.计算去年同期数据from datetime import datetime
same_m=data[(data['成交时间']>=datetime(2019,2,1))&(data['成交时间']<=datetime(2019,2,28))]
same_s=(same_m['销量']*same_m['单价']).sum()
same_t=same_m['订单ID'].drop_duplicates().count()
same_s_t=round(same_s/same_t,2)
4.将上述数据转成DataFrame表格型数据结构
result=pd.DataFrame([[current_s,last_s,same_s],[current_t,last_t,same_t],[curent_s_t,last_s_t,same_s_t]],columns=['本月','上月同期','去年同期'],index=['销售额','客流量','客单价'])
result
5.增加同比和环比数据result['同比']=result['本月']/result['上月同期']-1
result['环比']=result['本月']/result['去年同期']-1
result
自动化报表的模板基本已经做好了，等下月需要时，只需改一下时间，运行程序即可，省去了大量的人工计算时间
三、可视化展示
用plotly画出柱状图
import plotly.graph_objs as go
columns=['本月','上月同期','去年同期']
fig = go.Figure(data=[
go.Bar(name='销售额',x=columns,y=[current_s,last_s,same_s]),
go.Bar(name='客流量',x=columns,y=[current_t,last_t,same_t]),
go.Bar(name='客单价',x=columns,y=[curent_s_t,last_s_t,same_s_t]),])
fig.update_layout(barmode='group')
fig.show()