Open Excel Spreadsheet in Treeview Widget in Tkinter

An Excel Spreadsheet contains a set of information stored in the form of rows and columns. We can display and use the spreadsheet data in the Tkinter application using Treeview widget. The Treeview widget in Tkinter helps users to add and manipulate the data in the form of a table. However, to analyze and manipulate a large set of data, Python provides Pandas library which gives access to many inbuilt functions and methods to perform data analysis.

For this example, we will follow these steps to display the Excel Data in Tkinter,

Import the required Libraries such as Numpy, Pandas, and filedialog.
Add a Menu Bar to ask the user to open the file from the Explorer.
Add Command and define a function open_file() such that it accepts only .xlsx files from the explorer.
Create a Treeview Widget.
Add columns in the Treeview widget by converting the column data into a list.
Iterate over the column to find all the Heading in the given data.
Rows can be identified by converting the given data frame into NumPy object. Once we have converted then we can use the list method to convert it into a list.
Iterate over all the rows and insert the rows sequentially in the tree.

Example

# Import the required libraries
from tkinter import *
from tkinter import ttk, filedialog
import numpy
import pandas as pd

# Create an instance of tkinter frame
win = Tk()

# Set the size of the tkinter window
win.geometry("700x350")

# Create an object of Style widget
style = ttk.Style()
style.theme_use('clam')

# Create a Frame
frame = Frame(win)
frame.pack(pady=20)
# Define a function for opening the file
def open_file():
   filename = filedialog.askopenfilename(title="Open a File", filetype=(("xlxs files", ".*xlsx"),
("All Files", "*.")))

   if filename:
      try:
         filename = r"{}".format(filename)
         df = pd.read_excel(filename)
      except ValueError:
         label.config(text="File could not be opened")
      except FileNotFoundError:
         label.config(text="File Not Found")

   # Clear all the previous data in tree
   clear_treeview()

   # Add new data in Treeview widget
   tree["column"] = list(df.columns)
   tree["show"] = "headings"

   # For Headings iterate over the columns
   for col in tree["column"]:
      tree.heading(col, text=col)

   # Put Data in Rows
   df_rows = df.to_numpy().tolist()
      for row in df_rows:
         tree.insert("", "end", values=row)

   tree.pack()

# Clear the Treeview Widget
def clear_treeview():
   tree.delete(*tree.get_children())

# Create a Treeview widget
tree = ttk.Treeview(frame)

# Add a Menu
m = Menu(win)
win.config(menu=m)

# Add Menu Dropdown
file_menu = Menu(m, tearoff=False)
m.add_cascade(label="Menu", menu=file_menu)
file_menu.add_command(label="Open Spreadsheet", command=open_file)

# Add a Label widget to display the file content
label = Label(win, text='')
label.pack(pady=20)

win.mainloop()