用Python自动生成Excel数据报表!

简说Python

共 12456字,需浏览 25分钟

 ·

2021-09-21 12:26


大家好,我是老表~


之前分享了不少关于Python自动化操作Excel的文章,大家都挺喜欢的。


所以今天就带大家来实战一波,使用Python自动化生成数据报表!


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


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


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



下面我们就来看看这个案例吧,水果蔬菜销售报表。(文末有代码和数据获取方法


首先需要你的电脑安装好了Python环境,并且安装好了Python开发工具。

如果你还没有安装,可以参考以下文章:
如果仅用Python来处理数据、爬虫、数据分析或者自动化脚本、机器学习等,建议使用Python基础环境+jupyter即可,安装使用参考Windows/Mac 安装、使用Python环境+jupyter notebook

如果想利用Python进行web项目开发等,建议使用Python基础环境+Pycharm,安装使用参考:Windows下安装、使用Pycharm教程,这下全了 和 Mac下玩转Python-安装&使用Python/PyCharm 。


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



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


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)


结果如下。



一共是有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://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 = (198224180)

# 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([000])
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([017680])

# 不同产品总的收益情况图表名称、字体、字号、粗体、颜色(副标题)
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([000])

# 主标题和副标题的分割线, 粗细、颜色、线型
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([017680])
sht_dashboard.range('L2').api.get_border(which_border=7).line_style.set(-4115)


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



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


# 表格生成函数.
def create_formatted_summary(header_cell, title, df_summary, color):
    """
    Parameters
    ----------
    header_cell : Str
        左上角单元格位置, 放置数据

    title : Str
        当前表格的标题

    df_summary : DataFrame
        表格的数据

    color : Str
        表格填充色
    """


    # 可选择的表格填充色
    colors = {"purple": [(11248160), (16198208)],
              "blue": [(0112192), (155194230)],
              "green": [(017680), (169208142)],
              "yellow": [(2551920), (255217102)]}

    # 设置表格标题的列宽
    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([255255255])
    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) + 22):
        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=(63))
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 appscript)。


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


# Windows
sht_dashboard.range('B2').api.font.name = '黑体'

# Mac
sht_dashboard.range('B2').api.font_object.name.set('黑体')


对于Windows版本的,提供了相关的程序文件。


代码获取方法在公众号回复「excel报表」,即可获取代码及相关数据,还可以扫文末二维码加入学习交流群向我提问


感兴趣的小伙伴,可以动手尝试一下。无需太多的代码,就能轻松的创建一个Excel报表出来~


明天更新运营学Python第二弹和第三弹,就是讲解使用xlwings玩转excel的,本篇大家可以先过一篇,基础学习和练习可以继续看明日文章。


点赞+留言+转发,就是对我最大的支持啦~

图书推荐Python自动化办公:3分钟完成一天工作本书理论知识精练、代码简单、思路清晰、学习资源齐备,适合有一定办公软件使用基础又想进一步提高工作效率的办公人员,如从事行政、人事、营销、财务等职业的人士阅读,也可供Python编程爱好者参考。
本文点赞破100,除了直播带大家复现文章代码,还会在直播过程中送三本《Python自动化办公:3分钟完成一天工作》。


--End--


文章点赞超过100+

我将在个人视频号直播(老表Max)

带大家一起进行项目实战复现



扫码即可加我微信

老表朋友圈经常有赠书/红包福利活动

点击上方卡片关注公众号,回复:1024
领取最新Python学习资源

学习更多:
整理了我开始分享学习笔记到现在超过250篇优质文章,涵盖数据分析、爬虫、机器学习等方面,别再说不知道该从哪开始,实战哪里找了
点赞”就是对博主最大的支持 

浏览 29
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报