抛砖引玉-合并两个格式一致的excel文件

写在前面：
目前市面上有许多翻译excel的工具，但是鲜有生成双语文件的翻译工具。
而笔者常常需要双语文件，所以编写了这个软件，输入原文和译文的excel，输出双语excel（支持多sheet，sheet名使用文件1的）

程序说明：
笔者借助chatgpt编写了两个版本，一个版本可以实现格式完全一致，但是没有合并进度。另一个版本带有进度条，但是无法正确处理合并过的单元格（需要手动二次合并）

论坛里大神云集，笔者先抛块砖，你们有玉的尽管砸过来。

没有进度条

import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
import openpyxl
from openpyxl.styles import Font, Border, PatternFill, Alignment, Protection
from copy import copy
import re

class ExcelMergerApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel合并工具")

        self.file1_path = tk.StringVar()
        self.file2_path = tk.StringVar()

        self.create_widgets()

    def create_widgets(self):
        self.file1_label = tk.Label(self.root, text="第一个文件:")
        self.file1_label.pack(pady=10)

        self.file1_entry = tk.Entry(self.root, textvariable=self.file1_path, width=40)
        self.file1_entry.pack()

        self.file1_browse_button = tk.Button(self.root, text="浏览...", command=self.browse_file1)
        self.file1_browse_button.pack(pady=5)

        self.file2_label = tk.Label(self.root, text="第二个文件:")
        self.file2_label.pack(pady=10)

        self.file2_entry = tk.Entry(self.root, textvariable=self.file2_path, width=40)
        self.file2_entry.pack()

        self.file2_browse_button = tk.Button(self.root, text="浏览...", command=self.browse_file2)
        self.file2_browse_button.pack(pady=5)

        self.merge_button = tk.Button(self.root, text="合并文件", command=self.merge_files)
        self.merge_button.pack(pady=20)

        self.save_button = tk.Button(self.root, text="保存到...", command=self.save_merged_file)
        self.save_button.pack(pady=10)

        self.copy_right_label = tk.Label(self.root, text="© 52pj")
        self.copy_right_label.pack(pady=10)

    def browse_file1(self):
        file1_path = filedialog.askopenfilename(title="选择第一个文件")
        if file1_path:
            self.file1_path.set(file1_path)

    def browse_file2(self):
        file2_path = filedialog.askopenfilename(title="选择第二个文件")
        if file2_path:
            self.file2_path.set(file2_path)

    def merge_files(self):
        file1_path = self.file1_path.get()
        file2_path = self.file2_path.get()

        if not file1_path or not file2_path:
            messagebox.showerror("错误", "请选择正确的文件")
            return

        wb1 = openpyxl.load_workbook(file1_path, data_only=True)
        wb2 = openpyxl.load_workbook(file2_path, data_only=True)

        merged_wb = openpyxl.Workbook()
        merged_wb.remove(merged_wb.active)

        for index, sheet1 in enumerate(wb1.sheetnames):
            ws1 = wb1[sheet1]
            ws2 = wb2[wb2.sheetnames[index]]
            merged_ws = merged_wb.create_sheet(title=sheet1)
            copy_cells(ws1, merged_ws)
            copy_cells(ws2, merged_ws)
            for row in merged_ws.iter_rows():
                for cell in row:
                    cell1 = ws1[cell.coordinate]
                    cell2 = ws2[cell.coordinate]
                    if cell1.value and cell2.value and not (is_arabic_number(cell1.value) and is_arabic_number(cell2.value)):
                        cell.value = merge_cell_contents(cell1, cell2)

        self.merged_wb = merged_wb
        messagebox.showinfo("完成", "合并完成")

    def save_merged_file(self):
        if hasattr(self, "merged_wb"):
            save_path = filedialog.asksaveasfilename(title="保存到", defaultextension=".xlsx")
            if save_path:
                self.merged_wb.save(save_path)
                messagebox.showinfo("完成", f"已保存为 {save_path}")
        else:
            messagebox.showerror("错误", "请先合并文件")

def is_arabic_number(value):
    return re.match(r'^d+$', str(value))

def merge_cell_contents(cell1, cell2):
    value1 = cell1.value
    value2 = cell2.value
    if value1 == value2:
        return value1
    return f"{value1}
{value2}"

def copy_cells(source_ws, target_ws):
    for row in source_ws.iter_rows(min_row=1, max_row=source_ws.max_row,
                                    min_col=1, max_col=source_ws.max_column):
        for cell in row:
            target_cell = target_ws.cell(row=cell.row, column=cell.column,
                                         value=cell.value)
            if cell.has_style:
                target_cell.font = Font(**cell.font.__dict__)
                target_cell.border = Border(**cell.border.__dict__)
                target_cell.number_format = cell.number_format
                target_cell.protection = Protection(**cell.protection.__dict__)
                target_cell.alignment = Alignment(**cell.alignment.__dict__)
                if is_arabic_number(cell.value):
                    target_cell.fill = PatternFill(**cell.fill.__dict__)
            if source_ws.merged_cells.ranges:
                for merged_range in source_ws.merged_cells.ranges:
                    if cell.coordinate in merged_range:
                        target_ws.merge_cells(merged_range.coord)

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelMergerApp(root)
    root.mainloop()

有进度条

