Simple SpreadsheetML file (part 2 of 3)

This is a continuation on the "Simple SpreadsheetML file Part 1" post I made a couple weeks ago. In that post we created a SpreadsheetML file that consisted of a simple table with 3 columns and 3 rows of data (plus a header row). The table looked like this:

Sub Total

Tax

Total

14.95

   

19.95

   

4.95

   

Our goal though at the end of this series is to create a table that looks like this:

Sub Total

Tax

Total

$ 14.95

$ 1.20

$ 16.15

$ 19.95

$ 1.60

$ 21.55

$ 4.95

$ 0.40

$ 5.35

The pieces that we still need to add are the functions that calculate the values for the second and third columns, as well as the cell formatting. Today we're going to add the functions.

Part 1 - Simple Table

Let's pick up where we ended in Part 1 with the following parts:

workbook.xml

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<workbook xmlns="https://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="https://schemas.openxmlformats.org/officeDocument/2006/relationships">
<sheets>
<sheet name="Brian" sheetId="1" r:id="rId1"/>
</sheets>
</workbook>

_rels/workbook.xml.rels

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="https://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="https://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet" Target="worksheet.xml"/>
</Relationships>

worksheet.xml

<?xml version="1.0" encoding="UTF-8" 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">
<is>
<t>Sub Total</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Tax</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Total</t>
</is>
</c>
</row>
<row>
<c>
<v>14.95</v>
</c>
</row>
<row>
<c>
<v>19.95</v>
</c>
</row>
<row>
<c>
<v>4.95</v>
</c>
</row>
</sheetData>
</worksheet>

_rels/.rels

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="https://schemas.openxmlformats.org/package/2006/relationships">
<Relationship Id="rId1" Type="https://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="workbook.xml"/>
</Relationships>

[Content_Types].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"/>
</Types>

Re-Create Version 1 of our simple SpreadsheetML file

So if you take those five parts and ZIP them up you'll get a spreadsheet that looks like this:

Sub Total

Tax

Total

14.95

   

19.95

   

4.95

   

Version 2 - Add functions to the first row of data

Now we're going to add a function in cells B2 and C2 that will give us the tax, and total value for the first row of data. Let's say that the tax we apply is going to be 8%. That means that the function to calculate the tax is going to be: =A2*0.08. The function for the total will then just be: =A2+B2.

In order to update our spreadsheet, we're only going to need to edit the worksheet.xml part, and we can leave the rest of the parts alone.

worksheet.xml (version 2)

We will need to create cells for B2 and C2, and add the formula definition we want for each of those cells. So, in the second row, we'll add two more <c> elements. This time though, rather than using an inline string (<is>) or value (<v>), we'll use the function tag <f>.

<?xml version="1.0" encoding="UTF-8" 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">
<is>
<t>Sub Total</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Tax</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Total</t>
</is>
</c>
</row>
<row>
<c>
<v>14.95</v>
</c>
<c >
<f>A2*0.08</f
>
</c
>
<c
>
<f>A2+B2</f
>
</c>

    </row>
<row>
<c>
<v>19.95</v>
</c>
</row>
    <row>
<c>
<v>4.95</v>
</c>
</row>
</sheetData>
</worksheet>

Create Version 2 of the spreadsheet

Take the original parts (_rels/.rels; _rels/workbook.xml.rels; workbook.xml; [Content_Types].xml) as well as our new worksheet.xml part and ZIP them up. When you open the resulting file, you should have a spreadsheet with the formulas automatically calculated and it looks something like this:

Sub Total

Tax

Total

14.95

1.196

16.146

19.95

   

4.95

   

Version 3 - Make the formulas repeat for the other rows

There are now two options for adding the formulas to the next two rows. You could do the same thing we did in the first row, and update the cell references (ie A3*0.08 & A4*0.08), but that requires you to update the cell references for each row. It also requires the consuming application to parse each formula, which can be time consuming when you get into larger spreadsheets and more complex formulas.

Another approach is to use a shared formula. If you were in an application like Microsoft Excel, you could copy the formula from the first row and paste it into the rows below it. Excel would automatically update the cell references in each row so that the tax and total was properly calculated. You can do the exact same thing in the file format by specifying that the formula is a shared formula.

worksheet.xml (version 3)

To specify that the formula from the first row is shared, we use the t="shared" attribute. We then specify what the range is that we want it to apply to, and give the formula an id that the lower cells can reference. Then we create the cells for B3:C4 and specify that they are sharing a formula. The resulting worksheet.xml part will look like this:

<?xml version="1.0" encoding="UTF-8" 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">
<is>
<t>Sub Total</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Tax</t>
</is>
</c>
<c t="inlineStr">
<is>
<t>Total</t>
</is>
</c>
</row>
<row>
<c>
<v>14.95</v>
</c>
<c>
<f t= "shared"ref="B2:B4"si="0" >A2*0.08</f>
   </c>
<c>
<f t="shared"ref="C2:C4"si="1" >A2+B2</f>
</c>
    </row>
<row>
<c>
<v>19.95</v>
</c>
<c >
<f t="shared"si="0"/>

</c >
<c
>
<f t="shared"si="1"/>

</ c>
</row>
    <row>
<c>
<v>4.95</v>
</c>
<c >
<f t="shared"si="0"/>

</c >
<c
>
<f t="shared"si="1"/>

</ c>
</row>
</sheetData>
</worksheet>

Create Version 3 of the spreadsheet

So, again we've only updated the worksheet.xml part, so re-generate the ZIP file with the updated worksheet.xml part. You should get the following table of data:

Sub Total

Tax

Total

14.95

1.196

16.146

19.95

1.596

21.546

4.95

0.396

5.346

So, now you know the basics of using a formula in a spreadsheetML file. It's pretty straightforward. You could also specify the values of the formulas using the <v> tag as a sibling of the <f> tag, but that isn't necessary.

In the next post, we'll format the cells so that they actually look like currency, and not just plain numbers.

-Brian