python - How to iterate over all rows in an Excel Table using OpenPyXL?

Question

The documentation of OpenPyXL for Excel Tables don't mention how to iterate over values in the table (see here).

What would be an effecient way of doing it?

question from:https://stackoverflow.com/questions/65895165/how-to-iterate-over-all-rows-in-an-excel-table-using-openpyxl

Answer 1

I came up with the following function to do it.

from typing import Any, Dict, Generator

from openpyxl.worksheet.table import Table
from openpyxl.utils import rows_from_range

TableRow = Dict[str, Any]

def iter_table_rows(tb:Table) -> Generator[TableRow, None, None]:
    """Iterate over rows from a table with headers (row as dictionary)"""
    def get_row_values(row_cell_ref):
        return [ws[c].value for c in row_cell_ref]
    
    iter_rows = rows_from_range(tb.ref)
    headers = get_row_values(next(iter_rows))
    
    for row_cells in iter_rows:
        yield {h:v for h,v in zip(headers, get_row_values(row_cells))}


tb = ws.tables["MyTable"]
for row in iter_table_rows(tb):
    print(row)