Python办公自动化应用（三）

• 应用说明

根据中、英文对照字典（参见Python办公自动化应用（一））将Excel表单元格里的中文替换为英文。

• 工具函数

#定义打印区域
def print_area(sheetname):if sheetname.startswith("11-"):return "B2:M36"if sheetname.startswith("12-"):return "B2:X37"if sheetname.startswith("13-"):return "B2:X37"if sheetname.startswith("14-"):return "B2:Y37"#设置样式，自动换行，具中显示，控制列宽
def set_style(file):wb = load_workbook(file)col_start=ord('A')col_end=ord('Z')for ws in wb:#设置列宽set_column(ws)#设置页边距ws.page_margins = PageMargins(left=0.31,  # 左边距right=0.31,  # 右边距top=0.35,  # 上边距bottom=0.35,  # 下边距header=0.19,  # 页眉边距footer=0.31  # 页脚边距)for row_index in range(40):if row_index>=3 and row_index<=6:#设置固定行高ws.row_dimensions[row_index].height=30if row_index>=21 and row_index<=34:#判断是否空行，是则跳过，否则设置固定行高if not row_empty(ws,row_index):#设置固定行高ws.row_dimensions[row_index].height=30#设置空行行高# else:#     ws.row_dimensions[row_index].height=6for col_index in range(col_start,col_end+1):cell_name = str(chr(col_index)) + str(row_index + 1)if ws[cell_name].value != None:ws[cell_name].alignment = Alignment(wrapText=True,horizontal='center',vertical='center')wb.save(file)

• 业务代码实现

from openpyxl import load_workbook
import utilsdef load_mapping(mapping_file):mapping = {}try:# 加载映射文件wb = load_workbook(mapping_file)sheet = wb.activefor row in sheet.iter_rows(min_row=2,values_only=True):if row[0] and row[2]:# 将中文作为键，英文作为值存入字典mapping[row[0]] = row[2]except FileNotFoundError:print(f"错误：未找到映射文件 {mapping_file}")return mappingdef replace_chinese_to_thai(input_file, output_file, mapping_file):# 加载映射表mapping = load_mapping(mapping_file)if not mapping:returntry:# 加载要替换的 Excel 文件wb = load_workbook(input_file)for sheet in wb:#设置纸张方向横向sheet.page_setup.orientation=sheet.ORIENTATION_LANDSCAPEsheet.print_area=utils.print_area(sheet.title)for row in sheet.iter_rows():for cell in row:if cell.value in mapping:# 如果单元格的值在映射表中，将其替换为对应的英文cell.value = mapping[cell.value]# 保存替换后输出的 Excel 文件wb.save(output_file)#设置样式，自动换行，居中显示utils.set_style(output_file)print(f"已将 {input_file} 中的中文替换为英文，并保存到 {output_file}")except FileNotFoundError:print(f"错误：未找到输入文件 {input_file}")if __name__ == "__main__":# 中文原始文件夹input_dict = r'D:\Chinese'# 英文输出文件夹output_dict = r'D:\English'# 中英文对照字典mapping_file = r'D:\translateZ.xlsx'for file in utils.get_file_names(input_dict):output_file = utils.translate_to_thai(output_dict, file)replace_chinese_to_thai(file, output_file, mapping_file)