Python脚本如何生成Excel报表

wen 实用脚本 2026-07-11 3

Python脚本如何生成Excel报表：自动化数据处理与可视化全攻略

目录导读

为什么选择Python进行Excel报表生成
环境搭建与核心库介绍
基础操作：用Python创建和写入Excel
进阶技巧：样式美化与图表嵌入
实战案例：从数据库到报表的自动化流程
常见问题与避坑指南
Q&A问答环节

为什么选择Python进行Excel报表生成

在企业日常运营中,Excel报表堪称数据沟通的“通用语言”，手动制作报表存在三个致命痛点：重复性劳动耗费精力、数据更新容易出错、跨部门协作格式混乱，Python脚本恰好能完美解决这些问题。

Python脚本如何生成Excel报表

根据Stack Overflow 2023年开发者调查，Python在数据处理领域的采用率高达62%，远超Excel VBA，究其原因，Python拥有pandas（数据清洗）、openpyxl（Excel交互）、xlwings（Excel自动化）等成熟工具链，能实现从数据抓取到可视化呈现的全闭环。

更重要的是,Python脚本可以：

定时自动运行：结合cron或Windows任务计划程序
动态数据源：直接连接数据库、API或CSV文件
版本控制：用git管理报表逻辑的更新历史
多格式输出：一键生成XLSX、PDF、图片版报表

环境搭建与核心库介绍

1 必备Python库

pip install pandas openpyxl xlsxwriter matplotlib

各库定位：

pandas：数据清洗与聚合（类似于Excel的数据透视表）
openpyxl：读写.xlsx文件，支持样式和公式
xlsxwriter：高性能写入，适合大数据量且支持图表
matplotlib：生成图表后嵌入Excel

2 开发环境选择

推荐使用Jupyter Notebook进行原型开发，因为它能：

逐步预览数据处理结果
实时查看DataFrame形状
方便调试图表参数

3 快速验证环境

import pandas as pd
import openpyxl
print(f"pandas版本: {pd.__version__}")
print(f"openpyxl版本: {openpyxl.__version__}")

若输出版本号,表示环境就绪。

注意：不要同时安装openpyxl和xlrd（旧版），两者对.xlsx的解析有冲突。

基础操作：用Python创建和写入Excel

1 创建一个空白表格

from openpyxl import Workbook
wb = Workbook()
ws = wb.active= "销售报表"
ws['A1'] = "日期"
ws['B1'] = "销售额"
wb.save('simple_report.xlsx')

2 用pandas快速生成报表

import pandas as pd
data = {
    '产品': ['A', 'B', 'C'],
    '第一季度': [100, 200, 150],
    '第二季度': [120, 180, 160]
}
df = pd.DataFrame(data)
df.to_excel('product_report.xlsx', index=False, sheet_name='季度销售')

3 追加数据到已有文件

from openpyxl import load_workbook
wb = load_workbook('report.xlsx')
ws = wb['数据']
ws.append(['2024-01-15', '新产品X', 35000])
wb.save('report.xlsx')

关键点：pd.to_excel()默认覆盖文件，要追加需用openpyxl显式加载。

进阶技巧：样式美化与图表嵌入

1 单元格样式设置

from openpyxl.styles import Font, PatternFill, Alignment, Border
header_font = Font(name='微软雅黑', bold=True, color='FFFFFF')
header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
center_align = Alignment(horizontal='center', vertical='center')
for cell in ws[1]:  # 第一行
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align

2 插入数据透视表效果

pivot = df.pivot_table(
    values='销售额',
    index='区域',
    columns='月份',
    aggfunc='sum',
    fill_value=0
)
pivot.to_excel('pivot_report.xlsx')

3 在Excel中嵌入图表

from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.type = "col"
chart.title = "月度销售趋势"
chart.y_axis.title = '金额（万元）'
chart.x_axis.title = '月份'
data = Reference(ws, min_col=2, min_row=1, max_col=5, max_row=12)
cats = Reference(ws, min_col=1, min_row=2, max_row=12)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
ws.add_chart(chart, "E5")

