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

1107934496

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

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

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

程序主画面

没有进度条

[Lua] 纯文本查看 复制代码

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}\n{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()

有进度条

合并中

[Python] 纯文本查看 复制代码

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) + "\n" + 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

Guangnianyinan

遇上楼上同样问题求解

ZengHugh

import os
from tqdm import tqdm
import functools
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from tkinter import filedialog  # 模拟文件选择窗口，选取文件或文件夹
from rich.console import Console

# 打开窗口选择文件（可多选），返回选择的文件的完整路径名称列表
def get_filelist():
    excel_files = filedialog.askopenfilenames(title='选择需要处理的excel文件,可以多选',
                                              filetypes=[('xlsx', '*.xlsx'), ('xls', '*.xls'), ('All Files', '*')],
                                              initialdir=os.path.join(os.path.expanduser("~user"), 'Desktop'))
    return excel_files

# 将所有工作簿中是定名称工作表汇总到一个新的sheet中并保存
def all2onesheet(excel_files: list, sheet_name: str, out_put: str):
    frames = []
    progress_bar = tqdm(total=len(excel_files), desc="完成进度")
    for excel_file in excel_files:
        try:
            # 减少加载时间,若需将结果打印显示出来将screen_update改为True
            df = pd.read_excel(excel_file, sheet_name=sheet_name)
            # rprint(df)
            frames.append(df)
        except Exception as e:
            print(excel_file, e)
            pass
        # 更新进度条
        progress_bar.update(1)
    alldata = pd.concat(frames, axis=0, ignore_index=True)
    alldata.to_excel(out_put, sheet_name=sheet_name, index=False)
    progress_bar.close()


# 将所有工作簿中是定名称工作表汇总到一个新的工作薄以原文件名保存
def all2onebook(excel_files: list, sheet_name_to_copy: str, out_put: str):
    # 指定要复制的工作表名称
    # 请替换为你要复制的工作表名称

    # 创建一个新的工作簿
    new_workbook = Workbook()
    progress_bar = tqdm(total=len(excel_files), desc="完成进度")
    for excel_file in excel_files:
        # 加载原始Excel文件
        source_workbook = load_workbook(excel_file)
        # 获取指定名称的工作表
        source_sheet = source_workbook[sheet_name_to_copy]
        # 复制工作表的数据到新工作簿
        new_sheet = new_workbook.create_sheet(title=excel_file.split("/")[-1].split(".")[0])  # 使用原文件名作为工作表名称
        for row in source_sheet.iter_rows():
            for cell in row:
                new_sheet[cell.coordinate].value = cell.value
        # 更新进度条
        progress_bar.update(1)
    # 删除默认创建的空白工作表
    new_workbook.remove(new_workbook['Sheet'])
    # 保存新工作簿
    new_workbook.save(out_put)

# 主函数main()
@functools.lru_cache(maxsize=1024)
def main():
    console = Console()
    excel_files = get_filelist()
    excel_folder = '/'.join(excel_files[0].split("/")[:-1])
    # 将所有选定工作薄汇总到一个工作表中
    user_input = input("是否将所有选定工作薄汇总到一个工作表中? (yes/no):")
    if user_input in ['yes', 'y', 'Y', '是', '是的']:
        # 将需要汇总的sheets汇总到一个新工作薄中
        console.print("将所有选定工作薄中的指定名称工作表,汇总到一个工作表中:", style='bold red')
        all2onebook(excel_files, '实验结果', os.path.join(excel_folder, "合并工作簿.xlsx"))

        # 将需要汇总的sheets汇总到一个新工作薄的sheet中
        console.print("将所有选定工作薄中的指定名称工作表,汇总到一个工作表中:", style='bold red')
        all2onesheet(excel_files, '实验结果', os.path.join(excel_folder, '实验结果汇总表.xlsx'))

if __name__ == '__main__':
    # 开始主函数功能
    main()

# 打包方法： nuitka --mingw64 --standalone --plugin-enable=tk-inter --windows-icon-from-ico="icon.ico" --noinclude-default-mode=error  .\main.py

nizsm123

3KY，谢谢，研究下！

pb297110281

感谢分享～～！！

weiyanli

感谢楼主热心分享

Ambitious

学习一下

kaisen868

感谢分享思路。

xxa

感谢分享，试用一下

众益科技

无法启动此程序，因为计算机中丢失 api-ms-win-core-path-l1-1-0.dll尝试重新安装该程序以解决此问题。

1107934496

众益科技 发表于 2023-8-18 14:00
无法启动此程序，因为计算机中丢失 api-ms-win-core-path-l1-1-0.dll尝试重新安装该程序以解决此问题。

抱歉，我尝试了使用其他打包工具进行打包，没能打包成功。
您可以试试使用python运行原始程序