自动生成表格的软件(输入数据自动生成表格推荐)-众学网

今天带大家来实战一波，使用Python自动化生成数据报表！

从一条条的数据中，创建出一张数据报表，得出你想要的东西，提高效率。

主要使用到pandas、xlwings以及matplotlib这几个库。

先来看一下动态的GIF，都是程序自动生成。

下面我们就来看看这个案例吧，水果蔬菜销售报表。

原始数据如下，主要有水果蔬菜名称、销售日期、销售数量、平均价格、平均成本、总收入、总成本、总利润等。

先导入相关库，使用pandas读取原始数据。

importpandasaspdimportxlwingsasxwimportmatplotlib.pyplotasplt#对齐数据pd.set_option(‘display.unicode.ambiguous_as_wide’,True)pd.set_option(‘display.unicode.east_asian_width’,True)#读取数据df=pd.read_csv(r”fruit_and_veg_sales.csv”)print(df)

结果如下。

一共是有1000行的销售数据。

使用xlwings库创建一个Excel工作簿，在工作簿中创建一个表，表名为fruit_and_veg_sales，然后将原始数据复制进去。

#创建原始数据表并复制数据wb=xw.Book()sht=wb.sheets[“Sheet1”]sht.name=”fruit_and_veg_sales”sht.range(“A1”).options(index=False).value=d

中文版：

https://www.kancloud.cn/gnefnuy/xlwings-docs/1127455

英文版：

https://docs.xlwings.org/en/stable/index.html

推荐使用中文版，可以降低学习难度…

当然关于Excel的VBA操作，也可以看看微软的文档。

https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel

将原始数据取过来后，再在工作簿中创建一个可视化表，即Dashboard表。

#创建表wb.sheets.add(‘Dashboard’)sht_dashboard=wb.sheets(‘Dashboard’)

现在，我们有了一个包含两个工作表的Excel工作簿。fruit_and_veg_sales表有我们的数据，Dashboard表则是空白的。

下面使用pandas来处理数据，生成Dashboard表的数据信息。

DashBoard表的头两个表格，一个是产品的利润表格，一个是产品的销售数量表格。

使用到了pandas的数据透视表函数。

#总利润透视表pv_total_profit=pd.pivot_table(df,index=’类别’,values=’总利润(美元)’,aggfunc=’sum’)print(pv_total_profit)#销售数量透视表pv_quantity_sold=pd.pivot_table(df,index=’类别’,values=’销售数量’,aggfunc=’sum’)print(pv_quantity_sold)

得到数据如下。

稍后会将数据放置到Excel的表中去。

下面对月份进行分组汇总，得出每个月的销售情况。

#查看每列的数据类型print(df.dtypes)df[“销售日期”]=pd.to_datetime(df[“销售日期”])#每日的数据情况gb_date_sold=df.groupby(df[“销售日期”].dt.to_period(‘m’)).sum()[[“销售数量”,’总收入(美元)’,’总成本(美元)’,”总利润(美元)”]]gb_date_sold.index=gb_date_sold.index.to_series().astype(str)print(gb_date_sold)

得到结果如下。

这里先对数据进行了查询，发现日期列为object，是不能进行分组汇总的。

所以使用了pd.to_datetime()对其进行了格式转换，而后根据时间进行分组汇总，得到每个月的数据情况。

最后一个groupby将为Dashboard表提供第四个数据信息。

#总收入前8的日期数据gb_top_revenue=(df.groupby(df[“销售日期”]).sum().sort_values(‘总收入(美元)’,ascending=False).head(8))[[“销售数量”,’总收入(美元)’,’总成本(美元)’,”总利润(美元)”]]print(gb_top_revenue)

总收入前8的日期，得到结果如下。

现在我们有了4份数据，可以将其附加到Excel中。

#设置背景颜色,从A1单元格到Z1000单元格的矩形区域sht_dashboard.range(‘A1:Z1000’).color=(198,224,180)#A、B列的列宽sht_dashboard.range(‘A:B’).column_width=2.22print(sht_dashboard.range(‘B2’).api.font_object.properties.get())#B2单元格,文字内容、字体、字号、粗体、颜色、行高(主标题)sht_dashboard.range(‘B2’).value=’销售数据报表’sht_dashboard.range(‘B2’).api.font_object.name.set(‘黑体’)sht_dashboard.range(‘B2’).api.font_object.font_size.set(48)sht_dashboard.range(‘B2’).api.font_object.bold.set(True)sht_dashboard.range(‘B2’).api.font_object.color.set([0,0,0])sht_dashboard.range(‘B2’).row_height=61.2#B2单元格到W2单元格的矩形区域,下边框的粗细及颜色sht_dashboard.range(‘B2:W2’).api.get_border(which_border=9).weight.set(4)sht_dashboard.range(‘B2:W2’).api.get_border(which_border=9).color.set([0,176,80])#不同产品总的收益情况图表名称、字体、字号、粗体、颜色(副标题)sht_dashboard.range(‘M2’).value=’每种产品的收益情况’sht_dashboard.range(‘M2’).api.font_object.name.set(‘黑体’)sht_dashboard.range(‘M2’).api.font_object.font_size.set(20)sht_dashboard.range(‘M2’).api.font_object.bold.set(True)sht_dashboard.range(‘M2’).api.font_object.color.set([0,0,0])#主标题和副标题的分割线,粗细、颜色、线型sht_dashboard.range(‘L2’).api.get_border(which_border=7).weight.set(3)sht_dashboard.range(‘L2’).api.get_border(which_border=7).color.set([0,176,80])sht_dashboard.range(‘L2’).api.get_border(which_border=7).line_style.set(-4115)

