Excel是我们日常工作中最常用的数据处理工具之一。当需要从多个Excel文件中汇总数据、生成统计报表时，手动操作既繁琐又容易出错。本文将介绍如何使用Python自动化实现Excel数据的汇总、统计和报表生成。

核心工具：pandas和openpyxl

安装

pip install pandas openpyxl

基本使用

import pandas as pd

# 读取Excel
df = pd.read_excel('data.xlsx')

# 基本操作
print(df.head())           # 查看前几行
print(df.info())           # 查看数据类型
print(df.describe())       # 统计描述

# 保存Excel
df.to_excel('output.xlsx', index=False)

实战案例1：多文件数据汇总

import pandas as pd
from pathlib import Path
from typing import List, Dict
import glob

class ExcelMerger:
    """多Excel文件数据汇总"""
    
    def __init__(self):
        self.dataframes = []
        self.combined_df = None
    
    def read_excel(self, file_path: str, sheet_name: str = 0) -> pd.DataFrame:
        """读取Excel文件"""
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        df['_source_file'] = Path(file_path).name
        return df
    
    def read_multiple_files(self, file_paths: List[str], sheet_name: str = 0) -> List[pd.DataFrame]:
        """读取多个Excel文件"""
        for file_path in file_paths:
            try:
                df = self.read_excel(file_path, sheet_name)
                self.dataframes.append(df)
                print(f"读取: {Path(file_path).name} ({len(df)} 行)")
            except Exception as e:
                print(f"读取失败 {file_path}: {e}")
        
        return self.dataframes
    
    def read_folder(self, folder_path: str, pattern: str = '*.xlsx', sheet_name: str = 0) -> List[pd.DataFrame]:
        """读取文件夹中所有Excel文件"""
        folder = Path(folder_path)
        file_paths = list(folder.glob(pattern))
        
        print(f"找到 {len(file_paths)} 个文件\n")
        
        return self.read_multiple_files([str(p) for p in file_paths], sheet_name)
    
    def combine_all(self, ignore_index: bool = True) -> pd.DataFrame:
        """合并所有数据"""
        if not self.dataframes:
            print("没有数据可合并")
            return None
        
        self.combined_df = pd.concat(self.dataframes, ignore_index=ignore_index)
        print(f"\n合并完成: {len(self.combined_df)} 行数据")
        
        return self.combined_df
    
    def merge_by_key(self, left_key: str, right_key: str, how: str = 'inner') -> pd.DataFrame:
        """根据关键字合并数据"""
        if len(self.dataframes) < 2:
            print("需要至少两个数据框才能合并")
            return None
        
        result = self.dataframes[0]
        
        for df in self.dataframes[1:]:
            result = pd.merge(result, df, left_on=left_key, right_on=right_key, how=how)
        
        print(f"合并完成: {len(result)} 行")
        return result
    
    def save_combined(self, output_path: str, sheet_name: str = '汇总数据'):
        """保存合并后的数据"""
        if self.combined_df is None:
            print("没有合并数据")
            return
        
        self.combined_df.to_excel(output_path, sheet_name=sheet_name, index=False)
        print(f"已保存: {output_path}")

# 使用示例
if __name__ == '__main__':
    merger = ExcelMerger()
    
    # 方法1: 读取指定文件
    merger.read_multiple_files(['./sales_2024.xlsx', './sales_2025.xlsx'])
    
    # 方法2: 读取文件夹
    merger.read_folder('./sales_data', pattern='*.xlsx')
    
    # 合并数据
    combined = merger.combine_all()
    
    # 保存
    merger.save_combined('./output/merged_sales.xlsx')

实战案例2：数据统计报表生成

import pandas as pd
from pathlib import Path
from datetime import datetime
from typing import Dict, List

