← Blog
How it works

Where an xlsx keeps its meaning

“I am adding spreadsheet import to my product and every file I test finds a new way for it to be wrong.”

If you are writing an importer, CSV and xlsx fail you in opposite directions.

A CSV withholds. It does not say whether it has a header row, what separates its fields, or which way round a date is written, so the importer has to ask you and you have to know. We wrote about that already, in why your bank statement CSV will not import cleanly.

An xlsx is the other case. It does carry the answers. There is a type on the cell, a number format behind it, a date epoch declared once for the workbook, a list of sheets with their visibility, a record of which ranges are merged. The information is there, and importers still get it wrong. One bug report about a failing spreadsheet import ends with the reporter's own fix: "I tried saving the first sheet as a csv and importing that - this removed the error". The way round it was to throw away exactly what the xlsx carried.

They get it wrong because not one of those answers is stored in the cell. Each one is a reference to somewhere else in the archive, and a reader that stops at the value gets something that parses, looks reasonable, and is wrong. A missing answer makes an importer stop and ask. A wrong one makes it finish.

This post is about where those answers actually live. Everything below was read out of four readers people really use: calamine (Rust), Apache POI (Java), excelize (Go) and PhpSpreadsheet (PHP), each at a pinned version.

The cell has a type, and it is usually not the one you want

calamine's dispatch on a cell's t attribute is the whole shape of the problem in one match. A boolean and an error resolve from the value alone. A shared string is already an index into a different part of the archive. And a date resolves cleanly in the one branch where the cell says outright that it holds one:

Some(b"d") => {
    // date
    Ok(DataRef::DateTimeIso(v))
}

That branch is in the format on purpose. ECMA-376, the standard behind xlsx, lists d among the seven cell types it permits and defines it as "Cell contains a date in the ISO 8601 format" (Part 1, §18.18.11). Real files mostly do not use it. SheetJS, another widely used reader, says so in its documentation: "Typically dates are stored as n numeric cells, but the format supports a special type d where the data is an ISO 8601 date string. This is not used in the default Excel XLSX export and third-party support is poor."

So the branch real files take is the one where the cell says only that it holds a number:

Some(b"n") => {
    // n - number
    if v.is_empty() {
        Ok(DataRef::Empty)
    } else {
        v.parse()
            .map(|n| format_excel_f64_ref(n, cell_format, is_1904))
            .map_err(XlsxError::ParseFloat)
    }
}

Two extra arguments, and neither came from the cell. cell_format was resolved a moment earlier by taking the cell's s attribute as an index into the workbook's list of cell styles, each of which points at a number format. is_1904 was read off a different file inside the archive entirely. The number on its own does not know what it is.

There is also a None arm, because the type attribute is optional, and calamine's comment on it is honest about what that costs: "If type is not known, we try to parse as Float for utility, but fall back to String if this fails."

Whether a number is a date is decided by how it is displayed

POI states the mechanism plainly in a javadoc, and it is worth quoting because it is the part people are surprised by:

Check if a cell contains a date. Since dates are stored internally in Excel as double values we infer it is a date if it is formatted as such.

Inferred, from the formatting. Both readers therefore end up pattern-matching a display string to recover a type. POI checks the built-in format ids first and then, in its own words, "will check to see if the format string only contains date formatting characters (ymd-/), which covers most non US date formats". calamine does the same job in detect_custom_number_format, a character-by-character walk that tracks escapes, quoted literals, square brackets and an AM/PM state before concluding that a d, m, h, y or s outside all of those means this is a date.

The built-in ids are where it gets interesting, because the two readers disagree. calamine maps them like this:

14..=22 | 45 | 47 => CellFormat::DateTime,
46 => CellFormat::TimeDelta,

Id 46 is [h]:mm:ss, elapsed time, which calamine deliberately treats as a duration rather than a point in time. POI's isInternalDateFormat lists 0x2d, 0x2e and 0x2f, which is 45, 46 and 47, and returns true for all three. So a cell formatted as elapsed hours is a duration to one reader and a date to the other, from the same bytes. Neither is a bug. They are two defensible readings of a format string that was written to control rendering, not to record a type.

The epoch is one flag, in a different file, worth 1462 days

calamine reads it while parsing the workbook part:

