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?
2 Answers
I came up with the following function to do it.
from typing import Any, Dict, Generator
from openpyxl import load_workbook
from openpyxl.worksheet.table import Table
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.utils import rows_from_range
TableRow = Dict[str, Any]
def iter_table_rows(ws:Worksheet, 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))}
wb = load_workbook("my_file.xlsx")
ws = wb.active
tb = ws.tables["MyTable"]
for row in iter_table_rows(ws, tb):
print(row)
Thank you for the function. I 've added a ws argument, as ws is not a global variable and his required for the get_row_values.
def iter_table_rows(ws:Worksheet, tb:Table) -> Generator[TableRow, None, None]:
"""Iterate over rows from a table with headers (row as dictionary)"""
def get_row_values(ws:Worksheet,row_cell_ref:tuple):
return [ws[c].value for c in row_cell_ref]
iter_rows = rows_from_range(tb.ref)
headers = get_row_values(ws,next(iter_rows))
for row_cells in iter_rows:
yield {h:v for h,v in zip(headers, get_row_values(ws,row_cells))}
I've not seen other way to do it for the moment.
And there is no "parent" attribute to know in which worksheet is the table.
Well at the end I come to a different solution.
Here is a code of a function to extract data from a xlsx table in a json file.
I'm guessing it's more near the philosophy of the openpyxl. Data are stored in worksheet, and table seems to be a kind of pointer to a range. The most efficient way to read data in a worksheet is to use ws.iter_row specifiying the range based on min_row,max_row,min_col,max_col.
import json
import logging
from argparse import Namespace
from openpyxl import load_workbook
from openpyxl.worksheet.cell_range import CellRange
def xlsx_Json(args: Namespace):
"""Extract table values from an excel workbook in a json file
Args:
args (Namespace): parsed arguments
args.workbook : filename of the excel document
args.worksheet : name of the sheet to read
args.table : name of a table in worksheet
args.output : name of a output file (json format)
Note:
It's assumed that table exists in worksheet, and worksheet exists in workbook, and file exists
"""
#wb = load_workbook(filename=args.workbook,read_only=True) !!! read only worksheet doesn't have table ?
wb = load_workbook(filename=args.workbook)
ws = wb[args.worksheet]
table = ws.tables[args.table]
# table.ref needs to be converted in a CellRange, and use the range to read data in the worksheet
table_range = CellRange(table.ref)
tableJson={ args.table : [] }
header=table.column_names
for nb_rows,row in enumerate(ws.iter_rows(min_row=table_range.min_row+1,
min_col=table_range.min_col,
max_row=table_range.max_row,
max_col=table_range.max_col,
values_only=True )):
# min_row=range.min_row+1 skip the header row
# need to convert row element to string to make a serializablejson object
# value such datetime are not directly serializable
if nb_rows == 0:
for h,x in zip(header,row):
logging.info(f"{h}:type-{type(x)}:value:{x}")
if nb_rows % 100 == 0:
# print row number every 100 lines
logging.info(f"rows:{nb_rows}")
row_Json=dict(zip(header,[str(x) for x in row]))
tableJson[args.table].append(row_Json)
with open(args.json_output,"w") as file_out:
json.dump(tableJson,file_out,indent=4)
logging.info(f"{nb_rows} elements written in {args.json_output}")
Related