import os
from openpyxl import load_workbook
from openpyxl.styles import Font

def replace_string(old_str, new_str, file_path):
    # 加载原始Excel文件
    old_workbook = load_workbook(file_path)
    # 创建新的Excel文件
    new_workbook = load_workbook(file_path)
    # 遍历每个sheet
    for old_worksheet, new_worksheet in zip(old_workbook, new_workbook):
        # 遍历每个单元格
        for row in old_worksheet.iter_rows():
            for old_cell, new_cell in zip(row, new_worksheet[row[0].row]):
                # 如果单元格中包含要替换的字符串，则进行替换
                if old_str in str(old_cell.value):
                    new_cell.value = str(old_cell.value).replace(old_str, new_str)
                    # 复制单元格的字体格式
                    new_cell.font = Font(name=old_cell.font.name, size=old_cell.font.size,
                                         bold=old_cell.font.bold, italic=old_cell.font.italic,
                                         underline=old_cell.font.underline, strike=old_cell.font.strike)
    # 保存新的Excel文件
    new_workbook.save(file_path)


if __name__ == '__main__':
    # 文件路径和要替换的字符串
    dir_path = input("请输入Excel文件所在的文件夹路径：")
    old_str = input("请输入要替换的字符串：")
    new_str = input("请输入替换后的字符串：")

    # 遍历文件夹下的所有Excel文件
    for file_name in os.listdir(dir_path):
        file_path = os.path.join(dir_path, file_name)
        if file_name.endswith('.xlsx'):
            # 替换Excel文件中的指定字符串
            replace_string(old_str, new_str, file_path)

    input("按任意键退出...")