class ExcelProcessor:
    def __init__(self, root):
        # 初始化GUI窗口
        self.setup_ui()  # 界面布局
        self.df = None    # Pandas DataFrame存儲(chǔ)數(shù)據(jù)
        self.file_path = None  # 文件路徑

# 多條件搜索實(shí)現(xiàn)
keywords = [kw.strip() for kw in entry.get().replace('，', ',').split(',')]
pattern = '|'.join(keywords)
filtered_df = filtered_df[filtered_df[column].astype(str).str.contains(
    pattern, case=False, na=False, regex=True)]

# 動(dòng)態(tài)計(jì)算列寬
max_widths = {}
for column in self.df.columns:
    header_width = len(str(column)) * 10
    max_data_width = self.df[column].astype(str).str.len().max() * 10
    max_widths[column] = min(max(header_width, max_data_width, 100), 300)

def edit_row(self):
    # 創(chuàng)建獨(dú)立編輯窗口
    edit_window = tk.Toplevel(self.root)
    # 顯示所有字段可編輯
    for i, (column, value) in enumerate(zip(self.df.columns, values)):
        entry = ttk.Entry(frame)
        entry.insert(0, str(value))

# 刪除空值的快捷操作
def search_data(self):
    # 在搜索框輸入特定標(biāo)記如"$NULL$"
    if entry.get() == "$NULL$":
        filtered_df = filtered_df[filtered_df[column].isna()]

# 分塊讀取優(yōu)化
chunk_size = 100000
chunks = pd.read_excel(file_path, chunksize=chunk_size)
self.df = pd.concat(chunks)

from threading import Thread

def open_file_async(self):
    Thread(target=self._open_file_task, daemon=True).start()

def _open_file_task(self):
    # 實(shí)際文件操作代碼
    self.root.after(0, self.update_ui_after_loading)

import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import pandas as pd
import openpyxl
 
