The cells an xlsx does not store

An earlier post here was about where an xlsx keeps the meaning of a value: the type, the number format, the workbook's date epoch. See where an xlsx keeps its meaning. Every answer in it was somewhere other than the cell, and a reader that stopped at the value got something plausible and wrong.

This post is about the grid itself. Not what a cell means, but which cells exist, and how many rows and columns there are. That sounds like the easy half. It is not, for one reason: a worksheet is not stored as a grid. It is stored as a list of the rows that have something in them, each holding a list of the cells that have something in them, and three common structures mean the thing you want is not in that list at all.

So the reader has to fill the gap, and the readers do not fill it the same way. None of them errors. Each one returns a different sheet.

The file

Everything below is measured, not inferred. I wrote one worksheet by hand, zipped it into an xlsx, and ran four readers over it. The worksheet is short enough to print whole:

<worksheet>  <!-- namespace declaration omitted for brevity -->
<dimension ref="A1:B3"/>
<sheetData>
<row r="1"><c r="A1" t="inlineStr"><is><t>Region</t></is></c></row>
<row r="2"><c r="A2"><v>10</v></c><c r="C2"><f t="shared" ref="C2:C4" si="0">A2*2</f><v>20</v></c></row>
<row r="3"><c r="A3"><v>11</v></c><c r="C3"><f t="shared" si="0"/><v>22</v></c></row>
<row r="4"><c r="A4"><v>12</v></c><c r="C4"><f t="shared" si="0"/><v>24</v></c></row>
<row r="6"><c r="E6"><f t="shared" ref="E6:F7" si="1">A2+1</f><v>11</v></c><c r="F6"><f t="shared" si="1"/><v>11</v></c></row>
<row r="7"><c r="E7"><f t="shared" si="1"/><v>11</v></c><c r="F7"><f t="shared" si="1"/><v>11</v></c></row>
<row r="9"><c><v>1</v></c><c><v>2</v></c></row>
</sheetData>
<mergeCells count="1"><mergeCell ref="A1:C1"/></mergeCells>
</worksheet>

Seven row elements, thirteen cell elements. On screen that is a sheet nine rows deep and six columns wide, with a heading merged across the top, a doubled column, a small block of sums, and a pair of numbers at the bottom. Read it and count: rows 5 and 8 do not appear, B2 does not appear, B1 and C1 do not appear although the heading is merged across them, the cells in row 9 carry no position, and the dimension element at the top says the sheet is A1:B3, which is smaller than its own contents.

All four readers opened it without complaint. The four are openpyxl 3.1.5 (Python, installed from PyPI and read from the installed package), excelize 2.9.0 (Go), calamine 0.26.1 (Rust) and SheetJS (JavaScript), run twice: 0.18.5, which is still what npm install xlsx gives you, and 0.20.3, which the vendor publishes only from its own CDN. The two SheetJS builds printed identical results for everything below. Two of these are not current releases, and that turns out to matter; it comes up where it does.

A merged range stores one value, and the four readers hand back four different things

The heading spans A1 to C1. The fact that it does is recorded once, at the bottom of the worksheet, in a list that has nothing to do with the cells: <mergeCells count="1"><mergeCell ref="A1:C1"/></mergeCells>. The value lives in A1 alone. B1 and C1 are not in the file. That is the arrangement the standard describes: the example under ECMA-376 Part 1, §18.3.1.55, says "the formatting and content for the merged range is always stored in the top left cell". It says nothing about what the covered cells are.

So "what is in C1" has no answer in the bytes, and each reader invents one. Asked for A1, B1 and C1 in turn:

Reader	A1	B1	C1
openpyxl	'Region'	None	None
calamine	String("Region")	Empty	Empty
SheetJS	{t:"s", v:"Region"}	undefined	undefined
excelize	"Region"	"Region"	"Region"

Three readers give you one label and two blanks. One gives you the label three times.

