Got it, let's tweak your code to clear cell values instead of deleting columns, and scale it to handle all your Excel files in one go. Here's a practical, step-by-step solution:

Step 1: Fix Single-File Logic to Clear Values #

First, let's adjust your original code to target cell values instead of deleting columns. With openpyxl, we can loop through every cell in your specified range (E1:G200) and set its value to empty—no column deletion required:

from openpyxl import load_workbook

# Load the workbook
book = load_workbook('file1.xlsx')
sheet = book['sheet1']

# Define your target range: E1 to G200 (columns 5-7, rows 1-200; openpyxl uses 1-indexed columns)
start_col, end_col = 5, 7
start_row, end_row = 1, 200

# Clear values for each cell in the range
for row in range(start_row, end_row + 1):
    for col in range(start_col, end_col + 1):
        sheet.cell(row=row, column=col).value = None  # Use "" instead of None if you prefer empty strings

# Save the modified file (never overwrite your original!)
book.save('file1_modified.xlsx')

Step 2: Scale to Batch Process All Files #

Now let's wrap this logic in a loop to handle every Excel file in a folder. We'll use os to scan files, filter for valid Excel files, and process each one individually:

import os
from openpyxl import load_workbook

# Configuration - adjust these to match your needs
TARGET_FOLDER = "path/to/your/excel/files"  # Replace with your actual folder path
TARGET_SHEET = "sheet1"                     # Name of the worksheet to modify
START_COL, END_COL = 5, 7                   # Columns E to G (1-indexed)
START_ROW, END_ROW = 1, 200                 # Rows 1 to 200
OUTPUT_SUFFIX = "_modified"                 # Suffix to add to modified files (avoids overwriting originals)

# Loop through all files in the target folder
for filename in os.listdir(TARGET_FOLDER):
    # Skip non-Excel files and Excel's temporary hidden files
    if filename.endswith(".xlsx") and not filename.startswith("~$"):
        file_path = os.path.join(TARGET_FOLDER, filename)
        
        try:
            # Load the workbook
            book = load_workbook(file_path)
            
            # Check if the target sheet exists
            if TARGET_SHEET not in book.sheetnames:
                print(f"Skipping {filename}: Worksheet '{TARGET_SHEET}' not found")
                continue
            
            sheet = book[TARGET_SHEET]
            
            # Clear values in the specified range
            for row in range(START_ROW, END_ROW + 1):
                for col in range(start_col, end_col + 1):
                    sheet.cell(row=row, column=col).value = None
            
            # Generate a safe output filename
            name, ext = os.path.splitext(filename)
            output_filename = f"{name}{OUTPUT_SUFFIX}{ext}"
            output_path = os.path.join(TARGET_FOLDER, output_filename)
            
            # Save the modified file
            book.save(output_path)
            print(f"Successfully processed: {filename} → {output_filename}")
        
        except Exception as e:
            print(f"Error processing {filename}: {str(e)}")

Key Details to Note: #

• 1-indexed Columns: Openpyxl counts columns starting at 1, so E = 5, G = 7. Adjust these numbers if your target range changes.
• No Overwrites: The code adds a _modified suffix to each saved file, so your original data stays intact.
• Error Resilience: The try/except block catches issues like corrupted files or missing worksheets, ensuring the batch job doesn't crash halfway.
• Skip Temp Files: The check for not filename.startswith("~$") avoids Excel's temporary hidden files, which can cause loading errors.

This script will process every valid Excel file in your target folder, clear the specified cell values in the correct worksheet, and save each modified file separately—exactly what you need!

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