Spreadsheet Styles
Spreadsheets can be styled using styles, themes, and direct formatting. There are cell styles, table styles, and pivot styles. However, unlike in WordprocessingML, styling XML never appears with the content in a worksheet. The formatting is always stored separately within a single styles part for the workbook. There is also a single theme part for the entire workbook.
A cell style can specify number format, cell alignment, font information, cell borders, and background/foreground fills. Table styles specify formatting for regions of a table, such as, e.g., headers are bold or a gray fill should be applied to alternating rows. Pivot table styles specify formatting for regions of a pivot table, such as colors for totals or for the row axis. Themese define a set of colors, font information, and effects on shapes. A style or formatting element can define a color, font, or effect by referencing a theme, but of course that format may change if the thme is changed.
Text-Level Formatting
Before getting to the styles applied to a worksheet, however, let's first cover formatting at the text level, that is, not formatting applied to the entire cell, but formatting that might change from word to word, such as different colors or effects. For example, see cell A13 below, with blue color for the first word and orange underline for the second. Obviously this cannot be accomplished with a cell style. This formatting is done within the shared string part where the text of the cell is stored.
Let's look at the XML for the first cell in row 13 of the worksheet part. We know from the type attribute for the cell t="s" that the text is stored in the shared strings part, and from <v="25"/> we know that string is the 26th string or <si>. (Remember that it is a zero-based index.)
The XML for the string is below. Note that the formatting is applied directly within the string item, just as direct formatting is applied to text runs (<r>) using run properties (<rPr>) within wordprocessingML (docx) documents.
Cell-Level Formatting
Now let's return to cell styles. Styles within spreadsheetML are implemented to minimize repetition, and this is done with collections. Within the styles part there are the collections shown below.
Most of the collections above (except for <dxfs> and <tableStyles>) relate to cells. And the first four--numFmts, fonts, fills, and borders--contain all of the possible charateristics for every cell in the workbook. Each may have many elements, each one defining the characteristics for a set of cells that have the same such characteristics. For example, below is a sample of the <fills> for a workbook. Every cell in the workbook will use one of these fill definitions.
The <fill> for a particular cell is specified with a zero-based index into the above fills collection. The same is true of the font for the cell, the number format, and the borders. So the formatting for a cell can be specified with a list or collection of indices into these four collections. And in fact, that is what the <cellXfs> is. It contains a collection of groups of indices, one group for every combination of cell formatting characteristics found in the workbook. Below is one such grouping.
Every cell will have a reference to one <xf> in the <cellXfs> collection. This is direct formatting for the cell. To apply a style to the cell, the <xf> references the style using the xfId attribute. The xfId attribute is an index into the <cellStyleXFs> collection, which collects the cell styles available to the user. The <cellStyleXFs> contains one <xf> for each style. Each such <xf> is tied to its name via an index (in its xfId attribute) from the <cellStyles> collection.
Let's try and tie it all together by looking at a sample. Consider row 10 in the sample below.
The first cell A10 has a cell style applied. The XML for the cell in the worksheet is below.
From the attribute s="12" we know that the cell's formatting is stored at the 13th (zero-based index) <xf> within the <cellXfs> collection in the styles part. The 13th <xf> is below.
So for this cell, the number format is the first (index value is 0) within the <numFmts> collection. The cell uses the font format found within the 9th <font> in the <fonts> collection, the 5th <fill> within the <fills> collection (which references a theme for the green), and the first <border> within the <borders> collection. This cell also applies a style (xfId="3")--the 4th <xf> within the <cellStyleXfs> collection. The style is shown below.
The formatting of the style is same as the direct formatting, and the attributes applyNumberFormat, applyBorder, applyAlignment, and applyProtection, each with values of 0, tell us not to apply the corresponding values of the style but instead apply the values for the direct formatting. In this case they are the same, so there is no difference anyway.
Table-Level Formatting
A table applies a table style by specifying a <tableStyleInfo> element within the table definition in the tables part. For example, the following sample table definition specifies the TableStyleMedium9 style. Note that it is specified by name. Note also that not only is the style specified, but the specification also tells us which aspects of the style are turned on (e.g., showRowStripes="1") and which are turned off (e.g., showLastColumn="0"). Each table style is made up of a collection of formatting definitions, each of which corresponds to a particular region of the table--e.g., whole table, first column stripe, first row stripe, first column, header column, first header cell, etc. Each of these formatting definitions can be turned on or off.
Annex G of the ECMA-376, 3rd Edition (June, 2011) OOXML specification defines built-in styles for cells, tables, and pivot tables, and style TableStyleMedium9 is among the built-in table styles. The built-in table and pivot table styles are not stored in the styles part--only custom styles are.
Below is a custom style defined in the styles part, based on the TableStyleMedium9 style.
The style looks like this:
The style definition above uses differential formatting records (<dxf> elements referenced from the dxfId attribute), which enables subsets of formatting to be specified instead of specifying all formatting. Looking at the sample above, we see that the default style is the TableStyleMedium9 style. From that we are altering three aspects -- the wholeTable, headerRow, and firstColumn. Each of these elements references (again using a zero-based index) the <dxfs> collection within the styles part. For example, the <headerRow> element references the second <dxf> (dxfId="1"). It applies bold and a fill background color to the default table style defaultTableStyle="TableStyleMedium9".
Conditional Formatting
Conditional formatting is a format such as cell shading or font color that a spreadsheet can apply automatically to cells if a specified condition is true. For example, you can specify that a cell fill color should be red if the value in the cell is above 50. It can be a very effective tool for visually highlighting important aspects of the data in a worksheet.
Conditional formatting rules are stored in the worksheet part, within a <conditionalFormatting> element after the <sheetData> element. The range of cells to which the formatting applies is specified with the sqref attribute. Each condition is within a <cfRule> element. Multiple rules can be set, each with a different priority. There are several different types of rules to specify different conditions. For example, type="cellIs" will determine a cell format based on whether a cell value is greater than or less than a specified value, or between two values. A type="dataBar" will display a bar of varying length within a cell based on the value in the cell. Theses types are set with the type attribute on <cfRule>. A type="iconSet" will display an icon in the cell based upon the value in a cell. Below is a sample table which applies two conditions to the cells B2:B7 - one which applies a pink color fill if the value of the cell is greater than 500 and the other which applies a green fill if the value is less than 300.
The XML for the conditions is below.