capture_pro.py

import customtkinter as ctk
import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import json
import os
from datetime import datetime
import threading
import queue
import fitz  # PyMuPDF
import openpyxl
from PIL import Image, ImageDraw, ImageTk
import time # Added for sleep function

import pythoncom # Added for COM initialization

# --- Core Capture Logic ---

def execute_capture(config, output_dir, quality_dpi, log_queue):
    """
    The main logic for capturing Excel ranges.
    Adapted to be called from the GUI and report progress via a queue.
    """
    pythoncom.CoInitialize() # Initialize COM for this thread
    try:
        import win32com.client
    except ImportError:
        log_queue.put("Error: Pywin32 is not installed. Please run: pip install pywin32")
        return

    def log(message):
        log_queue.put(message)

    log(f"Log started at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")

    abs_output_dir = os.path.abspath(output_dir)
    if not os.path.exists(abs_output_dir):
        try:
            os.makedirs(abs_output_dir)
            log(f"Created output directory: {abs_output_dir}") # Keep this for initial setup
        except OSError as e:
            log(f"Error creating directory {abs_output_dir}: {e}")
            return

    excel = None
    workbook = None
    try:
        log("Starting Excel application...")
        excel = win32com.client.Dispatch("Excel.Application")
        excel.Visible = False
        excel.DisplayAlerts = False

        for job in config:
            file_path = os.path.abspath(job["file_path"])
            log(f"\n=========================================================")
            log(f"Processing File: {os.path.basename(file_path)}")
            log(f"=========================================================")

            if not os.path.exists(file_path):
                log(f"Error: Source file not found at {file_path}")
                continue

            try:
                workbook = excel.Workbooks.Open(file_path)
                for sheet_info in job["sheets"]:
                    sheet_name = sheet_info["name"]
                    capture_range = sheet_info["range"]
                    output_filename_format = sheet_info.get("filename_format", "{sheet_name}_{date}")

                    log(f"\n--- Processing Sheet: '{sheet_name}' (Range: {capture_range}) ---")
                    try:
                        worksheet = workbook.Worksheets(sheet_name)
                        worksheet.Activate()

                        # Export selected range to PDF
                        temp_pdf_path = os.path.join(abs_output_dir, f"temp_{os.urandom(8).hex()}.pdf")
                        worksheet.Range(capture_range).ExportAsFixedFormat(0, temp_pdf_path, 0, True, False, 1, 1, False)
                        
                        # Generate output filename based on format
                        date_str = datetime.now().strftime("%Y-%m-%d")
                        import re
                        thai_alphanumeric_pattern = re.compile(r'[\u0E00-\u0E7F0-9a-zA-Z _]+')
                        
                        safe_sheet_name = "".join(thai_alphanumeric_pattern.findall(sheet_name)).rstrip()
                        safe_workbook_name = "".join(thai_alphanumeric_pattern.findall(os.path.basename(file_path).split('.')[0])).rstrip()
                        
                        output_filename = output_filename_format.format(
                            sheet_name=safe_sheet_name,
                            date=date_str,
                            workbook_name=safe_workbook_name
                        )
                        abs_output_path = os.path.join(abs_output_dir, f"{output_filename}.png")

                        # Convert PDF to high-quality PNG
                        doc = fitz.open(temp_pdf_path)
                        page = doc.load_page(0)  # Assuming the range fits on one page

                        # Find the bounding box of the actual content (text and drawings)
                        content_bbox = fitz.Rect()
                        text_blocks = page.get_text("dict")["blocks"]
                        for block in text_blocks:
                            if block["type"] == 0: # Text block
                                content_bbox |= fitz.Rect(block["bbox"])
                        
                        for draw_obj in page.get_drawings():
                            content_bbox |= draw_obj["rect"]

                        # Add a small margin around the detected content
                        margin = 10 # pixels
                        if not content_bbox.is_empty: # Only expand if content was found
                            x0 = content_bbox.x0 - margin
                            y0 = content_bbox.y0 - margin
                            x1 = content_bbox.x1 + margin
                            y1 = content_bbox.y1 + margin
                            clip_rect = fitz.Rect(x0, y0, x1, y1)
                        else:
                            clip_rect = page.rect # Fallback to full page if no content detected

                        # Ensure the clip_rect is within the page boundaries
                        clip_rect = clip_rect.intersect(page.rect)

                        pix = page.get_pixmap(matrix=fitz.Matrix(quality_dpi / 72, quality_dpi / 72), clip=clip_rect)
                        pix.save(abs_output_path)
                        doc.close()
                        time.sleep(0.1) # Give time for file to be released
                        log(f"Successfully captured '{sheet_name}'.")

                    except Exception as inner_e:
                        log(f"Could not process sheet '{sheet_name}'. Reason: {inner_e}")
                    finally:
                        if os.path.exists(temp_pdf_path):
                            os.remove(temp_pdf_path) # Clean up temp PDF
                
                workbook.Close(SaveChanges=False)
                workbook = None

            except Exception as file_e:
                log(f"An error occurred while processing the file. Reason: {file_e}")

        log("\nAll jobs processed.")

    except Exception as e:
        log(f"A critical error occurred: {e}")

    finally:
        if excel:
            log("Closing Excel application.")
            if workbook:
                workbook.Close(SaveChanges=False)
            excel.Quit()
        log(f"\nLog finished at {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}")
        pythoncom.CoUninitialize() # Uninitialize COM for this thread


