xlsxparser 0.11.0

#!/usr/bin/env python3
"""Generates the "real Excel data" fixtures under tests/fixtures/{normal,
complex,error,load}/ using openpyxl, so xlsxparser is tested against files
a real, widely-used OOXML-writing tool actually produces — not just the
hand-authored minimal XML in tests/fixtures/{normal,error,complex,load,
security}.rs.

Two categories need more than "just call openpyxl and save":

- error/: no legitimate tool ever writes a syntactically broken or
  dangling-reference file on purpose, so these start from a genuine
  openpyxl-authored package and then mutate one specific part
  (`_mutate_zip_entries`) the same way a real-world failure would —
  truncating a part mid-write (disk full, interrupted upload), or breaking
  a single relationship ID — rather than being invalid from the start.
  `out_of_bounds_sst` additionally layers on a hand-built
  xl/sharedStrings.xml, because openpyxl itself never writes one (see
  `basic_types`'s doc comment) but real Microsoft Excel output almost
  always does, and the out-of-bounds scenario specifically needs one to
  reference out of bounds.

- load/: genuinely large workbooks, generated for real by openpyxl
  (`massive_dense_accounting`, `thousand_sheets`). `massive_sst` again
  layers a hand-built large xl/sharedStrings.xml on top for the same
  reason as `out_of_bounds_sst`.

Kept in the repository (rather than run-once-and-discard) so these binary
fixtures can be regenerated if openpyxl's output shape ever changes.

Usage: python3 scripts/generate_real_fixtures.py
Requires: pip install openpyxl
"""

import datetime
import os
import re
import zipfile

import openpyxl

ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
NORMAL_DIR = os.path.join(ROOT, "tests", "fixtures", "normal")
COMPLEX_DIR = os.path.join(ROOT, "tests", "fixtures", "complex")
ERROR_DIR = os.path.join(ROOT, "tests", "fixtures", "error")
LOAD_DIR = os.path.join(ROOT, "tests", "fixtures", "load")


# --- zip post-processing helpers (for error/ and the SST-layering cases) ---


def _read_zip_entries(path):
    with zipfile.ZipFile(path) as z:
        return {name: z.read(name) for name in z.namelist()}


def _write_zip_entries(path, entries):
    with zipfile.ZipFile(path, "w", zipfile.ZIP_DEFLATED) as z:
        for name, data in entries.items():
            z.writestr(name, data)


def _mutate_zip_entries(path, mutate_fn):
    """Reads back the package `path` (already saved by openpyxl), lets
    `mutate_fn(entries: dict[str, bytes])` edit it in place, and rewrites
    the same path.
    """
    entries = _read_zip_entries(path)
    mutate_fn(entries)
    _write_zip_entries(path, entries)


def _add_shared_strings_part(entries, strings):
    """Adds a hand-built, genuine-shaped xl/sharedStrings.xml to `entries`
    and registers its relationship in xl/_rels/workbook.xml.rels — the part
    openpyxl itself never writes (see module docstring) but real Microsoft
    Excel output does.
    """
    sst_xml = (
        '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\n'
        '<sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" '
        f'count="{len(strings)}" uniqueCount="{len(strings)}">'
        + "".join(f"<si><t>{s}</t></si>" for s in strings)
        + "</sst>"
    )
    entries["xl/sharedStrings.xml"] = sst_xml.encode("utf-8")

    rels_xml = entries["xl/_rels/workbook.xml.rels"].decode("utf-8")
    rels_xml = rels_xml.replace(
        "</Relationships>",
        '<Relationship Id="rIdSharedStrings" '
        'Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings" '
        'Target="sharedStrings.xml"/></Relationships>',
    )
    entries["xl/_rels/workbook.xml.rels"] = rels_xml.encode("utf-8")


# --- normal/ ---