class ReportGenerator:
    """统计报表生成器"""
    
    def __init__(self, df: pd.DataFrame):
        self.df = df
        self.report_sections = []
    
    def add_header(self, title: str):
        """添加报表标题"""
        self.report_sections.append({
            'type': 'header',
            'content': title
        })
    
    def add_summary_stats(self, column: str = None) -> pd.DataFrame:
        """添加汇总统计"""
        if column:
            stats = self.df[column].describe()
        else:
            stats = self.df.describe()
        
        self.report_sections.append({
            'type': 'stats',
            'data': stats
        })
        
        return stats
    
    def add_group_stats(self, group_by: str, agg_columns: Dict[str, str]) -> pd.DataFrame:
        """添加分组统计
        
        Args:
            group_by: 分组字段
            agg_columns: 聚合字段和函数，如 {'销售额': 'sum', '数量': 'count'}
        """
        grouped = self.df.groupby(group_by).agg(agg_columns)
        grouped = grouped.round(2)
        
        self.report_sections.append({
            'type': 'group',
            'group_by': group_by,
            'data': grouped
        })
        
        return grouped
    
    def add_top_n(self, column: str, n: int = 10, ascending: bool = False) -> pd.DataFrame:
        """添加Top N统计"""
        top_n = self.df.nlargest(n, column) if not ascending else self.df.nsmallest(n, column)
        
        self.report_sections.append({
            'type': 'top_n',
            'column': column,
            'n': n,
            'data': top_n
        })
        
        return top_n
    
    def generate_excel_report(self, output_path: str):
        """生成Excel报表"""
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            row = 0
            
            # 标题
            row = 0
            
            # 遍历各个部分
            for section in self.report_sections:
                if section['type'] == 'header':
                    # 标题直接写入第一个单元格
                    section['data'].to_excel(writer, sheet_name='报表', startrow=row, index=True)
                    row += len(section['data']) + 3
                elif section['type'] == 'stats':
                    section['data'].to_excel(writer, sheet_name='报表', startrow=row, index=True)
                    row += len(section['data']) + 3
                elif section['type'] == 'group':
                    section['data'].to_excel(writer, sheet_name='报表', 
                                           startrow=row, index=True)
                    print(f"分组统计 - {section['group_by']}")
                    row += len(section['data']) + 3
                elif section['type'] == 'top_n':
                    section['data'].to_excel(writer, sheet_name='报表',
                                           startrow=row, index=False)
                    print(f"Top {section['n']} - {section['column']}")
                    row += len(section['data']) + 3
            
            # 原始数据表
            self.df.to_excel(writer, sheet_name='原始数据', index=False)
        
        print(f"\n报表已生成: {output_path}")
    
    def generate_text_report(self) -> str:
        """生成文本格式报表"""
        report = []
        report.append("=" * 60)
        report.append("数据统计报表")
        report.append(f"生成时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        report.append("=" * 60)
        report.append("")
        
        for section in self.report_sections:
            if section['type'] == 'header':
                report.append(f"\n{section['content']}")
                report.append("-" * 40)
            elif section['type'] == 'stats':
                report.append(section['data'].to_string())
            elif section['type'] == 'group':
                report.append(f"\n按 {section['group_by']} 分组统计:")
                report.append(section['data'].to_string())
            elif section['type'] == 'top_n':
                report.append(f"\nTop {section['n']} - {section['column']}:")
                report.append(section['data'].to_string())
        
        return "\n".join(report)

# 使用示例
if __name__ == '__main__':
    # 读取数据
    df = pd.read_excel('./sales_data.xlsx')
    
    # 创建报表生成器
    generator = ReportGenerator(df)
    
    # 添加报表内容
    generator.add_header('销售数据统计报表')
    generator.add_summary_stats()
    generator.add_group_stats('产品类别', {'销售额': 'sum', '数量': 'count', '单价': 'mean'})
    generator.add_group_stats('销售区域', {'销售额': 'sum', '利润': 'sum'})
    generator.add_top_n('销售额', n=10)
    generator.add_top_n('利润', n=10)
    
    # 生成Excel报表
    generator.generate_excel_report('./output/sales_report.xlsx')
    
    # 生成文本报表
    print(generator.generate_text_report())

实战案例3：透视表报表

import pandas as pd
from typing import List, Dict

class PivotReportGenerator:
    """透视表报表生成器"""
    
    def __init__(self, df: pd.DataFrame):
        self.df = df
    
    def create_pivot_table(self, index: str, columns: str, 
                         values: str, aggfunc: str = 'sum') -> pd.DataFrame:
        """创建透视表"""
        pivot = pd.pivot_table(self.df, 
                              index=index, 
                              columns=columns, 
                              values=values, 
                              aggfunc=aggfunc,
                              fill_value=0)
        
        return pivot
    
    def create_multi_index_pivot(self, index: List[str], columns: str,
                                 values: str, aggfunc: str = 'sum') -> pd.DataFrame:
        """创建多维度透视表"""
        pivot = pd.pivot_table(self.df,
                              index=index,
                              columns=columns,
                              values=values,
                              aggfunc=aggfunc,
                              fill_value=0,
                              margins=True,
                              margins_name='合计')
        
        return pivot
    
    def add_totals(self, df: pd.DataFrame, axis: int = 0) -> pd.DataFrame:
        """添加汇总行/列"""
        if axis == 0:
            df.loc['总计'] = df.sum()
        else:
            df['总计'] = df.sum(axis=1)
        return df
    
    def generate_pivot_report(self, output_path: str):
        """生成透视表报表"""
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # 透视表1: 按产品和地区
            pivot1 = self.create_pivot_table(
                index='产品',
                columns='地区',
                values='销售额',
                aggfunc='sum'
            )
            pivot1.to_excel(writer, sheet_name='产品-地区透视')
            
            # 透视表2: 按月份和产品
            self.df['月份'] = pd.to_datetime(self.df['日期']).dt.month
            pivot2 = self.create_pivot_table(
                index='月份',
                columns='产品',
                values='销售额',
                aggfunc='sum'
            )
            pivot2.to_excel(writer, sheet_name='月份-产品透视')
            
            # 透视表3: 多维度
            pivot3 = self.create_multi_index_pivot(
                index=['地区', '产品类别'],
                columns='季度',
                values='销售额',
                aggfunc='sum'
            )
            pivot3.to_excel(writer, sheet_name='多维度透视')
            
            # 原始数据
            self.df.to_excel(writer, sheet_name='原始数据', index=False)
        
        print(f"透视表报表已生成: {output_path}")
    
    def style_pivot_table(self, df: pd.DataFrame) -> pd.io.formats.style.Styler:
        """美化透视表"""
        return df.style.format('{:,.0f}').background_gradient(
            subset=df.columns[1:], cmap='YlOrRd'
        ).set_caption('数据透视表')

# 使用示例
if __name__ == '__main__':
    df = pd.read_excel('./sales_data.xlsx')
    
    generator = PivotReportGenerator(df)
    generator.generate_pivot_report('./output/pivot_report.xlsx')
    
    # 创建透视表
    pivot = generator.create_pivot_table('产品', '地区', '销售额')
    print(pivot)

实战案例4：条件格式报表

import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import PatternFill, Font, Alignment, Border, Side
from openpyxl.formatting.rule import ColorScaleRule, DataBarRule, CellIsRule

class FormattedReportGenerator:
    """带条件格式的报表生成器"""
    
    def __init__(self, df: pd.DataFrame):
        self.df = df
    
    def generate_formatted_report(self, output_path: str):
        """生成带格式的报表"""
        # 先保存数据
        self.df.to_excel(output_path, sheet_name='数据', index=False, startrow=1)
        
        # 打开工作簿设置格式
        wb = load_workbook(output_path)
        ws = wb['数据']
        
        # 设置标题行
        header_fill = PatternFill(start_color='4472C4', end_color='4472C4', fill_type='solid')
        header_font = Font(color='FFFFFF', bold=True)
        
        for cell in ws[2]:  # 第2行是标题
            cell.fill = header_fill
            cell.font = header_font
            cell.alignment = Alignment(horizontal='center', vertical='center')
        
        # 设置边框
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        for row in ws.iter_rows(min_row=2, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
            for cell in row:
                cell.border = thin_border
        
        # 自动调整列宽
        for column in ws.columns:
            max_length = 0
            column_letter = column[0].column_letter
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column_letter].width = adjusted_width
        
        # 添加条件格式
        # 找到数值列
        for col_idx, col in enumerate(self.df.columns, start=1):
            if self.df[col].dtype in ['int64', 'float64']:
                col_letter = chr(64 + col_idx) if col_idx <= 26 else f'A{chr(64 + col_idx - 26)}'
                max_row = ws.max_row
                
                # 颜色标度
                ws.conditional_formatting.add(
                    f'{col_letter}3:{col_letter}{max_row}',
                    ColorScaleRule(
                        start_type='min', start_color='F8696B',
                        mid_type='percentile', mid_value=50, mid_color='FFEB84',
                        end_type='max', end_color='63BE7B'
                    )
                )
        
        # 添加合计行
        last_row = ws.max_row + 2
        ws.cell(row=last_row, column=1, value='合计')
        ws.cell(row=last_row, column=1).font = Font(bold=True)
        
        for col_idx, col in enumerate(self.df.columns, start=2):
            if self.df[col].dtype in ['int64', 'float64']:
                total = self.df[col].sum()
                ws.cell(row=last_row, column=col_idx, value=total)
                ws.cell(row=last_row, column=col_idx).font = Font(bold=True)
        
        wb.save(output_path)
        print(f"格式化报表已生成: {output_path}")
    
    def generate_summary_sheet(self, output_path: str):
        """生成汇总表"""
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            # 原始数据
            self.df.to_excel(writer, sheet_name='明细数据', index=False)
            
            # 按类别汇总
            summary = self.df.groupby('类别').agg({
                '数量': 'sum',
                '金额': 'sum',
                '利润': 'sum'
            }).round(2)
            summary.to_excel(writer, sheet_name='类别汇总')
            
            # 按月份汇总
            self.df['月份'] = pd.to_datetime(self.df['日期']).dt.to_period('M')
            monthly = self.df.groupby('月份').agg({
                '数量': 'sum',
                '金额': 'sum',
                '利润': 'sum'
            }).round(2)
            monthly.to_excel(writer, sheet_name='月度汇总')

# 使用示例
if __name__ == '__main__':
    df = pd.read_excel('./sales_data.xlsx')
    
    generator = FormattedReportGenerator(df)
    generator.generate_formatted_report('./output/formatted_report.xlsx')
    generator.generate_summary_sheet('./output/summary_report.xlsx')

实战案例5：多Sheet报表导出

import pandas as pd
from pathlib import Path
from datetime import datetime

class MultiSheetReportExporter:
    """多Sheet报表导出器"""
    
    def __init__(self, df: pd.DataFrame):
        self.df = df
        self.sheets = {}
    
    def add_sheet(self, name: str, data: pd.DataFrame):
        """添加Sheet"""
        self.sheets[name] = data
    
    def add_filtered_sheet(self, name: str, filter_column: str, filter_value):
        """添加过滤后的Sheet"""
        filtered = self.df[self.df[filter_column] == filter_value]
        self.sheets[name] = filtered
    
    def add_grouped_sheets(self, group_column: str, agg_dict: Dict[str, str]):
        """为每个分组创建单独的Sheet"""
        for group_value, group_df in self.df.groupby(group_column):
            sheet_name = f"{group_column}_{group_value}"
            if len(sheet_name) > 31:  # Excel Sheet名称限制
                sheet_name = f"Group_{str(group_value)[:28]}"
            
            grouped = group_df.groupby(agg_dict.keys()).agg(agg_dict).reset_index()
            self.add_sheet(sheet_name, grouped)
    
    def add_statistics_sheet(self, name: str = '统计分析'):
        """添加统计分析Sheet"""
        stats_list = []
        
        # 基本统计
        stats_list.append({
            '指标': '记录数',
            '值': len(self.df)
        })
        
        # 数值列统计
        for col in self.df.select_dtypes(include=['number']).columns:
            stats_list.append({'指标': f'{col}_总和', '值': self.df[col].sum()})
            stats_list.append({'指标': f'{col}_平均', '值': self.df[col].mean().round(2)})
            stats_list.append({'指标': f'{col}_最大', '值': self.df[col].max()})
            stats_list.append({'指标': f'{col}_最小', '值': self.df[col].min()})
        
        stats_df = pd.DataFrame(stats_list)
        self.add_sheet(name, stats_df)
    
    def export(self, output_path: str):
        """导出报表"""
        with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
            for sheet_name, data in self.sheets.items():
                data.to_excel(writer, sheet_name=sheet_name[:31], index=False)
        
        print(f"多Sheet报表已导出: {output_path}")
        print(f"共 {len(self.sheets)} 个Sheet")

# 使用示例
if __name__ == '__main__':
    df = pd.read_excel('./sales_data.xlsx')
    
    exporter = MultiSheetReportExporter(df)
    
    # 添加各类Sheet
    exporter.add_sheet('完整数据', df)
    exporter.add_filtered_sheet('大客户数据', '客户类型', 'VIP')
    exporter.add_grouped_sheets('地区', {'销售额': 'sum'})
    exporter.add_statistics_sheet()
    
    # 导出
    exporter.export('./output/multi_sheet_report.xlsx')

实战案例6：定时报表生成器

import pandas as pd
import schedule
import time
from pathlib import Path
from datetime import datetime

class ScheduledReportGenerator:
    """定时报表生成器"""
    
    def __init__(self, data_source: str):
        self.data_source = data_source
        self.report_configs = []
    
    def add_report_config(self, name: str, report_type: str, output_dir: str, **kwargs):
        """添加报表配置"""
        self.report_configs.append({
            'name': name,
            'type': report_type,
            'output_dir': output_dir,
            'params': kwargs,
            'last_run': None
        })
    
    def generate_report(self, config: dict):
        """生成单个报表"""
        # 读取数据
        df = pd.read_excel(self.data_source)
        
        # 根据类型生成
        if config['type'] == 'daily_summary':
            self._generate_daily_summary(df, config)
        elif config['type'] == 'weekly_summary':
            self._generate_weekly_summary(df, config)
        elif config['type'] == 'monthly_summary':
            self._generate_monthly_summary(df, config)
        
        config['last_run'] = datetime.now()
    
    def _generate_daily_summary(self, df: pd.DataFrame, config: dict):
        """生成日报"""
        today = datetime.now().strftime('%Y%m%d')
        output_path = Path(config['output_dir']) / f"日报_{today}.xlsx"
        
        with pd.ExcelWriter(output_path) as writer:
            df.to_excel(writer, sheet_name='今日数据', index=False)
            
            # 今日汇总
            summary = pd.DataFrame({
                '指标': ['销售数量', '销售金额', '利润'],
                '今日': [df['数量'].sum(), df['金额'].sum(), df['利润'].sum()]
            })
            summary.to_excel(writer, sheet_name='汇总', index=False)
        
        print(f"日报已生成: {output_path}")
    
    def _generate_weekly_summary(self, df: pd.DataFrame, config: dict):
        """生成周报"""
        week = datetime.now().strftime('%Y-W%W')
        output_path = Path(config['output_dir']) / f"周报_{week}.xlsx"
        
        df.to_excel(output_path, index=False)
        print(f"周报已生成: {output_path}")
    
    def _generate_monthly_summary(self, df: pd.DataFrame, config: dict):
        """生成月报"""
        month = datetime.now().strftime('%Y%m')
        output_path = Path(config['output_dir']) / f"月报_{month}.xlsx"
        
        with pd.ExcelWriter(output_path) as writer:
            df.to_excel(writer, sheet_name='明细', index=False)
            
            # 按类别汇总
            summary = df.groupby('类别').sum()
            summary.to_excel(writer, sheet_name='汇总')
        
        print(f"月报已生成: {output_path}")
    
    def generate_all_reports(self):
        """生成所有配置的报表"""
        print(f"\n[{datetime.now().strftime('%H:%M:%S')}] 开始生成报表")
        
        for config in self.report_configs:
            try:
                self.generate_report(config)
            except Exception as e:
                print(f"报表生成失败 {config['name']}: {e}")
        
        print(f"[{datetime.now().strftime('%H:%M:%S')}] 报表生成完成\n")
    
    def schedule_reports(self):
        """设置报表定时任务"""
        print("报表定时任务已设置")
        print("- 每日报告: 每天 09:00")
        print("- 周报: 每周一 08:00")
        print("- 月报: 每月1日 07:00")
        
        schedule.every().day.at("09:00").do(self.generate_all_reports)
        
        try:
            while True:
                schedule.run_pending()
                time.sleep(60)
        except KeyboardInterrupt:
            print("\n定时任务已停止")

# 使用示例
if __name__ == '__main__':
    generator = ScheduledReportGenerator('./sales_data.xlsx')
    
    # 添加报表配置
    generator.add_report_config('日报', 'daily_summary', './reports/daily')
    generator.add_report_config('周报', 'weekly_summary', './reports/weekly')
    generator.add_report_config('月报', 'monthly_summary', './reports/monthly')
    
    # 立即生成一次
    generator.generate_all_reports()
    
    # 设置定时任务（取消注释启用）
    # generator.schedule_reports()

实战案例7：图表报表生成

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, PieChart, Reference
from openpyxl.chart.label import DataLabelList

class ChartReportGenerator:
    """图表报表生成器"""
    
    def __init__(self, df: pd.DataFrame):
        self.df = df
    
    def create_bar_chart(self, ws, data_range: str, title: str, 
                       position: str = 'H2'):
        """创建柱状图"""
        chart = BarChart()
        chart.title = title
        chart.style = 10
        chart.x_axis.title = '类别'
        chart.y_axis.title = '数值'
        
        data = Reference(ws, range_string=data_range)
        chart.add_data(data, titles_from_data=True)
        
        ws.add_chart(chart, position)
    
    def create_line_chart(self, ws, data_range: str, title: str,
                        position: str = 'H20'):
        """创建折线图"""
        chart = LineChart()
        chart.title = title
        chart.style = 13
        chart.x_axis.title = '时间'
        chart.y_axis.title = '数值'
        
        data = Reference(ws, range_string=data_range)
        chart.add_data(data, titles_from_data=True)
        
        ws.add_chart(chart, position)
    
    def create_pie_chart(self, ws, data_range: str, title: str,
                        position: str = 'H38'):
        """创建饼图"""
        chart = PieChart()
        chart.title = title
        
        data = Reference(ws, range_string=data_range)
        chart.add_data(data, titles_from_data=True)
        
        # 添加数据标签
        chart.dataLabels = DataLabelList()
        chart.dataLabels.showPercent = True
        chart.dataLabels.showCatName = True
        
        ws.add_chart(chart, position)
    
    def generate_chart_report(self, output_path: str):
        """生成带图表的报表"""
        # 创建工作簿
        wb = Workbook()
        ws = wb.active
        ws.title = '图表数据'
        
        # 准备数据 - 按月份统计
        self.df['月份'] = pd.to_datetime(self.df['日期']).dt.month
        monthly_data = self.df.groupby('月份').agg({
            '数量': 'sum',
            '金额': 'sum',
            '利润': 'sum'
        }).reset_index()
        
        # 写入数据
        for i, row in monthly_data.iterrows():
            ws.cell(row=i+2, column=1, value=f'{int(row["月份"])}月')
            ws.cell(row=i+2, column=2, value=row['数量'])
            ws.cell(row=i+2, column=3, value=row['金额'])
            ws.cell(row=i+2, column=4, value=row['利润'])
        
        # 添加标题行
        ws.cell(row=1, column=1, value='月份')
        ws.cell(row=1, column=2, value='数量')
        ws.cell(row=1, column=3, value='金额')
        ws.cell(row=1, column=4, value='利润')
        
        # 创建图表
        data_range = 'Sheet!A1:D9'  # 1行标题 + 12个月数据
        self.create_bar_chart(ws, data_range, '月度销售柱状图', 'F2')
        self.create_line_chart(ws, data_range, '月度趋势折线图', 'F20')
        self.create_pie_chart(ws, 'Sheet!A1:B9', '月度数量占比', 'F38')
        
        wb.save(output_path)
        print(f"图表报表已生成: {output_path}")

# 使用示例
if __name__ == '__main__':
    df = pd.read_excel('./sales_data.xlsx')
    
    chart_gen = ChartReportGenerator(df)
    chart_gen.generate_chart_report('./output/chart_report.xlsx')

最佳实践

1. 大数据处理

# 分块读取大文件
for chunk in pd.read_excel('large_file.xlsx', chunksize=10000):
    process(chunk)

# 使用数据库处理大数据
import sqlite3
# 将数据导入SQLite进行预处理

2. 内存优化

# 指定数据类型减少内存
dtype_dict = {
    '数量': 'int32',
    '金额': 'float32'
}
df = pd.read_excel('file.xlsx', dtype=dtype_dict)

3. 异常处理

try:
    df = pd.read_excel('file.xlsx')
except Exception as e:
    print(f"读取失败: {e}")
    # 使用openpyxl作为备选方案

总结

Python Excel报表自动化功能非常强大，主要应用场景包括：

1. 多文件汇总：从多个Excel文件汇总数据
2. 统计报表：生成各种统计汇总报表
3. 透视表：创建多维度数据透视表
4. 条件格式：添加颜色和样式增强可读性
5. 多Sheet导出：按分类导出到不同Sheet
6. 定时报表：自动定时生成报表
7. 图表报表：添加可视化图表

通过这些工具和案例，你可以轻松实现各种Excel报表自动化，大大提高工作效率。

希望这篇文章对你有所帮助！