Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\tkinter\__init__.py", line 2068, in __call__
    return self.func(*args)
           ~~~~~~~~~^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 579, in change_column_data_types
    self.app.table_display.apply_column_data_types(new_data_types)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 743, in apply_column_data_types
    sheet.format(
    ~~~~~~~~~~~~^
        column_letter,
        ^^^^^^^^^^^^^^
        formatter=int_formatter()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\sheet.py", line 2814, in format
    kwargs = fix_format_kwargs({"formatter": formatter_class, **formatter_options, **kwargs})
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\functions.py", line 1339, in fix_format_kwargs
    if not isinstance(kwargs["invalid_value"], str):
                      ~~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'invalid_value'
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\tkinter\__init__.py", line 2068, in __call__
    return self.func(*args)
           ~~~~~~~~~^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 579, in change_column_data_types
    self.app.table_display.apply_column_data_types(new_data_types)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 743, in apply_column_data_types
    sheet.format(
    ~~~~~~~~~~~~^
        column_letter,
        ^^^^^^^^^^^^^^
        formatter=int_formatter()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\sheet.py", line 2814, in format
    kwargs = fix_format_kwargs({"formatter": formatter_class, **formatter_options, **kwargs})
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\functions.py", line 1339, in fix_format_kwargs
    if not isinstance(kwargs["invalid_value"], str):
                      ~~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'invalid_value'
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\tkinter\__init__.py", line 2068, in __call__
    return self.func(*args)
           ~~~~~~~~~^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 579, in change_column_data_types
    self.app.table_display.apply_column_data_types(new_data_types)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 743, in apply_column_data_types
    sheet.format(
    ~~~~~~~~~~~~^
        column_letter,
        ^^^^^^^^^^^^^^
        formatter=int_formatter()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\sheet.py", line 2814, in format
    kwargs = fix_format_kwargs({"formatter": formatter_class, **formatter_options, **kwargs})
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\functions.py", line 1339, in fix_format_kwargs
    if not isinstance(kwargs["invalid_value"], str):
                      ~~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'invalid_value'
Exception in Tkinter callback
Traceback (most recent call last):
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\tkinter\__init__.py", line 2068, in __call__
    return self.func(*args)
           ~~~~~~~~~^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 579, in change_column_data_types
    self.app.table_display.apply_column_data_types(new_data_types)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^
  File "D:\Bilder\Python\Database\Database 86.py", line 743, in apply_column_data_types
    sheet.format(
    ~~~~~~~~~~~~^
        column_letter,
        ^^^^^^^^^^^^^^
        formatter=int_formatter()
        ^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\sheet.py", line 2814, in format
    kwargs = fix_format_kwargs({"formatter": formatter_class, **formatter_options, **kwargs})
  File "C:\Users\Zarathustra\AppData\Local\Programs\Python\Python313\Lib\site-packages\tksheet\functions.py", line 1339, in fix_format_kwargs
    if not isinstance(kwargs["invalid_value"], str):
                      ~~~~~~^^^^^^^^^^^^^^^^^
KeyError: 'invalid_value'  tksheet handbook: Data Formatting

By default tksheet stores all user inputted data as strings and while tksheet can store and display any datatype with a __str__() method this has some obvious limitations.

Data formatting aims to provide greater functionality when working with different datatypes and provide strict typing for the sheet. With formatting you can convert sheet data and user input to a specific datatype.

Additionally, formatting also provides a function for displaying data on the table GUI (as a rounded float for example) and logic for handling invalid and missing data.

tksheet has several basic built-in formatters and provides functionality for creating your own custom formats as well.

A demonstration of all the built-in and custom formatters can be found here.
Creation and deletion of data formatting rules
Creating a data format rule

Span objects (more information here) can be used to format data for cells, rows, columns and the entire sheet.

You can use either of the following methods:

    Using a span method e.g. span.format() more information here.
    Using a sheet method e.g. sheet.format(Span)

Or if you need user inserted row/columns in the middle of areas with data formatting to also be formatted you can use named spans, more information here.

Whether data is formatted for cells, rows or columns depends on the kind of span.

format(
    *key: CreateSpanTypes,
    formatter_options: dict = {},
    formatter_class: object = None,
    redraw: bool = True,
    **kwargs,
) -> Span

Notes:

    When applying multiple overlapping formats with e.g. a formatted cell which overlaps a formatted row, the priority is as follows:
        Cell formats first.
        Row formats second.
        Column formats third.
    Data formatting will effectively override validate_input = True on cells with dropdown boxes.
    When getting data take careful note of the get_displayed options, as these are the difference between getting the actual formatted data and what is simply displayed on the table GUI.

Parameters:

    key (CreateSpanTypes) either a span or a type which can create a span. See here for more information on the types that can create a span.
    formatter_options (dict) a dictionary of keyword options/arguements to pass to the formatter, see here for information on what argument to use.
    formatter_class (class) in case you want to use a custom class to store functions and information as opposed to using the built-in methods.
    **kwargs any additional keyword options/arguements to pass to the formatter.

Deleting a data format rule

If the data format rule was created by a named span then the named span must be deleted, more information here.

Otherwise you can use either of the following methods to delete/remove data formatting rules:

    Using a span method e.g. span.del_format() more information here.
    Using a sheet method e.g. sheet.del_format(Span) details below:

del_format(
    *key: CreateSpanTypes,
    clear_values: bool = False,
    redraw: bool = True,
) -> Span

    key (CreateSpanTypes) either a span or a type which can create a span. See here for more information on the types that can create a span.
    clear_values (bool) if true, all the cells covered by the span will have their values cleared.

Delete all formatting

del_all_formatting(clear_values: bool = False) -> Sheet

    clear_values (bool) if true, all the sheets cell values will be cleared.

Reapply formatting to entire sheet

reapply_formatting() -> Sheet

    Useful if you have manually changed the entire sheets data using sheet.MT.data = and want to reformat the sheet using any existing formatting you have set.

Check if a cell is formatted

formatted(r: int, c: int) -> dict

    If the cell is formatted function returns a dict with all the format keyword arguments. The dict will be empty if the cell is not formatted.

Formatters

In addition to the generic formatter, tksheet provides formatters for many different data types.

A basic example showing how formatting some columns as float might be done:

import tkinter as tk

from tksheet import (
    Sheet,
    float_formatter,
)
from tksheet import (
    num2alpha,
)


class demo(tk.Tk):
    def __init__(self):
        super().__init__()
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self,
            data=[[f"{r}", f"{r}"] for r in range(5)],
            expand_sheet_if_paste_too_big=True,
            theme="dark blue",
        )
        """
        Format example
        """
        # some keyword arguments inside float_formatter()
        self.sheet.format(
            num2alpha(0), # column A
            formatter_options=float_formatter(
                decimals=1,
                nullable=True,
            ),
        )
        # some keyword arguments outside
        # of float_formatter() instead
        self.sheet.format(
            "B", # column B
            formatter_options=float_formatter(),
            decimals=3,
            nullable=False,
        )
        """
        Rest of code
        """
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.enable_bindings(
            "all",
            "ctrl_select",
            "edit_header",
            "edit_index",
        )


app = demo()
app.mainloop()

You can use any of the following formatters as an argument for the parameter formatter_options.

A full list of keyword arguments available to these formatters is shown here.
Int Formatter

The int_formatter is the basic configuration for a simple interger formatter.

int_formatter(
    datatypes: tuple[object] | object = int,
    format_function: Callable = to_int,
    to_str_function: Callable = to_str,
    invalid_value: object = "NaN",
    **kwargs,
) -> dict

Parameters:

    format_function (function) a function that takes a string and returns an int. By default, this is set to the in-built tksheet.to_int. This function will always convert float-likes to its floor, for example "5.9" will be converted to 5.
    to_str_function (function) By default, this is set to the in-built tksheet.to_str, which is a very basic function that will displace the default string representation of the value.

Example:

sheet.format_cell(0, 0, formatter_options = tksheet.int_formatter())

Float Formatter

The float_formatter is the basic configuration for a simple float formatter. It will always round float-likes to the specified number of decimal places, for example "5.999" will be converted to "6.0" if decimals = 1.

float_formatter(
    datatypes: tuple[object] | object = float,
    format_function: Callable = to_float,
    to_str_function: Callable = float_to_str,
    invalid_value: object = "NaN",
    decimals: int = 2,
    **kwargs
) -> dict

Parameters:

    format_function (function) a function that takes a string and returns a float. By default, this is set to the in-built tksheet.to_float.
    to_str_function (function) By default, this is set to the in-built tksheet.float_to_str, which will display the float to the specified number of decimal places.
    decimals (int, None) the number of decimal places to round to. Defaults to 2.

Example:

sheet.format_cell(0, 0, formatter_options = tksheet.float_formatter(decimals = None)) # A float formatter with maximum float() decimal places

Percentage Formatter

The percentage_formatter is the basic configuration for a simple percentage formatter. It will always round float-likes as a percentage to the specified number of decimal places, for example "5.999%" will be converted to "6.0%" if decimals = 1.

percentage_formatter(
    datatypes: tuple[object] | object = float,
    format_function: Callable = to_percentage,
    to_str_function: Callable = percentage_to_str,
    invalid_value: object = "NaN",
    decimals: int = 0,
    **kwargs,
) -> dict

Parameters:

    format_function (function) a function that takes a string and returns a float. By default, this is set to the in-built tksheet.to_percentage. This function will always convert percentages to their decimal equivalent, for example "5%" will be converted to 0.05.
    to_str_function (function) By default, this is set to the in-built tksheet.percentage_to_str, which will display the float as a percentage to the specified number of decimal places. For example, 0.05 will be displayed as "5.0%".
    decimals (int) the number of decimal places to round to. Defaults to 0.

Example:

sheet.format_cell(0, 0, formatter_options=tksheet.percentage_formatter(decimals=1)) # A percentage formatter with 1 decimal place

Note:

By default percentage_formatter() converts user entry 21 to 2100% and 21% to 21%. An example where 21 is converted to 21% instead is shown below:

# formats column A as percentage

# uses:
# format_function=alt_to_percentage
# to_str_function=alt_percentage_to_str
sheet.format(
    "A",
    formatter_options=tksheet.percentage_formatter(
        format_function=alt_to_percentage,
        to_str_function=alt_percentage_to_str,
    )
)

Bool Formatter

bool_formatter(
    datatypes: tuple[object] | object = bool,
    format_function: Callable = to_bool,
    to_str_function: Callable = bool_to_str,
    invalid_value: object = "NA",
    truthy: set = truthy,
    falsy: set = falsy,
    **kwargs,
) -> dict

Parameters:

    format_function (function) a function that takes a string and returns a bool. By default, this is set to the in-built tksheet.to_bool.
    to_str_function (function) By default, this is set to the in-built tksheet.bool_to_str, which will display the boolean as "True" or "False".
    truthy (set) a set of values that will be converted to True. Defaults to the in-built tksheet.truthy.
    falsy (set) a set of values that will be converted to False. Defaults to the in-built tksheet.falsy.

Example:

# A bool formatter with custom truthy and falsy values to account for aussie and kiwi slang
sheet.format_cell(0, 0, formatter_options = tksheet.bool_formatter(truthy = tksheet.truthy | {"nah yeah"}, falsy = tksheet.falsy | {"yeah nah"}))

Generic Formatter

formatter(
    datatypes: tuple[object] | object,
    format_function: Callable,
    to_str_function: Callable = to_str,
    invalid_value: object = "NaN",
    nullable: bool = True,
    pre_format_function: Callable | None = None,
    post_format_function: Callable | None = None,
    clipboard_function: Callable | None = None,
    **kwargs,
) -> dict

This is the generic formatter options interface. You can use this to create your own custom formatters. The following options are available.

Note that all these options can also be passed to the Sheet() format functions as keyword arguments and are available as attributes for all formatters.

You can also provide functions of your own creation for all the below arguments which take functions if you require.

    datatypes (list) a list of datatypes that the formatter will accept. For example, datatypes = [int, float] will accept integers and floats.
    format_function (function) a function that takes a string and returns a value of the desired datatype. For example, format_function = int will convert a string to an integer.
    to_str_function (function) a function that takes a value of the desired datatype and returns a string. This determines how the formatter displays its data on the table. For example, to_str_function = str will convert an integer to a string. Defaults to tksheet.to_str.
    invalid_value (any) the value to return if the input string is invalid. For example, invalid_value = "NA" will return "NA" if the input string is invalid.
    nullable (bool) if true, the formatter will accept None as a valid input.
    pre_format_function (function) a function that takes a input string and returns a string. This function is called before the format_function and can be used to modify the input string before it is converted to the desired datatype. This can be useful if you want to strip out unwanted characters or convert a string to a different format before converting it to the desired datatype.
    post_format_function (function) a function that takes a value which might not be of the desired datatype, e.g. None if the cell is nullable and empty and if successful returns a value of the desired datatype or if not successful returns the input value. This function is called after the format_function and can be used to modify the output value after it is converted to the desired datatype. This can be useful if you want to round a float for example.
    clipboard_function (function) a function that takes a value of the desired datatype and returns a string. This function is called when the cell value is copied to the clipboard. This can be useful if you want to convert a value to a different format before it is copied to the clipboard.
    **kwargs any additional keyword options/arguements to pass to the formatter. These keyword arguments will be passed to the format_function, to_str_function, and the clipboard_function. These can be useful if you want to specifiy any additional formatting options, such as the number of decimal places to round to.

Datetime Formatters and Designing Your Own Custom Formatters

tksheet is at the moment a dependency free library and so doesn't include a datetime parser as is.

You can however very easily make a datetime parser if you are willing to install a third-party package. Recommended are:

    dateparser
    dateutil

Both of these packages have a very comprehensive datetime parser which can be used to create a custom datetime formatter for tksheet.

Below is a simple example of how you might create a custom datetime formatter using the dateutil package.

from tksheet import *
from datetime import datetime, date
from dateutil.parser import parse

def to_local_datetime(dt, **kwargs):
    '''
    Our custom format_function, converts a string or a date to a datetime object in the local timezone.
    '''
    if isinstance(dt, datetime):
        pass # Do nothing
    elif isinstance(dt, date):
        dt = datetime(dt.year, dt.month, dt.day) # Always good to account for unexpected inputs
    else:
        try:
            dt = parser.parse(dt)
        except:
            raise ValueError(f"Could not parse {dt} as a datetime")
    if dt.tzinfo is None:
        dt = dt.replace(tzinfo = tzlocal()) # If no timezone is specified, assume local timezone
    dt = dt.astimezone(tzlocal()) # Convert to local timezone
    return dt

def datetime_to_str(dt, **kwargs):
    '''
    Our custom to_str_function, converts a datetime object to a string with a format that can be specfied in kwargs.
    '''
    return dt.strftime(kwargs['format'])

# Now we can create our custom formatter dictionary from the generic formatter interface in tksheet
datetime_formatter = formatter(datatypes = datetime,
                               format_function = to_local_datetime,
                               to_str_function = datetime_to_str,
                               invalid_value = "NaT",
                               format = "%d/%m/%Y %H:%M:%S",
                               )

# From here we can pass our datetime_formatter into sheet.format() or span.format() just like any other formatter

For those wanting even more customisation of their formatters you also have the option of creating a custom formatter class. This is a more advanced topic and is not covered here, but it's recommended to create a new class which is a subclass of the tksheet.Formatter class and overriding the methods you would like to customise. This custom class can then be passed into the format_cells() formatter_class argument. Python code: import ctypes
import csv
import json
import re
import tkinter as tk
from pathlib import Path
from tkinter import filedialog, messagebox, simpledialog, ttk
from typing import Any, Optional

from tksheet import Sheet, int_formatter, float_formatter, bool_formatter, num2alpha


def set_dpi_awareness():
    """Enable DPI awareness for high DPI settings."""
    try:
        ctypes.windll.shcore.SetProcessDpiAwareness(1)
    except Exception as e:
        print(f"Error setting DPI awareness: {e}")


def create_frame(parent: tk.Widget, width: Optional[int] = None, bg: str = "SystemButtonFace") -> tk.Frame:
    """Creates a frame with specified width and background color."""
    frame = tk.Frame(parent, width=width, bg=bg, bd=0, relief="flat")
    if width:
        frame.config(width=width)
    return frame


def custom_filter_design(
    master,
    style: ttk.Style,
    headers=None,
    filter_types=None,
    filter_font=None,
    filter_combobox_font=None,
    colors=None,
):
    """Creates a custom filter frame with all components."""
    filter_frame = ttk.Frame(master, style="Custom.TFrame")

    # Configure styles
    style.configure(
        "Custom.TFrame",
        background=colors["background"],
        relief="flat",
        borderwidth=0,
    )
    style.configure(
        "TCombobox",
        relief="flat",
        borderwidth=0,
        background=colors["background"],
        fieldbackground=colors["background"],
    )
    style.map(
        "TCombobox",
        fieldbackground=[("readonly", colors["background"])],
        background=[("readonly", colors["background"])],
    )
    style.configure(
        "TEntry",
        relief="flat",
        borderwidth=0,
        background=colors["background"],
    )
    style.configure("Line.TFrame", background=colors["filter_line"])

    # Create widgets
    header_combobox = ttk.Combobox(
        filter_frame,
        values=headers,
        width=15,
        font=filter_combobox_font,
        state="readonly",
    )
    filtertype_combobox = ttk.Combobox(
        filter_frame,
        values=filter_types,
        width=15,
        font=filter_combobox_font,
        state="readonly",
    )
    filter_entry = ttk.Entry(filter_frame, font=filter_font)
    underline = ttk.Frame(filter_frame, height=1, style="Line.TFrame")

    # Layout widgets
    header_combobox.grid(row=0, column=0, padx=(0, 30), pady=2, sticky="ew")
    filtertype_combobox.grid(row=0, column=1, padx=2, pady=2, sticky="ew")
    filter_entry.grid(
        row=1, column=0, columnspan=2, padx=2, pady=(2, 0), sticky="ew"
    )
    underline.grid(row=2, column=0, columnspan=2, sticky="ew", pady=(0, 2))

    # Prevent text selection in comboboxes
    for combobox in (header_combobox, filtertype_combobox):
        combobox.bind("<FocusIn>", lambda event: event.widget.selection_clear())

    # Configure column weights
    filter_frame.columnconfigure((0, 1), weight=1)

    # Ensure the underline doesn't resize vertically
    underline.pack_propagate(False)

    return (
        filter_frame,
        header_combobox,
        filtertype_combobox,
        filter_entry,
    )


def custom_scrollbar_design(style: ttk.Style, colors=None):
    """Configure custom scrollbar style."""
    style.configure(
        "Custom.Vertical.TScrollbar",
        troughcolor=colors["scrollbar_trough"],
        background=colors["scrollbar_background"],
        borderwidth=0,
        arrowcolor=colors["scrollbar_arrow"],
    )
    style.map(
        "Custom.Vertical.TScrollbar",
        background=[("active", colors["scrollbar_active"])],
    )
    style.layout(
        "Custom.Vertical.TScrollbar",
        [
            (
                "Vertical.Scrollbar.trough",
                {
                    "children": [
                        (
                            "Vertical.Scrollbar.thumb",
                            {"expand": "1", "sticky": "ns"},
                        )
                    ],
                    "sticky": "ns",
                },
            )
        ],
    )


class TableApp:
    """Main application controller for database table visualization."""

    def __init__(self, root: tk.Tk):
        """Initialize the TableApp with basic configuration and UI setup."""
        self._initialize_root(root)
        self._initialize_configurations()
        self._initialize_core_components()
        self._setup_ui_components()
        self._load_initial_data()

    def _initialize_root(self, root: tk.Tk):
        """Set up the root window properties."""
        self.root = root
        self.root.title("Database")
        self.root.geometry("1920x1080")
        self.root.minsize(1435, 910)

    def _initialize_configurations(self):
        """Initialize all configurations including dimensions, fonts, colors, and style."""
        self._set_dimensions()
        self._set_fonts()
        self._set_colors()
        self._set_style()

    def _set_dimensions(self):
        """Define application dimensions."""
        self.dimensions = {
            "sidebar_width": 400,
            "spacer_frame_width": 100,
            "header_height": 190,
            "separator_height": 1
        }

    def _set_fonts(self):
        """Define application fonts."""
        self.fonts = {
            "text": ("Literata", 12),
            "tksheet": ("Literata", 12, "normal"),
            "header_info": ("Literata", 10),
            "filter": ("Literata", 12),
            "filter_combobox": ("Literata", 8)
        }

    def _set_colors(self):
        """Define application colors."""
        self.colors = {
            "text": "#333333",
            "background": "#FFFFFF",
            "sidebar": "#F7F8FC",
            "button": "#F7F8FC",
            "button_selected": "#EAEDEF",
            "separator": "#333333",
            "header_info": "#999999",
            "scrollbar_trough": "#F7F8FC",
            "scrollbar_background": "#c0c1c4",
            "scrollbar_arrow": "#333333",
            "scrollbar_active": "#878789",
            "filter_line": "#00bcf0",
            "checkbox": "#00bcf0",
        }

    def _set_style(self):
        """Initialize and configure the application style."""
        self.style = ttk.Style()
        self.style.theme_use("default")

    def _initialize_core_components(self):
        """Initialize core application components and state variables."""
        self.current_table_name: str | None = None
        self.current_sheet: Sheet | None = None
        self.displayed_row_indices: list[int] = []
        
        self.data_manager = DataManager(self)
        self.table_operations = TableOperationsManager(self)
        self.table_display = TableDisplayManager(self)
        self.table_io = TableIOManager(self)

    def _setup_ui_components(self):
        """Set up all UI components and their layout."""
        self._configure_grid()
        self._create_main_components()
        self._create_auxiliary_frames()

    def _configure_grid(self):
        """Configure the root window grid layout."""
        self.root.grid_rowconfigure(0, weight=0)
        self.root.grid_rowconfigure(1, weight=1)
        self.root.grid_columnconfigure(0, weight=0)
        self.root.grid_columnconfigure(1, weight=0, 
                                     minsize=self.dimensions["spacer_frame_width"])
        self.root.grid_columnconfigure(2, weight=3)

    def _create_main_components(self):
        """Create main UI components."""
        self.sidebar = Sidebar(self.root, self)
        self.header = Header(self.root, self)
        self.filter_controls = FilterControls(self.header.lower_header_frame, self)

    def _create_auxiliary_frames(self):
        """Create auxiliary frames for layout purposes."""
        self._create_spacer_frame()
        self._create_table_frame()

    def _create_spacer_frame(self):
        """Create and configure the spacer frame."""
        spacer = create_frame(
            parent=self.root,
            width=self.dimensions["spacer_frame_width"],
            bg=self.colors["background"]
        )
        spacer.grid(row=1, column=1, sticky="nsew")
        spacer.grid_propagate(False)

    def _create_table_frame(self):
        """Create and configure the main table frame."""
        self.table_frame = create_frame(
            parent=self.root,
            width=None,
            bg=self.colors["background"]
        )
        self.table_frame.grid(row=1, column=2, sticky="nsew")
        self.table_frame.grid_rowconfigure(0, weight=1)
        self.table_frame.grid_columnconfigure(0, weight=1)

    def _load_initial_data(self):
        """Load initial application data."""
        self.data_manager.load_tables()

    # Public methods
    def load_selected_table(self, table_name: str):
        """Load and display the selected table."""
        self.table_display.load_selected_table(table_name)

    def refresh_table(self):
        """Refresh the currently displayed table."""
        if self.current_table_name:
            self.load_selected_table(self.current_table_name)

    def apply_filter(self):
        """Apply the current filter to the table."""
        self.filter_controls.apply_filter()


class DataManager:
    """Handles data persistence, loading, and saving."""

    def __init__(self, app: TableApp):
        self.app = app
        self.save_file_path = Path(__file__).parent / "Data.json"
        self.tables_data: dict[str, dict] = {}
        self.headers: dict[str, list[str]] = {}

    def load_tables(self):
        """Load tables from file."""
        if not self.save_file_path.exists():
            return
        try:
            with self.save_file_path.open("r", encoding="utf-8") as file:
                all_data = json.load(file)
        except Exception as e:
            messagebox.showerror("Load Error", f"Failed to load tables: {e}")
            return
        for table_name, data in all_data.items():
            if isinstance(data, dict):
                headers = data.get('headers', [])
                table_data = data.get('data', [])
                data_types = data.get('data_types', ['string'] * len(headers))
                # Convert boolean columns
                for row in table_data:
                    for col_idx, dtype in enumerate(data_types):
                        if dtype == 'boolean':
                            cell_value = row[col_idx]
                            row[col_idx] = True if str(cell_value).lower() in ['true', '1', 'yes'] else False
                self.headers[table_name] = headers
                self.tables_data[table_name] = {
                    "headers": headers,
                    "data": table_data,
                    "data_types": data_types
                }
            elif isinstance(data, list):
                if not data:
                    continue
                headers, table_data = data[0], data[1:]
                data_types = ['string'] * len(headers)  # Default to 'string' data type
                self.headers[table_name] = headers
                self.tables_data[table_name] = {"headers": headers, "data": table_data, "data_types": data_types}
            else:
                continue
            # Inform Sidebar to create table label
            self.app.sidebar.create_table_label(table_name)
        self.app.sidebar.sort_table_labels()

    def save_data(self):
        """Save all tables data."""
        all_data = {}
        for table_name, table_info in self.tables_data.items():
            all_data[table_name] = {
                "headers": table_info["headers"],
                "data": table_info["data"],
                "data_types": table_info.get("data_types", ['string'] * len(table_info['headers']))
            }
        try:
            with self.save_file_path.open("w", encoding="utf-8") as file:
                json.dump(all_data, file, indent=4)
        except Exception as e:
            print(f"Error saving data: {e}")


class TableOperationsManager:
    """Handles creating, deleting, and renaming tables."""

    def __init__(self, app: TableApp):
        self.app = app

    def create_new_table(self):
        """Create a new table."""
        existing_numbers = [
            int(name.split(" ")[1])
            for name in self.app.data_manager.tables_data
            if name.startswith("Table ") and name.split(" ")[1].isdigit()
        ]
        next_number = max(existing_numbers, default=0) + 1
        table_name = f"Table {next_number}"
        self.add_table(table_name)
        self.app.table_display.load_selected_table(table_name)

    def add_table(
        self,
        table_name: str,
        data: list[list[str]] | None = None,
        headers: Optional[list[str]] = None,
        data_types: Optional[list[str]] = None,
    ):
        """Add a new table."""
        if headers:
            self.app.data_manager.headers[table_name] = headers
        else:
            default_headers = [
                "Column 1",
                "Column 2",
                "Column 3",
                "Column 4",
                "Column 5",
            ]
            self.app.data_manager.headers[table_name] = default_headers
        if not data_types:
            data_types = ['string'] * len(self.app.data_manager.headers[table_name])

        self.app.data_manager.tables_data[table_name] = {
            "headers": self.app.data_manager.headers[table_name],
            "data": data
            or [
                ["" for _ in range(len(self.app.data_manager.headers[table_name]))]
                for _ in range(10)
            ],
            "data_types": data_types,
        }
        self.app.sidebar.create_table_label(table_name)
        self.app.data_manager.save_data()

    def delete_table(self):
        """Delete selected table."""
        if (
            not self.app.current_table_name
            or self.app.current_table_name not in self.app.data_manager.tables_data
        ):
            messagebox.showwarning(
                "No Table Selected", "Please select a valid table to delete."
            )
            return
        if messagebox.askyesno(
            "Delete Table",
            f"Are you sure you want to delete the table '{self.app.current_table_name}'?",
        ):
            self.remove_table(self.app.current_table_name)

    def remove_table(self, table_name: str):
        """Remove a table."""
        if self.app.current_table_name == table_name and self.app.current_sheet:
            self.app.current_sheet.destroy()
            self.app.current_sheet = None
        label = self.app.sidebar.table_labels.get(table_name)
        if label:
            label.destroy()
        if table_name in self.app.data_manager.tables_data:
            del self.app.data_manager.tables_data[table_name]
        if table_name in self.app.data_manager.headers:
            del self.app.data_manager.headers[table_name]
        if table_name in self.app.sidebar.table_labels:
            del self.app.sidebar.table_labels[table_name]
        self.app.current_table_name = None
        self.app.sidebar.selected_table_label = None
        self.app.data_manager.save_data()
        self.app.sidebar.sort_table_labels()

    def rename_headers(self):
        """Rename table headers."""
        if not self.app.current_table_name:
            messagebox.showwarning(
                "No Table Selected", "Please select a table to rename headers."
            )
            return
        current_headers = self.app.data_manager.headers[self.app.current_table_name]
        new_headers = simpledialog.askstring(
            "Rename Headers",
            "Enter new headers (comma-separated):",
            initialvalue=", ".join(current_headers),
        )
        if new_headers:
            header_list = [header.strip() for header in new_headers.split(",")]
            if not header_list:
                messagebox.showerror("Invalid Headers", "Header list cannot be empty.")
                return
            self.app.data_manager.headers[self.app.current_table_name] = header_list
            self.app.data_manager.tables_data[self.app.current_table_name][
                "headers"
            ] = header_list
            if self.app.current_sheet:
                try:
                    self.app.current_sheet.headers(header_list)
                except tk.TclError as e:
                    messagebox.showerror("Error", f"Failed to rename headers: {e}")

            self.app.filter_controls.filter_header_var.set("All")
            self.app.filter_controls.filter_header_dropdown["values"] = [
                "All"
            ] + header_list
            self.app.apply_filter()

            self.app.data_manager.save_data()

    def begin_rename(self, table_name: str):
        """Start renaming a table."""
        if table_name not in self.app.sidebar.table_labels:
            messagebox.showerror("Error", f"Table '{table_name}' not found.")
            return
        label = self.app.sidebar.table_labels[table_name]
        self.cancel_rename()
        self.app.sidebar.rename_entry = tk.Entry(
            self.app.sidebar.table_labels_frame,
            bg=label.cget("bg"),
            fg=self.app.colors["text"],
            relief="flat",
        )
        self.app.sidebar.rename_entry.insert(0, table_name)
        self.app.sidebar.rename_entry.place(
            x=label.winfo_x(),
            y=label.winfo_y(),
            width=label.winfo_width(),
            height=label.winfo_height(),
        )
        self.app.sidebar.rename_entry.focus()
        self.app.sidebar.rename_entry.select_range(0, tk.END)
        self.app.sidebar.rename_entry.bind(
            "<Return>", lambda event: self.end_rename(table_name)
        )
        self.app.sidebar.rename_entry.bind("<FocusOut>", lambda event: self.cancel_rename())

    def end_rename(self, old_name: str):
        """Finalize table renaming."""
        new_name = self.app.sidebar.rename_entry.get().strip()
        if not new_name:
            messagebox.showerror("Invalid Name", "Table name cannot be empty.")
            self.app.sidebar.rename_entry.focus_set()
            return
        if new_name in self.app.data_manager.tables_data and new_name != old_name:
            messagebox.showerror(
                "Duplicate Name", f"A table named '{new_name}' already exists."
            )
            self.app.sidebar.rename_entry.focus_set()
            return
        if new_name != old_name:
            self.update_table_name(old_name, new_name)
            self.app.table_display.load_selected_table(new_name)
        self.cancel_rename()

    def update_table_name(self, old_name: str, new_name: str):
        """Update table name and resources."""
        self.app.data_manager.tables_data[new_name] = self.app.data_manager.tables_data.pop(
            old_name
        )
        self.app.data_manager.headers[new_name] = self.app.data_manager.headers.pop(
            old_name
        )
        label = self.app.sidebar.table_labels.pop(old_name)
        label.config(text=new_name)
        label.bind(
            "<Button-1>", lambda event: self.app.table_display.load_selected_table(new_name)
        )
        label.bind(
            "<Double-1>", lambda event: self.begin_rename(new_name)
        )
        self.app.sidebar.table_labels[new_name] = label
        if self.app.sidebar.selected_table_label == label:
            try:
                label.config(bg=self.app.colors["button_selected"])
            except tk.TclError:
                pass
        self.app.data_manager.save_data()
        self.app.sidebar.sort_table_labels()

    def cancel_rename(self):
        """Cancel rename operation."""
        if self.app.sidebar.rename_entry:
            self.app.sidebar.rename_entry.destroy()
            self.app.sidebar.rename_entry = None

    def change_column_data_types(self):
        """Change data types for table columns."""
        if not self.app.current_table_name:
            messagebox.showwarning(
                "No Table Selected", "Please select a table to change column data types."
            )
            return

        headers = self.app.data_manager.headers[self.app.current_table_name]
        data_types = self.app.data_manager.tables_data[self.app.current_table_name].get("data_types")

        if not data_types:
            data_types = ['string'] * len(headers)
            self.app.data_manager.tables_data[self.app.current_table_name]["data_types"] = data_types

        # Open dialog to change data types
        dialog = ColumnDataTypesDialog(self.app.root, headers, data_types)

        if dialog.result:
            # Update data types
            new_data_types = dialog.result
            self.app.data_manager.tables_data[self.app.current_table_name]["data_types"] = new_data_types

            # Save data
            self.app.data_manager.save_data()

            # Reformat sheet
            self.app.table_display.apply_column_data_types(new_data_types)


class ColumnDataTypesDialog(tk.Toplevel):
    def __init__(self, parent, headers, data_types):
        super().__init__(parent)
        self.title("Change Column Data Types")
        self.headers = headers
        self.data_types = data_types.copy()
        self.result = None  # This will store the new data_types if OK is pressed

        self.vars = []
        for i, (header, dtype) in enumerate(zip(headers, data_types)):
            tk.Label(self, text=header).grid(row=i, column=0, padx=5, pady=5, sticky='w')
            var = tk.StringVar(value=dtype)
            options = ["string", "int", "float", "boolean"]
            option_menu = ttk.OptionMenu(self, var, dtype, *options)
            option_menu.grid(row=i, column=1, padx=5, pady=5, sticky='w')
            self.vars.append(var)

        button_frame = tk.Frame(self)
        button_frame.grid(row=len(headers), column=0, columnspan=2, pady=10)

        ok_button = tk.Button(button_frame, text="OK", command=self.on_ok)
        ok_button.pack(side='left', padx=5)

        cancel_button = tk.Button(button_frame, text="Cancel", command=self.on_cancel)
        cancel_button.pack(side='left', padx=5)

        self.transient(parent)
        self.grab_set()
        self.wait_window(self)

    def on_ok(self):
        self.result = [var.get() for var in self.vars]
        self.destroy()

    def on_cancel(self):
        self.destroy()


class TableDisplayManager:
    """Handles loading and displaying tables and related events."""

    def __init__(self, app: TableApp):
        self.app = app

    def load_selected_table(self, table_name: str):
        """Display selected table."""
        if self.app.current_sheet:
            self.app.current_sheet.pack_forget()
            self.app.current_sheet.destroy()
            self.app.current_sheet = None
        if self.app.sidebar.selected_table_label:
            try:
                self.app.sidebar.selected_table_label.config(
                    bg=self.app.colors["button"]
                )
            except tk.TclError:
                self.app.sidebar.selected_table_label = None

        if table_name not in self.app.data_manager.tables_data:
            messagebox.showerror("Error", f"Table '{table_name}' does not exist.")
            self.app.current_table_name = None
            self.app.sidebar.selected_table_label = None
            return

        table_info = self.app.data_manager.tables_data[table_name]
        headers = table_info["headers"]
        data = table_info["data"]
        try:
            sheet = Sheet(
                self.app.table_frame,
                data=data,
                show_table=True,
                headers=headers,
                font=self.app.fonts["tksheet"],
                header_font=self.app.fonts["tksheet"],
                auto_resize_columns=True,
            )
            sheet.set_options(
                table_fg=self.app.colors["text"], header_fg=self.app.colors["text"]
            )
            sheet.enable_bindings()
            sheet.extra_bindings(
                [
                    ("end_edit_cell", self.on_cell_edit),
                    ("end_delete_columns", self.on_columns_deleted),
                    ("end_insert_column", self.on_columns_inserted),
                    ("cell_select", self.on_cell_select),  # For checkbox focus
                ]
            )
            sheet.pack(expand=True, fill="both")
            self.app.current_sheet = sheet
            self.app.current_table_name = table_name

            # Apply column data types
            data_types = self.app.data_manager.tables_data[table_name].get("data_types")
            if data_types:
                self.apply_column_data_types(data_types)

            if self.app.header.table_name_label:
                self.app.header.table_name_label.config(text=self.app.current_table_name)
            else:
                self.app.header.table_name_label = tk.Label(
                    self.app.root,
                    text=self.app.current_table_name,
                    bg=self.app.colors["background"],
                    fg=self.app.colors["text"],
                    font=self.app.fonts["header_info"],
                )
                self.app.header.table_name_label.grid(
                    row=0, column=0, padx=(60, 10), pady=(5, 0), sticky="nw"
                )

            label = self.app.sidebar.table_labels.get(table_name)
            if label:
                self.app.sidebar.selected_table_label = label
                try:
                    self.app.sidebar.selected_table_label.config(
                        bg=self.app.colors["button_selected"]
                    )
                except tk.TclError:
                    self.app.sidebar.selected_table_label = None
                    messagebox.showerror(
                        "Error", f"Failed to update the label for '{table_name}'."
                    )
            else:
                self.app.sidebar.selected_table_label = None
                messagebox.showerror("Error", f"Label for '{table_name}' not found.")

            self.app.filter_controls.filter_header_var.set("All")
            self.app.filter_controls.filter_header_dropdown["values"] = ["All"] + headers

            self.app.filter_controls.filter_option_var.set("contains")
            self.app.filter_controls.update_filter_text_state()

            self.app.apply_filter()
        except Exception as e:
            messagebox.showerror(
                "Sheet Error", f"Failed to load table '{table_name}': {e}"
            )
            self.app.current_table_name = None
            self.app.sidebar.selected_table_label = None

    def apply_column_data_types(self, data_types):
        """Apply data formatting based on column data types."""
        if not self.app.current_sheet:
            return
    
        sheet = self.app.current_sheet
    
        # Remove all existing format rules
        sheet.del_all_formatting()
    
        # First, delete all existing checkboxes
        checkboxes = sheet.get_checkboxes()
        if checkboxes:
            for key in list(checkboxes.keys()):
                sheet.del_checkbox(key)
    
        for col_idx, dtype in enumerate(data_types):
            column_letter = num2alpha(col_idx)
            if dtype == 'int':
                sheet.format(
                    column_letter,
                    formatter=int_formatter()
                )
            elif dtype == 'float':
                sheet.format(
                    column_letter,
                    formatter=float_formatter()
                )
            elif dtype == 'boolean':
                # Create check boxes for this column
                sheet.checkbox(
                    column_letter,
                    checked=None,  # Will initialize based on cell data
                    check_function=self.on_checkbox_click
                )
                # Set the checkbox text color using highlight_cells
                # Get the number of rows
                num_rows = len(sheet.get_sheet_data())  # Exclude header
                # Create a list of cell coordinates for the column
                cells_to_highlight = [(row, col_idx) for row in range(num_rows)]
                # Highlight the cells in this column excluding the header
                sheet.highlight_cells(
                    cells=cells_to_highlight,
                    fg=self.app.colors["checkbox"]
                )
            elif dtype == 'string':
                # No need to format strings; tksheet default is string
                continue
            else:
                # Unknown data type; skip
                continue
    
        # Reapply formatting to sheet data
        sheet.reapply_formatting()

    def on_checkbox_click(self, event):
        """Handle checkbox click events and update data."""
        if not self.app.current_table_name or not self.app.current_sheet:
            return

        row = event['row']
        col = event['column']
        new_value = event['value']

        # Update the underlying data
        data_row_index = self.app.displayed_row_indices[row]
        self.app.data_manager.tables_data[self.app.current_table_name]["data"][data_row_index][col] = new_value

        # Save the updated data
        self.app.data_manager.save_data()

    def on_cell_edit(self, event=None):
        """Handle cell edits."""
        if self.app.current_table_name and self.app.current_sheet:
            sheet_data = self.app.current_sheet.get_sheet_data()
            data_types = self.app.data_manager.tables_data[self.app.current_table_name].get("data_types", [])
            for idx, row in enumerate(sheet_data):
                if idx < len(self.app.displayed_row_indices):
                    data_idx = self.app.displayed_row_indices[idx]
                    # Convert cell values based on data types
                    converted_row = []
                    for col_idx, cell in enumerate(row):
                        dtype = data_types[col_idx] if col_idx < len(data_types) else 'string'
                        if dtype == 'int':
                            try:
                                converted_row.append(int(cell))
                            except ValueError:
                                converted_row.append(0)
                        elif dtype == 'float':
                            try:
                                converted_row.append(float(cell))
                            except ValueError:
                                converted_row.append(0.0)
                        elif dtype == 'boolean':
                            if str(cell).lower() in ['true', '1', 'yes']:
                                converted_row.append(True)
                            else:
                                converted_row.append(False)
                        else:
                            converted_row.append(cell)
                    self.app.data_manager.tables_data[self.app.current_table_name]["data"][data_idx] = converted_row
                else:
                    # Handle new rows by appending to the underlying data
                    self.app.data_manager.tables_data[self.app.current_table_name]["data"].append(row)
            self.app.data_manager.save_data()

    def on_columns_deleted(self, event):
        """Handle column deletion and update filter headers."""
        if not self.app.current_table_name or not self.app.current_sheet:
            return
        new_headers = self.app.current_sheet.headers()
        self.app.data_manager.headers[self.app.current_table_name] = new_headers

        # Update data_types list
        deleted_columns = event.get('columns', [])

        data_types = self.app.data_manager.tables_data[self.app.current_table_name].get("data_types", [])
        # Remove data_types at deleted indices
        for col_idx in sorted(deleted_columns, reverse=True):
            if col_idx < len(data_types):
                del data_types[col_idx]

        self.app.data_manager.tables_data[self.app.current_table_name]["data_types"] = data_types

        # Also save data
        self.app.data_manager.save_data()

        self.app.filter_controls.filter_header_dropdown["values"] = ["All"] + new_headers
        self.app.filter_controls.filter_header_var.set("All")
        self.app.filter_controls.filter_option_var.set("contains")
        self.app.filter_controls.filter_text_var.set("")
        self.app.filter_controls.filter_text_entry.config(state="normal")
        self.app.apply_filter()

    def on_columns_inserted(self, event):
        """Handle columns inserted and update data types."""
        if not self.app.current_table_name or not self.app.current_sheet:
            return

        inserted_columns = event.get('columns', [])
        data_types = self.app.data_manager.tables_data[self.app.current_table_name].get("data_types", [])
        for col_idx in inserted_columns:
            data_types.insert(col_idx, 'string')  # Default data type is string

        self.app.data_manager.tables_data[self.app.current_table_name]["data_types"] = data_types

        # Also save data
        self.app.data_manager.save_data()

    def on_cell_select(self, event):
        """Handle cell selection to give focus to checkboxes."""
        row = event.row
        col = event.column
        checkboxes = self.app.current_sheet.get_checkboxes()
        if (row, col) not in checkboxes:
            return
        # Focus on the checkbox
        self.app.current_sheet.focus_set()


class TableIOManager:
    """Handles importing and exporting tables."""

    def __init__(self, app: TableApp):
        self.app = app

    def import_file(self):
        """Import table from file."""
        file_path = filedialog.askopenfilename(
            title="Import File",
            filetypes=[
                ("All Supported Files", "*.json *.csv"),
                ("JSON files", "*.json"),
                ("CSV files", "*.csv"),
            ],
        )
        if not file_path:
            return
        try:
            if file_path.endswith(".csv"):
                with open(file_path, newline="", encoding="utf-8") as csvfile:
                    data = list(csv.reader(csvfile))
            elif file_path.endswith(".json"):
                with open(file_path, "r", encoding="utf-8") as jsonfile:
                    data = json.load(jsonfile)
            else:
                raise ValueError("Unsupported file format.")
            if (
                not data
                or not isinstance(data, list)
                or not all(isinstance(row, list) for row in data)
            ):
                raise ValueError("Invalid data format in the file.")
            headers, table_data = data[0], data[1:]
            data_types = ['string'] * len(headers)  # Default data types to 'string' for imports
            self.add_table_from_data(file_path, headers, table_data, data_types)
        except Exception as e:
            messagebox.showerror("File Import Error", str(e))

    def add_table_from_data(
        self, file_path: str, headers: list[str], table_data: list[list[str]], data_types: list[str]
    ):
        """Add a table from file data."""
        table_name = Path(file_path).stem
        table_name = self.ensure_unique_table_name(table_name)
        self.app.table_operations.add_table(table_name, data=table_data, headers=headers, data_types=data_types)
        self.app.table_display.load_selected_table(table_name)
        self.app.data_manager.save_data()

    def ensure_unique_table_name(self, table_name: str) -> str:
        """Ensure table name is unique."""
        original_name = table_name
        counter = 1
        while table_name in self.app.data_manager.tables_data:
            table_name = f"{original_name} (New {counter})"
            counter += 1
        return table_name

    def export_table(self):
        """Export current table to file."""
        if (
            not self.app.current_table_name
            or self.app.current_table_name not in self.app.data_manager.tables_data
        ):
            messagebox.showwarning(
                "No Table Selected", "Please select a table to export."
            )
            return
        file_path = filedialog.asksaveasfilename(
            title="Export File",
            defaultextension=".json",
            filetypes=[("JSON files", "*.json"), ("CSV files", "*.csv")],
            initialfile=self.app.current_table_name,
        )
        if not file_path:
            return
        try:
            data = [
                self.app.data_manager.headers[self.app.current_table_name]
            ] + self.app.data_manager.tables_data[self.app.current_table_name]["data"]
            if file_path.endswith(".csv"):
                self.save_table_data(file_path, data, "csv")
            elif file_path.endswith(".json"):
                with open(file_path, "w", encoding="utf-8") as file:
                    json.dump(data, file, indent=4)
            else:
                raise ValueError("Unsupported file format.")
            self.app.data_manager.save_data()
            messagebox.showinfo(
                "Export Successful",
                f"Table '{self.app.current_table_name}' has been exported successfully.",
            )
        except Exception as e:
            messagebox.showerror(
                "Export Failed", f"An error occurred during export: {e}"
            )

    def save_table_data(self, file_path: str, data: list[list[Any]], format: str):
        """Save table data in specified format."""
        if format == "csv":
            # Convert all data to strings
            data_as_strings = []
            headers = data[0]
            data_types = self.app.data_manager.tables_data[self.app.current_table_name].get("data_types", [])
            for row in data:
                row_as_strings = []
                for idx, cell in enumerate(row):
                    dtype = data_types[idx] if idx < len(data_types) else 'string'
                    if dtype == 'boolean':
                        row_as_strings.append('TRUE' if cell else 'FALSE')
                    else:
                        row_as_strings.append(str(cell))
                data_as_strings.append(row_as_strings)
            with open(file_path, "w", newline="", encoding="utf-8") as file:
                csv.writer(file).writerows(data_as_strings)
        elif format == "json":
            with open(file_path, "w", encoding="utf-8") as file:
                json.dump(data, file, indent=4)
        else:
            raise ValueError("Unsupported file format.")


class Sidebar:
    """Handles all sidebar-related UI and interactions."""

    def __init__(self, root: tk.Tk, app: TableApp):
        self.root = root
        self.app = app
        self.colors = app.colors
        self.fonts = app.fonts
        self.dimensions = app.dimensions
        self.style = app.style

        # Initialize variables
        self.table_labels_frame: Optional[tk.Frame] = None
        self.table_labels: dict[str, tk.Label] = {}
        self.selected_table_label: Optional[tk.Label] = None
        self.rename_entry: Optional[tk.Entry] = None

        self.create_sidebar()

    def create_sidebar(self):
        """Create the sidebar UI components."""
        self.sidebar_frame = create_frame(
            parent=self.root,
            width=self.dimensions["sidebar_width"],
            bg=self.colors["sidebar"],
        )
        self.sidebar_frame.grid(row=0, column=0, rowspan=2, sticky="ns")
        self.sidebar_frame.grid_propagate(False)
        self.sidebar_frame.columnconfigure(0, weight=1)
        self.sidebar_frame.rowconfigure(0, weight=1)

        functional_sidebar = create_frame(
            parent=self.sidebar_frame,
            width=self.dimensions["sidebar_width"],
            bg=self.colors["sidebar"],
        )
        functional_sidebar.grid(row=0, column=0, sticky="nsew")
        functional_sidebar.columnconfigure(0, weight=1)
        functional_sidebar.rowconfigure(0, weight=0)
        functional_sidebar.rowconfigure(1, weight=1)
        functional_sidebar.rowconfigure(2, weight=0)

        self._add_normal_buttons(functional_sidebar)
        self._add_scrollable_canvas(functional_sidebar)
        self._add_delete_table_section(functional_sidebar)

    def _add_normal_buttons(self, sidebar: tk.Frame):
        """Add buttons to the sidebar."""
        normal_buttons_frame = create_frame(
            parent=sidebar,
            width=self.dimensions["sidebar_width"],
            bg=self.colors["sidebar"],
        )
        normal_buttons_frame.grid(row=0, column=0, sticky="ew", padx=10, pady=(10, 0))
        normal_buttons_frame.columnconfigure(0, weight=1)

        create_table_label = self._create_clickable_label(
            parent=normal_buttons_frame,
            text="Create New Table",
            command=self.app.table_operations.create_new_table,
            anchor="center",
        )
        create_table_label.grid(row=0, column=0, pady=10, sticky="ew")
        self.add_custom_hover(create_table_label)

        separator = tk.Frame(
            normal_buttons_frame,
            height=self.dimensions["separator_height"],
            bg=self.colors["separator"],
        )
        separator.grid(row=1, column=0, sticky="ew", pady=5)

    def _on_canvas_configure(self, event):
        """Update the table_labels_frame width to match the canvas width."""
        self.canvas.itemconfig(self.table_labels_window, width=event.width)

    def _add_scrollable_canvas(self, sidebar: tk.Frame):
        """Add scrollable canvas for table labels within a single frame."""
        scrollable_container = create_frame(
            parent=sidebar,
            width=self.dimensions["sidebar_width"],
            bg=self.colors["sidebar"],
        )
        scrollable_container.grid(row=1, column=0, sticky="nsew", padx=10, pady=10)
        scrollable_container.columnconfigure(0, weight=1)
        scrollable_container.rowconfigure(0, weight=1)

        self.canvas = tk.Canvas(
            scrollable_container,
            bg=self.colors["sidebar"],
            highlightthickness=0,
        )
        self.canvas.grid(row=0, column=0, sticky="nsew")

        custom_scrollbar_design(self.style, self.colors)

        self.scrollbar = ttk.Scrollbar(
            scrollable_container,
            orient="vertical",
            command=self.canvas.yview,
            style="Custom.Vertical.TScrollbar",
        )
        self.scrollbar.grid(row=0, column=1, sticky="ns")

        self.canvas.configure(yscrollcommand=self.scrollbar.set)

        self.table_labels_frame = tk.Frame(self.canvas, bg=self.colors["sidebar"])

        # Capture the window item ID
        self.table_labels_window = self.canvas.create_window(
            (0, 0),
            window=self.table_labels_frame,
            anchor="nw",
            width=self.dimensions["sidebar_width"],
        )

        self.canvas.bind("<MouseWheel>", self._on_mouse_wheel)
        self.table_labels_frame.bind("<MouseWheel>", self._on_mouse_wheel)

        self.table_labels_frame.bind(
            "<Configure>",
            lambda event: (
                self.canvas.configure(scrollregion=self.canvas.bbox("all")),
                self._update_scrollbar(
                    scrollable_container, self.canvas, self.scrollbar
                ),
            ),
        )
        scrollable_container.bind(
            "<Configure>",
            lambda event: self._update_scrollbar(
                scrollable_container, self.canvas, self.scrollbar
            ),
        )

        # Bind the canvas resize to update the table_labels_frame width
        self.canvas.bind("<Configure>", self._on_canvas_configure)

    def _on_mouse_wheel(self, event):
        """Handle mouse wheel scrolling only if scrollbar is visible."""
        if self.scrollbar.winfo_viewable():
            self.canvas.yview_scroll(int(-1 * (event.delta / 120)), "units")

    def _update_scrollbar(self, scrollbar_frame, canvas, scrollbar):
        """Update scrollbar visibility based on content size."""
        canvas.update_idletasks()
        canvas_height = scrollbar_frame.winfo_height()
        content_height = self.table_labels_frame.winfo_reqheight()

        if content_height > canvas_height:
            scrollbar.grid()
            canvas.configure(yscrollcommand=scrollbar.set)
            canvas.bind(
                "<MouseWheel>",
                lambda event: canvas.yview_scroll(int(-1 * (event.delta / 120)), "units"),
            )
        else:
            scrollbar.grid_remove()
            canvas.configure(yscrollcommand=lambda *args: None)
            canvas.unbind("<MouseWheel>")

    def _add_delete_table_section(self, sidebar: tk.Frame):
        """Add delete table option to the sidebar."""
        delete_table_frame = create_frame(
            parent=sidebar,
            width=self.dimensions["sidebar_width"],
            bg=self.colors["sidebar"],
        )
        delete_table_frame.grid(
            row=2, column=0, sticky="ew", padx=10, pady=(0, 10)
        )
        delete_table_frame.columnconfigure(0, weight=1)
        delete_table_frame.rowconfigure(1, weight=1)

        separator = tk.Frame(
            delete_table_frame,
            height=self.dimensions["separator_height"],
            bg=self.colors["separator"],
        )
        separator.grid(row=0, column=0, sticky="ew", pady=5)

        delete_table_label = self._create_clickable_label(
            parent=delete_table_frame,
            text="🗑 Delete Selected Table",
            command=self.app.table_operations.delete_table,
            anchor="center",
        )
        delete_table_label.grid(row=1, column=0, pady=10, sticky="ew")
        self.add_custom_hover(delete_table_label)

    def _create_clickable_label(
        self,
        parent: tk.Widget,
        text: str,
        command,
        allow_rename: bool = False,
        anchor: str = "w",
    ) -> tk.Label:
        """Create a clickable label with optional rename functionality."""
        cursor = "arrow" if allow_rename else "hand2"
        label = tk.Label(
            parent,
            text=text,
            bg=self.colors["button"],
            fg=self.colors["text"],
            cursor=cursor,
            anchor=anchor,
            font=self.fonts["text"],
        )
        label.bind("<Button-1>", lambda _: command())

        if allow_rename:
            label.bind("<Double-1>", lambda _: self.app.table_operations.begin_rename(text))

        label.bind("<MouseWheel>", self._on_mouse_wheel)

        return label

    def add_custom_hover(self, widget: tk.Widget):
        """Add custom hover effects to a widget, excluding the selected table label."""
        def on_enter(e):
            if widget != self.selected_table_label:
                widget.config(bg=self.colors["button_selected"])
        def on_leave(e):
            if widget != self.selected_table_label:
                widget.config(bg=self.colors["button"])
        widget.bind("<Enter>", on_enter)
        widget.bind("<Leave>", on_leave)

    def create_table_label(self, table_name: str):
        """Create label for table."""
        label = self._create_clickable_label(
            self.table_labels_frame,
            table_name,
            lambda: self.app.load_selected_table(table_name),
            allow_rename=True,
        )
        label.pack(fill="x", padx=0, pady=5, expand=True)
        self.table_labels[table_name] = label
        self.add_custom_hover(label)
        self.sort_table_labels()

    def sort_table_labels(self):
        """Sort table labels with numeric names first, then alphabetic, then alphanumeric."""
        
        def sort_key(name):
            match name:
                case _ if (m := re.match(r"^(\d+)", name)):
                    return (1, int(m.group(1)))
                case _ if (m := re.match(r"^([A-Za-z]+)$", name)):
                    return (2, m.group(1).lower())
                case _ if (m := re.match(r"^([A-Za-z]+)(\d+)", name)):
                    return (3, m.group(1).lower(), int(m.group(2)))
                case _:
                    return (4, name.lower())
    
        sorted_tables = sorted(
            self.app.data_manager.tables_data.keys(),
            key=sort_key
        )
    
        for table_name in sorted_tables:
            if label := self.table_labels.get(table_name):
                label.pack_forget()
                label.pack(fill="x", padx=0, pady=5, expand=True)
            
    def cancel_rename(self):
        """Cancel rename operation."""
        if self.rename_entry:
            self.rename_entry.destroy()
            self.rename_entry = None


class Header:
    """Manages the header section, including labels and menus."""

    def __init__(self, root: tk.Tk, app: TableApp):
        self.root = root
        self.app = app
        self.colors = app.colors
        self.fonts = app.fonts
        self.dimensions = app.dimensions
        self.style = app.style
        self.table_name_label: Optional[tk.Label] = None
        self.lower_header_frame: Optional[tk.Frame] = None
        
        self._setup_header()

    def _setup_header(self) -> None:
        """Main setup method for the header."""
        header_frame = self._setup_main_frame()
        self._setup_upper_section(header_frame)
        self._setup_lower_section(header_frame)

    def _setup_main_frame(self) -> tk.Frame:
        """Setup and configure the main header frame."""
        header_frame = create_frame(parent=self.root, width=None, bg=self.colors["background"])
        header_frame.grid(row=0, column=1, columnspan=2, sticky="ew")
        header_frame.config(height=self.dimensions["header_height"])
        header_frame.grid_propagate(False)

        for i in range(2):
            header_frame.grid_rowconfigure(i, weight=0)
        for i in range(5):
            header_frame.grid_columnconfigure(i, weight=1 if i == 4 else 0)

        return header_frame

    def _setup_upper_section(self, parent: tk.Frame) -> None:
        """Setup the upper section of the header."""
        upper_frame = create_frame(parent=parent, width=None, bg=self.colors["background"])
        upper_frame.grid(row=0, column=0, columnspan=5, sticky="ew", pady=(0, 10))
        upper_frame.grid_columnconfigure(5, weight=1)

        self._add_upper_content(upper_frame)

    def _add_upper_content(self, frame: tk.Frame) -> None:
        """Add content to the upper section."""
        tk.Label(
            frame,
            text="Data > ",
            bg=self.colors["background"],
            fg=self.colors["header_info"],
            font=self.fonts["header_info"]
        ).grid(row=0, column=0, padx=22, pady=(22, 0), sticky="nw")

        self.table_name_label = tk.Label(
            frame,
            text=self.app.current_table_name or "No Table Selected",
            bg=self.colors["background"],
            fg=self.colors["text"],
            font=self.fonts["header_info"]
        )
        self.table_name_label.grid(row=0, column=0, padx=(88, 10), pady=(22, 0), sticky="nw")

        self._setup_settings_section(frame)

    def _setup_settings_section(self, frame: tk.Frame) -> None:
        """Setup the settings section with label and menu."""
        settings_label = tk.Label(
            frame,
            text="...",
            bg=self.colors["background"],
            fg=self.colors["text"],
            cursor="hand2",
            anchor="e",
            font=self.fonts["text"]
        )
        settings_label.grid(row=0, column=5, padx=40, pady=20, sticky="e")

        settings_menu = self._create_settings_menu()
        
        settings_label.bind("<Button-1>", lambda e: settings_menu.post(e.x_root, e.y_root))
        settings_label.bind("<Enter>", 
                          lambda _: settings_label.config(bg=self.colors["button_selected"]))
        settings_label.bind("<Leave>", 
                          lambda _: settings_label.config(bg=self.colors["background"]))

    def _create_settings_menu(self) -> tk.Menu:
        """Create and return the settings menu."""
        menu = tk.Menu(self.root, tearoff=0)
        menu_items = [
            ("Export Table", self.app.table_io.export_table),
            ("Import Table", self.app.table_io.import_file),
            ("Rename Headers", self.app.table_operations.rename_headers),
            ("Change Column Data Types", self.app.table_operations.change_column_data_types)
        ]
        for label, command in menu_items:
            menu.add_command(label=label, command=command)
        return menu

    def _setup_lower_section(self, parent: tk.Frame) -> None:
        """Setup the lower section of the header."""
        self.lower_header_frame = create_frame(
            parent=parent, 
            width=None, 
            bg=self.colors["background"]
        )
        self.lower_header_frame.grid(row=1, column=0, columnspan=5, sticky="ew")


class FilterControls:
    """Manages filtering logic and UI components."""

    FILTER_OPTIONS = {
        "is": lambda cell, text: cell == text,
        "is not": lambda cell, text: cell != text,
        "contains": lambda cell, text: text in cell,
        "does not contain": lambda cell, text: text not in cell,
        "starts with": lambda cell, text: cell.startswith(text),
        "ends with": lambda cell, text: cell.endswith(text),
        "is empty": lambda cell, _: cell == "",
        "is not empty": lambda cell, _: cell != "",
    }

    def __init__(self, parent: tk.Frame, app: TableApp):
        self._initialize_attributes(parent, app)
        self._setup_variables()
        self.create_filter_controls()

    def _initialize_attributes(self, parent: tk.Frame, app: TableApp):
        """Initialize basic attributes."""
        self.app = app
        self.root = parent
        self.colors = app.colors
        self.fonts = app.fonts
        self.style = app.style

    def _setup_variables(self):
        """Setup tkinter variables."""
        self.filter_header_var = tk.StringVar(value="All")
        self.filter_option_var = tk.StringVar(value="contains")
        self.filter_text_var = tk.StringVar()

    def create_filter_controls(self):
        """Create and configure filter controls."""
        filter_components = self._create_filter_components()
        self._configure_filter_components(*filter_components)
        self._bind_filter_events()

    def _create_filter_components(self):
        """Create filter UI components."""
        headers = ["All"] + self.app.data_manager.headers.get(
            self.app.current_table_name, []
        )
        filter_frame, header_dropdown, option_dropdown, text_entry = custom_filter_design(
            self.root,
            self.style,
            headers=headers,
            filter_types=list(self.FILTER_OPTIONS.keys()),
            filter_font=self.fonts["filter"],
            filter_combobox_font=self.fonts["filter_combobox"],
            colors=self.colors,
        )
        filter_frame.grid(row=0, column=1, columnspan=4, padx=100, pady=0, sticky="ew")
        return header_dropdown, option_dropdown, text_entry

    def _configure_filter_components(self, header_dropdown, option_dropdown, text_entry):
        """Configure filter components with variables."""
        self.filter_header_dropdown = header_dropdown
        self.filter_option_dropdown = option_dropdown
        self.filter_text_entry = text_entry

        self.filter_header_dropdown["textvariable"] = self.filter_header_var
        self.filter_option_dropdown["textvariable"] = self.filter_option_var
        self.filter_text_entry["textvariable"] = self.filter_text_var

    def _bind_filter_events(self):
        """Bind events to filter components."""
        self.filter_header_dropdown.bind("<<ComboboxSelected>>", self.apply_filter)
        self.filter_option_dropdown.bind(
            "<<ComboboxSelected>>",
            lambda event: [self.update_filter_text_state(event), self.apply_filter()],
        )
        self.filter_text_entry.bind("<KeyRelease>", self.apply_filter)
        self.filter_text_entry.bind("<Return>", self.apply_filter)

    def update_filter_text_state(self, event=None):
        """Enable or disable the filter text entry based on filter option."""
        option = self.filter_option_var.get()
        is_special_option = option in ["is empty", "is not empty"]
        
        self.filter_text_entry.config(state="disabled" if is_special_option else "normal")
        if is_special_option:
            self.filter_text_var.set("")
            self.apply_filter()

    def apply_filter(self, event=None):
        """Apply filter to table data based on selected criteria."""
        if not self._is_filtering_possible():
            return

        filtered_data, displayed_indices = self._filter_data()
        self._update_display(filtered_data, displayed_indices)

    def _is_filtering_possible(self):
        """Check if filtering is possible."""
        return bool(self.app.current_table_name and self.app.current_sheet)

    def _filter_data(self):
        """Filter data based on current filter settings."""
        header = self.filter_header_var.get()
        option = self.filter_option_var.get()
        text = self.filter_text_var.get().lower()
        original_data = self.app.data_manager.tables_data[self.app.current_table_name]["data"]

        filtered_data = []
        displayed_indices = []

        target_indices = self._get_target_indices(header)
        filter_func = self.FILTER_OPTIONS[option]

        for i, row in enumerate(original_data):
            if self._row_matches(row, target_indices, filter_func, text):
                filtered_data.append(row)
                displayed_indices.append(i)

        return filtered_data, displayed_indices

    def _get_target_indices(self, header):
        """Get indices to filter on based on selected header."""
        if header == "All":
            return range(len(self.app.data_manager.headers[self.app.current_table_name]))
        try:
            return [self.app.data_manager.headers[self.app.current_table_name].index(header)]
        except ValueError:
            return []

    def _row_matches(self, row, target_indices, filter_func, text):
        """Check if row matches filter criteria."""
        return any(
            filter_func(str(row[idx]).lower(), text)
            for idx in target_indices
        )

    def _update_display(self, filtered_data, displayed_indices):
        """Update the display with filtered data."""
        try:
            self.app.displayed_row_indices = displayed_indices
            self.app.current_sheet.set_sheet_data(filtered_data)
        except tk.TclError as e:
            print(f"Error applying filter: {e}")


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