如何用Python自动生成Excel数据报表

作者：小F

来源：法纳斯特

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

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

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

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

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

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

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

importpandas aspd

importxlwings asxw

importmatplotlib.pyplot asplt

# 对齐数据

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

关于 xlwings库的使用，小F推荐两个文档地址

中文版：

英文版：

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.22

print(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 = title

summary_title_range.api.font_object.font_size.set( 14)

summary_title_range.row_height = 32.5

# 垂直对齐方式

summary_title_range.api.verticalalignment = xw.constants.HAlign.xlHAlignCenter

summary_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] # Darker color

# 设置表格内容、起始单元格、数据填充、字体大小、粗体、颜色填充

summary_header_range = sht_dashboard.range((row + 1, col + 1))

summary_header_range.value = df_summary

summary_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] # Darker color

sht_dashboard.range((row + 1, col + 1),

(row + len(df_summary), col + len(df_summary.columns) + 1)).autofit

fornum inrange( 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.row

side_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’]=[ ‘Songti SC’]

# 使用Matplotlib绘制可视化图表, 饼图

fig, ax = plt.subplots(figsize=( 6, 3))

pv_total_profit.plot(color= ‘g’, kind= ‘bar’, ax=ax)

# 添加图表到Excel

sht_dashboard.pictures.add(fig, name= ‘ItemsChart’,

left=sht_dashboard.range( “M5”).left,

top=sht_dashboard.range( “M5”).top,

update= True)

# 添加logo到Excel

logo = 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 = 54

logo.height = 54

# 保存Excel文件

wb.save( rf”水果蔬菜销售报表.xlsx”)

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

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

本文的示例代码，可以在Mac+Excel2016中运行的，与Windows还是会有一些区别，API函数的调用(pywin32 or app)。

比如表格文字的字体设置。

# Windows

sht_dashboard.range( ‘B2’).api.font.name = ‘黑体’

# Mac

sht_dashboard.range( ‘B2’).api.font_object.name.set( ‘黑体’)