Verified Workbook Operations

Edition: Commercial

wolfxl.operations is part of the Commercial edition (authenticated package index, latest published release 2.3.0). Community (MIT, public PyPI, 2.0.9) does not include it. See Install for the difference between the editions.

wolfxl.operations performs an inspect → plan → approve (when required) → apply → verify → attest → commit workflow. apply() creates only a managed temporary artifact; commit() is the sole publication step and refuses to overwrite an existing destination.

Package-preserving operation matrix

Operation	Preserving patch parts	Preconditions	Unsupported boundary
set_cells	Target worksheet XML	Bounded cell matrix and no unsafe shared/array/data-table/merge interaction	Structural worksheet edits and unsupported formula groups
set_formulas	Target worksheet XML; xl/workbook.xml; may add calculation-chain metadata	Same bounded safety checks; output requests recalculation	Formula evaluation is not claimed by this operation
set_defined_name	xl/workbook.xml	A unique workbook- or existing-sheet-scoped name target; a valid sheets mapping	Missing or ambiguous scope/name mappings
set_document_properties	docProps/core.xml	Existing, unique core-properties part; only the listed core keys	Custom, extended, and missing core-property packages
insert_rows	Target worksheet XML, affected cross-sheet formulas, and workbook defined names	One bounded row operation; copy_policy="none"; formula_translation_policy="translate"	Table/merged-cell intersections, unsupported drawings/comments, locked sheet protection, grid overflow, or mixed operations
delete_rows	Target worksheet XML, affected cross-sheet formulas, and workbook defined names	One bounded row operation; affected_object_policy="refuse"	Table/merged-cell intersections, unsupported drawings/comments, locked sheet protection, or mixed operations
move_rows	Target worksheet XML, every worksheet with a qualified reference to the band, table parts, workbook defined names, and the calculation chain	One operation per request; 1-based start_row..end_row and a non-zero offset that keeps the band on the grid; destination rows outside the band are overwritten (POI Sheet.shiftRows)	Typed refusal when a table, auto-filter, array or shared-formula block, or merged range straddles the band edge or the overwritten rows; pivots, drawings, comments and controls; 3-D or sheet-less references; locked sheet protection
set_row_properties	Target worksheet XML	WOM v3 sheet and properties {first_row, last_row, height_pt?, hidden?, outline_level?, collapsed?}; inclusive 1-based rows; at least one edit field	Rows outside 1..1048576, reversed bounds, negative height, outline levels outside 0..7, unknown fields, or worksheet policy refusal
set_column_properties	Target worksheet XML	WOM v3 sheet and properties {first_column, last_column, width?, hidden?, outline_level?, collapsed?}; inclusive 1-based columns; at least one edit field	Columns outside 1..16384, reversed bounds, widths outside 0..255, outline levels outside 0..7, unknown fields, or worksheet policy refusal
create_pivot_table	New cache definition, records and table parts; workbook, target sheet, relationships and content types	WOM v4 properties with optional name, source {sheet, range}, destination {sheet, cell}; source-sheet match is case-insensitive	Invalid coordinates, unreadable source or ambiguous field headers
add_pivot_field	Named pivot table; styles when allocating a number format	WOM v4 sheet and properties {pivot, source_column, axis, function?, name?, number_format?}; zero-based source column; row, column, page or data axis	Typed index_out_of_range with the source-column bound; grouped cache fields
set_pivot_field_sort	Named pivot table	WOM v4 sheet and properties {pivot, field, sort_type, auto_sort_scope?}; scope null or {data_field}	Typed field/data-field bounds; grouped cache fields

move_rows moves rows start_row..=end_row by offset and leaves every other row in place. References follow Apache POI's row-move rules: a reference into the band moves with it, absolute or relative; a reference into the overwritten rows becomes #REF! (or Sheet!#REF! when it is qualified); an area that partly overlaps the band or its destination grows or shrinks by POI's area rules. Unqualified references follow the move only on the moved sheet. Shared-formula blocks wholly inside or outside the band keep their group, and their master text and ref are rewritten. A block that straddles the band edge is refused with a typed move_rows refused error rather than dissolved.

