Substract values of column 2 with values from column 1

[Prospekteur]

I need help how to write a script for the following task:

After activating the code in the right click menu, the values of the second column should be substracted from the values of the first column (see images) in the selected area. I know how to start codes from the right click menu, but I need help at the script to substract second column values from the first column values in a mouse selected area. See images....first image: selected area before substracting, second image: selected area after substracting.

[Prospekteur]

Does anyone have a solution or idea to handle the above task? Couldnt get it to work:-(

[ragardner]

A couple of questions,

• Would it use the columns the user has selected or are the columns hard coded? e.g. 1 and 2
• If it uses the columns the user has selected would it only use the first two columns or subtract all columns after the first from the first e.g. subtract 2, 3, 4 from 1

[Prospekteur]

Hello, Im so happy to hear from you;-)

User mouse selects an particular area of "two" adjacent columns as in the image above. The selected values of the second column should be substracted from the selected values of the first column. I only need a solution for the first two selected columns. User select an area of "two" adjacent columns, in this selected area the values of second column should be substracted from the values of the first column.

Explanation of the posted images above:

First image: Selected area before substracting second column values from the first column values

Second image: Results after substracting second column values from the first column values

[ragardner]

If your data is in string format you'll have to first convert strings to floats or something before subtracting

edit: this code doesn't check the columns are next to each other, you'll have to add that check if you need it

from tksheet import *
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)

        data = [[r for c in range(6)] for r in range(50)]

        self.sheet = Sheet(
            self.frame,
            expand_sheet_if_paste_too_big=True,
            data=data,
            height=520,
            width=930,
        )
        self.sheet.enable_bindings("all", "edit_index", "edit header", "ctrl_select")

        self.sheet.popup_menu_add_command(
            "subtract values",
            self.subtract_values,
        )

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

    def subtract_values(self, event=None):
        # to determine if user has cell area or columns selected
        columns = sorted(self.sheet.get_selected_columns())
        if len(columns) < 2:
            # less than 2 columns selected, perhaps the user has a cell area selected
            columns = sorted(self.sheet.get_selected_columns(get_cells_as_columns=True))
            rows = self.sheet.get_selected_rows(get_cells_as_rows=True)
        else:
            # user has 2 or more columns selected, use all rows of sheet for calc
            rows = range(len(self.sheet.MT.data))
        if len(columns) < 2:
            return
        col_one = columns[0]
        col_two = columns[1]
        for row in rows:
            try:
                # this is where you may need to convert your data
                val_one = self.sheet.get_cell_data(row, col_one)
                val_two = self.sheet.get_cell_data(row, col_two)
                new_val = val_one - val_two
                self.sheet.set_cell_data(row,
                                            col_one,
                                            value=new_val,
                                            redraw=False)
            except Exception as error:
                # print (error)
                continue
        self.sheet.refresh()

app = demo()
app.mainloop()

[Prospekteur]

Ive implemented your code into my programme........it works like a dream!!! Im so happy!! You are a genius! Is there a way to support your work with a little donation?

Thanks a lot.....you are awesome;-)

[ragardner]

Hey, I'm glad to hear it's working! thanks for letting me know

if you really want to support my work I have a page at buy me a coffee

https://www.buymeacoffee.com/ragardner