Displaying MS Excel data in Treeview using openpyxl library

Reading Excel file and displaying data in Tkinter Treeview by using Python Openpyxl library

Download sample ( Excel file ) student.xlsx Reading from Excel using openpyxl

Reading excel file and getting data ( rows )

Read the student.xlsx page and create two lists. One list ( l1 here ) for the column headers and other list r_set is for the list of data.

Opened the workbook ( wb ) based on the path of the student.xlsx file. This workbook has one worksheet student. Inside the worksheet we will get rows of data by using iter_rows().

Here we can manage the number of rows and columns required by using the parameters min_row, min_column, max_row,max_column.

from openpyxl import load_workbook
wb = load_workbook(filename='D:\student.xlsx', read_only=False)
ws = wb['student'] # connecting to work sheet

l1=ws.iter_rows(min_row=1,max_row=1,max_col=5,values_only=True)
r_set=ws.iter_rows(min_row=2,max_row=5,values_only=True)

l1=[r for r in l1] # Prepare list for column headers 
r_set=[r for r in r_set] # Prepare list with data 
wb.close()# Close the workbook after reading
#print(l1) # to check the headers

Tkinter Treeview

We are adding one Treeview to the Tkinter window.

from tkinter import ttk # for treeview 
import tkinter as tk
my_w = tk.Tk() # Main window 
my_w.geometry("560x280") # width and hight of window 
my_w.title("www.plus2net.com")  
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=0,column=0,columnspan=3,padx=30,pady=20)


my_w.mainloop()

Adding dynamically the headers and data by using the header list ( l1 ) and data list ( r_set ) from above.

trv['height']=5 # Number of rows to display, default is 10
trv['show'] = 'headings' 
# column identifiers 
trv["columns"] = l1[0]
# Defining headings, other option in tree
# width of columns and alignment 
for i in l1[0]:
    trv.column(i, width = 100, anchor ='c')
# Headings of respective columns
for i in l1[0]:
    trv.heading(i, text =i)

## Adding data to treeview 
for dt in r_set:  
    trv.insert("",'end',iid=dt[0],values=dt) # adding row

Full code is here

from openpyxl import load_workbook
wb = load_workbook(filename='D:\student.xlsx', read_only=False)
ws = wb['student'] # connecting to work sheet
l1=ws.iter_rows(min_row=1,max_row=1,max_col=5,values_only=True)
r_set=ws.iter_rows(min_row=2,max_row=5,values_only=True)
#print(list(l1))
l1=[r for r in l1] # Prepare list for column headers 
r_set=[r for r in r_set] # Prepare list with data 
wb.close()# Close the workbook after reading
#print(l1) # to check the headers 

from tkinter import ttk # for treeview 
import tkinter as tk
my_w = tk.Tk() # Main window 
my_w.geometry("560x280") # width and hight of window 
my_w.title("www.plus2net.com")  
# Using treeview widget
trv = ttk.Treeview(my_w, selectmode ='browse')
trv.grid(row=0,column=0,columnspan=3,padx=30,pady=20)

trv['height']=5 # Number of rows to display, default is 10
trv['show'] = 'headings' 
# column identifiers 
trv["columns"] = l1[0]
# Defining headings, other option in tree
# width of columns and alignment 
for i in l1[0]:
    trv.column(i, width = 100, anchor ='c')
# Headings of respective columns
for i in l1[0]:
    trv.heading(i, text =i)

## Adding data to treeview 
for dt in r_set:  
    trv.insert("",'end',iid=dt[0],values=dt) # adding row
my_w.mainloop()

Display MySQL records in Treeview Pagination of MySQL records in Treeview
Displaying MySQL records using Entry or Label Dynamic Creation of Header & Columns in Treeview
Delete MySQL record Treeview Treeview insert Treeview parent child node Select -Edit-update MySQL Product table using Treeview

Python Tkinter Dictionary Python- Tutorials

Subscribe to our YouTube Channel here

Treeview using data from Excel

Reading excel file and getting data ( rows )

Tkinter Treeview

Subscribe