import os
import re
import tkinter as tk
from tkinter import filedialog, messagebox, ttk
from openpyxl import load_workbook

class ExcelChineseExtractorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel 中文提取工具")
        self.root.geometry("800x500")
        self.default_regex = r"[\u4e00-\u9fa5]+"
        self.input_file = tk.StringVar()
        self.output_dir = tk.StringVar()
        self.regex_pattern = tk.StringVar(value=self.default_regex)
        self.input_column = tk.StringVar(value="A")
        self.output_column = tk.StringVar(value="B")
        self.overwrite_mode = tk.BooleanVar(value=False)
        self.create_widgets()

    def create_widgets(self):
        main_frame = tk.Frame(self.root, padx=10, pady=10)
        main_frame.pack(fill="both", expand=True)

        tk.Label(main_frame, text="1. 選擇 Excel 文件:", anchor="w").pack(fill="x", pady=(5, 0))
        file_frame = tk.Frame(main_frame)
        file_frame.pack(fill="x", pady=5)
        tk.Entry(file_frame, textvariable=self.input_file, width=60).pack(side="left", expand=True, fill="x")
        tk.Button(file_frame, text="瀏覽...", command=self.browse_input_file).pack(side="right", padx=5)

        tk.Label(main_frame, text="2. 選擇輸出目錄:", anchor="w").pack(fill="x", pady=(5, 0))
        output_frame = tk.Frame(main_frame)
        output_frame.pack(fill="x", pady=5)
        tk.Entry(output_frame, textvariable=self.output_dir, width=60).pack(side="left", expand=True, fill="x")
        tk.Button(output_frame, text="瀏覽...", command=self.browse_output_dir).pack(side="right", padx=5)

        tk.Label(main_frame, text="3. 自定義正則表達(dá)式（默認(rèn)提取中文）:", anchor="w").pack(fill="x", pady=(5, 0))
        regex_frame = tk.Frame(main_frame)
        regex_frame.pack(fill="x", pady=5)
        tk.Entry(regex_frame, textvariable=self.regex_pattern, width=60).pack(side="left", expand=True, fill="x")
        tk.Button(regex_frame, text="恢復(fù)默認(rèn)", command=self.reset_regex).pack(side="right", padx=5)

        col_frame = tk.Frame(main_frame)
        col_frame.pack(fill="x", pady=5)
        tk.Label(col_frame, text="4. 處理列（如 A）:").pack(side="left")
        tk.Entry(col_frame, textvariable=self.input_column, width=5).pack(side="left", padx=5)
        tk.Label(col_frame, text="輸出列（如 B）:").pack(side="left")
        tk.Entry(col_frame, textvariable=self.output_column, width=5).pack(side="left", padx=5)

        adv_frame = tk.LabelFrame(main_frame, text="高級(jí)選項(xiàng)", padx=10, pady=10)
        adv_frame.pack(fill="x", pady=10)
        tk.Checkbutton(adv_frame, text="覆蓋原文件（不創(chuàng)建新文件）", variable=self.overwrite_mode).pack(anchor="w")

        tk.Button(
            main_frame,
            text="開始提取",
            command=self.run_extraction,
            bg="#4CAF50",
            fg="white",
            height=2,
            font=("Arial", 12, "bold")
        ).pack(fill="x", pady=10)

        self.progress = ttk.Progressbar(main_frame, orient="horizontal", length=700, mode="determinate")
        self.progress.pack(pady=10)

    def browse_input_file(self):
        filepath = filedialog.askopenfilename(
            title="選擇 Excel 文件",
            filetypes=[("Excel 文件", "*.xlsx"), ("所有文件", "*.*")]
        )
        if filepath:
            self.input_file.set(filepath)
            self.output_dir.set(os.path.dirname(filepath))

    def browse_output_dir(self):
        dirpath = filedialog.askdirectory(title="選擇輸出目錄")
        if dirpath:
            self.output_dir.set(dirpath)

    def reset_regex(self):
        self.regex_pattern.set(self.default_regex)

    def run_extraction(self):
        input_path = self.input_file.get()
        output_dir = self.output_dir.get()
        regex = self.regex_pattern.get()
        input_col = self.input_column.get().upper()
        output_col = self.output_column.get().upper()
        overwrite = self.overwrite_mode.get()

        if not input_path or not output_dir:
            messagebox.showwarning("警告", "請(qǐng)選擇輸入文件和輸出目錄！")
            return

        try:
            wb = load_workbook(input_path)
            ws = wb.active

            total_rows = ws.max_row
            self.progress["maximum"] = total_rows
            self.progress["value"] = 0

            for row in range(2, total_rows + 1):
                cell_value = str(ws[f"{input_col}{row}"].value) if ws[f"{input_col}{row}"].value else ""
                matches = re.findall(regex, cell_value)
                extracted_text = " ".join(matches) if matches else ""
                ws[f"{output_col}{row}"].value = extracted_text
                self.progress["value"] = row
                self.root.update()

            if overwrite:
                output_path = input_path
            else:
                filename = os.path.basename(input_path)
                output_path = os.path.join(output_dir, f"提取結(jié)果_{filename}")

            wb.save(output_path)
            messagebox.showinfo("成功", f"提取完成！\n結(jié)果已保存至:\n{output_path}")

        except Exception as e:
            messagebox.showerror("錯(cuò)誤", f"處理失敗:\n{str(e)}")
        finally:
            self.progress["value"] = 0

