Skip to content

Workbook API

Version scope

This API reference describes WolfXL 2.2.0.

Constructors

Workbook()

Creates a new workbook in write mode.

load_workbook(filename, read_only=False, data_only=False, keep_links=True, modify=False)

Opens existing workbook.

  • modify=False: read mode

  • modify=True: modify mode (read + patch/save)

  • read_only=True: uses the streaming reader surface for row iteration workflows.

  • data_only=True: read cached formula values where present.
  • keep_links=True: preserves external-link parts in modify-mode saves.
  • keep_links=False: hides external links when reading and drops external-link parts on modify-mode save.

With data_only=True, read saved values from ordinary formulas, array-formula anchors, and data-table anchors. Receive None for an absent cache and the stored error for an error cache. In ordinary formula mode, inspect array and data-table anchors as typed formula objects. Newly assigned formula objects remain available even after a data-only load.

Refresh existing external-link caches in a modify-mode XLSX workbook loaded with keep_links=True. Return the number of refreshed links; save the workbook to persist the updated caches.

Without a resolver, use a filesystem-backed workbook and bounded sibling XLSX targets. Resolve every target before opening any source; refuse non-local targets, path traversal, and symlinks.

Supply resolver(target) to resolve original target strings explicitly. Return XLSX bytes or a filesystem path; return None for an unresolved target. This mode also accepts bytes-backed destination workbooks and opaque URI targets. No network access, credential lookup, recursive dependency refresh, or fallback filesystem lookup is performed by the engine. Reentrant refresh is refused.

Preflight link/cache structures and resolve all targets before opening source readers. Publish cache updates together only after every source succeeds; unresolved targets, callback exceptions, or invalid source packages leave the existing caches unchanged. Preserve link targets and unrelated package parts. Persisted caches are not automatically imported into the calculation engine.

from pathlib import Path
from wolfxl import load_workbook

sources = {"external_source.xlsx": Path("external_source.xlsx").read_bytes()}
with load_workbook(Path("linked.xlsx").read_bytes(), modify=True) as workbook:
    refreshed = workbook.refresh_external_links(resolver=sources.get)
    workbook.save("refreshed.xlsx")

Copying protected worksheets

Use Workbook.copy_worksheet(source, name="Copied") within a modify-mode workbook to retain sheet protection and editable ranges. Edit the copied worksheet's protected_ranges or protection.protected_ranges independently of the source, including after renaming the source before copying. Preserve range passwords and security descriptors through save/reopen.

This metadata-preservation contract does not establish native Excel permission enforcement, encryption, or cross-workbook protection behavior.

Conversion

ConversionOptions(destination_format, sheet=None, cell_range=None, values=False, encoding="utf-8", bom=False)

Immutable conversion controls. destination_format accepts xlsx, ods, xlsb, xls, pdf, png, html, txt, csv, or tsv. Static destinations emit one worksheet; sheet=None selects the first worksheet. Structural destinations convert the complete workbook.

CSV and TSV use RFC 4180 quoting and CRLF row terminators. cell_range selects one bounded A1 rectangle. values=False writes formula text, while values=True writes cached formula results. encoding accepts any registered Python codec with explicit byte order for UTF-16/UTF-32; bom=True adds the matching UTF byte-order marker. For compatibility with supported Python 3.10 CSV tooling, U+0000 is rendered as the literal text \x00.

Conversion entry points

  • preflight_conversion(source, destination=None, *, destination_format=None, sheet=None, options=None) -> ConversionPreflight
  • convert(source, destination, *, destination_format=None, sheet=None, options=None) -> ConversionReport
  • convert_to_bytes(source, *, destination_format=None, sheet=None, options=None) -> ConversionBytes
  • convert_to_stream(source, destination, *, destination_format=None, sheet=None, options=None) -> ConversionReport

source accepts a path, bytes-like object, or readable binary stream. Destinations may be paths or replacement-safe binary streams that support read(), seek(), tell(), write(), and truncate(). A destination path selects its format by extension unless destination_format or ConversionOptions names it explicitly. Conflicting selectors are rejected before publication.

All routes complete backend generation before publishing. Path destinations use a same-directory temporary file and atomic replacement. Stream routes check every partial write and report a typed SDKIOError if publication stalls or fails. convert_to_bytes() returns an immutable ConversionBytes containing both data and its ConversionReport.

ConversionPreflight and ConversionReport expose ordered preservation and diagnostics tuples. Every discovered feature is classified as preserved, rewritten, or dropped; losses remains the compatibility projection of dropped features. Scanner coverage limits are reported explicitly instead of being treated as proof of lossless conversion.

from wolfxl import ConversionOptions, convert_to_bytes, preflight_conversion

options = ConversionOptions(destination_format="pdf", sheet="Summary")
plan = preflight_conversion("template.xlsx", options=options)
artifact = convert_to_bytes("template.xlsx", options=options)
assert artifact.report.selected_sheets == plan.selected_sheets

PDF and PNG require a render-enabled build. The committed high-value conversion evidence matrix covers these routes:

Source Verified destinations
XLSX XLSX, ODS, XLSB, XLS, PDF, PNG, HTML, TXT, CSV, TSV
ODS XLSX, HTML, TXT, CSV, TSV
XLSB XLSX, XLSB, HTML, TXT, CSV, TSV
XLS XLSX, HTML, TXT, CSV, TSV

