Streaming native Excel Workbooks to the Browser with Office Web Components
By Peter A. Bromberg, Ph.D.
Printer - Friendly Version
Peter Bromberg

There are many enterprise web - based applications where it is necessary or desireable to stream reports and other data to the user in a viewable, downloadable Excel Spreadsheet. Until recently, the only way to do this was to have Excel installed on the webserver, instantiate an instance of the COM Server (Excel.Application) through Interop, and hope that it went away after you were done. In my previous article, "Dynamic ASP.NET Excel Workbooks in C#" , I show how this can be done in ASP.NET.

However, there are a number of problems involved with this technique, not the least of which is the difficulty in cleaning up all the Interop-invoked COM classes from the server's memory space. The EXCEL.EXE COM server is not free-threaded, and so this causes even more potential problems with scalability in a multi-user environment. Sure, you can have native Excel workbook generation from the web server for your application. However you may also get a lot of flack from the unhappy Production team that has to keep the server(s) running!

Is there a better way? Yup! Starting with Office 10 (XP) MS has provided the Office Web Components, a free downloadable COM component pack that is indeed free - threaded. It's also a lot more lightweight than using the COM Server built into the main Excel executable, and can be used without Excel or any other Office component needing to be installed on the web server. Nice! To start out, if you don't have it, download it here.



To use the Excel OWC Component, all you need to to is set a COM reference to the installed library in ASP.NET. Fire up a new ASP.NET WebForms application, and stick a button and a DataGrid control on the design surface.

Click "Add Reference" from the Solution Explorer, choose the COM tab, and find the library, which will look like this:

 

Click "OK" and VS.NET will build a COM Interop wrapper for you, called appropriately, "OWC10". Add the folowing using statements to your WebForm codebehind class:
using OWC10;
   using System.IO;

What I'll show next is the code to get a reader from the SQL Server Northwind Database, bind the grid, and then the code to handle the button click to get the reader, create the spreadsheet with the OWC component, and stream it to the browser. I've also included a helper method that checks the file system every time a user requests a spreadsheet and cleans up any files older than 2 minutes:

private void Page_Load(object sender, System.EventArgs e)
{
BindDataGrid();
}

private void BindDataGrid() {
cnn = new SqlConnection("Initial Catalog=Northwind;Data Source=localhost;uid=sa;pwd=");
sql = new SqlCommand("select * from products",cnn);
cnn.Open();
SqlDataReader reader = sql.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataSource = reader;
DataGrid1.DataBind();
reader.Close();

}

private void ExportDataGridToExcel() {
SpreadsheetClass xlsheet = new SpreadsheetClass();
cnn.Open();
SqlDataReader reader = sql.ExecuteReader(CommandBehavior.CloseConnection);
int numcols = reader.FieldCount;
int row=1;
// lets create the header row from the column names in the reader....
for(int j=0;j<reader.FieldCount;j++)
{
xlsheet.ActiveSheet.Cells[row, j+1] = reader.GetName(j).ToString();
}
row++; // start data at row 2
// dump our data into the sheet...
while (reader.Read()) {
for (int i=0;i<numcols;i++)
{
xlsheet.ActiveSheet.Cells[row,i+1] = reader.GetValue(i).ToString();
}
row++;
}
reader.Close();

// use this just to get a unique filename...
string xlFileName=System.DateTime.Now.Ticks.ToString() +".xls";
// save it off to the filesystem...
xlsheet.Export(Server.MapPath(".")+"\\"+xlFileName,
     OWC10.SheetExportActionEnum.ssExportActionNone,
        OWC10.SheetExportFormat.ssExportHTML);
// set content header so browser knows you'r sending Excel workbook...
Response.ContentType="application/x-msexcel" ;
// stream it on out!
Response.WriteFile(Server.MapPath(".")+"\\"+xlFileName);
// clean up old files...
RemoveFiles(Server.MapPath("."));
}

private void ViewInExcel_Click(object sender, System.EventArgs e)
{
ExportDataGridToExcel();
}

private void RemoveFiles(string strPath)
{
System.IO.DirectoryInfo di = new DirectoryInfo(strPath);
FileInfo[] fiArr = di.GetFiles();
foreach (FileInfo fi in fiArr)
{
if(fi.Extension.ToString() ==".txt" || fi.Extension.ToString()==".csv" || fi.Extension.ToString()==".xls")
{
// if file is older than 2 minutes, we'll clean it up
TimeSpan min = new TimeSpan(0,0,0,2,0);
if(fi.CreationTime < DateTime.Now.Subtract(min))
{
fi.Delete();
}
}
}
}

What I've done with this for production use is to encapsulate the call into a generic "ExcelUtility" class library. You pass in the stored procedure and SqlConnection, the parameters, the Title string, which becomes the first few custom lines of the spreadsheet, and the method returns the URI of the finished Excel workbook on the disk for either your hyperlink control or for the Response.WriteFile method. In this OOP manner, anybody can use the class and simply tell it they want an excel spreadsheet from such-and-such sproc with these parameters and this title string, and back it comes!

 

 

 

Download the Code that accompanies this article

 


Peter Bromberg is a C# MVP, MCP, and .NET consultant who has worked in the banking and financial industry for 20 years. He has architected and developed web - based corporate distributed application solutions since 1995, and focuses exclusively on the .NET Platform.