def basic_types():
    """Every CellValue variant in one row, written by openpyxl rather than
    hand-authored XML. Notably, openpyxl writes text cells as
    t="inlineStr" (it dropped writing xl/sharedStrings.xml entirely in
    recent versions) — itself a real-world instance of the
    "third-party tool that emits inline strings" scenario
    tests/fixtures/normal.rs::inline_strings documents.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "日本語Text"
    ws["B1"] = 42
    ws["C1"] = 19.99
    ws["D1"] = datetime.date(2023, 6, 15)
    ws["D1"].number_format = "yyyy-mm-dd"
    ws["E1"] = True
    ws["F1"] = False
    # openpyxl has no direct "write an error value" API (error codes only
    # ever arise as cached formula results in a real workbook); set the
    # cell's data_type directly to produce a genuine t="e" cell.
    ws["G1"] = "#N/A"
    ws["G1"].data_type = "e"
    path = os.path.join(NORMAL_DIR, "basic_types.xlsx")
    wb.save(path)
    return path


# --- complex/ ---


def houganshi_merged():
    """A merged A1:C3 region, the "grid-paper Excel" shape from Issue #28."""
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "houganshi"
    ws.merge_cells("A1:C3")
    path = os.path.join(COMPLEX_DIR, "houganshi_merged.xlsx")
    wb.save(path)
    return path


def multi_sheet_states():
    """Visible / hidden / veryHidden / empty sheets in one workbook."""
    wb = openpyxl.Workbook()
    visible = wb.active
    visible.title = "Visible"
    visible["A1"] = 1

    hidden = wb.create_sheet("Hidden")
    hidden["A1"] = 1
    hidden.sheet_state = "hidden"

    very_hidden = wb.create_sheet("VeryHidden")
    very_hidden["A1"] = 1
    very_hidden.sheet_state = "veryHidden"

    # No cells written at all.
    wb.create_sheet("Empty")

    path = os.path.join(COMPLEX_DIR, "multi_sheet_states.xlsx")
    wb.save(path)
    return path


