当前位置：首页 > news >正文

新手向:Python数据处理Excel报表自动化生成与分析

news 2025/7/16 8:04:55

Python实现Excel报表自动化系统全流程指南

本文将详细介绍如何使用Python实现一个完整的Excel报表自动化系统，涵盖从数据清洗、分析到可视化报表生成的全流程。本教程面向Python初学者，通过实际案例讲解pandas和openpyxl库的核心用法。

系统概述

Excel报表自动化系统能够：

自动读取原始数据文件（如CSV、Excel等）
进行数据清洗和预处理
执行数据分析计算
生成格式化的Excel报表
创建可视化图表

环境准备

安装必要库

pip install pandas openpyxl matplotlib

导入库

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt

数据清洗阶段

1. 读取原始数据

# 从CSV文件读取
raw_data = pd.read_csv('sales_data.csv')# 从Excel文件读取
raw_data = pd.read_excel('sales_data.xlsx')

2. 数据清洗

典型的数据清洗操作包括：

处理缺失值：df.fillna()或df.dropna()
数据类型转换：df.astype()
去除重复值：df.drop_duplicates()
异常值处理：通过条件筛选或统计方法

示例：

# 填充缺失值
cleaned_data = raw_data.fillna({'Sales': 0})# 转换日期格式
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])# 去除重复记录
cleaned_data = cleaned_data.drop_duplicates()

数据分析阶段

1. 基础统计分析

# 按产品类别汇总销售数据
sales_summary = cleaned_data.groupby('Product Category')['Sales'].agg(['sum', 'mean', 'count'])# 计算月增长率
monthly_sales = cleaned_data.groupby(pd.Grouper(key='Date', freq='M'))['Sales'].sum()
monthly_growth = monthly_sales.pct_change() * 100

2. 高级分析

# 计算各区域销售占比
region_share = cleaned_data.groupby('Region')['Sales'].sum() / cleaned_data['Sales'].sum()# 客户价值分析(RFM模型)
current_date = pd.to_datetime('today')
rfm_data = cleaned_data.groupby('CustomerID').agg({'Date': lambda x: (current_date - x.max()).days,'OrderID': 'count','Sales': 'sum'
})

报表生成阶段

1. 创建Excel工作簿

wb = Workbook()
ws = wb.active
ws.title = "销售汇总"

2. 写入数据

# 写入表头
ws.append(['产品类别', '销售总额', '平均销售额', '销售数量'])# 写入汇总数据
for index, row in sales_summary.iterrows():ws.append([index, row['sum'], row['mean'], row['count']])

3. 添加图表

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=len(sales_summary)+1)
categories = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=len(sales_summary)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F2")

4. 格式化单元格

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill# 设置标题样式
for cell in ws[1]:cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')cell.fill = PatternFill("solid", fgColor="DDDDDD")# 设置数字格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=4):for cell in row:cell.number_format = '#,##0'

自动化流程整合

1. 创建主函数

def generate_report(input_file, output_file):# 数据读取raw_data = pd.read_excel(input_file)# 数据清洗cleaned_data = clean_data(raw_data)# 数据分析report_data = analyze_data(cleaned_data)# 生成报表create_excel_report(report_data, output_file)print(f"报表已成功生成: {output_file}")

2. 定时任务设置

import schedule
import timedef job():generate_report('daily_sales.xlsx', 'sales_report.xlsx')# 每天上午9点运行
schedule.every().day.at("09:00").do(job)while True:schedule.run_pending()time.sleep(1)

实际应用示例

场景：月度销售报表

系统自动从ERP导出原始数据
清洗并计算各产品线销售指标
生成包含以下内容的报表：
- 销售汇总表
- 区域销售分布饼图
- 月度销售趋势折线图
- 销售TOP10产品列表

场景：客户分析报告

整合CRM和销售系统数据
进行客户细分和价值分析
输出包含以下内容的报告：
- 客户价值矩阵(RFM)
- 客户生命周期分析
- 流失风险预警列表

通过本教程，您已掌握使用Python实现Excel报表自动化的核心技能。实际应用中可根据具体需求扩展功能，如添加邮件自动发送、多数据源整合等高级特性。

Python实现Excel报表自动化系统全流程指南

系统概述

Excel报表自动化系统能够：

自动读取原始数据文件（如CSV、Excel等）
进行数据清洗和预处理
执行数据分析计算
生成格式化的Excel报表
创建可视化图表

环境准备

安装必要库

