How to open an Excel Spreadsheet in Treeview widget in Tkinter?

An Excel Spreadsheet contains data organized in rows and columns. You can display spreadsheet data in a Tkinter application using the Treeview widget, which allows tabular data presentation. The Pandas library provides powerful tools for reading and manipulating Excel files in Python.

This tutorial demonstrates how to create a Tkinter application that opens Excel files and displays their contents in a Treeview widget with a file menu interface.

Required Libraries

You'll need to import the following libraries ?

• tkinter ? GUI framework for creating the interface
• pandas ? Reading and processing Excel files
• filedialog ? Opening file explorer dialog

Implementation Steps

Follow these key steps to build the Excel viewer ?

1. Create the main Tkinter window with menu bar
2. Define a file opening function that accepts .xlsx files
3. Create a Treeview widget to display data
4. Convert DataFrame columns to Treeview headings
5. Insert DataFrame rows into the Treeview

Complete Example

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

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

# Set the size of the tkinter window
win.geometry("700x350")
win.title("Excel Spreadsheet Viewer")

# 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", 
        filetypes=(("Excel files", "*.xlsx"), ("All Files", "*.*"))
    )
    
    if filename:
        try:
            # Read the Excel file using pandas
            df = pd.read_excel(filename)
            
            # Clear all the previous data in tree
            clear_treeview()
            
            # Configure Treeview columns
            tree["columns"] = list(df.columns)
            tree["show"] = "headings"
            
            # Set column headings
            for col in tree["columns"]:
                tree.heading(col, text=col)
                tree.column(col, width=100)
            
            # Insert data rows
            df_rows = df.to_numpy().tolist()
            for row in df_rows:
                tree.insert("", "end", values=row)
            
            # Update status
            label.config(text=f"Loaded: {filename.split('/')[-1]}")
            tree.pack(fill=BOTH, expand=True)
            
        except ValueError:
            label.config(text="Error: Invalid file format")
        except FileNotFoundError:
            label.config(text="Error: File not found")
        except Exception as e:
            label.config(text=f"Error: {str(e)}")

# Clear the Treeview Widget
def clear_treeview():
    for item in tree.get_children():
        tree.delete(item)

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

# Add scrollbars
v_scrollbar = ttk.Scrollbar(frame, orient="vertical", command=tree.yview)
h_scrollbar = ttk.Scrollbar(frame, orient="horizontal", command=tree.xview)
tree.configure(yscrollcommand=v_scrollbar.set, xscrollcommand=h_scrollbar.set)

v_scrollbar.pack(side="right", fill="y")
h_scrollbar.pack(side="bottom", fill="x")

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

# Add Menu Dropdown
file_menu = Menu(menubar, tearoff=False)
menubar.add_cascade(label="File", menu=file_menu)
file_menu.add_command(label="Open Spreadsheet", command=open_file)
file_menu.add_separator()
file_menu.add_command(label="Exit", command=win.quit)

# Add a Label widget to display status
label = Label(win, text='Select "File ? Open Spreadsheet" to load an Excel file')
label.pack(pady=10)

win.mainloop()

How It Works

The application works through these key components ?

• File Dialog ? Opens a file browser to select .xlsx files
• Pandas Integration ? Uses pd.read_excel() to read Excel data
• Treeview Configuration ? Sets columns and headings from DataFrame
• Data Population ? Converts DataFrame to list and inserts rows
• Error Handling ? Catches file errors and displays appropriate messages

Key Features

The enhanced version includes ?

Output

Running the application displays a window with a menu bar. When you select "File ? Open Spreadsheet", a file dialog opens to choose an Excel file. The spreadsheet data then appears in the Treeview widget with proper column headers and scrollable rows.

Conclusion

This implementation demonstrates how to integrate Pandas with Tkinter to create a functional Excel viewer. The Treeview widget effectively displays tabular data while the file dialog provides an intuitive way to load spreadsheets. The application handles errors gracefully and includes scrollbars for better navigation of large datasets.