如何使用Python循环读取多个Excel文件并分别将数据存入不同的表格？

<code class="python">from openpyxl import load_workbook

def read_excel_range(filepath, sheet_name, start_row, end_row, start_col, end_col):
    """读取Excel指定区域的数据"""
    try:
        workbook = load_workbook(filepath, read_only=True)  # read_only提高效率
        sheet = workbook[sheet_name]
        data = []
        for row in sheet.iter_rows(min_row=start_row, max_row=end_row, min_col=start_col, max_col=end_col):
            row_data = [cell.value for cell in row]
            data.append(row_data)
        workbook.close()
        return data
    except FileNotFoundError:
        print(f"Error: File not found - {filepath}")
        return None
    except Exception as e:
        print(f"Error reading Excel file: {e}")
        return None</code>

<code class="python">from openpyxl import Workbook

def write_excel(filepath, sheet_name, data):
    """将数据写入新的Excel文件"""
    try:
        workbook = Workbook()
        sheet = workbook.create_sheet(sheet_name)
        for row in data:
            sheet.append(row)
        workbook.save(filepath)
        workbook.close()
    except Exception as e:
        print(f"Error writing to Excel file: {e}")
</code>

<code class="python">import os

def process_excel_files(input_dir, output_dir, sheet_name='Sheet1', start_row=1, end_row=100, start_col=1, end_col=10):
    """批量处理Excel文件"""
    for filename in os.listdir(input_dir):
        if filename.endswith(".xlsx") or filename.endswith(".xls"):
            filepath = os.path.join(input_dir, filename)
            data = read_excel_range(filepath, sheet_name, start_row, end_row, start_col, end_col)
            if data:
                output_filepath = os.path.join(output_dir, filename)
                write_excel(output_filepath, sheet_name, data)

# 示例用法
input_directory = 'C:/path/to/excel/input/'  # 替换成你的输入目录
output_directory = 'C:/path/to/excel/output/' # 替换成你的输出目录
process_excel_files(input_directory, output_directory)
</code>