| 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
|