为什么需要合并Excel工作表？

在日常工作中，我们经常遇到需要处理多个Excel文件或工作表的情况：

• 每月/每周的数据分散在多个文件中
• 不同部门提供的数据需要整合
• 多个系统导出的数据需要统一分析
• 历史数据需要合并进行长期趋势分析

手动合并不仅费时费力，还容易出错。使用Python可以自动化完成这些重复性工作，大大提高效率。

准备工作

1. 安装必要的库

使用pip安装pandas和openpyxl：

pip install pandas openpyxl

2. 导入所需模块

import pandas as pd
import os
from glob import glob

工作表合并方法

1. 合并同一工作簿中的多个工作表

当所有需要合并的工作表都在同一个Excel文件中时：

def merge_sheets_in_workbook(file_path):
    # 读取Excel文件中的所有工作表
    xls = pd.ExcelFile(file_path)
    # 创建一个空的DataFrame用于存储合并后的数据
    combined_df = pd.DataFrame()
    
    # 遍历所有工作表
    for sheet_name in xls.sheet_names:
        # 读取当前工作表
        df = pd.read_excel(file_path, sheet_name=sheet_name)
        # 添加一列记录原始工作表名称
        df['来源工作表'] = sheet_name
        # 将当前工作表数据添加到合并的DataFrame
        combined_df = pd.concat([combined_df, df], ignore_index=True)
    
    return combined_df

# 使用示例
result = merge_sheets_in_workbook('销售数据.xlsx')
result.to_excel('合并后的销售数据.xlsx', index=False)

2. 合并不同工作簿中的工作表

当需要合并多个Excel文件中的特定工作表时：

def merge_workbooks(folder_path, sheet_name='Sheet1'):
    # 获取文件夹中所有的Excel文件
    excel_files = glob(os.path.join(folder_path, '*.xlsx'))
    
    # 创建一个空的DataFrame用于存储合并后的数据
    combined_df = pd.DataFrame()
    
    # 遍历所有Excel文件
    for file in excel_files:
        # 读取当前文件
        df = pd.read_excel(file, sheet_name=sheet_name)
        # 添加一列记录原始文件名
        df['来源文件'] = os.path.basename(file)
        # 将当前文件数据添加到合并的DataFrame
        combined_df = pd.concat([combined_df, df], ignore_index=True)
    
    return combined_df

# 使用示例
result = merge_workbooks('月度销售数据', sheet_name='销售记录')
result.to_excel('年度销售数据.xlsx', index=False)

3. 高级合并：处理不同结构的工作表

当工作表结构不完全相同时，需要更智能的合并方法：

def smart_merge(folder_path):
    # 获取文件夹中所有的Excel文件
    excel_files = glob(os.path.join(folder_path, '*.xlsx'))
    
    # 存储所有数据框
    all_dfs = []
    
    # 遍历所有Excel文件
    for file in excel_files:
        # 读取Excel文件中的所有工作表
        xls = pd.ExcelFile(file)
        # 遍历所有工作表
        for sheet_name in xls.sheet_names:
            # 读取当前工作表
            df = pd.read_excel(file, sheet_name=sheet_name)
            # 添加来源信息
            df['来源文件'] = os.path.basename(file)
            df['来源工作表'] = sheet_name
            # 添加到列表
            all_dfs.append(df)
    
    # 合并所有数据框，自动处理列名不一致的情况
    combined_df = pd.concat(all_dfs, sort=False, ignore_index=True)
    
    return combined_df

# 使用示例
result = smart_merge('多部门数据')
result.to_excel('公司总数据.xlsx', index=False)

常见问题与解决方案

# 使用chunksize参数分批读取
chunk_size = 10000
chunks = []

for file in excel_files:
    for chunk in pd.read_excel(file, chunksize=chunk_size):
        chunks.append(chunk)

combined_df = pd.concat(chunks, ignore_index=True)

# 方法1：重命名列
df.rename(columns={'销售金额': '销售额', '客户': '客户名称'}, inplace=True)

# 方法2：只选择需要的列
required_columns = ['日期', '产品', '销售额']
df = df[required_columns]

# 转换日期列
df['日期'] = pd.to_datetime(df['日期'], errors='coerce')

# 转换数值列
df['销售额'] = pd.to_numeric(df['销售额'], errors='coerce')

# 填充缺失值
df.fillna({'地区': '未知', '销售额': 0}, inplace=True)

最佳实践与优化建议