基于OpenPyXL实现批量将数据Excel内容复制到对应汇总Excel指定工作表的技术求助

Got it, let's turn your single-file script into a batch processor that handles all 140 file pairs automatically. Here's a step-by-step solution tailored to your needs:

First, we'll leverage Python's os module to traverse your folders, match up the corresponding files, and reuse your existing copy logic for each pair. We'll also add basic error handling to catch missing files or unexpected issues.

Batch Processing Script #

import openpyxl as xl
import os

# Define your folder paths (update these to match your actual directories)
DATA_FILES_DIR = "D:\\1. Python Extracts"
SUMMARY_FILES_DIR = "D:\\2. Summary shees"

# Loop through every file in the data files folder
for data_filename in os.listdir(DATA_FILES_DIR):
    # Skip any non-XLSX files to avoid errors
    if not data_filename.lower().endswith(".xlsx"):
        continue
    
    # Build full paths for both the data file and its matching summary file
    data_file_path = os.path.join(DATA_FILES_DIR, data_filename)
    summary_file_path = os.path.join(SUMMARY_FILES_DIR, data_filename)
    
    # Check if the corresponding summary file exists before proceeding
    if not os.path.exists(summary_file_path):
        print(f"Warning: Could not find summary file for {data_filename} — skipping this file.")
        continue
    
    try:
        # Load the source data workbook and its first worksheet
        source_wb = xl.load_workbook(data_file_path)
        source_ws = source_wb.worksheets[0]
        
        # Load the target summary workbook and its second worksheet
        target_wb = xl.load_workbook(summary_file_path)
        target_ws = target_wb.worksheets[1]
        
        # Get total rows and columns from the source sheet
        total_rows = source_ws.max_row
        total_cols = source_ws.max_column
        
        # Copy data from source to target (starting at row 2 of the target sheet)
        for row in range(1, total_rows + 1):
            for col in range(1, total_cols + 1):
                source_cell_value = source_ws.cell(row=row, column=col).value
                # Write to row row+1 to skip the target sheet's first row
                target_ws.cell(row=row + 1, column=col).value = source_cell_value
        
        # Save the updated summary file
        target_wb.save(summary_file_path)
        print(f"Successfully processed: {data_filename}")
    
    except Exception as e:
        print(f"Error processing {data_filename}: {str(e)}")

Key Features & Notes: #

• Automatic File Matching: Relies on your one-to-one filename pairing to find the right summary file for each data file
• Error Resilience: Skips non-Excel files, warns about missing summary files, and catches unexpected errors to keep the batch running
• Scalability: Works for any number of matching file pairs (not just 140)
• Clear Feedback: Prints status updates so you can track which files were processed successfully or failed

How to Use: #

1. Update DATA_FILES_DIR and SUMMARY_FILES_DIR to match your actual folder paths
2. Ensure all data files have exact name matches with their corresponding summary files
3. Run the script — it will process every valid pair and print updates as it goes

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