pip install pandas openpyxl matplotlib

导入库

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt

数据清洗阶段

1. 读取原始数据

# 从CSV文件读取
raw_data = pd.read_csv('sales_data.csv')# 从Excel文件读取
raw_data = pd.read_excel('sales_data.xlsx')

2. 数据清洗

典型的数据清洗操作包括：

处理缺失值：df.fillna()或df.dropna()
数据类型转换：df.astype()
去除重复值：df.drop_duplicates()
异常值处理：通过条件筛选或统计方法

示例：

# 填充缺失值
cleaned_data = raw_data.fillna({'Sales': 0})# 转换日期格式
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])# 去除重复记录
cleaned_data = cleaned_data.drop_duplicates()

数据分析阶段

1. 基础统计分析

# 按产品类别汇总销售数据
sales_summary = cleaned_data.groupby('Product Category')['Sales'].agg(['sum', 'mean', 'count'])# 计算月增长率
monthly_sales = cleaned_data.groupby(pd.Grouper(key='Date', freq='M'))['Sales'].sum()
monthly_growth = monthly_sales.pct_change() * 100

2. 高级分析

# 计算各区域销售占比
region_share = cleaned_data.groupby('Region')['Sales'].sum() / cleaned_data['Sales'].sum()# 客户价值分析(RFM模型)
current_date = pd.to_datetime('today')
rfm_data = cleaned_data.groupby('CustomerID').agg({'Date': lambda x: (current_date - x.max()).days,'OrderID': 'count','Sales': 'sum'
})

报表生成阶段

1. 创建Excel工作簿

wb = Workbook()
ws = wb.active
ws.title = "销售汇总"

2. 写入数据

# 写入表头
ws.append(['产品类别', '销售总额', '平均销售额', '销售数量'])# 写入汇总数据
for index, row in sales_summary.iterrows():ws.append([index, row['sum'], row['mean'], row['count']])

3. 添加图表

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=len(sales_summary)+1)
categories = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=len(sales_summary)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F2")

4. 格式化单元格

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill# 设置标题样式
for cell in ws[1]:cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')cell.fill = PatternFill("solid", fgColor="DDDDDD")# 设置数字格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=4):for cell in row:cell.number_format = '#,##0'

自动化流程整合

1. 创建主函数

def generate_report(input_file, output_file):# 数据读取raw_data = pd.read_excel(input_file)# 数据清洗cleaned_data = clean_data(raw_data)# 数据分析report_data = analyze_data(cleaned_data)# 生成报表create_excel_report(report_data, output_file)print(f"报表已成功生成: {output_file}")

2. 定时任务设置

import schedule
import timedef job():generate_report('daily_sales.xlsx', 'sales_report.xlsx')# 每天上午9点运行
schedule.every().day.at("09:00").do(job)while True:schedule.run_pending()time.sleep(1)

实际应用示例

场景：月度销售报表

系统自动从ERP导出原始数据
清洗并计算各产品线销售指标
生成包含以下内容的报表：
- 销售汇总表
- 区域销售分布饼图
- 月度销售趋势折线图
- 销售TOP10产品列表

场景：客户分析报告

整合CRM和销售系统数据
进行客户细分和价值分析
输出包含以下内容的报告：
- 客户价值矩阵(RFM)
- 客户生命周期分析
- 流失风险预警列表

Python实现Excel报表自动化系统全流程指南

系统概述

Excel报表自动化系统能够：

自动读取原始数据文件（如CSV、Excel等）
进行数据清洗和预处理
执行数据分析计算
生成格式化的Excel报表
创建可视化图表

环境准备

安装必要库

pip install pandas openpyxl matplotlib

导入库

import pandas as pd
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt

数据清洗阶段

1. 读取原始数据

# 从CSV文件读取
raw_data = pd.read_csv('sales_data.csv')# 从Excel文件读取
raw_data = pd.read_excel('sales_data.xlsx')

2. 数据清洗

典型的数据清洗操作包括：

处理缺失值：df.fillna()或df.dropna()
数据类型转换：df.astype()
去除重复值：df.drop_duplicates()
异常值处理：通过条件筛选或统计方法

示例：

# 填充缺失值
cleaned_data = raw_data.fillna({'Sales': 0})# 转换日期格式
cleaned_data['Date'] = pd.to_datetime(cleaned_data['Date'])# 去除重复记录
cleaned_data = cleaned_data.drop_duplicates()

数据分析阶段