The matrix checks public dispatch, deterministic repeated bytes, structural reopenability, output signatures, range/encoding semantics, and explicit feature-loss reporting. Routes outside this matrix may be accepted by a bounded backend but do not carry this matrix-level evidence.

Batch conversion

convert_many(sources, destination_dir, *, destination_format, options=None, overwrite=False, fail_fast=False) -> BatchConversionReport converts several sources into one existing destination directory. Items are reported in the order supplied. Destination names and existing files are preflighted before the first conversion. With overwrite=False, publication also uses an atomic no-clobber operation, so a destination created concurrently is preserved. Each success contains only source and destination formats, output hash and size, fidelity tier, and a lossless flag. It excludes sheet names, diagnostic scopes, and cell values. Failures are typed and value-free. fail_fast=True stops after the first failure.

The same outcome is available from the command line:

wolfxl-ops convert-many first.xlsx second.xlsx \
  --destination-dir exports \
  --format csv

The command prints one canonical JSON document of the form {"schema_version", "command", "status", "result"}. Exit code 0 means every item converted, 5 means at least one item failed, and 2 means the request was rejected before any conversion. The CLI does not create the destination directory and does not select per-item formats; one invocation writes one format.

Workbook comparison

compare_workbooks(before, after, *, policy=None) -> WorkbookComparison evaluates the existing Guard policy in memory without writing a report file. The immutable result contains status, passed, issue counts and codes, and path-free workbook identities with filename, SHA-256, and byte size. Omitting policy selects the Guard default policy. This comparison does not assess calculation correctness, rendered appearance, intended-change authorization, or macro execution.

XLSB fidelity ladder

The XLSB writer advances cumulatively. XLSB_FIDELITY_TIERS exposes the same machine-readable table used by the SDK.

Level Boundary Implemented
T0 Blank, number, Boolean, error, and text values Yes
T1 Shared strings, scalar formulas, and cached formula results Yes
T2 Number formats, font name/size, 1904 dates, sheet visibility, and document properties Yes
T3 Merged cells and row/column dimensions Yes
T4 Supported hyperlinks, legacy comments, list validations, cellIs/expression conditional formats, and simple tables Yes

T4 is the current bounded rewrite boundary. Unsupported subtypes and richer variants fail preflight rather than being silently discarded.

Reporting workflows

ReportOptions(strict=True, calculate=False, recompute_pivots=False, max_expanded_rows=1048576)

workbook.reporter returns a cached ReportService. preflight(sources, options=None) validates template markers and returns an immutable ReportPlan without changing the workbook. populate(sources, options=None) applies the plan and returns a ReportReport.

