如何将含多工作表的XLSX文件转为CSV？Pandas转换遇异常求解

问题根源 #

你的情况大概率是因为复杂Excel里存在合并单元格、不规则表头、隐藏行列或空值区域，pandas默认读取规则会把这些无效内容一并读入，导致NaN、无名列和数据错位。

一、精准读取Excel（从源头避免混乱） #

1. 指定有效表头与行列
   先打开Excel确认实际表头所在行，以及有效数据的列范围，读取时用参数限定：

   import pandas as pd
   # 假设表头在第2行，有效列是A到G
   df = pd.read_excel('your_file.xlsx', sheet_name='Sheet1', header=1, usecols='A:G')
   

   要是表头有空格或无效字符，读完后直接重命名：

   df.columns = ['订单号', '客户名称', '金额', ...]  # 手动对应实际列名
   

2. 预处理合并单元格
   合并单元格是数据错位的重灾区，先用openpyxl把合并值填充到所有单元格：

   from openpyxl import load_workbook
   
   wb = load_workbook('your_file.xlsx')
   ws = wb['Sheet1']
   # 遍历所有合并单元格区域
   for merged_range in ws.merged_cells.ranges:
       # 获取合并区域左上角的值
       top_left_val = ws.cell(merged_range.min_row, merged_range.min_col).value
       # 填充到合并区域内的所有单元格
       for row in range(merged_range.min_row, merged_range.max_row + 1):
           for col in range(merged_range.min_col, merged_range.max_col + 1):
               ws.cell(row, col).value = top_left_val
   # 保存预处理后的文件
   wb.save('processed_file.xlsx')
   

   之后用pandas读取这个预处理后的文件，就能避免合并单元格导致的空值错位。

二、清理数据后导出CSV #

读取完成后，先清理无效内容再导出：

# 删掉全空的行和列
df = df.dropna(how='all', axis=0).dropna(how='all', axis=1)
# 把NaN替换为空字符串（避免导出后出现NaN字样）
df = df.fillna('')
# 导出CSV，禁用索引（避免多出一列）
df.to_csv('cleaned_sheet.csv', index=False, encoding='utf-8-sig')

三、批量处理多工作表 #

要是有多个工作表，写个循环批量处理：

def process_single_sheet(sheet_name, input_xlsx):
    # 预处理合并单元格
    wb = load_workbook(input_xlsx)
    ws = wb[sheet_name]
    for merged_range in ws.merged_cells.ranges:
        tl_val = ws.cell(merged_range.min_row, merged_range.min_col).value
        for r in range(merged_range.min_row, merged_range.max_row+1):
            for c in range(merged_range.min_col, merged_range.max_col+1):
                ws.cell(r, c).value = tl_val
    temp_file = f'temp_{sheet_name}.xlsx'
    wb.save(temp_file)
    
    # 读取、清理、导出
    df = pd.read_excel(temp_file, sheet_name=sheet_name, header=1)  # 调整header为实际表头行
    df = df.dropna(how='all', axis=0).dropna(how='all', axis=1)
    df.columns = [str(col).strip() for col in df.columns]
    df.fillna('').to_csv(f'{sheet_name}.csv', index=False, encoding='utf-8-sig')

# 遍历所有工作表
wb = load_workbook('your_file.xlsx', read_only=True)
for sheet in wb.sheetnames:
    process_single_sheet(sheet, 'your_file.xlsx')

四、更高效的方案：跳过CSV直接导入PostgreSQL #

其实没必要转CSV，清理完DataFrame后直接用SQLAlchemy导入数据库，减少中间环节：

from sqlalchemy import create_engine

# 创建PostgreSQL连接（替换成你的数据库信息）
engine = create_engine('postgresql://username:password@host:port/db_name')

# 导入数据，if_exists可选'replace'/'append'/'fail'
df.to_sql('your_table_name', engine, if_exists='replace', index=False)

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