Simple SpreadsheetML file Part 3 - Formatting

Article
05/29/2007

Over 5 months later, I've finally had some free time to pull together part 3 of my "Intro to SpreadsheetML" posts. I'll pick up where I left off in Part 2, so if you need a little refresher, please head back to the first 2 posts:

In this "lab" we'll move to the next stage and add some formatting to the cells (character formatting; alignment; and number formatting). So our spreadsheet will go from this:

To this:

Step 1 – Adding a stylesheet

The first thing we'll do is start with the final document we had from Part 2. You can grab mine from here: https://jonesxml.com/labs/SpreadsheetML/IntroPart3/Start.xlsx

We're going to add a new part to the file which will store all the style information. That part is the stylesheet part, and it has the following content type and relationship type:

Content Type - application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml
Relationship Type - https://schemas.openxmlformats.org/officeDocument/2006/relationships/styles

Create a new part called "styles.xml" and place it in the root folder alongside workbook.xml; worksheet.xml; and [Content_Types].xml. We'll start off with a very simple stylesheet with just a few defaults declared (but no actual formatting).

styles.xml

Create a styles.xml part with the following XML:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<styleSheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="1">

</fonts>
<fills count="1">
<fill />
</fills>
<borders count="1">
<border />
</borders>
<cellStyleXfs count="1">
<xf />
</cellStyleXfs>
<cellXfs count="1">
<xf />
</cellXfs>
</styleSheet>

You've just created a styles part, where each of the property types contain one blank definition:

Fonts – This is where any character level formatting definitions will be stored.
Fills – The cell fill properties.
Borders – Defines the cell borders.
Cell formatting records (xfs) – The formatting records allow you to reference existing fonts, fills, borders, number formatting, and specify which settings should or should not be applied. The cells in the worksheet can then reference the specific formatting record that should be applied.
Cell Style – Allows you to define a named cell style, such as "total" or "price", where you can specify the formatting records (xfs). This means you can give it a name as well as reference any defined fonts, fills, borders, or other properties you wish to set for the cell.

The styles part is a child of the workbook part, so the first thing we'll do is update the workbook.xml relationship file:

_rels/workbook.xml.rels

Open the Workbook rels file, and add a new relationship for the stylesheet, so that it looks like this:

[Content_Types].xml

Now we need to update the content type declarations so that the part will be valid in the package. You should update the content types part to include the new "styles.xml"

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Types xmlns="https://schemas.openxmlformats.org/package/2006/content-types">
<Default Extension="rels" ContentType="application/vnd.openxmlformats-package.relationships+xml"/>
<Override PartName="/workbook.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.main+xml"/>
<Override PartName="/worksheet.xml" ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.worksheet+xml"/>
<OverridePartName="/styles.xml"ContentType="application/vnd.openxmlformats-officedocument.spreadsheetml.styles+xml" />
</Types>

Pull it together

Now just grab the other three files that you used in Simple SpreadsheetML file Part 2 – Using shared formulas:

_rels/.rels
workbook.xml
worksheet.xml

If you ZIP the collection of files and open in Excel, you should see a document that looks just like the one we created at the end of Part 2. Here's a file you can download with all the pieces together if you're having trouble getting it working: https://jonesxml.com/labs/SpreadsheetML/IntroPart3/EmptyStylesheet.xlsx

We're now set to add some formatting properties to the XFS and reference those formatting properties from within the spreadsheet.

Step 2 – Add a new XFS (cell formatting) and reference it

The two parts we'll need to modify here are worksheet.xml and styles.xml. Let's start with styles.xml.

styles.xml

We're going to do two things. The first thing we'll do is add a new node with bold formatting specified. Then we'll add a new <xf /> that references the new font node by id:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<styleSheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
<fonts count="2">




</fonts>
<fills count="1">
<fill />
</fills>
<borders count="1">
<border />
</borders>
<cellStyleXfs count="1">
<xf />
</cellStyleXfs>
<cellXfs count="2">
<xf />

<xffontId="1" /> </cellXfs>
</styleSheet>

Don't forget to also increase the count attribute for both the cellXfs tag as well as the fonts tag. Also notice that the xf (cell style) references the font by a zero-based index. So we now have a cell style with bold formatting. The next step we need to do is reference that cell style from the cells in the worksheet.

worksheet.xml

We're going to make the header row bold, so all we need to do is add the style reference ( "s" ) to the first three cells in the worksheet:

<row>
<c t="inlineStr" s="1">
<is>
<t>Sub Total</t>
</is>
</c>
<c t="inlineStr" s="1">
<is>
<t>Tax</t>
</is>
</c>
<c t="inlineStr" s="1">
<is>
<t>Total</t>
</is>
</c>
</row>

Just like in the styles.xml part, the reference from the cell in the worksheet uses a zero based index. Now open the file, and you should see that the first row has bold formatting applied.

