使用Python的openpyxl修改Excel行列并添加运动标识的技术咨询

First off, let's break this down into manageable steps—from setting up your tools to writing a script that can handle thousands of Excel files efficiently.

1. Prerequisites #

Make sure you have openpyxl installed, since it's the go-to library for modifying Excel files in Python:

pip install openpyxl

2. Define Your Exercise-to-Identifier Mapping #

First, create a dictionary that maps each exercise to its corresponding identifier. Since you have ~20 types, expand this to cover all your cases:

EXERCISE_TAGS = {
    # Cardio (C)
    "跑步": "(C)",
    "步行": "(C)",
    "游泳": "(C)",
    "骑自行车": "(C)",
    # Strength (S)
    "深蹲": "(S)",
    "弯举": "(S)",
    "卧推": "(S)",
    "硬拉": "(S)",
    # Add your remaining 12+ types here...
    "平衡训练": "(B)",
    "拉伸": "(F)"
}

3. Core Function: Process a Single Excel File #

This function will load an Excel file, locate the "运动" (Exercise) column, and append the correct identifier to each exercise entry:

from openpyxl import load_workbook

def process_single_excel(file_path):
    try:
        # Load the workbook (read-only mode isn't needed here since we're modifying)
        wb = load_workbook(file_path)
        sheet = wb.active  # Assumes the data is in the first sheet; adjust if needed
        
        # Find the header row and "运动" column index
        exercise_col = None
        header_row = None
        
        # Check first 5 rows to find the header (in case of extra empty rows at top)
        for row in range(1, 6):
            for col in range(1, sheet.max_column + 1):
                cell_value = sheet.cell(row=row, column=col).value
                if cell_value == "运动":
                    exercise_col = col
                    header_row = row
                    break
            if exercise_col is not None:
                break
        
        if exercise_col is None:
            print(f"Warning: Could not find '运动' column in {file_path}")
            return
        
        # Iterate through all rows below the header
        for row in range(header_row + 1, sheet.max_row + 1):
            exercise_cell = sheet.cell(row=row, column=exercise_col)
            exercise_name = exercise_cell.value
            
            if exercise_name and exercise_name in EXERCISE_TAGS:
                # Append the identifier to the exercise name
                exercise_cell.value = f"{exercise_name} {EXERCISE_TAGS[exercise_name]}"
        
        # Save the modified file (overwrites original; make backups first!)
        wb.save(file_path)
        print(f"Successfully processed: {file_path}")
    
    except Exception as e:
        print(f"Error processing {file_path}: {str(e)}")

4. Batch Process All Files in a Directory #

Use the os module to loop through every Excel file in a target folder and apply the above function:

import os

def batch_process_directory(dir_path):
    # Iterate over all files in the directory
    for filename in os.listdir(dir_path):
        if filename.endswith(".xlsx") or filename.endswith(".xlsm"):
            file_path = os.path.join(dir_path, filename)
            process_single_excel(file_path)

# Example usage: Replace with your actual directory path
batch_process_directory("path/to/your/patient_records_folder")

5. Key Notes for Smooth Processing #

• Backup First: Always make a copy of your original files before running batch scripts—mistakes happen!
• Sheet Names: If your data isn't in the active sheet, modify the code to target specific sheet names (e.g., wb["PatientRecord"] instead of wb.active).
• Edge Cases: Handle exercises that aren't in your mapping (add a default tag like "(U)" for unknown, or leave them untouched).
• Performance: For thousands of files, this should run efficiently, but if you hit bottlenecks, consider adding multi-threading (just be careful with file I/O conflicts).

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