tksheet Documentation

By default, tksheet stores all user-inputted data as strings. Data formatting allows you to provide strict typing, convert sheet data and user input to specific datatypes, and control how data is displayed on the GUI (e.g., rounding floats).

Priority of Overlapping Formats

When multiple formats overlap (e.g., a formatted cell within a formatted row), the priority is:

1. Cell formats (highest priority)
2. Row formats
3. Column formats (lowest priority)

Key Considerations

• Validation: Data formatting effectively overrides validate_input = True on cells with dropdown boxes.
• Data Retrieval: When using get_displayed=True or tdisp in data retrieval functions, you will receive the formatted string shown in the GUI rather than the underlying typed value.
• Nullability: If nullable=True is set in a formatter, None values are handled specifically via a nonelike set check before being passed to the format_function.

# Example of applying a format to a column
self.sheet.format(
    "B", # column B
    formatter_options=float_formatter(),
    decimals=3,
    nullable=False,
)

Functions bound via extra_bindings() receive a dictionary (or EventDataDict) containing metadata about the event. You can access keys using dot notation (e.g., event.eventname).

Key Data Fields:

• eventname: The name of the event triggered.
• cells: Contains dictionaries for table, header, and index. For modification events, these contain the old values at the specified coordinates.
• moved: Contains rows and columns dictionaries with data (old to new index mapping) and displayed mappings.
• added: Contains rows and columns info including data_index, displayed_index, and num added.
• deleted: Contains details about deleted rows, columns, headers, or indices.
• value: The current value in the editor (for begin_edit... or end_edit... events) or the target position (for begin_move... events).
• loc: The displayed coordinates (row, column) for cell edits.
• selected: A namedtuple of the current selection.
• resized: Dictionaries for rows and columns showing old_size and new_size.

def my_callback(event):
    # Accessing data via dot notation
    for (row, col), old_val in event.cells.table.items():
        print(f"Row {row}, Col {col} changed from {old_val}")

tksheet provides several built-in sorting keys to handle different Python object types. You can set a default sorting key for the entire sheet or override it during a specific sort operation.

Built-in Sorting Keys

• natural_sort_key: The default. Handles strings, floats, dates, and file paths naturally. Converts numeric strings to floats.
• version_sort_key: Respects and sorts version numbers (e.g., '1.2.3'). Does not convert strings to floats.
• fast_sort_key: Optimized for very large sheets (1M+ cells). Sacrifices accuracy for date and file path sorting to gain speed.
• date_sort_key: Emphasizes date sorting over numbers. Tries to convert strings to dates before floats.

How to set the sorting key

At initialization:

from tksheet import Sheet, natural_sort_key
my_sheet = Sheet(parent=parent, sort_key=natural_sort_key)

After initialization (global):

my_sheet.set_options(sort_key=natural_sort_key)

During a specific sort call (overrides global):

my_sheet.sort_columns(0, key=natural_sort_key)

You can intercept and validate user edits (including cut, paste, and dropdown selections) using two methods:

1. edit_validation(func): This function is called for every single cell edit within an action. If the bound function returns None, the specific cell edit is cancelled.

2. bulk_table_edit_validation(func): This is called at the end of an action. It allows you to delay edits until validation is complete. This is useful for validating multiple cells at once (e.g., after a paste).

Bulk Validation Pattern: In your validation function, you modify the event.data dictionary. The edits remaining in event.data when the function returns are the ones that will actually be applied to the sheet.

def validate(self, event: dict) -> Any:
    # Prevent edits if the value contains a space
    not_valid = set()
    for (r, c), value in event.data.items():
        if " " in value:
            not_valid.add((r, c))
    
    # Filter event.data to remove invalid entries
    event.data = {k: v for k, v in event.data.items() if k not in not_valid}

sheet.bulk_table_edit_validation(self.validate)

tksheet provides built-in formatters for common data types and allows for highly customizable formatting logic. You can apply formatters to specific columns using the .format() method on a column selection.

Built-in Formatters

• float_formatter(): Formats floating point numbers.
• int_formatter(): Formats integers.
• percentage_formatter(): Formats numbers as percentages. Supports decimals argument.
• bool_formatter(): Formats boolean values. Supports truthy and falsy sets to define custom truth/false values.

Custom Formatting Logic

You can extend formatting using several hooks:

• pre_format_function: A function applied to the raw value before it is processed by the formatter.
• post_format_function: A function applied to the value after the formatter has processed it.
• formatter() (Generic Interface): Used for complex types (like datetime). It requires datatypes, format_function (to convert raw data to the target type), and to_str_function (to convert the target type to a display string).

Column Selection

Use num2alpha(index) to convert a zero-based integer index into a spreadsheet-style letter index (e.g., 0 becomes 'A') for column selection.

from tksheet import Sheet, formatter, float_formatter, int_formatter, percentage_formatter, bool_formatter, truthy, falsy, num2alpha

# ... setup sheet ...

# Apply built-in formatters
self.sheet[num2alpha(0)].format(float_formatter(nullable=False))
self.sheet[num2alpha(2)].format(int_formatter())
self.sheet[num2alpha(3)].format(bool_formatter(truthy=truthy | {"nah yeah"}, falsy=falsy | {"yeah nah"}))
self.sheet[num2alpha(4)].format(percentage_formatter())

# Custom formatter with pre/post hooks
self.sheet[num2alpha(7)].format(float_formatter(post_format_function=round_up))
self.sheet[num2alpha(8)].format(float_formatter(), pre_format_function=only_numeric)

# Complex custom formatter (e.g., for datetime)
def convert_to_local_datetime(dt, **kwargs): ... 
def datetime_to_string(dt, **kwargs): ...

self.sheet[num2alpha(5)].format(
    formatter(
        datatypes=datetime,
        format_function=convert_to_local_datetime,
        to_str_function=datetime_to_string,
        nullable=False,
        invalid_value="NaT",
    )
)

You can apply formatting to cells, rows, columns, or the entire sheet using Span objects.

Creating Rules

Use either the span method or the sheet method:

• span.format(...)
• sheet.format(Span)

If you need formatting to persist through user-inserted rows or columns in the middle of a range, use named spans.

Deleting Rules

• To delete a specific rule: Use span.del_format() or sheet.del_format(Span).
• To delete all formatting: Use sheet.del_all_formatting(clear_values=False).
• Note: If a rule was created using a named span, you must delete the named span itself.

# Creating a format rule using the sheet method
# This applies a float formatter to column 'A'
sheet.format(
    "A",
    formatter_options=float_formatter(decimals=2),
)

# Deleting a format rule
sheet.del_format("A")

# Deleting all formatting and clearing all cell values
sheet.del_all_formatting(clear_values=True)