Update cells data using specific conditions on user input.

[White50Cent]

Firstly I'm a big fan of the tksheet, awesome job! Right now I have an issue and i can't find a solution to it. Is it possible to check what values entered a user in a column and update another cell from another column with specific text? The use case is as follows: A user enters some values in column A; it can enter 1 value or copy-paste multiple values on that column; After each action, I need to check each values to see if equals with "x_string" and then update column B, C and D with specific values if it does, else it should remain blank. The check and update should be done whenever the user modify something in column A. Thank you very much for your work and your time allocated for community!

[ragardner]

Hello and thanks, a couple of questions sorry

Upon column A cell edits (or paste) should:

1. Column A cells always remain blank? or...
2. Column A cells only be changed to their new values if those new values are equal to "x_string"?

Also upon column A cell edits (or paste) should:

1. Columns B, C, D cells be set to blank if the new column A values are not equal to "x_string"? or...
2. Columns B, C, D cells remain as they are if the new column A values are not equal to "x_string"?

[White50Cent]

Hi, Thanks for the quick response and sorry for my bad explanation, I will try to include more details below: At first, all the table is blank and the user will fill in some data in column A. Then we take the values, check to see if the value exist in an ex:Pandas DataFrame; and populate the column B, C, D with the corresponding values. (i assume it would be like a join tables). If no match then we should have blank on B,C,D. After the check, the column A should have the same value completed by the user and on column B, C, D the corresponding values if match. So, to respond to the your questions: Upon column A cell edits (or paste) should:

1. Column A should contain every time the value filled in by the user;
2. No values entered by the user should change. Also upon column A cell edits (or paste) should:
3. Columns B, C, D cells to be set to blank if no match else fill in with the corresponding data. I hope i manage to explain better the situation, also I'm curious if this will be valid for read-only dropdowns as well. Thanks again for your time and effort!

[ragardner]

Hello,

This is a very basic demonstration of the edit checking and cell modifying code. It doesn't have a pandas dataframe so you'll have to fill in your code but let me know if you run into issues

About the dropdown boxes, let me know if you need help with those

