A simple openpyxl field-scanning script pulled a whole thread of engineering decisions behind Excel-parsing engine selection: the underlying structure of XLSX files, openpyxl’s performance ceiling, how the Rust-written python-calamine replaces it in ETL pipelines, and finally the broader Polars ecosystem plus an actual code rewrite. This post follows the conversation’s progression start to finish.

Part 1: How openpyxl Reads Files — and the Interview-Level Deep Dive

🎯 The 30-Second Version

openpyxl doesn’t read Excel’s “now” — it reads the “then” of whenever the file was last saved. An XLSX file is really a ZIP archive full of XML documents (sheet1.xml, sharedStrings.xml, etc). openpyxl parses these into a Python object tree, and data_only=True gives you the formula result cached at last save time, not the formula itself. Using iter_rows as a generator to scan row by row and match substrings is essentially “structured grep.”

⚙️ Under the Hood

• XLSX = ZIP + XML: unzip -l xxx.xlsx shows xl/worksheets/sheet1.xml, xl/sharedStrings.xml, xl/styles.xml. Strings live in a shared-string table (a dedup dictionary) by default — cells store indices, not the raw text. That’s why a workbook with tens of thousands of repeated strings doesn’t bloat much on disk.
• The data_only=True trap: a formula cell’s cached value only exists if the file was opened and saved by Excel (or LibreOffice) at least once since the formula was written. If the file was generated by a script and never touched by Excel, data_only=True returns None — not the formula string, not a computed result.
• Memory model: load_workbook builds the entire workbook into memory (DOM-style) by default. For large files, use read_only=True — only then does iter_rows become a genuinely streaming generator (backed internally by lxml.etree.iterparse’s incremental XML parsing), rather than loading everything and slicing.

🔬 The Interviewer’s Follow-Up Chain

Q1: This xlsx has 500K rows and 200 columns. What happens with this code? A: The default mode builds the entire DOM tree in memory — easy OOM or a hang. The fix is load_workbook(path, data_only=True, read_only=True), which makes iter_rows a truly lazy generator — O(1) memory instead of O(rows × columns).

Q2: Can you still access row[0].row in read_only mode? What’s the catch? A: Yes, Cell.row/column still work. But read_only mode is not writable, and it doesn’t support random access (no ws['A100'] jumps) — only sequential iteration. You trade random access for constant memory.

Q3: What’s the complexity of joining each row into a string and doing substring matching? A: str(c.value) + join per row is O(columns), and substring search is O(string length). But materializing a full string every row means repeated allocations — under 500K rows the GC pressure is heavier than you’d think. Better to filter with a generator instead of building a full string every time.

Q4: Is it safe to read the same workbook concurrently from multiple threads? A: openpyxl’s Workbook object is not thread-safe. The right pattern is each worker calling load_workbook independently for its own read-only handle, not sharing one wb object across threads.

Q5: What’s the biggest failure mode here? A: Silently returning wrong data instead of erroring. data_only=True returning None, a mistyped sheet name causing KeyError, or a missing file causing FileNotFoundError — all need explicit try/except guards, or a silent None will leak into your matching logic.

🏗️ How Big Tech Actually Uses This

• Data ingestion layer: business teams will always hand you Excel files. ETL pipelines standardize on an “xlsx → parquet/csv” normalization adapter — for large files, this typically skips pure-Python openpyxl in favor of Rust-backed calamine or Polars’ read_excel.
• Schema mapping / data lineage scanning: many companies’ field-lineage tools start by scanning mapping documents for field names, then hook into the dbt/Airflow schema registry.

💸 The High-Stakes Version (Finance)

Correctness beats speed:

• Define the expected field set with pydantic/jsonschema and diff it against what was actually read — fail CI on any mismatch.
• Log the file’s SHA256 hash, read timestamp, and who read it, for audit trails.
• Formula traps are a real incident source — add a check comparing the file’s last-modified time against the cached-value timestamp, to catch “looks right but is actually stale” silent bugs.

🌉 The Cross-Discipline Lens

This is like stratigraphy in archaeology: you’re not looking at “live” data, you’re excavating a “layer that solidified at the last save.” The cached formula value under data_only=True is like an insect trapped in amber — it precisely preserves one historical moment, but if the layer was disturbed afterward (formula recalculated but never re-saved), what you dig up may be a “ghost version” that no longer exists.

Part 2: The Engineering Logic Behind python-calamine Replacing openpyxl

🎯 The 30-Second Version

openpyxl is a pure-Python XML parser — reading a large xlsx means repeatedly building a DOM tree at the Python layer, which is slow and memory-hungry. calamine is a Rust-written xlsx/xls/ods parsing library exposed to Python via PyO3 bindings, pushing the most expensive part — XML parsing and string handling — down into compiled code. ETL is read-only, so there’s no reason to pay the pure-Python parsing tax for write-only features (formulas, styling) you’ll never use.

⚙️ Under the Hood

