Python自动化办公实战：Excel数据批量处理与报告生成 | Python数据分析

引言：告别重复性手工劳动

在日常办公中，我们经常需要处理大量的Excel数据，并进行重复性的整理、分析和报告生成工作。使用Python可以自动化这些繁琐的任务，大大提高工作效率。本文将带你一步步实现一个完整的自动化数据处理项目。

项目概述

我们将创建一个Python脚本，实现以下功能：

• 读取多个Excel文件中的数据
• 清洗和整理数据
• 进行数据分析与计算
• 生成可视化图表
• 自动创建PDF报告

环境准备与库安装

首先，我们需要安装必要的Python库：

pip install pandas openpyxl matplotlib reportlab

这些库的作用分别是：

• pandas: 数据处理和分析
• openpyxl: 读写Excel文件
• matplotlib: 数据可视化
• reportlab: 生成PDF报告

实战案例：销售数据分析

假设我们有一个文件夹包含多个分公司的销售数据Excel文件，我们需要将这些数据整合分析，并生成季度报告。

1. 项目结构

sales_report/
├── data/
│   ├── branch1_sales.xlsx
│   ├── branch2_sales.xlsx
│   └── branch3_sales.xlsx
├── output/
│   └── (生成的报告和图表将在这里)
└── sales_analysis.py

2. 数据格式示例

每个Excel文件包含以下列：

• 日期 (date)
• 产品名称 (product)
• 销售数量 (quantity)
• 单价 (price)
• 销售员 (salesperson)

代码实现

1. 导入必要的库

import pandas as pd
import matplotlib.pyplot as plt
import os
from datetime import datetime
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer, Image
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib import colors

2. 读取和合并数据

def load_sales_data(data_folder):
    """
    从指定文件夹加载所有Excel销售数据文件并合并
    """
    all_data = []
    
    # 遍历文件夹中的所有Excel文件
    for file in os.listdir(data_folder):
        if file.endswith('.xlsx'):
            file_path = os.path.join(data_folder, file)
            # 读取Excel文件
            df = pd.read_excel(file_path)
            # 添加分公司名列
            df['branch'] = file.split('_')[0]
            all_data.append(df)
    
    # 合并所有数据
    combined_data = pd.concat(all_data, ignore_index=True)
    return combined_data

3. 数据清洗与处理

def clean_and_process_data(df):
    """
    清洗和处理销售数据
    """
    # 删除空值
    df = df.dropna()
    
    # 确保日期列是日期格式
    df['date'] = pd.to_datetime(df['date'])
    
    # 计算总销售额
    df['total_sales'] = df['quantity'] * df['price']
    
    # 提取月份和季度信息
    df['month'] = df['date'].dt.month
    df['quarter'] = (df['month'] - 1) // 3 + 1
    
    return df

4. 数据分析

def analyze_sales_data(df):
    """
    分析销售数据并返回关键指标
    """
    # 总销售额
    total_sales = df['total_sales'].sum()
    
    # 各分公司销售额
    branch_sales = df.groupby('branch')['total_sales'].sum()
    
    # 各产品销售额
    product_sales = df.groupby('product')['total_sales'].sum().sort_values(ascending=False)
    
    # 季度销售额
    quarterly_sales = df.groupby('quarter')['total_sales'].sum()
    
    # 最佳销售员
    top_salespeople = df.groupby('salesperson')['total_sales'].sum().sort_values(ascending=False).head(5)
    
    return {
        'total_sales': total_sales,
        'branch_sales': branch_sales,
        'product_sales': product_sales,
        'quarterly_sales': quarterly_sales,
        'top_salespeople': top_salespeople
    }

5. 生成可视化图表

def create_charts(analysis_results, output_folder):
    """
    创建可视化图表并保存
    """
    # 设置中文字体支持
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
    
    # 1. 各分公司销售额柱状图
    plt.figure(figsize=(10, 6))
    analysis_results['branch_sales'].plot(kind='bar')
    plt.title('各分公司销售额对比')
    plt.xlabel('分公司')
    plt.ylabel('销售额 (元)')
    plt.xticks(rotation=45)
    plt.tight_layout()
    plt.savefig(os.path.join(output_folder, 'branch_sales.png'))
    plt.close()
    
    # 2. 产品销售额饼图
    plt.figure(figsize=(8, 8))
    analysis_results['product_sales'].head(5).plot(kind='pie', autopct='%1.1f%%')
    plt.title('热销产品销售额占比 (前5名)')
    plt.ylabel('')
    plt.savefig(os.path.join(output_folder, 'product_sales.png'))
    plt.close()
    
    # 3. 季度销售额折线图
    plt.figure(figsize=(10, 6))
    analysis_results['quarterly_sales'].plot(kind='line', marker='o')
    plt.title('季度销售额趋势')
    plt.xlabel('季度')
    plt.ylabel('销售额 (元)')
    plt.grid(True)
    plt.savefig(os.path.join(output_folder, 'quarterly_sales.png'))
    plt.close()

