Calculation and conversion fidelity

Calculated arrays compose across selection, sorting, lookup, matrix, and reshaping functions, with automatic XLSX spill ownership, explicit CSE destinations, and bounded evaluation resources

Dynamic arrays and explicit CSE arrays

Assign an array-producing expression to one TXLSXCell.Formula and call TXLSXWorkbook.Recalculate; the result owns a rectangular spill anchored at that cell, including formulas such as SEQUENCE(3,2), A1:A3*10, array constants, and array-valued LET results

Sheet.Cells.Item[1, 1].Formula := 'SEQUENCE(3,2)';
Sheet.Cells.Item[1, 4].Formula := 'SUM(A1#)';
Workbook.Recalculate;

Recalculation grows or shrinks the result, clears only obsolete followers owned by the anchor, and rebuilds dependencies when the extent changes; references to newly created followers and the A1# spill operator use the current extent

Existing values, formulas, rich text, other arrays, merged ranges, tables, and worksheet boundaries obstruct a spill and produce #SPILL!; styles on otherwise empty cells remain usable, and clearing an obstruction lets the next recalculation retry the anchor

Editing a follower with a value or rich text detaches that cell from the anchor so the next calculation preserves the edit as an obstruction; removing or clearing the anchor releases its owned followers, while ConvertFormulasToValues keeps their calculated values as ordinary cells

XLSX saving records the current array extent and metadata, and loading restores ownership of existing cached followers before recalculation; array formulas that contain errors still retain their extent when the errors belong to individual elements

SetArrayFormula remains the explicit fixed-range CSE entry point in both workbook engines; raw range arithmetic evaluates element by element inside that range, while ordinary scalar formulas retain implicit intersection

The unary @ operator intersects a reference with the formula cell and takes the first element of a calculated array; SINGLE and ANCHORARRAY are accepted storage aliases for implicit intersection and spill references

MAP accepts multiple input arrays and a matching LAMBDA parameter list, propagates element errors, and rejects nested array results with #CALC!; FILTER accepts computed arrays and row or column masks, including error propagation and an empty-result #CALC! when no fallback is supplied

Dynamic spill mutation runs serially even when multiple calculation threads are configured; extent stabilization is bounded to 32 passes, and dynamic arrays in circular iterative calculations are rejected rather than treated as scalar results

INDEX accepts references, array constants, and calculated arrays; when both selectors are scalar, positive row and column selectors return one value, a zero selector returns the selected row or column, and two zero selectors return the complete array; a reference source with scalar selectors retains reference behavior for consumers such as ROW and ISREF

Row and column selectors can also be arrays, including constants, computed vectors, range values, and LET bindings; opposing row and column orientations broadcast to a rectangular cross-product, while matching orientations select paired positions; singleton dimensions broadcast, and missing positions in unequal dimensions return individual #N/A values

When either selector is an array, zero or an omitted selector chooses the first member of that axis rather than expanding an entire row or column; fractional selectors truncate toward zero, negative selectors return individual #VALUE! values before truncation, and selectors beyond the source bounds return individual #REF! values; selected strings, Boolean values, and typed errors retain their types

SORT and UNIQUE accept ranges, constants, and calculated arrays, including nested FILTER, SEQUENCE, and LET results; row and column orientation, ascending or descending sorting, first-occurrence ordering, and exactly-once deduplication follow the same contract for each source form

Workbook-defined names can supply reference, scalar, or calculated-array sources; ISREF(INDEX(...)) distinguishes an actual reference result from a named computed value; scalar INDEX selectors and SORT numeric options truncate fractional values, and optional Boolean arguments reject text other than TRUE or FALSE

Sheet.Cells.Item[1, 6].Formula := 'SORT(UNIQUE(FILTER(A1:B5,A1:A5>1)),1,-1)';
Sheet.Cells.Item[1, 9].Formula := 'INDEX(SORT(UNIQUE(A1:B5)),0,2)';
Sheet.Cells.Item[1, 12].Formula := 'INDEX(SEQUENCE(2,3,10,2),2,3)';
Workbook.Recalculate;

Sheet.Cells.Item[1, 1].Formula := 'INDEX(SEQUENCE(3,3),{1;3},{1,3})';
Workbook.Recalculate;

This selector example spills {1,3;7,9} into a two-by-two result; INDEX(SEQUENCE(3,3),{1;3},{1;3}) instead returns the paired column {1;9}; array selectors produce values even when the source is a reference, while scalar reference selectors preserve reference identity

These expressions can spill and resize in XLSX or evaluate inside an explicit CSE destination; array-source errors remain typed and an empty exactly-once result returns #CALC!; selector outputs consume the evaluation step budget, and the combined source, selector, and result matrix payloads are checked against the array memory budget

