Formatting information in Excel Spreadsheets

Introduction

This collection of features, new in xlrd version 0.6.1, is intended to provide the information needed to:

  • display/render spreadsheet contents (say) on a screen or in a PDF file

  • copy spreadsheet data to another file without losing the ability to display/render it.

The Palette; Colour Indexes

A colour is represented in Excel as a (red, green, blue) (“RGB”) tuple with each component in range(256). However it is not possible to access an unlimited number of colours; each spreadsheet is limited to a palette of 64 different colours (24 in Excel 3.0 and 4.0, 8 in Excel 2.0). Colours are referenced by an index (“colour index”) into this palette.

Colour indexes 0 to 7 represent 8 fixed built-in colours: black, white, red, green, blue, yellow, magenta, and cyan.

The remaining colours in the palette (8 to 63 in Excel 5.0 and later) can be changed by the user. In the Excel 2003 UI, Tools -> Options -> Color presents a palette of 7 rows of 8 colours. The last two rows are reserved for use in charts.

The correspondence between this grid and the assigned colour indexes is NOT left-to-right top-to-bottom.

Indexes 8 to 15 correspond to changeable parallels of the 8 fixed colours – for example, index 7 is forever cyan; index 15 starts off being cyan but can be changed by the user.

The default colour for each index depends on the file version; tables of the defaults are available in the source code. If the user changes one or more colours, a PALETTE record appears in the XLS file – it gives the RGB values for all changeable indexes.

Note that colours can be used in “number formats”: [CYAN].... and [COLOR8].... refer to colour index 7; [COLOR16].... will produce cyan unless the user changes colour index 15 to something else.

In addition, there are several “magic” colour indexes used by Excel:

0x18 (BIFF3-BIFF4), 0x40 (BIFF5-BIFF8):

System window text colour for border lines (used in XF, CF, and WINDOW2 records)

0x19 (BIFF3-BIFF4), 0x41 (BIFF5-BIFF8):

System window background colour for pattern background (used in XF and CF records )

0x43:

System face colour (dialogue background colour)

0x4D:

System window text colour for chart border lines

0x4E:

System window background colour for chart areas

0x4F:

Automatic colour for chart border lines (seems to be always Black)

0x50:

System ToolTip background colour (used in note objects)

0x51:

System ToolTip text colour (used in note objects)

0x7FFF:

System window text colour for fonts (used in FONT and CF records).

Note

0x7FFF appears to be the default colour index. It appears quite often in FONT records.

Default Formatting

Default formatting is applied to all empty cells (those not described by a cell record):

  • Firstly, row default information (ROW record, Rowinfo class) is used if available.

  • Failing that, column default information (COLINFO record, Colinfo class) is used if available.

  • As a last resort the worksheet/workbook default cell format will be used; this should always be present in an Excel file, described by the XF record with the fixed index 15 (0-based). By default, it uses the worksheet/workbook default cell style, described by the very first XF record (index 0).

Formatting features not included in xlrd

  • Asian phonetic text (known as “ruby”), used for Japanese furigana. See OOo docs s3.4.2 (p15)

  • Conditional formatting. See OOo docs s5.12, s6.21 (CONDFMT record), s6.16 (CF record)

  • Miscellaneous sheet-level and book-level items, e.g. printing layout, screen panes.

  • Modern Excel file versions don’t keep most of the built-in “number formats” in the file; Excel loads formats according to the user’s locale. Currently, xlrd’s emulation of this is limited to a hard-wired table that applies to the US English locale. This may mean that currency symbols, date order, thousands separator, decimals separator, etc are inappropriate.

    Note

    This does not affect users who are copying XLS files, only those who are visually rendering cells.