class ExcelProcessor:
    def __init__(self, root):
        self.root = root
        self.root.title('Excel表內(nèi)關(guān)鍵字查修改刪')
        self.df = None
        self.file_path = None
        # 設(shè)置默認(rèn)窗口大小
        window_width = 1200
        window_height = 900
        screen_width = root.winfo_screenwidth()
        screen_height = root.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        root.geometry(f"{window_width}x{window_height}+{x}+{y}")
        # 設(shè)置窗口最小尺寸
        root.minsize(1000, 800)
        self.setup_ui()
 
    def setup_ui(self):
        # 設(shè)置主窗口樣式
        style = ttk.Style()
        style.configure('TLabelframe', padding=8)
        style.configure('TButton', padding=5)
        style.configure('TFrame', padding=3)
        style.configure('TLabel', padding=2)
        style.configure('TEntry', padding=2)
         
        # 設(shè)置窗口圖標(biāo)
        try:
            # 獲取圖標(biāo)文件路徑
            import sys
            if getattr(sys, 'frozen', False):
                # 打包后的路徑
                base_path = sys._MEIPASS
            else:
                # 開發(fā)環(huán)境路徑
                base_path = os.path.dirname(os.path.abspath(__file__))
             
            icon_path = os.path.join(base_path, 'logo.ico')
            self.root.iconbitmap(icon_path)
        except:
            pass
 
        # 文件選擇區(qū)域
        file_frame = ttk.LabelFrame(self.root, text='文件操作')
        file_frame.pack(fill='x', padx=8, pady=(8,4))
 
        btn_container = ttk.Frame(file_frame)
        btn_container.pack(pady=8)
 
        self.file_btn = ttk.Button(btn_container, text='選擇Excel文件', command=self.open_file, width=15)
        self.file_btn.pack(side='left', padx=8)
 
        self.save_btn = ttk.Button(btn_container, text='保存更改', command=self.save_file, state='disabled', width=15)
        self.save_btn.pack(side='left', padx=8)
 
        # 搜索條件區(qū)域
        search_frame = ttk.LabelFrame(self.root, text='搜索條件')
        search_frame.pack(fill='x', padx=8, pady=6)
 
        self.search_entries = []
        self.search_combos = []
        self.search_frames = []
 
        search_controls = ttk.Frame(search_frame)
        search_controls.pack(fill='x', pady=8)
 
        # 默認(rèn)添加兩個(gè)搜索條件
        for _ in range(2):
            self.add_search_condition()
 
        control_frame = ttk.Frame(search_frame)
        control_frame.pack(fill='x', pady=8)
 
        self.add_condition_btn = ttk.Button(control_frame, text='添加條件', command=self.add_search_condition, width=12)
        self.add_condition_btn.pack(side='left', padx=8)
 
        self.search_btn = ttk.Button(control_frame, text='搜索', command=self.search_data, state='disabled', width=12)
        self.search_btn.pack(side='left', padx=8)
 
        # 數(shù)據(jù)顯示區(qū)域
        data_frame = ttk.LabelFrame(self.root, text='數(shù)據(jù)顯示')
        data_frame.pack(fill='both', expand=True, padx=8, pady=6)
 
        # 創(chuàng)建表格容器
        table_container = ttk.Frame(data_frame)
        table_container.pack(fill='both', expand=True, padx=8, pady=8)
 
        # 創(chuàng)建表格
        self.tree = ttk.Treeview(table_container)
        self.tree.pack(fill='both', expand=True, side='left')
 
        # 添加垂直滾動(dòng)條
        y_scrollbar = ttk.Scrollbar(table_container, orient='vertical', command=self.tree.yview)
        y_scrollbar.pack(fill='y', side='right')
 
        # 添加水平滾動(dòng)條
        x_scrollbar = ttk.Scrollbar(table_container, orient='horizontal', command=self.tree.xview)
        x_scrollbar.pack(fill='x', side='bottom')
 
        self.tree.configure(yscrollcommand=y_scrollbar.set, xscrollcommand=x_scrollbar.set)
 
        # 操作按鈕區(qū)域
        # 操作按鈕區(qū)域
        btn_frame = ttk.LabelFrame(self.root, text='操作')
        btn_frame.pack(fill='x', padx=8, pady=(6,8))
         
        btn_container = ttk.Frame(btn_frame)
        btn_container.pack(fill='x', pady=8)
         
        self.edit_btn = ttk.Button(btn_container, text='編輯選中行', command=self.edit_row, state='disabled', width=12)
        self.edit_btn.pack(side='left', padx=8)
         
        self.delete_btn = ttk.Button(btn_container, text='刪除選中行', command=self.delete_row, state='disabled', width=12)
        self.delete_btn.pack(side='left', padx=8)
         
        
 
    def open_file(self):
        file_path = filedialog.askopenfilename(filetypes=[
            ('Excel文件', '*.xlsx;*.xls')
        ])
        if file_path:
            try:
                # 禁用按鈕，顯示加載提示
                self.file_btn['state'] = 'disabled'
                self.root.config(cursor='wait')
                self.root.update()
 
                self.file_path = file_path
                self.df = pd.read_excel(file_path)
                self.update_table()
                self.update_search_fields()
                self.enable_buttons()
 
                # 恢復(fù)按鈕狀態(tài)和光標(biāo)
                self.file_btn['state'] = 'normal'
                self.root.config(cursor='')
                messagebox.showinfo('成功', '文件加載成功！')
            except Exception as e:
                self.file_btn['state'] = 'normal'
                self.root.config(cursor='')
                messagebox.showerror('錯(cuò)誤', f'文件加載失?。簕str(e)}')
 
    def update_table(self):
        # 清空現(xiàn)有數(shù)據(jù)
        for item in self.tree.get_children():
            self.tree.delete(item)
 
        # 設(shè)置列
        self.tree['columns'] = list(self.df.columns)
        self.tree['show'] = 'headings'
 
        # 計(jì)算每列的最大寬度
        max_widths = {}
        for column in self.df.columns:
            # 計(jì)算列標(biāo)題的寬度
            header_width = len(str(column)) * 10
            # 計(jì)算數(shù)據(jù)的最大寬度
            max_data_width = self.df[column].astype(str).str.len().max() * 10
            max_widths[column] = min(max(header_width, max_data_width, 100), 300)
 
        for column in self.df.columns:
            self.tree.heading(column, text=column,
                command=lambda col=column: self.sort_treeview(col))
            self.tree.column(column, width=max_widths[column], minwidth=50)
 
        # 添加數(shù)據(jù)
        for idx, row in self.df.iterrows():
            self.tree.insert('', 'end', values=list(row))
 
    def sort_treeview(self, col):
        """對(duì)表格按指定列排序"""
        if not hasattr(self, 'sort_reverse'):
            self.sort_reverse = {}
        self.sort_reverse[col] = not self.sort_reverse.get(col, False)
 
        # 獲取所有數(shù)據(jù)
        data = [(self.tree.set(child, col), child) for child in self.tree.get_children('')]
         
        # 排序
        data.sort(reverse=self.sort_reverse[col])
         
        # 重新插入數(shù)據(jù)
        for idx, item in enumerate(data):
            self.tree.move(item[1], '', idx)
 
    def update_search_fields(self):
        columns = list(self.df.columns)
        for combo in self.search_combos:
            combo['values'] = columns
            if columns:
                combo.set(columns[0])
 
    def enable_buttons(self):
        self.save_btn['state'] = 'normal'
        self.search_btn['state'] = 'normal'
        self.edit_btn['state'] = 'normal'
        self.delete_btn['state'] = 'normal'
 
    def search_data(self):
        if self.df is None:
            return
 
        try:
            # 禁用搜索按鈕并顯示等待光標(biāo)
            self.search_btn['state'] = 'disabled'
            self.root.config(cursor='wait')
            self.root.update()
 
            filtered_df = self.df.copy()
            for combo, entry in zip(self.search_combos, self.search_entries):
                if combo.get() and entry.get():
                    column = combo.get()
                    # 將輸入值按全角和半角逗號(hào)分割成多個(gè)關(guān)鍵字
                    keywords = [kw.strip() for kw in entry.get().replace('，', ',').split(',')]
                    # 使用正則表達(dá)式構(gòu)建'或'的檢索條件
                    pattern = '|'.join(keywords)
                    filtered_df = filtered_df[filtered_df[column].astype(str).str.contains(pattern, case=False, na=False, regex=True)]
 
            # 更新顯示
            self.update_table_with_df(filtered_df)
 
            if len(filtered_df) == 0:
                messagebox.showinfo('提示', '未找到匹配的數(shù)據(jù)')
        except Exception as e:
            messagebox.showerror('錯(cuò)誤', f'搜索失?。簕str(e)}')
        finally:
            # 恢復(fù)搜索按鈕狀態(tài)和光標(biāo)
            self.search_btn['state'] = 'normal'
            self.root.config(cursor='')
            self.root.update()
 
    def update_table_with_df(self, df):
        for item in self.tree.get_children():
            self.tree.delete(item)
        for idx, row in df.iterrows():
            self.tree.insert('', 'end', values=list(row))
 
    def edit_row(self):
        selected_items = self.tree.selection()
        if not selected_items:
            messagebox.showwarning('警告', '請(qǐng)先選擇要編輯的行！')
            return
 
        # 獲取選中行的數(shù)據(jù)
        item = selected_items[0]
        values = self.tree.item(item)['values']
         
        # 創(chuàng)建編輯窗口
        edit_window = tk.Toplevel(self.root)
        edit_window.title('編輯數(shù)據(jù)')
        edit_window.transient(self.root)  # 設(shè)置為主窗口的子窗口
        edit_window.grab_set()  # 模態(tài)窗口
         
        # 創(chuàng)建滾動(dòng)區(qū)域
        canvas = tk.Canvas(edit_window)
        scrollbar = ttk.Scrollbar(edit_window, orient='vertical', command=canvas.yview)
        scrollable_frame = ttk.Frame(canvas)
 
        scrollable_frame.bind(
            '<Configure>',
            lambda e: canvas.configure(scrollregion=canvas.bbox('all'))
        )
 
        canvas.create_window((0, 0), window=scrollable_frame, anchor='nw')
        canvas.configure(yscrollcommand=scrollbar.set)
         
        entries = []
        for i, (column, value) in enumerate(zip(self.df.columns, values)):
            frame = ttk.Frame(scrollable_frame)
            frame.pack(fill='x', padx=10, pady=3)
            ttk.Label(frame, text=f'{column}:', width=15).pack(side='left')
            entry = ttk.Entry(frame)
            entry.insert(0, str(value))
            entry.pack(side='left', fill='x', expand=True, padx=5)
            entries.append(entry)
 
        # 按鈕區(qū)域
        btn_frame = ttk.Frame(edit_window)
        btn_frame.pack(fill='x', padx=10, pady=10)
 
        def save_changes():
            try:
                edit_window.config(cursor='wait')
                edit_window.update()
                 
                new_values = [entry.get() for entry in entries]
                # 更新DataFrame
                idx = self.tree.index(item)
                for col, value in zip(self.df.columns, new_values):
                    self.df.iloc[idx, self.df.columns.get_loc(col)] = value
                # 更新樹形視圖
                self.tree.item(item, values=new_values)
                edit_window.destroy()
                messagebox.showinfo('成功', '數(shù)據(jù)更新成功！')
            except Exception as e:
                edit_window.config(cursor='')
                messagebox.showerror('錯(cuò)誤', f'保存失?。簕str(e)}')
 
        def cancel_edit():
            edit_window.destroy()
 
        ttk.Button(btn_frame, text='保存', command=save_changes).pack(side='left', padx=5)
        ttk.Button(btn_frame, text='取消', command=cancel_edit).pack(side='left')
 
        # 設(shè)置滾動(dòng)區(qū)域布局
        canvas.pack(side='left', fill='both', expand=True, padx=5, pady=5)
        scrollbar.pack(side='right', fill='y')
 
        # 設(shè)置窗口大小和位置
        edit_window.update()
        window_width = min(edit_window.winfo_reqwidth() + 30, 800)
        window_height = min(edit_window.winfo_reqheight() + 30, 600)
        screen_width = edit_window.winfo_screenwidth()
        screen_height = edit_window.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        edit_window.geometry(f'{window_width}x{window_height}+{x}+{y}')
 
    def delete_row(self):
        selected_items = self.tree.selection()
        if not selected_items:
            messagebox.showwarning('警告', '請(qǐng)先選擇要?jiǎng)h除的行！')
            return
 
        if messagebox.askyesno('確認(rèn)', '確定要?jiǎng)h除選中的行嗎？'):
            for item in selected_items:
                idx = self.tree.index(item)
                self.df.drop(self.df.index[idx], inplace=True)
                self.tree.delete(item)
            self.df.reset_index(drop=True, inplace=True)
            messagebox.showinfo('成功', '數(shù)據(jù)刪除成功！')
 
    def save_file(self):
        if self.file_path and self.df is not None:
            try:
                self.df.to_excel(self.file_path, index=False)
                messagebox.showinfo('成功', '文件保存成功！')
            except Exception as e:
                messagebox.showerror('錯(cuò)誤', f'文件保存失?。簕str(e)}')
 
    def add_search_condition(self):
        frame = ttk.Frame(self.root.children['!labelframe2'].children['!frame'])
        frame.pack(fill='x', pady=3)
         
        condition_num = len(self.search_frames) + 1
        ttk.Label(frame, text=f'條件{condition_num}:', width=8).pack(side='left')
         
        combo = ttk.Combobox(frame, state='readonly', width=20)
        combo.pack(side='left', padx=(0,5))
         
        entry = ttk.Entry(frame)
        entry.pack(side='left', padx=5, fill='x', expand=True)
        # 添加提示標(biāo)簽
        ttk.Label(frame, text='(可用逗號(hào)分隔多個(gè)關(guān)鍵字)', foreground='gray').pack(side='left', padx=5)
         
        # 添加刪除按鈕
        def remove_condition():
            frame.destroy()
            self.search_frames.remove(frame)
            self.search_combos.remove(combo)
            self.search_entries.remove(entry)
            # 更新剩余條件的編號(hào)
            for i, f in enumerate(self.search_frames, 1):
                f.children['!label']['text'] = f'條件{i}:'
         
        if len(self.search_frames) > 1:  # 只有當(dāng)有多個(gè)條件時(shí)才顯示刪除按鈕
            delete_btn = ttk.Button(frame, text='×', width=3, command=remove_condition)
            delete_btn.pack(side='left', padx=(5,0))
         
        self.search_frames.append(frame)
        self.search_combos.append(combo)
        self.search_entries.append(entry)
         
        if self.df is not None:
            combo['values'] = list(self.df.columns)
            if combo['values']:
                combo.set(combo['values'][0])
 
    def show_help(self):
        help_window = tk.Toplevel(self.root)
        help_window.title('使用說明')
        help_window.transient(self.root)
        help_window.grab_set()
 
        # 創(chuàng)建滾動(dòng)區(qū)域
        canvas = tk.Canvas(help_window)
        scrollbar = ttk.Scrollbar(help_window, orient='vertical', command=canvas.yview)
        scrollable_frame = ttk.Frame(canvas)
 
        scrollable_frame.bind(
            '<Configure>',
            lambda e: canvas.configure(scrollregion=canvas.bbox('all'))
        )
 
        canvas.create_window((0, 0), window=scrollable_frame, anchor='nw')
        canvas.configure(yscrollcommand=scrollbar.set)
 
        # 讀取README.md文件內(nèi)容
        try:
            # 獲取資源文件路徑
            import sys
            if getattr(sys, 'frozen', False):
                # 打包后的路徑
                base_path = sys._MEIPASS
            else:
                # 開發(fā)環(huán)境路徑
                base_path = os.path.dirname(os.path.abspath(__file__))
             
            readme_path = os.path.join(base_path, 'README.md')
            with open(readme_path, 'r', encoding='utf-8') as f:
                help_text = f.read()
            # 僅顯示使用說明部分
            start = help_text.find('## 使用說明')
            if start != -1:
                help_text = help_text[start:]
        except Exception as e:
            help_text = f'無法加載幫助文檔：{str(e)}'
 
        # 顯示幫助文本
        text_widget = tk.Text(scrollable_frame, wrap='word', width=60, height=20)
        text_widget.insert('1.0', help_text)
        text_widget.configure(state='disabled')
        text_widget.pack(padx=10, pady=10)
 
        # 設(shè)置滾動(dòng)區(qū)域布局
        canvas.pack(side='left', fill='both', expand=True)
        scrollbar.pack(side='right', fill='y')
 
        # 設(shè)置窗口大小和位置
        help_window.update()
        window_width = min(help_window.winfo_reqwidth() + 30, 800)
        window_height = min(help_window.winfo_reqheight() + 30, 600)
        screen_width = help_window.winfo_screenwidth()
        screen_height = help_window.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        help_window.geometry(f'{window_width}x{window_height}+{x}+{y}')
 
    def show_reward(self):
        reward_window = tk.Toplevel(self.root)
        reward_window.title('賞贊支持')
        reward_window.transient(self.root)
        reward_window.grab_set()
 
        # 創(chuàng)建容器框架
        container = ttk.Frame(reward_window)
        container.pack(padx=20, pady=20)
 
        # 顯示感謝文字
        thank_label = ttk.Label(container, text='您的慷慨是對(duì)作者最大的支持', font=('微軟雅黑', 12))
        thank_label.pack(pady=(0, 10))
 
        # 顯示賞贊二維碼
        try:
            # 檢查PIL模塊是否可用
            try:
                from PIL import Image, ImageTk
            except ImportError:
                raise ImportError('缺少必要的圖片處理模塊。請(qǐng)運(yùn)行 pip install Pillow 安裝所需模塊。')
 
            import os
            # 獲取資源文件路徑
            import sys
            if getattr(sys, 'frozen', False):
                # 打包后的路徑
                base_path = sys._MEIPASS
            else:
                # 開發(fā)環(huán)境路徑
                base_path = os.path.dirname(os.path.abspath(__file__))
             
            image_path = os.path.join(base_path, 'reward.jpg')
             
            # 添加加載提示
            loading_label = ttk.Label(container, text='正在加載賞贊二維碼...')
            loading_label.pack(pady=5)
            reward_window.update()
             
            if not os.path.exists(image_path):
                loading_label.destroy()
                raise FileNotFoundError('賞贊二維碼圖片文件(reward.jpg)不存在，請(qǐng)確保該文件在程序目錄中。')
             
            try:
                # 加載并處理圖片
                image = Image.open(image_path)
                # 調(diào)整圖片大小，保持縱橫比
                width, height = image.size
                max_size = 300
                ratio = min(max_size/width, max_size/height)
                new_size = (int(width*ratio), int(height*ratio))
                image = image.resize(new_size, Image.Resampling.LANCZOS)
                 
                # 創(chuàng)建圖片標(biāo)簽并顯示
                photo = ImageTk.PhotoImage(image)
                image_label = ttk.Label(container, image=photo)
                image_label.image = photo  # 保持引用
                 
                # 移除加載提示并顯示圖片
                loading_label.destroy()
                image_label.pack(pady=10)
                 
                # 添加成功提示
                ttk.Label(container, text='賞贊二維碼加載成功', foreground='green').pack(pady=5)
 
                # 設(shè)置固定窗口大小
                reward_window.update()
                window_width = max(400, image_label.winfo_reqwidth() + 40)
                window_height = image_label.winfo_reqheight() + thank_label.winfo_reqheight() + 80
                reward_window.geometry(f'{window_width}x{window_height}')
            except Exception as img_error:
                loading_label.destroy()
                error_msg = f'圖片加載失?。簕str(img_error)}'
                ttk.Label(container, text=error_msg, foreground='red', wraplength=300).pack(pady=10)
                window_width = 400
                window_height = 200
                reward_window.geometry(f'{window_width}x{window_height}')
        except ImportError as e:
            error_msg = str(e)
            error_label = ttk.Label(container, text=error_msg, foreground='red', wraplength=300)
            error_label.pack(pady=10)
            window_width = 400
            window_height = 200
            reward_window.geometry(f'{window_width}x{window_height}')
        except Exception as e:
            error_msg = f'無法加載賞贊二維碼：{str(e)}'
            error_label = ttk.Label(container, text=error_msg, foreground='red', wraplength=300)
            error_label.pack(pady=10)
            window_width = 400
            window_height = 200
            reward_window.geometry(f'{window_width}x{window_height}')
 
        # 設(shè)置窗口位置
        screen_width = reward_window.winfo_screenwidth()
        screen_height = reward_window.winfo_screenheight()
        x = (screen_width - window_width) // 2
        y = (screen_height - window_height) // 2
        reward_window.geometry(f'+{x}+{y}')
 
def main():
    root = tk.Tk()
    app = ExcelProcessor(root)
    root.geometry('800x600')
    root.mainloop()
 
if __name__ == '__main__':
    main()