A billing service I helped debug filled its invoice template by writing to fixed coordinates: customer name into B3, invoice date into E3, first detail line into row 10. One afternoon an accountant inserted a remit-to address block above the detail section of the shared template file. The generator kept writing to row 10, every save reported success, and for two days outgoing invoices carried line items stamped over the remit-to text while the totals row summed empty cells. Nothing crashed and no log line turned red. The output was simply wrong, and only a customer noticed.
That incident is the central design problem of template-based reporting: templates are edited by people who never read your source code. HotXLS, a native Delphi and C++Builder library that reads and writes XLS and XLSX workbooks without Excel automation, supplies the three primitives a generator needs to survive template edits — cell text search, style-preserving range copies, and row insertion that shifts dependent structures. The rest of this article is about wiring those primitives together so that a moved cell fails the build loudly instead of corrupting invoices silently.
Why hard-coded coordinates fail the first template edit
A spreadsheet template is a user interface owned by the finance or operations team, and they will change it: a taller logo row, an extra tax line, a reordered address block. Any generator that addresses cells by literal row and column numbers encodes one specific revision of that layout and has no way to detect that the revision changed underneath it. The file format gives no help here — a BIFF or OOXML save succeeds whether or not your numbers still mean what they meant last quarter.
The durable fix is a placeholder contract. The template author writes tokens such as {{CUSTOMER}}, {{DATE}}, and {{DETAIL_START}} into the cells the generator must touch, and the generator derives every coordinate at run time from where it actually finds those tokens. Just as important is the failure rule: if a required token is missing, the job must stop before a single value of customer data is written. A drifted template should produce a failed job ticket, never a delivered file.
Locating placeholders with FindText and ReplaceText
Both HotXLS class families expose worksheet-level search. FindText returns the row and column of the first cell whose text matches, with an overload adding case sensitivity; ReplaceText replaces every occurrence and returns the replacement count. On the XLSX side, anchoring a template fill looks like this:
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
R, C: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('invoice-template.xlsx') <> 1 then
raise Exception.Create('Cannot open invoice template');
Sheet := Book.Sheets[0]; // TXLSXSheets.Items is 0-based
if not Sheet.FindText('{{CUSTOMER}}', R, C) then
raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
Sheet.Cells[R, C].Value := 'ACME Corp';
if Sheet.ReplaceText('{{DATE}}',
FormatDateTime('yyyy-mm-dd', Date)) = 0 then
raise Exception.Create('Template drift: {{DATE}} token missing');
// detail expansion and save follow below
finally
Book.Free;
end;
end;Two details matter. First, FindText and ReplaceText match the text value of a cell; a token embedded inside a formula string is invisible to them, so placeholder tokens belong in plain cells, never inside formulas. Second, the replacement count is your drift detector. A template that should contain exactly one {{DATE}} token but reports zero replacements has been edited, and raising an exception at that moment is precisely what turns silent layout drift into a visible failure.
Cloning the detail row without losing styles or formulas
The detail section of an invoice grows with the data, and the naive approach — writing values into blank rows — discards the borders, number formats, and per-row formulas the designer prepared. The robust pattern keeps one fully formatted sample row in the template and clones it: CopyRange duplicates styles and formulas together, and the generator then overwrites only the value cells.
const
DetailRow = 10; // the formatted sample row in the template
var
I: Integer;
begin
// Open space before the totals block first, so the SUM range
// below the detail band stretches together with the data.
if Length(Items) > 1 then
Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);
for I := 0 to High(Items) do
begin
if I > 0 then // clone styles + formulas from the sample row
Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
Sheet.Cells[DetailRow + I, 4].Formula :=
Format('B%d*C%d', [DetailRow + I, DetailRow + I]); // no '=' prefix
end;
end;Watch the formula assignment closely: the XLSX Formula property takes the expression without a leading equals sign, while the XLS facade expects '=B10*C10' assigned through Value. Mixing the two conventions is the most common porting mistake between the two class families, and it fails quietly — the cell simply holds a literal string. If the template decorates the detail band with merged title rows, remember that only the top-left cell of a merged area carries a value; the layout rules in our article on merged cells in layout-driven report templates explain why merge regions should stay out of the data band entirely.
What InsertRows shifts automatically — and what it never will
Inserting rows ahead of the totals block is what keeps a SUM range stretching as the detail section grows. On the XLSX side, InsertRows shifts merged ranges, row heights, hyperlinks, comments, frozen panes, autofilter ranges, conditional formats, data validations, tables, defined names, and image and chart anchors along with the cells. Formula rewriting, however, covers only references within the same sheet. A formula on a summary sheet that points into the moved region keeps its old coordinates, which is exactly why totals consumed across sheets are safer expressed through workbook-level names — see the companion article on defined names and cross-sheet formulas for that pattern.
The legacy XLS format adds a sharper edge. HotXLS preserves pivot tables, query tables, and external data connections in BIFF files as raw byte blocks: they round-trip through open and save unchanged, but they are not modeled, so row insertion does not move them. A template that keeps a pivot table below an expanding detail block will save without any warning while the pivot source rectangle silently goes stale. Keep pivot and query content on sheets the generator never inserts into, and the problem disappears by construction.
Recalculate before you ship — or decide explicitly not to
HotXLS does not evaluate formulas during SaveAs. Excel recalculates when the file is opened (the XLS facade exposes CalculationMode and RecalcOnSave to steer that), so a report delivered to a human needs no extra step. The calculus changes when the workbook feeds a machine: CSV export emits formulas as literal text without evaluating them, and any downstream parser that reads cached values will see stale or empty results. For those paths, evaluate on the server with Calculate, which parses and computes an arbitrary expression against the loaded workbook:
var
Total: Variant;
LastDetail: Integer;
begin
LastDetail := DetailRow + Length(Items) - 1;
Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
[DetailRow, LastDetail]));
if (not VarIsNumeric(Total)) or
(Abs(Total - ExpectedTotal) > 0.005) then
raise Exception.Create('Invoice total does not match the order record');
if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
raise Exception.Create('Save failed: check output path and permissions');
end;Comparing the computed total against the business record before saving is cheap insurance. It converts a wrong invoice into a failed job, and a failed job is something an operator can retry — a wrong invoice in a customer mailbox is something an account manager has to apologize for.
Frequently asked questions
Can one code path fill both .xls and .xlsx templates?
Not literally. HotXLS ships two independent class families: TXLSWorkbook is interface-based and reference-counted with 1-based sheet indexing, while TXLSXWorkbook is a plain object you must free, with 0-based sheet indexing and a separate formula convention. FindText, ReplaceText, CopyRange, and InsertRows exist on both sides, so the algorithm ports cleanly, but commit to one format per pipeline or hide the differences behind a thin adapter of your own.
How many detail rows can this pattern handle?
Cloning a styled row a few thousand times is unremarkable on current hardware. When detail bands reach six-figure row counts, the save path becomes the bottleneck; enabling StreamingWrite streams worksheet XML directly into the output package, as covered in our article on streaming writes for server batch jobs.
What happens to a chart anchored below the detail block?
On the XLSX side both the chart anchor and its series references shift when InsertRows runs above them, so a chart under the totals row stays attached to the right data. On the XLS side, charts live on dedicated chart sheets and unmodeled structures such as pivot tables do not shift at all — another reason to separate presentation sheets from the sheet the generator expands.
Template-driven generation is the highest-leverage way to produce polished workbooks from Delphi, because it lets designers own appearance while code owns data. The search, copy, and insertion primitives shown here all ship with the HotXLS Component, together with the formula engine used for pre-delivery validation.