from tksheet import Sheet
import tkinter as tk

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            total_rows=50,
            total_columns=10,
            column_width=150,
            theme="black",
            height=520,
            width=930,
        )
        self.sheet.enable_bindings()
        self.sheet.extra_bindings(
            [
                ("edit_cell", self.sheet_cell_edited),
                ("paste", self.sheet_paste),
            ]
        )

        # disable undo due to self.sheet.set_cell_data not utilising it
        self.sheet.disable_bindings("undo")

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")

        self.check_str = "x_string"

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def sheet_cell_edited(self, event=None):
        # usage of displayed_column_to_data in case of hidden columns
        event_col = self.sheet.displayed_column_to_data(event.column)
        if not event_col:
            row = self.sheet.displayed_row_to_data(event.row)
            # look up corresponding value in pandas DataFrame
            # instead of comparing self.check_str
            if event.text == self.check_str:
                self.set_columns(row)
            else:
                self.clear_columns(row)
        return event.text

    def sheet_paste(self, event=None):
        start_col = self.sheet.displayed_column_to_data(event.currentlyselected.column)
        # if start column is > 0 it's not column A
        if start_col:
            return
        start_row = self.sheet.displayed_row_to_data(event.currentlyselected.row)
        for i, row in enumerate(event.rows):
            sheet_rn = start_row + i
            # look up corresponding value in pandas DataFrame
            # instead of comparing self.check_str
            if row[0] == self.check_str:
                self.set_columns(sheet_rn)
            else:
                self.clear_columns(sheet_rn)
        self.sheet.refresh()

    def clear_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.sheet.MT.get_value_for_empty_cell(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.sheet.MT.get_value_for_empty_cell(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.sheet.MT.get_value_for_empty_cell(rn, 3),
        )

    def set_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.get_df_value(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.get_df_value(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.get_df_value(rn, 3),
        )

app = demo()
app.mainloop()

[White50Cent]

You are a legend, thank you very much! Right now I don't know how to proceed with the dropdowns and your help it will really help me. For ex. let's say that there are available just 2 columns with dropdowns in each cell (each dropdown has at least 3 options from where to choose and a default one let's say "please select option"). If the user selects a option from column 1 it should not be able to select from column 2 any options and if it selects from column 2 it should not be able to select from column 1 (but if it will select the default option, it should let him choose again from the other column). After the user selected the option I need to check what option he choose and color the next column cells for that row. For ex: lets say that the user choose "foo" and the cells of column E, G, I and should be colored in green. If he choose "goo" the column H and J should be colored in green. If the user will change the option, the colors of the cells columns should change as well (for default to be reset to the normal color and for other selection to be set base on the rule). Again thank you very much for your time dedicated for helping people!

[ragardner]

Are the users able to insert columns or rows or does the sheet always have the same number of rows and columns?

[White50Cent]

The same number of rows and columns

[ragardner]

It is taking me longer than expected sorry, I'm having to fix an issue I discovered with cell edits firing two events which is resulting in a little bit of an overhaul of the code that deals with text editors and dropdown boxes

Shouldn't be longer than a few days more, will edit this comment if it is though

[White50Cent]

No reason to be sorry, I'm just glad that an unknown issue was discovered and is now being fixed. Thanks again for your time and dedication!

[ragardner]

Hello,

The below code unfortunately will only work on tksheet versions 6.3.1 and greater sorry

I am not sure if this is exactly what you wanted, setting a dropdown makes the one in the next column readonly and vice versa, unless you select default option

It also still has the original column A code from the previous example

Let me know if you have any questions

from tksheet import Sheet
import tkinter as tk

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            default_header="both",
            default_row_index="both",
            total_rows=50,
            total_columns=10,
            column_width=140,
            theme="black",
            height=520,
            width=1400,
            edit_cell_validation=False,
        )
        self.sheet.enable_bindings(
            "single_select",
            "drag_select",
            "column_select",
            "row_select",
            "column_width_resize",
            "double_click_column_resize",
            "row_width_resize",
            "column_height_resize",
            "arrowkeys",
            "prior",
            "next",
            "row_height_resize",
            "double_click_row_resize",
            "right_click_popup_menu",
            "rc_select",
            "copy",
            "cut",
            "paste",
            "delete",
            "edit_cell",
        )
        self.sheet.extra_bindings(
            [
                ("edit_cell", self.handle_sheet_edit_cell),
                ("paste", self.handle_sheet_paste),
            ]
        )

        self.sheet.dropdown_column(
            4,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )
        self.sheet.dropdown_column(
            5,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.refresh()

        self.check_str = "x_string"
        self.dd_4_cols = (4, 6, 8)
        self.dd_5_cols = (5, 7, 9)

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def handle_sheet_edit_cell(self, event=None):
        self.sheet_cell_edited(
            self.sheet.displayed_row_to_data(event.row),
            self.sheet.displayed_column_to_data(event.column),
            event.text,
        )
        return event.text

    def handle_sheet_paste(self, event=None):
        """
        receives paste event from Sheet
        iterates over rows and checks:
        - if any paste items are in column 0 which has the code from
          the original issue
        - if any paste items are in columns 4/5 which have the dropdown boxes

        """
        start_col = self.sheet.displayed_column_to_data(event.currentlyselected.column)
        start_row = self.sheet.displayed_row_to_data(event.currentlyselected.row)
        if start_col in (4, 5):
            for sheet_rn, row in enumerate(event.rows, start=start_row):
                for sheet_cn, val in enumerate(row, start=start_col):
                    # check the cell is not readonly before possibly highlighting stuff
                    if sheet_cn in (4, 5) and not self.sheet.MT.get_cell_kwargs(sheet_rn, sheet_cn, key="readonly"):
                        self.sheet_cell_edited(sheet_rn, sheet_cn, val)
        elif not start_col:
            for sheet_rn, row in enumerate(event.rows, start=sheet_rn):
                self.sheet_cell_edited(sheet_rn, start_col, row[0])
        self.sheet.refresh()

    def sheet_cell_edited(self, row, column, val):
        """
        Is called by the "handle_ ..." functions
        When cell editing occurs
        """
        # if we're dealing with edits in dropdown columns
        # then handle the readonly and highlight settings
        if column in (4, 5):
            itr_cols = self.dd_4_cols if column == 4 else self.dd_5_cols
            if val == "default option":
                # set both dropdowns back to normal
                for c in (4, 5):
                    self.sheet.readonly_cells(
                        row,
                        c,
                        readonly=False,
                    )
                # clear highlights
                for c in itr_cols:
                    self.sheet.dehighlight_cells(
                        row,
                        c,
                        redraw=False,
                    )
            elif val != "default option":
                # set opposite dropdown to readonly
                self.sheet.readonly_cells(
                    row,
                    4 if column == 5 else 5,
                    readonly=True,
                )
                # highlight relevant columns for that row
                bg = "#52C866" if "green" in val else "#fb5151"
                fg = "black"
                for c in itr_cols:
                    self.sheet.highlight_cells(
                        row,
                        c,
                        bg=bg,
                        fg=fg,
                        redraw=False,)
        # elif we're dealing with column A
        # then handle the pandas dataframe value comparison
        elif not column:
            # look up corresponding value in pandas DataFrame
            # instead of comparing self.check_str
            if val == self.check_str:
                self.set_columns(row)
            else:
                self.clear_columns(row)

    def clear_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.sheet.MT.get_value_for_empty_cell(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.sheet.MT.get_value_for_empty_cell(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.sheet.MT.get_value_for_empty_cell(rn, 3),
        )

    def set_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.get_df_value(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.get_df_value(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.get_df_value(rn, 3),
        )

app = demo()
app.mainloop()

[White50Cent]

Don't know if it's a problem from the last update but after I've installed tksheet 6.3.1 i get the next error: "AttributeError: 'Sheet' object has no attribute 'MT'". Also I don't know why my intellisense is not working in the .py file where tksheet is imported or used (I use as Visual Studio Code as IDE).

[ragardner]

Hm, sorry about that, what snippet of code is generating the error?

I also use vscode, i couldn’t say what might be preventing intellisense from working sorry

[White50Cent]

It was just my fault, after reading from pypi the project description I stubble upon "Only Python versions >= 3.8 are supported". I've installed the last version of python and the the script runs. But after testing I've discovered something else. If i will paste the "x_string" value in column A then the next columns are not populated and i get the next error: File "path\_tksheet_main_table.py", line 815, in ctrl_v self.extra_end_ctrl_v_func(PasteEvent("end_ctrl_v", currently_selected, rows)) File "path_of_code", line 112, in handle_sheet_paste for sheet_rn, row in enumerate(event.rows, start=sheet_rn): ^^^^^^^^ UnboundLocalError: cannot access local variable 'sheet_rn' where it is not associated with a value Is the dropdown logic interfering with the checks/paste or is something else?

[ragardner]

Thanks for the extra info, i will have to go over these issues tomorrow sorry

6.3.1 should in theory work with python 3.6, it was not my intention to make it 3.8+ :/

but perhaps i accidentally included some new python syntax somewhere, i’ll have to check

it’ll be version 7 when i eventually finish it that will be 3.8+

[White50Cent]

Just for info, I was using python 3.7 when the error occurred. Thanks a lot for your help and I will wait for your response for the rest of the issues. <3

[ragardner]

I've discovered and fixed the above issues,

The issue with Python 3.7 was some new functions I introduced to the Sheet which I neglected to test on an earlier version, specifically __bool__ and __len__. These have been removed in tksheet version 6.3.2

The sheet_rn error you were getting was due to my example sorry, it is fixed in the below code

I also decided to drop support for python 3.6 as it wasn't being supported in the vscode debugger anyway

I'm happy to help if you run into anymore issues

from tksheet import Sheet
import tkinter as tk

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            default_header="both",
            default_row_index="both",
            total_rows=50,
            total_columns=10,
            column_width=140,
            theme="black",
            height=520,
            width=1400,
            edit_cell_validation=False,
        )
        self.sheet.enable_bindings(
            "single_select",
            "drag_select",
            "column_select",
            "row_select",
            "column_width_resize",
            "double_click_column_resize",
            "row_width_resize",
            "column_height_resize",
            "arrowkeys",
            "prior",
            "next",
            "row_height_resize",
            "double_click_row_resize",
            "right_click_popup_menu",
            "rc_select",
            "copy",
            "cut",
            "paste",
            "delete",
            "edit_cell",
        )
        self.sheet.extra_bindings(
            [
                ("edit_cell", self.handle_sheet_edit_cell),
                ("paste", self.handle_sheet_paste),
            ]
        )

        self.sheet.dropdown_column(
            4,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )
        self.sheet.dropdown_column(
            5,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.refresh()

        self.dd_4_cols = (4, 6, 8)
        self.dd_5_cols = (5, 7, 9)
        """
        This is the value that edits to cells in column A
        are compared to, instead of comparing an edit
        to a value in a dataframe
        """
        self.check_str = "x_string"

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def handle_sheet_edit_cell(self, event=None):
        self.sheet_cell_edited(
            self.sheet.displayed_row_to_data(event.row),
            self.sheet.displayed_column_to_data(event.column),
            event.text,
        )
        return event.text

    def handle_sheet_paste(self, event=None):
        """
        receives paste event from Sheet
        iterates over rows and checks:
        - if any paste items are in column 0 which has the code from
          the original issue
        - if any paste items are in columns 4/5 which have the dropdown boxes

        """
        start_col = self.sheet.displayed_column_to_data(event.currentlyselected.column)
        start_row = self.sheet.displayed_row_to_data(event.currentlyselected.row)
        if start_col in (4, 5):
            for sheet_rn, row in enumerate(event.rows, start=start_row):
                for sheet_cn, val in enumerate(row, start=start_col):
                    # check the cell is not readonly before possibly highlighting stuff
                    if sheet_cn in (4, 5) and not self.sheet.MT.get_cell_kwargs(sheet_rn, sheet_cn, key="readonly"):
                        self.sheet_cell_edited(sheet_rn, sheet_cn, val)
        elif not start_col:
            for sheet_rn, row in enumerate(event.rows, start=start_row):
                self.sheet_cell_edited(sheet_rn, start_col, row[0])
        self.sheet.refresh()

    def sheet_cell_edited(self, row, column, val):
        """
        Is called by the "handle_ ..." functions
        When cell editing occurs
        """
        # if we're dealing with edits in dropdown columns
        # then handle the readonly and highlight settings
        if column in (4, 5):
            itr_cols = self.dd_4_cols if column == 4 else self.dd_5_cols
            if val == "default option":
                # set both dropdowns back to normal
                for c in (4, 5):
                    self.sheet.readonly_cells(
                        row,
                        c,
                        readonly=False,
                    )
                # clear highlights
                for c in itr_cols:
                    self.sheet.dehighlight_cells(
                        row,
                        c,
                        redraw=False,
                    )
            elif val != "default option":
                # set opposite dropdown to readonly
                self.sheet.readonly_cells(
                    row,
                    4 if column == 5 else 5,
                    readonly=True,
                )
                # highlight relevant columns for that row
                bg = "#52C866" if "green" in val else "#fb5151"
                fg = "black"
                for c in itr_cols:
                    self.sheet.highlight_cells(
                        row,
                        c,
                        bg=bg,
                        fg=fg,
                        redraw=False,)
        # elif we're dealing with column A
        # then handle the pandas dataframe value comparison
        elif not column:
            # look up corresponding value in pandas DataFrame
            # instead of comparing self.check_str
            if val == self.check_str:
                self.set_columns(row)
            else:
                self.clear_columns(row)

    def clear_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.sheet.MT.get_value_for_empty_cell(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.sheet.MT.get_value_for_empty_cell(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.sheet.MT.get_value_for_empty_cell(rn, 3),
        )

    def set_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.get_df_value(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.get_df_value(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.get_df_value(rn, 3),
        )

app = demo()
app.mainloop()

[White50Cent]

A last question I have. If I want to delete the data completed in column A with delete key from the keyboard, I need to add another binding to the extra bindings in order to clear the data autocompleted in the next columns? For example I want to delete 2 cells that contain "x_string" with Delete key and then update the next columns to blank.

[ragardner]

Oh, yea sorry I forgot about the delete event handler, also I should mention that the line edit_cell_validation=False, in the Sheet() creation arguments is of particular importance in this case

When I release tksheet version 7 I will make another post with code that works for version 7, as it will be slightly different due to the event objects being changed

Here's the updated code:

from tksheet import Sheet
import tkinter as tk

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            default_header="both",
            default_row_index="both",
            total_rows=50,
            total_columns=10,
            column_width=140,
            theme="black",
            height=520,
            width=1400,
            edit_cell_validation=False,
        )
        self.sheet.enable_bindings(
            "single_select",
            "drag_select",
            "column_select",
            "row_select",
            "column_width_resize",
            "double_click_column_resize",
            "row_width_resize",
            "column_height_resize",
            "arrowkeys",
            "prior",
            "next",
            "row_height_resize",
            "double_click_row_resize",
            "right_click_popup_menu",
            "rc_select",
            "copy",
            "cut",
            "paste",
            "delete",
            "edit_cell",
        )
        self.sheet.extra_bindings(
            [
                ("edit_cell", self.handle_sheet_edit_cell),
                ("paste", self.handle_sheet_paste),
                ("delete", self.handle_sheet_delete),
            ]
        )

        self.sheet.dropdown_column(
            4,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )
        self.sheet.dropdown_column(
            5,
            values=[
                "default option",
                "green",
                "red",
            ],
            redraw=False,
        )

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.refresh()

        self.dd_4_cols = (4, 6, 8)
        self.dd_5_cols = (5, 7, 9)
        """
        This is the value that edits to cells in column A
        are compared to, instead of comparing an edit
        to a value in a dataframe
        """
        self.check_str = "x_string"

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def handle_sheet_edit_cell(self, event=None):
        self.sheet_cell_edited(
            self.sheet.displayed_row_to_data(event.row),
            self.sheet.displayed_column_to_data(event.column),
            event.text,
        )
        return event.text

    def handle_sheet_paste(self, event=None):
        """
        receives paste event from Sheet
        iterates over rows and checks:
        - if any paste items are in column 0 which has the code from
          the original issue
        - if any paste items are in columns 4/5 which have the dropdown boxes

        """
        start_col = self.sheet.displayed_column_to_data(event.currentlyselected.column)
        start_row = self.sheet.displayed_row_to_data(event.currentlyselected.row)
        if start_col in (4, 5):
            for sheet_rn, row in enumerate(event.rows, start=start_row):
                for sheet_cn, val in enumerate(row, start=start_col):
                    # check the cell is not readonly before possibly highlighting stuff
                    if sheet_cn in (4, 5) and not self.sheet.MT.get_cell_kwargs(sheet_rn, sheet_cn, key="readonly"):
                        self.sheet_cell_edited(sheet_rn, sheet_cn, val)
        elif not start_col:
            for sheet_rn, row in enumerate(event.rows, start=start_row):
                self.sheet_cell_edited(sheet_rn, start_col, row[0])
        self.sheet.refresh()

    def handle_sheet_delete(self, event):
        for from_r, from_c, upto_r, upto_c in event.selectionboxes:
            for row in range(from_r, upto_r):
                for column in range(from_c, upto_c):
                    row = self.sheet.displayed_row_to_data(row)
                    column = self.sheet.displayed_column_to_data(column)
                    self.sheet_cell_edited(
                        row,
                        column,
                        self.sheet.get_value_for_empty_cell(row, column),
                    )

    def sheet_cell_edited(self, row, column, val):
        """
        Is called by the "handle_ ..." functions
        When cell editing occurs
        """
        # if we're dealing with edits in dropdown columns
        # then handle the readonly and highlight settings
        if column in (4, 5):
            itr_cols = self.dd_4_cols if column == 4 else self.dd_5_cols
            if val == "default option":
                # set both dropdowns back to normal
                for c in (4, 5):
                    self.sheet.readonly_cells(
                        row,
                        c,
                        readonly=False,
                    )
                # clear highlights
                for c in itr_cols:
                    self.sheet.dehighlight_cells(
                        row,
                        c,
                        redraw=False,
                    )
            elif val != "default option":
                # set opposite dropdown to readonly
                self.sheet.readonly_cells(
                    row,
                    4 if column == 5 else 5,
                    readonly=True,
                )
                # highlight relevant columns for that row
                bg = "#52C866" if "green" in val else "#fb5151"
                fg = "black"
                for c in itr_cols:
                    self.sheet.highlight_cells(
                        row,
                        c,
                        bg=bg,
                        fg=fg,
                        redraw=False,)
        # elif we're dealing with column A
        # then handle the pandas dataframe value comparison
        elif not column:
            # look up corresponding value in pandas DataFrame
            # instead of comparing self.check_str
            if val == self.check_str:
                self.set_columns(row)
            else:
                self.clear_columns(row)

    def clear_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.sheet.MT.get_value_for_empty_cell(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.sheet.MT.get_value_for_empty_cell(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.sheet.MT.get_value_for_empty_cell(rn, 3),
        )

    def set_columns(self, rn):
        self.sheet.set_cell_data(
            rn,
            1,
            self.get_df_value(rn, 1),
        )
        self.sheet.set_cell_data(
            rn,
            2,
            self.get_df_value(rn, 2),
        )
        self.sheet.set_cell_data(
            rn,
            3,
            self.get_df_value(rn, 3),
        )

app = demo()
app.mainloop()

[White50Cent]

This is awesome, now it works as intended! Now in principle I know how to use the validations within tksheet with dropdowns and cells. I'm also glad that some issues were discovered and fixed for future users. Thank you very much for your time, effort and support, you are a grate person! <3

[White50Cent]

Hello again. Is there a way to get the row data after the user just edited a cell? Let's say the user insert some text in the first 3 columns and i want to get entire row to see what value is now in the second column cell. I've tried get_cell_data, get_row_data and get_column_data but each of them return the values before the edit not after (ex: cell is empty at first then user insert "foo", the values of the return will be emty).

[ragardner]

Hello,

By what method would the user be inserting text in the first 3 columns sorry?

Speaking for single cell edits a Sheet() with edit_cell_validation=False will perform the cell edit before the event is emitted, and one with the setting as True will emit the event before the cell edit is performed. You can use sheet.set_options() with this argument to change it after initialization

[White50Cent]

Hi, the user will manually type in the first column cell by cell or will paste multiple values, and the second 2 columns will be autocompleted. The situation is similar to the one above.

[ragardner]

I am a bit confused sorry,

With my latest example above if I type into the first column first cell x_string and then at the top of the function sheet_cell_edited i have print (self.sheet.get_row_data(row)) it prints the following:

['x_string', '', '', '', 'default option', 'default option', '', '', '', '']

the edited first cell is there, the following three cells are unedited but that's because those edits take place at the bottom of that function (sheet_cell_edited), if you were to print the row afterwards the edits would be there

[White50Cent]

Hi and happy new year! Thanks for your help, i managed to get the input by changing edit_cell_validation=True as you instructed. Right now i can't figure it why i can't set a predefined value of a dropdownbox, it works fine with pasted values but not with edit values, the case is as follows: If i have "foo" on column 2 and the user selects "green" on the first column dropdown, then i must prompt the user that he shouldn't make that selection and reset the dropdown value of that cell to the 'default option'. I've tried sheet.set_dropdown_values( row, 4, set_existing_dropdown = False, set_value = "default option", values = "default option", "green", "red"], ) It works fine with paste values, but not with select values from dropdown. Any idea why?

[ragardner]

Hello, sorry for the long delay in getting back to you

I am not entirely sure what's going on but the current value for a cell with a dropdown box should just be whatever the cell value is so you could try using set_cell_data(row, 4, "default option") to reset it

I don't quite understand because with the example above the opposite dropdown should be readonly when "green" or "red" are selected?

I have just released version 7 of tksheet btw so if you happen to upgrade like 95% of functionality is the same but there have been some changes, you can check out the changelog I've tried to include everything. I have rewritten the example to fix the event data errors that would occur with upgrading and take advantage of the new syntax in version 7

import tkinter as tk

from tksheet import (
    Sheet,
)
from tksheet import (
    num2alpha as n2a,
)

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            default_header="both",
            default_row_index="both",
            total_rows=50,
            total_columns=10,
            column_width=140,
            theme="black",
            height=520,
            width=1400,
        )
        self.sheet.enable_bindings(
            "single_select",
            "drag_select",
            "column_select",
            "row_select",
            "column_width_resize",
            "double_click_column_resize",
            "row_width_resize",
            "column_height_resize",
            "move_columns",
            "move_rows",
            "arrowkeys",
            "prior",
            "next",
            "row_height_resize",
            "double_click_row_resize",
            "right_click_popup_menu",
            "rc_select",
            "copy",
            "cut",
            "paste",
            "delete",
            "edit_cell",
            "edit_index",
            "undo",
        )
        self.sheet.bind("<<SheetModified>>", self.sheet_modified)
        self.sheet.dropdown(
            n2a(4),
            values=[
                "default option",
                "green",
                "red",
            ],
        )
        self.sheet.dropdown(
            n2a(5),
            values=[
                "default option",
                "green",
                "red",
            ],
        )

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.refresh()

        self.dd_4_cols = (4, 6, 8)
        self.dd_5_cols = (5, 7, 9)
        """
        This is the value that edits to cells in column A
        are compared to, instead of comparing an edit
        to a value in a dataframe
        """
        self.check_str = "x_string"
        # self.sheet.set_cell_data()

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def sheet_modified(self, event):
        """
        Triggered after any cell editing occurs
        """
        for row, column in event.cells.table:
            val = self.sheet[row, column].data
            # if we're dealing with edits in dropdown columns
            # then handle the readonly and highlight settings
            if column in (4, 5):
                itr_cols = self.dd_4_cols if column == 4 else self.dd_5_cols
                if val == "default option":
                    # set both dropdowns back to normal
                    for c in (4, 5):
                        self.sheet.readonly(
                            row,
                            c,
                            readonly=False,
                        )
                    # clear highlights
                    for c in itr_cols:
                        self.sheet.dehighlight(
                            row,
                            c,
                        )
                elif val != "default option":
                    # set opposite dropdown to readonly
                    self.sheet.readonly(
                        row,
                        4 if column == 5 else 5,
                        readonly=True,
                    )
                    # highlight relevant columns for that row
                    bg = "#52C866" if "green" in val else "#fb5151"
                    fg = "black"
                    for c in itr_cols:
                        self.sheet.highlight(
                            row,
                            c,
                            bg=bg,
                            fg=fg,
                        )
            # elif we're dealing with column A
            # then handle the pandas dataframe value comparison
            elif not column:
                # look up corresponding value in pandas DataFrame
                # instead of comparing self.check_str
                if val == self.check_str:
                    self.set_columns(row)
                else:
                    self.clear_columns(row)

    def clear_columns(self, rn):
        self.sheet[rn, 1].data = self.sheet.get_value_for_empty_cell(rn, 1)
        self.sheet[rn, 2].data = self.sheet.get_value_for_empty_cell(rn, 2)
        self.sheet[rn, 3].data = self.sheet.get_value_for_empty_cell(rn, 3)

    def set_columns(self, rn):
        self.sheet[rn, 1].data = self.get_df_value(rn, 1)
        self.sheet[rn, 2].data = self.get_df_value(rn, 2)
        self.sheet[rn, 3].data = self.get_df_value(rn, 3)

app = demo()
app.mainloop()

[White50Cent]

Thanks for the response. I've tried set_cell_data(row, 4, "default option") and it works for other dropdown cells than the one that i'm on. Let's say i'm on line 2, i have "foo" on column 2 and the i select "green" on the first column dropdown. When i set set_cell_data(0, 4, "default option") it will change the value from the row=0, column=4, to "default option", but if i'm on row=0 the change will not take place. I'm must say that you did a awesome job with the new version of the module <3 nice changes.

[ragardner]

Ah I think I understand now sorry

This is because set_cell_data() isn't included in the sheet edit events and so the functions controlling readonly and colors etc aren't being called

In the short term what you can do with versions 7+ is:

self.sheet_modified(self.sheet.set_data(0, 4, data="default option"))

This is because the function set_data() returns the event data dictionary and so the sheet_modified() function can make use of it

In the longer term I will consider changing some of the new functions in version 7 to trigger these events

Edit: Thanks for the complements and feedback. Also, I added a function below named test_issue() you can edit it if you want to try and explain things if I'm still not quite on the right track with this issue

The full code I am using currently is below:

import tkinter as tk

from tksheet import (
    Sheet,
)
from tksheet import (
    num2alpha as n2a,
)

class demo(tk.Tk):
    def __init__(self):
        tk.Tk.__init__(self)
        self.grid_columnconfigure(0, weight=1)
        self.grid_rowconfigure(0, weight=1)
        self.frame = tk.Frame(self)
        self.frame.grid_columnconfigure(0, weight=1)
        self.frame.grid_rowconfigure(0, weight=1)
        self.sheet = Sheet(
            self.frame,
            default_header="both",
            default_row_index="both",
            total_rows=50,
            total_columns=10,
            default_column_width=140,
            theme="black",
            height=520,
            width=1400,
        )
        self.sheet.enable_bindings(
            "single_select",
            "drag_select",
            "column_select",
            "row_select",
            "column_width_resize",
            "double_click_column_resize",
            "row_width_resize",
            "column_height_resize",
            "move_rows",
            "arrowkeys",
            "prior",
            "next",
            "row_height_resize",
            "double_click_row_resize",
            "right_click_popup_menu",
            "rc_select",
            "copy",
            "cut",
            "paste",
            "delete",
            "edit_cell",
            "edit_index",
            "undo",
        )
        self.sheet.bind("<<SheetModified>>", self.sheet_modified)
        self.sheet.dropdown(
            n2a(4),
            values=[
                "default option",
                "green",
                "red",
            ],
        )
        self.sheet.dropdown(
            n2a(5),
            values=[
                "default option",
                "green",
                "red",
            ],
        )

        self.sheet.popup_menu_add_command("Test issue", self.test_issue)

        self.frame.grid(row=0, column=0, sticky="nswe")
        self.sheet.grid(row=0, column=0, sticky="nswe")
        self.sheet.refresh()

        self.dd_4_cols = (4, 6, 8)
        self.dd_5_cols = (5, 7, 9)
        """
        This is the value that edits to cells in column A
        are compared to, instead of comparing an edit
        to a value in a dataframe
        """
        self.check_str = "x_string"
        # self.sheet.set_cell_data()

    def test_issue(self):
        self.sheet_modified(self.sheet.set_data(0, 4, data="default option"))

    def get_df_value(self, row, col):
        # code for getting and returning actual value from your
        # PandasDataFrame goes here instead of this return value
        return f"r{row}, c{col} new val"

    def sheet_modified(self, event):
        """
        Triggered after any cell editing occurs
        """
        for row, column in event.cells.table:
            val = self.sheet[row, column].data
            # if we're dealing with edits in dropdown columns
            # then handle the readonly and highlight settings
            if column in (4, 5):
                itr_cols = self.dd_4_cols if column == 4 else self.dd_5_cols
                if val == "default option":
                    # set both dropdowns back to normal
                    for c in (4, 5):
                        self.sheet.readonly(
                            row,
                            c,
                            readonly=False,
                        )
                    # clear highlights
                    for c in itr_cols:
                        self.sheet.dehighlight(
                            row,
                            c,
                        )
                elif val != "default option":
                    # set opposite dropdown to readonly
                    self.sheet.readonly(
                        row,
                        4 if column == 5 else 5,
                        readonly=True,
                    )
                    # highlight relevant columns for that row
                    bg = "#52C866" if "green" in val else "#fb5151"
                    fg = "black"
                    for c in itr_cols:
                        self.sheet.highlight(
                            row,
                            c,
                            bg=bg,
                            fg=fg,
                        )
            # elif we're dealing with column A
            # then handle the pandas dataframe value comparison
            elif not column:
                # look up corresponding value in pandas DataFrame
                # instead of comparing self.check_str
                if val == self.check_str:
                    self.set_columns(row)
                else:
                    self.clear_columns(row)

    def clear_columns(self, rn):
        self.sheet[rn, 1].data = self.sheet.get_value_for_empty_cell(rn, 1)
        self.sheet[rn, 2].data = self.sheet.get_value_for_empty_cell(rn, 2)
        self.sheet[rn, 3].data = self.sheet.get_value_for_empty_cell(rn, 3)

    def set_columns(self, rn):
        self.sheet[rn, 1].data = self.get_df_value(rn, 1)
        self.sheet[rn, 2].data = self.get_df_value(rn, 2)
        self.sheet[rn, 3].data = self.get_df_value(rn, 3)

app = demo()
app.mainloop()

[White50Cent]

Thanks for your precious time, you are awesome! You build a tool that i think will help many <3