诀窍：先用matplotlib生成静态图片，再用openpyxl插入图片，兼容性更好。

实战案例：从数据库到报表的自动化流程

1 场景描述

某电商公司需要每日生成“运营日报”，包含：订单量、GMV、退款率、热销品类排名。

2 完整脚本框架

import pandas as pd
from datetime import date
import openpyxl
from openpyxl.chart import LineChart, Reference
import mysql.connector
# 1. 从MySQL提取数据
conn = mysql.connector.connect(
    host='localhost',
    user='analyst',
    password='p@ssword',
    database='ecommerce'
)
query = """
    SELECT DATE(created_at) as day, 
           COUNT(id) as orders,
           SUM(amount) as gmv,
           SUM(CASE WHEN refund=1 THEN amount ELSE 0 END)/SUM(amount) as refund_rate
    FROM orders
    WHERE created_at >= CURDATE() - INTERVAL 30 DAY
    GROUP BY day
"""
df = pd.read_sql_query(query, conn)
conn.close()
# 2. 数据清洗与计算
df['退款率'] = df['refund_rate'].apply(lambda x: f"{x:.1%}")
# 3. 输出Excel
output_path = f'每日运营报表_{date.today()}.xlsx'
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='日数据', index=False)
    # 生成趋势图
    workbook = writer.book
    ws = workbook['日数据']
    chart = LineChart()
    chart.title = "近30天GMV趋势"
    data_ref = Reference(ws, min_col=3, min_row=1, max_row=len(df)+1)
    cat_ref = Reference(ws, min_col=1, min_row=2, max_row=len(df)+1)
    chart.add_data(data_ref, titles_from_data=True)
    chart.set_categories(cat_ref)
    ws.add_chart(chart, "G2")
print(f"报表已生成：{output_path}")

3 定时执行方案（Linux crontab）

0 8 * * * /usr/bin/python3 /path/to/daily_report.py

每天8点自动生成前一日报表。

常见问题与避坑指南

1 数据量大导致内存溢出

解决方案：使用xlsxwriter引擎分批写入，或设置chunksize参数

for chunk in pd.read_sql(query, conn, chunksize=10000):
  chunk.to_excel(writer, sheet_name='data', startrow=row_count, header=False)

2 中文字符乱码

根本原因：Excel默认GBK编码，但openpyxl用UTF-8
修复方法：df.to_excel(encoding='utf-8') 或设置字体支持中文

3 公式无法自动计算

解决方法：写入公式后需保存并重新打开Excel，或使用data_only=True参数读取

4 图表显示异常

调试技巧：先用chart.to_file('chart.png')导出预览，再调整数据范围

Q&A问答环节

Q1：Python生成Excel和VBA相比，优势在哪里？ A：Python具备跨平台能力（macOS/Linux/Windows均可运行），可无缝对接现代数据栈（如AWS S3、Snowflake），且生态更丰富，VBA仅限Windows且代码难以版本控制。

Q2：如何一次性合并多个Excel报表？ A：使用pd.concat()：combined = pd.concat([pd.read_excel(f) for f in file_list], ignore_index=True)，再用to_excel输出。

Q3：报表中的敏感数据如何脱敏？ A：在写入前用pandas的apply函数处理：对手机号、邮箱等字段执行lambda x: x[:3] + '****' + x[-4:]。

Q4：能否在报表中生成雷达图、饼图以外的复杂图表？ A：openpyxl支持14种图表类型，更复杂的可视化建议先用Plotly生成HTML，再嵌入Excel中的Web Viewer控件。

Q5：报表脚本部署到服务器后，怎么处理错误日志？ A：使用Python的logging模块，将错误写入report_error.log，并设置邮件告警：

import logging
logging.basicConfig(filename='report.log', level=logging.ERROR)

通过上述方法,你已经掌握了从零构建Python Excel报表生成系统的完整技能，无论是日常的数据快照，还是复杂的可视化分析，Python都能在几分钟内将原始数据转化为专业报表，建议从简单需求开始尝试（比如把CSV转Excel），逐步加入样式、图表和自动化调度，最终打造属于你自己的智能报表工厂。