Simple SpreadsheetML file (part 2 of 3)

아티클
11/15/2006

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

_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

Comments

Anonymous
November 27, 2006
Would it be possible to add a reference to an image inside a data cell? It seems that an image itself is not supported but I was wondering if there was a way to reference the actual image instead. My Ex: (this, does not work but I didn't find any resource on how to insert a reference of an image) <Cell> <Data ss:Type="String"> <xsl:variable name="apos"> <xsl:text>'</xsl:text> </xsl:variable> <xsl:variable name="xSignatureURL"> <xsl:text>Signature.gif</xsl:text> </xsl:variable> <xsl:value-of select="substring-before(substring-after($xSignatureURL,concat('url(',$apos)),concat($apos,');'))"/> </Data> </Cell> Thank you so much for these interesting post. Véronique
Anonymous
April 20, 2007
Sorry I've been offline for the past couple weeks. I've been meaning to post some content for awhile
Anonymous
May 29, 2007
Over 5 months later, I've finally had some free time to pull together part 3 of my "Intro to SpreadsheetML"
Anonymous
May 19, 2008
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

다음을 통해 공유