if __name__ == "__main__":
    root = tk.Tk()
    app = ExcelChineseExtractorApp(root)
    root.mainloop()

# os: 用于文件路徑操作
import os

# re: 用于正則表達(dá)式匹配
import re

# tkinter及相關(guān)模塊: 創(chuàng)建圖形用戶界面
import tkinter as tk
from tkinter import filedialog, messagebox, ttk

# openpyxl: 處理Excel文件
from openpyxl import load_workbook

class ExcelChineseExtractorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel 中文提取工具")
        self.root.geometry("800x500")
        
        # 默認(rèn)正則表達(dá)式（匹配中文字符）
        self.default_regex = r"[\u4e00-\u9fa5]+"
        
        # 界面控件綁定的變量
        self.input_file = tk.StringVar()  # 輸入文件路徑
        self.output_dir = tk.StringVar()  # 輸出目錄
        self.regex_pattern = tk.StringVar(value=self.default_regex)  # 正則表達(dá)式
        self.input_column = tk.StringVar(value="A")  # 輸入列
        self.output_column = tk.StringVar(value="B")  # 輸出列
        self.overwrite_mode = tk.BooleanVar(value=False)  # 是否覆蓋原文件
        
        self.create_widgets()  # 創(chuàng)建界面控件

def create_widgets(self):
    # 主框架
    main_frame = tk.Frame(self.root, padx=10, pady=10)
    main_frame.pack(fill="both", expand=True)

    # 輸入文件選擇部分
    tk.Label(main_frame, text="1. 選擇 Excel 文件:", anchor="w").pack(fill="x", pady=(5, 0))
    file_frame = tk.Frame(main_frame)
    file_frame.pack(fill="x", pady=5)
    tk.Entry(file_frame, textvariable=self.input_file, width=60).pack(side="left", expand=True, fill="x")
    tk.Button(file_frame, text="瀏覽...", command=self.browse_input_file).pack(side="right", padx=5)

    # 輸出目錄選擇部分
    tk.Label(main_frame, text="2. 選擇輸出目錄:", anchor="w").pack(fill="x", pady=(5, 0))
    output_frame = tk.Frame(main_frame)
    output_frame.pack(fill="x", pady=5)
    tk.Entry(output_frame, textvariable=self.output_dir, width=60).pack(side="left", expand=True, fill="x")
    tk.Button(output_frame, text="瀏覽...", command=self.browse_output_dir).pack(side="right", padx=5)

    # 正則表達(dá)式設(shè)置部分
    tk.Label(main_frame, text="3. 自定義正則表達(dá)式（默認(rèn)提取中文）:", anchor="w").pack(fill="x", pady=(5, 0))
    regex_frame = tk.Frame(main_frame)
    regex_frame.pack(fill="x", pady=5)
    tk.Entry(regex_frame, textvariable=self.regex_pattern, width=60).pack(side="left", expand=True, fill="x")
    tk.Button(regex_frame, text="恢復(fù)默認(rèn)", command=self.reset_regex).pack(side="right", padx=5)

    # 列設(shè)置部分
    col_frame = tk.Frame(main_frame)
    col_frame.pack(fill="x", pady=5)
    tk.Label(col_frame, text="4. 處理列（如 A）:").pack(side="left")
    tk.Entry(col_frame, textvariable=self.input_column, width=5).pack(side="left", padx=5)
    tk.Label(col_frame, text="輸出列（如 B）:").pack(side="left")
    tk.Entry(col_frame, textvariable=self.output_column, width=5).pack(side="left", padx=5)

    # 高級(jí)選項(xiàng)部分
    adv_frame = tk.LabelFrame(main_frame, text="高級(jí)選項(xiàng)", padx=10, pady=10)
    adv_frame.pack(fill="x", pady=10)
    tk.Checkbutton(adv_frame, text="覆蓋原文件（不創(chuàng)建新文件）", variable=self.overwrite_mode).pack(anchor="w")

    # 開始按鈕
    tk.Button(
        main_frame,
        text="開始提取",
        command=self.run_extraction,
        bg="#4CAF50",
        fg="white",
        height=2,
        font=("Arial", 12, "bold")
    ).pack(fill="x", pady=10)

    # 進(jìn)度條
    self.progress = ttk.Progressbar(main_frame, orient="horizontal", length=700, mode="determinate")
    self.progress.pack(pady=10)

