One of the most common (and most troubling) forum posts and requests we have gotten here at eggheadcafe.com over the last couple of years has been people wanting some solution to create Excel Workbooks on the Server and send them to the browser. Let's face it, MS Excel is extremely popular, especially among the non-programmer "Office crowd" (no pun intended).

Unfortunately, Excel and its brethren Office products were never designed to be a free-threaded COM Servers. Ask any developer who has attempted to do COM Interop with it via ASP.NET and had to kill multiple copies of EXCEL.EXE in Task Manager on their webserver, and you will quickly understand.

A quick search up top in our Search section on the "Excel" keyword will reveal that we have tackled this issue several times, including such items as exporting a DataGrid to Excel. This offering provides a few definite benefits:

1) It is extremely lightweight, involving only a static method call on a very small C# library that can be included in any project.

2) There is no COM Interop and Excel does not need to be installed on the server.

3) It is "understood" by both Excel 2002 and Excel 2003.

4) It can be "extended", if desired, to handle more complex requirements.

Without launching into a wordy prologue, lets get down to the nitty gritty:

1) We pass a DataSet to our static method.

2) We pull an XSLT Stylesheet out of our assembly (nothing to deploy or get lost server-side).

3) We convert the DataSet to its underlying XmlDataDocument.

4) We perform an XSL Transform and send it back out as Excel XML.

Now here's the code for the small utility library:

using System.Data;
using System.IO;
using System.Xml;
using System.Xml.Xsl;

namespace ExcelUtil
{
 public class WorkbookEngine
 {
 // you could have other overloads if you want to get creative...
 public static string CreateWorkbook(DataSet ds)
 {
  XmlDataDocument xmlDataDoc = new XmlDataDocument(ds);
   XslTransform xt = new XslTransform();
   StreamReader reader =new 
  StreamReader(typeof (WorkbookEngine).Assembly.GetManifestResourceStream(typeof (WorkbookEngine), "Excel.xsl"));
   XmlTextReader xRdr = new XmlTextReader(reader);
   xt.Load(xRdr, null, null);
   StringWriter sw = new StringWriter();
   xt.Transform(xmlDataDoc, null, sw, null);
   return sw.ToString();
  }
 }
}

Pretty slick, eh? Right now it only handles DataSets with one table (if you have more than one table, it will get rendered just below the first one, but in the same worksheet). However, with some judicious reworking of the stylesheet, it would not be too difficult to write some nifty XSLT that does a for-each-select on the <TABLE> nodes and creates a separate worksheet for each.

And now, some sample code to send a DataSet into this, get the Excel Workbook, and stream it to the browser to be either displayed or saved:

DataSet ds = new DataSet();
   /*
    SqlConnection cn = new SqlConnection("server=(local);database=Northwind;user id=sa;password=;");
    SqlCommand cmd = new SqlCommand("Select * from customers;Select * from employees",cn) ;
    cn.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    da.Fill(ds);
    ds.WriteXml(Server.MapPath("Customers.xml")) ; 
    */  
   

   ds.ReadXml(Server.MapPath("Customers.xml"));
   string xml = WorkbookEngine.CreateWorkbook(ds);
   Response.ContentType = "application/vnd.ms-excel";
   Response.Charset = "";
   Response.Write(xml);
   Response.Flush();
   Response.End();

Note that above, I've left in and commented out my original code to save the DataSet as XML so that you can try this out without the need for any database.

So, if you need a very lightweight, fast, no-hassle way to send your people an Excel Spreadsheet of a DataSet for a report, or whatever purpose, look no further. The downloadable solution includes everything along with the XSLT that is built as an embedded resource in the assembly.

Now, having said that -- if you want to get more sophisticated, Carlog Aguilar Mares has produced his ExcelXmlWriter library which I highly recommend. Excellent work, Carlos! And, the price is right.

Download the Visual Studio.NET solution that accompanies this article