循环读取多Excel文件合并后数据间空行问题的解决方法

问题说明 #

使用以下脚本合并多个Excel文件生成finalfile4.xlsx时，不同文件的数据间存在大量空行（仅Qty Received列值为0），数据未连续堆叠，下一个文件的数据需跳至第10982行才显示。原脚本代码：

path ="C:\Users\Adam\Desktop\Stock Trackers\"

excel_file_list = os.listdir(path)


finalDf = pd.DataFrame()
for file in excel_file_list:
 #if excel_files.startswith("Stock"): 
    df = pd.read_excel(path+file,sheet_name="Main",usecols="A:D,R")
    df['Qty Received']=df['Total Received']
    df = df.drop('Total Received', axis=1)
    df['InvoicedValue'] = df['Price']*df['Qty Invoiced']
    df['ReceivedValue'] = df['Price']*df['Qty Received']
    df['DeltaQty']= df['Qty Received']-df['Qty Invoiced']
    df['DeltaValue']= df['ReceivedValue']-df['InvoicedValue']
    finalDf = pd.concat([finalDf, df])


finalDf.to_excel("finalfile4.xlsx")

原因分析 #

空行是因为读取Excel时，原文件中存在的大量空白行被读入DataFrame，合并后这些空行被保留在不同文件数据之间。

修改方案 #

读取每个文件后，先过滤无效空行，再执行后续计算与合并操作，同时优化路径拼接方式避免潜在错误。

修改后的代码：

import os
import pandas as pd

path = r"C:\Users\Adam\Desktop\Stock Trackers\"

# 仅筛选Excel格式文件，排除目录中其他类型文件
excel_file_list = [f for f in os.listdir(path) if f.endswith(('.xlsx', '.xls'))]

finalDf = pd.DataFrame()
for file in excel_file_list:
    # 用os.path.join规范拼接路径，避免转义字符问题
    file_path = os.path.join(path, file)
    df = pd.read_excel(file_path, sheet_name="Main", usecols="A:D,R")
    
    # 过滤所有列均为空的无效行，也可根据业务逻辑针对关键列（如Qty Invoiced）过滤
    df = df.dropna(how='all')
    
    df['Qty Received'] = df['Total Received']
    df = df.drop('Total Received', axis=1)
    df['InvoicedValue'] = df['Price'] * df['Qty Invoiced']
    df['ReceivedValue'] = df['Price'] * df['Qty Received']
    df['DeltaQty'] = df['Qty Received'] - df['Qty Invoiced']
    df['DeltaValue'] = df['ReceivedValue'] - df['InvoicedValue']
    
    # 合并时重置索引，避免索引混乱
    finalDf = pd.concat([finalDf, df], ignore_index=True)

# 导出时不生成索引列，减少冗余
finalDf.to_excel("finalfile4.xlsx", index=False)

关键修改点 #

• 用os.path.join拼接路径，解决Windows路径转义问题
• 筛选目录中的Excel文件，避免非Excel文件被错误读取
• 读取后用dropna(how='all')过滤全空行，可根据实际业务调整过滤规则
• 合并时添加ignore_index=True，重置合并后DataFrame的索引
• 导出时设置index=False，避免生成多余的索引列

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