用python自动生成excel数据报表！ -凯发app官网

今天带大家来实战一波，使用python自动化生成数据报表！从一条条的数据中，创建出一张数据报表，得出你想要的东西，提高效率。主要使用到pandas、xlwings以及matplotlib这几个库。文章源自玩技e族-https://www.playezu.com/26327.html

先来看一下动态的gif，都是程序自动生成。
文章源自玩技e族-https://www.playezu.com/26327.html

 文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

 文章源自玩技e族-https://www.playezu.com/26327.html

下面我们就来看看这个案例吧，水果蔬菜销售报表。文章源自玩技e族-https://www.playezu.com/26327.html

原始数据如下，主要有水果蔬菜名称、销售日期、销售数量、平均价格、平均成本、总收入、总成本、总利润等。文章源自玩技e族-https://www.playezu.com/26327.html

先导入相关库，使用pandas读取原始数据。文章源自玩技e族-https://www.playezu.com/26327.html

import pandas as pd
import xlwings as xw
import matplotlib.pyplot as plt

# 对齐数据
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)

结果如下。文章源自玩技e族-https://www.playezu.com/26327.html

一共是有1000行的销售数据。文章源自玩技e族-https://www.playezu.com/26327.html

使用xlwings库创建一个excel工作簿，在工作簿中创建一个表，表名为fruit_and_veg_sales，然后将原始数据复制进去。文章源自玩技e族-https://www.playezu.com/26327.html

# 创建原始数据表并复制数据
wb = xw.book()
sht = wb.sheets["sheet1"]
sht.name = "fruit_and_veg_sales"
sht.range("a1").options(index=false).value = d

关于xlwings库的使用，推荐两个文档地址文章源自玩技e族-https://www.playezu.com/26327.html

中文版：文章源自玩技e族-https://www.playezu.com/26327.html

https://www.kancloud.cn/gnefnuy/xlwings-docs/1127455文章源自玩技e族-https://www.playezu.com/26327.html

英文版：文章源自玩技e族-https://www.playezu.com/26327.html

https://docs.xlwings.org/en/stable/index.html文章源自玩技e族-https://www.playezu.com/26327.html

推荐使用中文版，可以降低学习难度...文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

当然关于excel的vba操作，也可以看看微软的文档。文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

地址：文章源自玩技e族-https://www.playezu.com/26327.html

https://docs.microsoft.com/zh-cn/office/vba/api/overview/excel文章源自玩技e族-https://www.playezu.com/26327.html

将原始数据取过来后，再在工作簿中创建一个可视化表，即dashboard表。文章源自玩技e族-https://www.playezu.com/26327.html

# 创建表
wb.sheets.add('dashboard')
sht_dashboard = wb.sheets('dashboard')

现在，我们有了一个包含两个工作表的excel工作簿。fruit_and_veg_sales表有我们的数据，dashboard表则是空白的。文章源自玩技e族-https://www.playezu.com/26327.html

下面使用pandas来处理数据，生成dashboard表的数据信息。文章源自玩技e族-https://www.playezu.com/26327.html

dashboard表的头两个表格，一个是产品的利润表格，一个是产品的销售数量表格。文章源自玩技e族-https://www.playezu.com/26327.html

使用到了pandas的数据透视表函数。文章源自玩技e族-https://www.playezu.com/26327.html

# 总利润透视表
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)

得到数据如下。文章源自玩技e族-https://www.playezu.com/26327.html

稍后会将数据放置到excel的表中去。文章源自玩技e族-https://www.playezu.com/26327.html

下面对月份进行分组汇总，得出每个月的销售情况。文章源自玩技e族-https://www.playezu.com/26327.html

# 查看每列的数据类型
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)

得到结果如下。文章源自玩技e族-https://www.playezu.com/26327.html

这里先对数据进行了查询，发现日期列为object，是不能进行分组汇总的。文章源自玩技e族-https://www.playezu.com/26327.html

所以使用了pd.to_datetime()对其进行了格式转换，而后根据时间进行分组汇总，得到每个月的数据情况。文章源自玩技e族-https://www.playezu.com/26327.html

最后一个groupby将为dashboard表提供第四个数据信息。文章源自玩技e族-https://www.playezu.com/26327.html

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

总收入前8的日期，得到结果如下。文章源自玩技e族-https://www.playezu.com/26327.html

现在我们有了4份数据，可以将其附加到excel中。文章源自玩技e族-https://www.playezu.com/26327.html

# 设置背景颜色, 从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)

先配置一些基本内容，比如文字，颜色背景，边框线等，如下图。文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

使用函数，批量生成四个表格的格式。文章源自玩技e族-https://www.playezu.com/26327.html

# 表格生成函数.
def create_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()
    for num in range(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')

得到结果如下。文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

可以看到，一行行的数据经过python的处理，变为一目了然的表格。文章源自玩技e族-https://www.playezu.com/26327.html

最后再绘制一个matplotlib图表，添加一张logo图片，并保存excel文件。文章源自玩技e族-https://www.playezu.com/26327.html

# 中文显示
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")

此处需设置一下中文显示，否则会显示不了中文，只有一个个方框。文章源自玩技e族-https://www.playezu.com/26327.html

得到最终的水果蔬菜销售报表。文章源自玩技e族-https://www.playezu.com/26327.html

文章源自玩技e族-https://www.playezu.com/26327.html

本文的示例代码，可以在mac excel2016中运行的，与windows还是会有一些区别，api函数的调用(pywin32 or appscript)。文章源自玩技e族-https://www.playezu.com/26327.html

比如表格文字的字体设置。文章源自玩技e族-https://www.playezu.com/26327.html

# windows
sht_dashboard.range('b2').api.font.name = '黑体'

# mac
sht_dashboard.range('b2').api.font_object.name.set('黑体')

关于windows版本的，作者也提供了相关的程序文件，在公众号回复「excel报表」，即可获取代码及相关数据。文章源自玩技e族-https://www.playezu.com/26327.html

感兴趣的小伙伴，可以动手尝试一下。无需太多的代码，就能轻松的创建一个excel报表出来～文章源自玩技e族-https://www.playezu.com/26327.html