How should I create a table with dynamic column width by importing data from an excel file using tkinter Python?

Question

I want to import all data(headers, columns, rows) from an any sample MS Excel spreadsheet to a tkinter frame. I have tried to do it but didn't work. Can someone help me with this task ? Thank you!

This is the code I developed until now, but in this code there are buttons to import data via columns. What my expectation is to directly import all the rows, columns as it is in the excel file without any button clicks. Also the columns width should be re-sizable.

from tkinter import *
from openpyxl.workbook import Workbook
from openpyxl import load_workbook

root = Tk()
root.title('Excel Sheet via Tkinter')
# root.iconbitmap('')
root.geometry("500x800")

# Create workbook instance
wb = Workbook()

# Load existing workbook
wb = load_workbook('test.xlsx')

# Create active worksheet
ws = wb.active

# Create variable for Column A
column_a = ws['A']
column_b = ws['B']

def get_a():
    list = ''
    for cell in column_a:
        list = f'{list + str(cell.value)}\n'
    
    label_a.config(text=list)
    
def get_b():
    list = ''
    for cell in column_b:
        list = f'{list + str(cell.value)}\n'
    
    label_b.config(text=list)

ba = Button(root, text="Get Column A", command=get_a)
ba.pack(pady=20)

label_a = Label(root, text="")
label_a.pack(pady=20)

bb = Button(root, text="Get Column B", command=get_b)
bb.pack(pady=20)

label_b = Label(root, text="")
label_b.pack(pady=20)

root.mainloop()

PS : I'm very new to Python so please bare with me.

Answer 1

Use read only mode to get all data from excel to list in Openpyxl, each sublist represent a row so you can find column number by len() on each row. Then back to tkinter create number of frames that match total number of rows from list. Each frames create number of note that determine by number of columns each row. And assign all cell’s info from list to tkinker’s notes

Answer 2

this is just the basic frame work, and you are welcome to adjust things such as color and size in tkinter. try it:

def excel_data_to_list(excel):
    from openpyxl import load_workbook
    wb = load_workbook(filename=excel, read_only=True)
    ws = wb.worksheets[0]  # you can change it by making an arguments to input sheetname,
    info = []
    for row in ws.rows:
        info_row =[]
        for cell in row:
            info_row.append(cell.value)
        info.append(info_row)
    wb.close()
    return info

def excel_sheet_via_tkinter(excel):
    import tkinter as tk
    root = tk.Tk()
    root.title('Excel Sheet via Tkinter')
    root.geometry("500x800")   # you may want to define a better size to fit all data from excel

    info       = excel_data_to_list(excel)
    for row_num, row in enumerate(info):
        for col_num, cell in enumerate(row):
            label = tk.Label(root, text=cell)    # you can add color in each label, this code hasn't involve this part yet
            label.grid(row=row_num, column=col_num)
    root.mainloop()

# run everything now!!!
excel = 'import_export.xlsx'   # this is my file name, but input your
excel_sheet_via_tkinter(excel)