功能说明：以下是一个使用Tkinter和Pandas实现的完整示例，支持Excel数据读取、双表格展示和高级条件筛选功能：
1.文件操作：点击"打开文件"按钮选择Excel文件（支持.xlsx和.xls格式），自动加载数据到左侧表格。输入Pandas兼容的查询条件，点击"执行筛选"查看结果，点击"清空条件"重置筛选结果。
2.数据展示：左侧表格显示原始数据，右侧表格显示筛选结果，自动适应列宽，支持垂直滚动。
3.高级筛选：数值比较：Age >= 25，字符串包含：Name.str.contains(“张”)，多条件组合：(Salary > 8000) & (Department == “销售部”)，日期筛选：Join_Date > “2023-01-01”，在条件输入框使用Pandas查询语法，例如：点击"执行筛选"按钮应用条件，点击"清空条件"按钮重置筛选。
4.错误处理：文件读取错误提示，条件语法错误提示，空条件警告。
5.数据库保存功能：原始数据（JSON格式），保存时间戳（精确到秒），新增"保存结果"按钮，自动创建SQLite数据库文件（data_records.db），存储结构包含：每次保存记录当前系统时间。
6.数据库查看功能：点击"查看历史"按钮弹出历史记录窗口，显示保存时间和记录数量，双击条目可查看详细数据，详细数据显示原始保存的表格格式。
7.条件收藏功能：新增收藏条件按钮，支持为当前条件命名保存，下拉选择框可快速调用历史条件，自动同步数据库中的收藏条件。
# -*- coding: utf-8 -*-
# @Author : 小红牛
# 微信公众号：WdPython
import tkinter as tk
from tkinter import ttk, filedialog, messagebox, simpledialog
import pandas as pd
import sqlite3
from datetime import datetime
class ExcelViewerApp:
def __init__(self, root):
self.root = root
self.root.title("Excel数据加载+分析1.0")
self.df = pd.DataFrame()
self.filtered_df = pd.DataFrame()
self.saved_conditions = []
# 初始化数据库
self.init_db()
self.load_saved_conditions()
# 创建界面组件
self.create_widgets()
self.setup_layout()
self.setup_style()
def init_db(self):
"""初始化数据库连接和表结构"""
self.conn = sqlite3.connect('data_records.db')
self.cursor = self.conn.cursor()
# 结果表
self.cursor.execute('''CREATE TABLE IF NOT EXISTS results
(id INTEGER PRIMARY KEY AUTOINCREMENT,
record_data TEXT,
save_time TIMESTAMP)''')
# 条件表（增加唯一约束）
self.cursor.execute('''CREATE TABLE IF NOT EXISTS saved_conditions
(id INTEGER PRIMARY KEY AUTOINCREMENT,
condition_name TEXT UNIQUE,
condition_expr TEXT,
save_time TIMESTAMP)''')
self.conn.commit()
def load_saved_conditions(self):
"""加载收藏条件"""
self.cursor.execute("SELECT condition_name, condition_expr FROM saved_conditions ORDER BY save_time DESC")
self.saved_conditions = self.cursor.fetchall()
def create_widgets(self):
"""创建所有界面组件"""
# 工具栏
self.toolbar = ttk.Frame(self.root)
self.open_btn = ttk.Button(self.toolbar, text="打开文件", command=self.open_file)
self.save_btn = ttk.Button(self.toolbar, text="保存数据", command=self.save_to_db)
self.history_btn = ttk.Button(self.toolbar, text="查看数据", command=self.show_history)
self.manage_btn = ttk.Button(self.toolbar, text="管理条件", command=self.manage_conditions)
self.clear_btn = ttk.Button(self.toolbar, text="清空条件", command=self.clear_condition)
# 条件输入区
self.condition_frame = ttk.LabelFrame(self.root, text="筛选条件")
self.condition_combo = ttk.Combobox(
self.condition_frame,
values=[c[0] for c in self.saved_conditions],
width=25,
state="readonly"
)
self.condition_combo.bind("<<ComboboxSelected>>", self.select_condition)
self.condition_entry = ttk.Entry(self.condition_frame, width=50)
self.save_condition_btn = ttk.Button(self.condition_frame, text="收藏条件", command=self.save_condition)
self.search_btn = ttk.Button(self.condition_frame, text="执行筛选", command=self.filter_data)
# 示例条件文本框
self.example_frame = ttk.LabelFrame(self.root, text="条件命令示例（cv可复制）")
self.example_text = tk.Text(
self.example_frame,
height=3,
width=60,
wrap=tk.WORD,
bg='#F7F7F7',
relief=tk.FLAT
)
self.example_text.insert(tk.END,
"1.数值比较：工资 >= 9000，工资 == 8000，工资.between(9000, 15000)\n"
"2.文本匹配：姓名.str.contains('张')，部门 == '市场部'\n"
"3.多条件：工资 >= 9000 & 入职日期 > '2025-01-18'\n"
)
self.example_text.configure(state=tk.DISABLED)
# 数据表格
self.tree_frame = ttk.Frame(self.root)
self.original_tree = ttk.Treeview(self.tree_frame, show="headings")
self.result_tree = ttk.Treeview(self.tree_frame, show="headings")
# 滚动条
self.original_scroll = ttk.Scrollbar(self.tree_frame, orient="vertical", command=self.original_tree.yview)
self.result_scroll = ttk.Scrollbar(self.tree_frame, orient="vertical", command=self.result_tree.yview)
def setup_layout(self):
"""布局组件"""
# 工具栏
self.toolbar.pack(fill=tk.X, padx=5, pady=5)
self.open_btn.pack(side=tk.LEFT, padx=2)
self.save_btn.pack(side=tk.LEFT, padx=2)
self.history_btn.pack(side=tk.LEFT, padx=2)
self.manage_btn.pack(side=tk.LEFT, padx=2)
self.clear_btn.pack(side=tk.LEFT, padx=2)
# 条件输入区
self.condition_frame.pack(fill=tk.X, padx=5, pady=5)
self.condition_combo.pack(side=tk.LEFT, padx=2)
self.condition_entry.pack(side=tk.LEFT, fill=tk.X, expand=True, padx=2, pady=2)
self.save_condition_btn.pack(side=tk.LEFT, padx=2)
self.search_btn.pack(side=tk.LEFT, padx=2)
# 示例文本框
self.example_frame.pack(fill=tk.X, padx=5, pady=5)
self.example_text.pack(padx=5, pady=5, fill=tk.BOTH, expand=True)
# 表格区
self.tree_frame.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
self.original_tree.grid(row=0, column=0, sticky="nsew")
self.original_scroll.grid(row=0, column=1, sticky="ns")
self.result_tree.grid(row=0, column=2, sticky="nsew")
self.result_scroll.grid(row=0, column=3, sticky="ns")
# 列权重
self.tree_frame.columnconfigure(0, weight=1)
self.tree_frame.columnconfigure(2, weight=1)
self.tree_frame.rowconfigure(0, weight=1)
def setup_style(self):
"""配置界面样式"""
style = ttk.Style()
style.configure("Treeview", rowheight=28, font=('微软雅黑', 10))
style.configure("Treeview.Heading", font=('微软雅黑', 10, 'bold'))
style.configure("TButton", padding=6, font=('微软雅黑', 9))
style.configure("TLabelFrame", font=('微软雅黑', 9, 'bold'))
style.configure("TEntry", font=('微软雅黑', 10))
def open_file(self):
"""打开Excel文件并加载数据"""
file_path = filedialog.askopenfilename(
filetypes=[("Excel文件", "*.xlsx *.xls"), ("所有文件", "*.*")]
)
if file_path:
try:
self.df = pd.read_excel(file_path)
self.update_treeview(self.original_tree, self.df)
messagebox.showinfo("成功", f"成功加载文件：{file_path}")
except Exception as e:
messagebox.showerror("错误", f"文件读取失败：{str(e)}")
def update_treeview(self, tree, dataframe):
"""更新Treeview组件显示数据"""
# 清空现有数据
tree.delete(*tree.get_children())
# 配置列
columns = list(dataframe.columns)
tree["columns"] = columns
for col in columns:
tree.heading(col, text=col)
tree.column(col, width=100, anchor="w", minwidth=50)
# 插入数据
for _, row in dataframe.iterrows():
values = [self.format_value(v) for v in row.values]
tree.insert("", "end", values=values)
def format_value(self, value):
"""格式化显示值"""
if pd.isna(value):
return ""
if isinstance(value, (float, int)):
return round(value, 4)
if isinstance(value, datetime):
return value.strftime("%Y-%m-%d")
return str(value)[:50] # 截断长字符串
def filter_data(self):
"""执行数据筛选"""
condition = self.condition_entry.get().strip()
if not condition:
messagebox.showwarning("输入错误", "请输入筛选条件")
return
try:
self.filtered_df = self.df.query(condition, engine='python')
self.update_treeview(self.result_tree, self.filtered_df)
except Exception as e:
messagebox.showerror("条件错误", f"无效的筛选条件：\n{str(e)}")
def save_to_db(self):
"""保存筛选结果到数据库"""
if self.filtered_df.empty:
messagebox.showwarning("保存错误", "没有可保存的筛选结果")
return
try:
# 转换为JSON格式
json_data = self.filtered_df.to_json(orient='records', force_ascii=False)
save_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
# 插入数据库
self.cursor.execute(
"INSERT INTO results (record_data, save_time) VALUES (?, ?)",
(json_data, save_time)
)
self.conn.commit()
messagebox.showinfo("保存成功",
f"成功保存 {len(self.filtered_df)} 条记录\n保存时间：{save_time}")
except Exception as e:
messagebox.showerror("保存失败", f"数据库操作失败：{str(e)}")
def show_history(self):
"""显示历史记录窗口"""
history_win = tk.Toplevel(self.root)
history_win.title("历史保存记录")
history_win.geometry("600x400")
# 创建表格
tree = ttk.Treeview(history_win, columns=("time", "count"), show="headings")
tree.heading("time", text="保存时间")
tree.heading("count", text="记录数")
tree.column("time", width=200)
tree.column("count", width=100, anchor="center")
# 滚动条
scroll = ttk.Scrollbar(history_win, orient="vertical", command=tree.yview)
tree.configure(yscrollcommand=scroll.set)
# 布局
tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
scroll.pack(side=tk.RIGHT, fill=tk.Y)
# 加载数据
self.cursor.execute("SELECT save_time, record_data FROM results ORDER BY save_time DESC")
for save_time, data in self.cursor.fetchall():
count = len(pd.read_json(data))
display_time = datetime.strptime(save_time, "%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d %H:%M")
tree.insert("", "end", values=(display_time, count))
# 双击查看详情
def on_double_click(event):
selected = tree.selection()
if selected:
item = tree.item(selected[0])
time_str = item["values"][0]
self.show_history_detail(time_str)
tree.bind("<Double-1>", on_double_click)
def show_history_detail(self, time_str):
"""显示历史记录详情"""
detail_win = tk.Toplevel(self.root)
detail_win.title(f"记录详情 - {time_str}")
detail_win.geometry("800x600")
# 查询数据库
self.cursor.execute(
"SELECT record_data FROM results WHERE save_time LIKE ?",
(f"{time_str}%",)
)
result = self.cursor.fetchone()
if not result:
messagebox.showerror("错误", "找不到对应的记录数据")
return
# 创建表格
df = pd.read_json(result[0])
tree = ttk.Treeview(detail_win, show="headings")
scroll_x = ttk.Scrollbar(detail_win, orient="horizontal", command=tree.xview)
scroll_y = ttk.Scrollbar(detail_win, orient="vertical", command=tree.yview)
tree.configure(xscrollcommand=scroll_x.set, yscrollcommand=scroll_y.set)
# 配置列
tree["columns"] = list(df.columns)
for col in df.columns:
tree.heading(col, text=col)
tree.column(col, width=120, minwidth=80, anchor="w")
# 插入数据
for _, row in df.iterrows():
values = [self.format_value(v) for v in row.values]
tree.insert("", "end", values=values)
# 布局
tree.pack(side=tk.TOP, fill=tk.BOTH, expand=True)
scroll_y.pack(side=tk.RIGHT, fill=tk.Y)
scroll_x.pack(side=tk.BOTTOM, fill=tk.X)
def save_condition(self):
"""保存当前筛选条件"""
condition = self.condition_entry.get().strip()
if not condition:
messagebox.showwarning("输入错误", "当前没有可保存的条件")
return
# 获取条件名称
name = simpledialog.askstring("保存条件", "请输入条件名称：", parent=self.root)
if not name:
return
# 检查重名
self.cursor.execute("SELECT 1 FROM saved_conditions WHERE condition_name=?", (name,))
if self.cursor.fetchone():
messagebox.showerror("保存失败", "该名称已存在，请使用其他名称")
return
try:
# 插入数据库
save_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
self.cursor.execute(
"INSERT INTO saved_conditions (condition_name, condition_expr, save_time) VALUES (?, ?, ?)",
(name, condition, save_time)
)
self.conn.commit()
# 更新界面
self.load_saved_conditions()
self.condition_combo["values"] = [c[0] for c in self.saved_conditions]
messagebox.showinfo("保存成功", "条件已成功收藏！")
except Exception as e:
messagebox.showerror("保存失败", f"数据库错误：{str(e)}")
def select_condition(self, event):
"""选择已保存的条件"""
selected_name = self.condition_combo.get()
for name, expr in self.saved_conditions:
if name == selected_name:
self.condition_entry.delete(0, tk.END)
self.condition_entry.insert(0, expr)
break
def manage_conditions(self):
"""打开条件管理窗口"""
manage_win = tk.Toplevel(self.root)
manage_win.title("管理收藏条件")
manage_win.geometry("600x400")
# 条件列表
tree = ttk.Treeview(manage_win, columns=("name", "expr"), show="headings", selectmode="browse")
tree.heading("name", text="条件名称")
tree.heading("expr", text="条件表达式")
tree.column("name", width=150)
tree.column("expr", width=400)
# 操作按钮
btn_frame = ttk.Frame(manage_win)
edit_btn = ttk.Button(btn_frame, text="编辑", command=lambda: self.edit_condition(tree, manage_win))
delete_btn = ttk.Button(btn_frame, text="删除", command=lambda: self.delete_condition(tree))
# 布局
tree.pack(fill=tk.BOTH, expand=True, padx=5, pady=5)
btn_frame.pack(pady=5)
edit_btn.pack(side=tk.LEFT, padx=5)
delete_btn.pack(side=tk.LEFT, padx=5)
# 加载数据
for name, expr in self.saved_conditions:
tree.insert("", "end", values=(name, expr))
def edit_condition(self, tree, parent_win):
"""编辑选中条件"""
selected = tree.selection()
if not selected:
messagebox.showwarning("提示", "请先选择一个条件")
return
old_name, old_expr = tree.item(selected[0], "values")
# 创建编辑对话框
edit_win = tk.Toplevel(parent_win)
edit_win.title("编辑条件")
# 输入组件
ttk.Label(edit_win, text="名称：").grid(row=0, column=0, padx=5, pady=5, sticky="e")
name_entry = ttk.Entry(edit_win, width=30)
name_entry.insert(0, old_name)
name_entry.grid(row=0, column=1, padx=5, pady=5)
ttk.Label(edit_win, text="表达式：").grid(row=1, column=0, padx=5, pady=5, sticky="e")
expr_entry = ttk.Entry(edit_win, width=50)
expr_entry.insert(0, old_expr)
expr_entry.grid(row=1, column=1, padx=5, pady=5)
def save_changes():
new_name = name_entry.get().strip()
new_expr = expr_entry.get().strip()
if not new_name or not new_expr:
messagebox.showwarning("输入错误", "名称和表达式不能为空")
return
try:
# 检查名称冲突
if new_name != old_name:
self.cursor.execute("SELECT 1 FROM saved_conditions WHERE condition_name=?", (new_name,))
if self.cursor.fetchone():
messagebox.showerror("错误", "名称已存在")
return
# 更新数据库
self.cursor.execute(
"UPDATE saved_conditions SET condition_name=?, condition_expr=? WHERE condition_name=?",
(new_name, new_expr, old_name)
)
self.conn.commit()
# 更新界面
self.load_saved_conditions()
self.condition_combo["values"] = [c[0] for c in self.saved_conditions]
tree.item(selected[0], values=(new_name, new_expr))
edit_win.destroy()
messagebox.showinfo("成功", "条件已更新")
except Exception as e:
messagebox.showerror("错误", f"更新失败：{str(e)}")
ttk.Button(edit_win, text="保存", command=save_changes).grid(row=2, column=1, pady=10)
def delete_condition(self, tree):
"""删除选中条件"""
selected = tree.selection()
if not selected:
messagebox.showwarning("提示", "请先选择一个条件")
return
condition_name = tree.item(selected[0], "values")[0]
if messagebox.askyesno("确认删除", f"确定要删除条件 '{condition_name}' 吗？"):
try:
self.cursor.execute("DELETE FROM saved_conditions WHERE condition_name=?", (condition_name,))
self.conn.commit()
tree.delete(selected[0])
self.load_saved_conditions()
self.condition_combo["values"] = [c[0] for c in self.saved_conditions]
messagebox.showinfo("成功", "条件已删除")
except Exception as e:
messagebox.showerror("错误", f"删除失败：{str(e)}")
def save_condition(self):
"""保存当前条件"""
condition = self.condition_entry.get().strip()
if not condition:
messagebox.showwarning("输入错误", "当前没有可保存的条件")
return
name = simpledialog.askstring("保存条件", "请输入条件名称：", parent=self.root)
if not name:
return
# 检查重复
self.cursor.execute("SELECT 1 FROM saved_conditions WHERE condition_name=?", (name,))
if self.cursor.fetchone():
messagebox.showerror("错误", "该名称已存在")
return
try:
save_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
self.cursor.execute(
"INSERT INTO saved_conditions VALUES (NULL, ?, ?, ?)",
(name, condition, save_time)
)
self.conn.commit()
# 更新界面
self.load_saved_conditions()
self.condition_combo["values"] = [c[0] for c in self.saved_conditions]
messagebox.showinfo("成功", "条件已保存")
except Exception as e:
messagebox.showerror("错误", f"保存失败：{str(e)}")
def select_condition(self, event):
"""选择已保存的条件"""
selected_name = self.condition_combo.get()
for name, expr in self.saved_conditions:
if name == selected_name:
self.condition_entry.delete(0, tk.END)
self.condition_entry.insert(0, expr)
break
def clear_condition(self):
"""清空筛选条件和结果"""
self.condition_entry.delete(0, tk.END)
self.result_tree.delete(*self.result_tree.get_children())
self.filtered_df = pd.DataFrame()
messagebox.showinfo("已清空", "筛选条件和结果已重置")
def __del__(self):
"""关闭数据库连接"""
if hasattr(self, 'conn'):
self.conn.close()
if __name__ == "__main__":
root = tk.Tk()
app = ExcelViewerApp(root)
root.geometry("1366x768")
root.mainloop()
完毕！！感谢您的收看
----------★★跳转到历史博文集合★★----------
扫一扫
1435
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
