Pandas(Python) 合并多xlsx为单文件多工作表路径报错处理

问题根因 #

你的两版脚本分别存在几个明确的语法和逻辑错误：

• 第一版错误使用pd.read_csv()读取xlsx格式文件，这个接口仅支持读取csv文本格式，读取Excel需要用pd.read_excel()
• 第二版的路径拼接逻辑错误：os.listdir(dest)仅返回目标目录下的文件名，不包含父路径，直接调用open(file)会在你当前运行脚本的工作目录下找文件，自然触发FileNotFoundError
• 额外逻辑错误：xlsx是二进制格式文件，不需要用Python原生open()打开后再给pandas处理，pd.read_excel()可以直接接收完整文件路径读取；且原生open返回的文件对象不是pandas DataFrame，根本无法调用to_excel()方法。
• 依赖提醒：pandas读写xlsx格式需要openpyxl库作为引擎，先执行安装命令：

pip install openpyxl

可直接运行的修正代码 #

如果你的目录下固定只有SvnUsers.xlsx和SvnGroupMembership.xlsx两个文件，直接用下面的代码即可：

import pandas as pd
from timestampdirectory import createdir
import os

# 获取时间戳目录的绝对路径
dest = createdir()

# 拼接完整文件路径，用pd.read_excel读取为DataFrame
df_users = pd.read_excel(os.path.join(dest, "SvnUsers.xlsx"), engine="openpyxl")
df_group = pd.read_excel(os.path.join(dest, "SvnGroupMembership.xlsx"), engine="openpyxl")

# 写入目标文件，用with上下文自动处理关闭逻辑，避免文件损坏
with pd.ExcelWriter("Usage-SvnAnalysis.xlsx", engine="openpyxl") as xlwriter:
    df_users.to_excel(xlwriter, sheet_name="SvnUsers", index=False)
    df_group.to_excel(xlwriter, sheet_name="SvnGroupMembership", index=False)

扩展：自动遍历目录下所有xlsx文件 #

如果后续时间戳目录下会新增更多xlsx文件，不需要硬编码文件名，可以自动遍历所有文件生成对应工作表，工作表名默认取xlsx的文件名（不含后缀）：

import pandas as pd
from timestampdirectory import createdir
import os

dest = createdir()

with pd.ExcelWriter("Usage-SvnAnalysis.xlsx", engine="openpyxl") as xlwriter:
    for file_name in os.listdir(dest):
        # 只处理xlsx文件，跳过子目录和其他格式文件
        if file_name.endswith(".xlsx") and os.path.isfile(os.path.join(dest, file_name)):
            # 文件名去后缀作为工作表名
            sheet_name = os.path.splitext(file_name)[0]
            df = pd.read_excel(os.path.join(dest, file_name), engine="openpyxl")
            df.to_excel(xlwriter, sheet_name=sheet_name, index=False)

注意：如果你的createdir()每次调用都会新建一个带新时间戳的目录，要确认调用时所有待合并的xlsx文件已经写入完成，避免读到空目录。

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