import os
import openpyxl
from tkinter import *
from tkinter import filedialog

# 定義UI界面
class App:
    def __init__(self, master):
        self.master = master
        master.title('Excel內(nèi)容查找器')
        master.geometry('350x600')

        # 創(chuàng)建文件夾選擇按鈕
        self.folder_button = Button(master, text='選擇文件夾', command=self.select_folder)
        self.folder_button.pack()

        # 創(chuàng)建查找內(nèi)容輸入框和按鈕
        self.search_label = Label(master, text='請輸入要查找的內(nèi)容：')
        self.search_label.pack()

        # 創(chuàng)建20個文本框，用于輸入需要查找的內(nèi)容
        self.search_entries = []
        for i in range(20):
            search_entry = Entry(master)
            search_entry.pack()
            self.search_entries.append(search_entry)

        # 創(chuàng)建導出路徑輸入框和按鈕
        self.export_label = Label(master, text='請選擇導出路徑：')
        self.export_label.pack()
        self.export_entry = Entry(master)
        self.export_entry.pack()
        self.export_button = Button(master, text='選擇路徑', command=self.select_export_path)
        self.export_button.pack()

        # 創(chuàng)建開始查找按鈕
        self.search_button = Button(master, text='開始查找', command=self.search)
        self.search_button.pack()

        # 創(chuàng)建狀態(tài)欄
        self.status_label = Label(master, text='')
        self.status_label.pack()

    # 選擇文件夾
    def select_folder(self):
        self.folder_path = filedialog.askdirectory()
        self.status_label.config(text=f'已選擇文件夾：{self.folder_path}')

    # 選擇導出路徑
    def select_export_path(self):
        export_path = filedialog.asksaveasfilename(defaultextension='.xlsx')
        self.export_entry.delete(0, END)
        self.export_entry.insert(END, export_path)

    # 查找指定內(nèi)容
    def search(self):
        # 獲取要查找的內(nèi)容列表
        search_texts = [entry.get().strip() for entry in self.search_entries if entry.get().strip()]

        if not search_texts:
            self.status_label.config(text='請至少輸入一個要查找的內(nèi)容')
            return

        # 獲取導出路徑
        export_path = self.export_entry.get().strip()
        if not export_path:
            self.status_label.config(text='請選擇導出路徑')
            return

        # 創(chuàng)建新的Excel文件
        export_wb = openpyxl.Workbook()
        export_ws = export_wb.active

        # 遍歷文件夾內(nèi)的所有Excel文件
        for filename in os.listdir(self.folder_path):
            if filename.endswith('.xlsx'):
                # 打開Excel文件
                filepath = os.path.join(self.folder_path, filename)
                wb = openpyxl.load_workbook(filepath)

                # 遍歷Excel文件中的所有工作表
                for sheetname in wb.sheetnames:
                    ws = wb[sheetname]

                    # 遍歷工作表中的所有單元格
                    for row in ws.iter_rows():
                        for cell in row:
                            for search_text in search_texts:
                                if search_text in str(cell.value):
                                    # 如果找到指定內(nèi)容，提取所需內(nèi)容和緊隨其后的表格數(shù)據(jù)
                                    extract_data(wb, sheetname, cell.row, filepath, search_text, export_ws)

        # 保存并關閉新的Excel文件
        export_wb.save(export_path)
        export_wb.close()

        self.status_label.config(text='查找完成')

# 提取所需內(nèi)容和緊隨其后的表格數(shù)據(jù)
def extract_data(wb, sheetname, row, filepath, search_text, export_ws):
    ws = wb[sheetname]

    # 提取所需內(nèi)容
    data = [search_text]
    for col in range(1, ws.max_column + 1):
        data.append(ws.cell(row=row, column=col).value)

    # 將數(shù)據(jù)寫入新的Excel文件
    export_ws.append(data)

# 創(chuàng)建UI界面并運行程序
root = Tk()
app = App(root)
root.mainloop()

import sys
from cx_Freeze import setup, Executable

build_exe_options = {"packages": ["os"], "excludes": ["tkinter"]}

base = None
if sys.platform == "win32":
    base = "Win32GUI"

setup(
    name="程序名稱",
    version="1.0",
    description="My Application Description",
    options={"build_exe": build_exe_options},
    executables=[Executable("你的源碼.py名稱.py", base=base)]
)