import tkinter as tk
from tkinter import filedialog, ttk, messagebox
import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Border, PatternFill, Alignment, Protection, Copy
import threading
import os
class ExcelDiffApp:
def __init__(self, root):
self.root = root
self.root.title("Excel差分工具")
self.root.geometry("800x600")
# 文件路径变量
self.new_file_path = tk.StringVar()
self.old_file_path = tk.StringVar()
# 创建界面组件
self.create_widgets()
def create_widgets(self):
# 文件选择区域（使用grid布局）
frame_files = tk.Frame(self.root)
frame_files.pack(pady=10, fill=tk.X)
tk.Label(frame_files, text="新版本Excel:", width=15).grid(row=0, column=0, sticky=tk.W)
tk.Entry(frame_files, textvariable=self.new_file_path, width=50).grid(row=0, column=1, padx=5)
tk.Button(frame_files, text="浏览", command=self.select_new_file).grid(row=0, column=2, padx=5)
tk.Label(frame_files, text="旧版本Excel:", width=15).grid(row=1, column=0, sticky=tk.W)
tk.Entry(frame_files, textvariable=self.old_file_path, width=50).grid(row=1, column=1, padx=5)
tk.Button(frame_files, text="浏览", command=self.select_old_file).grid(row=1, column=2, padx=5)
# 开始按钮
self.start_btn = tk.Button(self.root, text="开始差分", command=self.start_process, width=20)
self.start_btn.pack(pady=10)
# 进度条
self.progress = ttk.Progressbar(self.root, orient="horizontal", length=700, mode="determinate")
self.progress.pack(pady=10)
# 状态标签
self.status_label = tk.Label(self.root, text="就绪", fg="green")
self.status_label.pack(pady=5)
def select_new_file(self):
file = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
if file:
self.new_file_path.set(file)
def select_old_file(self):
file = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx *.xls")])
if file:
self.old_file_path.set(file)
def get_last_column(self, ws):
"""获取工作表中最后一列有内容的列号"""
max_col = 0
for row in ws.iter_rows():
for cell in row:
if cell.value is not None:
max_col = max(max_col, cell.column)
return max_col
def get_last_row(self, ws):
"""获取工作表中最后一行有内容的行号"""
max_row = 0
for row in ws.iter_rows():
for cell in row:
if cell.value is not None:
max_row = max(max_row, cell.row)
return max_row
def copy_cell_style(self, source_cell, target_cell):
"""复制单元格样式"""
# 处理字体
if source_cell.has_style:
target_cell.font = Font(**source_cell.font.__dict__)
target_cell.border = Border(**source_cell.border.__dict__)
# 处理填充
if isinstance(source_cell.fill, PatternFill):
target_cell.fill = PatternFill(**source_cell.fill.__dict__)
else:
target_cell.fill = source_cell.fill
target_cell.number_format = source_cell.number_format
target_cell.protection = Protection(**source_cell.protection.__dict__)
target_cell.alignment = Alignment(**source_cell.alignment.__dict__)
def process_sheet(self, ws_new, ws_old, sheet_name, total_sheets, current_idx):
# 获取新文件最后一列和最后一行
last_col_new = self.get_last_column(ws_new)
last_row_new = self.get_last_row(ws_new)
# 计算旧数据复制起始列（新文件B列对应旧文件T列）
# 新文件B列是2，旧文件T列是20，所以需要偏移18列
start_col = last_col_new + 18 # 例如：新最后一列是J（10），旧数据从T（20）开始
# 复制旧数据到新文件
for row in ws_old.iter_rows():
for cell in row:
new_col = start_col + (cell.column - 1)
new_cell = ws_new.cell(row=cell.row, column=new_col)
new_cell.value = cell.value
# 保留格式
self.copy_cell_style(cell, new_cell)
# 复制列宽
for col in ws_old.column_dimensions:
new_col = start_col + (openpyxl.utils.column_index_from_string(col) - 1)
new_col_letter = get_column_letter(new_col)
# 直接复制列宽属性
ws_new.column_dimensions[new_col_letter].width = ws_old.column_dimensions[col].width
ws_new.column_dimensions[new_col_letter].hidden = ws_old.column_dimensions[col].hidden
ws_new.column_dimensions[new_col_letter].outline_level = ws_old.column_dimensions[col].outline_level
ws_new.column_dimensions[new_col_letter].collapsed = ws_old.column_dimensions[col].collapsed
# 确定比较公式起始列（新文件B列对应比较公式H列）
# 新文件B列是2，比较公式从H列（8）开始，偏移6列
compare_start_col = last_col_new + 6 # 例如：新最后一列是J（10），比较从H（8）开始
# 获取旧数据列数（T列到W列共4列）
old_columns = 4
# 生成比较公式（从B列开始）
for row in ws_new.iter_rows(min_row=1, max_row=last_row_new):
for cell in row:
if cell.column <= last_col_new:
# 计算旧列位置（新列B对应旧列T，C对应U，D对应V，E对应W）
old_col = start_col + (cell.column - 2) # B列是2，T列是20，差值18
new_col_letter = get_column_letter(cell.column)
old_col_letter = get_column_letter(old_col)
formula = f'=IF(AND({new_col_letter}{cell.row}<>"",{old_col_letter}{cell.row}<>""),IF({new_col_letter}{cell.row}={old_col_letter}{cell.row},"无变更","变更"),IF({new_col_letter}{cell.row}<>"","新增",IF({old_col_letter}{cell.row}<>"","删除","无变更")))'
# 填充到H列到K列（对应B列到E列）
target_col = compare_start_col + (cell.column - 2) # B列对应H列（8）
ws_new.cell(row=cell.row, column=target_col).value = formula
# 生成N列统计公式（填充到L列）
n_col = compare_start_col + old_columns # H到K列共4列，L列是第5列
for row in ws_new.iter_rows(min_row=1, max_row=last_row_new):
# 计算比较范围（H1:K1，共4列）
start_range = get_column_letter(compare_start_col)
end_range = get_column_letter(compare_start_col + old_columns - 1)
formula_n = f'=IF(COUNTIF({start_range}{row[0].row}:{end_range}{row[0].row},"无变更")={old_columns},"无","有")'
ws_new.cell(row=row[0].row, column=n_col).value = formula_n
# 更新进度
progress = (current_idx + 1) / total_sheets * 100
self.progress["value"] = progress
self.status_label.config(text=f"处理中：{sheet_name}")
self.root.update_idletasks()
def start_process(self):
new_file = self.new_file_path.get()
old_file = self.old_file_path.get()
if not all([new_file, old_file]):
messagebox.showerror("错误", "请先选择新旧版本文件")
return
try:
self.start_btn.config(state=tk.DISABLED)
self.status_label.config(text="正在处理...", fg="orange")
# 加载工作簿
wb_new = openpyxl.load_workbook(new_file)
wb_old = openpyxl.load_workbook(old_file)
# 获取所有sheet
sheets = [sheet for sheet in wb_new.sheetnames if sheet in wb_old.sheetnames]
total_sheets = len(sheets)
# 多线程处理
def worker():
for idx, sheet_name in enumerate(sheets):
ws_new = wb_new[sheet_name]
ws_old = wb_old[sheet_name]
self.process_sheet(ws_new, ws_old, sheet_name, total_sheets, idx)
# 保存结果
output_path = os.path.join(os.path.dirname(new_file), "diff_result.xlsx")
wb_new.save(output_path)
self.root.after(0, self.show_completion, output_path)
threading.Thread(target=worker).start()
except Exception as e:
messagebox.showerror("错误", f"处理失败: {str(e)}")
self.start_btn.config(state=tk.NORMAL)
self.status_label.config(text="就绪", fg="green")
def show_completion(self, output_path):
self.progress["value"] = 100
self.status_label.config(text=f"完成！结果保存至: {output_path}", fg="green")
self.start_btn.config(state=tk.NORMAL)
if __name__ == "__main__":
root = tk.Tk()
app = ExcelDiffApp(root)
root.mainloop()，这个代码运行时出现问题，请解决PS C:\Users\chen.yang> & C:/Users/chen.yang/AppData/Local/Microsoft/WindowsApps/python3.13.exe d:/system/Desktop/Clear_excel.py
Traceback (most recent call last):
File "d:\system\Desktop\Clear_excel.py", line 5, in <module>
from openpyxl.styles import Font, Border, PatternFill, Alignment, Protection, Copy
ImportError: cannot import name 'Copy' from 'openpyxl.styles' (C:\Users\chen.yang\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.13_qbz5n2kfra8p0\LocalCache\local-packages\Python313\site-packages\openpyxl\styles\__init__.py)
PS C:\Users\chen.yang>
扫一扫
1019
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
