jp-payroll-conversion-tool/app.py

# -*- 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("<Double-1>", 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()