ASP.NET: Export to Excel Methods

Written By: Christopher Brandsma,  chris.brandsma@gmail.com
Tree Top Technologies  (www.treetoptech.com)
Date: June 2, 2005
Technologies: .NET Framework 1.1, ASP.NET, ADO.NET, C#, Excel

Purpose:

Background:

I am going to start by labeling the two methods: the Traditional way and the Extreme way.  There is nothing extreme about the code involved in the Extreme way, but it does help highlight one of the main differences between the two methods.

But first an explanation of the Traditional way. If you do any Google search on ASP.NET Export to Excel, you will see this method.   The formula works like this: take a standard ASP.NET DataGrid, do the standard data binding, grab the output, and stream it to Excel.  The best part is, using this method, the data is formatted in Excel just the same as it is in the ASP.NET DataGrid.  If you do any special text formatting in the DataGrid (using the ItemDataBound event) it will be formatted that way in both the DataGrid and in Excel.

The basic code for this looks like this (note: I’m using the Oracle data client here, don’t worry, it works for all the different data clients):

  string sConn = ConfigurationSettings.AppSettings["OracleConnectionString"];
  OracleConnection oConn = new OracleConnection(sConn);
  string sHTML = "";
  using(OracleCommand oCmd = new OracleCommand(sql, oConn))
  {
    oConn.Open();
    OracleDataReader oReader = oCmd.ExecuteReader(CommandBehavior.SingleResult);
    // setup and load the grid, 
    System.Web.UI.WebControls.DataGrid oGrid = new System.Web.UI.WebControls.DataGrid();
    oGrid.HeaderStyle.BackColor = System.Drawing.Color.Silver;
    oGrid.HeaderStyle.ForeColor = System.Drawing.Color.Black;
    // Bind the data to the grid

    oGrid.DataSource = oReader; oGrid.DataBind();   
    // grab the data grid html text 
    System.IO.StringWriter oStringWriter = new System.IO.StringWriter(); 
    System.Web.UI.HtmlTextWriter oHTMLWriter = new   System.Web.UI.HtmlTextWriter(oStringWriter); 
    oGrid.RenderControl(oHTMLWriter); 
    sHTML = oStringWriter.ToString(); 
    oHTMLWriter.Close(); 
    oStringWriter.Close(); 
   } 
   // send the data to Excel on the client’s machine 
   Response.Clear(); 
   Response.ContentType = "application/vnd.ms-excel"; 
   Response.Charset = ""; 
   Response.Write(sHtml); 
   Response.End(); 

But then again…

But there is one little side effect: when you use this method, all of the data in the query has to be downloaded, and loaded into the ASP.NET DataGrid before it can be sent to Excel on the Client’s machine.  If you are not pulling large amounts of data and the connection between the database and the web server is fast, this may not be an issue.   Otherwise there are two significant issues:

1. Amount of data.  Remember, all of the data is built into an ASP.NET DataGrid, and then sent to the client browser – via a STRING!  Trust me, if you are dealing with a multi-thousand row query, that string can be pretty large.  Also, all of the output is being built in memory.  So you really will have three copies of every data element in memory to perform this method: one in ADO.NET, one in the DataGrid, and the other in the string you are returning.  Now add multiple people performing the action at the same time and you had better be prepared for “Out of Memory” error popping up from time to time.
2. Next is the amount of time it takes to return the data.  For large exports, it can take several minutes to return all of the data.  Now, what is happening on the client’s browser while all of this is happening?  The browser is sitting there happily, with the waiting for response icon spinning.  After a minute or two, the client will think that the browser is hung and shut down.  If you are really lucky, they will call you up to tell you that the server is hung.  Also remember point one: there is nothing quite like having to wait 5-10 minutes for you data, only to have the server run out of memory at the last minute.  Good time!
3. You get no control over the number of rows, or the size of the cells of data returned to Excel.  Excel has limits, and this method will ram right over them.

Enter the Extreme Way

I call this the Extreme Way because it is specifically suited for dealing with exporting extreme amounts of data (well, extreme amounts of data for Excel might not be that much – say 10,000 rows, but you get the idea).  

The idea here is to forgo the tradition use of using the ASP.NET components just because they exist.  This is also breaking a Microsoft ASP.NET best practice, but best practices are only best practices when they work.  If a best practice causes IIS to run out of memory, it is no longer a best practice in my book. 

So, in this method, we are going to create the HTML table by hand, streaming it to the client as we go.  And not using StringBuilder either!  To use StringBuilder would be to build the entire file on the server again, we don’t want that.

There is one other piece to this puzzle that needs to be exposed, and that is this code:

This code removes any special characters that would make Excel thing that the output is a formula instead of data.  If you don’t do this, you will be inundated with “Cell data too large” messages from Excel.

Great, so now where are we now

So how does this help?  First off, within seconds, the user is presented with the dialog to either Save, Open, or Cancel (see dialog below).

If the user clicks Save, they will see this dialog:

Now compare what is happening on the web server between the two methods. 

• With the Traditional Way you will see the amount of memory being consumed by the server steadily rise until the file is sent back to the user. 
• With the Extreme Way the memory rise a small bit, and then stay relatively steady.  Because the data is being sent as soon as it is read, there is no caching, and no excess memory usage.  Meaning: no “Out of Memory” errors.  Or, if you do get an “Out of Memory” error, it is probably someone else’s fault.

 Note: use the Task Manager or PerfMon to monitor your memory usage.  Other sources for .NET performance monitoring can be found at http://msdn.microsoft.com/perf and at Rico Mariani's Performance Tidbits bog http://blogs.msdn.com/ricom.

So is this really any better for the user? 

Don’t forget the user.  So the server likes this better, what about the end user?

Is this way better? Yes.

Is this way faster?  Not really. 

The difference is in perception.  Using the traditional method, the user would receive no feedback from the system to tell them that: “Yes, I, the server you have contacted, have received your request, and I am working on it.  Please return back for the answer in 5 million years.”

Using the Extreme way, the user quickly sees that things are happening: data is moving, a file is being created on the spot, spontaneous joy commences!  Or something like that. The key is feedback.  Users hate feeling helpless to the computer.  Giving them some feedback as to what is happening lets the user feel involved and part of the process.  That way they are more likely to forgive your bad coding on insufficient web servers.

Links:

• Rico Mariani's Performance Tidbits bog http://blogs.msdn.com/ricom
• Microsoft's Performance site: http://msdn.microsoft.com/perf
• Export ASP.NET DataGrid to Excel
• How To Export Data in a DataGrid on an ASP . NET WebForm to Microsoft Excel