Iterate the cells of a freshly opened report template and a merged title behaves like a leak. You read A1 and get "Quarterly Statement"; you read B1 through F1, which visibly sit under the same banner, and you get nothing. Write a value into C1 to patch the header and it never appears on screen. The grid did not lose your data. It is doing exactly what a merge means: in both XLS and XLSX, a merged rectangle renders the content of one cell, the top-left anchor, and treats the rest as covered space that holds values but never shows them. Excel users absorb this through trial and error. A report generator has to encode it as a rule, because in generated code the symptom is a blank region with no exception to trace it to. HotXLS, a native Object Pascal library that reads and writes both Excel formats from Delphi and C++Builder, surfaces the merge table explicitly enough that you can program against the rule rather than rediscover it in a support ticket
One value, one anchor
A merge is a display instruction layered over a grid that does not change shape. Every covered cell still exists in the file as its own slot; the merge record only tells a consumer to paint the anchor's content across the rectangle. That distinction drives three behaviors worth internalizing before you write any layout code. Reading a covered cell returns its own stored value, which for a banner you built is usually empty, so any code that inspects a merged title has to resolve and read the anchor. Writing to a covered cell succeeds at the file level and shows up nowhere, which is the invisible-header trap from the opening. And unmerging a region exposes whatever was sitting under it the whole time, so a stray value written into covered space turns into a visible defect the day someone dissolves the merge
On the XLSX side that table is a first-class object. Sheet.MergedCells carries Add('A1:C1'), FindAt(Row, Col), DeleteAt and Items, and the one call you reach for most is FindAt: hand it any coordinate and it returns the merged region covering that cell, or nil when the cell stands alone. That single lookup is the foundation for both halves of correct merge handling, the safe read and the write guard, and both show up later
Two facades, two merge idioms
HotXLS keeps the classic BIFF8 .xls engine and the OOXML .xlsx engine as separate object models, and they spell merging differently because they descend from different conventions. The XLS facade follows the Excel COM idiom: you take a range from a two-argument indexed property and call Merge with an OleVariant whose value decides the geometry you end up with
var
Book: IXLSWorkbook; // interface-counted: no manual Free
Sh: IXLSWorksheet;
begin
Book := TXLSWorkbook.Create;
Sh := Book.Sheets[1]; // XLS sheet collection is 1-based
Sh.Range['A1', 'F1'].Merge(False); // False = one merged block
Sh.Cells.Item[1, 1].Value := 'Quarterly Statement';
Sh.Range['A3', 'F4'].Merge(True); // True = merge across: one merge per row
Book.SaveAs('layout.xls');
end;The argument to Merge is the part people get wrong. Over a two-row range, Merge(True) produces two independent one-row merges, which is Excel's "Merge Across" and exactly what you want for a stacked header band that should keep its rows separable. Merge(False) fuses the whole rectangle into a single block. The range also reports MergeCells as a state flag, returns the containing region through MergeArea, and dissolves itself with Unmerge. The XLSX facade exposes the same operations under different names: Sheet.MergeCells(Row1, Col1, Row2, Col2) takes integer bounds, TXLSXRange.Merge accepts the equivalent Across variant, and the MergedCells collection holds the result
A template that grows with its data
A real report template is not a fixed grid. The header and totals are fixed, but the detail section between them stretches to whatever the query returns. The pattern that holds up keeps one fully styled detail row in the template, clones it once per record, and then opens a gap ahead of the totals block so everything anchored below slides down without losing its formatting
Sheet.Range['A1:F1'].Merge;
Sheet.Cells[1, 1].Value := 'INVOICE #2026-0611'; // value goes to the anchor, A1
Sheet.RowHeight[1] := 28;
TitleFont := Book.Fonts.Add('Calibri', 16, True, False);
Sheet.Cells[1, 1].FontIndex := TitleFont + 1; // pool index 0-based, cell side 1-based
// row 5 is the styled detail template line
for I := 0 to ItemCount - 1 do
Sheet.CopyRange(5, 1, 5, 6, 6 + I, 1); // styles and formulas travel with it
// open a gap above the totals block; content below shifts down
Sheet.InsertRows(6 + ItemCount, 1);
Sheet.Range['A1:F1'].SetBorders(xlsxEdgeOutline, xlsxBorderMedium);Two lines repay a second look. The font assignment carries an off-by-one that bites silently: Fonts.Add hands back a 0-based pool position, while a cell stores a 1-based font reference where 0 means the default font, so dropping the + 1 does not raise anything, it just styles your title in the wrong typeface. The other line is CopyRange, which moves formatting and formulas along with values. That is the whole reason to clone a hand-built template row instead of reconstructing its look in code. A designer owns the appearance once, in the template; the generator only ever pours data into copies of it
That split scales further when the reusable layout lives in its own workbook, say a sheet of header and footer bands shared across reports. CopyRangeTo performs the same clone across worksheet boundaries, taking a target sheet plus destination coordinates, so a generator can keep one pristine template sheet and stamp its regions into as many output sheets as a job needs. The alternative, mutating the template in place and trying to restore it afterward, is the kind of thing that works until the day a run aborts halfway
What InsertRows moves, and what it does not
The grow-a-template pattern only works because XLSX InsertRows is a structural edit rather than a cell shuffle. When it opens a gap it relocates the merged regions, row heights, hyperlinks, comments, frozen panes, autofilter ranges, conditional formats, data validations, tables, defined names, image anchors and chart anchors that sit below the insertion point, not just the cell values. That is what lets the totals block arrive at its new row with its merges and number formats intact instead of arriving stripped
Its two documented limits are the ones to design around. Formula adjustment is scoped to the sheet being edited: references inside that sheet are rewritten, and a formula on another sheet that points into the shifted area is rewritten too, but the adjustment only follows references that target the edited sheet, so any cross-workbook reference scheme deserves its own audit rather than blind trust. The second limit is sharper, and it is on the XLS side. Pivot tables survive open-save cycles as raw preserved records, not as modeled objects HotXLS can move, so inserting rows does not relocate a pivot's footprint. Any template you build for the .xls format should park its pivot regions well clear of any band that grows
Refusing to write data into layout space
The merged-cell failure that actually reaches production is not the cosmetic one. It is structural: a detail row drifts into a merged layout band, its values land in covered cells and turn invisible, and the column totals quietly stop matching what anyone reading the sheet can see. Because FindAt answers the covering-region question for any coordinate, the generator can refuse that write at the moment it would happen rather than ship a report that silently undercounts
// refuse to write detail data into a merged layout region
if Sheet.MergedCells.FindAt(Row, 1) <> nil then
raise Exception.CreateFmt('row %d overlaps a merged layout region', [Row]);
Sheet.Cells[Row, 1].Value := Detail.Description;The same boundary check belongs anywhere a user will later sort or filter the output. A range with merges inside it cannot sort cleanly, because the sort moves rows independently and a merge spanning rows has no single row to travel with; Excel responds with an error or a scrambled layout. The discipline that keeps reports correct is geographic. Confine merges to title bands, section dividers and signature blocks, and keep the tabular middle of the sheet flat. The template report generation article develops this layout-versus-data split into a full placeholder-driven workflow, and the conditional formatting and rich text article covers styling that flat data band
How merges degrade on the way out
A merge is a workbook concept, and each text-oriented export format honors it to a different degree. Knowing the three behaviors up front saves a QA cycle. HTML export reproduces merges faithfully, emitting colspan and rowspan on a single table, so a browser-bound report keeps its banded look. RTF export does not span columns at all: the anchor text lands in its own cell and the remaining width of the merge comes out as empty cells, which leaves a wide title visually shoved to the left in a word processor. CSV has no concept of a merge, so the anchor value occupies one field and every covered cell emits as an empty field. The takeaway for a workbook that also feeds delimited exports is to keep anything load-bearing out of merged geometry; the CSV, TSV and HTML export article walks through each format in detail
One reassurance for anyone weighing this against file size: merges cost almost nothing at report scale. The merge table is tiny next to the cell data, and reading a covered cell still goes through FindAt rather than scanning. The performance pressure on large workbooks comes from elsewhere, mainly style-pool growth and the memory the save path holds, which the large workbook performance article takes up directly. Both merge APIs, the structural editing operations, and the template demos ship with the HotXLS Component