.XLS File Format – Styles and conditional formats

Cell styles

Style (0x0293)

A named cell style (2.4.269).

BuiltInStyle (2.5.16) is two bytes: istyBuiltIn, the built-in style number of ECMA-376 Part 1 section 18.8.7 (the same ids as XLSX’s builtinId), and iLevel, for RowLevel and ColLevel styles (istyBuiltIn 1 or 2) the outline level minus 1 (0 to 6), otherwise 0xFF.

StyleExt (0x0892)

Extra properties of the style in the Style record just before it (2.4.270).

StyleXF: the XF of a style

An XF record whose fStyle bit is 1 describes a style; its parent index should be 0xFFF, its f123Prefix bit is 0, and its last 14 bytes are a StyleXF (2.5.249) instead of a CellXF. It has the same fields as CellXF but no fAtr… bits; a colour index of 0 means “not specified” (and the matching border style must then be 0 too).

The HorizAlign, VertAlign, BorderStyle and FillPattern codes are listed under CellXF.

Formatting extensions

XFExt (0x087D)

Extra properties for one XF record (2.4.355): FrtHeader (12, rt = 0x087D); 2 reserved; ixfe (2, the XF it extends, at most 4,050); 2 reserved; cexts (2); then cexts ExtProp structures from offset 20.

XFCRC (0x087C)

Present exactly when XFExt records are (2.4.354): FrtHeader (12); 2 reserved; cxfs (2, the number of XF records, 16 to 4,050); crc (4), a checksum, as defined in [MS-OSHARED] section 2.4.3, over the data of the XF records. It shows whether the XF records were changed by a program that does not know about XFExt records.

ExtProp

Four bytes, extType (2) and cb (2, size of the whole ExtProp), then the data (2.5.108):

Colour structures

Every extended colour starts from an XColorType (2.5.279): 0 automatic, 1 indexed (a palette index, IcvXF), 2 RGB, 3 theme colour, 4 not set. Theme colours (ColorTheme, 2.5.49) are 0 dark 1, 1 light 1, 2 dark 2, 3 light 2, 4–9 accent 1 to 6, 10 hyperlink, 11 followed hyperlink (not allowed in CF12).

FontScheme (2.5.131): 0 none, 1 major (headings) font, 2 minor (body) font, 0xFF not set.

XFProps: property lists

StyleExt and DXF hold formatting as a list (2.5.288): 2 reserved bytes, cprops (2), then cprops XFProp structures. Each XFProp is xfPropType (2), cb (2, the size of the whole XFProp) and the data. A list may hold a pattern fill (type 0) or a gradient (types 3 and 4), never both.

Types 0x27 and 0x28 are not used; the highest is 0x2C. XFPropTextRotation (2.5.289) is one byte: 0–90 anticlockwise degrees, 91–180 clockwise 1 to 90 degrees, 255 vertical text.

XFPropGradient (2.5.286, 44 bytes): type (4: 0 linear, 1 rectangular), then five doubles: numDegree (angle of a linear gradient), numFillToLeft, numFillToRight, numFillToTop, numFillToBottom (the inner rectangle of a rectangular gradient, 0.0 to 1.0).

Palette and theme

Palette (0x0092, 2.4.188): ccv (2, must be 56), then 56 LongRGB colours that replace palette entries 8 to 63 (the default palette is under colour indexes).

Theme (0x0896, 2.4.326): FrtHeader (12, rt = 2198); dwThemeVersion (4): 0 a custom theme, 124226 the default theme; then, required for a custom theme, the theme itself as defined in ECMA-376 Part 1 section 14.2.7.

Differential formats and table styles

DXF (0x088D)

One differential format, referenced by index from table style elements (2.4.97): FrtHeader (12, rt = 2189); 2 bytes of flags (bit 1 fNewBorder: the properties may include inside borders of a range; bits 0 and 2 unused; 3–15 reserved); then XFProps.

TableStyles (0x088E)

TableStyle records follow, one per custom style.

TableStyle (0x088F)

FrtHeader (12); 2 bytes of flags (bit 1 fIsPivot: usable for PivotTables; bit 2 fIsTable: usable for tables; others reserved); ctse (4, the number of TableStyleElement records that follow, at most 28); cchName (2, 1 to 255); rgchName (UTF-16) (2.4.320).

TableStyleElement (0x0890)

FrtHeader (12); tseType (4, below); size (4, rows or columns per stripe, 1 to 9, only for types 5–8); index (4, a DXFId: the zero-based index of a DXF record) (2.4.321).

