Pandas合并Excel多工作表、拆分导出CSV及自动化相关问题咨询

1. 导出为CSV格式 #

直接调用DataFrame内置的to_csv方法即可实现，指定编码参数避免乱码：

# utf-8-sig编码适配全平台，用Excel打开CSV不会出现乱码
Pipeline_df.to_csv('Pipeline_output.csv', encoding='utf-8-sig', index=False)

2. 按100万行自动拆分导出Excel #

Excel单工作表最大支持1048576行，按100万行拆分可避免超出行数限制，实现逻辑如下：

import math
# 单Excel文件最大行数
max_rows = 1000000
# 计算需要拆分的文件总数
total_file = math.ceil(len(Pipeline_df) / max_rows)

for i in range(total_file):
    # 切割当前批次数据
    split_df = Pipeline_df[i*max_rows : (i+1)*max_rows]
    # 按序号生成文件名导出
    split_df.to_excel(f'Pipeline_output_{i+1}.xlsx', index=False, engine='xlsxwriter')

3. 全流程自动化实现 #

可通过封装函数+循环实现从读取合并到导出的全流程自动化，无需手动分步操作，完整代码如下：

# 依赖安装（Jupyter中执行需加!前缀：!pip install pandas xlsxwriter openpyxl）
import pandas as pd
import math

def auto_process_excel(input_file, output_prefix, split_row=1000000, export_csv=True):
    # 读取所有工作表并合并
    sheet_dict = pd.read_excel(input_file, sheet_name=None)
    merged_df = pd.concat(sheet_dict.values(), ignore_index=True)
    
    # 导出CSV
    if export_csv:
        merged_df.to_csv(f'{output_prefix}.csv', encoding='utf-8-sig', index=False)
    
    # 拆分导出Excel
    total_file = math.ceil(len(merged_df)/split_row)
    for i in range(total_file):
        split_df = merged_df[i*split_row : (i+1)*split_row]
        split_df.to_excel(f'{output_prefix}_{i+1}.xlsx', index=False, engine='xlsxwriter')
    print(f'处理完成，共生成{total_file}个Excel文件、1个CSV文件')

# 直接调用即可完成全流程
auto_process_excel(input_file='PandasToExcel.xlsx', output_prefix='Pipeline_output')

4. 异常问题修复 #

xlsx文件无法打开的原因 #

你原有代码仅创建了ExcelWriter写入对象，没有将合并后的DataFrame写入到对象中，生成的是空文件所以无法打开，正确的写入写法：

# 简化写法
Pipeline_df.to_excel('Pipeline_output.xlsx', index=False, engine='xlsxwriter')

# 等价的ExcelWriter写法
with pd.ExcelWriter('Pipeline_output.xlsx', engine='xlsxwriter') as writer:
    Pipeline_df.to_excel(writer, index=False)

CSV导出乱码、内容不符的原因 #

是编码参数设置错误导致的，导出时指定encoding='utf-8-sig'即可适配Windows、Mac多平台，同时加上index=False避免导出多余的索引列。

内容的提问来源于stack exchange，提问作者Joshua3535Econ