def extreme_sparse():
    """A1 and Excel's absolute bottom-right corner, XFD1048576, populated —
    nothing in between.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws.cell(row=1, column=1, value=1)
    ws.cell(row=1_048_576, column=16_384, value=2)
    path = os.path.join(COMPLEX_DIR, "extreme_sparse.xlsx")
    wb.save(path)
    return path


# --- error/ ---


def corrupted_xml():
    """A genuine openpyxl-authored worksheet part, truncated partway
    through — simulating a real-world interrupted write (disk full, killed
    process, a network upload cut short) rather than a file that was
    invalid XML from the start.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "will be truncated"
    ws["B1"] = 123
    path = os.path.join(ERROR_DIR, "corrupted_xml.xlsx")
    wb.save(path)

    def mutate(entries):
        sheet_xml = entries["xl/worksheets/sheet1.xml"]
        entries["xl/worksheets/sheet1.xml"] = sheet_xml[: len(sheet_xml) * 2 // 3]

    _mutate_zip_entries(path, mutate)
    return path


def missing_relations():
    """A genuine workbook.xml.rels with the worksheet's relationship Id
    changed, so workbook.xml's <sheet r:id="rId1"> no longer resolves —
    simulating real-world rels corruption (e.g. a re-zipping tool that
    renumbers relationship IDs inconsistently with the parts that
    reference them).
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = 1
    path = os.path.join(ERROR_DIR, "missing_relations.xlsx")
    wb.save(path)

    def mutate(entries):
        rels_xml = entries["xl/_rels/workbook.xml.rels"].decode("utf-8")
        # openpyxl assigns the worksheet relationship Id="rId1" for a
        # single-sheet workbook; workbook.xml's <sheet> still references
        # "rId1" after this, so the reference now dangles.
        broken = rels_xml.replace('Id="rId1"', 'Id="rIdRenumbered"', 1)
        assert broken != rels_xml, "expected to find Id=\"rId1\" to break"
        entries["xl/_rels/workbook.xml.rels"] = broken.encode("utf-8")

    _mutate_zip_entries(path, mutate)
    return path


def invalid_merge_ref():
    """A genuine merged region (A1:C3), with only the <mergeCell> tag's
    `ref` reversed to C3:A1 after the fact — <dimension ref="A1:C3"/>
    (unrelated, and not read by xlsxparser at all) is deliberately left
    untouched to keep the mutation minimal and realistic.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "merged"
    ws.merge_cells("A1:C3")
    path = os.path.join(ERROR_DIR, "invalid_merge_ref.xlsx")
    wb.save(path)

    def mutate(entries):
        sheet_xml = entries["xl/worksheets/sheet1.xml"].decode("utf-8")
        corrupted, count = re.subn(
            r'(<mergeCell ref=")A1:C3(")', r"\1C3:A1\2", sheet_xml
        )
        assert count == 1, "expected exactly one <mergeCell ref=\"A1:C3\"/>"
        entries["xl/worksheets/sheet1.xml"] = corrupted.encode("utf-8")

    _mutate_zip_entries(path, mutate)
    return path


def out_of_bounds_sst():
    """A genuine openpyxl-authored package with a hand-built, real-shaped
    xl/sharedStrings.xml layered on (1 entry), and A1 rewritten to
    reference shared-string index 99999 — out of bounds.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "placeholder"
    path = os.path.join(ERROR_DIR, "out_of_bounds_sst.xlsx")
    wb.save(path)

    def mutate(entries):
        _add_shared_strings_part(entries, ["only one entry"])
        sheet_xml = entries["xl/worksheets/sheet1.xml"].decode("utf-8")
        rewritten, count = re.subn(
            r'<c r="A1"[^>]*>.*?</c>',
            '<c r="A1" t="s"><v>99999</v></c>',
            sheet_xml,
            count=1,
            flags=re.DOTALL,
        )
        assert count == 1, "expected exactly one <c r=\"A1\"> to rewrite"
        entries["xl/worksheets/sheet1.xml"] = rewritten.encode("utf-8")

    _mutate_zip_entries(path, mutate)
    return path


# --- load/ ---


def massive_dense_accounting():
    """A genuinely-generated dense 10,000-row x 30-column sheet (300,000
    cells) — no post-processing, this is exactly what openpyxl writes for
    that much data.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Ledger"
    for row in range(1, 10_001):
        for col in range(1, 31):
            ws.cell(row=row, column=col, value=row * 100 + col)
    path = os.path.join(LOAD_DIR, "massive_dense_accounting.xlsx")
    wb.save(path)
    return path


def thousand_sheets():
    """1,000 genuinely-generated sheets, each holding a single cell."""
    wb = openpyxl.Workbook()
    wb.remove(wb.active)
    for i in range(1, 1001):
        ws = wb.create_sheet(f"Sheet{i}")
        ws["A1"] = i
    path = os.path.join(LOAD_DIR, "thousand_sheets.xlsx")
    wb.save(path)
    return path


def massive_sst():
    """A genuine openpyxl-authored package with a hand-built 50,000-entry
    xl/sharedStrings.xml layered on, and 3 cells referencing the first, a
    middle, and the last index.
    """
    wb = openpyxl.Workbook()
    ws = wb.active
    ws.title = "Sheet1"
    ws["A1"] = "placeholder-a"
    ws["B1"] = "placeholder-b"
    ws["C1"] = "placeholder-c"
    path = os.path.join(LOAD_DIR, "massive_sst.xlsx")
    wb.save(path)

    string_count = 50_000

    def mutate(entries):
        strings = [f"unique-string-{i}" for i in range(string_count)]
        _add_shared_strings_part(entries, strings)
        sheet_xml = entries["xl/worksheets/sheet1.xml"].decode("utf-8")
        for ref, index in (
            ("A1", 0),
            ("B1", string_count // 2),
            ("C1", string_count - 1),
        ):
            sheet_xml, count = re.subn(
                rf'<c r="{ref}"[^>]*>.*?</c>',
                f'<c r="{ref}" t="s"><v>{index}</v></c>',
                sheet_xml,
                count=1,
                flags=re.DOTALL,
            )
            assert count == 1, f"expected exactly one <c r=\"{ref}\"> to rewrite"
        entries["xl/worksheets/sheet1.xml"] = sheet_xml.encode("utf-8")

    _mutate_zip_entries(path, mutate)
    return path


def main():
    for directory in (NORMAL_DIR, COMPLEX_DIR, ERROR_DIR, LOAD_DIR):
        os.makedirs(directory, exist_ok=True)
    for fn in (
        basic_types,
        houganshi_merged,
        multi_sheet_states,
        extreme_sparse,
        corrupted_xml,
        missing_relations,
        invalid_merge_ref,
        out_of_bounds_sst,
        massive_dense_accounting,
        thousand_sheets,
        massive_sst,
    ):
        path = fn()
        print(f"wrote {os.path.relpath(path, ROOT)}")


if __name__ == "__main__":
    main()