1. 基础统计分析

# 按产品类别汇总销售数据
sales_summary = cleaned_data.groupby('Product Category')['Sales'].agg(['sum', 'mean', 'count'])# 计算月增长率
monthly_sales = cleaned_data.groupby(pd.Grouper(key='Date', freq='M'))['Sales'].sum()
monthly_growth = monthly_sales.pct_change() * 100

2. 高级分析

# 计算各区域销售占比
region_share = cleaned_data.groupby('Region')['Sales'].sum() / cleaned_data['Sales'].sum()# 客户价值分析(RFM模型)
current_date = pd.to_datetime('today')
rfm_data = cleaned_data.groupby('CustomerID').agg({'Date': lambda x: (current_date - x.max()).days,'OrderID': 'count','Sales': 'sum'
})

报表生成阶段

1. 创建Excel工作簿

wb = Workbook()
ws = wb.active
ws.title = "销售汇总"

2. 写入数据

# 写入表头
ws.append(['产品类别', '销售总额', '平均销售额', '销售数量'])# 写入汇总数据
for index, row in sales_summary.iterrows():ws.append([index, row['sum'], row['mean'], row['count']])

3. 添加图表

# 创建柱状图
chart = BarChart()
data = Reference(ws, min_col=2, max_col=4, min_row=1, max_row=len(sales_summary)+1)
categories = Reference(ws, min_col=1, max_col=1, min_row=2, max_row=len(sales_summary)+1)
chart.add_data(data, titles_from_data=True)
chart.set_categories(categories)
ws.add_chart(chart, "F2")

4. 格式化单元格

from openpyxl.styles import Font, Alignment, Border, Side, PatternFill# 设置标题样式
for cell in ws[1]:cell.font = Font(bold=True)cell.alignment = Alignment(horizontal='center')cell.fill = PatternFill("solid", fgColor="DDDDDD")# 设置数字格式
for row in ws.iter_rows(min_row=2, min_col=2, max_col=4):for cell in row:cell.number_format = '#,##0'

自动化流程整合

1. 创建主函数

def generate_report(input_file, output_file):# 数据读取raw_data = pd.read_excel(input_file)# 数据清洗cleaned_data = clean_data(raw_data)# 数据分析report_data = analyze_data(cleaned_data)# 生成报表create_excel_report(report_data, output_file)print(f"报表已成功生成: {output_file}")

2. 定时任务设置

import schedule
import timedef job():generate_report('daily_sales.xlsx', 'sales_report.xlsx')# 每天上午9点运行
schedule.every().day.at("09:00").do(job)while True:schedule.run_pending()time.sleep(1)

实际应用示例

场景：月度销售报表

系统自动从ERP导出原始数据
清洗并计算各产品线销售指标
生成包含以下内容的报表：
- 销售汇总表
- 区域销售分布饼图
- 月度销售趋势折线图
- 销售TOP10产品列表

场景：客户分析报告

整合CRM和销售系统数据
进行客户细分和价值分析
输出包含以下内容的报告：
- 客户价值矩阵(RFM)
- 客户生命周期分析
- 流失风险预警列表

环境准备

我们需要安装以下Python库：

pandas：强大的数据分析工具
openpyxl：Excel文件操作库
matplotlib：数据可视化库

安装命令：

bashpip install pandas openpyxl matplotlib

项目功能概述

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

从多个数据源读取原始数据
数据清洗与预处理
关键指标计算与分析
自动生成格式化的Excel报表
创建数据可视化图表
邮件自动发送报表

代码详细解析

1. 导入必要的库

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt
import os
from datetime import datetime

2. 配置参数设置