# --- GUI Application ---

class CaptureProApp(ctk.CTk):
    CONFIG_FILE = "capture_pro_config.json"

    def __init__(self):
        super().__init__()

        self.title("Capture Pro - Excel Range to Image")
        self.geometry("1000x700")

        self.config = []
        self.output_dir = os.path.join(os.path.expanduser("~"), "Desktop") # Default output dir
        self.image_quality_dpi = 300 # Default DPI

        self.load_config()
        self.create_icon_file()
        self.setup_widgets()
        self.populate_tree()
        
        self.log_queue = queue.Queue()
        self.after(100, self.process_log_queue)

        self._drag_start_item = None # For multi-selection drag

    def create_icon_file(self):
        # Create a simple PNG icon (green grid on dark green background)
        img = Image.new('RGBA', (256, 256), (0, 0, 0, 0)) # Transparent background
        draw = ImageDraw.Draw(img)

        # Dark green background
        draw.rectangle([(0, 0), (256, 256)], fill=(0, 100, 0, 255)) # Dark green

        # Lighter green grid lines
        line_color = (0, 200, 0, 255) # Lighter green
        for i in range(0, 256, 32):
            draw.line([(i, 0), (i, 256)], fill=line_color, width=2)
            draw.line([(0, i), (256, i)], fill=line_color, width=2)
        
        # Save as ICO
        self.icon_path = os.path.join(os.path.dirname(__file__), "app_icon.ico")
        img.save(self.icon_path, format="ICO")
        self.iconbitmap(self.icon_path)

    def setup_widgets(self):
        # --- Main Layout (Grid) ---
        self.grid_columnconfigure(1, weight=1)
        self.grid_rowconfigure(0, weight=1)

        # --- Sidebar Frame ---
        self.sidebar_frame = ctk.CTkFrame(self, width=180, corner_radius=0)
        self.sidebar_frame.grid(row=0, column=0, rowspan=4, sticky="nsew")
        self.sidebar_frame.grid_rowconfigure(4, weight=1)

        self.logo_label = ctk.CTkLabel(self.sidebar_frame, text="Capture Pro", font=ctk.CTkFont(size=24, weight="bold"))
        self.logo_label.grid(row=0, column=0, padx=20, pady=(20, 10))

        self.add_file_button = ctk.CTkButton(self.sidebar_frame, text="Add Excel File", command=self.add_file, font=ctk.CTkFont(size=14))
        self.add_file_button.grid(row=1, column=0, padx=20, pady=10)

        self.add_sheet_button = ctk.CTkButton(self.sidebar_frame, text="Add Sheet/Range", command=self.add_sheet_to_selected_file, font=ctk.CTkFont(size=14))
        self.add_sheet_button.grid(row=2, column=0, padx=20, pady=10)

        self.edit_selected_button = ctk.CTkButton(self.sidebar_frame, text="Edit Selected", command=self.edit_selected, font=ctk.CTkFont(size=14))
        self.edit_selected_button.grid(row=3, column=0, padx=20, pady=10)

        self.remove_selected_button = ctk.CTkButton(self.sidebar_frame, text="Remove Selected", command=self.remove_selected, font=ctk.CTkFont(size=14))
        self.remove_selected_button.grid(row=4, column=0, padx=20, pady=10)

        # Output Directory
        self.output_dir_label = ctk.CTkLabel(self.sidebar_frame, text="Output Folder:", anchor="w", font=ctk.CTkFont(size=14))
        self.output_dir_label.grid(row=5, column=0, padx=20, pady=(10, 0))
        self.output_dir_var = ctk.StringVar(value=self.output_dir)
        self.output_dir_entry = ctk.CTkEntry(self.sidebar_frame, textvariable=self.output_dir_var, state="readonly", font=ctk.CTkFont(size=14))
        self.output_dir_entry.grid(row=6, column=0, padx=20, pady=(0, 10), sticky="ew")
        self.browse_output_button = ctk.CTkButton(self.sidebar_frame, text="Browse...", command=self.select_output_dir, font=ctk.CTkFont(size=14))
        self.browse_output_button.grid(row=7, column=0, padx=20, pady=(0, 20))

        # Image Quality
        self.quality_label = ctk.CTkLabel(self.sidebar_frame, text="Image Quality (DPI):", font=ctk.CTkFont(size=14))
        self.quality_label.grid(row=8, column=0, padx=20, pady=(10, 0))
        self.quality_optionmenu = ctk.CTkOptionMenu(self.sidebar_frame, values=["150", "300", "600"], command=self.change_quality, font=ctk.CTkFont(size=14))
        self.quality_optionmenu.set(str(self.image_quality_dpi)) # Set initial value
        self.quality_optionmenu.grid(row=9, column=0, padx=20, pady=(0, 20))

        # --- Main Content Frame ---
        self.main_frame = ctk.CTkFrame(self, corner_radius=0)
        self.main_frame.grid(row=0, column=1, sticky="nsew", padx=10, pady=10)
        self.main_frame.grid_columnconfigure(0, weight=1)
        self.main_frame.grid_rowconfigure(0, weight=1)

        # Treeview for config display
        self.tree_frame = ctk.CTkFrame(self.main_frame)
        self.tree_frame.grid(row=0, column=0, sticky="nsew", pady=(0, 10))
        self.tree_frame.grid_columnconfigure(0, weight=1)
        self.tree_frame.grid_rowconfigure(0, weight=1)

        self.tree = ttk.Treeview(self.tree_frame, columns=("type", "details"), show="headings", selectmode="extended")
        self.tree.heading("type", text="Item")
        self.tree.heading("details", text="Details")
        self.tree.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)

        # Apply CustomTkinter styling to Treeview
        style = ttk.Style()
        style.theme_use("default") # Use default theme as base

        current_appearance_mode = ctk.get_appearance_mode() # "dark" or "light"
        mode_index = 0 if current_appearance_mode == "dark" else 1

        # Get colors from the ThemeManager
        main_bg_color = ctk.ThemeManager.theme["CTkFrame"]["fg_color"][mode_index]
        text_color = ctk.ThemeManager.theme["CTkLabel"]["text_color"][mode_index]
        
        # Accent color: Get the fg_color for CTkButton for the currently active theme
        accent_color_tuple = ctk.ThemeManager.theme["CTkButton"]["fg_color"]
        accent_color = accent_color_tuple[mode_index]

        # Heading background color (let's use the accent color for a distinct look)
        heading_bg_color = accent_color

        # Selected text color (usually white on accent color)
        selected_text_color = "#FFFFFF" # White color for selected text

        style.configure("Treeview",
                        background=main_bg_color,
                        foreground=text_color,
                        fieldbackground=main_bg_color,
                        bordercolor=main_bg_color,
                        lightcolor=main_bg_color,
                        darkcolor=main_bg_color)
        style.map("Treeview",
                  background=[("selected", accent_color)],
                  foreground=[("selected", selected_text_color)])
        style.configure("Treeview.Heading",
                        background=heading_bg_color,
                        foreground=text_color,
                        font=("Segoe UI", 12, "bold"))

        scrollbar = ctk.CTkScrollbar(self.tree_frame, command=self.tree.yview)
        self.tree.configure(yscrollcommand=scrollbar.set)
        scrollbar.pack(side=tk.RIGHT, fill=tk.Y)

        # Log Display
        self.log_frame = ctk.CTkFrame(self.main_frame)
        self.log_frame.grid(row=1, column=0, sticky="nsew")
        self.log_frame.grid_columnconfigure(0, weight=1)
        self.log_frame.grid_rowconfigure(0, weight=1)

        self.log_text = ctk.CTkTextbox(self.log_frame, height=10, state="disabled", wrap="word", font=("Segoe UI", 14))
        self.log_text.grid(row=0, column=0, sticky="nsew", padx=10, pady=10)
        
        self.log_scrollbar = ctk.CTkScrollbar(self.log_frame, command=self.log_text.yview)
        self.log_text.configure(yscrollcommand=self.log_scrollbar.set)
        self.log_scrollbar.grid(row=0, column=1, sticky="ns", padx=(0, 10), pady=10)

        # Action Button
        self.run_button = ctk.CTkButton(self, text="Start Capture", command=self.start_capture_thread, font=ctk.CTkFont(size=20, weight="bold"))
        self.run_button.grid(row=1, column=1, pady=10)

    def populate_tree(self):
        for item in self.tree.get_children():
            self.tree.delete(item)
        
        for i, job in enumerate(self.config):
            file_node = self.tree.insert("", "end", iid=f"file_{i}", values=("File", os.path.basename(job['file_path'])))
            self.tree.item(file_node, open=True) # Automatically expand the file node
            for j, sheet in enumerate(job['sheets']):
                self.tree.insert(file_node, "end", iid=f"file_{i}_sheet_{j}", values=(f"  - {sheet['name']}", f"Sheet: {sheet['name']} | Range: {sheet['range']}"))

    

    def add_file(self):
        filepath = filedialog.askopenfilename(
            title="Select an Excel File",
            filetypes=(("Excel Files", "*.xlsx *.xls *.xlsm"), ("All files", "*.*" ))
        )
        if not filepath:
            return

        # Check for duplicates
        if any(job['file_path'] == filepath for job in self.config):
            messagebox.showwarning("Duplicate", "This file is already in the list.")
            return

        # Prompt for adding all sheets
        add_all = messagebox.askyesno("Add Sheets", "Do you want to add all sheets from this workbook automatically?")

        if add_all:
            try:
                workbook = openpyxl.load_workbook(filepath, read_only=True)
                sheets_to_add = []
                for sheet_name in workbook.sheetnames:
                    # Attempt to auto-detect range
                    try:
                        ws = workbook[sheet_name]
                        min_row, min_col, max_row, max_col = ws.max_row + 1, ws.max_column + 1, 0, 0
                        has_data = False

                        for row_idx, row in enumerate(ws.iter_rows(), 1):
                            for col_idx, cell in enumerate(row, 1):
                                if cell.value is not None and str(cell.value).strip() != "":
                                    has_data = True
                                    min_row = min(min_row, row_idx)
                                    min_col = min(min_col, col_idx)
                                    max_row = max(max_row, row_idx)
                                    max_col = max(max_col, col_idx)
                        
                        if not has_data: # If no data found, default to A1:A1
                            detected_start = "A1"
                            detected_end = "A1"
                        else:
                            detected_start = openpyxl.utils.get_column_letter(min_col) + str(min_row)
                            detected_end = openpyxl.utils.get_column_letter(max_col) + str(max_row)

                        sheets_to_add.append({"name": sheet_name, "range": f"{detected_start}:{detected_end}", "filename_format": "{sheet_name}_{date}"})
                    except Exception as e:
                        self.log_message(f"Warning: Could not auto-detect range for sheet '{sheet_name}': {e}")
                        sheets_to_add.append({"name": sheet_name, "range": "A1", "filename_format": "{sheet_name}_{date}"}) # Fallback
                
                self.config.append({"file_path": filepath, "sheets": sheets_to_add})
                self.log_message(f"Added {len(sheets_to_add)} sheets from {os.path.basename(filepath)}.")

            except Exception as e:
                messagebox.showerror("Error", f"Could not read Excel file to get sheet names: {e}")
                return
        else:
            self.config.append({"file_path": filepath, "sheets": []}) # Only add file, no sheets

        self.populate_tree()
        self.save_config()

    def _open_sheet_dialog(self, file_index, existing_sheet_info=None):
        filepath = self.config[file_index]["file_path"]
        
        dialog = SheetDialog(self, filepath, existing_sheet_info, self.log_queue) # Pass self (root) and filepath
        self.wait_window(dialog)

        if dialog.result:
            sheet_name, start_cell, end_cell, filename_format = dialog.result
            sheet_range = f"{start_cell}:{end_cell}"

            if existing_sheet_info:
                # Update existing sheet
                for i, sheet in enumerate(self.config[file_index]["sheets"]):
                    if sheet is existing_sheet_info:
                        self.config[file_index]["sheets"][i] = {"name": sheet_name, "range": sheet_range, "filename_format": filename_format}
                        break
            else:
                # Add new sheet
                self.config[file_index]["sheets"].append({"name": sheet_name, "range": sheet_range, "filename_format": filename_format})
            
            self.populate_tree()
            self.save_config()

    def add_sheet_to_selected_file(self):
        selected = self.tree.selection()
        if not selected:
            self.log_message("Error: No Excel file selected for adding sheet.")
            messagebox.showerror("Error", "Please select an Excel file in the list first.")
            return
        
        item_id = selected[0]
        self.log_message(f"Selected item_id for adding sheet: {item_id}")

        if "_sheet_" in item_id: # If a sheet is selected, use its parent file
            parent_id = self.tree.parent(item_id)
            item_id = parent_id

        try:
            file_index = int(item_id.split("_")[1])
            self.log_message(f"Parsed file_index for adding sheet: {file_index}")
            self._open_sheet_dialog(file_index=file_index)
        except IndexError:
            self.log_message(f"Error: Could not parse item_id for adding sheet. item_id: {item_id}")
            messagebox.showerror("Error", "Invalid selection for adding sheet. Please try again.")
        except Exception as e:
            self.log_message(f"An unexpected error occurred during add_sheet_to_selected_file: {e}")
            messagebox.showerror("Error", f"An unexpected error occurred: {e}")

    def edit_selected(self):
        selected = self.tree.selection()
        if not selected:
            self.log_message("Error: No item selected for editing.")
            messagebox.showerror("Error", "Please select a sheet to edit.")
            return
        
        item_iid = selected[0]
        self.log_message(f"Selected item_iid for editing: {item_iid}")

        if "_sheet_" not in item_iid: # If it doesn't contain "_sheet_", it's a file node
            self.log_message("Error: Selected item is a file, not a sheet.")
            messagebox.showerror("Error", "Please select a specific sheet to edit, not the file.")
            return

        try:
            parent_id = self.tree.parent(item_iid)
            file_index = int(parent_id.split("_")[1])
            sheet_index = int(item_iid.split("_")[-1])
            
            self.log_message(f"Parsed file_index: {file_index}, sheet_index: {sheet_index}")
            
            existing_sheet_info = self.config[file_index]["sheets"][sheet_index]
            self.log_message(f"Retrieved existing_sheet_info: {existing_sheet_info}")

            self._open_sheet_dialog(file_index=file_index, existing_sheet_info=existing_sheet_info)
        except IndexError:
            self.log_message(f"Error: Could not parse item_iid or access config for editing. item_iid: {item_iid}")
            messagebox.showerror("Error", "Invalid selection for editing. Please try again.")
        except Exception as e:
            self.log_message(f"An unexpected error occurred during edit_selected: {e}")
            messagebox.showerror("Error", f"An unexpected error occurred: {e}")

    def remove_selected(self):
        selected_items = self.tree.selection()
        if not selected_items:
            messagebox.showerror("Error", "Please select at least one item to remove.")
            return

        if messagebox.askyesno("Confirm Removal", f"Are you sure you want to remove {len(selected_items)} selected items?"):
            # Separate files and sheets for removal
            files_to_remove_indices = set()
            sheets_to_remove_info = [] # (file_index, sheet_index)

            for item_id in selected_items:
                if "_sheet_" not in item_id: # It's a file node
                    file_index = int(item_id.split("_")[1])
                    files_to_remove_indices.add(file_index)
                else: # It's a sheet node
                    parent_id = self.tree.parent(item_id)
                    file_index = int(parent_id.split("_")[1])
                    sheet_index = int(item_id.split("_")[-1])
                    sheets_to_remove_info.append((file_index, sheet_index))
            
            # Sort sheets to remove in reverse order to avoid index issues
            sheets_to_remove_info.sort(key=lambda x: (x[0], x[1]), reverse=True)

            # Remove sheets first
            for file_index, sheet_index in sheets_to_remove_info:
                # Only remove if the parent file is not also being removed
                if file_index not in files_to_remove_indices:
                    del self.config[file_index]['sheets'][sheet_index]

            # Remove files
            # Sort file indices in reverse order to avoid index issues
            sorted_files_to_remove = sorted(list(files_to_remove_indices), reverse=True)
            for file_index in sorted_files_to_remove:
                del self.config[file_index]

            self.populate_tree() # Re-populate the treeview to reflect changes
            self.save_config()
            messagebox.showinfo("Removal Complete", f"Successfully removed selected items.")

    def select_output_dir(self):
        dir_path = filedialog.askdirectory(title="Select Output Folder")
        if dir_path:
            self.output_dir = dir_path
            self.output_dir_var.set(self.output_dir)
            self.save_config()

    def change_quality(self, new_quality):
        self.image_quality_dpi = int(new_quality)
        self.save_config()

    def log_message(self, message):
        self.log_text.configure(state="normal")
        self.log_text.insert("end", message + "\n")
        self.log_text.configure(state="disabled")
        self.log_text.see("end")

    def process_log_queue(self):
        try:
            while True:
                message = self.log_queue.get_nowait()
                self.log_message(message.strip())
        except queue.Empty:
            pass
        self.after(100, self.process_log_queue)

    def start_capture_thread(self):
        if not self.config:
            messagebox.showerror("Error", "Configuration is empty. Please add at least one file and sheet.")
            return
        
        self.run_button.configure(state="disabled", text="Capturing...")
        self.log_text.configure(state="normal")
        self.log_text.delete("1.0", "end")
        self.log_text.configure(state="disabled")

        thread = threading.Thread(target=self.run_capture_logic, daemon=True)
        thread.start()

    def run_capture_logic(self):
        execute_capture(self.config, self.output_dir, self.image_quality_dpi, self.log_queue)
        self.after(0, self.on_capture_complete)

    def on_capture_complete(self):
        self.run_button.configure(state="normal", text="Start Capture")
        messagebox.showinfo("Complete", "Capture process has finished. Check the log for details.")

    def load_config(self):
        try:
            if os.path.exists(self.CONFIG_FILE):
                with open(self.CONFIG_FILE, 'r') as f:
                    data = json.load(f)
                    self.config = data.get("config", [])
                    self.output_dir = data.get("output_dir", self.output_dir)
                    self.image_quality_dpi = data.get("image_quality_dpi", self.image_quality_dpi)
        except (json.JSONDecodeError, IOError) as e:
            messagebox.showerror("Error", f"Could not load config file: {e}")
            self.config = []

    def save_config(self):
        try:
            with open(self.CONFIG_FILE, 'w') as f:
                json.dump({"config": self.config, "output_dir": self.output_dir, "image_quality_dpi": self.image_quality_dpi}, f, indent=4)
        except IOError as e:
            messagebox.showerror("Error", f"Could not save config file: {e}")

    def on_closing(self):
        self.save_config()
        if os.path.exists(self.icon_path):
            os.remove(self.icon_path) # Clean up temp icon file
        self.destroy()