Conditional formatting

A worksheet holds conditional formats as groups: a CondFmt record followed by its CF records (the older form, at most three rules), or a CondFmt12 record followed by CF12 records (the newer form, which adds colour scales, data bars, icon sets and filters). CFEx records add the newer properties to rules in the older form.

CondFmt (0x01B0)

CF (0x01B1)

Comparisons (cp), with v the cell value and v1, v2 the formulas’ results: 1 between (inclusive, in either order), 2 not between, 3 equal to v1, 4 not equal, 5 greater than, 6 less than, 7 greater than or equal, 8 less than or equal. Formulas in conditional formats (2.5.198.5–7) are ordinary parsed formulas but may not use 3-D references, external names, unions, intersections, arrays, PtgExp, PtgTbl or the natural-language Ptgs, may not consist of a single area reference, and must give a value.

CondFmt12 (0x0879)

FrtRefHeaderU (12, fFrtRef 1, its range equal to refBound), then a CondFmtStructure laid out exactly like the CondFmt record above but with no upper limit on ccf, the number of CF12 records that follow (2.4.57, 2.5.56).

CF12 (0x087A)

icfTemplate values: 0 cell value, 1 formula, 2 colour scale, 3 data bar, 4 icon set, 5 filter, 7 unique values, 8 contains text, 9 contains blanks, 10 no blanks, 11 contains errors, 12 no errors, 15 today, 16 tomorrow, 17 yesterday, 18 last 7 days, 19 last month, 20 next month, 21 this week, 22 next week, 23 last week, 24 this month, 25 above average, 26 below average, 27 duplicate values, 29 above or equal to average, 30 below or equal to average.

Template parameters (16 bytes)

Colour scales, data bars, icon sets and filters

A CFVO (2.5.39) gives a threshold: cfvoType (1), a CFVOParsedFormula (2-byte length, non-zero for type 7, then tokens without relative references), and, when the formula is empty and the type is not 2 or 3, a double numValue. With X the formula’s result or numValue: 1 the number X; 2 the lowest value in the range; 3 the highest; 4 lowest + X% of (highest − lowest); 5 the Xth percentile; 7 the formula’s result. For types 4 and 5, X is above 0 and below 100.

The specification shows the 17 iIconSet values as pictures; the KPISets enumeration (2.5.170) uses the same numbers, agrees with the icon counts above, and names them: 0 3 arrows, 1 3 grey arrows, 2 3 flags, 3 3 traffic lights (1), 4 3 traffic lights (2), 5 3 signs, 6 3 symbols, 7 3 symbols (2), 8 4 arrows, 9 4 grey arrows, 10 4 red to black, 11 4 ratings, 12 4 traffic lights, 13 5 arrows, 14 5 grey arrows, 15 5 ratings, 16 5 quarters. (In the KPISets enumeration −1 means no icon, used for sorting.)

CFEx (0x087B)

FrtRefHeaderU (12, fFrtRef 1, the CondFmt’s range); fIsCF12 (4: 0 extends a CF rule, 1 extends the CF12 record that must follow); nID (2, the CondFmt it belongs to); then, only when fIsCF12 is 0, a CFExNonCF12 (2.4.44, 2.5.27):

DXFN: differential formatting

Conditional formats and table features store their formatting as a DXFN (2.5.95): a 6-byte header, then whichever parts its ibitAtr… bits say are present, in the order number format, font, alignment, border, pattern, protection. The “ninch” bits mean “not set: ignore this property”.

Stxp (2.5.248, 16 bytes): twpHeight (4, height in twips, 20 to 8,191, or −1 to ignore); ts (4, a Ts: bit 1 italic, bit 7 strikeout); bls (2: 0x0190 normal, 0x02BC bold, 0xFFFF ignore); sss (2: 0 normal, 1 superscript, 2 subscript, 0xFFFF ignore); uls (1: 0 none, 1 single, 2 double, 0x21 single accounting, 0x22 double accounting, 0xFF ignore); bFamily (1, 0 to 5); bCharSet (1); 1 unused.

DXFN12 (2.5.96): cbDxf (4). When it is 0, two reserved bytes follow and there is no formatting; otherwise a DXFN and, if cbDxf is larger than the DXFN, an XFExtNoFRT: 2 reserved bytes, 0xFFFF, 2 reserved, cexts (2) and that many ExtProp (2.5.281). DXFN12List and DXFN12NoCB, used by table features, are the same DXFN-plus-optional-XFExtNoFRT pair without the count.