Technical Article

Lossless XLSX Round-Trip in Delphi: Theme, extLst, calcChain

A billing job opens a customer template, writes 42750 into B3, and saves. Diff the unzipped packages afterwards and the change list is far longer than one cell: xl/theme/theme1.xml now holds the stock Office palette instead of the customer's teal and slate, the <extLst> at the bottom of the worksheet XML has lost the sparkline group and a newer conditional-format rule, and xl/calcChain.xml is missing outright. Excel opens the result, offers a repair, and the brand colors are gone. The code touched one cell. Everything else changed because the save rebuilt the package from whatever the in-memory model knew about, and three parts of a typical template sit outside what a spreadsheet model normally holds

Three mechanisms behind a lossless HotXLS XLSX round-trip in Delphi: xl/theme/theme1.xml cached as raw bytes and written back unchanged apart from any ThemeColor slot overrides, foreign extLst blocks captured from XML events and replayed, and a fresh spec-valid calcChain.xml emitted on every formula save
Each preserved part follows its own path through the save — verbatim theme bytes, event-level extLst replay, and a regenerated calculation chain — while worksheet XML and styles are rebuilt from the model

Why a one-cell edit rewrites the whole XLSX package

An .xlsx file is a ZIP of XML parts: xl/workbook.xml, one xl/worksheets/sheetN.xml per sheet, xl/styles.xml, xl/sharedStrings.xml, the theme, the calculation chain, and whatever drawings, tables and pivot caches the workbook carries. A library that reads values and formulas into objects and regenerates every part from those objects on save has one weak spot. Anything the object model has no slot for never makes it into memory, so the regenerated part comes out without it. Nobody has to delete the sparklines; they simply have nowhere to live between Open and SaveAs

ECMA-376 saw half of this coming. SpreadsheetML reserves extLst (ECMA-376 Part 1, §18.2.10 for the workbook-level element, with matching elements on worksheets, styles and conditional formats) as a future-feature storage area. A newer producer wraps each feature in an <ext> element whose uri attribute names the feature, and an older consumer is expected to carry what it does not understand. Sparklines, slicer lists, newer data-bar and icon-set options, and extended data validation all travel this way in files Excel writes today. Dropping unknown <ext> blocks therefore breaks the forward-compatibility contract the format was built around, and the damage only shows up when the customer reopens the file in Excel

The theme and the calculation chain fail for different reasons, so each needs its own treatment. The rest of this article walks through how HotXLS handles all three, what a Delphi caller can observe and check, and where the guarantee stops

Keeping a custom theme1.xml intact in Delphi

HotXLS keeps a workbook's theme by holding the original bytes of xl/theme/theme1.xml from the opened package and writing that part back as-is on SaveAs. The theme part (ECMA-376 Part 1, §14.2.7) is DrawingML: a color scheme with twelve slots, a font scheme, a format scheme of fills, lines and effects. Cell styles point into it with <color theme="4"/> and similar references, so the spreadsheet engine only needs to resolve slots, never to understand gradients or effect styles. Regenerating a fixed Office theme on every save is exactly what turns the customer's teal into Office blue. A workbook created from scratch, with no theme to carry, gets the built-in Office theme instead

HotXLS caches the raw bytes of xl/theme/theme1.xml at open and writes them back unchanged at save, replacing only color slots overridden through ThemeColor, while a rebuild-from-model library regenerates a stock Office theme and snaps the customer's brand colors back
Caching theme1.xml verbatim needs no theme model at all, and ThemeMajorFont with ThemeMinorFont only style workbooks that carry no captured theme

The everyday case needs no theme code at all. Note the sheet index: Sheets is 1-based in the XLSX engine, and Cells[3, 2] is row 3, column 2, which is B3

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('branded-invoice.xlsx');
    Book.Sheets[1].Cells[3, 2].Value := 42750.00;  // the one edit
    Book.SaveAs('branded-invoice-out.xlsx');
    // xl/theme/theme1.xml in the output carries the template's own theme
  finally
    Book.Free;
  end;