class SheetDialog(ctk.CTkToplevel):
    def __init__(self, parent, excel_filepath, existing_sheet_info=None, log_queue=None):
        super().__init__(parent)
        self.title("Add/Edit Sheet and Range")
        self.transient(parent)
        self.grab_set()

        self.result = None
        self.excel_filepath = excel_filepath
        self.existing_sheet_info = existing_sheet_info
        self.log_queue = log_queue # Store log_queue

        self.sheet_names = self.get_sheet_names()

        self.setup_widgets()
        if existing_sheet_info:
            self.load_existing_data()

    def get_sheet_names(self):
        try:
            workbook = openpyxl.load_workbook(self.excel_filepath, read_only=True)
            return workbook.sheetnames
        except Exception as e:
            if self.log_queue:
                self.log_queue.put(f"Error: Could not read Excel file '{os.path.basename(self.excel_filepath)}' to get sheet names: {e}")
            messagebox.showerror("Error", f"Could not read Excel file to get sheet names: {e}")
            return []

    def setup_widgets(self):
        ctk.CTkLabel(self, text="Sheet Name:", font=ctk.CTkFont(size=14)).grid(row=0, column=0, padx=10, pady=5, sticky="w")
        self.sheet_name_var = ctk.StringVar(self)
        if self.sheet_names:
            self.sheet_name_var.set(self.sheet_names[0]) # Set default to first sheet
        self.sheet_name_optionmenu = ctk.CTkOptionMenu(self, variable=self.sheet_name_var, values=self.sheet_names, font=ctk.CTkFont(size=14))
        self.sheet_name_optionmenu.grid(row=0, column=1, padx=10, pady=5, sticky="ew")

        # Range Inputs
        ctk.CTkLabel(self, text="Start Cell (e.g., A1):", font=ctk.CTkFont(size=14)).grid(row=1, column=0, padx=10, pady=5, sticky="w")
        self.start_cell_entry = ctk.CTkEntry(self, width=30, font=ctk.CTkFont(size=14))
        self.start_cell_entry.grid(row=1, column=1, padx=10, pady=5, sticky="ew")

        ctk.CTkLabel(self, text="End Cell (e.g., I64):", font=ctk.CTkFont(size=14)).grid(row=2, column=0, padx=10, pady=5, sticky="w")
        self.end_cell_entry = ctk.CTkEntry(self, width=30, font=ctk.CTkFont(size=14))
        self.end_cell_entry.grid(row=2, column=1, padx=10, pady=5, sticky="ew")

        # Auto-Detect Button
        self.auto_detect_button = ctk.CTkButton(self, text="Auto-Detect Range", command=self.auto_detect_range, font=ctk.CTkFont(size=14))
        self.auto_detect_button.grid(row=3, column=0, columnspan=2, padx=10, pady=10, sticky="ew")

        # Filename Format
        ctk.CTkLabel(self, text="Output Filename Format:", font=ctk.CTkFont(size=14)).grid(row=4, column=0, padx=10, pady=5, sticky="w")
        self.filename_format_entry = ctk.CTkEntry(self, width=30, font=ctk.CTkFont(size=14))
        self.filename_format_entry.insert(0, "{sheet_name}_{date}") # Default format
        self.filename_format_entry.grid(row=4, column=1, padx=10, pady=5, sticky="ew")
        ctk.CTkLabel(self, text="Use {sheet_name}, {date}, {workbook_name}", font=("Segoe UI", 11)).grid(row=5, column=0, columnspan=2, padx=10, pady=(0,5), sticky="w")

        # Buttons
        ok_button = ctk.CTkButton(self, text="OK", command=self.ok, font=ctk.CTkFont(size=14))
        ok_button.grid(row=6, column=0, padx=10, pady=10, sticky="ew")
        cancel_button = ctk.CTkButton(self, text="Cancel", command=self.cancel, font=ctk.CTkFont(size=14))
        cancel_button.grid(row=6, column=1, padx=10, pady=10, sticky="ew")

        self.sheet_name_optionmenu.focus_set()

    def load_existing_data(self):
        if self.existing_sheet_info:
            self.sheet_name_var.set(self.existing_sheet_info["name"])
            
            # Split range into start and end cells
            full_range = self.existing_sheet_info["range"]
            if ":" in full_range:
                start, end = full_range.split(":")
                self.start_cell_entry.insert(0, start)
                self.end_cell_entry.insert(0, end)
            else:
                self.start_cell_entry.insert(0, full_range)
                self.end_cell_entry.insert(0, "") # No end cell if only one
            
            self.filename_format_entry.delete(0, "end")
            self.filename_format_entry.insert(0, self.existing_sheet_info.get("filename_format", "{sheet_name}_{date}"))

    def auto_detect_range(self):
        selected_sheet_name = self.sheet_name_var.get()
        if not selected_sheet_name:
            messagebox.showwarning("Warning", "Please select a sheet first.")
            return
        
        try:
            workbook = openpyxl.load_workbook(self.excel_filepath, read_only=True)
            ws = workbook[selected_sheet_name]
            
            min_row, min_col, max_row, max_col = ws.max_row + 1, ws.max_column + 1, 0, 0
            has_data = False

            for row_idx, row in enumerate(ws.iter_rows(), 1):
                for col_idx, cell in enumerate(row, 1):
                    if cell.value is not None and str(cell.value).strip() != "":
                        has_data = True
                        min_row = min(min_row, row_idx)
                        min_col = min(min_col, col_idx)
                        max_row = max(max_row, row_idx)
                        max_col = max(max_col, col_idx)
            
            if not has_data: # If no data found, default to A1:A1
                detected_start = "A1"
                detected_end = "A1"
            else:
                detected_start = openpyxl.utils.get_column_letter(min_col) + str(min_row)
                detected_end = openpyxl.utils.get_column_letter(max_col) + str(max_row)

            self.start_cell_entry.delete(0, "end")
            self.start_cell_entry.insert(0, detected_start)
            self.end_cell_entry.delete(0, "end")
            self.end_cell_entry.insert(0, detected_end)

        except Exception as e:
            messagebox.showerror("Error", f"Could not auto-detect range for sheet '{selected_sheet_name}': {e}")

    def ok(self):
        sheet_name = self.sheet_name_var.get().strip()
        start_cell = self.start_cell_entry.get().strip().upper() # Convert to uppercase
        end_cell = self.end_cell_entry.get().strip().upper() # Convert to uppercase
        filename_format = self.filename_format_entry.get().strip()

        if not all([sheet_name, start_cell, end_cell, filename_format]):
            messagebox.showerror("Error", "All fields are required.", parent=self)
            return
        
        self.result = (sheet_name, start_cell, end_cell, filename_format)
        self.destroy()

    def cancel(self):
        self.destroy()

if __name__ == "__main__":
    ctk.set_appearance_mode("dark")  # Modes: "System" (default), "Dark", "Light"
    ctk.set_default_color_theme("blue")  # Themes: "blue" (default), "dark-blue", "green"

    app = CaptureProApp()
    app.protocol("WM_DELETE_WINDOW", app.on_closing)
    app.mainloop()