Dynamically Generate Excel Files from ASP.Net

 

Why does it seem so difficult to generate an Excel doc from an ASP.Net page? Now, I know this post is a bit behind the times; Excel 2007 has made this easier. But for the gig I’m working on, I need to generate an Excel 2003 Doc.

The options (according to http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757):

  1. OpenXML. This is for Office 07, but there is a compatibility pack for older versions (http://office.microsoft.com/en-us/products/HA101686761033.aspx). However, in this situation, I have no control over the user environment, and cannot install any additional software, so this is out.
  2. HTML. Neat trick. Well, it used to be a neat trick. Generate html on the server and send it to the client with a MIME type of Excel. The client opens it in Excel, and you have a spreadsheet! Cool! It doesn’t support calculations or multiple spreadsheets. And, there are additional security measures in place in Excel 07 that warns the user that “The file you are trying to open, ‘filename‘, is in a different format than specified by the file extension”. (Since it’s html content being sent as an xls (or xlsx) file. It still works. In fact, it works well, but every user will be calling the help desk to find out what to do about the warning message. More on that warning is here: http://blogs.msdn.com/vsofficedeveloper/pages/Excel-2007-Extension-Warning.aspx . And more on generating the html files is here: http://support.microsoft.com/kb/199841
  3. ADO. I’ve used this with Excel many times before, and it works quite well. However, it’s looking for a table, not a spreadsheet with individual cells that need to be modified scattered all over the place.
  4. CSV. Not mentioned in the above MS article, but this is fast and easy. Of course, this method does not allow you to produce a formatted spreadsheet, or one with multiple workbooks. And, this does not allow you to put a value into a particular cell. It is great getting a datatable into Excel for the user.
  5. XML. This is the one. Generate an XML doc and return it to the client. The doc has metadata embedded in it so that it opens in Excel. It doesn’t support all Excel functionality, but it does support quite a bit.

XML is definitely the way to go. But then why do all of the tutorials (like this one: http://msdn.microsoft.com/en-us/library/aa203722(office.11).aspx) show how to generate a file from scratch in XML? I don’t need to create one, I need to modify one. Isn’t that the standard practice? Have the user create the excel file with all formatting, etc, then programmatically insert values into specific cells? If I in any way embed the xml as part of the process, then the user will need the assistance of a developer to make any modifications to the Excel template.

No. It must be set up so that the user generates the Excel template, and any time the whim strikes them, they should be able to open up the file, make some changes(add rows, add columns, delete rows, whatever). And, the process should still work (If I ever wanted a client to never call me again, I think all I’d have to do is tell them that to change their Excel template, all they have to do is open some xml or xsl file, scroll down to the right spot, and make their change (I’m sure there are companies out there that are willing to pay for legions of developers to maintain that kind of code, but I don’t work for one of them)).

I thought about trying the old, cheap, stupid Word find and replace method. In Word, you could type in a placeholder, like %firstname%. Then, just programmatically read in the doc, do a find and replace, and that’s it. Easy. However, when you type that into Excel, it changes the data type of the field to String. So just doing a string based find and replace won’t work, as when you replace it with a numeric, the data type doesn’t change. Of course, if the file is xml the code could specify the data type as well. But if the user is using drop down lists and the code needs to select the appropriate item from the list then the system of having things like %firstname% typed into the cell won’t work.

So, enough of talking about strategies that don’t work well, and on to what does work:

  1. Set up the Excel workbook:

    In Excel, create named ranges for each cell that you want to access from code. So, if there are 20 cells you need to work with, create 20 named ranges, each being 1 cell in size. Also, the cells need to have data, or they need to be formatted in some way. Otherwise, there won’t be an xml node to manipulate (even if the cell is named). So, give the cells default values, or give them a background color, or just about anything.

    Then, save the workbook as an XML Spreadsheet 2003 (*.xml)

  2. Code. Again, what I needed was an asp.net app that returned the excel file to a client. So the following is for a C# ASP.Net app, in an otherwise empty web form:

First, add a using statement for System.Xml, and a couple of private declarations:

XmlDocument doc;
XmlNamespaceManager mgr;
 

Then, write a function to load the excel template file into the XmlDocument, and init the XmlDocument with the necessary xml namespaces:

private
void InitXmlDoc(string xmlFilePath)
{
doc = new XmlDocument();
doc.Load(xmlFilePath);
mgr = new XmlNamespaceManager(doc.NameTable);
mgr.AddNamespace(string.Empty, “urn:schemas-microsoft-com:office:spreadsheet”);
mgr.AddNamespace(“o”, “urn:schemas-microsoft-com:office:office”);
mgr.AddNamespace(“x”, “urn:schemas-microsoft-com:office:excel”);
mgr.AddNamespace(“ss”, “urn:schemas-microsoft-com:office:spreadsheet”);
mgr.AddNamespace(“html”, http://www.w3.org/TR/REC-html40);
mgr.AddNamespace(“def”, “urn:schemas-microsoft-com:office:spreadsheet”);
}

 

The following is a snippet of the Excel xml file:

<Row
ss:AutoFitHeight=”0″>
<Cell><Data
ss:Type=”String”>Name</Data></Cell>
<Cell
ss:StyleID=”s62″><NamedCell
ss:Name=”Name”/></Cell>
</Row>
<Row
ss:AutoFitHeight=”0″>
<Cell><Data
ss:Type=”String”>Duration</Data></Cell>
<Cell
ss:StyleID=”s63″><NamedCell
ss:Name=”Duration”/></Cell>
</Row>

 

The above cells are empty, here is what they look like with data:

<Row ss:AutoFitHeight=”0″>
<Cell><Data
ss:Type=”String”>Name</Data></Cell>
<Cell
ss:StyleID=”s62″><Data
ss:Type=”String”>Mike</Data><NamedCell

ss:Name=”Name”/></Cell>
</Row>
<Row ss:AutoFitHeight=”0″>
<Cell><Data
ss:Type=”String”>Duration</Data></Cell>
<Cell
ss:StyleID=”s63″><Data
ss:Type=”Number”>5</Data><NamedCell

ss:Name=”Duration”/></Cell>
</Row>

 

So, all we have to do is use xPath to reach in to the doc, find the cells with the appropriate named range, and either add a data element or modify an existing data element:

public void setValue(string namedRange, string newString, string newDataType){
//try to find the cell with the specified named range

string xpathtoCell = String.Format(“//def:Cell[def:NamedCell/@def:Name='{0}’]”, namedRange);
XmlNode CellNode = doc.SelectSingleNode(xpathtoCell, mgr);
if (CellNode != null)
{
//got the cell, does it have a data element?
XmlNode datanode = CellNode.SelectSingleNode(“def:Data”, mgr);
  if (datanode != null)
  {
    //got the data node, now just change the innertext
    datanode.InnerText = newString;
  }
  else
  {
  //the cell didn’t have a data element as a child, need to add one and populate it
  XmlElement data = doc.CreateElement(“ss”, “Data”, “urn:schemas-microsoft-com:office:spreadsheet”);
  data.InnerText = newString;
  XmlAttribute att = doc.CreateAttribute(“ss”, “Type”, “urn:schemas-microsoft-com:office:spreadsheet”);
  att.Value = newDataType;
  data.Attributes.Append(att);
  CellNode.AppendChild(data);
  }
}
}

 

Note, the data types can be one of: “Number”, “DateTime”, “Boolean”, “String”. You do have to ensure that the data matches the appropriate type. For example, dates need to be stored in Excel XML files as:

2010-01-01T00:00:00.000

And, bools are stored as either 0 or 1.

So, with the above functions, to run a replace all that is needed in the page_load event is:

InitXmlDoc(Server.MapPath(“Book1.xml”));
setValue(“Name”, “Mike”, “String”);
setValue(“Duration”, “6”, “Number”);

And, I needed this to be generated from an ASP.Net page, so the end result is that the user clicks on a link and then is prompted to open their file. So, to return the file:

byte[] bytes = System.Text.Encoding.ASCII.GetBytes(doc.OuterXml);
//SEND
Response.Clear();
Response.ContentType = “application/vns.ms-excel”;
Response.AddHeader(“Content-Disposition”, “attachment;filename=NewExcelFile.xml”);
Response.BinaryWrite(bytes);

 Enjoy!

Advertisements

One thought on “Dynamically Generate Excel Files from ASP.Net

  1. Pingback: Export to Excel in ASP.NET, issue with decimal formatting for numbers greater than 1000 | trouble86.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s