.XLSX File Format – Cells, strings and styles

A worksheet part

The root worksheet holds, in order: sheetPr, dimension (the used range, such as A1:D20), sheetViews, sheetFormatPr (default row height and column width), cols (col elements with min, max, width, style, hidden, outlineLevel), sheetData, then protection, auto-filter, mergeCells, conditionalFormatting, dataValidations, hyperlinks, page setup, headers and footers, drawing, tableParts and extLst.

<sheetData>
  <row r="1" spans="1:3">
    <c r="A1" t="s"><v>0</v></c>
    <c r="B1"><v>42.5</v></c>
    <c r="C1" s="1"><f>B1*2</f><v>85</v></c>
  </row>
</sheetData>

A row has r (1-based row number), and optionally spans (a hint of the columns used), s with customFormat (row style), ht with customHeight (height in points), hidden, outlineLevel, collapsed, thickTop, thickBot and ph. Rows and cells appear in order; missing ones are empty.

Cells

The d type is not in the first edition of ECMA-376 (its ST_CellType has only the six types above). Microsoft’s Open XML SDK lists it as CellValues.Date, written "d", “only available in Office 2010 and later”. It is a feature of Strict SpreadsheetML, which can store a date as text instead of a serial number: only local dates and times from 0001-01-01 00:00 to 9999-12-31 23:59:59.999, with no time zone or UTC marker, written as YYYY-MM-DD, hh:mm:ss or YYYY-MM-DDThh:mm:ss (a write-up of Strict dates on Eric White’s blog, which shows the same date as <v>40957</v> in a transitional file and as t="d" with <v>2012-03-01T14:30:00</v> in a Strict one). A reader should accept both forms.

Dates and times are ordinary numbers shown with a date format: the whole part is a day count and the fraction the time of day. In the default 1900 system day 1 is 1 January 1900, and Excel counts a non-existent 29 February 1900 (day 60) for compatibility with Lotus 1-2-3; with date1904 set in workbookPr, day 0 is 1 January 1904.

Formulas

The formula is stored as text without the leading =, using A1 references and English function names; functions newer than Excel 2007 carry a prefix such as _xlfn.. Attributes of f:

Shared strings

xl/sharedStrings.xml has a root sst with count (number of references in the workbook) and uniqueCount (number of items), then one si per string in index order. An si holds either a single t (plain text; xml:space="preserve" keeps spaces) or a series of rich text runs r, each a t with optional run properties rPr (font, size, colour, bold, italic, underline…), followed by optional phonetic runs rPh and phonetic properties phoneticPr. Characters not allowed in XML are written as _xhhhh_.

The styles part

xl/styles.xml (styleSheet) holds, in order: numFmts (custom number formats, each numFmtId and formatCode, ids 164 and up), fonts, fills (the first two are reserved: none and gray125), borders, cellStyleXfs (formats of named styles), cellXfs (the cell formats a cell’s s points to: numFmtId, fontId, fillId, borderId, xfId, apply… flags, and child alignment and protection), cellStyles (named styles, with builtinId), dxfs (differential formats for conditional formatting and tables), tableStyles, colors (custom indexed palette and recent colours) and extLst. Every element and attribute is on the styles part page.

Built-in number formats

Number format ids below 164 are built in and need no numFmt element (ECMA-376 Part 1, 18.8.30; Excel’s binary formats share them):

Some ids are shown differently depending on the application’s language, and further ids exist only for East Asian and Thai versions:

Thai (th-TH): 59 t0, 60 t0.00, 61 t#,##0, 62 t#,##0.00, 67 t0%, 68 t0.00%, 69 t# ?/?, 70 t# ??/??, 71 ว/ด/ปปปป, 72 ว-ดดด-ปป, 73 ว-ดดด, 74 ดดด-ปป, 75 ช:นน, 76 ช:นน:ทท, 77 ว/ด/ปปปป ช:นน, 78 นน:ทท, 79 [ช]:นน:ทท, 80 นน:ทท.0, 81 d/m/bb. The ids 5–8, 23–26 and 41–44 have no fixed text in the standard; Microsoft’s [MS-XLS] treats them as locale-dependent formats that a file defines with its own format string, so a reader should take them from numFmts.