INDEX accepts an explicit fourth area_num argument of 1 for a calculated source, and row, column and area selectors can broadcast together over a supported same-sheet reference union; actual multi-area references retain identity through local defined names and LET, while scalar CHOOSE retains the selected reference, including a choice on another sheet; selector-array results are values rather than nested reference arrays; see INDEX area selection and reference identity for zero, missing, coercion and error rules

For standalone calculation, TXLSCalculator.OnGetSpillRange accepts a TXLSGetSpillRange callback that resolves the current inclusive bounds of an anchor; workbook-backed evaluators install this callback automatically

TXLSGetSpillRange = function(SheetIndex, AnchorRow, AnchorCol: Integer;
  var LastRow, LastCol: Integer): Integer of object;

lxNormalizeStorageFormula normalizes authored formula syntax for OOXML, lxTryStorageFormulaOperators translates spill and implicit-intersection operators, and lxApplyXlfnPrefix applies required future-function prefixes; protected text and array-row separators remain intact, and assigning a formula retains the caller's text in memory

Saving does not change the authored in-memory expression; reopening newly authored XLSX formulas returns canonical storage syntax with comma arguments and no leading =, consistent with native Excel imports; unchanged imported formula XML retains its existing preservation path

Lookup, matrix, and reshape composition

TRANSPOSE accepts references, constants, and calculated arrays while preserving text, Boolean values, and individual errors; blank source cells become numeric zero

MMULT, MDETERM, and MINVERSE accept calculated matrices and nested expressions such as MMULT(MUNIT(2),SEQUENCE(2,2)); their matrix entries must be numeric, with text, Boolean values, and blanks rejected rather than coerced, and source errors retaining their error codes

CHOOSECOLS and CHOOSEROWS accept scalar or one-dimensional selector vectors, concatenate selectors in argument order, preserve duplicates, and count negative indices from the end; fractional selectors truncate toward zero, zero and out-of-range selectors reject, and two-dimensional selector matrices are unsupported

XLOOKUP, XMATCH, and MATCH accept calculated lookup vectors and array-valued lookup requests; keys must occupy one row or one column, and XLOOKUP return arrays must align with that lookup axis

A scalar XLOOKUP can return an entire matching row or column, including typed errors; an array-valued lookup request retains its own shape and takes the first component of each matching slice or fallback array

Exact lookup scans skip error entries in the key vector; Boolean, numeric, and text keys remain distinct, while ordinary scalar reference paths retain their existing streaming and recalculation-cache behavior; binary search modes still require correctly sorted keys

VSTACK, HSTACK, TAKE, DROP, TOROW, TOCOL, WRAPROWS, WRAPCOLS, and EXPAND accept calculated inputs and typed padding; a result with no rows or columns returns #CALC!, and wrapping requires a one-dimensional input

Matrix work, lookup scanning, selector traversal, copying, and padding consume the evaluation-step budget; simultaneously retained input, working, and output matrix payloads are checked against the configured array-memory limit before allocation

Sheet.Cells.Item[1, 1].Formula := 'XLOOKUP({1;3},SEQUENCE(3),SEQUENCE(3,2))';
Sheet.Cells.Item[1, 4].Formula := 'CHOOSECOLS(SEQUENCE(2,3),{-1;1})';
Sheet.Cells.Item[1, 7].Formula := 'TRANSPOSE(UNIQUE({"A";"B";"A"}))';
Workbook.Recalculate;

Typed modern calculation errors

#SPILL! and #CALC! remain typed cell errors through calculation, display, cache inspection, and XLSX reopening; XLSX output carries their rich-value metadata while retaining the standard cached-error fallback for readers that do not understand that extension

Formula text results, including CSE and dynamic-array followers, use literal string caches so Excel can display them before recalculation; plain value cells continue to use shared strings

Modern rich-error output includes the workbook version and Rich Data calculation-feature declarations required by Excel to interpret those caches before recalculation; the emitted calcId is at least 191029, preserving higher requested values without changing the workbook's in-memory CalcId property

Imported cell and value metadata, unrelated rich-value structures, and opaque buckets retain their original identities when new error buckets are appended; replacing an error cell clears obsolete typed-error metadata on save

TXLSXRichErrorCodes is the integer-array type used for decoded rich-error buckets; application code reads typed errors from the cell's Variant value

BIFF8 cannot represent these modern errors or spill operators faithfully; the Classic serializer rejects unsupported modern representations instead of assigning an unrelated legacy error code

Conversion preflight

TXLSXWorkbook.GetConversionReport returns a caller-owned TXLSConversionReport without recalculating or changing the workbook; the types are declared in lxDiagnostics

Report := Workbook.GetConversionReport(xlsConversionOpenDocument);
try
  for I := 0 to Report.Count - 1 do
    InspectIssue(Report.Items[I]);
finally
  Report.Free;
end;

The report inventories known format losses such as VBA, protection, workbook views, connections, query tables, comments, external links, XML maps, table structure, filter criteria, drawings, typed error caches, unsupported validation or formula syntax, array ownership, and opaque extension parts; it describes the implemented conversion paths and does not certify every feature in every external application

