Formatting information in Excel Spreadsheets¶
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
PALETTE record appears in the XLS file – it gives the RGB values
for all changeable
Note that colours can be used in “number formats”:
[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:
- System window text colour for border lines (used in
- System window background colour for pattern background (used in
- System face colour (dialogue background colour)
- System window text colour for chart border lines
- System window background colour for chart areas
- Automatic colour for chart border lines (seems to be always Black)
- System ToolTip background colour (used in note objects)
- System ToolTip text colour (used in note objects)
System window text colour for fonts (used in
0x7FFFappears to be the default colour index. It appears quite often in
Default formatting is applied to all empty cells (those not described by a cell record):
- Firstly, row default information (
Rowinfoclass) is used if available.
- Failing that, column default information (
Colinfoclass) 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
XFrecord with the fixed index 15 (0-based). By default, it uses the worksheet/workbook default cell style, described by the very first
XFrecord (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.
This does not affect users who are copying XLS files, only those who are visually rendering cells.