import tkinter as tk
from tkinter import ttk, filedialog, messagebox
import openpyxl
import copy

class ExcelEditorApp:
    def __init__(self, root):
        self.root = root
        self.root.title("Excel簡易編集ツール")
        self.root.geometry("900x600")

        # Excel関連
        self.wb = None
        self.sheet = None
        self.sheet_data = []
        self.undo_stack = []

        # GUI構築
        self.create_widgets()

    def create_widgets(self):
        # 上部ボタンフレーム
        frame = ttk.Frame(self.root)
        frame.pack(fill=tk.X, padx=5, pady=5)

        ttk.Button(frame, text="Excel読み込み", command=self.load_excel).pack(side=tk.LEFT, padx=5)
        self.sheet_cb = ttk.Combobox(frame, state="readonly")
        self.sheet_cb.pack(side=tk.LEFT, padx=5)
        self.sheet_cb.bind("<<ComboboxSelected>>", self.change_sheet)

        ttk.Button(frame, text="保存", command=self.save_excel).pack(side=tk.LEFT, padx=5)
        ttk.Button(frame, text="Undo", command=self.undo).pack(side=tk.LEFT, padx=5)

        # フィルタ / ソート
        ttk.Label(frame, text="フィルタ(列番号=値):").pack(side=tk.LEFT, padx=5)
        self.filter_entry = ttk.Entry(frame, width=15)
        self.filter_entry.pack(side=tk.LEFT, padx=5)
        ttk.Button(frame, text="適用", command=self.apply_filter).pack(side=tk.LEFT, padx=5)
        ttk.Button(frame, text="リセット", command=self.reset_filter).pack(side=tk.LEFT, padx=5)

        # Treeview
        self.tree = ttk.Treeview(self.root, show="headings")
        self.tree.pack(fill=tk.BOTH, expand=True)
        self.tree.bind("<Double-1>", self.edit_cell)

        # スクロールバー
        vsb = ttk.Scrollbar(self.tree, orient="vertical", command=self.tree.yview)
        vsb.pack(side="right", fill="y")
        self.tree.configure(yscrollcommand=vsb.set)

    def load_excel(self):
        file_path = filedialog.askopenfilename(filetypes=[("Excel files", "*.xlsx")])
        if not file_path:
            return
        try:
            self.wb = openpyxl.load_workbook(file_path)
            self.sheet_cb['values'] = self.wb.sheetnames
            self.sheet_cb.current(0)
            self.change_sheet()
        except Exception as e:
            messagebox.showerror("エラー", f"Excel読み込み失敗:\n{e}")

    def change_sheet(self, event=None):
        sheet_name = self.sheet_cb.get()
        if not sheet_name:
            return
        self.sheet = self.wb[sheet_name]
        self.sheet_data = [[cell.value for cell in row] for row in self.sheet.iter_rows()]
        self.display_data(self.sheet_data)

    def display_data(self, data):
        # ヘッダ設定
        self.tree.delete(*self.tree.get_children())
        if not data:
            return
        self.tree["columns"] = list(range(len(data[0])))
        for i, _ in enumerate(data[0]):
            self.tree.heading(i, text=f"Col {i}")
            self.tree.column(i, width=100, anchor=tk.CENTER)

        for row in data:
            self.tree.insert("", tk.END, values=row)

    def edit_cell(self, event):
        selected = self.tree.selection()
        if not selected:
            return
        col = self.tree.identify_column(event.x)
        row = self.tree.identify_row(event.y)
        col_index = int(col.replace("#","")) - 1
        row_index = self.tree.index(row)
        old_value = self.sheet_data[row_index][col_index]

        # 編集ダイアログ
        new_value = tk.simpledialog.askstring("セル編集", f"旧値: {old_value}", initialvalue=str(old_value))
        if new_value is None:
            return

        # Undo用に保存
        self.undo_stack.append(copy.deepcopy(self.sheet_data))

        # 更新
        self.sheet_data[row_index][col_index] = new_value
        self.display_data(self.sheet_data)

    def save_excel(self):
        if not self.wb or not self.sheet:
            return
        try:
            # Treeviewデータをシートに書き込み
            for r_idx, row in enumerate(self.sheet_data):
                for c_idx, value in enumerate(row):
                    self.sheet.cell(row=r_idx+1, column=c_idx+1, value=value)
            self.wb.save(filedialog.asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")]))
            messagebox.showinfo("保存完了", "Excelファイルを保存しました。")
        except Exception as e:
            messagebox.showerror("エラー", f"保存失敗:\n{e}")

    def undo(self):
        if not self.undo_stack:
            messagebox.showinfo("Undo", "Undo履歴なし")
            return
        self.sheet_data = self.undo_stack.pop()
        self.display_data(self.sheet_data)

    def apply_filter(self):
        filter_text = self.filter_entry.get()
        if "=" not in filter_text:
            messagebox.showerror("エラー", "正しい形式で入力してください（例: 0=Apple）")
            return
        col_str, val = filter_text.split("=", 1)
        try:
            col_idx = int(col_str)
        except ValueError:
            messagebox.showerror("エラー", "列番号は整数で指定してください")
            return
        filtered = [row for row in self.sheet_data if str(row[col_idx]) == val]
        self.display_data(filtered)

    def reset_filter(self):
        self.display_data(self.sheet_data)

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