• openpyxl’s bottleneck: every <c> tag triggers a Python object construction (a Cell instance), type inference, and a shared-string-table lookup. All of this runs in Python interpreter loops — function call overhead and object allocation add up fast at millions of cells.
• What calamine does: the entire parse happens in Rust, producing a compact in-memory structure in one pass, then handed to Python once via PyO3. The key insight: the number of cross-language boundary crossings drops from O(cells) to O(1) or O(sheets).
• Relationship to parquet: parquet is columnar storage with dictionary encoding. xlsx’s shared-string table is already a primitive form of dictionary encoding, so “xlsx → parquet” is really “row-oriented + dict-encoded → columnar + dict-encoded” — which is also why calamine feeds naturally into Polars (Arrow columnar internally) rather than pandas (numpy-backed, row/column semantics blurred).

🔬 The Interviewer’s Follow-Up Chain

Q1: calamine is an order of magnitude faster — is that CPU or I/O bound? A: CPU-bound parsing overhead, not I/O. The xlsx file itself is small (ZIP-compressed), so I/O time is negligible. The bottleneck is turning XML text into structured data — object allocation and type checking — where Rust’s zero-cost abstractions and lack of GC crush CPython.

Q2: What does calamine do with formulas in the xlsx? A: It only reads cached values (similar to data_only=True), doesn’t evaluate formulas, and doesn’t expose the formula text either. If you need to see, edit, or store formulas, openpyxl remains the only option.

Q3: What happens with merged cells or multi-level headers when Polars uses the calamine engine? A: Merged cells only have a value in the top-left cell — the rest are None, same as openpyxl; you still have to forward-fill at the business layer. Type inference is stricter with calamine/Polars though — a column mixing strings and numbers tends to get coerced into one dtype, with mismatches becoming null or an error. This is the biggest gotcha when migrating “dirty” hand-filled spreadsheets.

Q4: How should a production normalization adapter be designed for fault tolerance? A: Three layers of defense: ① format detection (check magic bytes to catch mislabeled files); ② schema validation (pydantic/pandera checks column names/counts/types right after conversion — fail loudly instead of producing dirty parquet); ③ fallback strategy (retry with openpyxl if calamine fails on an edge case).

Q5: Memory-wise, which is leaner — calamine or openpyxl with read_only=True? A: calamine typically still loads the whole thing into memory (it has no streaming cursor mode like openpyxl’s read_only), but its underlying structures are more compact than Python objects, so memory usage is significantly lower than openpyxl’s full-DOM mode — though not necessarily lower than openpyxl’s streaming mode. For huge files under tight memory constraints, this needs a case-by-case trade-off.

🏗️ How Big Tech Actually Uses This

• Data platforms at companies like Airbnb or Uber typically stand up a lightweight microservice (or Airflow Operator) that normalizes business-uploaded xlsx files into parquet in the data lake — downstream consumers only ever touch parquet, containing “Excel parsing pain” to one place.
• Polars’ official benchmarks show read_excel(engine="calamine") running 5-10x faster than pandas + openpyxl on mid-to-large xlsx files (100K+ rows).
• DuckDB’s Excel extension offers another path: querying xlsx directly with SQL, skipping the Python object layer entirely.

💸 The High-Stakes Version (Finance)

• Determinism requirements: calamine and openpyxl can disagree on type inference for “dirty” Excel data. In regulated environments, you must pin the engine version and maintain golden-file regression tests.
• Audit trail: keep a mapping from original xlsx hash to parquet hash, so any issue can be traced back.
• The formula trap, upgraded: when field names themselves come from formulas, calamine only reads cached values — if the file was uploaded without being re-saved after the formula last changed, the normalized parquet will carry stale field names. This is a classic silent-failure bug; you need a diff check comparing the field-name set against the historical version.

🚀 What’s Cutting-Edge in 2026

• calamine is now the default Excel-reading engine in the Polars ecosystem; the pandas community is pushing toward pluggable engine support.
• DuckDB’s native Excel extension is maturing — “query xlsx directly with SQL” is gaining traction among analysts.
• More tools now output Arrow RecordBatches directly instead of pandas DataFrames, cutting down on memory-format conversions.
• openpyxl’s role has narrowed to “writing” xlsx (formulas, styles, formatting) — for pure reads, it’s now considered a legacy choice.

🌉 The Cross-Discipline Lens

Think of it as literal translation vs. compiled translation in translation theory. openpyxl is like a human simultaneous interpreter — parsing and rephrasing on the spot, word by word: accurate but slow, and bottlenecked by the interpreter’s (Python interpreter’s) own cognitive bandwidth. calamine is more like a professionally pre-trained machine translation engine — the work of “understanding structure” was already compiled down into a lower, faster system ahead of time. You just want the final translation; you don’t need that slow general-purpose interpreter showing up for every single step.

Part 3: The Polars Ecosystem, in Full

