如何用Python根据DataFrame列表批量重命名300个Excel文件？

Hey there! Let's tackle this step by step—first fixing the single-file rename issue, then scaling it up to loop through all 300 of your Excel files. Let's start with the basics.

First: Diagnose the Single-File Rename Failure #

Before adding loop logic, let's make sure we can reliably rename one file. Here are the most common pitfalls to check, plus a testable code snippet:

Key Checks to Run #

• Path Accuracy: Are you using absolute paths (e.g., C:/Users/You/Files/old.xlsx) instead of relative paths that might point to the wrong folder? Use os.path.abspath() to verify your file paths.
• List Reading: Are you actually pulling the correct old/new filename pairs from your specified worksheet? Print the values to confirm they match your Excel files.
• File Permissions: Is the file open in Excel (or another program)? That will block renaming. Also, ensure you have write access to the folder.
• File Extensions: Don't forget to append .xlsx/.xls to your new filename—otherwise you'll lose the file format.

Test Code for Single File Rename #

This uses pandas to read your rename list worksheet, then targets one file to test:

import os
import pandas as pd

# 1. Load your rename list from the specified worksheet
rename_list = pd.read_excel("your_rename_list_file.xlsx", sheet_name="SheetNameWithList")

# 2. Grab the first row's old/new names (adjust column names to match your sheet)
old_file = rename_list.iloc[0]["OriginalFileNameColumn"]
new_file = rename_list.iloc[0]["NewFileNameColumn"] + ".xlsx"  # Add extension!

# 3. Build full file paths (avoids messy string concatenation errors)
file_folder = "path/to/your/excel/files"
old_path = os.path.join(file_folder, old_file)
new_path = os.path.join(file_folder, new_file)

# 4. Attempt rename with error handling to see what's breaking
if os.path.exists(old_path):
    try:
        os.rename(old_path, new_path)
        print(f"Success! Renamed {old_file} to {new_file}")
    except Exception as e:
        print(f"Failed to rename: {str(e)}")  # This will tell you exactly what's wrong
else:
    print(f"Error: File not found at {old_path}")

Second: Scale to Loop Through All Files #

Once the single-file test works, expand it to loop through every entry in your rename list. This version includes robust error handling to catch common issues like missing files or duplicate names:

import os
import pandas as pd

# Configure your settings here
RENAME_LIST_PATH = "your_rename_list_file.xlsx"
TARGET_SHEET = "SheetNameWithList"
EXCEL_FILES_FOLDER = "path/to/your/excel/files"

# Load the full rename list
rename_df = pd.read_excel(RENAME_LIST_PATH, sheet_name=TARGET_SHEET)

# Loop through each row in the rename list
for index, row in rename_df.iterrows():
    old_filename = row["OriginalFileNameColumn"]
    new_filename = row["NewFileNameColumn"] + ".xlsx"  # Adjust extension if needed
    
    # Build full paths
    old_full_path = os.path.join(EXCEL_FILES_FOLDER, old_filename)
    new_full_path = os.path.join(EXCEL_FILES_FOLDER, new_filename)
    
    # Skip if original file doesn't exist
    if not os.path.exists(old_full_path):
        print(f"⚠️ Skipping: File not found - {old_filename}")
        continue
    
    # Attempt rename with specific error handling
    try:
        os.rename(old_full_path, new_full_path)
        print(f"✅ Success: {old_filename} → {new_filename}")
    except PermissionError:
        print(f"❌ Failed: {old_filename} is open in another program (like Excel)")
    except FileExistsError:
        print(f"❌ Failed: A file named {new_filename} already exists")
    except Exception as e:
        print(f"❌ Failed: {old_filename} - Error: {str(e)}")

Bonus: If Files Are in Subfolders #

If your 300 Excel files are spread across subfolders, add os.walk() to find them all:

# Replace the loop above with this to search subfolders
for root, dirs, files in os.walk(EXCEL_FILES_FOLDER):
    for file in files:
        # Check if this file is in our rename list
        match = rename_df[rename_df["OriginalFileNameColumn"] == file]
        if not match.empty:
            old_path = os.path.join(root, file)
            new_name = match.iloc[0]["NewFileNameColumn"] + ".xlsx"
            new_path = os.path.join(root, new_name)
            
            # Run the same rename logic as above here
            try:
                os.rename(old_path, new_path)
                print(f"✅ Renamed in subfolder: {file} → {new_name}")
            except Exception as e:
                print(f"❌ Failed subfolder rename: {file} - {str(e)}")

Critical Precaution #

Backup all your files before running the full loop! Even with error handling, it's easy to make a typo in column names or paths—having a backup ensures you don't lose data.

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