ODS retains ordinary table cells, cached formula results, supported annotation text and authors, passwordless worksheet protection and representable filters while losing the Excel table object, richer chart geometry or unsupported rule semantics; an opaque OOXML preservation payload is reported as dropped when the target cannot carry it

The XLSX facade reports BIFF8 output as unsupported; use the Classic workbook API for supported BIFF workflows; version 2.384.93 adds a bounded XLSB values-and-styles backend that rejects unrepresented formulas, feature objects, metadata and calculation settings in both checked and unchecked saves

Checked saving

Status := Workbook.SaveAsChecked(Stream,
  xlsxOpenDocumentSpreadsheet, False);

File and stream overloads run the known-loss preflight before writing; AllowLossy=False rejects degraded or dropped features with conversion diagnostics, while AllowLossy=True explicitly accepts those losses and still rejects unsupported conversions

A rejected preflight leaves destination bytes and stream position unchanged; successful requests use the existing save transaction and return the same save status conventions as SaveAs

Ordinary SaveAs remains available with its existing behavior; applications that require an explicit loss decision should inspect the report and use checked saving

Template formats

xlsxOpenXMLTemplate selects macro-free XLTX, while xlsxOpenXMLMacroEnabledTemplate selects XLTM and preserves an existing VBA project; the format is explicit and is not inferred from a file extension or imported template

Macro-free XLTX rejects a VBA-bearing workbook even when lossy checked saving is enabled; an XLTM package may be emitted without a VBA project, and HotXLS does not create VBA code

ODS validation formulas

Custom Boolean validation uses of:is-true-formula(...); supported scalar bounds, list sources, qualified A1 references, strings, and argument separators translate to OpenFormula and rebase against the validation base-cell address on import

Unsupported structured, external, or three-dimensional validation expressions are omitted together with their cell references, avoiding dangling validation definitions; conversion preflight reports the omitted rules

ODS annotations, protection and filters

Ordinary cell comments export as OpenDocument annotations with their text and author, including empty notes, tabs, spaces and line breaks; notes on otherwise absent cells and distant coordinates use sparse repeated row and column runs without creating worksheet cells

A comment on a merged range's anchor cell retains its address; a note on a covered cell or outside the worksheet grid is reported as dropped; rich comment text is flattened to its displayed text, and conversion reports identify the loss of run styling, custom box dimensions and always-visible presentation

The OpenDocument protected flag and supported locked or unlocked cell styles are retained; the lossless worksheet subset uses no password or editable-range exceptions and permits selection of locked and unlocked cells only

Sheet.Protect;
Sheet.SheetProtectionOptions :=
  [xlsxSpoSelectLockedCells, xlsxSpoSelectUnlockedCells];
Sheet.Range['B2:B100'].Locked := False;

Excel password hashes, modern password settings, editable ranges and action permissions outside that subset are reported as degraded while the protected flag remains; the broad default Excel permission set also requires an explicit loss decision

OpenDocument import retains the protected flag and cell lock styles; native password hashes are not converted into Excel hashes, and unrepresented imported permission metadata remains a named loss in subsequent conversion reports

AutoFilter export supports six scalar comparison operators, up to two scalar conditions per column joined with AND or OR, and an AND between columns; supported plain value-selection lists and blank selections have no two-value limit and remain native filter metadata

Numeric scalar criteria use invariant finite values; text selections preserve their literal characters, including wildcard characters inside selection lists, while wildcard expressions in custom filters, ambiguous Boolean criteria, date groups, dynamic dates, color and icon filters have no equivalent supported conversion

Unsupported filter expressions are omitted as a complete expression rather than weakening selected conditions; the filter range remains, and the conversion report identifies the omitted criteria

ODS stream saves stage the completed package in a registered temporary file before writing at the destination's original position; existing prefix bytes and absolute ZIP offsets are retained, including positions beyond the original end; cancellation or a progress callback exception before commit preserves the destination bytes, size and position and releases the temporary file; the final writing notification remains non-cancellable

OpenDocument import supports the same scalar conjunctions and same-column text disjunctions or value sets; cross-column OR, regular expressions, case-sensitive comparisons and unsupported native filter operators remain explicit imported losses

TXLSXOdsImportLoss and TXLSXOdsImportLosses describe the private worksheet provenance categories; conversion reports use ImportedOdsProtectionPassword, ImportedOdsProtectionOptions, ImportedOdsFilterCriteria and ImportedOdsCommentFormatting; they survive worksheet copying and appear for both OpenDocument and Open XML conversion targets

Filter conversion retains metadata and existing row visibility without applying filters or recalculating cells; native applications retain responsibility for their own filtering and editing behavior

Related workflows

• Stable members and transactional local Pivot cache refresh
• Caller-supplied query-table providers and transactional refresh
• Threaded conversations in repeated report bands
• Chart cache refresh and merged native pagination
• Workbook declarations and save methods