合并多个Excel文件时的文件损坏与扩展名问题（Python实现故障排查）

Hey there! Let's figure out why your Excel merge script worked once but now spits out broken 0KB files that Excel can't open. I've gone through your code and spotted a few key issues causing this, plus easy fixes to get you back on track.

1. Missing Full File Paths (The Big Culprit!) #

Your code grabs filenames with os.listdir(cwd), but when you pass file to pd.ExcelFile(), it looks for the file in your current working directory—not the July folder you specified. The first run probably worked because your terminal was already in that folder, but subsequent runs might have switched directories, leading Pandas to fail finding files. This leaves df_total empty, so when you write it to Excel, you get a 0KB broken file.

Fix: Always use full file paths by combining the directory and filename with os.path.join():

file_path = os.path.join(cwd, file)
excel_file = pd.ExcelFile(file_path)

2. Outdated append() Method #

Pandas has deprecated DataFrame.append()—while it might work for small datasets, it’s unstable for merging 30 files and can lead to memory leaks or unexpected empty data. Switch to pd.concat() instead, which is designed for combining multiple DataFrames efficiently.

Fix: Store each sheet’s DataFrame in a list, then concatenate them all at once:

df_list = []
# ... inside your loops ...
df_list.append(df)
# ... after processing all files ...
df_total = pd.concat(df_list, ignore_index=True)

3. No Error Handling #

If even one Excel file is corrupted, unreadable, or has a weird format, your script will crash mid-run. When this happens, df_total might be empty, resulting in that broken 0KB file. Adding try/except blocks lets you skip bad files and see exactly what went wrong.

Fix: Wrap your file processing in a try/except block to catch errors:

try:
    excel_file = pd.ExcelFile(file_path)
    # ... process sheets ...
except Exception as e:
    print(f"Oops, couldn't process {file}: {str(e)}")

4. Unspecified Excel Engine #

Sometimes Pandas’ default Excel writer can cause compatibility issues, especially if you’re switching between .xls and .xlsx formats. Specifying an explicit engine (openpyxl for .xlsx, xlwt for .xls) avoids format-related corruption.

Fix: Add the engine parameter when writing the merged file:

df_total.to_excel('combined_file.xlsx', engine='openpyxl', index=False)

Note: You might need to install openpyxl first with pip install openpyxl if you haven’t already.

Here’s your updated script with all the fixes applied:

import os
import pandas as pd

def main():
    cwd = os.path.abspath('C:\\Users\\Documents\\Reports\\July')
    files = os.listdir(cwd)
    df_list = []
    
    for file in files:
        # Support both .xlsx and .xls formats
        if file.endswith(('.xlsx', '.xls')):
            file_path = os.path.join(cwd, file)
            try:
                excel_file = pd.ExcelFile(file_path)
                for sheet in excel_file.sheet_names:
                    df = excel_file.parse(sheet_name=sheet)
                    df_list.append(df)
                print(f"Successfully processed: {file}")
            except Exception as e:
                print(f"Failed to process {file}: {str(e)}")
    
    if df_list:
        df_total = pd.concat(df_list, ignore_index=True)
        # Write with explicit engine, skip index column to reduce clutter
        df_total.to_excel('combined_file.xlsx', engine='openpyxl', index=False)
        print(f"Merge done! Total rows in combined file: {len(df_total)}")
    else:
        print("No valid Excel files found to merge.")

if __name__ == "__main__":
    main()

• Close Excel first: Make sure no Excel files (including the merged one) are open when you run the script—file locks can cause write failures.
• Backup originals: Always keep a copy of your 30 Excel files before running merge scripts, just in case.
• Check memory: If you’re dealing with super large datasets, consider adding dtype specifications when parsing sheets to reduce memory usage.

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