set_defined_name accepts name, formula, and scope. scope is either "workbook" or the exact worksheet title. Repeating one (name, scope) target in a request is rejected during preflight.

set_document_properties accepts one properties mapping using only category, content_status, creator, description, identifier, keywords, language, last_modified_by, subject, and title. Strings, finite JSON numbers, and booleans are written as deterministic text; null removes that core-property element. The operation never creates package relationships or modifies extended/custom properties.

For a row operation, supply sheet, one-based row, positive integer count, and the policies listed above. The affected interval and shifted cells must fit within the worksheet's 1,048,576-row limit. Boolean coordinates, unsupported policies, multiple structural operations, and requests mixing row operations with other edits are refused. A formula-bearing source still requires its bound approval before application. Reopen the result to inspect relocated cells and translated references; no Excel-equivalence claim follows from this check.

The native WOM v3/v4 execution contracts also admit these bounded operations. Host-owned artifact providers and worksheet policies remain authoritative, including authorization for cross-sheet and defined-name rewrites. Verification checks relocated values and references before publication, including changes at the final worksheet column; an unrelated staged edit cannot be published as a verified row mutation.

All preserving operations precompute every replacement before the source copy is rewritten. The archive is rebuilt privately, fsynced, and atomically replaced. Every package member other than the declared changed parts (and the documented formula calculation-chain additions) retains identical uncompressed member bytes. Verification compares that contract before an artifact can be attested. A preflight or apply error removes the managed temporary artifact: the source and caller destination remain unchanged.

WOM v3 row and column dimensions

set_row_properties and set_column_properties use the existing {id, op, sheet, properties} envelope through the additive ABI-v1 execution surface. No rows or columns payload alias is accepted. The inclusive first_row/last_row or first_column/last_column bounds must be ordered and lie within the worksheet grid; native admission enforces the ordering. At least one edit field is required, and unknown operation or property keys are refused.

height_pt is a finite nonnegative number in points, or null to restore the sheet's default row height. width is a finite number in 0..255, in the character units stored by OOXML <col width>, or null to remove the custom width. hidden and collapsed are booleans; outline_level is an integer in 0..7. Omitted edit fields preserve their current values.

Row edits patch <row> attributes, creating empty row elements when needed; column edits split and merge <col> spans around the requested interval. Targeted edits preserve unrelated worksheet content and package parts. A semantic no-op batch reports zero modified parts, and an invalid request publishes nothing.

After changing manual row visibility, expect recalculated formula caches. Use SUBTOTAL codes 101..111 or AGGREGATE options 1, 3, 5, and 7 to omit hidden rows. AGGREGATE function codes above 100 remain invalid. Other dimension edits preserve formula caches. Read stored metadata through the existing range query with fields: ["dimensions"].

WOM v3 page-layout authoring

The native v3 contract adds set_page_setup, set_header_footer, set_page_breaks, set_print_area, and set_sheet_view. These operations require id, op, sheet, and a nonempty properties object, and reject every other operation field. This extension does not change WOM v1, v2, or v4.

Operation	properties payload
set_page_setup	One or more nonempty page_setup, page_margins, print_options, or sheet_properties attribute objects
set_header_footer	Either boolean flags different_odd_even, different_first, scale_with_doc, align_with_margins, and/or raw text odd_header, odd_footer, even_header, even_footer, first_header, first_footer; or one slot {kind, slot, text} with kind odd/even/first, slot header/footer, and text a string or null
set_page_breaks	rows and/or columns arrays of break objects; each supplied array replaces its axis and an empty array clears it
set_print_area	Required areas array of unqualified A1 cells/ranges; multiple areas replace the sheet-local _xlnm.Print_Area, and an empty array removes it
set_sheet_view	sheet_view attribute object, pane object or null, and/or selections array of attribute objects

