Calculation and conversion fidelity

Calculated arrays compose across the 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 a single TXLSXCell.Formula and call TXLSXWorkbook.Recalculate; the result then owns a rectangular spill anchored at that cell, taking in 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 just the obsolete followers owned by the anchor and rebuilds dependencies whenever 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 yield #SPILL!; styles on otherwise empty cells stay 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 keeps the edit in place as an obstruction; removing or clearing the anchor releases its owned followers, whilst ConvertFormulasToValues keeps their calculated values as ordinary cells

XLSX saving writes down the current array extent and metadata, and loading restores ownership of existing cached followers before recalculation; array formulas containing errors still hold their extent when the errors belong to individual elements

SetArrayFormula stays the explicit fixed-range CSE entry point in both workbook engines; raw range arithmetic evaluates element by element within that range, whilst ordinary scalar formulas keep implicit intersection

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

MAP takes several input arrays and a matching LAMBDA parameter list, propagates element errors and refuses nested array results with #CALC!; FILTER accepts computed arrays together with row or column masks, covering error propagation and an empty-result #CALC! when no fallback is given

Dynamic spill mutation runs serially even with several calculation threads configured; extent stabilisation is bounded to 32 passes, and dynamic arrays inside circular iterative calculations are rejected rather than being treated as scalar results

INDEX takes references, array constants and calculated arrays; with both selectors 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 keeps reference behaviour for consumers such as ROW and ISREF

Row and column selectors may also be arrays, taking in constants, computed vectors, range values and LET bindings; opposing row and column orientations broadcast to a rectangular cross-product, whilst 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, a zero or omitted selector picks the first member of that axis instead of expanding a whole row or column; fractional selectors truncate towards zero, negative selectors return individual #VALUE! values ahead of truncation, and selectors past the source bounds return individual #REF! values; selected strings, Boolean values and typed errors keep their types

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

Workbook-defined names can supply reference, scalar or calculated-array sources; ISREF(INDEX(...)) tells an actual reference result apart from a named computed value; scalar INDEX selectors and SORT numeric options truncate fractional values, and optional Boolean arguments refuse 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 gives back the paired column {1;9}; array selectors produce values even when the source is a reference, whilst scalar reference selectors preserve reference identity

These expressions can spill and resize in XLSX or evaluate inside an explicit CSE destination; array-source errors stay 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 takes 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 hold their identity through local defined names and LET, whilst scalar CHOOSE keeps 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 the zero, missing, coercion and error rules

For standalone calculation, TXLSCalculator.OnGetSpillRange takes a TXLSGetSpillRange callback that works out the current inclusive bounds of an anchor; workbook-backed evaluators fit this callback automatically

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

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

Saving leaves the authored in-memory expression untouched; reopening newly authored XLSX formulas gives back canonical storage syntax with comma arguments and no leading =, matching native Excel imports; unchanged imported formula XML keeps its existing preservation path

Lookup, matrix and reshape composition

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

MMULT, MDETERM and MINVERSE take 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 refused rather than coerced, and source errors keeping their error codes

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

XLOOKUP, XMATCH and MATCH take calculated lookup vectors and array-valued lookup requests; keys must occupy a single row or column, and XLOOKUP return arrays must line up with that lookup axis

A scalar XLOOKUP can return a whole matching row or column, typed errors included; an array-valued lookup request keeps 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 stay distinct, whilst ordinary scalar reference paths keep their existing streaming and recalculation-cache behaviour; binary search modes still demand correctly sorted keys

VSTACK, HSTACK, TAKE, DROP, TOROW, TOCOL, WRAPROWS, WRAPCOLS and EXPAND take calculated inputs and typed padding; a result with no rows or columns returns #CALC!, and wrapping needs 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! stay typed cell errors through calculation, display, cache inspection and XLSX reopening; XLSX output carries their rich-value metadata whilst keeping the standard cached-error fallback for readers that do not understand that extension

Formula text results, CSE and dynamic-array followers included, use literal string caches so Excel can show them before recalculation; plain value cells carry on using shared strings

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

Imported cell and value metadata, unrelated rich-value structures and opaque buckets keep 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 employed for decoded rich-error buckets; application code reads typed errors from the cell's Variant value

BIFF8 cannot faithfully represent these modern errors or spill operators; the Classic serialiser refuses unsupported modern representations rather than assigning an unrelated legacy error code

Conversion preflight

TXLSXWorkbook.GetConversionReport gives back a caller-owned TXLSConversionReport without recalculating or altering 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 the known format losses, among them 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 keeps ordinary table cells, cached formula results, supported annotation text and authors, passwordless worksheet protection and representable filters, whilst 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; carry out supported BIFF work through the Classic workbook API; version 2.384.93 adds a bounded XLSB values-and-styles backend that refuses unrepresented formulas, feature objects, metadata and calculation settings in both checked and unchecked saves

Checked saving

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

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

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

Ordinary SaveAs stays available with its existing behaviour; applications that need an explicit loss decision should inspect the report and use checked saving

Template formats

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

Macro-free XLTX refuses a VBA-bearing workbook even when lossy checked saving is switched on; an XLTM package may be emitted without a VBA project, and HotXLS does not write 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 are rebased against the validation base-cell address on import

Unsupported structured, external or three-dimensional validation expressions are left out 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, empty notes, tabs, spaces and line breaks included; 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 keeps 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 name the loss of run styling, custom box dimensions and always-visible presentation

The OpenDocument protected flag and the supported locked or unlocked cell styles are kept; the lossless worksheet subset uses no password or editable-range exceptions and allows 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, whilst the protected flag remains; the broad default Excel permission set likewise needs an explicit loss decision

OpenDocument import keeps 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 later 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 face no two-value limit and remain native filter metadata

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

Unsupported filter expressions are left out as a complete expression rather than weakening selected conditions; the filter range remains, and the conversion report names 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 kept, positions beyond the original end included; cancellation or a progress callback exception before commit preserves the destination bytes, size and position and releases the temporary file; the final writing notification stays non-cancellable

OpenDocument import handles 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 stay 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 OpenDocument and Open XML conversion targets alike

Filter conversion keeps metadata and existing row visibility without applying filters or recalculating cells; native applications remain responsible for their own filtering and editing behaviour

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