end;

Raw bytes are the strongest fidelity you can get for a part like this: no parse, no re-serialization, nothing to drift. When the save writes Strict OOXML instead of the transitional dialect, the captured theme goes through the namespace translator rather than being copied, because a byte copy would keep transitional namespace URIs inside a Strict package

The trap is on the API side. TXLSXWorkbook exposes ThemeMajorFont and ThemeMinorFont, and after Open they report the heading and body typefaces parsed from the template's font scheme. Setting them looks like the natural way to swap fonts, but they only shape the built-in Office theme that new workbooks receive. On a workbook that brought its own theme, the captured part wins and those two setters change nothing in the saved file. Colors are handled differently: ThemeColor[Slot] addresses the twelve clrScheme slots, and an override is written into the preserved theme by replacing just that slot's color, leaving the font scheme, the format scheme and any theme extensions untouched

Book.Open('branded-invoice.xlsx');
// No effect on the saved file: the template's own theme is preserved
Book.ThemeMajorFont := 'Segoe UI Semibold';
// Does reach the file: only the accent1 slot inside theme1.xml changes
Book.ThemeColor[xlsxThemeAccent1] := $FF0F7B6C;  // $AARRGGBB
Book.SaveAs('rebranded.xlsx');

That split is deliberate. A color override is a narrow, well-defined edit to one element, so the rest of a designer's theme can stay exactly as it was. A font-scheme rewrite would touch per-script font lists that a data-oriented API has no good way to express. If a template needs a new typeface, change it in Excel and ship the new template; that keeps the theme in the hands of whoever owns the brand

What happens to unknown extLst blocks on save?

HotXLS captures every worksheet-level <ext> block it does not model natively and replays it inside the saved worksheet's extLst. The blocks it does model are parsed into typed objects and rebuilt from them: sparkline groups go into SparklineGroups, the x14 data-validation extension is read into the data-validation collection, and slicer lists are re-emitted by the slicer model. Everything else lands, one XML fragment per block, in the read-only RawWorksheetExts string list on each TXLSXWorksheet, which means a test can inspect exactly what will be carried forward

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-newer-excel.xlsx');
    Sheet := Book.Sheets[1];
    WriteLn(Format('%d foreign ext block(s), %d sparkline group(s)',
      [Sheet.RawWorksheetExts.Count, Sheet.SparklineGroups.Count]));
    for i := 0 to Sheet.RawWorksheetExts.Count - 1 do
      WriteLn(Copy(Sheet.RawWorksheetExts[i], 1, 100)); // opening tag shows the uri
    // Namespace declarations the fragments borrowed from the <worksheet> root
    WriteLn(Sheet.RawExtNamespaceDecls);
  finally
    Book.Free;
  end;
end;

The capture works from the XML event stream, because a streaming reader hands out elements, text and end tags but no byte offsets into the source. Each unknown subtree is rebuilt from those events as they pass. A naive depth counter breaks here: it increments on every start tag and decrements on every end tag, and a self-closing child such as <x14:cfvo type="min"/> arrives as one start event flagged empty with no end tag to follow. Count it as an open element and the counter never returns to zero, so the capture swallows the rest of the worksheet. The capture loop in HotXLS only increments depth for a start tag that is not empty, and an <ext/> that is itself empty is stored immediately as a one-line fragment

Rebuilding from events has a visible side effect. Attribute quoting, whitespace between elements and the choice between <a></a> and <a/> come back normalized, so a fragment is equivalent in meaning to the original but not byte-for-byte identical. Excel parses the XML; it does not compare bytes. A diff tool that compares text will flag the change, though, and that is worth knowing before someone files it as a regression

Namespaces are the second thing that can go wrong with a replayed fragment. Excel usually declares the prefixes it needs on the <ext> element itself, which makes the fragment self-contained, but a block can also lean on a prefix such as x14ac or xr that was declared once on the source <worksheet> root. Replay that fragment into a worksheet without the declaration and the saved XML is not namespace-well-formed. HotXLS scans each captured fragment for prefixes it uses but does not declare, records the matching declarations from the source in RawExtNamespaceDecls, and writes them on the saved worksheet root. Because the fragments are plain strings, duplicating a worksheet within or across workbooks can carry them to the new sheet as well