Attribute names and types are the snake-case attribute tables enumerated by the packaged v3 schema. Integer attributes retain their signed/unsigned 32-bit table kinds; double attributes use finite JSON numbers; enums use the exact table strings. Unknown keys and printer relationship_id creation are not supported. null removes optional attributes or header/footer text elements, but required margin attributes and workbook_view_id cannot be removed. When creating pageMargins, the patch supplies left/right 0.75, top/bottom 1, header/footer 0.3 before applying overrides. Header/footer strings preserve the existing reader's section-codec syntax, including &L, &C, &R, and field codes. Each text is at most 32,767 characters; characters outside XML 1.0 Char are written as ST_Xstring _xHHHH_ escapes and a literal _xHHHH_ is written _x005F_xHHHH_, the same policy as cell text. The {kind, slot, text} form also maintains the flag its kind needs to print: text for an even or first slot sets differentOddEven or differentFirst, and null removes the slot and then the flag once neither slot of that kind remains. Odd slots never change a flag.

Each page break requires integer id and permits min, max, man, and pt. Row break ids are 0..1048576 and spans are 0..16383; column break ids are 0..16384 and spans are 0..1048575. Defaults are min=0, the axis span maximum for max, man=true, and pt=false. Admission also enforces unique ids per axis and min <= max; the patch derives count and manualBreakCount.

Sheet-view edits target the first sheetView only, preserving later views and unknown children. Empty attribute objects are allowed; creating the first view supplies workbookViewId=0. pane: null removes the pane and selections: [] clears selections. Cell and range references (top_left_cell, active_cell, and whitespace-separated sqref) are validated before patching. Print areas are written with a quoted sheet name and absolute A1 references without changing other defined names. Semantic no-ops preserve the input XML.

WOM v3 array-formula groups

The native v3 contract adds set_array_formula and remove_array_formula, which edit a legacy array formula (<f t="array" ref="...">) as a whole group, as POI's Sheet.setArrayFormula and removeArrayFormula do. Both require id, op, sheet and an A1 range; set_array_formula also requires formula (an optional leading = is dropped), and every other field is refused. A request carries only these two operations; they apply in order.

Operation	Effect	Typed refusal
set_array_formula	Writes the formula on the top-left cell with ref (the cell alone for a one-cell range) and an empty cell element for every other cell of the range; array groups inside the range are replaced; the output requests full recalculation, which writes each cell's element of the result	unsupported_operation (array formula range partially overlaps ...) when the range partially overlaps another array group or intersects a data table, a merged range or a table; unsupported_operation for more than 100,000 cells
remove_array_formula	Blanks every cell of the group whose range is exactly range and drops its calculation-chain entries	invalid_request (range is not an existing array formula group) for any other range

Cell writes (set_cells, set_formulas) that touch a cell of an array group refuse with unsupported_operation (cell write targets part of an array formula group); only these operations change a group. Verification reopens the output and requires exactly the planned groups of every touched sheet.

The ABI-v1 document query array_formula_groups ({"query": "array_formula_groups", "sheet": ...}) lists a sheet's groups in row-major master order as {ref, master, formula}, with the formula text stored on the master and without a leading =. Data tables are not listed. The Java host exposes it as WolfxlDocument.arrayFormulaGroups(sheet).

WOM v4 picture authoring

The native v4 contract adds add_picture, remove_picture, move_picture and copy_picture. Each requires id, op and a worksheet sheet; the other members travel in properties and every unknown member is refused. A request carries only picture operations; they apply in order as one transaction, so a refusal at any step publishes nothing. Pictures are identified by their cNvPr/@id, as the drawings query reports it.

