Skip to content

Iterating over tables in an existing Excel workbook with OpenPyXL

Picture of Conny Söderholm

There is a way to iterate over Excel tables with OpenPyXL. The functionality has changed in newer versions of OpenPyXL. The iterator is accessed from the worksheets tables property. 

First we load the workbook and set the active sheet:

from openpyxl import load_workbook
FILENAME = "Tables.xlsx"
wb = load_workbook(FILENAME)
ws = wb.active
 

In order to access all tables, we can do a for loop of the worksheet.tables.values(). After that we can get the attributes of the table, for example, the name with table.name and the tables reference with table.ref

When we have the Worksheet object ws we can then iterate over the tables.

for table in ws.tables.values():
    print(table.name, table.ref)
 

The Complete Code

Source code is available on my GitHub

#!/usr/bin/env python3

"""
Iterating over tables in an existing workbook
"""
from openpyxl import load_workbook

FILENAME = "Tables.xlsx"

wb = load_workbook(FILENAME)

ws = wb.active

for table in ws.tables.values():
    print(table.name, table.ref)

 
, ,