Writing calcChain.xml so Excel trusts your formulas

HotXLS writes xl/calcChain.xml (the Calculation Chain part, ECMA-376 Part 1, §12.3.1) whenever the saved workbook contains at least one formula cell, together with its content-type override and workbook relationship. Each entry is a <c r="D10" i="1"/> element: the cell reference plus the 1-based sheet index, which the writer emits on every entry even though the schema lets a run of entries on the same sheet omit it. Explicit is simpler and just as valid

The order depends on what the workbook already knows. When the formula dependency graph exists and is current, which is the state right after Recalculate, the chain follows a full topological order: every formula after the cells it depends on, with members of circular references appended at the end so the chain still lists every formula cell. When the graph is stale or was never built, formula cells are listed in document order, sheet by sheet. Both are legal. Microsoft's implementation notes for the format, [MS-XLSX], describe the chain as a hint that Excel checks and reorders while it calculates, so a complete listing in any order loads cleanly

HotXLS emits xl/calcChain.xml in topological order when Recalculate has built the dependency graph, and in document order otherwise; Excel treats either complete listing as a hint and reorders it during load
Both orderings stay legal because Excel re-verifies the chain on load, and HotXLS never forces a full dependency-graph build inside SaveAs
Book.Open('model.xlsx');
Book.Sheets[1].Cells[10, 4].Formula := '=SUM(D2:D9)';
// Saving here lists formula cells in document order, because changing
// a formula invalidated the dependency graph.
Book.Recalculate;   // rebuilds the graph and refreshes cached values
Book.SaveAs('model-out.xlsx');
// calcChain.xml now follows dependency order: D10 after D2:D9

SaveAs does not build the graph on its own initiative. A full dependency build means resolving every reference in every formula, which on a large model is real work that a caller who only wanted to write a file never asked for. If the cached values in the output matter to you, and they usually do for anything a downstream system reads without opening Excel, call Recalculate before saving anyway; the dependency-ordered chain comes along for free

Why emit a part Excel treats as advisory? Because its absence stands out. Repair heuristics, third-party viewers and diff tooling all expect a formula workbook to carry a calculation chain, and a package without one looks unlike anything Excel itself produces. Writing a valid chain keeps the output inside the shape the rest of the ecosystem has been exercised against, and costs one pass over the formula cells

Where the lossless round-trip ends

HotXLS does not copy the whole package byte-for-byte, and the output should not be judged that way. Worksheet XML, styles.xml, shared strings and the workbook part are regenerated from the parsed model, so they are faithful in content but not binary-identical to the input. ZIP local headers alone carry fresh timestamps. Captured <ext> fragments come back normalized, as described above. ThemeMajorFont and ThemeMinorFont stay out of a preserved theme, while ThemeColor overrides go into it slot by slot

The preservation net has a defined shape: features the engine models natively, such as sparklines and extended data validation, which are parsed and rewritten; foreign <ext> content at worksheet level, which is replayed; and the theme part, which is carried as captured. VBA projects and external workbook references ride through save on the same keep-what-you-do-not-model approach, covered in the companion article on VBA and external-link preservation, and document properties in docProps have their own read-write API. A part from an exotic add-in that is neither modeled nor inside an extension point sits outside the three mechanisms covered here

The check that matters uses your own files. Open a feature-rich production template, change one value, save, unzip both packages, and diff part by part. Expect differences in the sheet you edited, in styles.xml and in attribute formatting inside extLst; the theme part should match the input, the <ext> elements should list the same uri values, and calcChain.xml should name every formula cell. Anything beyond that list is worth chasing before the template goes into production

Theme preservation, foreign extLst replay and calculation-chain output are part of the XLSX engine in the HotXLS Delphi Excel Component; the product page lists the rest of what it reads and writes for Delphi and C++Builder projects