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.
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.
The result is the following XML file :
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.
When we have our desired layout, we save the file as an XML spreadsheet.
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)
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 :
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.
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.
The actual result looks like this in the XML file.
And this can be neatly opened in Excel and gives the following result.