xlsx-parsing

xlsx-parsing

Excel workbooks are the lingua franca of operational documents that nobody bothered to put in a database — playbooks, rate cards, deviation policies, finance models, SLAs. They show up in tasks with three properties that trip up naive readers:

1. Multiple sheets, only one of which is the data you actually want.
2. Sparse cells — a row that uses a column may sit next to a row that doesn't, leaving None cells. Empty is meaningful (the rule does not apply), not an error.
3. Composite cells — a single cell that contains a comma-separated list, a JSON blob, or a sentence rather than an atomic value.

Treat the workbook as a typed table with declared columns, not a free-form spreadsheet. Read every sheet you need, normalise it to list[dict[str, Any]], then operate on that.

Reading with openpyxl (pure Python, no compiled dependencies)

import openpyxl

wb = openpyxl.load_workbook("workbook.xlsx", data_only=True, read_only=True)
print(wb.sheetnames)            # e.g., ['Metadata', 'Definitions', 'Rules']

ws = wb["Rules"]
rows = ws.iter_rows(values_only=True)
header = [str(c).strip() if c else "" for c in next(rows)]
records = [dict(zip(header, row)) for row in rows if any(cell is not None for cell in row)]

Notes:

• data_only=True returns the cached value of formula cells instead of the formula expression. Without this you may get strings like "=A1+B2".
• read_only=True is faster on big workbooks and avoids loading styles you don't need.
• The any(cell is not None ...) filter drops entirely-blank rows that Excel preserves at the bottom of a sheet.
• dict(zip(header, row)) handles trailing empty columns gracefully when a row is shorter than the header.

Reading with pandas (if it's installed)

import pandas as pd

# Multi-sheet read returns a dict of DataFrames
sheets = pd.read_excel("workbook.xlsx", sheet_name=None, dtype=object)
rules_df = sheets["Rules"]

# Drop fully-empty rows; keep partial rows
rules_df = rules_df.dropna(how="all")

# Iterate as dicts; NaN becomes None
records = rules_df.where(rules_df.notna(), None).to_dict(orient="records")