Creating Excel XP spreadsheets by using .NET and XML: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Creating Excel XP spreadsheets by using .NET and XML

page 1 of 1

Published: 26 Sep 2003

Unedited - Community Contributed

Abstract
Excel XP has a cool feature related to XML as the product can easily read and write properly-structured XML documents, run web queries against properly-structured XML data sources and save entire workbooks as XML spreadsheets. This article demonstrates one view to the utilization of this feature in .NET, namely creating a basic spreadsheet that has formulas.

by Teemu Keiski
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 11609/ 15

The article

Source XML

<?xml version="1.0" encoding="UTF-8"?>
<sales>
           <text>Demonstration spreadsheet created with .NET</text>
           <product>
                      <name>CD</name>
                      <price>20</price>
                      <sold>15</sold>
           </product>
           <product>
                      <name>Book</name>
                      <price>50</price>
                      <sold>7</sold>
           </product>
           <product>
                      <name>Pen</name>
                      <price>2</price>
                      <sold>18</sold>
           </product>
</sales>

Target XML

This is an example of the format Excel can open straight as a file from file system or as a web page and then produce a working spreadsheet with formulas. Note that this is a reduced version to keep the example concise.
If you save an Excel spreadsheet as XML and take a look at it, there would be content related to worksheet's options, protection, document's properties and so on. However, those are optional elements in the XML format and therefore stripped out of this example.

<?xml version="1.0" encoding="utf-8"?>

<Worksheet ss:Name="Sheet1">
    <Table>
      <Row>
        <Cell>
          <Data ss:Type="String">Demonstration spreadsheet created with .NET</Data>
        </Cell>
      </Row>

      <Row ss:Index="3">
        <Cell ss:StyleID="s1">
          <Data ss:Type="String">Product</Data>
        </Cell>
        <Cell ss:StyleID="s1">
          <Data ss:Type="String">Sold</Data>
        </Cell>
        <Cell ss:StyleID="s1">
          <Data ss:Type="String">Price</Data>
        </Cell>
        <Cell ss:StyleID="s1">
          <Data ss:Type="String">Sum</Data>
        </Cell>
      </Row>
      <Row>
        <Cell>
          <ss:Data ss:Type="String">CD</ss:Data>
        </Cell>
        <Cell>
          <ss:Data ss:Type="Number">15</ss:Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">20</Data>
        </Cell>
        <Cell ss:Formula="=RC[-2]*RC[-1]">
        </Cell>
      </Row>
      <Row>
        <Cell>
          <ss:Data ss:Type="String">Book</ss:Data>
        </Cell>
        <Cell>
          <ss:Data ss:Type="Number">7</ss:Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">50</Data>
        </Cell>
        <Cell ss:Formula="=RC[-2]*RC[-1]">
        </Cell>
      </Row>
      <Row>
        <Cell>
          <ss:Data ss:Type="String">Pen</ss:Data>
        </Cell>
        <Cell>
          <ss:Data ss:Type="Number">18</ss:Data>
        </Cell>
        <Cell>
          <Data ss:Type="Number">2</Data>
        </Cell>
        <Cell ss:Formula="=RC[-2]*RC[-1]">
        </Cell>
      </Row>
      <Row>
        <Cell ss:Index="3" ss:StyleID="s1">
          <Data ss:Type="String">Sum</Data>
        </Cell>
        <Cell ss:Formula="=R[-3]C+R[-2]C+R[-1]C">
        </Cell>
      </Row>
    </Table>
</Worksheet>
</Workbook>

About some elements

This declares a style that can be used at the spreadsheet by referring to ss:StyleID="s1"

<Worksheet ss:Name="Sheet1">

Declares a worksheet with name “Sheet 1”.

Specifies the data type of the content of the cell.

Specifies that the row is placed at the third row at the spreadsheet.

Specifies that the cell is placed at the third cell in this row at the spreadsheet.

Specifies the formula to be used to produce the content for the cell. The syntax is the basic RC syntax that uses relative locations. R means row and C means cell.

If you want to know more about the exact syntax, see resources provided at the end of this article.

So?

Well, it should be obvious that creating these Excel compatible XML files is very easy task in .NET. You could create the XML for example using XSLT or DOM and output it from web page using built-in Xml Control. Consider also the benefits if these docs i.e. Excel spreadsheets would be transmitted using XML web services. Also according to Microsoft the Office 2003 will have better support for XML. The result XML provided earlier was successfully tested with beta version of Excel 2003 by Thomas Johansen, http://authors.aspalliance.com/aylar .

As much as it is cool to have this kind feature, there are also restrictions. You can't save charts, OLE objects, VBA code etc. special features. I've included sample codes for the XSLT approach. It contains the source XML, the XSLT style sheet with extension C# class and the page to test the transformation and to produce the result XML. If you try to open the page from your server using Excel XP, you get fully working spreadsheet that has formulas.

[Download code]

Resources

XML in Excel and the Spreadsheet component (MSDN)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlsmlinss.asp

Transform XML Files when importing into Microsoft Excel 2002 (MSDN)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexcl2k2/html/odc_xlflatnr.asp

User Comments

Title: mr
Name: olivg
Date: 2011-03-02 6:12:41 PM
Comment:
How can I render a column with formulas embedded written in A1 notation and not in R1C1 notation? My user dislikes R1C1 notation; I have tried rendering the formula as a string but in excel it ends up treated as a text and formula is not calculated. Thanks.

Product Spotlight