Create a gui with tksheet with and example table. it should show dataformatting in tksheet. Create an option in the gui to change datatypes and also save the data in a json file. 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.