Both positions are deliberate and both are written down. openpyxl hands back an object of a separate class whose docstring is a single sentence: "The value of a MergedCell is always None." excelize says the opposite in the doc comment on MergeCell: "Merging cells only keeps the upper-left cell value, and discards the other values."

excelize's reading is not a quirk of its merge writer, it is wired into every read. Before any cell lookup, the requested reference is rewritten:

if cellInRange([]int{col, row}, ws.MergeCells.Cells[i].rect) {
    cell = strings.Split(ws.MergeCells.Cells[i].Ref, ":")[0]
    break
}

strings.Split(ref, ":")[0] is the top-left cell of the range. Ask excelize for C1 and it asks the file for A1. GetMergeCells does the same split and then reads that cell, so the library's own idea of a merged range's value is the top-left cell's value.

This is the one on the list most likely to be mistaken for a data bug rather than a reader difference, because it changes totals. Count the populated cells in that heading row and the answer is 1 or 3 depending on which library you linked. Neither number is wrong about the file. They are answers to different questions, and the file asks neither.

Worth noting what all four agree on: the merge list is available. calamine keeps it behind a separate load_merged_regions call you have to make on purpose, SheetJS exposes it as !merges, openpyxl as merged_cells.ranges. The structure is never hidden. It is just not where you are looking when you read a cell.

A filled-down formula is stored once

Column C in the file is A2*2 filled down three rows. It is not stored three times. The first cell carries the formula text, a range it governs, and an index: <f t="shared" ref="C2:C4" si="0">A2*2</f>. The two below it carry the index and nothing else: <f t="shared" si="0"/>. There is no formula text in C3 or C4 to read. The standard puts it this way (§18.3.1.40): "Subsequent cells sharing this formula need not have the formula written in their f element. Instead, the attribute si value for a particular cell is used to figure what the formula expression should be based on the cell's relative location to the master formula cell."

A reader that wants to tell you C3 is computed therefore has to find the cell that owns index 0 and shift the references by the distance between the two cells. All four do. Asked for the formula in each cell of that column, all four returned A2*2, A3*2, A4*2.

The second group in the file is the interesting one, because its range spans both rows and columns: ref="E6:F7", four cells. Three readers translated all four. calamine 0.26.1 translated two:

Cell	openpyxl	excelize	SheetJS	calamine 0.26.1
E6	=A2+1	A2+1	A2+1	A2+1
F6	=B2+1	B2+1	B2+1	(none)
E7	=A3+1	A3+1	A3+1	A3+1
F7	=B3+1	B3+1	B3+1	(none)

The mechanism is visible in calamine's reader. When it meets the owning cell it builds a map from cell position to reference offset, and it builds that map in two branches: one if the range spans more than one row, another if it spans more than one column. A range that spans both takes the first branch, which walks only the range's first column. Every follower outside that column looks itself up in a map that was never given an entry for it, finds nothing, and ends with no formula at all. Not an error, not an empty string. The cell simply has no formula as far as the API is concerned.

Whether that matters depends entirely on what you asked for. The cached results were still there and still correct, so a reader after values would never notice. A reader after formulas, to warn someone that a column is computed and should not be edited, loses half the block silently.

This one has been fixed. calamine 0.32.0 (2025-11-20) lists "Fixed and extended support for XLSX shared formulas with handling of ranges" in its changelog, and from that release the map is built over every row and every column of the range. So the table above is a statement about 0.26.1, not about calamine. It is in the post anyway, because it is the clearest case of the general point: a reader translating shared formulas is doing real work with real edge cases, and the version you pinned a year ago is the one that runs.

Which brings up the other half of a formula cell. The formula and the last computed result are two independent fields. The standard says the v element holds "the last calculated result of the formula" (§18.3.1.96), and nothing in the file ties the two together. I wrote this one's cached values inconsistently with its formulas on purpose: every cell in the E6:F7 block holds <v>11</v>, which is not what B2+1 evaluates to given that B2 is empty. All four readers reported the formula when asked for the formula and 11 when asked for the value, and not one of them remarked on the gap. There is no check to fail. A file whose cached values were computed before someone edited the inputs is a valid file, and it reads clean.