self.is_1904 = match e.try_get_attribute("date1904")? {

Absent means false. And when it is true, the arithmetic is a single constant:

const EXCEL_1900_1904_DIFF: f64 = 1462.;

Microsoft gives the direction: "the serial number of a date in the 1900 date system is always 1,462 days greater than the serial number of the same date in the 1904 date system", and its worked example has 5 July 2011 as 40729 in one system and 39267 in the other. So the same serial resolves to two calendar dates 1462 days apart, four years and a day, and which one you get turns on a single attribute you will not see if you are looking at the cell. It lives in a part of the archive most importer bugs never reach.

POI and calamine both carry the 1900 leap-year bug, and resolve it differently

Excel's serial numbering includes 29 February 1900, a day that did not exist. Every reader has to decide what to do at that point, and you can see each one deciding.

POI writes it down:

int dayAdjust = -1; // Excel thinks 2/29/1900 is a valid date, which it isn't

with a second branch for dates before 1 March, whose comment says exactly where the collision goes: "If Excel date == 2/29/1900, will become 3/1/1900 in Java representation".

calamine writes it as a bare inequality against an epoch of 30 December 1899, with no comment at all:

let f = if f >= 60.0 { f } else { f + 1.0 };

Follow both code paths by hand and they part company on exactly one value. Serial 60 is 1 March 1900 to POI, which is what its comment promises, and 28 February 1900 to calamine. One reader double-books 1 March, the other double-books 28 February. Everything from serial 61 up agrees, which is why this almost never bites and is very hard to find when it does.

A percent is stored as the fraction, and the multiply happens on the way out

Built-in format 9 is 0% and format 10 is 0.00%. That is not a guess: POI's BuiltinFormats lists them in its class javadoc, and excelize's builtInNumFmt map spells out the same two entries independently.

What matters for an importer is where the hundred gets applied. In excelize it is in the formatter, at print time:

if nf.percent > 0 {
    num *= math.Pow(100, float64(nf.percent))
}

Note the Pow: the scale is applied once per percent sign in the format, so a format carrying two of them shifts by ten thousand. The stored value is the fraction throughout. Read the file and a share of 11.1 per cent is 0.111. Read the screen and it is 11.1%. Both are correct, and an importer that compares one against a threshold written for the other is out by two orders of magnitude without ever failing. It is not an exotic case either: a request to import a shared sports projection sheet lists "S% as a share (0.111)" among the things an importer would have to cope with, beside a row of weights above the headings and a second sheet that holds no stats.

A formula cell holds two values, and readers hand back different ones

An xlsx formula cell stores the formula and, usually, the result as of the last save beside it. Readers choose which one they hand you, and the cached result may not be there at all.

calamine's cell reader takes the cached result and skips the formula, returning DataRef::Empty for the <f> element so that the value comes from <v>; the formulas are available, but only through a separate worksheet_formula call. You get a number, computed by whatever wrote the file, whenever it last wrote it.

PhpSpreadsheet goes the other way. getValue() returns the stored value, which for a formula cell is the formula:

public function getValue(): mixed
{
    return $this->value;
}

getCalculatedValue() is a separate method that runs the calculation engine, and the first thing it does inside the formula branch is adopt the workbook's own calendar before evaluating, which is the epoch flag from earlier showing up a second time.

Of everything on this list, this is the one worth checking first, because it fails on files that look perfect. PhpSpreadsheet stores a formula cell's value with a plain (string) $value, so a check that reads getValue() sees the formula text, never the result. One project's importer refused its own blank template with 1000 errors for exactly this reason. Its blank-row test read getValue(), and the template's key column was a formula, so every pre-formatted row, 200 on each of five sheets, looked non-empty, survived the skip, and failed validation for having no name. The issue gives both readings of the same cell: getValue() returned =IF(B2="","",...) and the calculated value was ''. Filling in five real rows took the count from 1000 to 995. The file opened correctly in a spreadsheet. The template your own product ships can be the file that fails.

The structural questions have answers too, in other files

A merged range keeps one value. excelize says it in a doc comment: "Merging cells only keeps the upper-left cell value, and discards the other values." In a file written that way the other cells in the range hold nothing, and a merged heading spanning four columns gives you one label and three blanks.

"The first sheet" can mean three different things. A <sheet> entry carries a name, a sheetId, a relationship id and a state. The order of the entries is tab order; sheetId is not a position, and excelize warns about precisely that confusion: "Note that the active index is different from the ID returned by function GetSheetMap()." Separately, the workbook records an activeTab, the sheet it opens on. And state may be visible, hidden or veryHidden, which calamine parses as three cases with visible as the default. First in the list, lowest sheetId, and the one the workbook opens on can be three different sheets, and a file where they happen to coincide will never tell you that your importer is picking by the wrong rule.

Nothing marks a header row. Not in an ordinary worksheet. A real table object has a header row count, which excelize sets to zero when a table is created with its header hidden, so the concept exists in the format. It just does not exist for the plain grid most people paste their data into. Header detection is your heuristic, and it is guesswork whatever the file format.

If you are writing the importer

  1. Read the type attribute, and treat its absence as a case you handle rather than a default you assume.
  2. Resolve the style index to a number format and carry that, plus the workbook's epoch flag, alongside every numeric cell. A bare f64 has already lost the argument.
  3. Ask for the calculated value explicitly, and decide what you do when there is no cached result to read.
  4. Never compare a percentage against a threshold without knowing which side is the fraction.
  5. Show the reader what you inferred, per column, and let them correct it. Every ambiguity above is one an importer resolves silently by default, and a wrong silent answer costs more than a question.

The pattern under all of it is worth stating on its own. An xlsx separates what a value is from how it is shown, which is the right design for a spreadsheet and a trap for anything reading one, because the separation puts the type in the part of the file a reader is most likely to skip.

What this post covers. It describes what four readers do, at the versions linked above, read from their source, plus the ECMA-376 standard (5th edition, 2016) and Microsoft's description of the two date systems. The examples come from public issue trackers of projects building importers; they show the shape of the problem, not how often it occurs. Nothing was run: the divergence at serial 60 was derived by following both code paths and doing the date arithmetic by hand, so treat it as a reading of the code rather than a measured result. Where the post says a reader does something, it means that reader's source does it, which is a narrower claim than "xlsx works this way".