import tkinter as tk
from tkinter import filedialog
from tkinter import messagebox
from tkinter.ttk import Progressbar
import openpyxl
from openpyxl.styles import Font, Border, PatternFill, Alignment, Protection
from copy import copy
import re
import threading
import time

class ExcelMergerApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel合并工具")

        self.file1_path = tk.StringVar()
        self.file2_path = tk.StringVar()

        self.create_widgets()

    def create_widgets(self):
        self.file1_label = tk.Label(self.root, text="第一个文件:")
        self.file1_label.pack(pady=10)

        self.file1_entry = tk.Entry(self.root, textvariable=self.file1_path, width=40)
        self.file1_entry.pack()

        self.file1_browse_button = tk.Button(self.root, text="浏览...", command=self.browse_file1)
        self.file1_browse_button.pack(pady=5)

        self.file2_label = tk.Label(self.root, text="第二个文件:")
        self.file2_label.pack(pady=10)

        self.file2_entry = tk.Entry(self.root, textvariable=self.file2_path, width=40)
        self.file2_entry.pack()

        self.file2_browse_button = tk.Button(self.root, text="浏览...", command=self.browse_file2)
        self.file2_browse_button.pack(pady=5)
        self.merge_button = tk.Button(self.root, text="合并文件", command=self.merge_files)
        self.merge_button.pack(pady=20)

        self.save_button = tk.Button(self.root, text="保存文件", command=self.save_merged_file)
        self.save_button.pack(pady=10)

        self.progress_bar = Progressbar(self.root, orient="horizontal", length=300, mode="determinate")
        self.copy_right_label = tk.Label(self.root, text="© 52pj")
        self.copy_right_label.pack(pady=10)

    def browse_file1(self):
        file1_path = filedialog.askopenfilename(title="选择第一个文件", filetypes=[("Excel 文件", "*.xlsx")])
        if file1_path:
            self.file1_path.set(file1_path)

    def browse_file2(self):
        file2_path = filedialog.askopenfilename(title="选择第二个文件", filetypes=[("Excel 文件", "*.xlsx")])
        if file2_path:
            self.file2_path.set(file2_path)
    def merge_files(self):
        file1_path = self.file1_path.get()
        file2_path = self.file2_path.get()

        if not file1_path or not file2_path:
            messagebox.showerror("错误", "请选择正确的文件")
            return

        self.show_merge_animation()

        def merge_process():
            wb1 = openpyxl.load_workbook(file1_path, data_only=True)
            wb2 = openpyxl.load_workbook(file2_path, data_only=True)

            merged_wb = openpyxl.Workbook()
            merged_wb.remove(merged_wb.active)

            total_sheets = len(wb1.sheetnames)
            for index, sheet1 in enumerate(wb1.sheetnames):
                ws1 = wb1[sheet1]
                ws2 = wb2[wb2.sheetnames[index]]
                merged_ws = merged_wb.create_sheet(title=sheet1)
                self.copy_cells(ws1, merged_ws)
                self.copy_cells(ws2, merged_ws)
                for row in merged_ws.iter_rows():
                    for cell in row:
                        cell1 = ws1[cell.coordinate]
                        cell2 = ws2[cell.coordinate]
                        if cell1.value and cell2.value and not (self.is_arabic_number(cell1.value) and self.is_arabic_number(cell2.value)):
                            cell.value = self.merge_cell_contents(cell1, cell2)

                progress = (index + 1) / total_sheets * 100
                self.update_progress(progress)

            self.merged_wb = merged_wb
            self.hide_merge_animation()
            messagebox.showinfo("完成", "合并完成")

        merge_thread = threading.Thread(target=merge_process)
        merge_thread.start()

    def show_merge_animation(self):
        self.merge_button["state"] = "disabled"
        self.progress_bar["value"] = 0
        self.progress_bar["maximum"] = 100
        self.progress_bar.pack(pady=10)
    def hide_merge_animation(self):
        self.merge_button["state"] = "normal"
        self.progress_bar.pack_forget()

    def update_progress(self, value):
        self.progress_bar["value"] = value
        self.root.update_idletasks()

    def save_merged_file(self):
        if hasattr(self, "merged_wb"):
            save_path = filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel 文件", "*.xlsx")])
            if save_path:
                self.merged_wb.save(save_path)
                messagebox.showinfo("保存成功", "文件已保存为：" + save_path)
        else:
            messagebox.showerror("保存失败", "没有合并完成的文件")

    def is_arabic_number(self, value):
        if isinstance(value, str):
            return bool(re.match("^d+$", value))
        return False

    def merge_cell_contents(self, cell1, cell2):
        return str(cell1.value) + "
" + str(cell2.value)

    def copy_cells(self, source_ws, target_ws):
        for row in source_ws.iter_rows():
            for cell in row:
                target_cell = target_ws[cell.coordinate]
                target_cell.value = cell.value
                target_cell.font = copy(cell.font)
                target_cell.border = copy(cell.border)
                target_cell.fill = copy(cell.fill)
                target_cell.alignment = copy(cell.alignment)
                target_cell.number_format = copy(cell.number_format)
                target_cell.protection = copy(cell.protection)

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelMergerApp(root)
    root.mainloop()

打包后的程序只上传了带进度条的

https://wwvv.lanzout.com/i2ibA15p681c

密码:2gk8