6. 生成PDF报告

def generate_pdf_report(analysis_results, output_folder):
    """
    生成PDF报告
    """
    # 创建PDF文档
    doc = SimpleDocTemplate(
        os.path.join(output_folder, "销售分析报告.pdf"),
        pagesize=letter
    )
    
    # 获取样式
    styles = getSampleStyleSheet()
    story = []
    
    # 添加标题
    title = Paragraph("销售数据分析报告", styles['Title'])
    story.append(title)
    story.append(Spacer(1, 12))
    
    # 添加日期
    date_str = datetime.now().strftime("%Y年%m月%d日")
    date_paragraph = Paragraph(f"生成日期: {date_str}", styles['Normal'])
    story.append(date_paragraph)
    story.append(Spacer(1, 24))
    
    # 添加总销售额
    total_sales = analysis_results['total_sales']
    total_sales_text = Paragraph(f"总销售额: {total_sales:,.2f} 元", styles['Heading2'])
    story.append(total_sales_text)
    story.append(Spacer(1, 12))
    
    # 添加分公司销售额表格
    branch_data = [['分公司', '销售额 (元)']]
    for branch, sales in analysis_results['branch_sales'].items():
        branch_data.append([branch, f"{sales:,.2f}"])
    
    branch_table = Table(branch_data)
    branch_table.setStyle(TableStyle([
        ('BACKGROUND', (0, 0), (-1, 0), colors.grey),
        ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
        ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
        ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
        ('FONTSIZE', (0, 0), (-1, 0), 14),
        ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
        ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
        ('FONTSIZE', (0, 1), (-1, -1), 12),
        ('GRID', (0, 0), (-1, -1), 1, colors.black)
    ]))
    story.append(Paragraph("各分公司销售额:", styles['Heading3']))
    story.append(Spacer(1, 6))
    story.append(branch_table)
    story.append(Spacer(1, 24))
    
    # 添加图表
    story.append(Paragraph("各分公司销售额对比:", styles['Heading3']))
    branch_img = Image(os.path.join(output_folder, 'branch_sales.png'))
    branch_img.drawHeight = 300
    branch_img.drawWidth = 500
    story.append(branch_img)
    story.append(Spacer(1, 12))
    
    story.append(Paragraph("热销产品占比:", styles['Heading3']))
    product_img = Image(os.path.join(output_folder, 'product_sales.png'))
    product_img.drawHeight = 400
    product_img.drawWidth = 400
    story.append(product_img)
    story.append(Spacer(1, 12))
    
    story.append(Paragraph季度销售额趋势:", styles['Heading3']))
    quarterly_img = Image(os.path.join(output_folder, 'quarterly_sales.png'))
    quarterly_img.drawHeight = 300
    quarterly_img.drawWidth = 500
    story.append(quarterly_img)
    
    # 生成PDF
    doc.build(story)

7. 主函数

def main():
    # 设置路径
    data_folder = 'data'
    output_folder = 'output'
    
    # 如果输出文件夹不存在，则创建
    if not os.path.exists(output_folder):
        os.makedirs(output_folder)
    
    # 加载数据
    print("正在加载数据...")
    sales_data = load_sales_data(data_folder)
    
    # 清洗和处理数据
    print("正在清洗和处理数据...")
    cleaned_data = clean_and_process_data(sales_data)
    
    # 分析数据
    print("正在分析数据...")
    analysis_results = analyze_sales_data(cleaned_data)
    
    # 生成图表
    print("正在生成图表...")
    create_charts(analysis_results, output_folder)
    
    # 生成PDF报告
    print("正在生成PDF报告...")
    generate_pdf_report(analysis_results, output_folder)
    
    print("报告生成完成！")
    
if __name__ == "__main__":
    main()

运行结果

运行上述脚本后，将在output文件夹中生成：

• branch_sales.png – 各分公司销售额对比柱状图
• product_sales.png – 热销产品占比饼图
• quarterly_sales.png – 季度销售额趋势折线图
• 销售分析报告.pdf – 完整的PDF报告

扩展应用

这个基础框架可以进一步扩展：

• 添加电子邮件功能，自动发送报告给相关人员
• 集成数据库，直接从数据库读取数据
• 添加更复杂的统计分析
• 创建Web界面，允许用户上传文件并生成报告
• 添加自动异常检测，识别数据中的异常值

总结

通过这个实战项目，我们展示了如何使用Python自动化处理Excel数据、进行数据分析、创建可视化图表和生成PDF报告。这种自动化方法可以节省大量手工操作时间，减少错误，并提高工作效率。

Python在办公自动化方面有着广泛的应用前景，掌握这些技能将使你在工作中更具竞争力。