# 数据源配置
DATA_SOURCES = {'sales': 'data/raw_sales_data.xlsx','inventory': 'data/inventory_records.csv','customers': 'data/customer_info.json'
}# 报表输出配置
REPORT_PATH = 'reports/monthly_report.xlsx'
REPORT_TITLE = '2023年度销售分析报告'
ANALYSIS_PERIOD = ('2023-01-01', '2023-12-31')# 样式配置
HEADER_STYLE = {'font': Font(bold=True, color='FFFFFF'),'fill': PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid'),'alignment': Alignment(horizontal='center')
}DATA_STYLE = {'border': Border(left=Side(style='thin'), 'alignment': Alignment(horizontal='right')
}

3. 数据加载与清洗

def load_and_clean_data():"""加载并清洗原始数据"""# 加载销售数据sales_df = pd.read_excel(DATA_SOURCES['sales'])sales_df = sales_df.dropna(subset=['order_id', 'sale_amount'])sales_df['order_date'] = pd.to_datetime(sales_df['order_date'])# 加载库存数据inventory_df = pd.read_csv(DATA_SOURCES['inventory'])inventory_df = inventory_df[inventory_df['stock_quantity'] > 0]# 加载客户数据customers_df = pd.read_json(DATA_SOURCES['customers'])customers_df['join_date'] = pd.to_datetime(customers_df['join_date'])return sales_df, inventory_df, customers_df

4. 数据分析与计算

def perform_analysis(sales_df, inventory_df, customers_df):"""执行核心数据分析"""# 销售分析period_mask = (sales_df['order_date'] >= ANALYSIS_PERIOD[0]) & \(sales_df['order_date'] <= ANALYSIS_PERIOD[1])sales_analysis = sales_df[period_mask].groupby('product_category')['sale_amount'] \.agg(['sum', 'count', 'mean']).reset_index()# 库存周转分析inventory_turnover = inventory_df.groupby('category')['stock_quantity'].sum().reset_index()# 客户分析active_customers = customers_df[customers_df['status'] == 'active']customer_growth = active_customers.groupby(pd.Grouper(key='join_date', freq='M')).size().reset_index(name='count')return sales_analysis, inventory_turnover, customer_growth

5. Excel报表生成

def generate_excel_report(data, output_path):"""生成格式化的Excel报表"""wb = Workbook()ws = wb.activews.title = "销售摘要"# 添加标题ws.merge_cells('A1:D1')title_cell = ws['A1']title_cell.value = REPORT_TITLEtitle_cell.font = Font(size=16, bold=True)title_cell.alignment = Alignment(horizontal='center')# 添加数据表头headers = ['产品类别', '总销售额', '订单数量', '平均订单价值']for col_num, header in enumerate(headers, 1):cell = ws.cell(row=3, column=col_num, value=header)cell.font = HEADER_STYLE['font']cell.fill = HEADER_STYLE['fill']cell.alignment = HEADER_STYLE['alignment']# 填充数据for row_num, row_data in enumerate(data[0].values, 4):for col_num, value in enumerate(row_data, 1):cell = ws.cell(row=row_num, column=col_num, value=value)cell.border = DATA_STYLE['border']cell.alignment = DATA_STYLE['alignment']# 添加图表chart = BarChart()chart.title = "各品类销售额对比"chart.y_axis.title = '销售额'chart.x_axis.title = '产品类别'data_ref = Reference(ws, min_col=2, min_row=3, max_col=2, max_row=row_num)cats_ref = Reference(ws, min_col=1, min_row=4, max_row=row_num)chart.add_data(data_ref, titles_from_data=False)chart.set_categories(cats_ref)ws.add_chart(chart, "F3")# 保存文件os.makedirs(os.path.dirname(output_path), exist_ok=True)wb.save(output_path)

6. 主程序流程

def main():print("开始生成月度分析报告...")start_time = datetime.now()try:# 数据加载与清洗sales, inventory, customers = load_and_clean_data()# 数据分析sales_analysis, inventory_turnover, customer_growth = perform_analysis(sales, inventory, customers)# 生成报表generate_excel_report((sales_analysis, inventory_turnover, customer_growth), REPORT_PATH)# 生成可视化图表generate_visualizations(sales_analysis, customer_growth)elapsed = datetime.now() - start_timeprint(f"报表生成完成！耗时: {elapsed.total_seconds():.2f}秒")print(f"报表路径: {os.path.abspath(REPORT_PATH)}")except Exception as e:print(f"生成报告时出错: {str(e)}")if __name__ == "__main__":main()

完整代码实现

import pandas as pd
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from openpyxl.chart import BarChart, Reference
import matplotlib.pyplot as plt
import os
from datetime import datetime# 配置区域
DATA_SOURCES = {'sales': 'data/raw_sales_data.xlsx','inventory': 'data/inventory_records.csv','customers': 'data/customer_info.json'
}REPORT_PATH = 'reports/monthly_report.xlsx'
REPORT_TITLE = '2023年度销售分析报告'
ANALYSIS_PERIOD = ('2023-01-01', '2023-12-31')HEADER_STYLE = {'font': Font(bold=True, color='FFFFFF'),'fill': PatternFill(start_color='4F81BD', end_color='4F81BD', fill_type='solid'),'alignment': Alignment(horizontal='center')
}DATA_STYLE = {'border': Border(left=Side(style='thin'), 'alignment': Alignment(horizontal='right')
}def load_and_clean_data():"""加载并清洗原始数据"""sales_df = pd.read_excel(DATA_SOURCES['sales'])sales_df = sales_df.dropna(subset=['order_id', 'sale_amount'])sales_df['order_date'] = pd.to_datetime(sales_df['order_date'])inventory_df = pd.read_csv(DATA_SOURCES['inventory'])inventory_df = inventory_df[inventory_df['stock_quantity'] > 0]customers_df = pd.read_json(DATA_SOURCES['customers'])customers_df['join_date'] = pd.to_datetime(customers_df['join_date'])return sales_df, inventory_df, customers_dfdef perform_analysis(sales_df, inventory_df, customers_df):"""执行核心数据分析"""period_mask = (sales_df['order_date'] >= ANALYSIS_PERIOD[0]) & \(sales_df['order_date'] <= ANALYSIS_PERIOD[1])sales_analysis = sales_df[period_mask].groupby('product_category')['sale_amount'] \.agg(['sum', 'count', 'mean']).reset_index()inventory_turnover = inventory_df.groupby('category')['stock_quantity'].sum().reset_index()active_customers = customers_df[customers_df['status'] == 'active']customer_growth = active_customers.groupby(pd.Grouper(key='join_date', freq='M')).size().reset_index(name='count')return sales_analysis, inventory_turnover, customer_growthdef generate_visualizations(sales_analysis, customer_growth):"""生成可视化图表"""# 销售额分布图plt.figure(figsize=(10, 6))plt.bar(sales_analysis['product_category'], sales_analysis['sum'])plt.title('各品类销售额对比')plt.xlabel('产品类别')plt.ylabel('销售额')plt.xticks(rotation=45)plt.tight_layout()plt.savefig('reports/sales_by_category.png')# 客户增长曲线plt.figure(figsize=(10, 6))plt.plot(customer_growth['join_date'], customer_growth['count'], marker='o')plt.title('月度新增客户趋势')plt.xlabel('月份')plt.ylabel('新增客户数')plt.grid(True)plt.tight_layout()plt.savefig('reports/customer_growth.png')def generate_excel_report(data, output_path):"""生成格式化的Excel报表"""wb = Workbook()ws = wb.activews.title = "销售摘要"# 添加标题ws.merge_cells('A1:D1')title_cell = ws['A1']title_cell.value = REPORT_TITLEtitle_cell.font = Font(size=16, bold=True)title_cell.alignment = Alignment(horizontal='center')# 添加数据表头headers = ['产品类别', '总销售额', '订单数量', '平均订单价值']for col_num, header in enumerate(headers, 1):cell = ws.cell(row=3, column=col_num, value=header)cell.font = HEADER_STYLE['font']cell.fill = HEADER_STYLE['fill']cell.alignment = HEADER_STYLE['alignment']# 填充数据for row_num, row_data in enumerate(data[0].values, 4):for col_num, value in enumerate(row_data, 1):cell = ws.cell(row=row_num, column=col_num, value=value)cell.border = DATA_STYLE['border']cell.alignment = DATA_STYLE['alignment']# 添加图表chart = BarChart()chart.title = "各品类销售额对比"chart.y_axis.title = '销售额'chart.x_axis.title = '产品类别'data_ref = Reference(ws, min_col=2, min_row=3, max_col=2, max_row=row_num)cats_ref = Reference(ws, min_col=1, min_row=4, max_row=row_num)chart.add_data(data_ref, titles_from_data=False)chart.set_categories(cats_ref)ws.add_chart(chart, "F3")# 保存文件os.makedirs(os.path.dirname(output_path), exist_ok=True)wb.save(output_path)def main():print("开始生成月度分析报告...")start_time = datetime.now()try:sales, inventory, customers = load_and_clean_data()sales_analysis, inventory_turnover, customer_growth = perform_analysis(sales, inventory, customers)generate_excel_report((sales_analysis, inventory_turnover, customer_growth), REPORT_PATH)generate_visualizations(sales_analysis, customer_growth)elapsed = datetime.now() - start_timeprint(f"报表生成完成！耗时: {elapsed.total_seconds():.2f}秒")print(f"报表路径: {os.path.abspath(REPORT_PATH)}")except Exception as e:print(f"生成报告时出错: {str(e)}")if __name__ == "__main__":main()