def browse_input_file(self):
    
    """打開文件對(duì)話框選擇輸入Excel文件"""
    filepath = filedialog.askopenfilename(
        title="選擇 Excel 文件",
        filetypes=[("Excel 文件", "*.xlsx"), ("所有文件", "*.*")]
    )
    if filepath:
        self.input_file.set(filepath)
        # 默認(rèn)設(shè)置輸出目錄為輸入文件所在目錄
        self.output_dir.set(os.path.dirname(filepath))

def browse_output_dir(self):
    
    """打開目錄對(duì)話框選擇輸出目錄"""
    dirpath = filedialog.askdirectory(title="選擇輸出目錄")
    if dirpath:
        self.output_dir.set(dirpath)

def reset_regex(self):
    
    """重置正則表達(dá)式為默認(rèn)值"""
    self.regex_pattern.set(self.default_regex)

def run_extraction(self):
    
    """執(zhí)行文本提取的主要邏輯"""
    # 獲取用戶輸入?yún)?shù)
    input_path = self.input_file.get()
    output_dir = self.output_dir.get()
    regex = self.regex_pattern.get()
    input_col = self.input_column.get().upper()
    output_col = self.output_column.get().upper()
    overwrite = self.overwrite_mode.get()

    # 驗(yàn)證必要參數(shù)
    if not input_path or not output_dir:
        messagebox.showwarning("警告", "請(qǐng)選擇輸入文件和輸出目錄！")
        return

    try:
        # 加載Excel文件
        wb = load_workbook(input_path)
        ws = wb.active

        # 設(shè)置進(jìn)度條最大值
        total_rows = ws.max_row
        self.progress["maximum"] = total_rows
        self.progress["value"] = 0

        # 處理每一行數(shù)據(jù)（從第2行開始，假設(shè)第1行是標(biāo)題）
        for row in range(2, total_rows + 1):
            # 獲取輸入單元格的值
            cell_value = str(ws[f"{input_col}{row}"].value) if ws[f"{input_col}{row}"].value else ""
            
            # 使用正則表達(dá)式提取匹配內(nèi)容
            matches = re.findall(regex, cell_value)
            extracted_text = " ".join(matches) if matches else ""
            
            # 將結(jié)果寫入輸出列
            ws[f"{output_col}{row}"].value = extracted_text
            
            # 更新進(jìn)度條
            self.progress["value"] = row
            self.root.update()  # 保持界面響應(yīng)

        # 確定輸出文件路徑
        if overwrite:
            output_path = input_path  # 覆蓋原文件
        else:
            filename = os.path.basename(input_path)
            output_path = os.path.join(output_dir, f"提取結(jié)果_{filename}")  # 創(chuàng)建新文件

        # 保存結(jié)果
        wb.save(output_path)
        messagebox.showinfo("成功", f"提取完成！\n結(jié)果已保存至:\n{output_path}")

    except Exception as e:
        # 錯(cuò)誤處理
        messagebox.showerror("錯(cuò)誤", f"處理失敗:\n{str(e)}")
    finally:
        # 重置進(jìn)度條
        self.progress["value"] = 0