Polars isn’t just “pandas with a new skin” — it’s a full ecosystem spanning single-machine in-memory computation all the way to distributed cloud execution, built on Rust, the Arrow memory format, and lazy evaluation. pandas is a “manual bicycle”; Polars is a “turbocharged, self-driving car” — same road, completely different ride.

Ecosystem components:

More hands-on examples:

import polars as pl

# Example 1: Lazy query + predicate pushdown
# Nothing executes until collect() is called; Polars automatically pushes
# the filter down as close to the read as possible.
result = (
    pl.scan_parquet("s3://bucket/events/*.parquet")
    .filter(pl.col("event_date") >= "2026-01-01")
    .group_by("user_id")
    .agg(pl.col("amount").sum().alias("total_spend"))
    .sort("total_spend", descending=True)
    .limit(100)
    .collect()
)

# Example 2: Window functions (equivalent to SQL's OVER PARTITION BY)
df = df.with_columns(
    pl.col("revenue").rank(descending=True).over("region").alias("rank_in_region")
)

# Example 3: Zero-copy interop with DuckDB, playing to each tool's strengths
import duckdb
arrow_tbl = df.to_arrow()
duckdb.sql("SELECT region, SUM(revenue) FROM arrow_tbl GROUP BY region")

Part 4: Rewriting the Original Scanning Script with calamine

The original task was scanning multiple sheets for rows containing specific field names. Here are two versions: plain python-calamine (simplest, leanest memory footprint) and Polars with the calamine engine (better if you’re chaining filtering/aggregation logic afterward).

Version A: Plain python-calamine

from python_calamine import CalamineWorkbook

path = '/Users/toddzhang/ws/mq/uac/docs/FTG-083-UAC_to_SFEC_Full_Mapping_v2 _mask_fields_revised.xlsx'

wb = CalamineWorkbook.from_path(path)

for sheet_name in ['PersonAccount', 'UAC-SFBuildCheck']:
    print('====', sheet_name)
    sheet = wb.get_sheet_by_name(sheet_name)
    rows = sheet.to_python()  # list[list[Any]], parsed into native Python types in one shot

    for row_idx, row in enumerate(rows[:80], start=1):
        vals = [str(v) for v in row if v is not None]
        joined = ' | '.join(vals)
        if 'UacApplicantId' in joined or 'external' in joined.lower():
            print(f'r{row_idx}: {joined[:400]}')

Key differences from openpyxl:

• to_python() parses the entire sheet into a list-of-lists in one pass — there’s no per-cell generator like openpyxl’s, and no equivalent of read_only=True streaming mode. calamine’s philosophy is: “Rust parsing is fast enough — just hand you everything at once.”
• The data_only parameter doesn’t exist — calamine is designed from the ground up to only read cached values, with no choice between “read the formula” and “read the result.”
• No .row / .column attributes — you get a plain Python list, so you maintain the row number yourself with enumerate.

Version B: Polars + calamine engine (recommended)

import polars as pl

path = '/Users/toddzhang/ws/mq/uac/docs/FTG-083-UAC_to_SFEC_Full_Mapping_v2 _mask_fields_revised.xlsx'

for sheet_name in ['PersonAccount', 'UAC-SFBuildCheck']:
    print('====', sheet_name)
    df = pl.read_excel(
        path,
        sheet_name=sheet_name,
        engine="calamine",
        read_options={"header_row": None},  # original code didn't assume a header row, keep parity
    )

    # Concatenate each row into a single string column, then use a Polars
    # expression for substring matching (vectorized, not a Python for-loop)
    joined_expr = pl.concat_str(
        [pl.col(c).cast(pl.Utf8) for c in df.columns],
        separator=' | ',
        ignore_nulls=True,
    )

    matched = (
        df.with_row_index("row_num", offset=1)
        .with_columns(joined_expr.alias("joined"))
        .filter(
            pl.col("joined").str.contains("UacApplicantId")
            | pl.col("joined").str.to_lowercase().str.contains("external")
        )
        .head(80)
    )

    for row in matched.iter_rows(named=True):
        print(f"r{row['row_num']}: {row['joined'][:400]}")

The value of Version B: string matching moves from a Python-level row-by-row loop to a vectorized string operation inside Polars’ expression engine (backed by Rust’s str.contains, SIMD-accelerated). At the million-row scale, the original openpyxl version is likely seconds to tens of seconds of a Python for-loop, whereas the Polars-expression version runs in milliseconds.

Which to pick: for a quick field-name scan across a few sheets, Version A is simplest with the fewest dependencies. For a real ETL/validation pipeline that later needs groupby/join/diff, go straight to Version B and skip an extra layer of glue code.

Summary

One-line mic-drop: calamine isn’t fast because “Rust beats Python” as some kind of folklore — it’s fast because it drops the number of cross-language boundary crossings from once per cell to once per sheet. The essence of engineering optimization is always reducing boundary crossings, not rewriting logic in a different language.