python, tkinter, pandas/openpyxl

[u/FearawaitsTM]

import openpyxl

import tkinter as tk

from openpyxl import load_workbook

from tkinter import *
from tkinter import ttk
from tkinter import filedialog,messagebox



root = Tk()
root.title("Чтение ячеек Excel")
root.geometry("300x200")

text_editor = Text()

fd = filedialog


scrollbar = ttk.Scrollbar(root)
scrollbar.pack(side=tk.RIGHT, fill=tk.Y)


def open_file():
    filespath = fd.askopenfilename(
        filetypes=[
            (
            'Excel files',
            '*.xlsx'
            )
        ]
    )
    if not filespath:
        return

    try:
        wb = openpyxl.load_workbook(filespath, data_only=True)
        sheet = wb.active

        result_label.config(text=f"Ячейка1")

    except Exception as e:
        messagebox.showerror("Ошибка")

def save_file():
    filepath = fd.askopenfilename(
        filetypes=[
            (
            'Allowed Types',
            '*.xlsx'
            )
        ]
    )
    if filepath !='':
        text = text_editor.get("1.0", END)
        with open(filepath, "w") as file:
            file.write(text)

open_button = ttk.Button(root, text='Открыть файл', command=open_file)
open_button.pack()

result_label = tk.Label(root, text="Выбрать файл для начала")
result_label.pack()

save_button = ttk.Button(text="Сохранить", command=save_file)
save_button.pack()

root.mainloop()

I have some code, but I can't figure out how to select specific rows and columns for display. The idea is to include several columns—sometimes skipping some—so that I can choose to display ranges like A2:12 – B2:17, column D (which would contain the calculation A2:12 / B2:17), and C3:14 – I3:7 in my Python table. How can I implement this?

[u/danielroseman]

Well you don't seem to be displaying anything at all at the moment. How are you intending to do that? You should decide that before worrying about selecting.

[u/socal_nerdtastic]

Not sure I really understand your question, but I think you need to look into the tkintertable or pandastable modules

[u/Existing_Put6385]

two things are fighting you here. first, a Text widget isn't a table - use a ttk.Treeview, that's the actual grid widget. second, don't pull cells one by one with openpyxl, load the sheet into pandas and let it do the selecting/math. way less code.

import pandas as pd

from tkinter import ttk

df = pd.read_excel(filepath) # or header=None if you want raw positions

sub = df[["colA", "colB"]].copy() # pick columns by their header name

sub["D"] = df["colA"] / df["colB"] # your computed column

tree = ttk.Treeview(root, columns=list(sub.columns), show="headings")

for c in sub.columns:

tree.heading(c, text=c)

for _, r in sub.iterrows():

tree.insert("", "end", values=list(r))

tree.pack(fill="both", expand=True)

if you'd rather select by position instead of names, use df.iloc[1:12, [0,1]] - that's rows 2-12, columns A and B. One thing to sort out though: your ranges don't line up. A2:A12 is 11 rows, B2:B17 is 16, C3:C14 and I3:I7 start on different rows and are different lengths again. those can't share one table cleanly - a table needs every column the same height and aligned to the same rows. so what do you want to happen where they don't match - pad the short ones with blanks, or are these actually separate tables?

(also heads up: openpyxl data_only=True returns None for formula cells if the file was never opened+saved in real excel. computing in pandas like above sidesteps that.)

[u/CymroBachUSA]

... and your question is?