先配置一些基本内容，比如文字，颜色背景，边框线等，如下图。

使用函数，批量生成四个表格的格式。

#表格生成函数.defcreate_formatted_summary(header_cell,title,df_summary,color):”””Parameters———-header_cell:Str左上角单元格位置,放置数据title:Str当前表格的标题df_summary:DataFrame表格的数据color:Str表格填充色”””#可选择的表格填充色colors={“purple”:[(112,48,160),(161,98,208)],”blue”:[(0,112,192),(155,194,230)],”green”:[(0,176,80),(169,208,142)],”yellow”:[(255,192,0),(255,217,102)]}#设置表格标题的列宽sht_dashboard.range(header_cell).column_width=1.5#获取单元格的行列数row,col=sht_dashboard.range(header_cell).row,sht_dashboard.range(header_cell).column#设置表格的标题及相关信息, 如：字号、行高、向左居中对齐、颜色、粗体、表格的背景颜色等summary_title_range=sht_dashboard.range((row,col))summary_title_range.value=titlesummary_title_range.api.font_object.font_size.set(14)summary_title_range.row_height=32.5#垂直对齐方式summary_title_range.api.verticalalignment=xw.constants.HAlign.xlHAlignCentersummary_title_range.api.font_object.color.set([255,255,255])summary_title_range.api.font_object.bold.set(True)sht_dashboard.range((row,col),(row,col len(df_summary.columns) 1)).color=colors[color][0]#Darkercolor#设置表格内容、起始单元格、数据填充、字体大小、粗体、颜色填充summary_header_range=sht_dashboard.range((row 1,col 1))summary_header_range.value=df_summarysummary_header_range=summary_header_range.expand(‘right’)summary_header_range.api.font_object.font_size.set(11)summary_header_range.api.font_object.bold.set(True)sht_dashboard.range((row 1,col),(row 1,col len(df_summary.columns) 1)).color=colors[color][1]#Darkercolorsht_dashboard.range((row 1,col 1),(row len(df_summary),col len(df_summary.columns) 1)).autofit()fornuminrange(1,len(df_summary) 2,2):sht_dashboard.range((row num,col),(row num,col len(df_summary.columns) 1)).color=colors[color][1]#找到表格的最后一行last_row=sht_dashboard.range((row 1,col 1)).expand(‘down’).last_cell.rowside_border_range=sht_dashboard.range((row 1,col),(last_row,col))#给表格左边添加带颜色的边框side_border_range.api.get_border(which_border=7).weight.set(3)side_border_range.api.get_border(which_border=7).color.set(colors[color][1])side_border_range.api.get_border(which_border=7).line_style.set(-4115)#生成4个表格create_formatted_summary(‘B5′,’每种产品的收益情况’,pv_total_profit,’green’)create_formatted_summary(‘B17′,’每种产品的售出情况’,pv_quantity_sold,’purple’)create_formatted_summary(‘F17′,’每月的销售情况’,gb_date_sold,’blue’)create_formatted_summary(‘F5′,’每日总收入排名Top8′,gb_top_revenue,’yellow’)

得到结果如下。

可以看到，一行行的数据经过Python的处理，变为一目了然的表格。

最后再绘制一个matplotlib图表，添加一张logo图片，并保存Excel文件。

#中文显示plt.rcParams[‘font.sans-serif’]=[‘SongtiSC’]#使用Matplotlib绘制可视化图表,饼图fig,ax=plt.subplots(figsize=(6,3))pv_total_profit.plot(color=’g’,kind=’bar’,ax=ax)#添加图表到Excelsht_dashboard.pictures.add(fig,name=’ItemsChart’,left=sht_dashboard.range(“M5”).left,top=sht_dashboard.range(“M5″).top,update=True)#添加logo到Excellogo=sht_dashboard.pictures.add(image=”pie_logo.png”,name=’PC_3′,left=sht_dashboard.range(“J2”).left,top=sht_dashboard.range(“J2″).top 5,update=True)#设置logo的大小logo.width=54logo.height=54#保存Excel文件wb.save(rf”水果蔬菜销售报表.xlsx”)

此处需设置一下中文显示，否则会显示不了中文，只有一个个方框。

得到最终的水果蔬菜销售报表。