Dynamically generating Excel files
Price list download
Recently the need to provide a link to download prices from our corporate website arose. Generally I like to keep things simple, so the obvious thing to do was to squirt the data into a csv file in to the response stream to the browser. However there was a desire to include other elements for Microsoft Excel users, like the “=HYPERLINK(“https://www.timwappat.info/”,”Tims site”)” type formulas.
I checked out so see the discussions on Stackoverflow, the view I derived from there was that SpreadsheetML was the way forward as a download format. I also kept in the csv for those users with older software.
This is the XML format used by excel. The excel document is broken up into multiple xml files that normalise the data and make it possible for millions of cells to be held in a sensible manner. The collection of files are then zipped up and given the xlsx file extension. It is this archive that becomes the spreadsheet excel “file”.
Microsoft have released a SDK that allows developers to work with the xml files. The SDK can be downloaded from here: Open XML Format SDK 2.0
By referencing the .dll files in the SDK in your Visual Studio 2008+ project, the open XML namespaces become available to you. A good introduction is here: Dive into SpreadsheetML (Part 1 of 2)
Structure of XML using Microsoft XML Notepad:
You can see from the screen shot the structure drilling down to an individual text cell.
Create the template
To create a template file to make things a little easier. This is done by creating an Excel document that is formatted and contains everything you need bar the data you wish to manipulate. This all could be done by using code, but easier to just use Excel.
Creating Documents by Using the Open XML Format SDK 2.0 (Part 1 of 3)
The above link is useful to get a introduction to basic manipulation of the documents. Once installed the SDK also has a help file containing some good examples of basic and medium level interactions with documents.
Populate the template document
Microsoft are promoting LINQ by using it as the data technology stack behind the OPEN XML examples they provide. The code is quite readable, working much as you would expect.
There is not much online regarding the creating of formulas in sheets, but it turned out quite easy as you can see in the code. One issue that seems to affect a lot of people is how to get any formula you enter to recalculate on opening the sheet. This turns out to be done by ensuring the VALUE of the formula cell is null/nothing. If a value already exists then the sheet will use that cached value else a refresh of the formula occurs.
Private Function CreateFormulaCell(ByVal Header As String, ByVal value As String, ByVal Index As Integer) As Cell Dim newCell As Cell = New Cell(New CellFormula(value), Nothing) newCell.CellReference = Header & Index Return newCell End Function