用Python自动生成Excel报表

2020-12-15 21:15 大数据DT

导读:在日常工作中,可能会有一些重复无聊的任务,比如说,从 Excel 或数据库中收集一些数据,设置相应的数据格式并做成报表。


类似这种重复无聊的任务,我们完全可以交给 Python 去自动完成,只要第一次把 Python 代码写好,以后就可以一键完成,省时省力,还不容易出错,这是提升工作质量和工作效率的利器,让我们能够腾出时间和精力,去做更有价值的事情。


作者 / 来源:林骥(ID:linjiwx)



01 安装和导入模块


以 Python 中的 openpyxl 模块为例,它能够读取和修改 Excel 文件,如果你还没有安装,可以通过以下命令进行安装:


pip install openpyxl


要测试 openpyxl 是否正确安装,可以在 Jupyter Lab 中运行以下代码:


# 导入库
import openpyxl
# 查看版本
openpyxl.__version__


如果该模块正确安装,那么会输出版本号,假如你在使用过程中遇到问题,可以查阅官方文档。



02 读取和处理数据


为了演示用 Python 自动生成 Excel 报表,我从网上找了一个数据集,是一家跨国公司的 54 万多行在线零售业务的交易数据,你可以进入公众号「林骥」的后台,回复「零售」两个字,获取该数据集的完整下载链接。


把这个数据文件保存到代码上级目录的 data 文件夹,然后用 Pandas 读取它:


# 读取数据
import pandas as pd
df = pd.read_excel('../data/Online Retail.xlsx')

df



其中每一列代表的含义如下:


  • InvoiceNo:发票编号

  • StockCode:产品代码

  • Description:产品名称

  • Quantity:产品数量

  • InvoiceDate:开票时间

  • UnitPrice:产品单价

  • CustomerID:客户编号

  • Country:国家名称


为了统计每天的销售额,我们先在数据中增加两列:日期和销售额,然后用函数实现汇总:


# 从时间列中提取日期
df['日期'] = df.InvoiceDate.dt.to_period('D').astype(str)

# 计算销售额
df['销售额'] = df.Quantity * df.UnitPrice

# 汇总每天的销售额
df_daily = pd.DataFrame(df.groupby('日期')['销售额'].agg('sum')).reset_index()

df_daily


图片



03 设置和保存报表


接下来,我们对表格进行相应的设置,包括:重命名工作表、把数据写入工作表、自定义标题和表格边框样式、设置行高和列宽、不显示网格线、冻结窗格、自动筛选、设置日期和数字格式等等。


from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font, Color, NamedStyle, Border, Side, PatternFill, Alignment, numbers

# 创建工作簿
wb = openpyxl.Workbook()

# 激活工作表
ws = wb.active

# 重命名工作表
ws.title = '每日销售额'

# 把数据写入工作表
for row in dataframe_to_rows(df_daily, index=False, header=True):
    ws.append(row)

# 创建自定义的标题样式
mytitle = NamedStyle(name='mytitle')
mytitle.font = Font(bold=True, size=11, color='FFFFFF')
bd = Side(style='thin', color='A6A6A6')
mytitle.border = Border(left=bd, top=bd, right=bd, bottom=bd)
mytitle.fill = PatternFill('solid', fgColor='00589F')
mytitle.alignment = Alignment(horizontal='left', vertical='center')
wb.add_named_style(mytitle)

# 创建自定义表格边框样式
myborder = NamedStyle(name='myborder')
myborder.font = Font(bold=False, size=11, color='000000')
bd = Side(style='thin', color='A6A6A6')
myborder.border = Border(left=bd, top=bd, right=bd, bottom=bd)
myborder.alignment = Alignment(vertical='center')
wb.add_named_style(myborder)

# 应用标题样式
for cell in ws[1]:
    cell.style = mytitle

# 对表格区域加边框
from openpyxl.utils import get_column_letter, column_index_from_string
table_range = ws['A2:' + get_column_letter(ws.max_column) + str(ws.max_row)]
for row in table_range:
    for cell in row:
        cell.style = myborder

# 设置行高和列宽
ws.row_dimensions[1].height = 26
ws.column_dimensions['A'].width = 15
ws.column_dimensions['B'].width = 12

# 设置不显示网格线
ws.views.sheetView[0].showGridLines = False

# 冻结窗格
ws.freeze_panes = 'A3'

# 自动筛选
ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + str(ws.max_row)

# 设置对齐格式
for cell in ws['A']:
    cell.alignment = Alignment(horizontal='center', vertical='center')

# 设置日期格式
for cell in ws['A']:
    cell.number_format = numbers.FORMAT_DATE_YYYYMMDD2

# 设置数字格式
for cell in ws['B']:
    cell.number_format = numbers.BUILTIN_FORMATS[3]


最后,保存自动生成的 Excel 报表文件:


# 保存为新的表格
wb.save('../data/每日销售报表.xlsx')


打开这个新生成的 Excel 报表文件,其中的内容如下:




小结


本文介绍了用 Python 自动生成 Excel 报表的一种方法,从 openpyxl 模块的安装和导入,到读取和处理数据,再到设置和保存报表,只要你把数据源放在合适的位置,就能在 Jupyter Lab 中一键运行,自动生成相应的报表。


你完全可以根据自己的实际情况,修改数据源和报表格式等设置,充分发挥自己的创意,生成个性化定制的报表。



划重点👇

大数据DT推荐搜索
Python
pandas
Excel
报表


干货直达👇


更多精彩👇

在公众号对话框输入以下关键词
查看更多优质内容!

PPT | 读书 | 书单 | 硬核 | 干货 | 讲明白 | 神操作
大数据 | 云计算 | 数据库 | Python | 可视化
AI | 人工智能 | 机器学习 | 深度学习 | NLP
5G | 中台 | 用户画像 1024 | 数学 | 算法 数字孪生

据统计,99%的大咖都完成了这个神操作
👇


本文章转载自公众号:hzdashuju

首页 - 大数据 相关的更多文章: