Python项目源码69：Excel数据筛选器1.0（tkinter+sqlite3+pandas）

功能说明：以下是一个使用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 datetimeclass ExcelViewerApp:def __init__(self, root):self.root = rootself.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"] = columnsfor 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("输入错误", "请输入筛选条件")returntry: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("保存错误", "没有可保存的筛选结果")returntry:# 转换为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("保存失败", "该名称已存在，请使用其他名称")returntry:# 插入数据库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)breakdef 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("提示", "请先选择一个条件")returnold_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("输入错误", "名称和表达式不能为空")returntry:# 检查名称冲突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("提示", "请先选择一个条件")returncondition_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("输入错误", "当前没有可保存的条件")returnname = 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("错误", "该名称已存在")returntry: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)breakdef 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()

完毕！！感谢您的收看

----------★★跳转到历史博文集合★★----------

我的零基础Python教程，Python入门篇 进阶篇 视频教程 Py安装py项目 Python模块 Python爬虫 Json Xpath 正则表达式 Selenium Etree CssGui程序开发 Tkinter Pyqt5 列表元组字典数据可视化 matplotlib 词云图 Pyecharts 海龟画图 Pandas Bug处理 电脑小知识office自动化办公 编程工具 NumPy Pygame