The empty spreadsheet cell that ate the next three columns

An importer read .xlsx files correctly for months, then started dropping values out of certain rows. Not all rows. Not all files. The rows that broke had one thing in common, and it took an embarrassingly long time to see it: they contained a blank cell.

The setup

An .xlsx file is a zip of XML. A row in a worksheet looks like this:

<row r="7">
  <c r="A7" t="s"><v>12</v></c>
  <c r="B7"/>
  <c r="C7"><v>31432</v></c>
  <c r="D7"><v>4</v></c>
</row>

B7 is empty, so Excel writes it self-closing. That is the entire bug surface.

The parser matched cells with what looks like a careful regex — it handles both forms, after all:

const CELL = /<c\s[^>]*>|<c\s[^>]*\/>/g;   // open tag, or self-closing

Why it fails

Regex alternation takes the first branch that matches, not the best one.

Against <c r="B7"/>, the first branch <c\s[^>]*> is tried first. [^>]* means “anything that is not >” — and / is not >. So it happily consumes r="B7"/ and then matches the closing >.

The self-closing tag matched the open-tag branch. The second branch is unreachable for every input the first one can reach.

Downstream, the code believed it was inside an open cell and scanned forward for </c> to find the value. The next </c> in the document belongs to C7. So B7 swallowed C7’s value, the cursor resumed past it, and the row came out short and misaligned — every column after the blank shifted by one.

Which is the worst kind of parsing bug: not a crash, just quietly wrong numbers in the right-shaped table.

The fix

Stop trying to express “open tag or self-closing tag” as alternation. Match the tag once, then look at how it ended:

const TAG = /<c(\s[^>]*?)?(\/)?>/g;

for (const m of xml.matchAll(TAG)) {
  const attrs = m[1] ?? "";
  const selfClosing = m[2] === "/";

  if (selfClosing) {
    cells.push({ ref: refOf(attrs), value: null });
    continue;
  }

  const start = m.index + m[0].length;
  const end = xml.indexOf("</c>", start);
  cells.push({ ref: refOf(attrs), value: valueOf(xml.slice(start, end)) });
}

One pattern, one decision point, and the two cases cannot shadow each other.

The general rule

Never distinguish XML tag forms with an alternation of two patterns where one can match the other’s input. Scan the open tag, then determine how it terminated. The same trap is waiting in <v/>, <is/>, <t/>, and every other element the writer is free to collapse when it is empty.

The better lesson

We should not have been reading sheet XML with regular expressions at all.

The honest reason we did is that a real parser was a dependency we did not want, and the format looked simple in the three sample files we had. It was simple in those three files because none of them had a blank cell.

If you do hand-roll it, build the adversarial fixture first — a sheet with an empty cell, a cell containing >, a shared string with an escaped entity, and a row that skips column references entirely (Excel omits <c> for trailing blanks rather than emitting empty ones). Every one of those is a real thing Excel writes. Our three samples had none of them, which is exactly why they passed.

← All notes