# -*- coding: utf-8 -*- import os import tkinter as tk from tkinter import filedialog, ttk, messagebox import pandas as pd import shutil from datetime import datetime, timedelta import openpyxl from openpyxl.styles import Font, Border, Side, Alignment, PatternFill from openpyxl.utils import get_column_letter import xlrd import logging import xlwings as xw from copy import copy import re # Import configuration from config import ( TEMPLATE_PATH, CELL_MAPPINGS, CUSTOM_CELLS, FILENAME_CONFIG, SHEET2_NAME_FORMAT, OUTPUT_FILENAME_FORMAT, EMPLOYEE_INFO, EMPLOYEE_CONFIG_PATH, COMPANY_OPTIONS, BANK_OPTIONS, save_options ) logging.basicConfig(level=logging.DEBUG) class ExcelConverterApp: def __init__(self, root): self.root = root self.root.title("工资明细表转换工具") self.root.geometry("950x600") # 减小整体窗口尺寸 # 设置主题和样式 self.style = ttk.Style() if 'winnative' in self.style.theme_names(): self.style.theme_use('winnative') elif 'vista' in self.style.theme_names(): self.style.theme_use('vista') elif 'xpnative' in self.style.theme_names(): self.style.theme_use('xpnative') elif 'clam' in self.style.theme_names(): self.style.theme_use('clam') # 设置简约现代配色 self.primary_color = "#4A6D8C" # 主色调 - 沉稳的蓝灰色 self.accent_color = "#DB4D6D" # 强调色 - 适当的红色用于警告 self.text_color = "#333333" # 文本色 - 深灰色易于阅读 self.light_bg = "#F8F9FA" # 背景色 - 淡灰色背景 self.light_accent = "#E9ECEF" # 浅强调 - 用于分隔和高亮 # 设置基本样式 self.style.configure("TFrame", background=self.light_bg) self.style.configure("TLabel", font=("Microsoft YaHei UI", 10), background=self.light_bg, foreground=self.text_color) self.style.configure("TLabelframe", font=("Microsoft YaHei UI", 10), background=self.light_bg) self.style.configure("TLabelframe.Label", font=("Microsoft YaHei UI", 10), background=self.light_bg, foreground=self.text_color) # 设置Treeview样式 - 简约样式 self.style.configure("Treeview", font=("Microsoft YaHei UI", 10), background="white", foreground=self.text_color, fieldbackground="white") self.style.configure("Treeview.Heading", font=("Microsoft YaHei UI", 10), background=self.light_accent) self.style.map('Treeview', background=[('selected', self.primary_color)], foreground=[('selected', 'white')]) # Variables self.import_files = [] self.export_dir = "" self.template_path = TEMPLATE_PATH # Create UI self.create_widgets() def create_widgets(self): # 设置根框架背景 self.root.configure(background=self.light_bg) # 创建主框架 - 减少内边距 main_frame = ttk.Frame(self.root, padding=5) main_frame.pack(fill="both", expand=True) # 导入文件区域 - 减少内边距 import_frame = ttk.LabelFrame(main_frame, text="导入Excel文件", padding=5) import_frame.pack(fill="both", expand=True, pady=5) # 按钮区域 button_frame = ttk.Frame(import_frame) button_frame.pack(fill="x", pady=(0, 5)) import_btn = ttk.Button(button_frame, text="选择Excel文件", command=self.select_files) import_btn.pack(side="left", padx=3) # 添加文件数量显示 self.file_count_label = ttk.Label(button_frame, text="已选择: 0 个员工") self.file_count_label.pack(side="left", padx=10) # 文件列表区域 list_frame = ttk.Frame(import_frame) list_frame.pack(fill="both", expand=True) columns = ("employee", "company", "bank", "date", "match_status") self.file_tree = ttk.Treeview(list_frame, columns=columns, show="headings", selectmode="browse") # 设置列标题 self.file_tree.heading("employee", text="员工姓名 (C3)") self.file_tree.heading("company", text="所属公司信息 (C2)") self.file_tree.heading("bank", text="转账银行信息 (B30)") self.file_tree.heading("date", text="日本日期 (F2)") self.file_tree.heading("match_status", text="工作表匹配状态") # 设置列宽 self.file_tree.column("employee", width=120) self.file_tree.column("company", width=170) self.file_tree.column("bank", width=170) self.file_tree.column("date", width=120) self.file_tree.column("match_status", width=120) # 添加滚动条 scrollbar_y = ttk.Scrollbar(list_frame, orient="vertical", command=self.file_tree.yview) scrollbar_x = ttk.Scrollbar(list_frame, orient="horizontal", command=self.file_tree.xview) self.file_tree.configure(yscrollcommand=scrollbar_y.set, xscrollcommand=scrollbar_x.set) # 放置树状视图和滚动条 self.file_tree.grid(row=0, column=0, sticky="nsew") scrollbar_y.grid(row=0, column=1, sticky="ns") scrollbar_x.grid(row=1, column=0, sticky="ew") # 配置列表框架的权重 list_frame.columnconfigure(0, weight=1) list_frame.rowconfigure(0, weight=1) # 注意：移除双击编辑功能，导入列表不可修改 # self.file_tree.bind("", self.edit_item) # 下部区域布局 - 减少间隔 bottom_frame = ttk.Frame(main_frame) bottom_frame.pack(fill="x", pady=5) # 左侧区域 - 导出设置 left_frame = ttk.Frame(bottom_frame) left_frame.pack(side="left", fill="y") export_label = ttk.Label(left_frame, text="导出位置:") export_label.pack(side="left", padx=(0, 3)) self.export_label = ttk.Label(left_frame, text="未选择", width=28) self.export_label.pack(side="left") export_btn = ttk.Button(left_frame, text="选择导出位置", command=self.select_export_dir) export_btn.pack(side="left", padx=3) # 右侧区域 - 功能按钮 right_frame = ttk.Frame(bottom_frame) right_frame.pack(side="right") info_btn = ttk.Button(right_frame, text="信息维护", command=self.maintain_info) info_btn.pack(side="left", padx=3) convert_btn = ttk.Button(right_frame, text="开始转换", command=self.convert_files) convert_btn.pack(side="left", padx=3) # 添加状态栏 status_frame = ttk.Frame(main_frame, relief="sunken") status_frame.pack(fill="x", side="bottom", pady=(3, 0)) self.status_label = ttk.Label(status_frame, text="准备就绪", anchor="w") self.status_label.pack(side="left", padx=5) def select_files(self): file = filedialog.askopenfilename( title="选择Excel文件", filetypes=[("Excel Files", "*.xlsx")] ) if file: self.import_files = [file] self.update_file_tree() def update_file_tree(self): # Clear tree for item in self.file_tree.get_children(): self.file_tree.delete(item) # 用于存储未匹配的员工姓名 unmatch_employees = [] # 当前只处理单个文件 if not self.import_files: return file = self.import_files[0] try: # 打开工作簿 wb = openpyxl.load_workbook(file) name_sheet = wb.worksheets[0] # 验证是否存在第二个工作表 if len(wb.worksheets) < 2: messagebox.showerror("错误", "导入的Excel必须包含至少两个工作表") self.import_files = [] return date_sheet = wb.worksheets[1] # 获取日期信息 original_date = date_sheet.cell(row=4, column=2).value or "" # 提取年月信息 extracted_year = None extracted_month = None try: if isinstance(original_date, (int, float)): base_date = datetime(1899, 12, 30) date_obj = base_date + timedelta(days=original_date) extracted_year = date_obj.year extracted_month = date_obj.month elif isinstance(original_date, datetime): extracted_year = original_date.year extracted_month = original_date.month else: date_str = str(original_date) year_month_match = re.search(r'(\d{4})年(\d{1,2})月', date_str) if year_month_match: extracted_year, extracted_month = map(int, year_month_match.groups()) except Exception as e: logging.error("Error processing date: {0}".format(e)) # 生成令和年号日期格式 reiwa_date = "" if extracted_year and extracted_month: reiwa_year = extracted_year - 2019 + 1 if reiwa_year > 0: reiwa_date = "令和{0}年{1}月分".format(reiwa_year, extracted_month) # 检查工作表中是否存在员工姓名对应的工作表 sheet_names = wb.sheetnames employee_sheets = sheet_names[1:] # 从第二个工作表开始 # 获取所有员工数据（从C3开始的行） current_row = 3 # 从C3开始 while True: name_cell = name_sheet.cell(row=current_row, column=3) # C列 if not name_cell.value: # 如果C列单元格为空，说明没有更多数据 break employee_name = name_cell.value if employee_name: # 清除员工姓名中的所有空格 employee_name = employee_name.replace(" ", "").strip() name_sheet.cell(row=current_row, column=3).value = employee_name # 默认值 company = "选择公司" bank_info = "选择银行" # 检查员工信息是否存在于员工信息维护中 employee_matched = False if employee_name: for emp_info in EMPLOYEE_INFO: # 清除员工配置中姓名的空格后再比较 config_name = emp_info.get("employee_name", "").replace(" ", "").strip() if config_name == employee_name: employee_matched = True # 匹配到员工信息，更新公司和银行信息 company = emp_info.get("company_name", "") bank_name = emp_info.get("bank_name", "") branch_account = emp_info.get("branch_account", "") bank_info = "振込先金融機関:{0}\n口座番号:{1}".format(bank_name, branch_account) break # 检查员工名称是否在工作表名称中 sheet_match = False for sheet_name in employee_sheets: # 清除工作表名称中的空格后再比较 clean_sheet_name = sheet_name.replace(" ", "").strip() if employee_name == clean_sheet_name: sheet_match = True break # 如果未匹配，添加到未匹配列表 if not employee_matched and employee_name not in unmatch_employees: unmatch_employees.append(employee_name) # 添加到文件树 - 在日本日期(F2)字段显示令和年月及工作表匹配状态 matching_info = "✓ 已匹配工作表" if sheet_match else "✗ 未匹配工作表" self.file_tree.insert("", "end", values=( employee_name, # 员工姓名 company, # 公司信息 bank_info, # 银行信息 reiwa_date, # 日期信息 matching_info # 工作表匹配状态 )) current_row += 1 except Exception as e: logging.error("Error reading file {0}: {1}".format(file, e)) # 更新文件计数 total_employees = len(self.file_tree.get_children()) self.file_count_label.config(text="已选择: {0} 个员工".format(total_employees)) # 如果有未匹配的员工，显示警告消息 if unmatch_employees: warning_msg = "以下员工信息未在员工信息维护中找到匹配记录：\n" warning_msg += "\n".join(unmatch_employees) warning_msg += "\n\n请先在【信息维护】中添加这些员工信息，然后再进行转换。" messagebox.showwarning("员工信息不匹配", warning_msg) # 标记未匹配的行 for item in self.file_tree.get_children(): values = self.file_tree.item(item, "values") if values[0] in unmatch_employees: self.file_tree.item(item, tags=("unmatch",)) # 设置未匹配样式（黄色背景） self.file_tree.tag_configure("unmatch", background="#FFF9C4") # 浅黄色 # 设置状态提示 if total_employees > 0: self.status_label.config(text="员工信息已加载，请配置转换选项", foreground=self.text_color) else: self.status_label.config(text="准备就绪", foreground="gray") def select_export_dir(self): directory = filedialog.askdirectory(title="选择导出目录") if directory: self.export_dir = directory self.export_label.config(text=directory) def maintain_info(self): # 创建弹出窗口 popup = tk.Toplevel(self.root) popup.title("员工信息维护") popup.geometry("700x500") popup.transient(self.root) popup.grab_set() # 标题 title_frame = ttk.Frame(popup, padding=5) title_frame.pack(fill="x") title_label = ttk.Label(title_frame, text="员工信息维护", font=("Microsoft YaHei UI", 12, "bold")) title_label.pack(side="left") # 创建表格视图 tree_frame = ttk.Frame(popup, padding=5) tree_frame.pack(fill="both", expand=True) columns = ("employee", "company", "bank", "account", "holder") tree = ttk.Treeview(tree_frame, columns=columns, show="headings", selectmode="browse") # 设置列标题 tree.heading("employee", text="员工姓名") tree.heading("company", text="所属公司") tree.heading("bank", text="振込先金融機関") tree.heading("account", text="口座番号") tree.heading("holder", text="名義人") # 设置列宽 tree.column("employee", width=100) tree.column("company", width=120) tree.column("bank", width=120) tree.column("account", width=180) tree.column("holder", width=120) # 添加滚动条 vscrollbar = ttk.Scrollbar(tree_frame, orient="vertical", command=tree.yview) tree.configure(yscrollcommand=vscrollbar.set) hscrollbar = ttk.Scrollbar(tree_frame, orient="horizontal", command=tree.xview) tree.configure(xscrollcommand=hscrollbar.set) tree.grid(row=0, column=0, sticky="nsew") vscrollbar.grid(row=0, column=1, sticky="ns") hscrollbar.grid(row=1, column=0, sticky="ew") tree_frame.columnconfigure(0, weight=1) tree_frame.rowconfigure(0, weight=1) # 添加搜索功能 search_frame = ttk.Frame(popup, padding=5) search_frame.pack(fill="x", pady=5) search_label = ttk.Label(search_frame, text="搜索:") search_label.pack(side="left", padx=5) search_var = tk.StringVar() search_entry = ttk.Entry(search_frame, textvariable=search_var, width=30) search_entry.pack(side="left", fill="x", expand=True, padx=5) def search_info(*args): query = search_var.get().lower() tree.delete(*tree.get_children()) for info in EMPLOYEE_INFO: # 在所有字段中搜索 search_in = ( info.get("employee_name", "").lower() + info.get("company_name", "").lower() + info.get("bank_name", "").lower() + info.get("branch_account", "").lower() + info.get("account_holder", "").lower() ) if query in search_in: tree.insert("", "end", values=( info.get("employee_name", ""), info.get("company_name", ""), info.get("bank_name", ""), info.get("branch_account", ""), info.get("account_holder", "") )) search_var.trace("w", search_info) # 添加按钮 btn_frame = ttk.Frame(popup, padding=5) btn_frame.pack(fill="x", pady=5) # 编辑功能 def edit_info(): selected = tree.selection() if not selected: messagebox.showinfo("提示", "请先选择一条记录") return selected_idx = tree.index(selected[0]) if selected_idx < 0 or selected_idx >= len(EMPLOYEE_INFO): messagebox.showerror("错误", "选择的记录无效") return edit_employee_info(EMPLOYEE_INFO[selected_idx]) # 新增功能 def add_info(): edit_employee_info() # 删除功能 def delete_info(): selected = tree.selection() if not selected: messagebox.showinfo("提示", "请先选择一条记录") return selected_idx = tree.index(selected[0]) if selected_idx < 0 or selected_idx >= len(EMPLOYEE_INFO): messagebox.showerror("错误", "选择的记录无效") return if messagebox.askyesno("确认", "确定要删除此记录吗?"): del EMPLOYEE_INFO[selected_idx] save_options(EMPLOYEE_CONFIG_PATH, EMPLOYEE_INFO) refresh_tree() # 刷新表格 def refresh_tree(): tree.delete(*tree.get_children()) for info in EMPLOYEE_INFO: tree.insert("", "end", values=( info.get("employee_name", ""), info.get("company_name", ""), info.get("bank_name", ""), info.get("branch_account", ""), info.get("account_holder", "") )) # 编辑员工信息 def edit_employee_info(info=None): is_new = info is None if is_new: info = { "employee_name": "", "company_name": "", "bank_name": "", "branch_account": "", "account_holder": "" } edit_popup = tk.Toplevel(popup) edit_popup.title("编辑员工信息") edit_popup.geometry("450x300") edit_popup.transient(popup) edit_popup.grab_set() # 创建表单 form_frame = ttk.Frame(edit_popup, padding=10) form_frame.pack(fill="both", expand=True) # 员工姓名 name_label = ttk.Label(form_frame, text="员工姓名:") name_label.grid(row=0, column=0, sticky="w", pady=5) name_var = tk.StringVar(value=info.get("employee_name", "")) name_entry = ttk.Entry(form_frame, textvariable=name_var, width=30) name_entry.grid(row=0, column=1, sticky="ew", pady=5) # 所属公司 company_label = ttk.Label(form_frame, text="所属公司:") company_label.grid(row=1, column=0, sticky="w", pady=5) company_var = tk.StringVar(value=info.get("company_name", "")) company_entry = ttk.Entry(form_frame, textvariable=company_var, width=30) company_entry.grid(row=1, column=1, sticky="ew", pady=5) # 银行名称 bank_label = ttk.Label(form_frame, text="振込先金融機関:") bank_label.grid(row=2, column=0, sticky="w", pady=5) bank_var = tk.StringVar(value=info.get("bank_name", "")) bank_entry = ttk.Entry(form_frame, textvariable=bank_var, width=30) bank_entry.grid(row=2, column=1, sticky="ew", pady=5) # 账户信息 account_label = ttk.Label(form_frame, text="口座番号:") account_label.grid(row=3, column=0, sticky="w", pady=5) account_var = tk.StringVar(value=info.get("branch_account", "")) account_entry = ttk.Entry(form_frame, textvariable=account_var, width=30) account_entry.grid(row=3, column=1, sticky="ew", pady=5) # 账户持有人 holder_label = ttk.Label(form_frame, text="名義人:") holder_label.grid(row=4, column=0, sticky="w", pady=5) holder_var = tk.StringVar(value=info.get("account_holder", "")) holder_entry = ttk.Entry(form_frame, textvariable=holder_var, width=30) holder_entry.grid(row=4, column=1, sticky="ew", pady=5) form_frame.columnconfigure(1, weight=1) # 按钮区域 btn_frame = ttk.Frame(edit_popup, padding=10) btn_frame.pack(fill="x") def save_info(): # 验证 if not name_var.get().strip(): messagebox.showerror("错误", "员工姓名不能为空") return # 保存 new_info = { "employee_name": name_var.get().strip(), "company_name": company_var.get().strip(), "bank_name": bank_var.get().strip(), "branch_account": account_var.get().strip(), "account_holder": holder_var.get().strip() } if is_new: EMPLOYEE_INFO.append(new_info) else: idx = EMPLOYEE_INFO.index(info) EMPLOYEE_INFO[idx] = new_info save_options(EMPLOYEE_CONFIG_PATH, EMPLOYEE_INFO) refresh_tree() edit_popup.destroy() save_btn = ttk.Button(btn_frame, text="保存", command=save_info) save_btn.pack(side="right", padx=5) cancel_btn = ttk.Button(btn_frame, text="取消", command=edit_popup.destroy) cancel_btn.pack(side="right", padx=5) name_entry.focus_set() # 添加按钮 add_btn = ttk.Button(btn_frame, text="新增", command=add_info) add_btn.pack(side="left", padx=5) edit_btn = ttk.Button(btn_frame, text="编辑", command=edit_info) edit_btn.pack(side="left", padx=5) delete_btn = ttk.Button(btn_frame, text="删除", command=delete_info) delete_btn.pack(side="left", padx=5) close_btn = ttk.Button(btn_frame, text="关闭", command=popup.destroy) close_btn.pack(side="right", padx=5) # 初始化 refresh_tree() search_entry.focus_set() def convert_files(self): if not self.import_files: messagebox.showerror("错误", "请先选择要导入的Excel文件") return if not self.export_dir: messagebox.showerror("错误", "请选择导出位置") return # Template file check if not os.path.exists(self.template_path): messagebox.showerror("错误", "模板文件不存在: {0}".format(self.template_path)) return # 检查是否有未匹配的员工 unmatch_employees = [] for item in self.file_tree.get_children(): values = self.file_tree.item(item, "values") employee_name = values[0] # 检查是否有匹配 if employee_name and not any(emp_info.get("employee_name") == employee_name for emp_info in EMPLOYEE_INFO): unmatch_employees.append(employee_name) # 如果有未匹配员工，提示用户先维护信息 if unmatch_employees: warning_msg = "以下员工信息未在员工信息维护中找到匹配记录：\n" warning_msg += "\n".join(unmatch_employees) warning_msg += "\n\n请先在【信息维护】中添加这些员工信息，然后再进行转换。" messagebox.showwarning("员工信息不匹配", warning_msg) return # 更新状态 self.status_label.config(text="正在转换文件...", foreground=self.accent_color) self.root.update() # Process each file success_count = 0 error_count = 0 # 确保文件树和导入文件列表长度一致 tree_items = self.file_tree.get_children() for i, file_path in enumerate(self.import_files): try: # 更新状态 self.status_label.config(text="正在处理: {0} ({1}/{2})".format( os.path.basename(file_path), i+1, len(self.import_files)), foreground=self.text_color) self.root.update() # 检查索引是否有效 if i < len(tree_items): item = tree_items[i] values = self.file_tree.item(item, "values") # 安全地获取值 employee_name = values[0] # Process file success = self.process_file(file_path, "", "", "", employee_name) if success: success_count += 1 # 设置行的背景色为成功 self.file_tree.item(item, tags=("success",)) else: error_count += 1 # 设置行的背景色为错误 self.file_tree.item(item, tags=("error",)) else: # 文件树中没有对应的项，直接处理文件 success = self.process_file(file_path, "", "", "", "") if success: success_count += 1 else: error_count += 1 except Exception as e: error_count += 1 logging.error("处理文件 {0} 时出错: {1}".format(os.path.basename(file_path), str(e))) # 不显示对话框，只在状态栏更新 self.status_label.config(text="处理文件 {0} 时出错".format(os.path.basename(file_path)), foreground="red") # 找到对应的项并设置错误标签 try: if i < len(tree_items): item = tree_items[i] self.file_tree.item(item, tags=("error",)) except: pass # 设置Tag样式 self.file_tree.tag_configure("success", background="#E8F8F5") # 浅绿色 self.file_tree.tag_configure("error", background="#FADBD8") # 浅红色 # 更新最终状态 if error_count == 0: self.status_label.config(text="转换完成! 成功处理 {0} 个文件".format(success_count), foreground=self.accent_color) else: self.status_label.config(text="转换结束, 成功: {0}, 失败: {1}".format(success_count, error_count), foreground="red") # Show completion message messagebox.showinfo("完成", "成功转换 {0} 个文件到 {1}".format(success_count, self.export_dir)) def process_file(self, input_file, company, bank, other_info, employee_name=None): try: logging.debug("Processing file: {0}".format(input_file)) # Extract name and date input_wb = openpyxl.load_workbook(input_file) name_sheet = input_wb.worksheets[0] date_sheet = input_wb.worksheets[1] date_value = date_sheet.cell(row=4, column=2).value # B4 cell for date # 获取年月信息 try: extracted_year = None extracted_month = None if isinstance(date_value, (int, float)): base_date = datetime(1899, 12, 30) date_obj = base_date + timedelta(days=date_value) extracted_year = date_obj.year extracted_month = date_obj.month elif isinstance(date_value, datetime): # 日期对象 extracted_year = date_value.year extracted_month = date_value.month else: # 字符串格式 - 尝试提取年月 date_str = str(date_value) year_month_match = re.search(r'(\d{4})年(\d{1,2})月', date_str) if year_month_match: extracted_year, extracted_month = map(int, year_month_match.groups()) if extracted_year is None or extracted_month is None: current_date = datetime.now() extracted_year = current_date.year extracted_month = current_date.month logging.warning("Could not extract year/month from date value: {0}, using current date".format(date_value)) year = extracted_year month = extracted_month except Exception as e: logging.error("Error extracting year/month: {0}".format(e)) return False # 获取所有工作表名称，用于后续匹配 sheet_names = input_wb.sheetnames # 获取所有员工数据（从C3开始的行） employee_rows = [] current_row = 3 # 从C3开始 while True: name_cell = name_sheet.cell(row=current_row, column=3) # C列 if not name_cell.value: # 如果C列单元格为空，说明没有更多数据 break employee_rows.append(current_row) current_row += 1 if not employee_rows: logging.error("No employee data found in the first sheet") return False # 为每个员工创建输出文件 success = True for row_index in employee_rows: employee_name = name_sheet.cell(row=row_index, column=3).value if not employee_name: continue # 清除员工姓名中的所有空格 employee_name = employee_name.replace(" ", "").strip() # 创建输出文件名 output_filename = OUTPUT_FILENAME_FORMAT.format( year=year, month=month, name=employee_name ) output_path = os.path.join(self.export_dir, output_filename) # 加载模板 template_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), TEMPLATE_PATH) output_wb = openpyxl.load_workbook(template_path) # 应用单元格映射 for src, dst in CELL_MAPPINGS.items(): src_sheet_idx, src_row_offset, src_col = src dst_sheet_idx, dst_row, dst_col = dst try: src_sheet = input_wb.worksheets[src_sheet_idx] src_row = row_index # 使用当前员工的行 # 处理合并单元格 cell = src_sheet.cell(row=src_row, column=src_col) if isinstance(cell, openpyxl.cell.cell.MergedCell): for merged_range in src_sheet.merged_cells.ranges: if cell.coordinate in merged_range: top_left = merged_range.min_row, merged_range.min_col src_value = src_sheet.cell(row=top_left[0], column=top_left[1]).value break else: src_value = None else: src_value = cell.value dst_sheet = output_wb.worksheets[dst_sheet_idx] dst_sheet.cell(row=dst_row, column=dst_col).value = src_value except Exception as e: logging.error("Error copying cell from {0} to {1}: {2}".format(src, dst, e)) continue # 设置自定义字段 try: matched_info = None for emp_info in EMPLOYEE_INFO: # 清除员工配置中姓名的空格后再比较 config_name = emp_info.get("employee_name", "").replace(" ", "").strip() if config_name == employee_name: matched_info = emp_info break if matched_info and matched_info.get("company_name"): company = matched_info.get("company_name") sheet_idx, row, col = CUSTOM_CELLS["company"] output_wb.worksheets[sheet_idx].cell(row=row, column=col).value = company except Exception as e: logging.error("Error setting company field: {0}".format(e)) try: sheet_idx, row, col = CUSTOM_CELLS["bank"] # 计算转账日期（月份+1） transfer_month = month + 1 transfer_year = year if transfer_month > 12: transfer_month = 1 transfer_year += 1 # 计算令和年号 reiwa_year = transfer_year - 2019 + 1 transfer_date = "令和{0}年{1}月25日".format(reiwa_year, transfer_month) # 获取银行信息 bank_info = bank if employee_name: # 查找匹配的员工信息 matched_info = None for emp_info in EMPLOYEE_INFO: # 清除员工配置中姓名的空格后再比较 config_name = emp_info.get("employee_name", "").replace(" ", "").strip() if config_name == employee_name: matched_info = emp_info break if matched_info: bank_name = matched_info.get("bank_name", "") branch_account = matched_info.get("branch_account", "") account_holder = matched_info.get("account_holder", employee_name) # 组合完整的银行转账信息 bank_info = "振込先金融機関:{0}\n口座番号:{1}\n名義人：{2}\n振込日：{3}\n※休日の場合は、翌営業日にお振込みします。".format(bank_name, branch_account, account_holder, transfer_date) output_wb.worksheets[sheet_idx].cell(row=row, column=col).value = bank_info # 设置单元格自动换行 cell = output_wb.worksheets[sheet_idx].cell(row=row, column=col) cell.alignment = Alignment(wrap_text=True, vertical='top') except Exception as e: logging.error("Error setting bank info: {0}".format(e)) try: sheet_idx, row, col = CUSTOM_CELLS["other"] reiwa_date = "" if year and month: reiwa_year = year - 2019 + 1 reiwa_date = "令和{0}年{1}月分".format(reiwa_year, month) output_wb.worksheets[sheet_idx].cell(row=row, column=col).value = reiwa_date or other_info except Exception as e: logging.error("Error setting other info: {0}".format(e)) # 复制第二个工作表 - 根据员工姓名匹配工作表 try: if len(output_wb.worksheets) == 1: output_wb.create_sheet() output_sheet2 = output_wb.worksheets[1] output_sheet2.title = SHEET2_NAME_FORMAT.format(month=month) # 查找员工姓名匹配的工作表 matched_sheet = None for sheet_name in input_wb.sheetnames: # 清除工作表名称中的空格后再比较 clean_sheet_name = sheet_name.replace(" ", "").strip() if employee_name == clean_sheet_name: matched_sheet = sheet_name break if matched_sheet: logging.debug("Found matching sheet: {0} for employee: {1}".format(matched_sheet, employee_name)) try: # 保存当前工作簿以便使用xlwings处理 temp_output_path = os.path.join(self.export_dir, "temp_{0}.xlsx".format(employee_name)) output_wb.save(temp_output_path) # 使用xlwings处理文件 - 这样可以避免合并单元格的问题 with xw.App(visible=False, add_book=False) as app: app.display_alerts = False app.screen_updating = False try: # 打开输入和输出文件 input_wb_xw = app.books.open(input_file) output_wb_xw = app.books.open(temp_output_path) # 获取匹配的工作表 src_sheet = None for sheet in input_wb_xw.sheets: if sheet.name == matched_sheet: src_sheet = sheet break if src_sheet: # 获取输出工作表 dst_sheet = output_wb_xw.sheets[1] # 复制所有单元格的值 - 只复制数据 used_range = src_sheet.used_range data_values = used_range.value # 获取所有值而不带格式 # 确保data_values不为None if data_values: # 确保是二维数组 if not isinstance(data_values, list): data_values = [[data_values]] elif not isinstance(data_values[0], list): data_values = [data_values] # 写入所有值 dst_sheet.range('A1').value = data_values # 保存并关闭 output_wb_xw.save() input_wb_xw.close() output_wb_xw.close() # 重新加载保存的文件 output_wb = openpyxl.load_workbook(temp_output_path) # 删除临时文件 if os.path.exists(temp_output_path): try: os.remove(temp_output_path) except: pass logging.debug("Successfully copied data from sheet {0} using xlwings".format(matched_sheet)) except Exception as e: logging.error("Error in xlwings processing: {0}".format(e)) # 如果xlwings失败，我们将尝试直接处理数据 try: # 重新打开工作簿 output_wb = openpyxl.load_workbook(template_path) if len(output_wb.worksheets) == 1: output_wb.create_sheet() output_sheet2 = output_wb.worksheets[1] output_sheet2.title = SHEET2_NAME_FORMAT.format(month=month) # 直接使用openpyxl src_sheet = input_wb[matched_sheet] # 获取所有单元格的值并只复制值 max_row = src_sheet.max_row max_col = src_sheet.max_column for r in range(1, max_row + 1): for c in range(1, max_col + 1): src_cell = src_sheet.cell(row=r, column=c) # 判断是否为合并单元格 if isinstance(src_cell, openpyxl.cell.cell.MergedCell): # 对于合并单元格，跳过写入 continue else: # 对于普通单元格，只复制值 output_sheet2.cell(row=r, column=c).value = src_cell.value logging.debug("Successfully copied data from sheet {0} using openpyxl fallback".format(matched_sheet)) except Exception as fallback_error: logging.error("Fallback error: {0}".format(fallback_error)) raise except Exception as e: logging.error("Error copying sheet {0} data: {1}".format(matched_sheet, e)) success = False else: logging.warning("No matching sheet found for employee: {0}".format(employee_name)) except Exception as e: logging.error("Error setting up second sheet: {0}".format(e)) success = False # 保存输出工作簿 try: output_wb.save(output_path) logging.debug("File processed successfully for employee: {0}".format(employee_name)) except Exception as e: logging.error("Error saving output file: {0}".format(e)) success = False return success except Exception as e: logging.error("Error processing file: {0}".format(e)) return False if __name__ == "__main__": root = tk.Tk() app = ExcelConverterApp(root) root.mainloop()