External Workbook References

Overview

Spreadsheet models often draw data from secondary workbooks using cross-reference formulas; HotXLS supports parsing, writing, and preserving external workbook references in both BIFF8 (classic XLS) and OpenXML (XLSX) documents

Live workbook workspace

TXLSWorkbookWorkspace in lxWorkbookWorkspace is the shared identity and registration core for live external workbooks; both TXLSWorkbook and TXLSXWorkbook expose CreateWorkspaceWorkbook and own an ExternalWorkspace used by their formula evaluators, while an ODS workbook opened through TXLSXWorkbook reports the OpenDocument engine kind through the same adapter

var
  Host, Target: TXLSXWorkbook;
begin
  Host.ExternalWorkspace.Add(
    '..\Data\Target.xlsx',
    '..\Data\Target.xlsx',
    'C:\Models\Host.xlsx',
    Target.CreateWorkspaceWorkbook);

  // The compatibility facade maps an unambiguous name to the
  // relationship target already stored in Host.ExternalLinks
  Host.RegisterExternalWorkbook('Target.xlsx', Target);

  // The matching call revokes a facade-level registration
  Host.UnregisterExternalWorkbook('Target.xlsx');
end;

Classic workbooks use the same compatibility methods with TXLSWorkbook targets; RegisterExternalWorkbook and UnregisterExternalWorkbook manage only the facade-level name mapping, while ExternalWorkspace.Remove and Clear revoke the workspace registrations themselves. Cross-engine callers add any Classic, XLSX, or ODS adapter directly to ExternalWorkspace

• Identity normalization is lexical and case-insensitive, preserves paths and extensions, resolves relative targets against the owner source identity, collapses dot segments, and performs no file-system or network access
• Exact identities and explicit aliases resolve first; basename-only lookup succeeds only when exactly one connected registration matches, otherwise Resolve returns xlswrsConflict
• Add rejects exact-key, alias, and duplicate-workbook collisions; distinct full paths and distinct extensions can coexist
• Remove and Clear revoke registrations, while direct destruction of a Classic or XLSX target disconnects its adapter and waits for active readers before the model is released
• External worksheets resolve by declared name before the one-based positional fallback, so target workbook sheet order does not need to match the source link directory
• The formula evaluator reads a resolved live workbook first and then falls back to the host file's typed cache; Classic XLS hosts decode sparse XCT and CRN values, while XLSX hosts use the external-link cache already held by the package model
• OnLoadWorkbook is an optional controlled resource request; its default value is nil, so recalculation still performs no file-system or network access unless application code explicitly provides that policy
• Each normalized identity invokes the loader at most once until ResetLoadAttempts; concurrent top-level requests share the in-flight result, including typed not-found and error outcomes, instead of opening the same resource repeatedly
• MaxLoadDepth defaults to 16 and MaxWorkbookCount defaults to 64; same-identity re-entry, nested dependencies on an identity already in flight, depth exhaustion, and workbook-count exhaustion return typed diagnostics without blocking in a load cycle
• ODF external-source IRIs remain complete lexical identities, including URI schemes and quoted characters, but never authorize implicit file or network access
• An identity conflict remains #REF! so stale cached data cannot hide ambiguous routing; an absent cached cell is an empty value only when the file declares a valid cache for that sheet

Resolve performs registered lookup and then the optional controlled load; its outcome is a TXLSWorkspaceResolveStatus (xlswrsResolved, xlswrsNotFound, xlswrsConflict, xlswrsDisconnected, xlswrsLoadNotFound, xlswrsLoadLimit, xlswrsLoadLoop, or xlswrsLoadError), and ResolveWithLoader also returns a TXLSWorkspaceLoadDiagnostic that pairs a TXLSWorkspaceLoadDiagnosticCode with a TXLSWorkspaceLoadResponseStatus (xlswlrsNotFound, xlswlrsResolved, xlswlrsError) to distinguish not found, loader error, disconnected result, depth or workbook limit, identity re-entry, concurrent dependency, and registration conflict outcomes. The loader callback receives a TXLSWorkspaceLoadRequest (identity, depth, registered count, limits) and answers with a TXLSWorkspaceLoadResponse

Recalculate reports a TXLSWorkspaceRecalcStatus, optionally with a TXLSWorkspaceRecalcResult breakdown, and the evaluator records per-cell provenance as a TXLSWorkspaceRuntimeLookup (xlswrlInactive, xlswrlResolved, xlswrlFallbackCache, or xlswrlError) so diagnostics can tell a live read from a cached fallback

The IXLSWorkspaceWorkbook adapter exposes EngineKind (type TXLSWorkspaceEngineKind: xlsweClassic, xlsweOpenXml, or xlsweOpenDocument), SourceIdentity, InstanceIdentity, and Generation, tests liveness with IsConnected, reads one cell through TryGetCellValue (returning a typed TXLSWorkspaceCellStatus of value, missing, invalid reference, disconnected, or error plus an out-of-used-range flag), refreshes the model with Recalculate, and detaches from the workbook model with Disconnect

The registry adds AddAlias for extra names bound to one registered identity, TryResolve as the exception-free lookup variant, and BaseNameMatchCount to preview how many connected registrations share a basename before choosing an unambiguous target

Cross-workbook dependency graph

