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 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()

完毕！！感谢您的收看

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

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