If you had trouble getting this to work, take a look at this example file as it should match the one you created: https://jonesxml.com/labs/SpreadsheetML/IntroPart3/AddFont.xlsx

Step 3 – Add some more formatting to the first row

We now have a cell style defined and the first row of cells is referencing that style. It will now be really easy to add additional formatting to that row if we want. Let's make the text in the cells horizontally aligned to the center rather than the left (which is the default).

We only need to update the styles.xml part to get this working.

styles.xml

Open the styles.xml part, and add the <alignment /> tag to the cell style we created in step 2.

Now repackage everything and open the file. You should see that the text is now bold and center aligned. If you had trouble getting this to work, take a look at this example file as it should match the one you created: https://jonesxml.com/labs/SpreadsheetML/IntroPart3/MoreCellFormatting.xlsx

Now let's move onto the last step, where we add some number formatting.

Step 4 – Number formatting

The values in the table are currency, so let's change the formatting on those values so they look like this: "$1.20" rather than this: "1.196"

We'll need to create a new cell style, and update the cells in the worksheet to reference that style. Let's start with the styles.xml part.

styles.xml

The first thing we'll need to do is create a number format (numFmt), and then a new XFS that references that number format. The number formats actually have IDs assigned to them, so rather than referencing them by index, the XFS will reference it based on its id.

The number format is defined using the formatCode attribute. The syntax for the formatCode is fully defined in section 3.8.30 of Part 4 for the Open XML format standard (ECMA 376).

Here's how we would use the formatCode attribute to specify the number formatting we're trying to get: formatCode="$ #,##0.00;$ #,##0.00;-"

The formats are separated by a semicolon (;) where the first one applies to positive numbers, the second applied to negative, and the last applies to zero values. So you can see that for zero values, we're just going to display a single dash "-" rather than "$0.00".

So, let's update the styles.xml part to look like this:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<styleSheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main">
<numFmtscount="1">
<numFmtnumFmtId="100"formatCode="$ #,##0.00;$ #,##0.00;-" />
</numFmts>
<fonts count="2">




</fonts>
<fills count="1">
<fill />
</fills>
<borders count="1">
<border />
</borders>
<cellStyleXfs count="1">
<xf />
</cellStyleXfs>
<cellXfs count="3">
<xf />
<xf fontId="1">
<alignment horizontal="center" />
</xf>
<xfnumFmtId="100" />
</cellXfs>
</styleSheet>

You've created a new number format, as well as a new style that references that number format. Also make sure you increment the count attribute on the cellXfs tag. Now let's update the cells in the worksheet to reference the new style.

worksheet.xml

All we need to do here is add the style attribute ( "s" ) to the cells in the sheet and have it reference the third cell style. So you're worksheet part should now look like this:

<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<worksheet xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheetData>
<row>
 <c t="inlineStr" s="1">
 <is>
 <t>Sub Total</t>
 </is>
 </c>
 <c t="inlineStr" s="1">
 <is>
 <t>Tax</t>
 </is>
 </c>
 <c t="inlineStr" s="1">
 <is>
 <t>Total</t>
 </is>
 </c>
</row>
<row>
 <c s="2">
 <v>14.95</v>
 </c>
 <c s="2">
 <f t="shared" ref="B2:B4" si="0">A2*0.08</f>
 </c>
 <c s="2">
 <f t="shared" ref="C2:C4" si="1">A2+B2</f>
 </c>
</row>
<row>
 <c s="2">
 <v>19.95</v>
 </c>
 <c s="2">
 <f t="shared" si="0" />
 </c>
 <c s="2">
 <f t="shared" si="1" />
 </c>
</row>
<row>
 <c s="2">
 <v>4.95</v>
 </c>
 <c s="2">
 <f t="shared" si="0" />
 </c>
 <c s="2">
 <f t="shared" si="1" />
 </c>
</row>
</sheetData>
</worksheet>

Now repackage the file and open it. You should see now that the numbers are formatted to match our initial goals. Also note that if you change one of the values to zero, it will display as a dash, rather than $0.00

If you had trouble getting this to work, take a look at this example file as it should match the one you created: https://jonesxml.com/labs/SpreadsheetML/IntroPart3/NumberFormatting.xlsx

You're file should look like this:

Sorry it took me so long to getting around to building this lab. I've had zero free time lately. I hope it was useful.

-Brian

Simple SpreadsheetML file Part 3 - Formatting

Step 1 – Adding a stylesheet

styles.xml

_rels/workbook.xml.rels

[Content_Types].xml

Pull it together

Step 2 – Add a new XFS (cell formatting) and reference it

styles.xml

worksheet.xml

Step 3 – Add some more formatting to the first row

styles.xml

Step 4 – Number formatting

styles.xml

worksheet.xml

Additional resources