BuildDependencyGraph snapshots every registered workbook adapter and extracts formula nodes from Classic XLS, XLSX, and ODS models into one TXLSWorkspaceDepGraph; the method returns False and no partial graph when any registered adapter cannot provide dependency metadata

• Each formula node keeps its canonical workbook identity, worksheet name and one-based worksheet position, zero-based cell position, array output rectangle, volatility, and unresolved-reference state
• Cell and rectangular-range references keep canonical target-workbook and worksheet identities; full-column, full-row, and full-sheet references remain one interval rather than expanding into millions of cells
• Local defined names remain queryable as symbolic dependencies and also expand to their concrete cell or range dependencies when the definition can be resolved statically
• External defined names preserve the external-link slot, declared name, optional worksheet scope, and canonical target identity without treating DDE, OLE, or user-function metadata as workbook names
• FindDependentsOfCell and graph edge construction use row-interval trees with maximum-end pruning; LastRangeCandidateChecks and EdgeCandidateChecks expose the number of exact rectangle checks for performance verification
• Formula extraction runs under workbook read leases and scans only materialized formula objects, so packed value storage remains packed and graph creation does not modify workbook generations

Scheduled recalculation

Recalculate retains the shared graph and rebuilds it only when workspace registration or a workbook's formula-dependency generation changes; value generations seed a new dirty pass, and dirtiness propagates through the cross-workbook dependent edges

var
  RecalcInfo: TXLSWorkspaceRecalcResult;
  Status: TXLSWorkspaceRecalcStatus;
begin
  Status := Workspace.Recalculate(RecalcInfo);
  if Status <> xlswrcOk then
    HandleWorkspaceCalculation(Status, RecalcInfo);
end;

• Strongly connected components are computed on formula cells, so workbooks may link in both directions while remaining acyclic when their cell-level dependency paths do not form a loop
• Real cycle members and dirty descendants blocked by a cycle are invalidated and excluded from the topological order, preventing stale cached values from being presented as successful results
• Volatile or statically unresolved references force the conservative dirty behavior required for correctness, while stable graphs reuse prior dependency work
• External reads within a pass use an exact workbook-instance, worksheet, row, and column cache; a scheduled formula invalidates its output range before evaluation so later dependents observe the new value
• The pass-local cache never grants resource authority; registered workbooks and the optional caller-controlled loader remain the only live resolution sources, followed by typed file caches and #REF!
• TXLSWorkspaceRecalcResult exposes whether the graph was rebuilt plus dirty, evaluated, invalidated, cycle, blocked, cache-hit, and cache-miss counts
• The status distinguishes success, unsupported adapters, disconnected workbooks, circular references, calculation errors, and workspace mutation during the pass
• Calls to Recalculate on the same workspace are serialized, so two calculation sessions never modify the shared graph or workbook caches concurrently
• Remove or Clear may run while a pass is active; the pass keeps safe adapter snapshots and returns xlswrcWorkspaceChanged instead of dereferencing a removed registration
• Destroying a workspace waits for its active pass to finish, while destroying a registered workbook disconnects its adapter and makes later resolution fail safely
• Loader failures and not-found results remain cached once per normalized identity until ResetLoadAttempts, and a failed calculation retains local dirty state for an explicit retry
• The retained graph makes an unchanged follow-up pass proportional to registered workbooks rather than formula count; non-recursive component analysis and compact range edges keep deep and wide models bounded by materialized formula metadata

Detach external defined names

ConvertExternalDefinedNamesToRefErrors provides the same public operation on TXLSWorkbook and TXLSXWorkbook; it returns the number of workbook-scoped and worksheet-scoped definitions replaced with native #REF!

var
  Converted: Integer;
begin
  Converted := Workbook.ConvertExternalDefinedNamesToRefErrors;
  // External-link parts and ordinary cell formulas remain intact
end;

• Classic XLS selection uses compiled BIFF reference tokens and the XTI supporting-workbook identity, so same-workbook three-dimensional references and uncertain token streams remain unchanged
• XLSX selection uses syntax-aware numeric workbook slots in relationship document order and accepts only workbook external-link parts, excluding DDE, OLE, unresolved slots, table references, and bracket text inside strings
• All replacements are prepared before the first mutation and committed as one write operation; a second call is idempotent
• Name text, workbook or worksheet scope, visibility, comments, macro and built-in flags, unknown XLSX attributes, and the external-link directory remain available after conversion and round trip
• Malformed and unsupported definitions stay byte- or text-preserving where possible and add xlsDiagnosticDefinedNameConversionSkipped diagnostics instead of being guessed
• Dependent formulas recalculate to the corresponding Excel error value, while a valid cached result for an ordinary direct external formula remains available if its live workbook later disconnects
• The operation is Excel-specific and does not reinterpret OpenDocument name-formula semantics

Classic XLS External References

In classic XLS workbooks, external links are stored in the global directory block using EXTERNALBOOK and EXTERNNAME records; HotXLS maintains these directories during file read/write cycles, ensuring that remote range references survive modification loops

XLSX External Relations

For OOXML workbooks, external link mapping is managed through relationship parts; check the supporting interface details below

• TXLSXExternalLinks class — API to list and update spreadsheet external links
• IXLSWorkbook interface — Top-level workbook properties
• IXLSRange.Formula property — Read and write cross-workbook formulas