A fourteen-column spreadsheet with 45 rows of data, saved as ODS, usually ends with one more element than you expect: <table:table-row table:style-name="ro1" table:number-rows-repeated="1048530"><table:table-cell table:number-columns-repeated="14"/></table:table-row>. That single line describes every row from 46 down to the bottom of the grid. Expand it literally, as that row written out 1,048,530 times, and a small content.xml turns into a million row records, a million height entries and, if the columns carry a default cell style, nearly fifteen million styled blank cells
Why a single repeated row is expensive to import
The cost comes from expansion, and the expansion is the obvious way to write an importer. Style ro1 sets style:row-height="0.452cm", which is 12.81 points. A row finisher that applies it with one SetRowHeight(RowIndex + i, RowHeight) call per repeated row writes 1,048,530 entries into whatever per-row height table the object model keeps. If that table is a keyed string list where each write first searches for an existing key, the import is quadratic in the repeat count, and a repeat count of a million is precisely the input where quadratic stops being theoretical
Blank cells make it worse. Each <table:table-column> in such a file usually names a table:default-cell-style-name, and every empty cell in the repeated row inherits it. A styled blank is still a cell as far as most object models are concerned. Materialise one per column per repeated row and the memory bill dwarfs the height table
The save side has the mirror-image problem. Once the model says that row 1,048,575 has a height, a writer that walks from row 1 to the last row with anything on it emits a million <table:table-row> elements. A cheap import followed by an expensive save only moves the cost to a different button
How HotXLS stores a repeated row height
HotXLS Delphi Excel Component stores a repeated ODS row height as a run: a TXLSXRowHeightRun record holding FirstRow, LastRow and one Height. When TXLSXWorkbook.OpenODS finishes a row whose repeat count is 1, it calls SetRowHeight like any other code would. For any larger count it records the whole stretch as one run, so the tail in the example becomes rows 46 to 1,048,575 at 12.81 pt. A repeat count that runs past the end of the grid is cut at row 1,048,576 instead of being rejected
One rule in the importer surprises people who diff the result against the source file. Spreadsheet applications that write ODS tend to give every row style a style:row-height, including rows nobody ever resized, and mark those with style:use-optimal-row-height="true". For a row that stores no cells at all, an automatic height is simply the default height, so HotXLS records no height for it. Rows that do store something keep their height even when it is marked optimal, because a wrapped-text row that grew to fit its content would otherwise shrink back when the workbook is reopened as XLSX. That trade-off is the right one: dropping a real height produces a visible layout change, while keeping a redundant one costs a single run
Reading heights back goes through the ordinary RowHeight property. It checks the per-row list first and falls back to the runs only when the row has no explicit entry; HasRowHeight follows the same order. Runs never overlap, so the fallback stops at the first run that contains the row
var
Workbook: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Spans: TXLSXAxisSpans;
I: Integer;
begin
Workbook := TXLSXWorkbook.Create;
try
// A sheet whose last row element repeats 1,048,530 times
Workbook.OpenODS('tail-repeat.ods');
Sheet := Workbook.Sheets[1]; // Sheets is 1-based
// Stored row metadata: single-row entries first, then runs
Spans := Sheet.GetStoredAxisSpans(True);
for I := 0 to High(Spans) do
Writeln(Spans[I].FirstIndex, '..', Spans[I].LastIndex);
// Both reads come from the same run
Writeln(Sheet.RowHeight[46]:0:2, ' pt');
Writeln(Sheet.RowHeight[1048575]:0:2, ' pt');
// A per-row entry is looked up before the run, so it wins
Sheet.RowHeight[500000] := 36;
// Clearing one row inside the run cuts a one-row hole in it
Sheet.ClearRowHeight(500001);
Writeln(Sheet.HasRowHeight(500001)); // FALSE
Writeln(Sheet.RowHeight[500002]:0:2, ' pt'); // still the run height
finally
Workbook.Free;
end;
end;The two edits in that example behave differently on purpose. Sheet.RowHeight[500000] := 36 leaves the run alone and adds one entry to the per-row list, which shadows the run because it is consulted first. ClearRowHeight cannot work that way, since a cleared row has to report no height even though a run still spans it. So it deletes any per-row entry and then rebuilds the run list: every run outside the cleared row is kept as is, the run that contained it is split into the part before and the part after, and nothing is stored for row 500,001 itself. GetStoredAxisSpans(True) now lists 46..500000 and 500002..1048575 as two separate runs, next to the single-row entry for 500,000. ClearRowHeights empties the per-row list and the runs in one call
Where do the inherited blank-cell styles go?
Inherited blank styles become style overlays: rectangles on the worksheet that carry a style, with no cell objects behind them. While reading a row, the importer walks the repeated cells in spans of adjacent columns that resolve to the same style. A span whose style looks exactly like Default stores nothing. A span with real formatting, say a centred or bold column default, becomes one row value covering all of its columns
When that row value is committed and the row repeats, HotXLS materialises a single cell at the top-left of the span, applies the inherited style to it, and registers the six style indexes (font, fill, border, number format, alignment, protection) through Sheet.StyleOverlays.Add for the whole rectangle: every column in the span, from the first repeated row to the last. Nothing else is created for the remaining 1,048,529 rows. Sheet.Cells.Count stays close to the number of cells that actually hold data
A cell inside the rectangle picks up the overlay the moment your code touches it. After opening a sheet whose tail is formatted by a bold column default, Sheet.Cells[700000, 1].FontIndex resolves to the bold font even though no cell existed at that coordinate a line earlier. XLSX files with a formatted-but-empty column go through the same machinery, and the article on row-block cell storage and interval style overlays covers how several overlays layer and resolve. The ODS importer differs only in where the overlays come from: it derives them from repeat counts on its own
How SaveAsODS writes the repeat count back
TXLSXWorkbook.SaveAsODS writes each stretch of identical rows as one <table:table-row> with table:number-rows-repeated, and ends a stretch only where something about the rows changes. The table extends down to the lowest row that carries anything: a value, a style-only blank cell, a single-row height, the end of a height run, the bottom edge of an overlay, a hidden or outline entry, or a validation or conditional-format area
From row 1, the writer looks ahead for the next boundary. Candidates are the next materialised cell, the start or end of a height run, any single-row entry (height, hidden flag, outline level, collapsed state, manual page break), overlay edges, merged ranges, hyperlinks, comments, images, and the edges of every validation and conditional-format rectangle. Everything up to that boundary is one element. Inside each element the same logic runs across columns, so an empty stretch becomes one <table:table-cell> with table:number-columns-repeated and the style name that the overlay resolves to
Row styles are shared. The row height is formatted as text, 12.81pt for the example, and that text together with the hidden flag and the page-break flag forms the key for the automatic row style. Every row with the same three values points at the same ro<N> style, which holds a single style:row-height property. A row that has no height, is visible and has no break gets no row style at all
var
Workbook, Reopened: TXLSXWorkbook;
Saved: TMemoryStream;
begin
Workbook := TXLSXWorkbook.Create;
Reopened := TXLSXWorkbook.Create;
Saved := TMemoryStream.Create;
try
Workbook.OpenODS('tail-repeat.ods');
Workbook.Sheets[1].RowHeight[500000] := 36;
Workbook.Sheets[1].ClearRowHeight(500001);
// The tail is written as a few repeated row elements
Workbook.SaveAsODS(Saved);
Writeln('ODS stream: ', Saved.Size, ' bytes');
Saved.Position := 0;
Reopened.OpenODS(Saved);
// Expected: override, hole and both run pieces survive the round trip
Writeln(Reopened.Sheets[1].RowHeight[500000]:0:2); // 36.00
Writeln(Reopened.Sheets[1].HasRowHeight(500001)); // FALSE
Writeln(Reopened.Sheets[1].RowHeight[500002]:0:2); // the run height
finally
Saved.Free;
Reopened.Free;
Workbook.Free;
end;
end;For the sheet above, the tail splits into four pieces: rows 46 to 499,999 at the run height, row 500,000 at 36 pt, row 500,001 with no row style, and rows 500,002 to 1,048,575 at the run height again. Any column whose default style left an overlay can add a split where that overlay ends. The 45 data rows above the tail are written one element per row because each of them holds cells
XLSX is a different story. SpreadsheetML has no repeat attribute, and a <row> element describes exactly one row, so saving a run-backed sheet as .xlsx enumerates the rows a run covers and writes ht and customHeight="1" on each of them. The model stays compact in memory; the target format decides how large the file gets. If a workbook arrives as ODS and only has to leave as ODS, it never pays that per-row cost
Edits that renumber rows
A second representation of row heights is only correct if every operation that moves rows moves it too. TXLSXWorksheet.InsertRows and DeleteRows shift the runs along with the per-row lists: the part of a run before the edit point stays where it is, rows inside a deleted window are dropped, and the rest moves by the inserted or deleted count. A run that straddles an insertion therefore becomes two runs with a gap of unsized rows between them, and a run that straddles a deletion becomes shorter
procedure ShiftAroundTheRun(Sheet: TXLSXWorksheet);
begin
Sheet.RowHeight[500000] := 36;
Sheet.ClearRowHeight(500001);
// Two new rows at 500000: override moves to 500002, hole to 500003
Sheet.InsertRows(500000, 2);
Writeln(Sheet.RowHeight[500002]:0:2); // 36.00
Writeln(Sheet.HasRowHeight(500003)); // FALSE
Writeln(Sheet.HasRowHeight(500000)); // FALSE: inserted rows have no height
// Remove them again: the override and the hole return to their old row numbers
Sheet.DeleteRows(500000, 2);
Writeln(Sheet.RowHeight[500000]:0:2); // 36.00
Writeln(Sheet.HasRowHeight(500001)); // FALSE
Writeln(Sheet.UsedRange.Row2); // includes the end of the shifted run
end;The round trip does not leave the tail exactly where it was. The insert pushes the tail run from 500,002..1,048,575 to 500,004..1,048,577, and the grid ends at row 1,048,576, so the part past that row is cut off. The delete then moves what is left up by two, and the run ends at row 1,048,574: row 1,048,575 no longer has a stored height. If the last rows of the grid matter to you, set their height again after an insert and delete pair. Rows inserted into the middle of a run do not pick up its height either: they open a gap of unsized rows and do not inherit the run height, which is why HasRowHeight(500000) printed FALSE above
The other row-moving operations follow the same pattern. TileRangeAxisMetadata clears the runs across the whole tiled span and then registers each source run once per copy at its offset. TXLSXWorksheet.CopyFrom and TXLSXSheets.AddCopy give the new sheet its own copy of the run list, so ClearRowHeights on a clone leaves the original's heights in place. On the reading side, UsedRange extends to the first and last row of every run, and the XLSX writer's row walk extends through the runs so height-only rows are still written
Hidden rows and outline levels are kept per row
Row heights and inherited blank styles are the two things that collapse into runs and overlays. Hidden flags, outline levels and collapsed state are recorded once per row. A repeated row that is hidden, or that sits inside a <table:table-row-group>, produces one entry per repeated row for each of those attributes, and on save each such entry is a boundary of its own, so the hidden stretch is written one <table:table-row> per row
For templates you control, keep the hidden and grouped part of a sheet as tight as the data needs. Hiding twenty rows below a report is free. Hiding everything from row 46 to the bottom of the grid means a million entries on load and a million row elements on save, and the only visible difference is that nobody can scroll past the report any more
The workbook-level side of the format, including which ODS features map onto which parts of the object model, is in the walkthrough of opening and saving ODS files, and other levers for big files are collected under large workbook performance. The row element itself, with its repeat and style attributes, is defined in the table chapter of ODF 1.3 Part 3
A million-row repeat is a format detail that a native reader has to model well, because there is no office suite in the background to absorb it. The HotXLS Delphi spreadsheet component page lists the XLS, XLSX and ODS features it reads and writes, along with the supported Delphi and C++Builder versions