Display Excel Data in Tkinter Treeview using OpenPyXL

We can read rows from an Excel worksheet with OpenPyXL and display the data in a Tkinter Treeview. The Excel header row can be used as the visible Treeview headings, while each following worksheet row becomes one Treeview item.

This approach is useful when the number or names of Excel columns are not known in advance because the Treeview columns can be created dynamically from the worksheet.

Install OpenPyXL 🔝

Install OpenPyXL if it is not already available:

pip install openpyxl

Read the Excel Worksheet 🔝

The sample workbook contains a worksheet named student.

from openpyxl import load_workbook

wb=load_workbook('student.xlsx', read_only=True, data_only=True)
ws=wb['student']

Using a relative filename makes the example portable. Place student.xlsx in the same directory as the Python script, or provide the correct relative path.

Read the Excel Header and Data Rows 🔝

iter_rows() can return the cell values directly by using values_only=True.

Read the First Row as Headers

headers=next(ws.iter_rows(min_row=1, max_row=1, values_only=True))
print(headers)

Sample output:

('id', 'name', 'class', 'mark', 'gender')

Read the Remaining Rows

rows=list(ws.iter_rows(min_row=2, values_only=True))

Each item in rows is a tuple representing one Excel row.

Create the Tkinter Treeview 🔝

import tkinter as tk
from tkinter import ttk

root=tk.Tk()
root.geometry('650x320')
root.title('Excel Data in Treeview - plus2net')

tree=ttk.Treeview(root, show='headings', selectmode='browse', height=8)

Because this example displays spreadsheet-style data rather than parent-child hierarchy, show='headings' is appropriate.

Create Treeview Columns Dynamically 🔝

Excel headings should be used as the text displayed to the user. However, blank or duplicate Excel headings can cause problems when used directly as Treeview column identifiers.

Create safe internal column IDs instead:

column_ids=[f'col{i}' for i in range(len(headers))]
tree['columns']=column_ids

Then display the actual Excel headings:

for column_id, heading in zip(column_ids, headers):
    heading_text=str(heading) if heading is not None else ''
    tree.heading(column_id, text=heading_text)
    tree.column(column_id, width=110, anchor='center')

Insert Excel Rows into Treeview 🔝

for row in rows:
    tree.insert('', tk.END, values=row)

Treeview automatically generates an item ID for every row.

This is safer than:

tree.insert('', tk.END, iid=row[0], values=row)

unless the first Excel column is guaranteed to contain a unique, non-empty value for every record.

Add Vertical and Horizontal Scrollbars 🔝

Excel files can contain more rows or columns than the application window can display.

ys=ttk.Scrollbar(root, orient='vertical', command=tree.yview)
xs=ttk.Scrollbar(root, orient='horizontal', command=tree.xview)

tree.configure(yscrollcommand=ys.set, xscrollcommand=xs.set)

Complete Excel to Treeview Example 🔝

import tkinter as tk
from tkinter import ttk
from openpyxl import load_workbook

wb=load_workbook('student.xlsx', read_only=True, data_only=True)
ws=wb['student']

headers=next(ws.iter_rows(min_row=1, max_row=1, values_only=True))
rows=list(ws.iter_rows(min_row=2, values_only=True))
wb.close()

root=tk.Tk()
root.geometry('700x330')
root.title('Excel Data in Treeview - plus2net')

root.rowconfigure(0, weight=1)
root.columnconfigure(0, weight=1)

tree=ttk.Treeview(root, show='headings', selectmode='browse')
tree.grid(row=0, column=0, sticky='nsew', padx=(10, 0), pady=(10, 0))

ys=ttk.Scrollbar(root, orient='vertical', command=tree.yview)
ys.grid(row=0, column=1, sticky='ns', pady=(10, 0))

xs=ttk.Scrollbar(root, orient='horizontal', command=tree.xview)
xs.grid(row=1, column=0, sticky='ew', padx=(10, 0))

tree.configure(yscrollcommand=ys.set, xscrollcommand=xs.set)

column_ids=[f'col{i}' for i in range(len(headers))]
tree['columns']=column_ids

for column_id, heading in zip(column_ids, headers):
    heading_text=str(heading) if heading is not None else ''
    tree.heading(column_id, text=heading_text)
    tree.column(column_id, width=110, minwidth=60, anchor='center')

for row in rows:
    tree.insert('', tk.END, values=row)

root.mainloop()

Limit Excel Rows and Columns 🔝

iter_rows() supports limits for rows and columns.

rows=ws.iter_rows(min_row=2, max_row=20, min_col=1, max_col=5, values_only=True)

Display Excel Formula Results 🔝

When loading a workbook with:

wb=load_workbook('student.xlsx', data_only=True)

cells containing formulas return the cached result stored in the Excel file, when one is available.

If data_only=False, the formula expression itself is returned instead.

Common Excel to Treeview Mistakes 🔝

1. Using an Absolute Machine-Specific Path

Avoid:

D:\student.xlsx

when the workbook can be placed with the program:

student.xlsx

2. Using Excel Headings Directly as Internal Column IDs

Blank or duplicate headings can create confusing Treeview definitions. Use internal IDs such as:

col0
col1
col2

and use the Excel values only as visible heading text.

3. Using the First Excel Value as iid

This can fail when the value is duplicated or missing. Let Treeview create its own item IDs unless the worksheet contains a guaranteed unique key.

4. Forgetting values_only=True

Without it, iter_rows() returns Cell objects rather than their values.

5. Forgetting to Close the Workbook

wb.close()

is especially relevant when a workbook is opened in read-only mode.

6. Loading a Large Workbook into a List

The simple tutorial uses:

rows=list(ws.iter_rows(...))

which is convenient for a small sample workbook. For a very large worksheet, process rows directly from the iterator instead of first copying every row into a Python list.

Excel Data in Treeview Summary 🔝

• OpenPyXL reads data from .xlsx workbooks.
• load_workbook() opens the Excel file.
• read_only=True is useful when only reading workbook content.
• iter_rows() reads worksheet rows.
• values_only=True returns cell values.
• The first Excel row can provide the visible Treeview headings.
• Use safe internal Treeview column identifiers.
• Each following Excel row can be inserted using values=.
• Let Treeview generate item IDs unless the Excel data contains a guaranteed unique key.
• show='headings' is appropriate for spreadsheet-style tables.
• Horizontal and vertical scrollbars help with larger worksheets.
• data_only=True can return cached formula results.
• OpenPyXL itself does not calculate Excel formulas.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: