XLSX styles part

All cell formatting lives in one part, normally xl/styles.xml, whose root is styleSheet. A cell’s s attribute is a zero-based index into its cellXfs list (see cells), and each of those cell formats in turn points by index into the font, fill and border lists and by id to a number format. The element and attribute definitions here are those of ECMA-376 1st edition, Part 4, section 3.8 (section 18.8 in later editions and ISO/IEC 29500), read in the HTML rendering of the standard published by docx4java; Boolean attributes are XML Schema booleans (true/false or 1/0).

styleSheet

The children come in this order, each optional and at most once (3.8.39):

Child	Holds
`numFmts`	Custom number formats.
`fonts`	Fonts.
`fills`	Fills.
`borders`	Borders.
`cellStyleXfs`	xf records of the named cell styles.
`cellXfs`	xf records that cells use.
`cellStyles`	Named cell styles.
`dxfs`	Differential formats for conditional formatting, tables and so on.
`tableStyles`	Table and PivotTable styles.
`colors`	A replacement indexed palette and recently used colours.
`extLst`	Extensions added by later versions.

Each list element (numFmts, fonts, fills, borders, cellStyleXfs, cellXfs, cellStyles, dxfs, tableStyles) has an optional count attribute, the number of children.

numFmt

Two required attributes (3.8.30): numFmtId, the id that xf records use, and formatCode, the format string. Ids below 164 are the built-in formats, which are not written out; custom formats take ids from 164. A numFmt may also appear inside a dxf.

font

A font (3.8.21) has no attributes. Its children may appear in any order, each at most once; a missing child means the default. Most are on/off properties with a single val attribute, an optional Boolean that defaults to true, so <b/> means bold.

Child	`val`	Meaning
`name`	string	Font name, such as `Calibri`.
`charset`	integer 0–255	Character set: 0 ANSI, 1 default, 2 symbol, 77 Mac, 128 Shift-JIS, 129 Hangul, 130 Johab, 134 GB2312, 136 Big5, 161 Greek, 162 Turkish, 163 Vietnamese, 177 Hebrew, 178 Arabic, 186 Baltic, 204 Russian, 222 Thai, 238 Eastern European, 255 OEM (depends on the system). The standard says the list is not exhaustive.
`family`	integer 0–255	Font family: 0 not applicable, 1 Roman, 2 Swiss, 3 Modern, 4 Script, 5 Decorative.
`b`	Boolean	Bold.
`i`	Boolean	Italic.
`strike`	Boolean	Strikethrough.
`outline`	Boolean	Draw only the outline of each character.
`shadow`	Boolean	Shadow behind, below and to the right (a Macintosh setting that applications may ignore).
`condense`	Boolean	Squeeze characters together (Macintosh compatibility; may be ignored).
`extend`	Boolean	Stretch the text (compatibility with older applications).
`color`	–	Text colour, a colour element.
`sz`	double, required	Size in points.
`u`	`single` (default when `val` is omitted), `double`, `singleAccounting`, `doubleAccounting`, `none`	Underline. The plain forms cross the descenders of letters such as g and p; the accounting forms sit below them.
`vertAlign`	`baseline`, `superscript`, `subscript` (required)	Raised or lowered text, which also uses a smaller size when one is available.
`scheme`	`major`, `minor`, `none` (required)	Whether the font is the theme’s major (headings) or minor (body) font.

fill

A fill (3.8.19) holds exactly one of patternFill or gradientFill.

patternFill

Attribute patternType (3.8.32; no pattern when absent), then optional children fgColor and bgColor, both colour elements. A solid fill uses only the foreground colour. The pattern types: none (no fill, colours ignored), solid, mediumGray, darkGray, lightGray, darkHorizontal, darkVertical, darkDown, darkUp, darkGrid, darkTrellis, lightHorizontal, lightVertical, lightDown, lightUp, lightGrid, lightTrellis, gray125 (12.5% grey) and gray0625 (6.25% grey) (ST_PatternType, 3.18.57).

gradientFill

Attribute	Default	Meaning
`type`	`linear`	`linear`: the colour changes along a line; `path`: it changes outward from the rectangle given by `left`, `right`, `top` and `bottom`.
`degree`	0	Angle of a linear gradient: 0 and 180 vertical, 90 and 270 horizontal, 45, 135, 225 and 315 diagonal.
`left`, `right`, `top`, `bottom`	0	For a path gradient, the edges of the inner rectangle as fractions 0 to 1 of the cell.

Its children are stop elements (3.8.38), each with a required position (a double, read like the edge values) and exactly one color.

border

A border (3.8.4) holds, in this order and each optional, left, right, top, bottom, diagonal, vertical and horizontal (the last two are the inside borders of a range, used in differential formats). Each edge has a style attribute (default none) and an optional color child; with no colour, automatic is meant. The border itself has:

Attribute	Default	Meaning
`diagonalDown`	–	Draw the diagonal from top left to bottom right.
`diagonalUp`	–	Draw the diagonal from bottom left to top right.
`outline`	`true`	Apply left, right, top and bottom only to the outside edges of a range.

Border styles (ST_BorderStyle, 3.18.3): none, thin, medium, dashed, dotted, thick, double, hair, mediumDashed, dashDot, mediumDashDot, dashDotDot, mediumDashDotDot, slantDashDot. These are the same fourteen styles as BIFF8’s border style codes 0 to 13.

xf: cell formats

One xf (3.8.45) describes all the formatting of a cell or of a named style. Children, each optional: alignment, protection, extLst.

Attribute	Default	Meaning
`numFmtId`	–	Number format id.
`fontId`	–	Zero-based index into `fonts`.
`fillId`	–	Zero-based index into `fills`.
`borderId`	–	Zero-based index into `borders`.
`xfId`	–	In `cellXfs` only: zero-based index of the `cellStyleXfs` record of the cell’s named style.
`applyNumberFormat`, `applyFont`, `applyFill`, `applyBorder`, `applyAlignment`, `applyProtection`	–	Whether this record’s number format, font, fill, border, alignment or protection is applied.
`quotePrefix`	`false`	The text was typed with a leading apostrophe (which is not stored in the shared strings).
`pivotButton`	`false`	The cell shows a PivotTable drop-down button.

alignment

Attribute	Values	Meaning
`horizontal`	`general`, `left`, `center`, `right`, `fill`, `justify`, `centerContinuous`, `distributed`	`general` puts text left, numbers, dates and times right and Booleans in the centre; `fill` repeats the value across the cell in whole copies; `centerContinuous` centres across neighbouring cells with the same format; `distributed` spreads words across the width, with any indent on both sides (3.18.42).
`vertical`	`top`, `center`, `bottom`, `justify`, `distributed`	Vertical alignment (3.18.89).
`textRotation`	0–180	0–90 degrees upward from horizontal; 91–180 downward, by `textRotation` − 90 degrees.
`wrapText`	Boolean	Wrap text inside the cell.
`indent`	unsigned	Indent level; each level is three spaces of the Normal style’s font. Only with left, right and distributed alignment.
`relativeIndent`	signed	Indent to add or remove, only in a `dxf`.
`justifyLastLine`	Boolean	Also justify or distribute the last line (East Asian alignment).
`shrinkToFit`	Boolean	Shrink the text to fit the column (not with several lines).
`readingOrder`	0, 1, 2	0 by context, 1 left to right, 2 right to left.

protection

Two Boolean attributes (3.8.33), which take effect only when the sheet is protected: locked (the cell cannot be changed as the sheet’s sheetProtection says) and hidden (the formula is not shown in the formula bar; the value still is).

cellStyle: named styles

Attribute	Meaning
`name`	The style name.
`xfId`	Required: zero-based index of its record in `cellStyleXfs`.
`builtinId`	For a built-in style, which one (below); the id, not the name, decides.
`iLevel`	Outline level, for the RowLevel and ColLevel styles.
`hidden`	Not shown in the user interface.
`customBuiltin`	A built-in style that has been changed, to be saved even when unused.

Built-in style ids (3.8.7):

Id	Style	Id	Style	Id	Style
0	Normal	20	Input	37	Accent3
1	RowLevel_1 to RowLevel_7 (by `iLevel`)	21	Output	38–40	20%, 40%, 60% - Accent3
2	ColLevel_1 to ColLevel_7	22	Calculation	41	Accent4
3	Comma	23	Check Cell	42–44	20%, 40%, 60% - Accent4
4	Currency	24	Linked Cell	45	Accent5
5	Percent	25	Total	46–48	20%, 40%, 60% - Accent5
6	Comma [0]	26	Good	49	Accent6
7	Currency [0]	27	Bad	50–52	20%, 40%, 60% - Accent6
8	Hyperlink	28	Neutral	53	Explanatory Text
9	Followed Hyperlink	29	Accent1
10	Note	30–32	20%, 40%, 60% - Accent1
11	Warning Text	33	Accent2
15	Title	34–36	20%, 40%, 60% - Accent2
16–19	Heading 1 to Heading 4

Ids 12 to 14 are not listed in the standard.

dxf: differential formats

A dxf (3.8.14) holds only what differs from the underlying format: optional font, numFmt, fill, alignment, border, protection and extLst, in that order, with the same content as above. Conditional formatting rules and table style elements point to them by zero-based index (dxfId).

Table styles

tableStyles (3.8.42) has count, defaultTableStyle and defaultPivotStyle (names of the styles given to new tables and PivotTables) and holds tableStyle elements. A tableStyle (3.8.40) has a required name, pivot and table (default true: offered for PivotTables, for tables, or both) and count, and holds tableStyleElement elements (3.8.41), each with a required type, an optional dxfId (the formatting) and size (default 1: rows or columns per stripe, for the stripe types only).

Element types (ST_TableStyleType, 3.18.79): wholeTable, headerRow, totalRow, firstColumn, lastColumn, firstRowStripe, secondRowStripe, firstColumnStripe, secondColumnStripe, firstHeaderCell, lastHeaderCell, firstTotalCell, lastTotalCell; and for PivotTables firstSubtotalColumn, secondSubtotalColumn, thirdSubtotalColumn, firstSubtotalRow, secondSubtotalRow, thirdSubtotalRow, blankRow (only with blank rows after each item), firstColumnSubheading, secondColumnSubheading, thirdColumnSubheading, firstRowSubheading, secondRowSubheading, thirdRowSubheading, pageFieldLabels and pageFieldValues.

Colours

Every colour element (color, fgColor, bgColor and the border edges’ color) has these optional attributes; one of the first four says where the colour comes from:

Attribute	Meaning
`auto`	Automatic: the system colour.
`indexed`	Index into the indexed palette (below); kept for backward compatibility.
`rgb`	Eight hex digits, alpha then red, green and blue (`FF0000FF` is opaque blue).
`theme`	Index into the theme’s colour scheme (`clrScheme`) in the theme part.
`tint`	−1.0 to 1.0, default 0: darken or lighten. The colour is turned into hue, luminance and saturation with a maximum of 255; for a negative tint the luminance becomes L × (1 + tint), for a positive one L × (1 − tint) + (255 − 255 × (1 − tint)). Luminance 100 with tint 0.75 becomes 217.

colors and the indexed palette

colors (3.8.11) holds an optional indexedColors, written when the palette has been changed, with one or more rgbColor children that replace it in order, and an optional mruColors, the recently chosen custom colours. The default palette (3.8.26), as ARGB:

Index	ARGB	Index	ARGB	Index	ARGB	Index	ARGB
0	00000000	17	00008000	34	00FFFF00	51	00FFCC00
1	00FFFFFF	18	00000080	35	0000FFFF	52	00FF9900
2	00FF0000	19	00808000	36	00800080	53	00FF6600
3	0000FF00	20	00800080	37	00800000	54	00666699
4	000000FF	21	00008080	38	00008080	55	00969696
5	00FFFF00	22	00C0C0C0	39	000000FF	56	00003366
6	00FF00FF	23	00808080	40	0000CCFF	57	00339966
7	0000FFFF	24	009999FF	41	00CCFFFF	58	00003300
8	00000000	25	00993366	42	00CCFFCC	59	00333300
9	00FFFFFF	26	00FFFFCC	43	00FFFF99	60	00993300
10	00FF0000	27	00CCFFFF	44	0099CCFF	61	00993366
11	0000FF00	28	00660066	45	00FF99CC	62	00333399
12	000000FF	29	00FF8080	46	00CC99FF	63	00333333
13	00FFFF00	30	000066CC	47	00FFCC99	64	system foreground
14	00FF00FF	31	00CCCCFF	48	003366FF	65	system background
15	0000FFFF	32	00000080	49	0033CCCC
16	00800000	33	00FF00FF	50	0099CC00

Example

A minimal styles part with one extra format, bold red text with a thin bottom border and two decimal places (hand-written):

<styleSheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
  <fonts count="2">
    <font><sz val="11"/><name val="Calibri"/><family val="2"/><scheme val="minor"/></font>
    <font><b/><sz val="11"/><color rgb="FFFF0000"/><name val="Calibri"/></font>
  </fonts>
  <fills count="2">
    <fill><patternFill patternType="none"/></fill>
    <fill><patternFill patternType="gray125"/></fill>
  </fills>
  <borders count="2">
    <border><left/><right/><top/><bottom/><diagonal/></border>
    <border><left/><right/><top/><bottom style="thin"><color auto="1"/></bottom><diagonal/></border>
  </borders>
  <cellStyleXfs count="1"><xf numFmtId="0" fontId="0" fillId="0" borderId="0"/></cellStyleXfs>
  <cellXfs count="2">
    <xf numFmtId="0" fontId="0" fillId="0" borderId="0" xfId="0"/>
    <xf numFmtId="2" fontId="1" fillId="0" borderId="1" xfId="0"
        applyNumberFormat="1" applyFont="1" applyBorder="1"/>
  </cellXfs>
  <cellStyles count="1"><cellStyle name="Normal" xfId="0" builtinId="0"/></cellStyles>
</styleSheet>

A cell written <c r="B2" s="1"> uses the second xf: built-in format 2 (0.00), font 1 and border 1.

Sources

ECMA-376 1st edition, Part 4, SpreadsheetML reference – HTML rendering of section 3.8 and the simple types in 3.18, one page per element
[MS-OI29500] – Microsoft’s notes on how Office implements ISO/IEC 29500, including section 18.8

.XLSX File Format – Styles part