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=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r=http://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=http://schemas.openxmlformats.org/package/2006/relationships>
  <
Relationship Id=rId1 Type=http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet Target=worksheet.xml/>
</
Relationships>


worksheet.xml


<?xml version=1.0 encoding=UTF-8 standalone=yes?>
<
worksheet xmlns=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r=http://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=http://schemas.openxmlformats.org/package/2006/relationships>
  <
Relationship Id=rId1 Type=http://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=http://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=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r=http://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=http://schemas.openxmlformats.org/spreadsheetml/2006/main xmlns:r=http://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 (4)

  1. Véronique says:

    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

  2. Sorry I’ve been offline for the past couple weeks. I’ve been meaning to post some content for awhile

  3. Over 5 months later, I’ve finally had some free time to pull together part 3 of my "Intro to SpreadsheetML"

  4. 247Blogging says:

    This is a continuation on the &quot; Simple SpreadsheetML file Part 1 &quot; 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