Python处理Excel多工作表：openpyxl与pandas实战对比
1. 场景概述
假设我们需要处理一个包含多个工作表的销售数据Excel文件，主要任务包括：
- 读取和分析数据
- 跨工作表数据汇总
- 数据清洗和转换
- 生成汇总报表
2. 示例数据准备
先创建一个示例Excel文件（sales_data.xlsx）：
Sheet1: 北京销售数据Sheet2: 上海销售数据 Sheet3: 广州销售数据
每个工作表包含：日期、产品、销售额、数量等字段。
3. openpyxl实战示例
3.1 基础操作
from openpyxl import load_workbook, Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
import datetime
# 1. 读取工作簿
def read_excel_openpyxl(file_path):
"""读取Excel文件的所有工作表"""
wb = load_workbook(file_path, data_only=True) # data_only=True获取计算后的值
sheet_info = {}
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
data = []
# 读取数据（假设第一行是标题）
for row in ws.iter_rows(min_row=2, values_only=True): # 跳过标题行
if any(cell is not None for cell in row): # 跳过空行
data.append(row)
sheet_info[sheet_name] = {
'data': data,
'max_row': ws.max_row,
'max_column': ws.max_column
}
wb.close()
return sheet_info
# 2. 跨工作表汇总
def summarize_sales_openpyxl(file_path):
"""汇总各城市的销售总额"""
wb = load_workbook(file_path, data_only=True)
summary = {}
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
total_sales = 0
total_quantity = 0
# 假设第3列是销售额，第4列是数量
for row in ws.iter_rows(min_row=2, max_col=4):
sales = row[2].value or 0 # 第3列
quantity = row[3].value or 0 # 第4列
if isinstance(sales, (int, float)):
total_sales += sales
if isinstance(quantity, (int, float)):
total_quantity += quantity
summary[sheet_name] = {
'total_sales': total_sales,
'total_quantity': total_quantity,
'avg_price': total_sales / total_quantity if total_quantity > 0 else 0
}
wb.close()
return summary
# 3. 数据清洗和格式设置
def clean_and_format_openpyxl(file_path, output_path):
"""数据清洗并添加格式"""
wb = load_workbook(file_path)
for sheet_name in wb.sheetnames:
ws = wb[sheet_name]
# 添加汇总行
total_row = ws.max_row + 2
ws[f'A{total_row}'] = '总计'
# 计算总和
sales_col = get_column_letter(3) # C列
quantity_col = get_column_letter(4) # D列
ws[f'{sales_col}{total_row}'] = f'=SUM({sales_col}2:{sales_col}{ws.max_row})'
ws[f'{quantity_col}{total_row}'] = f'=SUM({quantity_col}2:{quantity_col}{ws.max_row})'
# 设置格式
header_font = Font(bold=True, color="FFFFFF")
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
for cell in ws[1]: # 标题行
cell.font = header_font
cell.fill = header_fill
cell.alignment = Alignment(horizontal='center')
# 创建汇总工作表
summary_ws = wb.create_sheet(title='汇总')
summary_ws.append(['城市', '销售总额', '总数量', '平均单价'])
summary_data = summarize_sales_openpyxl(file_path)
for city, data in summary_data.items():
summary_ws.append([city, data['total_sales'], data['total_quantity'], data['avg_price']])
# 自动调整列宽
for sheet in wb.worksheets:
for column in sheet.columns:
max_length = 0
column_letter = get_column_letter(column[0].column)
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)
sheet.column_dimensions[column_letter].width = adjusted_width
wb.save(output_path)
wb.close()
print(f"处理完成，保存到: {output_path}")
4. pandas实战示例
4.1 基础操作
import pandas as pd
import numpy as np
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill
import warnings
warnings.filterwarnings('ignore')
# 1. 读取工作簿
def read_excel_pandas(file_path):
"""使用pandas读取所有工作表"""
# 方法1：读取所有工作表到字典
all_sheets = pd.read_excel(file_path, sheet_name=None)
# 方法2：分别读取每个工作表（更灵活）
xl = pd.ExcelFile(file_path)
sheet_dict = {}
for sheet_name in xl.sheet_names:
df = xl.parse(sheet_name)
sheet_dict[sheet_name] = df
return sheet_dict
# 2. 跨工作表汇总
def summarize_sales_pandas(file_path):
"""使用pandas进行数据汇总"""
# 读取所有工作表
all_sheets = pd.read_excel(file_path, sheet_name=None)
summary_list = []
for sheet_name, df in all_sheets.items():
# 数据清洗：删除空值
df_clean = df.dropna(subset=['销售额', '数量'])
# 计算统计指标
total_sales = df_clean['销售额'].sum()
total_quantity = df_clean['数量'].sum()
avg_price = total_sales / total_quantity if total_quantity > 0 else 0
# 更多统计分析
stats = {
'城市': sheet_name,
'销售总额': total_sales,
'总数量': total_quantity,
'平均单价': avg_price,
'最大单笔销售': df_clean['销售额'].max(),
'最小单笔销售': df_clean['销售额'].min(),
'销售笔数': len(df_clean),
'平均每单数量': df_clean['数量'].mean()
}
summary_list.append(stats)
# 创建汇总DataFrame
summary_df = pd.DataFrame(summary_list)
# 计算总计
totals = pd.DataFrame([{
'城市': '总计',
'销售总额': summary_df['销售总额'].sum(),
'总数量': summary_df['总数量'].sum(),
'平均单价': summary_df['销售总额'].sum() / summary_df['总数量'].sum(),
'销售笔数': summary_df['销售笔数'].sum()
}])
return pd.concat([summary_df, totals], ignore_index=True)
# 3. 数据透视和复杂分析
def advanced_analysis_pandas(file_path):
"""使用pandas进行高级分析"""
# 合并所有工作表数据
all_sheets = pd.read_excel(file_path, sheet_name=None)
# 添加城市列并合并
combined_data = []
for city, df in all_sheets.items():
df['城市'] = city
combined_data.append(df)
combined_df = pd.concat(combined_data, ignore_index=True)
# 数据透视表：按产品和城市汇总
pivot_table = pd.pivot_table(
combined_df,
values=['销售额', '数量'],
index=['产品'],
columns=['城市'],
aggfunc={'销售额': 'sum', '数量': 'sum'},
fill_value=0,
margins=True, # 添加总计
margins_name='总计'
)
# 分组分析
grouped = combined_df.groupby(['城市', '产品']).agg({
'销售额': ['sum', 'mean', 'count'],
'数量': 'sum'
}).round(2)
# 时间序列分析（假设有日期列）
if '日期' in combined_df.columns:
combined_df['日期'] = pd.to_datetime(combined_df['日期'])
combined_df['月份'] = combined_df['日期'].dt.to_period('M')
monthly_sales = combined_df.groupby(['月份', '城市'])['销售额'].sum().unstack()
return {
'combined_data': combined_df,
'pivot_table': pivot_table,
'grouped_analysis': grouped,
'monthly_sales': monthly_sales if '日期' in combined_df.columns else None
}
# 4. 输出到Excel（保留格式）
def export_to_excel_with_format_pandas(file_path, output_path):
"""使用pandas处理，openpyxl美化输出"""
# 使用pandas进行数据处理
summary_df = summarize_sales_pandas(file_path)
analysis_results = advanced_analysis_pandas(file_path)
# 创建Excel写入器
with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
# 写入汇总数据
summary_df.to_excel(writer, sheet_name='汇总', index=False)
# 写入透视表
analysis_results['pivot_table'].to_excel(writer, sheet_name='数据透视')
# 写入详细分析
analysis_results['grouped_analysis'].to_excel(writer, sheet_name='分组分析')
if analysis_results['monthly_sales'] is not None:
analysis_results['monthly_sales'].to_excel(writer, sheet_name='月度趋势')
# 获取workbook对象进行格式设置
workbook = writer.book
# 设置汇总表格式
summary_sheet = workbook['汇总']
# 设置标题格式
header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
header_font = Font(bold=True, color="FFFFFF")
for cell in summary_sheet[1]: # 第一行
cell.fill = header_fill
cell.font = header_font
# 设置数字格式
for row in summary_sheet.iter_rows(min_row=2, max_col=4):
for cell in row[1:4]: # 数值列
cell.number_format = '#,##0.00'
# 自动调整列宽
for column in summary_sheet.columns:
max_length = 0
column_letter = column[0].column_letter
for cell in column:
try:
cell_value = str(cell.value) if cell.value else ""
max_length = max(max_length, len(cell_value))
except:
pass
adjusted_width = min(max_length + 2, 50)
summary_sheet.column_dimensions[column_letter].width = adjusted_width
print(f"处理完成，保存到: {output_path}")
5. 实战对比表
| 特性 |
openpyxl |
pandas |
|---|
| 读取性能 |
中等，适合逐行处理 |
优秀，批量读取快 |
| 内存使用 |
较低，支持只读模式 |
较高，全量加载到内存 |
| 写入功能 |
强大，支持单元格级控制 |
良好，支持DataFrame输出 |
| 格式控制 |
非常精细，单元格级控制 |
有限，依赖openpyxl引擎 |
| 数据处理 |
基础，需要手动实现 |
强大，内置丰富功能 |
| 公式支持 |
支持读取和写入公式 |
有限支持 |
| 图表支持 |
支持创建图表 |
不支持 |
| 学习曲线 |
较平缓 |
较陡峭（需pandas知识） |
6. 综合对比示例
def comprehensive_comparison(input_file):
"""
综合对比两种方法的优势
"""
print("=" * 60)
print("Excel处理方案对比")
print("=" * 60)
# 场景1：快速查看数据
print("\n1. 快速查看数据:")
print(" pandas方案：")
sheets_pandas = read_excel_pandas(input_file)
for name, df in sheets_pandas.items():
print(f" {name}: {len(df)} 行 × {len(df.columns)} 列")
# 场景2：数据汇总
print("\n2. 数据汇总分析:")
print(" openpyxl汇总结果（基础）:")
openpyxl_summary = summarize_sales_openpyxl(input_file)
for city, data in openpyxl_summary.items():
print(f" {city}: 销售额={data['total_sales']:,.2f}")
print("\n pandas汇总结果（高级）:")
pandas_summary = summarize_sales_pandas(input_file)
print(pandas_summary.to_string())
# 场景3：复杂分析
print("\n3. 复杂分析能力:")
print(" pandas可轻松完成:")
analysis = advanced_analysis_pandas(input_file)
print(" - 数据透视表")
print(" - 分组聚合")
print(" - 时间序列分析")
# 性能测试
import time
print("\n4. 性能测试:")
# openpyxl读取测试
start = time.time()
for _ in range(10):
read_excel_openpyxl(input_file)
openpyxl_time = time.time() - start
# pandas读取测试
start = time.time()
for _ in range(10):
read_excel_pandas(input_file)
pandas_time = time.time() - start
print(f" 读取性能：")
print(f" openpyxl: {openpyxl_time:.2f} 秒")
print(f" pandas: {pandas_time:.2f} 秒")
print(f" pandas比openpyxl快 {openpyxl_time/pandas_time:.1f} 倍")
# 使用示例
if __name__ == "__main__":
# 创建测试数据
from create_sample_data import create_sample_excel
create_sample_excel("sales_data.xlsx")
# 对比测试
comprehensive_comparison("sales_data.xlsx")
# 使用openpyxl处理
clean_and_format_openpyxl("sales_data.xlsx", "output_openpyxl.xlsx")
# 使用pandas处理
export_to_excel_with_format_pandas("sales_data.xlsx", "output_pandas.xlsx")
7. 创建示例数据的辅助函数
# create_sample_data.py
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
def create_sample_excel(filepath):
"""创建示例Excel文件"""
cities = ['北京', '上海', '广州']
products = ['产品A', '产品B', '产品C', '产品D']
with pd.ExcelWriter(filepath, engine='openpyxl') as writer:
for city in cities:
# 生成模拟数据
dates = [datetime(2024, 1, 1) + timedelta(days=i) for i in range(30)]
data = {
'日期': np.random.choice(dates, 100),
'产品': np.random.choice(products, 100),
'销售额': np.random.uniform(100, 10000, 100).round(2),
'数量': np.random.randint(1, 100, 100),
'客户类型': np.random.choice(['新客户', '老客户'], 100)
}
df = pd.DataFrame(data)
df.to_excel(writer, sheet_name=city, index=False)
print(f"示例文件已创建: {filepath}")
8. 选择建议
使用openpyxl的场景：
需要精细控制格式（字体、颜色、边框、合并单元格等）
需要操作Excel公式和图表
文件非常大，需要流式读取处理
只需要读取部分单元格，而非整个工作表
使用pandas的场景：
需要进行复杂的数据分析和转换
需要数据透视、分组聚合等操作
需要合并多个Excel文件或工作表
需要与其它数据源（数据库、CSV等）整合处理
最佳实践：结合使用
def hybrid_solution(input_file, output_file):
"""
结合openpyxl和pandas的最佳实践
"""
# 1. 使用pandas进行数据处理
all_data = pd.read_excel(input_file, sheet_name=None)
# 数据处理逻辑
processed_data = {}
for sheet_name, df in all_data.items():
# pandas数据清洗和转换
df_clean = df.dropna()
df_clean['利润率'] = (df_clean['销售额'] * 0.2) # 示例计算
processed_data[sheet_name] = df_clean
# 2. 使用openpyxl进行格式设置
from openpyxl import Workbook
from openpyxl.utils.dataframe import dataframe_to_rows
wb = Workbook()
for sheet_name, df in processed_data.items():
ws = wb.create_sheet(title=sheet_name)
# 将DataFrame写入工作表
for r_idx, row in enumerate(dataframe_to_rows(df, index=False, header=True), 1):
for c_idx, value in enumerate(row, 1):
ws.cell(row=r_idx, column=c_idx, value=value)
# openpyxl格式设置
from openpyxl.styles import Font, Alignment
# 设置标题格式
for cell in ws[1]:
cell.font = Font(bold=True, color="FFFFFF")
# 设置数字格式
for row in ws.iter_rows(min_row=2):
for cell in row[2:4]: # 数值列
cell.number_format = '#,##0.00'
# 删除默认创建的空工作表
if 'Sheet' in wb.sheetnames:
std = wb['Sheet']
wb.remove(std)
wb.save(output_file)
return output_file
总结
openpyxl更适合：需要对Excel文件进行精细控制、格式设置、公式操作等场景
pandas更适合：需要进行数据清洗、转换、分析和复杂计算的场景
结合使用：先用pandas处理数据，再用openpyxl设置格式，发挥各自优势
根据具体需求选择合适的工具，或者结合两者优势，可以高效地处理Excel多工作表任务。
