import tkinter as tk
from tkinter import ttk, filedialog, messagebox, scrolledtext
import pandas as pd
import os
import threading
import tempfile
import zipfile
import shutil
import configparser
from xml.etree import ElementTree as ET
from openpyxl import load_workbook
import re
# 辅助函数：将Excel列字母转换为索引（A=0, B=1, ..., AA=26等）
def excel_column_letter_to_index(letter):
"""Convert Excel column letter to zero-based index"""
index = 0
for char in letter:
index = index * 26 + (ord(char.upper()) - ord('A') + 1)
return index - 1
class ExcelComparatorApp:
def __init__(self, root):
self.root = root
self.root.title("Excel 文件比较工具")
self.root.geometry("1000x700")
self.root.minsize(900, 600)
# 加载配置
self.config = configparser.ConfigParser()
self.config_path = os.path.join(os.path.expanduser("~"), "excel_comparator_config.ini")
self.load_config()
# 初始化配置相关的属性
self.initialize_config_based_attributes()
# 配置样式
self.style = ttk.Style()
self.style.configure("TFrame", padding=10)
self.style.configure("TButton", padding=6)
self.style.configure("TLabel", padding=5)
self.style.configure("TCheckbutton", padding=5)
self.style.configure("Accent.TButton", background="#4CAF50", foreground="white", font=("Arial", 10, "bold"))
# 主容器使用网格布局管理器
self.main_frame = ttk.Frame(root)
self.main_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 创建界面
self.create_interface()
# 初始化变量
self.old_file = ""
self.new_file = ""
# 确保界面元素可见
self.root.update_idletasks()
# 初始化后尝试加载列名
self.root.after(100, self.try_load_columns)
def initialize_config_based_attributes(self):
"""初始化所有配置相关的属性"""
# 文件路径
self.old_file = ""
self.new_file = ""
# 表格设置
self.skiprows = self.config.getint('SETTINGS', 'skiprows', fallback=9)
# 列范围（使用Excel字母格式）
self.signal_start_col = 0
self.signal_end_col = 12
self.data_start_col = 53
self.data_end_col = 55
# 报告类型变量
self.generate_text_report = tk.BooleanVar(
value=self.config.getboolean('SETTINGS', 'generate_text_report', fallback=True))
self.generate_excel_report = tk.BooleanVar(
value=self.config.getboolean('SETTINGS', 'generate_excel_report', fallback=True))
# 列加载基准文件选项
self.col_load_based_on = tk.StringVar(
value=self.config.get('SETTINGS', 'col_load_based_on', fallback='old'))
# 列选择变量
self.signal_col_vars = {}
self.data_col_vars = {}
# 初始化其他变量
self.old_file_entry = None
self.new_file_entry = None
self.old_sheet_var = None
self.new_sheet_var = None
self.output_path_entry = None
self.log_text = None
self.status_var = None
self.compare_btn = None
# 进度条相关
self.progress = None
self.status_label = None
# 初始化 skiprows_var
self.skiprows_var = tk.StringVar(value=str(self.skiprows))
# 列范围变量
self.signal_start_var = tk.StringVar(value=self.config.get('SETTINGS', 'signal_start', fallback='A'))
self.signal_end_var = tk.StringVar(value=self.config.get('SETTINGS', 'signal_end', fallback='M'))
self.data_start_var = tk.StringVar(value=self.config.get('SETTINGS', 'data_start', fallback='BT'))
self.data_end_var = tk.StringVar(value=self.config.get('SETTINGS', 'data_end', fallback='CD'))
# 报告类型变量
self.text_report_var = tk.BooleanVar(value=self.generate_text_report.get())
self.excel_report_var = tk.BooleanVar(value=self.generate_excel_report.get())
def try_load_columns(self):
"""尝试在界面初始化后加载列名（如果文件已存在）"""
if self.old_file_entry.get() or self.new_file_entry.get():
self.load_columns_from_file()
def load_config(self):
"""加载配置文件"""
if os.path.exists(self.config_path):
try:
self.config.read(self.config_path)
except:
self.create_default_config()
else:
self.create_default_config()
def create_default_config(self):
"""创建默认配置"""
self.config['SETTINGS'] = {
'skiprows': '9',
'signal_start': 'A',
'signal_end': 'M',
'data_start': 'BT',
'data_end': 'CD',
'generate_text_report': 'True',
'generate_excel_report': 'True',
'col_load_based_on': 'old',
'output_path': ''
}
self.save_config()
def save_config(self):
"""保存配置到文件"""
with open(self.config_path, 'w') as configfile:
self.config.write(configfile)
def create_interface(self):
"""创建界面元素 - 使用网格布局"""
# 创建行计数器
row = 0
# 文件选择部分
file_frame = ttk.LabelFrame(self.main_frame, text="文件选择")
file_frame.grid(row=row, column=0, sticky="ew", padx=5, pady=5)
row += 1
# 旧文件选择
ttk.Label(file_frame, text="旧Excel文件:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
self.old_file_entry = ttk.Entry(file_frame, width=60)
self.old_file_entry.grid(row=0, column=1, padx=5, pady=5, sticky="ew")
self.old_file_entry.insert(0, self.config.get('SETTINGS', 'old_file', fallback=''))
ttk.Button(
file_frame,
text="浏览...",
command=lambda: self.browse_file(self.old_file_entry),
width=10
).grid(row=0, column=2, padx=5, pady=5)
# 旧文件Sheet配置
ttk.Label(file_frame, text="Sheet名称或索引:").grid(row=0, column=3, sticky="w", padx=(15, 5), pady=5)
self.old_sheet_var = tk.StringVar(value=self.config.get('SETTINGS', 'old_sheet', fallback=''))
self.old_sheet_entry = ttk.Entry(file_frame, textvariable=self.old_sheet_var, width=20)
self.old_sheet_entry.grid(row极=0, column=4, padx=5, pady=5, sticky="w")
# 新文件选择
ttk.Label(file_frame, text="新Excel文件:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
self.new_file极_entry = ttk.Entry(file_frame, width=60)
self.new_file_entry.grid(row=1, column=1, padx=5, pady=5, sticky="ew")
self.new_file_entry.insert(0, self.config.get('SETTINGS', 'new_file', fallback=''))
ttk.Button(
file_frame,
text="浏览...",
command=lambda: self.browse_file(self.new_file_entry),
width=10
).grid(row=1, column=2, padx=5, pady=5)
# 新文件Sheet配置
ttk.Label(file_frame, text="Sheet名称或索引:").grid(row=1, column=3, sticky="w", padx=(15, 5), pady=5)
self.new_sheet_var = tk.StringVar(value=self.config.get('SETTINGS', 'new_sheet', fallback=''))
self.new_sheet_entry = ttk.Entry(file_frame, textvariable=self.new_sheet_var, width=20)
self.new_sheet_entry.grid(row=1, column=4, padx=5, pady=5, sticky="w")
file_frame.columnconfigure(1, weight=1)
# 设置按钮
settings_btn = ttk.Button(
self.main_frame,
text="高级设置",
command=self.open_settings_dialog,
width=15
)
settings_btn.grid(row=row, column=0, sticky="e", padx=5, pady=5)
row += 1
# 列选择部分
cols_frame = ttk.LabelFrame(self.main_frame, text="选择要比较的列 (文件选择后自动加载)")
cols_frame.grid(row=row, column=0, sticky="nsew", padx=5, pady=5)
# 配置网格权重
self.main_frame.rowconfigure(row, weight=1)
self.main_frame.columnconfigure(0, weight=1)
cols_frame.columnconfigure(0, weight=1)
cols_frame.rowconfigure(0, weight=1)
# 创建两个面板容器
paned_window = ttk.PanedWindow(cols_frame, orient=tk.HORIZONTAL)
paned_window.grid(row=0, column=0, sticky="nsew", padx=5, pady=5)
cols_frame.columnconfigure(0, weight=1)
cols_frame.rowconfigure(0, weight=1)
# 信号级别列
signal_frame = ttk.LabelFrame(paned_window, text="信号级别列")
signal_frame.grid(row=0, column=0, sticky="nsew")
# 信号级别列的滚动区域
signal_scroll = ttk.Scrollbar(signal_frame)
signal_scroll.pack(side=tk.RIGHT, fill=tk.Y)
self.signal_canvas = tk.Canvas(signal_frame, yscrollcommand=signal_scroll.set)
self.signal_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
signal_scroll.config(command=self.signal_canvas.yview)
self.signal_inner_frame = ttk.Frame(self.signal_canvas)
self.signal_canvas.create_window((0, 0), window=self.signal_inner_frame, anchor="nw")
self.signal_inner_frame.bind(
"<Configure>",
lambda e: self.signal_canvas.configure(scrollregion=self.signal_canvas.bbox("all"))
)
paned_window.add(signal_frame, weight=1)
# 数据级别列
data_frame = ttk.LabelFrame(paned_window, text="数据级别列")
data_frame.grid(row=0, column=1, sticky="nsew")
# 数据级别列的滚动区域
data_scroll = ttk.Scrollbar(data_frame)
data_scroll.pack(side=tk.RIGHT, fill=tk.Y)
self.data_canvas = tk.Canvas(data_frame, yscrollcommand=data_scroll.set)
self.data_canvas.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
data_scroll.config(command=self.data_canvas.yview)
self.data_inner_frame = ttk.Frame(self.data_canvas)
self.data_canvas.create_window((0, 0), window=self.data_inner_frame, anchor="nw")
self.data_inner_frame.bind(
"<Configure>",
lambda e: self.data_canvas.configure(scrollregion=self.data_canvas.bbox("all"))
)
paned_window.add(data_frame, weight=1)
# 绑定鼠标滚轮事件
self.signal_canvas.bind("<MouseWheel>", lambda e: self.signal_canvas.yview_scroll(int(-1*(e.delta/120)), "units"))
self.data_canvas.bind("<MouseWheel>", lambda e: self.data_canvas.yview_scroll(int(-1*(e.delta/120)), "units"))
row += 1
# 输出路径
output_frame = ttk.LabelFrame(self.main_frame, text="输出设置")
output_frame.grid(row=row, column=0, sticky="ew", padx=5, pady=5)
row += 1
# 输出路径
ttk.Label(output_frame, text="输出路径:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
self.output_path_entry = ttk.Entry(output_frame)
self.output_path_entry.grid(row=0, column=1, padx=5, pady=5, sticky="ew")
self.output_path_entry.insert(0, self.config.get('SETTINGS', 'output_path', fallback=''))
ttk.Button(
output_frame,
text="浏览...",
command=self.browse_output_path,
width=10
).grid(row=0, column=2, padx=5, p极ady=5)
output_frame.columnconfigure(1, weight=1)
# 按钮区域
button_frame = ttk.Frame(self.main_frame)
button_frame.grid(row=row, column=0, sticky="ew", padx=5, pady=10)
row += 1
# 比较按钮
self.compare_btn = ttk.Button(
button_frame,
text="开始比较",
command=self.start_comparison_thread,
width=30,
style="Accent.TButton"
)
self.compare_btn.pack(pady=10)
# 进度条
self.progress = ttk.Progressbar(
button_frame,
orient=tk.HORIZONTAL,
length=400,
mode='indeterminate'
)
self.progress.pack(pady=5, fill=tk.X, expand=True)
self.progress.pack_forget() # 初始隐藏
# 状态栏
self.status_var = tk.StringVar(value="就绪")
status_bar = ttk.Label(self.main_frame, textvariable=self.status_var, relief=tk.SUNKEN, anchor=tk.W)
status_bar.grid(row=row, column=0, sticky="ew", padx=5, pady=5)
row += 1
# 日志区域
log_frame = ttk.LabelFrame(self.main_frame, text="日志输出")
log_frame.grid(row=row, column=0, sticky="nsew", padx=5, pady=5)
self.main_frame.rowconfigure(row, weight=1) # 日志区域获得额外空间
self.log_text = scrolledtext.ScrolledText(
log_frame,
wrap=tk.WORD,
height=10
)
self.log_text.pack(fill="both", expand=True, padx=5, pady=5)
self.log_text.config(state=tk.DISABLED)
# 添加标签样式
self.log_text.tag_config("error", foreground="red")
self.log_text.tag_config("success", foreground="green")
self.log_text.tag_config("warning", foreground="orange")
def open_settings_dialog(self):
"""打开设置对话框"""
dialog = tk.Toplevel(self.root)
dialog.title("高级设置")
dialog.geometry("550x400")
dialog.transient(self.root)
dialog.grab_set()
# 对话框框架
main_frame = ttk.Frame(dialog, padding=10)
main_frame.pack(fill=tk.BOTH, expand=True)
# 列名行配置
row_frame = ttk.LabelFrame(main_frame, text="列名行配置")
row_frame.pack(fill=tk.X, padx=5, pady=5)
ttk.Label(row_frame, text="列名所在行索引 (0表示第一行):").grid(row=0, column=0, sticky="w", padx=5, pady=5)
skiprows_entry = ttk.Entry(row_frame, textvariable=self.skiprows_var, width=10)
skiprows_entry.grid(row=0, column=1, padx=5, pady=5, sticky="w")
# 列范围配置
range_frame = ttk.LabelFrame(main_frame, text="列范围配置 (Excel列字母)")
range_frame.pack(fill=tk.X, padx=5, pady=5)
# 信号级别列范围
ttk.Label(range_frame, text="信号级别列范围:").grid(row=0, column=0, sticky="w", padx=5, pady=5)
signal_start_entry = ttk.Entry(range_frame, textvariable=self.signal_start_var, width=5)
signal_start_entry.grid(row=0, column=1, padx=5, pady=5, sticky="w")
ttk.Label(range_frame, text="至").grid(row=0, column=2, padx=5, pady=5)
signal_end_entry = ttk.Entry(range_frame, textvariable=self.signal_end_var, width=5)
signal极_end_entry.grid(row=0, column=3, padx=5, pady=5, sticky="w")
# 数据级别列范围
ttk.Label(range_frame, text="数据级别列范围:").grid(row=1, column=0, sticky="w", padx=5, pady=5)
data_start_entry = ttk.Entry(range_frame, textvariable=self.data_start_var, width=5)
data_start_entry.grid(row=1, column=1, padx=5, pady=5, sticky="w")
ttk.Label(range_frame, text="至").grid(row=1, column=2, padx=5, pady=5)
data_end_entry = ttk.Entry(range_frame, textvariable=self.data_end_var, width=5)
data_end_entry.grid(row=1, column=3, padx=5, pady=5, sticky="w")
# 列加载基准文件设置
col_based_frame = ttk.LabelFrame(main_frame, text="列加载基准文件")
col_based_frame.pack(fill=tk.X, padx=5, pady=5)
ttk.Radiobutton(
col_based_frame,
text="基于旧文件加载列",
variable=self.col_load_based_on,
value="old"
).pack(anchor=tk.W, padx=10, pady=3)
ttk.Radiobutton(
col_based_frame,
text="基于新文件加载列",
variable=self.col_load_based_on,
value="new"
).pack(anchor=tk.W, padx=10, pady=3)
# 输出设置
output_frame = ttk.LabelFrame(main_frame, text="输出设置")
output_frame.pack(fill=tk.X, pad极x=5, pady=5)
# 报告输出
ttk.Checkbutton(
output_frame,
text="生成文本报告",
variable=self.text_report_var
).pack(anchor=tk.W, padx=10, pady=5)
ttk.Checkbutton(
output_frame,
text="生成Excel报告",
variable=self.excel_report_var
).pack(anchor=tk.W, padx=10, pady=5)
# 应用按钮
btn_frame = ttk.Frame(main_frame)
btn_frame.pack(fill=tk.X, pady=10)
ttk.Button(
btn_frame,
text="应用设置",
command=lambda: self.apply_settings(dialog),
width=15
).pack(side=tk.RIGHT, padx=5)
ttk.Button(
btn_frame,
text="保存设置",
command=self.save_settings,
width=15
).pack(side=tk.RIGHT, padx=5)
ttk.Button(
btn_frame,
text="重置为默认",
command=self.reset_settings,
width=15
).pack(side=tk.LEFT, padx=5)
def reset_settings(self):
"""重置设置为默认值"""
self.skiprows_var.set('9')
self.signal_start_var.set('A')
self.signal_end_var.set('M')
self.data_start_var.set('BT')
self.data_end_var.set('CD')
self.text_report_var.set(True)
self.excel_report_var.set(True)
self.col_load_based_on.set('old')
self.log("设置已重置为默认值")
def save_settings(self):
"""保存当前设置"""
try:
# 更新配置对象
self.config['SETTINGS'] = {
'skiprows': self.skiprows_var.get(),
'signal_start': self.signal_start_var.get(),
'signal_end': self.signal_end_var.get(),
'data_start': self.data_start_var.get(),
'data_end': self.data_end_var.get(),
'generate_text_report': str(self.text_report_var.get()),
'generate_excel_report': str(self.excel_report_var.get()),
'col_load_based_on': self.col_load_based_on.get(),
'old_file': self.old_file_entry.get(),
'new_file': self.new_file_entry.get(),
'old_sheet': self.old_sheet_var.get(),
'new_sheet': self.new_sheet_var.get(),
'output_path': self.output_path_entry.get()
}
# 保存到文件
self.save_config()
self.log("设置已成功保存")
except Exception as e:
self.log(f"保存设置失败: {str(e)}", error=True)
def apply_settings(self, dialog=None):
"""应用设置并关闭对话框"""
try:
# 应用列范围设置
self.apply_column_range()
# 保存设置
self.save_settings()
# 重新加载列
self.load_columns_from_file()
# 关闭对话框
if dialog:
dialog.destroy()
except Exception as e:
self.log(f"设置应用错误: {str(e)}", error=True)
def browse_file(self, entry_widget):
"""浏览文件并设置到输入框"""
file_path = filedialog.askopenfilename(
filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
)
if file_path:
entry_widget.delete(0, tk.END)
entry_widget.insert(0, file_path)
# 文件选择后自动加载列
self.load_columns_from_file()
def browse_output_path(self):
"""浏览输出路径"""
dir_path = filedialog.askdirectory()
if dir_path:
self.output_path_entry.delete(0, tk.END)
self.output_path_entry.insert(0, dir_path)
def apply_column_range(self):
"""应用列范围设置（使用Excel字母格式）"""
try:
# 将字母转换为数字索引
self.signal_start_col = excel_column_letter_to_index(self.signal_start_var.get())
self.signal_end_col = excel_column_letter_to_index(self.signal_end_var.get())
self.data_start_col = excel_column_letter_to_index(self.data_start_var.get())
self.data_end_col = excel_column_letter_to_index(self.data_end_var.get())
self.log(f"列范围设置已应用: 信号列[{self.signal_start_var.get()} ({self.signal_start_col}) - {self.signal_end_var.get()} ({self.signal_end_col})], 数据列[{self.data_start_var.get()} ({self.data_start_col}) - {self.data_end_var.get()} ({self.data_end_col})]")
except ValueError:
self.log("错误: 请输入有效的列范围索引", error=True)
def load_columns_from_file(self):
"""从文件中加载列名（显示索引位置）并根据范围过滤"""
try:
# 确定基于哪个文件加载列
if self.col_load_based_on.get() == 'old':
file_path = self.old_file_entry.get()
sheet_name = self.old_sheet_var.get().strip()
else:
file_path = self.new_file_entry.get()
sheet_name = self.new_sheet_var.get().strip()
if not file_path:
return
skiprows = int(self.skiprows_var.get()) if self.skiprows_var.get().isdigit() else 9
# 如果sheet_name为空，则使用0（第一个sheet）
if not sheet_name and sheet_name != "0":
sheet_name = 0
else:
try:
sheet_name = int(sheet_name)
except ValueError:
pass # 保持为字符串
df = self.safe_read_excel(file_path, skiprows, sheet_name)
if df is None:
self.log(f"无法加载文件: {file_path}", error=True)
return
# 清空现有列选择
for widget in self.signal_inner_frame.winfo_children():
widget.destroy()
for widget in self.data_inner_frame.winfo_children():
widget.destroy()
# 创建列选择复选框
self.signal_col_vars = {}
self.data_col_vars = {}
# 获取配置的列范围
signal_start = self.signal_start_col
signal_end = self.signal_end_col
data_start = self.data_start_col
data_end = self.data_end_col
# 信号级别列
for i, col in enumerate(df.columns):
if signal_start <= i <= signal_end:
var = tk.BooleanVar(value=True)
cb = ttk.Checkbutton(
self.signal_inner_frame,
text=f"{i}: {col}",
variable=var
)
cb.pack(anchor=tk.W, padx=5, pady=2)
self.signal_col_vars[col] = var
# 数据级别列
for i, col in enumerate(df.columns):
if data_start <= i <= data_end:
var = tk.BooleanVar(value=True)
cb = ttk.Checkbutton(
self.data_inner_frame,
text=f"{i}: {col}",
variable=var
)
cb.pack(anchor=tk.W, padx=5, pady=2)
self.data_col_vars[col] = var
self.log(f"列名已从文件加载 ({self.col_load_based_on.get()}文件)")
except Exception as e:
self.log(f"加载列名失败: {str(e)}", error=True)
def start_comparison_thread(self):
"""启动比较线程"""
# 禁用按钮避免重复点击
self.compare_btn.config(state=tk.DISABLED)
self.status_var.set("正在比较...")
# 显示进度条
self.progress.pack(pady=5, fill=tk.X, expand=True)
self.progress.start(10)
# 获取输入参数
self.old_file = self.old_file_entry.get()
self.new_file = self.new_file_entry.get()
self.skiprows = int(self.skiprows_var.get()) if self.skiprows_var.get().isdigit() else 9
# 验证文件是否存在
if not os.path.exists(self.old_file) or not os.path.exists(self.new_file):
self.log("错误: 文件不存在", error=True)
self.comparison_finished()
return
# 获取选择的列
self.signal_cols = [col for col, var in self.signal_col_vars.items() if var.get()]
self.data_cols = [col for col, var in self.data_col_vars.items() if var.get()]
if not self.signal_cols or not self.data_cols:
self.log("错误: 请选择要比较的列", error=True)
self.comparison_finished()
return
# 获取新旧文件的Sheet名称
old_sheet = self.old_sheet_var.get().strip()
new_sheet = self.new_sheet_var.get().strip()
# 如果为空，则使用第一个Sheet
if not old_sheet and old_sheet != "0":
old_sheet = 0
else:
try:
old_sheet = int(old_sheet)
except:
pass # 保持为字符串
if not new_sheet and new_sheet != "0":
new_sheet = 0
else:
try:
new_sheet = int(new_sheet)
except:
pass
# 启动后台线程
threading.Thread(target=self.compare_excel_wrapper, args=(old_sheet, new_sheet), daemon=True).start()
def compare_excel_wrapper(self, old_sheet, new_sheet):
"""比较Excel文件的包装函数"""
try:
# 执行比较
comparison_result = self.compare_excel(self.old_file, self.new_file, self.skiprows, old_sheet, new_sheet)
if comparison_result is not None and not comparison_result.empty:
# 保存结果
output_dir = self.output_path_entry.get() or os.getcwd()
if not os.path.exists(output_dir):
os.makedirs(output_dir)
# 生成Excel报告
if self.excel_report_var.get():
excel_path = os.path.join(output_dir, "comparison_result.xlsx")
comparison_result.to_excel(excel_path, index=False)
self.log(f"Excel报告保存到: {excel_path}")
# 生成文本报告
if self.text_report_var.get():
report_path = os.path.join(output_dir, "comparison_report.txt")
report = self.generate_report(comparison_result)
with open(report_path, 'w', encoding='utf-8') as f:
f.write(re极port)
self.log(f"文本报告保存到: {report_path}")
self.log(f"比较完成! 发现 {len(comparison_result)} 处变更", success=True)
else:
self.log("比较完成! 未发现变更", success=True)
except Exception as e:
self.log(f"比较过程中发生错误: {str(e)}", error=True)
import traceback
self.log(traceback.format_exc(), error=True)
finally:
self.comparison_finished()
def comparison_finished(self):
"""比较完成后的清理工作"""
self.progress.stop()
self.progress.pack_forget() # 隐藏进度条
self.compare_btn.config(state=tk.NORMAL)
self.status_var.set("完成")
def log(self, message, error=False, success=False):
"""记录日志消息"""
self.log_text.config(state=tk.NORMAL)
if error:
self.log_text.insert(tk.END, "[错误] ")
self.log_text.tag_add("error", "end-1c linestart", "end-1c lineend")
elif success:
self.log_text.insert(tk.END, "[成功] ")
self.log_text.tag_add("success", "end-1c linestart", "end-1c lineend")
else:
self.log_text.insert(tk.END, "[信息] ")
self.log_text.insert(tk.END, message + "\n")
self.log_text.see(tk.END)
self.log_text.config(state=tk.DISABLED)
# ======================== 以下是核心比较功能 ========================
def repair_xlsx_file(self, file_path):
"""修复损坏的.xlsx文件"""
temp_dir = tempfile.mkdtemp()
repaired_file = os.path.join(temp_dir, "repaired.xlsx")
try:
# 解压原文件
with zipfile.ZipFile(file_path, 'r') as zip_ref:
zip_ref.extractall(temp_dir)
# 修复workbook.xml中的无效GUID
workbook_path = os.path.join(temp_dir, 'xl', 'workbook.xml')
if os.path.exists(workbook_path):
tree = ET.parse(workbook_path)
root = tree.getroot()
# 修复无效GUID
for view in root.findall('.//{http://schemas.openxmlformats.org/spreadsheetml/2006/main}workbookView'):
guid = view.get('guid', '')
if not guid or not guid.startswith('{'):
import uuid
new_guid = '{' + str(uuid.uuid4()) + '}'
view.set('guid', new_guid)
# 保存修复后的XML
tree.write(workbook_path, encoding='UTF-8', xml_declaration=True)
# 重新打包为修复后的Excel文件
with zipfile.ZipFile(repaired_file, 'w', zipfile.ZIP_DEFLATED) as zipf:
for root_dir, _, files in os.walk(temp_dir):
for file in files:
if file != "repaired.xlsx":
file_path = os.path.join(root_dir, file)
arcname = os.path.relpath(file_path, temp_dir)
zipf.write(file_path, arcname)
return repaired_file
except Exception as e:
self.log(f"修复文件失败: {str(e)}", error=True)
return None
finally:
# 清理临时目录
shutil.rmtree(temp_dir, ignore_errors=True)
def safe_read_excel(self, file_path, skiprows, sheet_name=None):
"""安全的Excel读取函数，包含自动修复功能和Sheet选择"""
if sheet_name is None:
sheet_name = 0 # 默认为第一个Sheet
try:
self.log(f"尝试直接读取: {file_path} (Sheet: {sheet_name})")
df = pd.read_excel(
file_path,
skiprows=skiprows,
dtype=str,
engine='openpyxl',
sheet_name=sheet_name
).fillna('')
self.log(f"直接读取成功: {file_path}")
return df
except Exception as e:
self.log(f"直接读取失败: {str(e)}", error=True)
# 尝试修复文件
self.log("尝试修复Excel文件...")
repaired_path = self.repair_xlsx_file(file_path)
if repaired_path:
self.log(f"已创建修复文件: {repaired_path}")
try:
# 尝试读取修复后的文件
df = pd.read_excel(
repaired_path,
skiprows=skiprows,
dtype=str,
engine='openpyxl',
sheet_name=sheet_name
).fillna('')
self.log("修复文件读取成功!", success=True)
return df
except Exception as e:
self.log(f"修复文件读取失败: {str(e)}", error=True)
# 最后尝试：使用二进制模式读取
self.log("尝试二进制读取作为最后手段")
try:
with open(file_path, 'rb') as f:
# 尝试不同的引擎
for engine in ['openpyxl', 'xlrd', 'odf']:
try:
df = pd.read_excel(
f,
skiprows=skiprows,
dtype=str,
engine=engine,
sheet_name=sheet_name
).fillna('')
self.log(f"成功使用引擎: {engine}", success=True)
return df
except:
continue
except Exception as e:
self.log(f"二进制读取失败: {str(e)}", error=True)
# 终极手段：手动提取数据
self.log("所有方法失败，尝试手动提取数据")
try:
wb = load_workbook(file_path, read_only=True, data_only=True, keep_vba=False)
# 获取指定Sheet
if isinstance(sheet_name, int):
if sheet_name < len(wb.sheetnames):
ws = wb.worksheets[sheet_name]
else:
ws = wb.active
else:
if sheet_name in wb.sheetnames:
ws = wb[sheet_name]
else:
ws = wb.active
data = []
for i, row in enumerate(ws.iter_rows(values_only=True)):
if i < skiprows:
continue
data.append(row)
headers = data[0] if data else []
df = pd.DataFrame(data[1:], columns=headers)
return df.fillna('').astype(str)
except Exception as e:
raise ValueError(f"无法读取文件 {file_path}: {str(e)}")
def find_met_columns(self, df, is_old_file=True):
"""
查找MET列
- 旧文件：查找包含"MET"的列（V列和W列）
- 新文件：查找包含"MET"的列（AE列）
"""
met_cols = []
# 尝试通过列名匹配
for col in df.columns:
if "MET" in col.upper():
met_cols.append(col)
# 如果未找到，尝试按位置查找
if not met_cols:
self.log(f"警告: 通过列名未找到MET列，尝试按位置查找")
if is_old_file:
# 旧文件：V列(22)和W列(23)，索引21和22
if len(df.columns) > 22:
met_cols = [df.columns[21], df.columns[22]]
self.log(f"使用位置索引的MET列: {met_cols}")
elif len(df.columns) > 21:
met_cols = [df.columns[21]]
else:
# 新文件：AE列(31)，索引30
if len(df.columns) > 30:
met_cols = [df.columns[30]]
self.log(f"使用位置索引的MET列: {met_cols}")
if not met极_cols:
self.log(f"严重警告: 未找到任何MET列，创建虚拟列以避免错误")
# 创建虚拟列
df['MET_DUMMY'] = ''
met_cols = ['MET_DUMMY']
return met_cols
def check_met_status(self, row, met_cols):
"""
检查行的MET状态
如果任一MET列包含'T'或'R'，则返回True
"""
for col in met_cols:
if col in row.index:
met_value = str(row[col]).strip().upper()
if met_value in ['T', 'R']:
return True
return False
def build_signal_hierarchy(self, df, signal_col, data_col):
"""构建信号-数据的层级结构"""
signals = {}
current_signal = None
for idx, row in df.iterrows():
# 检查是否是信号行（信号名列非空）
row_data = row.to_dict() # 转换为字典以便处理
if pd.notna(row_data.get(signal_col)) and str(row_data.get(signal_col)).strip():
# 新信号开始
current_signal = str(row_data.get(signal_col)).strip()
signals[current_signal] = {
'signal_row': row_data,
'data_items': []
}
elif current_signal and pd.notna(row_data.get(data_col)) and str(row_data.get(data_col)).strip():
# 数据项属于当前信号
signals[current_signal]['data_items'].append(row_data)
return signals
def compare_excel(self, old_file, new_file, skiprows, old_sheet, new_sheet):
"""核心比较函数"""
# 使用安全的读取方式
self.log(f"正在读取旧文件: {old_file} (Sheet: {old_sheet})")
df_old = self.safe_read_excel(old_file, skiprows, old_sheet)
self.log(f"正在读取新文件: {new_file} (Sheet: {new_sheet})")
df_new = self.safe_read_excel(new_file, skiprows, new_sheet)
# 标准化列名（处理可能的换行符问题）
def clean_col_name(col):
return str(col).replace('\n', '').strip()
df_old.columns = [clean_col_name(col) for col in df_old.columns]
df_new.columns = [clean_col_name(col) for col in df_new.columns]
# 定义关键列的默认名称
SIGNAL_NAME_COL_DEFAULT = 'ﾌﾚｰﾑ名'
DATA_NAME_COL_DEFAULT = 'ﾃﾞｰﾀ名'
# 自动检测信号列和数据列
signal_col_candidates = [col for col in df_old.columns if '名' in col or 'frame' in col.lower()]
signal_col = signal_col_candidates[0] if signal_col_candidates else df_old.columns[0]
data_col_candidates = [col for col in df_old.columns if 'ﾃﾞｰﾀ' in col or 'data' in col.lower()]
data_col = data_col_candidates[0] if data_col_candidates else df_old.columns[1]
self.log(f"使用信号列: '{signal_col}'")
self.log(f"使用数据列: '{data_col}'")
# 查找MET列
old_met_cols = self.find_met_colu极mns(df_old, is_old_file=True)
new_met_cols = self.find_met_columns(df_new, is_old_file=False)
self.log(f"旧文件MET列: {old_met_cols}")
self.log(f"新文件MET列: {new_met_cols}")
# 存储最终结果
results = []
# 构建旧文件和新文件的层级结构
old_signals = self.build_signal_hierarchy(df_old, signal_col, data_col)
new_signals = self.build_signal_hierarchy(df_new, signal_col, data_col)
self.log(f"旧文件找到 {len(old_signals)} 个信号")
self.log(f"新文件找到 {len(new_signals)} 个信号")
# ====================================================
# 步骤1: 比较信号级别的增减和变更
# ====================================================
# 检查删除的信号
for signal_name in set(old_signals.keys()) - set(new_signals.keys()):
signal_data = old_signals[signal_name]
# 检查MET条件
if self.check_met_status(signal_data['signal_row'], old_met_cols):
results.append({
'信号名': signal_name,
'变更类型': '信号删除',
'数据名': '',
'变更详情': f"整个信号被删除（满足MET条件）"
})
# 检查新增的信号
for signal_name in set(new_signals.keys()) - set(old_signals.keys()):
signal_data = new_signals[signal_name]
# 检查MET条件
if self.check_met_status(signal_data['signal_row'], new_met_cols):
results.append({
'信号名': signal_name,
'变更类型': '信号新增',
'数据名': '',
'变更详情': f"整个信号被新增（满足MET条件）"
})
# 检查共有信号的变更
for signal_name in set(old_signals.keys()) & set(new_signals.keys()):
old_signal = old_signals[signal_name]
new_signal = new_signals[signal_name]
# 检查信号层面属性变更
signal_changes = []
for col in self.signal_cols:
if col in old_signal['signal_row'] and col in new_signal['signal_row']:
old_val = str(old_signal['signal_row'][col]).strip()
new_val = str(new_signal['signal_row'][col]).strip()
if old_val != new_val:
signal_changes.append(f"{col}:[{old_val}]→[{new_val}]")
# 如果有信号级别的变更且满足MET条件
if signal_changes and (self.check_met_status(old_signal['signal_row'], old_met_cols) or
self.check_met_status(new_signal['signal_row'], new_met_cols)):
results.append({
'信号名': signal_name,
'变更类型': '信号变更',
'数据名': '',
'变更详情': "; ".join(signal_changes)
})
# ====================================================
# 步骤2: 比较数据级别的增减和变更
# ====================================================
# 创建数据名映射
old_data_map = {str(row[data_col]).strip(): row for row in old_signal['data_items']}
new_data_map = {str(row[data_col]).strip(): row for row in new_signal['data_items']}
# 检查删除的数据项
for data_name in set(old_data_map.keys()) - set(new_data_map.keys()):
old_row = old_data_map[data_name]
if self.check_met_status(old_row, old_met_cols):
results.append({
'信号名': signal_name,
'变更类型': '数据删除',
'数据名': data_name,
'变更详情': f"数据被删除（满足MET条件）"
})
# 检查新增的数据项
for data_name in set(new_data_map.keys()) - set(old_data_map.keys()):
new_row = new_data_map[data_name]
if self.check_met_status(new_row, new_met_cols):
results.append({
'信号名': signal_name,
'变更类型': '数据新增',
'数据名': data_name,
'变更详情': f"数据被新增（满足MET条件）"
})
# 检查变更的数据项
for data_name in set(old_data_map.keys()) & set(new_data_map.keys()):
old_row = old_data_map[data_name]
new_row = new_data_map[data_name]
# 检查数据层面属性变更
data_changes = []
for col in self.data_cols:
if col in old_row and col in new_row:
old_val = str(old_row[col]).strip()
new_val = str(new_row[col]).strip()
if old_val != new_val:
data_changes.append(f"{col}:[{old_val}]→[{new_val}]")
# 如果有数据级别的变更且满足MET条件
if data_changes and (self.check_met_status(old_row, old_met_cols) or self.check_met_status(new_row, new_met_cols)):
results.append({
'信号名': signal_name,
'变更类型': '数据变更',
'数据名': data_name,
'变更详情': "; ".join(data_changes)
})
# 转为结果DataFrame
result_df = pd.DataFrame(results)
# 如果结果为空，添加一行提示信息
if result_df.empty:
result_df = pd.DataFrame([{
'信号名': '无变更',
'变更类型': '无变更',
'数据名': '',
'变更详情': '未发现满足MET条件的变更'
}])
return result_df
def generate_report(self, comparison_result):
"""生成文本格式的详细报告"""
report = []
# 检查是否需要生成报告
if comparison_result.empty or (len(comparison_result) == 1 and comparison_result.iloc[0]['信号名'] == '无变更'):
return "比较完成，未发现满足MET条件的变更"
# 分组处理：按信号名分组
grouped = comparison_result.groupby('信号名')
for signal, group in grouped:
report.append(f"\n信号: {signal}")
# 处理信号级别的变更
signal_changes = group[group['数据名'] == '']
for _, row in signal_changes.iterrows():
if row['变更类型'] == '信号删除':
report.append(f" - 信号删除: {row['变更详情']}")
elif row['变更类型'] == '信号新增':
report.append(f" - 信号新增: {row['变更详情']}")
elif row['变更类型'] == '信号变更':
report.append(f" - 信号变更: {row['变更详情']}")
# 处理数据级别的变更
data_changes = group[group['数据名'] != '']
if not data_changes.empty:
# 数据删除
deleted_data = data_changes[data_changes['变更类型'] == '数据删除']
for _, row in deleted_data.iterrows():
report.append(f" - 数据删除: {row['数据名']} - {row['变更详情']}")
# 数据新增
added_data = data_changes[data_changes['变更类型'] == '数据新增']
for _, row in added_data.iterrows():
report.append(f" - 数据新增: {row['数据名']} - {row['变更详情']}")
# 数据变更
changed_data = data_changes[data_changes['变更类型'] == '数据变更']
for _, row in changed_data.iterrows():
report.append(f" - 数据变更: {row['数据名']} - {row['变更详情']}")
# 添加总结信息
summary = f"\n\n总结:\n"
summary += f"总变更数: {len(comparison_result)}\n"
summary += f"- 信号级别变更: {len(comparison_result[comparison_result['变更类型'].str.contains('信号')])}\n"
summary += f"- 数据级别变更: {len(comparison_result[comparison_result['变更类型'].str.contains('数据')])}"
report.append(summary)
return "\n".join(report)
# ===== 主程序入口 =====
if __name__ == "__main__":
root = tk.Tk()
app = ExcelComparatorApp(root)
root.mainloop()
[Running] python -u "e:\system\Desktop\项目所需文件\工具\CAN bit比较工具\Diff Analyzer.py"
Traceback (most recent call last):
File "e:\system\Desktop\\u9879�ڏ�������\�H��\CAN bit��\u8f83�H��\Diff Analyzer.py", line 1148, in <module>
app = ExcelComparatorApp(root)
^^^^^^^^^^^^^^^^^^^^^^^^
File "e:\system\Desktop\\u9879�ڏ�������\�H��\CAN bit��\u8f83�H��\Diff Analyzer.py", line 50, in __init__
self.create_interface()
File "e:\system\Desktop\\u9879�ڏ�������\�H��\CAN bit��\u8f83�H��\Diff Analyzer.py", line 180, in create_interface
self.old_sheet_entry.grid(row\u6781=0, column=4, padx=5, pady=5, sticky="w")
File "C:\Users\cheny9210\AppData\Local\Programs\Python\Python312\Lib\tkinter\__init__.py", line 2580, in grid_configure
self.tk.call(
_tkinter.TclError: bad option "-row\u6781": must be -column, -columnspan, -in, -ipadx, -ipady, -padx, -pady, -row, -rowspan, or -sticky
本文记录了一个在使用Python解析plist文件时遇到的错误：文件格式不正确导致无法正常解析。错误发生在尝试从plist文件中读取内容并转换为XML元素树的过程中。
扫一扫
265
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
