如何基于列表导入多个CSV文件并追加至多个工作表

问题原因 #

1. 每次循环创建pd.ExcelWriter会覆盖目标Excel文件，导致最终只保留最后一个工作表。
2. 使用mode='a'报错是因为：
   • 目标文件不存在时，默认引擎（xlsxwriter）不支持创建并追加；
   • 即使文件存在，也需要指定engine='openpyxl'才能实现追加（openpyxl是唯一支持追加工作表的引擎）。
3. 手动列出csv路径效率低，可通过批量扫描文件实现自动化。

优化后的代码 #

import numpy as np
import pandas as pd
import os
import glob

# 目标Excel文件路径
pathDestination = 'Downloads/TemplateOne.xlsx'
# 批量获取所有CSV文件（可根据需要修改路径，比如'./data/*.csv'）
csvpath_list = glob.glob('*.csv')

# 判断目标文件是否存在，决定写入模式和引擎
if os.path.exists(pathDestination):
    writer = pd.ExcelWriter(pathDestination, mode='a', engine='openpyxl', if_sheet_exists='replace')
else:
    writer = pd.ExcelWriter(pathDestination, engine='openpyxl')

with writer:
    for csvpath in csvpath_list:
        # 读取CSV文件
        df = pd.read_csv(csvpath)
        
        # 数据转换
        conversion_unit = 1000
        df['quantity_converted'] = df['Quantity'] * conversion_unit
        df['supplimentry_quantity_converted'] = df['Supplimentary Quantity'] * conversion_unit
        
        # 获取工作表名称（去掉.csv后缀）
        sheet_name = os.path.splitext(os.path.basename(csvpath))[0]
        
        # 写入工作表
        df.to_excel(writer, sheet_name=sheet_name, index=False)

关键说明 #

• 批量获取CSV：用glob.glob('*.csv')自动扫描当前目录下所有CSV文件，如需指定路径，修改为glob.glob('指定目录/*.csv')即可。
• 写入模式处理：
  • 文件存在时用mode='a'+engine='openpyxl'实现追加；if_sheet_exists='replace'表示如果已有同名工作表则覆盖，改为'new'则会自动给工作表名加后缀（如xxx_1）避免冲突。
  • 文件不存在时直接创建新文件。
• 高效写入：将pd.ExcelWriter的上下文管理器放在循环外，仅打开一次文件，避免重复覆盖和IO损耗。

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