Empty cells are absent, so the size of the sheet is a claim the file makes

Rows 5 and 8 are not in the file. Neither is B2. The two cells in row 9 have no r attribute, so their column is whatever their order implies. All of that is allowed: in the standard's schema, r is optional on both row and c, and the only illustration of an empty sheet region (under §18.3.1.73) writes rows 1 and 10 and nothing between them. What a reader should do with a cell that has no r, the standard does not say.

None of that caused trouble. All four readers placed row 9's values in A9 and B9 from position alone, and all four filled the absent rows with blanks rather than closing the gap. Count the row elements in the file and you get seven; openpyxl, calamine and excelize all report nine, which is the number that matches the screen. SheetJS also reports nine, but only once the next problem is out of the way, which is the subject of the rest of this section.

The extent is the part that goes wrong, and it goes wrong because the file states it. <dimension ref="A1:B3"/> is a declaration about the used range, sitting above the data, and nothing validates it against the data underneath. Here it is a lie: the content runs to F9. The standard (§18.3.1.35) defines it as "the row and column bounds of used cells in the worksheet" and, in the same paragraph, calls it "optional and is not required". It does not say what a reader should do when the declaration and the cells disagree.

openpyxl and calamine ignore the declaration and compute the extent from the cells they actually read. Both report A1:F9, nine rows by six columns.

excelize does something stranger. Its GetRows walks the data and returns nine rows, while GetSheetDimension, whose doc comment says it "provides the method to get the used range of the worksheet", returns the string A1:B3. It is handing back the file's claim under a name that describes the measurement. Both are available from the same open file, and they disagree. The current release, 2.11.0, still returns the stored ref without recomputing it.

SheetJS believes the declaration, and that is the consequential case, because believing it loses data:

sheet_to_json(ws, {header: 1})
  => [["Region"], [10], [11]]

Three rows and one column, out of a sheet with nine rows and six columns. Every number in columns C through F, and everything below row 3, is gone. The cells were parsed correctly: ask SheetJS for C2 directly and it has it, formula and value both. They are missing from the output because the conversion iterates the sheet's !ref, and !ref came from the dimension element.

The chain is three short lines. The declaration is accepted if it is merely well formed, with no comparison against the content: if(d.s.r<=d.e.r && d.s.c<=d.e.c && d.s.r>=0 && d.s.c>=0) ws["!ref"] = encode_range(d);. While parsing the rows the parser does track a true extent, in a variable named refguess, but it only uses it if nothing has claimed the field already:

if(!s["!ref"] && refguess.e.c >= refguess.s.c && refguess.e.r >= refguess.s.r) s["!ref"] = encode_range(refguess);

The guard is !s["!ref"]. The accurate extent is computed and then thrown away whenever the file declared one.

That produces a result worth stating on its own, because it inverts the usual advice. Writing no dimension at all is safer than writing a wrong one. Same file, same cells, three spellings of the extent:

dimension in the file	SheetJS !ref	Rows from sheet_to_json
absent	A1:F9	9
ref="A1"	A1:F9	9
ref="A1:A1"	A1	1
ref="A1:B3"	A1:B3	3

Remove the element and SheetJS computes the right answer. Write A1:A1, which asserts the same thing as a single-cell sheet, and you get one value out of the thirteen the file holds. The difference between rows two and three of that table is a colon: the pattern the parser matches the attribute with is /"(\w*:\w*)"/, which requires one, so ref="A1" is not recognised as a dimension at all and falls through to the computed extent.

If you generate xlsx files, this is the line to take away. A conservative placeholder dimension is not conservative. It is a smaller number that one widely used reader will prefer over the truth.

If you read them with SheetJS, there is a switch. 0.20.3 has a parse option, nodim, documented as "If true, calculate worksheet ranges". With it, all four variants in that table come back as A1:F9 and nine rows. 0.18.5, the one npm hands out, does not have the option.

