Python ExcelWriter嵌套循环生成多机构多工作表工作簿时文件空白问题求助

It’s frustrating when your code generates the right DataFrames but fails to save them to Excel—especially when the single-agency version works perfectly. Let’s break down what’s going on and fix this issue.

The Likely Culprit: Manual save() vs. Proper Resource Management #

Your original single-agency code works because you’re only creating one ExcelWriter instance, and the save() call reliably commits data to disk. But in nested loops, relying on manual save() can lead to unflushed buffers or incomplete resource cleanup. The xlsxwriter engine sometimes needs explicit lifecycle handling to ensure all data is written properly.

The Fix: Use a with Statement for ExcelWriter #

The best way to guarantee your ExcelWriter behaves correctly (and data gets saved) is to wrap it in a with block. This automatically handles saving and closing the writer when you’re done with it, even if unexpected issues pop up in the loop.

Here’s your revised nested loop code with this fix, plus readability improvements:

import os
import pandas as pd

# Your agency list (cleaned up for clarity)
agency_names = pd.DataFrame(data={'Agency Name': ['Example 1', 'Example 2', 'Example 3']})
input_directory = "your_input_path_here"

# Loop directly over agency names instead of using range(len())
for current_agency in agency_names['Agency Name']:
    output_file = f"path/{current_agency} HoH Errors 2022.xlsx"
    
    # Use with to manage the ExcelWriter lifecycle automatically
    with pd.ExcelWriter(output_file, engine='xlsxwriter') as writer:
        for filename in os.listdir(input_directory):
            file_path = os.path.join(input_directory, filename)
            df = pd.read_csv(file_path)
            
            # Filter for the current agency
            filtered_df = df[df['Agency Name'] == current_agency]
            
            # Skip if no matching data in this CSV
            if filtered_df.empty:
                continue
            
            # Create a clean sheet name (remove .csv extension)
            sheet_name = filename.replace('.csv', '')
            # Write filtered data to the worksheet
            filtered_df.to_excel(writer, sheet_name=sheet_name, index=False)
# The with block automatically saves and closes the writer here

Additional Checks to Rule Out Edge Cases #

If you still hit issues, verify these details:

• Sheet Name Length: Excel limits worksheet names to 31 characters. If your cleaned filename exceeds this, xlsxwriter will fail silently. Truncate names if needed: sheet_name = filename.replace('.csv', '')[:31].
• File Permissions: Ensure the output directory allows write access. If your code can’t write to the path, it might create empty files instead of throwing an error.
• Data Type Mismatch: Double-check that df['Agency Name'] and current_agency are both string types—mismatched types could cause the filter to fail unexpectedly (even if you think the DataFrame is correct).

Why This Works #

The with statement guarantees the ExcelWriter object is properly finalized: it flushes all buffered data to the file and closes the file handle. In your original code, even though you called writer.save(), nested loops can sometimes leave the writer in an unstable state where not all data is committed to disk.

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