Python 实现自动化 Excel 报表生成与打包

一颗松果

发布时间：2026-10-15| 3人浏览过

原创

本文介绍如何使用 Python 结合 Pandas 和 Xlwings 库，通过 SQL 查询数据库并将数据自动填充至 Excel 模板，实现报表自动化。同时讲解如何利用 PyInstaller 将脚本打包为独立的桌面 EXE 程序，从而无需安装 Python 环境即可运行，大幅提升文员及业务人员的重复性工作效率。

距离上一篇笔记已过去数月，期间并非没有积累，只是稍显懈怠。为了保持工作成长的连续性，决定重新整理并分享这些内容。

近期在处理报表工作时，发现缺乏合适的工具，且不愿投入前端和后端的开发精力。经过权衡，认为 Excel 在可视化方面表现良好，尽管缺乏动态交互功能，但足以满足大部分需求。本文旨在分享如何利用 Python 自动化生成 Excel 报表，从而解放双手并提升工作效率。

总体解决方案

输出报表

此处使用的是测试用的模拟数据。

自动化 Py 脚本

基本思路如下： 1. 准备用于生成报表数据的 SQL 语句。 2. 使用 Pandas 连接数据库并执行 SQL，获取 DataFrame 格式的数据。 3. 利用 Xlwings 直接打开 Excel 文件，将 DataFrame 中的数据填充到预设的单元格中。 4. 保存文件并退出应用程序。

具体代码如下：


import pandas as pd 
import xlwings as xw
import pymssql


# 各品类月同期 
def get_last_year_sale(start_date, end_date):
  """各品类同期销量, 对比19年"""
  sql_01 = f"""
  SELECT 
   品类
   , SUM(数量) AS QTY
  FROM XXX
  WHERE 是否电商 = 1 
   AND 销售时间 BETWEEN DATEADD(YEAR, -2, '{start_date}') AND DATEADD(YEAR, -2, '{end_date}')   
  GROUP BY 品类
  """
  df = pd.read_sql(sql_01, con=con)
  df_xtc = df[df['品类'] == 'A品类'][['品类', 'QTY']]
  df_bbk = df[df['品类'] == 'B品类'][['品类', 'QTY']]
  return df_xtc, df_bbk 
  
def get_anget_sale(start_date, end_date):
    """返回各品类, 各区域的时间段销量"""
    sql = f"""
    SELECT 
     品类
     , AGENT
     , SUM(数量) AS QTY
     , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK
    FROM XXX
    WHERE 是否电商 = 1 
     AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'
    GROUP BY AGENT, 品类
    """
    df = pd.read_sql(sql, con=con)
    df_xtc = df[df['品类'] == 'A品类'][['AGENT', 'QTY']]
    df_bbk = df[df['品类'] == 'B品类'][['AGENT', 'QTY']]
    df_pad = df[df['品类'] == 'C品类'][['AGENT', 'QTY']]

    return df_xtc, df_bbk, df_pad 
  
def get_machine_sale(start_date, end_date):
  """返回各品类, 各区域的时间段销量"""
  sql = f"""
  SELECT 
   品类
   , 机型
   , SUM(数量) AS QTY
   , ROW_NUMBER()OVER(PARTITION BY 品类 ORDER BY SUM(数量) DESC) MY_RANK
  FROM V_REALSALE
  WHERE 是否电商 = 1 
   AND 销售时间 BETWEEN '{start_date}' AND '{end_date}'
  GROUP BY 机型, 品类
  """
  df = pd.read_sql(sql, con=con)
  df_xtc = df[df['品类'] == 'A品类'][['机型', 'QTY']]
  df_bbk = df[df['品类'] == 'B品类'][['机型', 'QTY']]

  return df_xtc, df_bbk 


# main 
con = pymssql.connect('xxxxx', 'sxxx', 'xxxxxx', 'xxxxx')

# 基础配置: 根据用户输入当前日期, 输出当月, 当季度第一天 
print("欢迎哦, 此小程序专门为XX看板做数据自动更新呢~")
print()

today = input("请输入截止日期(昨天), 形如: 2021/5/20 按回车结束:  ")

if len(today.split('/')) != 3:
  raise "日期格式输入错误!!, 请按照形如 '2021/5/20'的格式重新输入"
else:
  m_cur = today.split('/')[1]
  m_first_day = '2021/' + m_cur + '/1'

# 季度第一天 
if m_cur in ('1', '01', '2', '02', '3', '03'):
  q_time_start = '2021/1/1'
  
elif m_cur in ('4', '04', '5', '05', '6', '06'):
  q_time_start = '2021/4/1'
  
elif m_cur in ('7', '07', '8', '08', '9', '09'):
  q_time_start = '2021/7/1'
else:
  q_time_start = '2021/10/1'

print()
print("正在开始更新....")
print("提示, 接下看到闪退, 是正常现象, 就程序模拟人去打开文件, 填充数据, 不要紧张哦~~~")

# 去年月, 季度同期 
df_mm_xtc, df_mm_bbk = get_last_year_sale(m_first_day, today)
df_qq_xtc, df_qq_bbk = get_last_year_sale(q_time_start, today)

# 当月各地区累积销量
df_m_xtc, df_m_bbk, df_m_pad = get_anget_sale(m_first_day, today)

# 各地区当季度销量 
df_q_xtc, df_q_bbk, df_q_pad = get_anget_sale(q_time_start, today)

# 各机型当季度销量 
df_q_type_xtc, df_q_type_bbk = get_machine_sale(q_time_start, today) 
# 过滤掉 销量为0的型号 
df_q_type_xtc = df_q_type_xtc[df_q_type_xtc.QTY > 0]
df_q_type_xtc.replace('Z6áÛ·å°æ', 'Z6巅峰版', inplace=True)

df_q_type_bbk = df_q_type_bbk[df_q_type_bbk.QTY > 0]

# 打开excel 模板 等待数据填充 
app = xw.App(visible=True, add_book=False)

app.display_alerts = False  # 关闭一些提示信息，可以加快运行速度。 默认为 True。
app.screen_updating = True

wb = app.books.open("XXX_全品类_看板.xlsx")
data_sht = wb.sheets['数据']

# 19年当月同期销量
data_sht.range('B9').value = df_mm_xtc.values
data_sht.range('G9').value = df_mm_bbk.values

# 当季度同比
data_sht.range('B10').value = df_qq_xtc.values
data_sht.range('G10').value = df_qq_bbk.values

# 填充各品类当月销量, 注意单元格是写死的哦
data_sht.range('I72').value = df_m_xtc.values
data_sht.range('T72').value = df_m_bbk.values
data_sht.range('AE72').value = df_m_pad.values

# 填充当季度销量, 同理是写死的
data_sht.range('A54').value = df_q_xtc.values
data_sht.range('F54').value = df_q_bbk.values
data_sht.range('K54').value = df_q_pad.values

# 填充当季度各型号, 同理是写死的
data_sht.range('A21').value = df_q_type_xtc.values
data_sht.range('F21').value = df_q_type_bbk.values

wb.save()
app.quit()

print()
print("~~更新结束了哦~~")
print()
input("请按任意键退出~~")
print()
print('BYE~~ 人生若只如初见呢~~')