Using C# / XML / XSLT to create Excel Spreadsheet

This article will not go into the details of the SpreadSheetML format but is inteded to show a way of creating Excel spreadsheets programatically.
There are several ways to do this and this is only one of them so I am also not going to make comparisons between the different methods.

In this example we are going to do the following :

  • Create an XML file containing customers
  • Create a template from Excel
  • Adjust the template file and turn it into an XSL file
  • Transform the customer XML file to get the result we want

Creating the XML file

We are going to create a simple XML file using C# containing a list of Items. The following figure shows the fields available for an item.

tblinventTable

For this post, we are going to keep it simple and write three sample items in the XML file.
Normally this data would be fetched from a database but here we just export three sample items from the code.

CreateXml

The result is the following XML file :

RawXml

Creating the template

Now that we have the XML file (which is relatively simple to create from your application), we are going to create an excel file template that will be used to merge with the XML file to get the result we want.

So we fire up Excel and create a simple spreadsheet to contain the list of Items.

ExcelTemplate1

When we have our desired layout, we save the file as an XML spreadsheet.

FileType

Now it is getting interesting… We can open the XML file an there we see that it is saved in the OOXML format (SpreadsheetML in this case)

ExcelTemplate2

From here on we can change the template by adding XSLT code that will receive data from the Items.xml file and that way we can add a row for each item to our spreadsheet. (You many possibilities here. It is also possible for example to create a worksheet for each item, …)

First  we adjust the template to create an XSL file. So rename the ItemsTemplate.xml filename to ItemsTemplate.xsl.

Then adjust the xsl file by adding following code at the beginning of the document :

BeginDoc

Also add the closing tags at the end of the document.

To create a record for each item in the source XML file , we just need to do an xsl:for-each loop for each item and process the XML code generated by excel.

 ValueOfSelect

Before we continue, there is a little thing we still need to do because otherwise this will not be working. In the xml node “Table” we need to delete the attribute “ExpandedRowCount” otherwise excel will not be able to open the result document.

Now our template is ready to be used in a transformation with the source XML file.

Transform the XML

Following code shows how you can do XSLT transformation through C# code resulting in the final XML file.

TransformCode

The actual result looks like this in the XML file.

ResultXml

And this can be neatly opened in Excel and gives the following result.

ResultExcel
Warning: count(): Parameter must be an array or an object that implements Countable in /customers/5/2/1/ksaelen.be/httpd.www/wordpresses/dynamicsaxblog/wp-includes/class-wp-comment-query.php on line 399

9 thoughts on “Using C# / XML / XSLT to create Excel Spreadsheet

  1. Nice tutorial. Is there a way I can do the reverse operation? (from xml spreadsheet to .xls OR .xlsx)

    Thanks.

  2. Hi john

    What do you want to do, reverse it to get a XSLT or do you want the excel data to be saved in an XML file.
    For the latter you can just save it from Excel to XML, but I guess you already know that :)

    So if it is to create an XSLT from a excel sheet, then I do not see how to do that right away :(

    kind regards,
    Kenny

  3. It gives an XML Exception that the root element cannot be found in the resulting XML file. How do we create ItemsResult.xml file?

  4. Never mind, Ignore my above comment. It worked out really well. Awesome article. :)

  5. Stunning article. Too much fragmented informations on internet. And here? Lovely packed step-by-step solution. Thanks.

  6. I want to create in Excel without microsoft.office.interop namespace ,will this help to achieve that ?

Leave a Comment Yourself

Your email address will not be published. Required fields are marked *