Operation	Properties	Effect
add_picture	anchor, media {format, data_base64}, optional name, descr	Adds an xdr:pic with the next free cNvPr id (default name Picture N); creates the sheet's drawing part, its relationships, the worksheet drawing element and the content-type declarations when absent
remove_picture	picture	Removes the anchor; a media part no other relationship reaches is removed with it, and a drawing left empty is removed with its relationships and the worksheet drawing element
move_picture	picture plus exactly one of anchor or resize {scale_x, scale_y}	anchor re-places the picture, keeping its current size where the anchor omits it; resize scales the native image size and keeps the anchor type and top-left corner
copy_picture	picture, to_sheet, optional anchor, name	Copies the picture to the same or another worksheet with a new id; the copy shares the source media part

media.format is one of png, jpeg, gif, bmp, tiff, emf, wmf and pict; the header must match it (invalid_request otherwise). Media parts are deduplicated by the SHA-256 digest of their bytes: identical bytes reuse one xl/media/ part and, within a drawing, one relationship.

An anchor is one_cell (from, optional ext), two_cell (from, optional to and edit_as of twoCell, oneCell or absolute) or absolute (pos, optional ext). An omitted ext or to is computed by the engine from the native image size (as image_info reports it) or the picture's current size, against the target sheet's column widths and row heights (the anchor_geometry grid). A move to the current anchor, an empty batch and every query change zero parts; every part an operation does not name is copied byte for byte. Hosts reach these operations only through wolfxl_runtime_execute_v1.

WOM v4 shape authoring

Shape operations use the v4 {id, op, sheet, properties} envelope through the existing wolfxl_runtime_execute_v1 C ABI. create_drawing is the exception: it has {id, op, sheet} and no properties, and is a no-op if the sheet already has a drawing. add_shape.properties requires a complete anchor and kind (sp, txBox, cxnSp or grpSp), with optional preset geometry, line/fill, text body insets, wrapping, overflow and rich paragraphs. Groups contain recursive children whose anchors are local {pos, ext} EMU coordinates. Groups/connectors do not accept text; group children may be textboxes.

set_shape_text.properties is {shape, paragraphs}; remove_shape.properties is {shape}. shape is the drawing query's positive cNvPr id, including a group descendant. Text updates preserve body properties and list style. Removal removes the shape or whole group subtree, retaining package dependencies and even an empty drawing. Existing drawings change only their own part; new drawings also require worksheet relationships and content types. Unrelated drawing bytes and package parts are retained. Identical text and existing-drawing creation change zero parts; a refused batch publishes nothing.

Run size is in points; geometry, line width, insets and paragraph indent are EMU. Paragraphs support font/run formatting, character or DrawingML autonumber bullets, levels and alignment. Typed validation bounds shape recursion, total shape/text counts and coordinates. The canonical v4 schema holds the exact property contract. Java/Python/Node use the canonical v4 schema; .NET reaches these additive operations through its existing canonical-request-byte WolfxlRuntime.Execute entrypoint rather than new typed .NET operation records.

Save options: Strict to Transitional

OperationRequest.save_options (WOM v3 and v4) is an optional request-level object. {"convert_conformance": "transitional"} saves an ISO/IEC 29500 Strict source as Transitional: every part naming a http://purl.oclc.org/ooxml/ URI (worksheets, VML, relationships, document properties, DrawingML) is rewritten before planning, and every other part keeps its bytes. Without the option a Strict source is published byte for byte by an empty request and refused with unsupported_operation for edits; a Transitional source is unchanged by it. A Strict construct with no Transitional counterpart is refused with conformance_unsupported in the message and writes nothing.

Opted-in calculation writeback

OperationRequest.recalculate is a request-level opt-in. It is false by default and must be a real JSON or Python boolean: "true", 1, null, and arrays are refused with invalid_request at request.recalculate, both when a request document is parsed and when a directly constructed request is revalidated before planning. Only the opted-in value is serialized, so a default request keeps the exact identity its plan, approval, and attestation hashes were bound to.

Recalculation is bound into those same identities rather than being a separate execution flag:

