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
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
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
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