这有一段代码，增加一些功能，里面显示数据的表格，大小改成动态调整的，目前我里面的逻辑，正确的数据显示绿色，错误的是红色，在这个表格的左上角，加两个勾选，显示成功，和显示失败，默认是两个都显示，以下为代码import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import pandas as pd
import os
import subprocess
class ExcelViewerApp:
def __init__(self, root):
self.root = root
self.root.title("TPC效率化工具")
self.root.geometry("1000x700")
# 使用网格布局的主容器
self.main_frame = tk.Frame(root, bg='#f0f0f0', padx=15, pady=15)
self.main_frame.pack(fill=tk.BOTH, expand=True)
# 配置网格
self.main_frame.columnconfigure(0, weight=0)
self.main_frame.columnconfigure(1, weight=1)
self.main_frame.columnconfigure(2, weight=0)
self.main_frame.columnconfigure(3, weight=0)
# === 主表控件 ===
# 文件路径行
tk.Label(self.main_frame, text="主表文件路径:", bg='#f0f0f0').grid(row=0, column=0, sticky='w', padx=(0, 5))
self.path_label = tk.Label(
self.main_frame,
text="未选择主表文件",
anchor='w',
relief=tk.SUNKEN,
bg="#ffffff",
padx=5,
pady=5
)
self.path_label.grid(row=0, column=1, sticky='ew', padx=(0, 10))
# 工作表行
tk.Label(self.main_frame, text="主表工作表:", bg='#f0f0f0').grid(row=1, column=0, sticky='w', padx=(0, 5), pady=(10, 0))
self.sheet_label = tk.Label(
self.main_frame,
text="未选择主表工作表",
anchor='w',
relief=tk.SUNKEN,
bg="#ffffff",
padx=5,
pady=5
)
self.sheet_label.grid(row=1, column=1, sticky='ew', padx=(0, 10), pady=(10, 0))
# 打开文件按钮
self.open_btn = tk.Button(
self.main_frame,
text="打开主表文件",
command=self.open_excel,
bg='#f0f0f0',
fg='black',
padx=15,
pady=8,
width=10
)
self.open_btn.grid(row=0, column=2, rowspan=2, sticky='ns', padx=(0, 5))
# 选择主表按钮
self.select_btn = tk.Button(
self.main_frame,
text="选择主表",
command=self.load_excel,
bg='#f0f0f0',
fg='black',
padx=15,
pady=8,
width=10
)
self.select_btn.grid(row=0, column=3, rowspan=2, sticky='ns', padx=(0, 5))
# === 副表控件 === (添加在下方)
tk.Label(self.main_frame, text="副表文件路径:", bg='#f0f0f0').grid(row=2, column=0, sticky='w', padx=(0, 5), pady=(20, 0))
self.aux_path_label = tk.Label(
self.main_frame,
text="未选择副表文件",
anchor='w',
relief=tk.SUNKEN,
bg="#ffffff",
padx=5,
pady=5
)
self.aux_path_label.grid(row=2, column=1, sticky='ew', padx=(0, 10), pady=(20, 0))
tk.Label(self.main_frame, text="副表工作表:", bg='#f0f0f0').grid(row=3, column=0, sticky='w', padx=(0, 5), pady=(10, 0))
self.aux_sheet_label = tk.Label(
self.main_frame,
text="未选择副表工作表",
anchor='w',
relief=tk.SUNKEN,
bg="#ffffff",
padx=5,
pady=5
)
self.aux_sheet_label.grid(row=3, column=1, sticky='ew', padx=(0, 10), pady=(10, 0))
# 打开副表文件按钮
self.aux_open_btn = tk.Button(
self.main_frame,
text="打开副表文件",
command=self.open_aux_excel,
bg='#f0f0f0',
fg='black',
padx=15,
pady=8,
width=10
)
self.aux_open_btn.grid(row=2, column=2, rowspan=2, sticky='ns', padx=(0, 5), pady=(20, 0))
# 选择副表按钮
self.aux_select_btn = tk.Button(
self.main_frame,
text="选择副表",
command=self.load_aux_excel,
bg='#f0f0f0',
fg='black',
padx=15,
pady=8,
width=10
)
self.aux_select_btn.grid(row=2, column=3, rowspan=2, sticky='ns', padx=(0, 5), pady=(20, 0))
# 使用数组存储数据
self.dataset = [] # 主表数据
self.aux_dataset = [] # 副表数据
# 窗口居中
self.center_window(self.root)
# 添加对比按钮
self.compare_btn = tk.Button(
self.main_frame,
text="对比数据",
command=self.compare_data,
bg='#4CAF50',
fg='white',
padx=15,
pady=8,
width=15
)
self.compare_btn.grid(row=4, column=1, columnspan=2, pady=20)
# 创建结果显示区域
self.result_frame = tk.Frame(root, bg='#f0f0f0')
self.result_frame.pack(fill=tk.BOTH, expand=True, padx=10, pady=10)
# 添加Treeview显示结果
self.tree = ttk.Treeview(self.result_frame)
self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
# 添加滚动条
scrollbar = ttk.Scrollbar(self.result_frame, orient="vertical", command=self.tree.yview)
scrollbar.pack(side=tk.RIGHT, fill=tk.Y)
self.tree.configure(yscrollcommand=scrollbar.set)
# 状态标签
self.status_label = tk.Label(root, text="就绪", bg='#f0f0f0', anchor='w')
self.status_label.pack(fill=tk.X, padx=10, pady=5)
def center_window(self, window):
window.update_idletasks()
width = window.winfo_width()
height = window.winfo_height()
x = (window.winfo_screenwidth() // 2) - (width // 2)
y = (window.winfo_screenheight() // 2) - (height // 2)
window.geometry(f"+{x}+{y}")
# ===== 主表功能 =====
def open_excel(self):
"""打开主表文件"""
file_path = self.path_label.cget("text")
if not file_path or file_path == "未选择主表文件":
messagebox.showwarning("警告", "请先选择主表Excel文件")
return
try:
if os.name == 'nt':
os.startfile(file_path)
elif os.name == 'posix':
subprocess.call(('open', file_path))
else:
subprocess.call(('xdg-open', file_path))
except Exception as e:
messagebox.showerror("错误", f"打开主表文件失败: {str(e)}")
def load_excel(self):
"""加载主表Excel"""
file_path = filedialog.askopenfilename(
title="选择主表Excel文件",
filetypes=[("Excel文件", "*.xlsx *.xls")]
)
if not file_path:
return
try:
self.path_label.config(text=file_path)
self.select_main_sheet(file_path)
except Exception as e:
messagebox.showerror("错误", f"读取主表文件失败: {str(e)}")
def select_main_sheet(self, file_path):
"""选择主表工作表"""
try:
xl = pd.ExcelFile(file_path)
sheet_names = xl.sheet_names
selector = tk.Toplevel(self.root)
selector.title("选择主表工作表")
selector.geometry("300x150")
tk.Label(selector, text="请选择主表工作表:").pack(pady=10)
sheet_var = tk.StringVar(selector)
combobox = ttk.Combobox(
selector,
textvariable=sheet_var,
values=sheet_names,
state="readonly",
width=40
)
combobox.pack(pady=10, padx=20, fill=tk.X)
combobox.current(0)
tk.Button(
selector,
text="确认选择",
command=lambda: self.process_main_sheet_selection(
file_path,
sheet_var.get(),
selector
),
bg='#f0f0f0',
fg='black',
padx=10,
pady=5
).pack(pady=15)
self.center_window(selector)
except Exception as e:
messagebox.showerror("错误", f"读取主表文件失败: {str(e)}")
def process_main_sheet_selection(self, file_path, sheet_name, selector):
"""处理主表工作表选择结果"""
try:
df = pd.read_excel(file_path, sheet_name=sheet_name, header=0, skiprows=list(range(0,9)))
self.dataset = df.to_dict(orient='records')
row_count = len(self.dataset)
col_count = len(df.columns) if row_count > 0 else 0
self.sheet_label.config(text=f"{sheet_name} ({row_count}行×{col_count}列)")
selector.destroy()
messagebox.showinfo("加载成功",
f"主表工作表 [{sheet_name}] 已载入\n"
f"数据维度: {row_count}行 × {col_count}列"
)
print(f"主表数据示例: {self.dataset[0] if self.dataset else '空'}")
except Exception as e:
messagebox.showerror("错误", f"加载主表数据失败: {str(e)}")
# ===== 副表功能 =====
def open_aux_excel(self):
"""打开副表文件"""
file_path = self.aux_path_label.cget("text")
if not file_path or file_path == "未选择副表文件":
messagebox.showwarning("警告", "请先选择副表Excel文件")
return
try:
if os.name == 'nt':
os.startfile(file_path)
elif os.name == 'posix':
subprocess.call(('open', file_path))
else:
subprocess.call(('xdg-open', file_path))
except Exception as e:
messagebox.showerror("错误", f"打开副表文件失败: {str(e)}")
def load_aux_excel(self):
"""加载副表Excel"""
file_path = filedialog.askopenfilename(
title="选择副表Excel文件",
filetypes=[("Excel文件", "*.xlsx *.xls")]
)
if not file_path:
return
try:
self.aux_path_label.config(text=file_path)
self.select_aux_sheet(file_path)
except Exception as e:
messagebox.showerror("错误", f"读取副表文件失败: {str(e)}")
def select_aux_sheet(self, file_path):
"""选择副表工作表"""
try:
xl = pd.ExcelFile(file_path)
sheet_names = xl.sheet_names
selector = tk.Toplevel(self.root)
selector.title("选择副表工作表")
selector.geometry("300x150")
tk.Label(selector, text="请选择副表工作表:").pack(pady=10)
sheet_var = tk.StringVar(selector)
combobox = ttk.Combobox(
selector,
textvariable=sheet_var,
values=sheet_names,
state="readonly",
width=40
)
combobox.pack(pady=10, padx=20, fill=tk.X)
combobox.current(0)
tk.Button(
selector,
text="确认选择",
command=lambda: self.process_aux_sheet_selection(
file_path,
sheet_var.get(),
selector
),
bg='#f0f0f0',
fg='black',
padx=10,
pady=5
).pack(pady=15)
self.center_window(selector)
except Exception as e:
messagebox.showerror("错误", f"读取副表文件失败: {str(e)}")
def process_aux_sheet_selection(self, file_path, sheet_name, selector):
"""处理副表工作表选择结果"""
try:
#df = pd.read_excel(file_path, sheet_name=sheet_name, header=0, skiprows=list(range(0,9)))
df = pd.read_excel(file_path, sheet_name=sheet_name, header=0)
self.aux_dataset = df.to_dict(orient='records')
row_count = len(self.aux_dataset)
col_count = len(df.columns) if row_count > 0 else 0
self.aux_sheet_label.config(text=f"{sheet_name} ({row_count}行×{col_count}列)")
selector.destroy()
messagebox.showinfo("加载成功",
f"副表工作表 [{sheet_name}] 已载入\n"
f"数据维度: {row_count}行 × {col_count}列"
)
print(f"副表数据示例: {self.aux_dataset[0] if self.aux_dataset else '空'}")
except Exception as e:
messagebox.showerror("错误", f"加载副表数据失败: {str(e)}")
def compare_data(self):
"""对比主表和副表数据并设置背景色，仅显示和对比共有列，并显示具体错误列"""
if not self.dataset or not self.aux_dataset:
messagebox.showwarning("警告", "请先加载主表和副表数据")
return
# 获取主表和副表的列名
main_columns = set(self.dataset[0].keys()) if self.dataset else set()
aux_columns = set(self.aux_dataset[0].keys()) if self.aux_dataset else set()
# 获取两个表共有的列（不包括Z2列，因为Z2用于匹配）
common_columns = sorted(main_columns & aux_columns - {'Z2'})
# 确保Z2列存在（用于匹配的关键列）
if 'Z2' not in main_columns or 'Z2' not in aux_columns:
messagebox.showerror("错误", "主表或副表缺少Z2列，无法进行对比")
return
# 配置Treeview列 - 只显示共有列
self.tree["columns"] = common_columns
self.tree["show"] = "headings"
for col in common_columns:
self.tree.heading(col, text=col)
self.tree.column(col, width=100, anchor=tk.CENTER)
# 添加Z2列作为第一列（用于显示匹配状态及详细错误）
self.tree["columns"] = ["Z2"] + common_columns
self.tree.heading("Z2", text="Z2(匹配状态)")
self.tree.column("Z2", width=200, anchor=tk.CENTER) # 增加宽度以显示更多错误信息
# 清空现有数据
for item in self.tree.get_children():
self.tree.delete(item)
# 创建用于设置颜色的tag
self.tree.tag_configure('match', background='#DFF0D8') # 绿色
self.tree.tag_configure('mismatch', background='#F8D7DA') # 红色
self.tree.tag_configure('not_found', background='#F8D7DA') # 红色
# 统计结果
match_count = 0
mismatch_count = 0
not_found_count = 0
# 创建从Z2值到副表行的映射（提高查找效率）
aux_map = {}
for aux_row in self.aux_dataset:
z2_value = aux_row.get('Z2', None)
if z2_value is not None:
aux_map.setdefault(z2_value, []).append(aux_row)
# 记录所有不匹配详情（Z2值→不匹配列列表）
mismatch_details = {}
# 遍历主表每一行
for main_row in self.dataset:
main_z2 = main_row.get('Z2', None)
found_in_aux = False
all_matched = True
mismatched_columns = [] # 存储当前行不匹配的列名
# 查找匹配的副表行
matching_aux_rows = aux_map.get(main_z2, []) if main_z2 is not None else []
if matching_aux_rows:
found_in_aux = True
# 检查所有匹配行中是否有完全匹配的
row_match = False
for aux_row in matching_aux_rows:
current_match = True
# 只比较共有列
for col in common_columns:
if col in main_row and col in aux_row:
if main_row[col] != aux_row[col]:
current_match = False
# 记录不匹配的列（避免重复）
if col not in mismatched_columns:
mismatched_columns.append(col)
if current_match:
row_match = True
mismatched_columns = [] # 完全匹配时清空不匹配列
break
all_matched = row_match
# 准备Treeview行数据
if not found_in_aux:
z2_display = f"{main_z2} ✗ (未找到)"
not_found_count += 1
elif all_matched:
z2_display = f"{main_z2} ✓"
match_count += 1
else:
# 显示不匹配的具体列（最多显示3个）
error_cols = ", ".join(mismatched_columns[:3])
if len(mismatched_columns) > 3:
error_cols += f" 等{len(mismatched_columns)}处"
z2_display = f"{main_z2} ✗ ({error_cols})"
mismatch_count += 1
mismatch_details[main_z2] = mismatched_columns
values = [z2_display] + [main_row.get(col, '') for col in common_columns]
item_id = self.tree.insert("", "end", values=values)
# 根据匹配情况设置背景色
if not found_in_aux:
self.tree.item(item_id, tags=('not_found',))
elif all_matched:
self.tree.item(item_id, tags=('match',))
else:
self.tree.item(item_id, tags=('mismatch',))
# 更新状态（添加错误详情）
detail_text = ""
if mismatch_details:
sample_errors = "\n".join([f"Z2={k}: {', '.join(v[:3])}{'...' if len(v)>3 else ''}"
for k,v in list(mismatch_details.items())[:3]])
if len(mismatch_details) > 3:
sample_errors += f"\n...等{len(mismatch_details)}行存在差异"
detail_text = f"\n错误详情示例:\n{sample_errors}"
self.status_label.config(
text=(f"对比完成 | 匹配: {match_count}行 | 不匹配: {mismatch_count}行 | 未找到: {not_found_count}行 | "
f"共有列: {', '.join(common_columns)}{detail_text}")
)
if __name__ == "__main__":
root = tk.Tk()
app = ExcelViewerApp(root)
root.mainloop()
最新发布
本文深入探讨了TreeView控件的交互实现，包括鼠标选择、键盘导航及代码控制等多种交互方式。重点介绍了如何处理复杂的鼠标点击事件，如Shift+Click区间选择等功能，并详细解释了键盘事件的处理逻辑。
<
script
language
="javascript"
>
</
script
>
input.onfocus =
function(){ FindParentElement(
this, 'TD').focus(); };
{
if ( innerCache.m_Selecteds.m_Count == 0 )
{
}
}
，在处理键盘事件中我们再来详细说它。那么我们在键盘上需要处理那些操作呢？看下面的代码，我们处理：Up、Down、+、-、Space和Esc这几个按键。
：
扫一扫
667
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
