Python实现Excel报表自动化的实践教程

目录

• 前言
• 场景描述
• 环境准备
• 核心代码
• 1. 读取 Excel 数据
• 2. 数据汇总统计
• 3. 生成报表 Excel
• 4. 添加图表
• 完整自动化脚本
• 进阶技巧
• 数据清洗
• 条件格式
• 定时自动执行
• 总结

每个月底都要花几小时整理 Excel 报表？数据汇总、格式调整、图表制作……这些重复劳动完全可以交给 Python 自动化完成。

本文教你用 Python 自动处理 Excel，从数据读取到报表生成，一键搞定。

假设你每月需要处理这样的销售数据：

sales_data.xlsx
├── 1月：订单表、客户表、产品表
├── 2月：订单表、客户表、产品表
└── …

需要汇总成月度报表，包括：

• 总销售额
• 各产品销量排行
• 客户购买分析
• 可视化图表

# 安装必要的库
pip install pandas openpyxl matplotlib

import pandas as pd

def read_sales_data(file_path):
    """读取销售数据"""
    # 读取多个 sheet
    excel_file = pd.ExcelFile(file_path)
    
    data = {}
    for sheet_name in excel_file.sheet_names:
        data[sheet_name] = pd.read_excel(file_path, sheet_name=sheet_name)
    
    return data

# 使用示例
data = read_sales_data('sales_data.xlsx')
orders = data['订单表']
print(orders.head())

def generate_summary(orders_df):
    """生成销售汇总"""
    summary = {
        '总销售额': orders_df['金额'].sum(),
        '订单数量': len(orders_df),
        '平均订单金额': orders_df['金额'].mean(),
        '最大订单': orders_df['金额'].max(),
        '最小订单': orders_df['金额'].min()
    }
    return summary

# 产品销量排行
def product_ranking(orders_df):
    """产品销量排行"""
    ranking = orders_df.groupby('产品名称').agg({
        '数量': 'sum',
        '金额': 'sum'
    }).sort_values('金额', ascending=False)
    
    return ranking

def create_report(data, output_path):
    """生成报表文件"""
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        # 写入汇总数据
        summary_df = pd.DataFrame([generate_summary(data['订单表'])])
        summary_df.to_excel(writer, sheet_name='汇总', index=False)
        
        # 写入产品排行
        ranking = product_ranking(data['订单表'])
        ranking.to_excel(writer, sheet_name='产品排行')
        
        # 写入原始数据
        for sheet_name, df in data.items():
            df.to_excel(writer, sheet_name=sheet_name, index=False)
    
    print(f"报表已生成：{output_path}")

# 使用
create_report(data, '月度销售报表.xlsx')

import matplotlib.pyplot as plt
from openpyxl.drawing.image import Image as XLImage

def add_charts(excel_path):
    """为报表添加图表"""
    from openpyxl import load_workbook
    
    wb = load_workbook(excel_path)
    
    # 创建图表数据
    ranking = pd.read_excel(excel_path, sheet_name='产品排行')
    
    # 生成柱状图
    plt.figure(figsize=(10, 6))
    ranking['金额'].head(10).plot(kind='bar')
    plt.title('Top 10 产品销售额')
    plt.tight_layout()
    plt.savefig('chart.png')
    plt.close()
    
    # 插入到 Excel
    ws = wb.create_sheet('图表')
    img = XLImage('chart.png')
    ws.add_image(img, 'A1')
    
    wb.save(excel_path)
    print("图表已添加")

import pandas as pd
import matplotlib.pyplot as plt
from openpyxl import load_workbook
from openpyxl.drawing.image import Image as XLImage
import os
from datetime import datetime

def auto_generate_report(input_folder, output_folder):
    """自动生成月度报表"""
    
    # 确保输出文件夹存在
    os.makedirs(output_folder, exist_ok=True)
    
    # 获取当前月份
    current_month = datetime.now().strftime('%Y年%m月')
    output_file = f"{output_folder}/{current_month}销售报表.xlsx"
    
    # 读取所有数据文件
    all_data = []
    for file in os.listdir(input_folder):
        if file.endswith('.xlsx'):
            file_path = os.path.join(input_folder, file)
            data = read_sales_data(file_path)
            all_data.append(data['订单表'])
    
    # 合并数据
    combined_data = pd.concat(all_data, ignore_index=True)
    
    # 生成报表
    create_report({'订单表': combined_data}, output_file)
    
    # 添加图表
    add_charts(output_file)
    
    print(f"✅ 报表生成完成：{output_file}")
    return output_file

# 每月自动执行
if __name__ == '__main__':
    auto_generate_report('./raw_data', './reports')

# 处理缺失值
orders_df['金额'].fillna(0, inplace=True)

# 数据类型转换
orders_df['日期'] = pd.to_datetime(orders_df['日期'])

# 异常值处理
orders_df = orders_df[orders_df['金额'] > 0]

from openpyxl.styles import PatternFill

def highlight_top10(excel_path):
    """高亮显示前10名"""
    wb = load_workbook(excel_path)
    ws = wb['产品排行']
    
    # 红色填充
    red_fill = PatternFill(start_color='FF0000', end_color='FF0000', fill_type='solid')
    
    # 高亮前10行
    for row in range(2, 12):
        for col in range(1, 4):
            ws.cell(row=row, column=col).fill = red_fill
    
    wb.save(excel_path)

# 添加到 crontab，每月1号凌晨执行
0 0 1 * * /usr/bin/python3 /path/to/auto_generate_report.py

用 Python 处理 Excel：

• 读取多个 sheet
• 数据汇总统计
• 自动生成图表
• 格式化美化
• 定时自动执行

从此告别手工报表，每月节省数小时！

到此这篇关于Python实现Excel报表自动化的实践教程的文章就介绍到这了,更多相关Python自动生成Excel报表内容请搜索风君子博客以前的文章或继续浏览下面的相关文章希望大家以后多多支持风君子博客！

您可能感兴趣的文章:

• 利用Python实现Excel报表自动化的操作指南
• Python数据处理之Excel报表自动化生成与分析
• Python 实现自动化Excel报表的步骤
• python实现自动化报表功能(Oracle/plsql/Excel/多线程)
• Python数据报表之Excel操作模块用法分析
• python生成每日报表数据(Excel)并邮件发送的实例
• Python实现导出数据生成excel报表的方法示例