Where TableRune fits

TableRune imports spreadsheets in the browser, with nothing uploaded, so the reader choice above is one we make rather than describe. src/lib/import/workbook.ts lazy-loads SheetJS, reads the workbook, and converts the chosen sheet to CSV bytes that feed the same DuckDB path a real CSV takes.

That makes !ref the authority for how much of a sheet gets imported, in both directions. The preview grid decodes it to decide which rows to read. The CSV conversion narrows it on a shallow copy of the worksheet, to drop leading title rows without disturbing the cells:

const range = XLSX.utils.decode_range(ref);
range.s.r = Math.min(range.s.r + gridHeader, range.e.r);
const sliced = { ...ws, '!ref': XLSX.utils.encode_range(range) };

The merged-range behaviour is already load-bearing there, and the code says so. The header detector's job is to skip the title banner that real exports carry above the labels, and its comment names the case directly: it skips leading rows that are blank or "hold a single value (a merged title)". That works because SheetJS leaves a merged range's other cells undefined, so a banner merged across three columns arrives as a row with one populated cell, and one populated cell is the signal. The unit test for it uses exactly that shape, a title string followed by two empty strings.

Run that function against both readings of the same banner and the dependency is plain. Given blanks in the covered cells it returns row 1, the real labels. Given the top-left value propagated across them, the reading excelize would supply, it returns row 0, which would name every column after the banner and push the real labels down into the data. The heuristic is sound, and it is sound because of a choice its own source file does not make.

The other dependency was not sound, and writing this post is how we found it. Because !ref is the import extent, TableRune inherited SheetJS's trust in the dimension element. We copied the import function out of the source and ran it, with the SheetJS build TableRune ships, against the file above. It produced three lines of CSV, Region,, 10, and 11,, and no error. With ref="A1:A1" it produced one. A user importing a file whose writer got the dimension wrong would have seen a clean import of part of their data.

The obvious fix was the nodim option from the previous section, and we did not ship it. nodim also starts every range at A1, so a sheet whose data correctly begins at C3 came back with two empty columns in front of it. What shipped instead re-derives each sheet's range from the cells SheetJS actually parsed, straight after reading the workbook, and ignores the declaration entirely. Two tests hold it there: one writes a workbook, shrinks its dimension, and checks every row comes back; the other puts the data at C3 and checks no empty columns appear. It shipped in TableRune on 6 October 2026, in commit 1b04298.

If you are writing an importer

1. Decide what a merged range's covered cells mean to you, write it down, and do not let the library decide by default. The two defensible answers differ by a factor of three on a cell count.
2. Compute the extent from the cells you read. Treat a declared dimension as a hint worth comparing against, and if it disagrees with the content, trust the content and say so.
3. If you generate xlsx, either write a correct dimension or write none.
4. Do not infer "this column is not a formula" from an empty formula field. Shared groups mean the text is in another cell, and a reader as recent as last year did not always find it for you.
5. Treat a cached result and its formula as two separate pieces of evidence that happen to sit together, because that is what they are.
6. Count rows the way your user does, including the absent ones. All four of these libraries already do, which is a good sign that it is the right answer.

The pattern is the same one as last time, one level up. An xlsx stores the sheet economically, leaving out everything derivable, and every omission is a question the format answers somewhere other than where you are reading. Last time that cost you a value's meaning. This time it costs you the shape of the table.

What this post covers. It reports what four named readers, at the versions linked above, did with one worksheet I wrote by hand, plus the code paths in their source that produce that behaviour. The readers were installed and run, so the tables are measured output rather than a reading of the code. Where a later release behaves differently, the post says so, from that release's source. Section numbers refer to ECMA-376 Part 1, 5th edition (2016); the merged-range sentence comes from an example, which the standard treats as informative. It makes no claim about what Microsoft Excel itself writes, which was not available to test. The file is deliberately awkward in ways a real export may not be. It shows what the readers do when the format allows something, not how often files in the wild do it.