Templates support:

  • scalar markers with optional literal defaults such as {{ customer.discount ?? 0.15 }}
  • repeated row bands using {{#each orders}} and {{/each}}
  • nested repeated bands
  • exact-Boolean conditional bands using {{#if customer.active}}
  • caller-owned local images using {{image customer.logo}} and ReportImage
  • deterministic group_records(...) groups with sum, count, min, max, and average subtotals

Expansion translates formulas and updates supported row-scoped merges, tables, defined names, validations, conditional formats, page breaks, charts, and drawing anchors. Dotted lookup reads mapping keys, dataclass fields, or stored object attributes. It does not evaluate Python expressions, properties, or methods. The reporting engine does not fetch URLs or refresh external data. Image sources must be caller-supplied bytes, a local path, or a binary stream.

A scalar default is a literal JSON scalar only: null, true or false, a finite integer or float, or a quoted string with escaped quotes (embedded }} and ?? inside quotes are literal text). A missing path or an explicit None applies the default; 0, False, and empty strings are real values and never trigger it. Applied defaults are not reported as unresolved fields. A whole-cell default keeps its native Excel type (42 stays a number, false a boolean), while a default inside surrounding text substitutes str(value). Fallbacks that are not scalar literals (NaN, Infinity, arrays, objects) and ?? on #each, #each-col, #if, closing, or image directives are refused as malformed before any mutation. The verified wolfxl.Template runtime does not admit the ?? operator; see Verified Template Runtime.

max_expanded_rows is checked before structural mutation. Strict preflight rejects unresolved fields, malformed or overlapping blocks, non-Boolean conditions, invalid image sources, and expansions beyond the configured or Excel row limit.

Complete XLSX and PDF workflow

from wolfxl import ReportOptions, ReportOutputOptions, load_workbook

workbook = load_workbook("monthly-pack-template.xlsx", modify=True)
result = workbook.reporter.generate(
    {
        "company": {"name": "Acme"},
        "orders": [
            {"sku": "A-1", "quantity": 2, "price": 12.5},
            {"sku": "B-4", "quantity": 1, "price": 8.0},
        ],
    },
    ReportOutputOptions("monthly-pack.xlsx", "monthly-pack.pdf"),
    ReportOptions(calculate=True, recompute_pivots=True),
)
print(result.calculation.report.diagnostics.engine)

generate(...) preflights XLSX, optional PDF, and requested calculation capabilities before population. It then runs population, formula calculation, supported pivot recomputation, a final calculation pass when a pivot changed cells, XLSX serialization, and optional paginated PDF rendering. The immutable ReportWorkflowReport exposes population, calculation, pivots, and outputs. result.outputs.publication records the publication scope and the byte count and SHA-256 identity of each completed artifact.

Pivot recomputation is native only for worksheet-backed sources. External, OLAP, and other non-worksheet sources are preserved without network access and reported in result.pivots.diagnostics. A failure in a supported native pivot is raised instead of silently falling back.

Paired path outputs are fully serialized before either caller destination is changed. Replacement failure restores every destination already changed, or removes it when no prior file existed, and raises ReportPublicationError with rollback_complete and cleanup_complete state. This is an in-process all-new-or-all-restored publication guarantee, not a crash-safe transaction across filesystems. The in-memory workbook remains populated if calculation, pivot recomputation, serialization, or publication fails.

Invalid options, malformed templates, unresolved strict-mode fields, unsafe image sources, and row-limit violations raise ReportError, a stable InvalidSDKRequestError subtype. Unsupported calculation, save, and rendering capabilities fail before template population.

Structured data exchange

wolfxl.data provides the module-level import_records, range_to_records, import_json, range_to_json, import_pandas, export_pandas, import_polars, import_arrow, and import_sql_cursor adapters. These are distinct from the only structured-data Worksheet methods: Worksheet.import_records(...) and Worksheet.to_records(...).

Imports use bounded Worksheet.write_rows batches. Pandas, Polars, PyArrow, and NumPy remain lazy optional dependencies. Pandas, Polars, and PyArrow reject bytes, bytearray, and memoryview cell values before any worksheet mutation; binary payloads must be explicitly converted to a supported Excel scalar by the caller. The Polars and PyArrow adapters have a positive max_rows bound (default 1,048,576): a LazyFrame collects at most one additional row to prove the bound, while a PyArrow batch reader is consumed and validated within that bound before it writes cells. SQL cursor imports consume and advance the caller-owned cursor only through fetchmany(chunk_size), support optional on_progress and should_cancel callbacks, and never execute SQL, commit, roll back, or close the cursor or its connection. Cancellation before completion raises SqlImportCancelled carrying the exact partial DataWriteReport. import_json(worksheet, document, ...) writes one JSON document — records, row arrays, or columns — into a worksheet and range_to_json(worksheet, ...) reads one rectangle back as canonical JSON text. Declared schema types are the only conversions performed, nested objects and arrays are stored as canonical JSON text, and text is never retyped into a formula by accident. The full shape, schema, nesting, literal-text, and refusal contract is documented in docs/api/data-bridge.md.

The native file-boundary API is available from both wolfxl.data and the package root:

  • scan_arrow(filename, sheet_name=None, *, header=True, batch_rows=65536, columns=None, start_row=1, max_rows=None, temporal="native", strict=True)
  • read_arrow(...), with exactly the same scan options, materializes one PyArrow RecordBatch.
  • scan_polars(...), with exactly the same scan options, returns a Polars LazyFrame.
  • write_arrow(data, filename, *, sheet_name="Sheet1", header=True) writes a single-sheet XLSX file from an Arrow C stream or C array.

scan_arrow(...) is a native, one-shot Arrow stream for XLSX worksheets. It implements the Arrow PyCapsule stream protocol without importing PyArrow. batch_rows is a positive per-batch bound; start_row is 1-based before optional header removal; max_rows is a positive bound on emitted data rows; and columns is a non-empty ordered projection of field names or 1-based Excel column indices. temporal="native" exposes format-classified serials as Arrow date, time, and timestamp arrays; temporal="serial" retains Float64 serials and records logical type, number format, and date system metadata. Sparse cells are null. With strict=False, later incompatible values become null and the schema records that loss mode; with strict=True, the scan names the offending sheet, field, and cell. read_arrow imports PyArrow only after the shared scan contract validates. scan_polars imports Polars only when called and ingests the same PyCapsule stream without importing PyArrow.

write_arrow(...) is intentionally distinct from the existing import_arrow(worksheet, table, ...) adapter: the latter writes validated Python-level values into an already-owned worksheet, while write_arrow performs native Arrow-to-XLSX path I/O without constructing Python Cell objects. It requires an object exposing __arrow_c_stream__ or __arrow_c_array__, consumes batches incrementally, requires one schema, writes nulls as blank cells, and maps native Arrow date/time/timestamp values to Excel serials with stable styles. Unsupported types, temporal units or time zones, non-finite numbers, schema changes, and Excel row/column or exact-numeric limits fail without replacing the destination path. DuckDB can object returned by scan_arrow(...) directly through the Arrow stream protocol; WolfXL does not install or monkeypatch a DuckDB engine.

Publishing a dataset as a workbook

records_to_workbook(records, destination, *, sheet_name="Sheet", table_name=None, columns=None, strict=True, overwrite=False) -> DatasetExportReport turns records into one published .xlsx file in a single call. The destination directory is proven writable before the records iterable is consumed. Schema and value validation complete against a sibling staging file before publication. With overwrite=False, an atomic no-clobber operation also preserves a destination created concurrently. table_name defines a real Excel table and rejects identifiers that conflict with Excel cell-reference grammar. DatasetExportReport is immutable and reports only row_count, column_count, sheet_name, table_name, artifact_sha256, and artifact_size.

The command-line form consumes UTF-8 JSONL, one JSON object per line, from a file or -:

wolfxl-ops records-to-workbook records.jsonl \
  --output dataset.xlsx \
  --sheet Units \
  --table UnitsTable \
  --columns region,units

Limits: one worksheet per invocation, every line must be a JSON object, and --columns is a comma-separated order, so column names containing commas require the Python API. Rejected input returns exit code 2 with the offending line number and no cell values in the JSON document.

Calculation

CalculationOptions(sheets=(), ranges=(), dirty_only=False, tolerance=1e-10)

Immutable controls for one calculation. sheets filters returned formula cells by worksheet. ranges accepts qualified A1 selections such as "Summary!B2:D20" and filters returned values further. The complete dependency graph is still evaluated before selection. dirty_only=True recalculates from changed input cells when a compatible evaluator cache exists.

workbook.calculator

Returns a CalculationService bound to the workbook:

  • calculate(options=None) -> CalculationReport
  • dependency_graph() -> DependencyGraphSnapshot

CalculationReport.values is an immutable mapping of qualified cell references to computed values. diagnostics records the selected engine, calculation mode, fallback reason, unsupported formulas, cycles, formula counts, and maximum dependency depth. Dirty calculation also returns ordered CellDelta records. Public failures raise CalculationError, which is both an SDKError and a RuntimeError.

DependencyGraphSnapshot

dependency_graph() returns an immutable, deterministically ordered snapshot of the workbook formula graph. Inspecting it never mutates workbook or dirty state.

Member Purpose
formulas Sorted (cell, formula) pairs for every formula cell
dependencies Sorted forward edges, (formula cell, precedent cells)
dependents Sorted reverse edges, (precedent cell, formula cells)
topological_order Calculation order; empty when a cycle is reported
cycles Circular-reference messages naming the involved cells
precedents_of(cell) Direct precedents of one formula cell
dependents_of(cell) Direct formula dependents of any cell
affected_cells(changed) Transitively dirty formulas in calculation order

Edges are complete rather than range endpoints: a range precedent contributes every cell it covers, across sheets, so an interior edit still propagates. References are canonicalized to the workbook's own worksheet spelling, so case variants and quoted sheet tokens such as 'O''Brien Data'!A1:A2 resolve to one identity. A formula whose cumulative unique precedents exceed 100000 cells raises CalculationError before any edge is materialized.

snapshot = workbook.calculator.dependency_graph()
snapshot.precedents_of("Summary!C1")
snapshot.affected_cells(("Input Data!A2",))

The compatibility methods Workbook.calculate() and Workbook.recalculate(perturbations, tolerance) remain available.

from wolfxl import CalculationOptions, load_workbook

workbook = load_workbook("model.xlsx", modify=True)
report = workbook.calculator.calculate(
    CalculationOptions(sheets=("Summary",), dirty_only=True)
)
print(report.values, report.diagnostics.engine)

Rendering

RenderOptions(...)

RenderOptions selects exactly one workbook, sheet, range, paginated-sheet, or chart target. Its public fields are:

Field Purpose
format png, pdf, jpeg, or svg for sheet, range, and chart selection; workbook-level multi-sheet and paginated selections do not accept jpeg
sheet Worksheet title or object; None selects the workbook route
cell_range One A1 rectangle
chart_index Zero-based native chart order
paginated Print-aware multi-page PDF, PNG, or SVG route
output None for returned bytes, or a path/binary stream
dpi, scale Positive output controls
background Optional RGB color such as #FFFFFF
width, height Optional chart-only pixel dimensions
jpeg_quality Optional integer quality between 1 and 100 (default 90) for JPEG rendering
png_compression Optional integer compression level between 0 and 9 for PNG rendering

workbook.renderer

workbook.renderer.render(options=None) -> RenderReport routes through the same native renderer used by the direct methods. RenderReport.result contains bytes, an immutable sheet-to-bytes mapping, or None after path/stream publication. RenderDiagnostics records the selected route, format, rendered sheet count, typed diagnostics, and any omitted or substituted visual features.

Direct workbook methods remain available:

  • render_workbook_to_pdf(...)
  • render_sheet_to_png(...), render_sheet_to_pdf(...), render_sheet_to_svg(...), render_sheet_to_jpeg(...)
  • render_sheet_to_paginated_pdf(...), render_sheet_to_paginated_pngs(...), render_sheet_to_paginated_svgs(...)
  • render_layout_inventory(...), render_layout_object_to_png(...)
  • render_range_to_png(...), render_range_to_pdf(...), render_range_to_svg(...), render_range_to_jpeg(...)
  • render_chart_to_png(...), render_chart_to_pdf(...)
  • render_chart_to_jpeg(...), render_chart_to_svg(...)

JPEG output is genuine raster JPEG, and SVG output is vector/text SVG. Whole-worksheet and rectangular cell-range JPEG rendering are supported via render_sheet_to_jpeg(...) and render_range_to_jpeg(...) as well as RenderService. render_range_to_jpeg(sheet, cell_range, ...) normalizes same-sheet rectangular A1 coordinates (such as "A1:C3"), applies the native layout, and preserves merged-cell geometry and overlapping drawings. jpeg_quality selects the encoder quality from 1 to 100 (default 90); out-of-range or non-integer values raise ValueError or TypeError.

Whole-workbook and multi-sheet JPEG rendering are refused, paginated rendering does not accept JPEG, chart-only width and height dimensions refuse on sheet and range rendering, and incompatible encoder options (such as png_compression on JPEG or jpeg_quality on PNG/PDF) are rejected. Unsupported raster formats (BMP, TIFF, GIF) fail with ValueError. Chart width or height alone preserves aspect ratio subject to integer-pixel rounding; passing both sets the exact canvas size.

from wolfxl import RenderOptions, load_workbook

workbook = load_workbook("dashboard.xlsx")
report = workbook.renderer.render(
    RenderOptions(format="svg", sheet="Summary", chart_index=0, width=1200)
)
svg_bytes = report.result

render_layout_inventory(sheet=None, *, dpi=96.0, scale=1.0)

Reports one worksheet's native full-sheet layout: the rendered canvas, the effective density, scale, and direction, the laid-out extent, and one record per source object: every materialized cell, every merged range, every chart, and every floating image. sheet names one worksheet or takes a Worksheet object; None selects the active worksheet, and an unknown name raises ValueError. Rendering owns an immutable requested-sheet snapshot, so the workbook and any pending modify state are unchanged. The frozen LayoutInventory and its LayoutObject, LayoutProvenance, and LayoutRect records are importable from wolfxl.layout.

Every rectangle lives in the rendered_output_pixels coordinate space: a top-left origin, already carrying the requested density, the uniform output scale, and the right-to-left mirror a right-to-left sheet renders with. Each record carries rect in that rendered pixel space and grid_rect in the unscaled layout space the native crop consumes. dpi and scale must be finite and greater than zero, and they are the only values that change geometry: read the inventory with the same two values used to render, and a reported rectangle is the region that render actually paints. Geometry is the renderer's own, derived from the same native layout the full-sheet routes rasterize; it is bounded by the native layout cell limit (1,000,000 cells), and an oversized extent refuses rather than truncating. Hidden rows and columns lay out at zero extent, so an object made only of hidden bands reports a zero-area rect.

Identity is the worksheet's own and source-versioned: cell:B2 addresses a cell coordinate, merged_cell:0:B2:D4 a merge ordinal plus its range, and chart:0 / image:1 an ordinal in the worksheet's own chart or image list, never a filtered paint position. Each record's provenance carries the worksheet-expressed coordinates (cell_range, anchor_cell, merge_index, chart_index, image_index, anchor_kind) that tie the rectangle to its source object.

Editability is explicit. cell and merged_cell records report editable true and a target_selector in the existing guarded target-discovery vocabulary; chart and image records report editable false with an editable_reason, because no guarded authoring route edits a rendered chart canvas or a floating image. Resolution to an authoritative target descriptor is the resolve operation of wolfxl-ops layout below; the inventory itself never fabricates an authoring operation.

One inventory does not assess print pagination or page geometry, shape and conditional-format geometry, or visual content the native renderer itself omits. The CLI result spells these out as not_assessed; the same tuple is wolfxl.layout.LAYOUT_NOT_ASSESSED.

from wolfxl import load_workbook

workbook = load_workbook("dashboard.xlsx")
inventory = workbook.render_layout_inventory("Summary", dpi=144.0, scale=1.5)
print(inventory.width_px, inventory.height_px, inventory.coordinate_space)
entry = inventory.object("merged_cell:0:B2:C3")
assert entry is not None and entry.editable
print(entry.rect, entry.grid_rect, entry.provenance)

render_layout_object_to_png(sheet, object_id, *, output=None, dpi=96.0, scale=1.0, background=None, png_compression=None, font_dirs=(), font_substitutions=None, use_system_fonts=False)

Crop one layout object's region out of this worksheet's full render. sheet is required, and object_id is one id render_layout_inventory reported for that sheet under the same dpi and scale. The full worksheet is rasterized once, then cropped from its actual pixels rather than rendered a second time from the object's geometry. Each edge of the inventory record's rect is rounded to the nearest pixel, with half-pixel ties away from zero. The returned pixels match exactly that integral rectangle in the full render, including its antialiasing. Pass output to write to a path-like destination or binary file-like object; omit it to return the PNG bytes.

An unknown object_id, an object with no rendered area (for example one made only of hidden rows or columns), an unknown sheet, or an invalid render option refuses with ValueError instead of emitting a blank or substituted image. A wheel built without the render Cargo feature raises RuntimeError, and an unsupported loaded format raises NotImplementedError.

crop = workbook.render_layout_object_to_png(
    "Summary", entry.id, dpi=144.0, scale=1.5
)

Layout geometry from the command line

Use wolfxl-ops layout --request REQUEST --input SOURCE [--output-dir DESTINATION] or wolfxl.operations.layout_workbook to access the same native geometry as a source-bound operation. One strict version-1 request names the caller's whole-source SHA-256, exactly one operation (inventory, resolve, or crop), that operation's object_id where it needs one, and the effective native render options for one explicit worksheet:

{
  "schema_version": 1,
  "source_sha256": "<sha256 of report.xlsx>",
  "operation": "inventory",
  "options": {"sheet": "Summary", "dpi": 144.0, "scale": 1.5}
}

wolfxl-ops layout --request layout-request.json --input report.xlsx

The worksheet is always explicit: a request without options.sheet refuses rather than rendering the active tab. options admits only the native render vocabulary (dpi, scale, background, png_compression, font_dirs, font_substitutions, use_system_fonts) and rejects process-owned names such as output or destination. dpi and scale are the only options that change geometry; the rest change the crop's pixels only. The source is read once and bound to the caller's digest: the admitted bytes are what the renderer consumes, and the original path is guarded again before any publication, so a source replaced mid-run refuses instead of publishing superseded content. --request accepts a JSON file path or - for stdin.

The command prints one canonical JSON document and exits 0 on success. inventory writes nothing and refuses --output-dir; its result carries the admitted source_sha256, the coordinate space, canvas, extent, one source-versioned record per object, and the explicit not_assessed boundaries. resolve requires object_id and returns exactly that record plus the target descriptor the existing target discovery produced for the record's own coordinates (target and target_operation), or a target_reason when no target is admitted or the object is a chart or image with no guarded authoring route; an unknown object_id or a stale source refuses. crop requires --output-dir naming a directory that does not yet exist: the object's region is rasterized, cropped, verified against the staged bytes, and published as one PNG (layout-1.png) through the shared no-replace publisher, so an existing destination is preserved rather than replaced and a failed run removes only its own staging. The result carries the object record, the crop's own width_px and height_px, and the established outputs inventory (relative_path, sha256, media_type, size_bytes).

Rejected requests exit 2; a stale source or unsupported native request exits 4; a failed operation, including an existing destination, exits 5.

Inspection, capabilities, and binary I/O

Standalone inspection and loading

  • inspect_workbook(source) -> WorkbookPreflight
  • load_workbook_from_bytes(data, ...) -> Workbook
  • load_workbook_from_stream(stream, ...) -> Workbook

inspect_workbook() accepts a path, bytes-like object, or readable binary stream. It returns source format, package parts, preservation inventory, diagnostics, and operation capability results without mutating a workbook. Seekable streams are scanned from offset zero and restored to their original position.

Workbook capability and save methods

  • workbook.preflight_save(destination_format="xlsx") -> CapabilityResult
  • workbook.capability(operation, destination_format=None) -> CapabilityResult
  • workbook.save_to_bytes(*, destination_format="xlsx", password=None) -> bytes
  • workbook.save_to_stream(stream, *, destination_format="xlsx", password=None) -> None

Capability results use supported, supported_with_warnings, unsupported, or unknown tiers and carry typed diagnostics plus preservation records. Queries do not flush pending writes or publish artifacts. Explicit byte and stream saves accept only destination_format="xlsx" and reject other formats before publication. save_to_stream() destinations must support read(), seek(), tell(), write(), and truncate() so failed replacement can restore existing bytes and position. Legacy Workbook.save(binary_stream) also accepts forward-only binary sinks, preserving its established compatibility. After a failed write-only workbook publication, WolfXL retains the completed artifact for a same-options retry, but a forward-only sink cannot roll back bytes it already accepted before a write or flush failure.

Shared diagnostics

Calculation, rendering, conversion, inspection, and save operations share these root exports:

  • SDKDiagnostic, DiagnosticCode, DiagnosticSeverity, DiagnosticContext
  • SDKError, SDKIOError, InvalidSDKRequestError, UnsupportedSDKOperationError
  • SDKWarning, SDKRuntimeWarning, SDKDeprecationWarning
  • CapabilityResult, SupportTier
  • PreservationRecord, PreservationDisposition, WorkbookPreflight
  • CancellationToken, OperationProgress, ProgressCallback, OperationCancelledError

Diagnostics and reports expose deterministic to_dict() payloads. Context uses normalized package-part and A1 fields rather than host filesystem paths.

Long-operation control and concurrency

CalculationOptions, RenderOptions, and ConversionOptions accept:

  • cancellation_token: CancellationToken | None
  • progress: ProgressCallback | None

OperationProgress reports a stable operation name, phase, completed count, and optional total. CancellationToken.cancel() is thread-safe and monotonic. Cancellation raises OperationCancelledError, which is also an SDKError and RuntimeError, with diagnostic code operation_cancelled.

Cancellation is cooperative at Python-owned service checkpoints. A request cancelled before dispatch does not call the evaluator, renderer, or converter. Once a monolithic native method has started, it runs to its next service checkpoint; the current API does not promise per-formula, per-page, or mid-conversion interruption. Direct legacy methods such as Workbook.calculate(), Workbook.save(), and Workbook.render_sheet_to_png() do not accept these controls.

A mutable Workbook and its worksheets belong to one thread. Create, use, and close a workbook on that owner thread; do not share one workbook across worker threads. Independent workbooks are supported in parallel. close() releases thread-affine native calculation, reader, writer, and patcher handles on the calling thread.

Scale boundaries

Workbook(write_only=True) streams row XML into a per-sheet spool. Each active sheet retains up to 512 KiB of row XML in memory, then rolls to an unnamed OS temporary file that is removed when its final handle closes. Shared strings and styles remain resident, so workloads with many unique strings or styles are not constant-memory. Write-only workbooks are append-only and consumed after a successful save.

load_workbook(..., read_only=True) streams worksheet rows. Normal eager loads, calculation, rendering, conversion, images, shared strings, and style tables are not covered by a bounded-memory guarantee. Worksheet coordinates are limited to 1,048,576 rows and 16,384 columns; out-of-range writes fail instead of silently truncating.

Properties

  • sheetnames -> list[str]
  • active -> Worksheet | None
  • power_queries -> PowerQueryCollection
  • connections -> tuple[ConnectionMetadata, ...]

Methods

  • create_sheet(title: str) -> Worksheet (write mode)
  • copy_worksheet(source: Worksheet, name: str | None = None) -> Worksheet
  • save(filename: str) -> None
  • close() -> None
  • __getitem__(name: str) -> Worksheet
  • add_power_query(name: str, formula: str) -> PowerQueryDefinition (modify mode)
  • update_power_query(name: str, formula: str) -> PowerQueryDefinition (modify mode)
  • remove_power_query(name: str) -> PowerQueryDefinition (modify mode)
  • execute_power_query(name: str | None = None) -> None (always raises NotImplementedError)
  • create_connection(name: str, connection_type: int, *, connection_string: str | None = None, command: str | None = None, source_file: str | None = None, description: str | None = None) -> ConnectionMetadata (modify mode)
  • retarget_connection(connection_id: int, *, connection_string: str | None = None, command: str | None = None, source_file: str | None = None) -> ConnectionMetadata (modify mode)
  • remove_connection(connection_id: int) -> None (modify mode)

Worksheet copying

Use destination.copy_worksheet(source, name="Summary") to copy a self-contained worksheet into a write-mode or modify-mode workbook. The source may be an in-memory worksheet or a worksheet loaded in ordinary read or modify mode. Copy values, explicit cell types, direct style components, row and column dimensions, merged ranges, page settings, print setup, and freeze panes. Rewrite genuine self-sheet formula references and internal hyperlinks to the new title while preserving string literals. Explicit name collisions and read-only/write-only modes are refused.

from wolfxl import Workbook, load_workbook

source = load_workbook("source.xlsx")
destination = Workbook()
copied = destination.copy_worksheet(source["Summary"], name="Imported Summary")
destination.save("combined.xlsx")
source.close()
destination.close()

Cross-workbook copying supports bounded sheet-scoped defined names belonging to the source worksheet. Admitted defined names must have valid non-empty names, cannot be macros or procedures (vb_procedure, xlm), and must resolve closed local dependencies (absolute or relative cell/range coordinates or admitted sheet-scoped defined names). Self-sheet references in defined-name formulas are rewritten to the destination worksheet name, and admitted defined names are registered on the destination worksheet (ws.defined_names) and in the destination workbook's local defined names registry with retargeted localSheetId. Formulas in copied cells referencing admitted sheet-scoped defined names are resolved and admitted.

Cross-workbook copying refuses unsupported foreign-sheet, external-workbook, 3D, and structured-table dependencies before destination mutation. Dynamic INDIRECT and HYPERLINK formulas are also refused. Case-insensitive defined-name collisions against existing names in the destination worksheet or workbook (global or local) fail closed and refuse before mutation. It also refuses drawings, images, charts, comments, tables, conditional formatting, validations, pivots, query tables, slicers, sparklines, and scenarios. The existing same-workbook dependency-copy behavior is unchanged. For a same-workbook copy in modify mode, assign edited legacy or threaded comments through the copied cell's comment or threaded_comment property before saving. Preserve independent source/copy comments and their package relationships through repeated save/reopen.

Non-Normal named styles used by copied cells are migrated into the destination workbook's named style registry. Semantically identical styles are deduplicated, while conflicting style definitions with matching names or incompatible theme schemes fail closed and refuse before mutation. Direct cell formatting overrides and source workbook style bindings are preserved.

For an existing target worksheet, use WorksheetCopy(source, target).copy_worksheet() from wolfxl.worksheet.copier. Replace copied cells' direct styles, including defaults, and preserve the target on refusal or failure. Copying does not calculate formulas or migrate unsupported dependent resources.

Power Query

WolfXL inspects embedded, connection-only Power Query definitions without executing connectors or accessing data sources. Inventory records expose names, connector kinds, load destinations, formula sizes, and formula hashes. They do not expose formulas, locations, credentials, or raw DataMashup bytes.

Path-backed workbooks opened with modify=True can add, update, and remove bounded definitions:

from wolfxl import load_workbook

workbook = load_workbook("template.xlsx", modify=True)
workbook.update_power_query("Sales", "let Value = 1 in Value")
workbook.save("updated.xlsx")

Authoring accepts a restricted, offline subset of M. It rejects connector calls, dynamic evaluation, credential-like tokens, source locations, worksheet loads, data-model loads, and native queries before writing output. WolfXL preserves unrelated package parts and validates the source package again during save.

Workbook Connections

WolfXL provides immutable inspection, authoring, bounded retargeting, and owner-validated removal of workbook connections (xl/connections.xml):

from wolfxl import load_workbook

wb = load_workbook("model.xlsx", modify=True)

# Inspect immutable connection metadata snapshots
for conn in wb.connections:
    print(conn.connection_id, conn.name, conn.connection_type, conn.description)

# Create a new connection (types: 1=ODBC, 5=OLE DB, 6=Text)
new_conn = wb.create_connection(
    name="SalesWarehouse",
    connection_type=1,
    connection_string="ODBC;DSN=SalesData;",
    command="SELECT * FROM [Orders]",
)

# Retarget an existing connection
wb.retarget_connection(
    1,
    connection_string="OLEDB;Provider=Microsoft.ACE.OLEDB.16.0;Data Source=new_db.accdb",
    command="SELECT * FROM [UpdatedData]",
)

# Remove an unreferenced connection
wb.remove_connection(new_conn.connection_id)

wb.save("updated_model.xlsx")

Connection Lifecycle Guarantees

  • Creation (wb.create_connection): Authors new connections in modify mode for supported normative types: 1 (ODBC) and 5 (OLE DB) with <dbPr>, and 6 (Text) with <textPr>. Connection type 4 (Web query) is refused. Allocates deterministic unused positive IDs safely even when existing connection IDs are sparse, and enforces non-empty unique names (case-insensitive). Creating the first connection in a connection-free workbook handles the 0-to-1 package transition by adding xl/connections.xml, workbook relationship parts, and content type declarations.
  • Retargeting (wb.retarget_connection): Updates existing dbPr connection strings and commands as well as text connection sourceFile metadata in modify mode. All IDs, connection types, opaque attributes, child elements, and <extLst> extensions are preserved.
  • Removal (wb.remove_connection): Stages connection removal by connection ID in modify mode. Refuses before mutation (ValueError) if the connection is currently referenced by any active query table, pivot cache, or other owner. Removing the last remaining connection handles the 1-to-0 package transition by cleanly removing xl/connections.xml, relationship parts, and content type declarations.
  • Staged Operation Composition: Staged create, retarget, and removal operations compose safely within a single session (for example, retargeting an owning query table to a new connection and then removing the former connection).
  • Credential Safety: Inspect connection strings, commands, and source locations only through explicit attributes. Sensitive values are excluded from implicit repr and error messages.
  • Save Workflows & Publication: Connection authoring and lifecycle updates are preserved across repeat path saves, save_to_bytes(), binary streams (save_to_stream() / BytesIO), and password-protected encrypted saves (save(..., password="...")). Completed package output is staged atomically before destination replacement.
  • Authorized-execution boundary: WolfXL does not evaluate Power Query M or manage credentials. refresh_query_tables() refreshes database and web query tables only through caller-supplied authorization; WolfXL performs no network I/O itself.

Local and Authorized Query Table Refresh

WolfXL supports atomic, bounded refresh of local text query tables and caller-authorized database or web query tables in path-backed modify-mode workbooks. This source-qualified contract does not assert installed-wheel availability or release adoption:

from wolfxl import load_workbook

wb = load_workbook("report.xlsx", modify=True)
count = wb.refresh_query_tables()
print(f"Refreshed {count} query tables")
wb.save("report_updated.xlsx")

For database query tables, pass database_connections, a mapping from the exact workbook connection ID to a caller-owned DB-API connection. WolfXL executes the connection's stored command through that connection; the cursor must provide execute(), fetchmany(), and a description whose column names exactly match the query-table headers. A database connection not present in the mapping is refused.

For web query tables, pass web_authorizer, a callable receiving the exact connection ID and endpoint. It must return bytes or a UTF-8 str response; WolfXL parses the returned payload but never performs network I/O. Omitting the callback refuses web refresh.

Supported Text Dialects and Delimiters

Refreshes inspect the connection's OOXML <textPr> element in xl/connections.xml to determine the delimited format. Per the OOXML CT_TextPr schema, tab defaults to true. Supported delimited dialects include: - TSV (Tab-separated values): default when no delimiter attribute is declared, or explicitly declared via tab="1", delimiter="&#9;", or delimiter="\t". - CSV (Comma-separated values): declared via tab="0" comma="1" or tab="0" delimiter=",". - Semicolon-separated values: declared via tab="0" semicolon="1" or tab="0" delimiter=";". - Pipe-separated values: declared via tab="0" delimiter="|".

Multiple active separators (for example, declaring comma="1" without setting tab="0") or unsupported delimiters are rejected. Delimiter flags set to 0 with a custom delimiter attribute allow the custom delimiter to independently activate that character.

Qualifiers and Text Fields

  • Text Qualifiers: Documented OOXML qualifier="doubleQuote" (default "), qualifier="singleQuote" ('), and qualifier="none" (no quoting stripped).
  • Schema Representation: Canonical OOXML <textFields count="N"><textField type="text"/>...</textFields> container within <textPr>.

Execution and Safety Guarantees

  • Strict UTF-8 and Character Set: Source files are read strictly as UTF-8. Canonical characterSet overrides codePage per primary documentation. UTF-8 BOM (\xef\xbb\xbf) is preserved and not silently stripped; source headers must match the query table field headers byte-for-byte.
  • All-Text Values: Refreshed data cells are inserted strictly as text cells (inlineStr). Formula-like strings (such as =SUM(...)) are preserved as text values without formula evaluation. Empty strings are populated as blank cells.
  • Atomic Preflight: Multi-table and single-table refreshes validate all source paths, headers, dimensions, cell corridors, and destination table boundaries prior to queueing any changes. If any validation or parsing step fails, no destination worksheets are modified.
  • Local-Source Security: Refreshes only access local regular files within the workbook's source directory tree. Directory traversal (..), absolute paths, device files, and files exceeding 16 MiB or 100,000 rows are rejected.

Context manager

Workbook supports with statements.