1. plan() derives the cache authority from the source package and the request's own cell edits, and publishes it as plan.expected_changes.calculation with mode: "full", the affected worksheet parts, the bounding ranges, cell_count, and a cells_sha256 digest. The authority names every standard formula cell whose cache this request may rewrite.
2. apply() copies the source, applies the edits, calculates the staged workbook natively, and patches only the declared formula cache payloads. Nothing is written unless the whole declared inventory computes to finite, representable values and every declared cell is covered.
3. verify() re-derives the authority from the source bytes and compares it to the plan digest, then reads the persisted caches back out of the output package and compares them, cell by cell, with the values the engine calculated. Every other byte of a cache-bearing worksheet is compared with the source, so an unrelated edit on that sheet fails verification.

On success, verify reports {"status": "computed", "caches_updated": true, "values": {"Sheet!A1": ...}, "value_source": "calculated"}. A request that did not opt in keeps the historical {"status": ...} shape, reports calculation_writeback = null on the apply result, and makes no calculation claim; the calculation dimension stays in not_assessed (calculation, or calculated_values when the request writes formulas). attest() and commit() refuse to publish opted-in evidence that is missing, stale, or fabricated, and refuse cache evidence on a transaction that never requested recalculation.

Refusal	Where it is detected
Opt-in is not a boolean	Request parsing or direct-request revalidation
Array, data-table, or spilled formula footprint in the inventory; unreadable inventory; no formula cell to calculate; inventory above the 50,000-cell budget	plan() returns unsupported and writes no output
Cyclic inventory, non-finite or otherwise unrepresentable result, missing calculated cell	apply() refuses and removes the managed temporary artifact
Authority digest that does not match the plan, stale or fabricated writeback record, persisted cache differing from the calculated value, unrelated content changed on a cache-bearing worksheet	verify() fails, so attest() and commit() refuse

A needs_approval plan still requires the host capability. The opt-in participates in the request and plan digests, so an approval issued for the default plan cannot authorize the opted-in request, or the reverse. The CLI's --approve-source-formula-risk delegation only covers the plan shapes it names and makes no freshness claim of its own: when a request opts in, the delegated reason states that the requested native cache writeback must pass independent verification before commit; without the opt-in it states that formula cache freshness remains unassessed.

Every adopted value is what this library's own calculation engine computed and then persisted and read back. That is a self-consistent, hash-bound proof about this engine and this artifact. It is not an Excel or LibreOffice equivalence claim: independently authored corpus qualification and customer acceptance remain separate lanes.

Guard preservation matrix

Guard uses the existing "unchanged" / "preserve" vocabulary; null means unassessed. The default policy assesses the five core dimensions and each frozen fidelity dimension below. Explicit legacy policies may omit the added dimensions; specifying one assesses it.

Dimension family	Source/output measurement
charts	Chart, chart-sheet, and chart-style semantic fingerprint counts plus feature-part counts
pivots	Pivot semantic fingerprint and feature-part counts
conditional_formatting	Conditional-formatting semantic fingerprint and feature-part counts
data_validations	Data-validation semantic fingerprint counts
comments	Comment feature-part counts and feature-part-loss audit findings
connections, data_model, drawing_objects, extensions	Corresponding semantic fingerprints and accepted feature-part counts
named_sheet_views, page_setup, python, sheet_metadata	Corresponding semantic fingerprints and accepted feature-part counts
slicers, style_theme, structured_references, timelines, workbook_globals	Corresponding semantic fingerprints and accepted feature-part counts

For each assessed fidelity dimension, Guard records deterministic before and after measurements and fails on a changed count or related semantic-drift or feature-part-loss finding. The existing macro_inventory, external_link_inventory, worksheet_inventory, formula_integrity, and package_integrity dimensions remain unchanged.

Guard is a preservation comparison, not an authorization mechanism for an intended change. Operation verify() establishes declared changes; use an explicit Guard policy with the intentionally changed dimensions unassessed when comparing such artifacts.