The bug report said the invoice title had vanished. The generator merged A1:F1 correctly, styled it correctly — and wrote the title into B1. In both XLS and XLSX, a merged range displays the content of exactly one cell: the top-left anchor. Whatever ends up in the covered cells does not render, and content written there before a merge stops being visible the moment the merge is applied. Excel users learn this by trial and error; report generators have to learn it as a rule, because in generated code the symptom is a blank region and no error anywhere. HotXLS — a native Object Pascal library that reads and writes both Excel formats from Delphi and C++Builder — models merges explicitly enough that you can build on the rule instead of tripping over it.
One value, one anchor
Think of a merge as a display instruction layered over an unchanged grid. The cells still exist individually in the file; the merge record tells consumers to render the anchor's content across the rectangle. Three practical consequences follow. Reading any covered cell other than the anchor yields nothing useful, so code inspecting a merged title must address the anchor. Writing anywhere except the anchor produces invisible content — the "vanished title" above. And unmerging resurfaces whatever was stored underneath, which is why stray values under layout regions eventually become a customer-visible surprise.
On the XLSX side HotXLS exposes the merge table directly: Sheet.MergedCells is a collection with Add('A1:C1'), FindAt(Row, Col), DeleteAt and Items, and FindAt answers the essential question — given an arbitrary cell, which merged region, if any, covers it? That single lookup powers both safe reads (find the region, then read its anchor) and the data-region guard shown later.
The merge calls on each facade
The XLS facade follows the Excel COM idiom: ranges come from a two-argument indexed property, and Merge takes an OleVariant whose value changes the geometry of what you get.
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 True/False distinction matters for header bands: Merge(True) over a two-row range produces two independent one-row merges (Excel's "Merge Across"), while Merge(False) produces a single rectangle. The range also exposes MergeCells as a readable state flag, MergeArea to recover the containing region, and Unmerge to dissolve it. On the XLSX side the same operations appear as Sheet.MergeCells(Row1, Col1, Row2, Col2), TXLSXRange.Merge with an equivalent Across variant, and the MergedCells collection.
A template that grows: CopyRange plus InsertRows
Real report templates are not static — a detail section expands to match the data. The workable pattern keeps one styled template row in the layout and clones it per data row, then opens a gap before the totals block so everything anchored below shifts down intact:
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 deserve scrutiny. The font assignment carries the pool-index offset — Fonts.Add returns a 0-based pool position while cells store 1-based references with 0 meaning the default font, so omitting + 1 silently styles the title with the wrong font. And CopyRange copies formatting and formulas along with values, which is precisely why cloning a hand-styled template row beats rebuilding its formatting in code: the template designer owns the look, the generator owns the data.
When the layout library lives in a separate workbook — a sheet of reusable header bands, for instance — CopyRangeTo extends the same operation across worksheet boundaries, taking a target sheet plus destination coordinates. That allows a generator to keep one pristine template sheet and stamp its regions into as many output sheets as the report needs, rather than mutating the template in place and hoping to restore it.
What InsertRows shifts — and what it leaves behind
XLSX InsertRows is a structural edit, not a cell shuffle. It relocates merged regions, row heights, hyperlinks, comments, frozen panes, autofilter ranges, conditional formats, data validations, tables, defined names, image anchors and chart anchors along with the cells. That breadth is what makes the grow-a-template pattern safe: the totals block below the insertion point arrives at its new row with its merges and formats intact.
Two boundaries are documented and worth engineering around. Formula adjustment covers references to the sheet being edited — a formula on another sheet pointing into the shifted area is rewritten too, but only same-sheet-targeting references participate, so audit cross-workbook reference patterns separately. And on the XLS side, pivot tables survive open-save cycles as raw preserved records rather than as modeled objects, which means row insertion does not relocate a pivot's footprint. Templates destined for the XLS format should keep pivot regions well away from any band that grows.
Guarding data regions against layout regions
The classic merged-cell failure in production is not visual — it is structural: a data row lands inside a merged layout band, its values become invisible, and totals stop matching the visible sheet. Because MergedCells.FindAt can interrogate the merge table for any coordinate, the generator can enforce the boundary instead of hoping:
// 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 discipline applies to interactive features: ranges that users will sort or filter should contain no merges at all, since merged cells inside a sort range produce errors or scrambled layout in Excel. Keep merges in title bands, section separators and signature blocks; keep the tabular middle of the sheet flat. The template report generation article extends this layout-versus-data split into a full placeholder workflow, and the conditional formatting and rich text article covers styling the flat data band itself.
How merges survive export
Merged layout is a workbook concept, and every other output format degrades it differently — knowing how saves a round of QA surprises. HTML export renders merges faithfully as colspan and rowspan attributes on a single table, so browser-destined reports keep their banded look. RTF export does not span columns: the anchor's text lands in its own column and the rest of the merged width comes out as blank cells, so wide merged titles look left-shifted in word processors. CSV has no merge representation at all — the anchor value occupies one field and the covered cells emit as empty fields. If a workbook doubles as the source for delimited feeds, design the data region so nothing meaningful depends on merge geometry; the CSV, TSV and HTML export article details each format's behavior.
Merge questions from the support queue
How do I read the value of a merged region when I only have a covered cell?
Resolve the region first, then read its anchor. On XLSX, MergedCells.FindAt(Row, Col) returns the covering merged range or nil; on XLS, Range.MergeArea hands back the full region for any cell inside it. Reading the covered coordinate directly returns empty content even when the region visibly displays text.
Why did sorting my report scramble the layout?
The sort range almost certainly intersected a merged region. Sorting rearranges rows independently, and a merge that spans rows cannot move with only one of them. Keep merges out of sortable ranges, or unmerge, sort, and re-merge around the operation.
Do merged cells slow large workbooks down?
Not meaningfully at report scale — the merge table is small compared with cell data. The performance concerns for big files live elsewhere: style pool growth and save-path memory, covered in the large workbook performance article.
Both merge APIs, the structural editing operations, and template demos are included with the HotXLS Component.