The reliable way to produce a styled Excel report from Delphi is to start from a workbook a designer already built. Someone in finance lays out the invoice in Excel: the logo, the column headers, the borders on the detail band, the bold totals row, the currency formats. Your code opens that file, drops live data into the cells the designer reserved for it, and saves the result. The appearance is theirs; the numbers are yours. HotXLS, a native Delphi and C++Builder library that reads and writes XLS and XLSX workbooks without driving Excel, gives you the three operations this approach needs: search for a cell by its text, copy a range with its styles and formulas intact, and insert rows so that everything below shifts down with the data
The one rule that separates a generator that survives template edits from one that breaks on the first one is to never address cells by literal row and column numbers. A template is a document that other people edit. The finance team adds a tax line, raises the height of the logo row, reorders the address block, and the file format helps you not at all: a BIFF or OOXML save succeeds whether or not row 10 still means what it meant last quarter. A generator that writes the first detail line to a hard-coded row 10 will, the first time someone inserts a block above the detail section, stamp line items over the wrong cells and sum a totals range that no longer covers the data. Nothing throws, every save returns success, and the only signal is a customer noticing a wrong invoice
Anchor every coordinate to a placeholder token
The fix is to make the template carry its own coordinates. The designer writes tokens such as {{CUSTOMER}}, {{DATE}}, and {{DETAIL_START}} into the cells the generator must touch, and the generator works out every position at run time from where it finds those tokens. Layout edits no longer matter, because the token moves with the cell it sits in. The second half of the contract is the failure rule: if a required token is missing, the job stops before any customer data reaches the file. A template that has drifted should produce a failed job ticket, not a delivered document
Finding the tokens: 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 that adds case sensitivity. ReplaceText swaps every occurrence and returns how many it changed. The two cover the two kinds of token you tend to have. A single anchor like the customer name you locate once and write next to; a token that should appear exactly once, like the report date, you replace and check the count. On the XLSX side, a fill that anchors itself this way 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. Writing values straight into blank rows below the sample line throws away everything the designer prepared: the borders, the number formats, the per-row formulas. The pattern that keeps all of that is to leave one fully formatted sample row in the template and clone it for each item. CopyRange duplicates styles and formulas in a single call, after which the generator 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 class families, and it fails without complaint: the cell just holds a literal string that Excel shows as text. 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 the companion article on merged cells in layout-driven report templates explain why merge regions belong outside the data band entirely
What InsertRows moves, and what it leaves behind
Inserting rows ahead of the totals block is what keeps a SUM range stretching as the detail section grows. On the XLSX side, InsertRows carries a long list of dependent structures down with the cells: merged ranges, row heights, hyperlinks, comments, frozen panes, autofilter ranges, conditional formats, data validations, tables, defined names, and image and chart anchors. There is one boundary in that list worth committing to memory. Formula rewriting reaches only references within the same sheet. A formula on a summary sheet that points into the moved region keeps its old coordinates and quietly reads the wrong cells, which is why totals pulled across sheets are safer expressed through workbook-level names. The companion article on defined names and cross-sheet formulas works through that pattern
The legacy XLS format draws the line in a harder place. HotXLS keeps pivot tables, query tables, and external data connections in BIFF files as raw byte blocks. They survive open and save unchanged, but they are not modeled, so row insertion never touches them. A template that parks a pivot table below an expanding detail block saves with no warning at all while the pivot source rectangle drifts off the data. The way out is structural, not defensive: keep pivot and query content on sheets the generator never inserts into, and the staleness cannot happen
Recalculate before delivery, or know why you skipped it
HotXLS does not evaluate formulas during SaveAs. When a person opens the file, Excel recalculates everything (the XLS facade exposes CalculationMode and RecalcOnSave if you need to steer that), so a report headed for a human inbox needs nothing more from you. The picture changes the moment the workbook feeds another program. CSV export writes formulas out as their literal text and never computes them, and any downstream parser that trusts cached values will read stale numbers or blanks. For those paths, compute on the server with Calculate, which evaluates an arbitrary expression against the loaded workbook and hands back the result:
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;Checking the computed total against the order record before the save is cheap insurance with a good payoff. It turns a wrong invoice into a failed job. An operator can retry a failed job in seconds; a wrong invoice already in a customer's mailbox costs an account manager an apology and a correction
Two class families, one algorithm
The same logic ports between formats, but not the same code. TXLSWorkbook for legacy .xls is interface-based and reference-counted, with 1-based sheet indexing, and you never free it by hand. TXLSXWorkbook for .xlsx is a plain object you must free in a try..finally, with 0-based sheet indexing and the formula convention shown above. FindText, ReplaceText, CopyRange, and InsertRows all live on both sides, so the anchor-clone-recalculate shape carries over cleanly. The practical advice is to commit to one format per pipeline, or to hide the two object lifecycles behind a thin adapter of your own rather than scattering the difference through the generator
Size rarely matters for the kind of report this pattern produces. Cloning a styled row a few thousand times is nothing for current hardware. The save path only becomes the bottleneck when a detail band runs into six figures of rows, and at that point setting StreamingWrite sends worksheet XML straight into the output package instead of buffering it; the article on streaming writes for server batch jobs covers when that trade is worth making. Charts behave the way the rest of the layout does: on the XLSX side both the chart anchor and its series references move when InsertRows runs above them, so a chart under the totals row stays bound to the right data, while on the XLS side charts sit on their own chart sheets and, like pivot tables, never shift. That is one more argument for keeping presentation sheets clear of the sheet the generator expands
This anchor-clone-recalculate approach lets a designer own how a workbook looks while your code owns what it says, which is usually what makes generated Excel output worth maintaining. The search, copy, and insertion calls shown here, along with the formula engine used for the pre-delivery total check, ship with the HotXLS Component for Delphi and C++Builder