Blog

Scenerio: I’ve recently had a a few projects that I’ve created forms for.  I have given the client a login to view all the form submissions, but in the end they want this data in an Excel file so they can easily manage it.

Research: I have found there a quite a few ways to achieve this.  Some take advantage of the Excel interop, some create a .csv, some use third party tools, some use xml. Here is a good discussion I found on the topic at Stack Overflow, http://stackoverflow.com/questions/151005/create-excel-xls-and-xlsx-file-from-c.
All have pros and cons.

Solution: Here is what I have found to be the simplest way to read your dataset in an Excel file. First off, this doesn’t product a true Excel file, but it is something Excel can read into a table which often is all you need.

What I found is you can write the contents of a datagrid to an output stream that can be saved as a .xls file.

code snippet: I am using linq to bind to a datagrid

var registrations = from reg in db.Registrations
                    where reg.JumpPageId == int.Parse((String)e.CommandArgument)
                    orderby reg.TimeStamp descending
                    select reg;
HttpResponse response = HttpContext.Current.Response;

// first let's clean up the response.object
response.Clear();
response.Charset = "";

// set the response mime type for excel
response.ContentType = "application/vnd.ms-excel";
response.AddHeader("Content-Disposition", "attachment; filename=registrants.xls");

// create a string writer
using (StringWriter sw = new StringWriter())
{
    using (HtmlTextWriter htw = new HtmlTextWriter(sw))
    {
        // instantiate a datagrid
        DataGrid dg = new DataGrid();
        dg.DataSource = registrations;
        dg.DataBind();
        dg.RenderControl(htw);
        response.Write(sw.ToString());
        response.End();
    }
}
 

LIke I said, this doesn’t output a true Excel file so when you go to open it, you will be prompted a warning:

It is saying the contents of the file are in a different format than what the .xls extension says it is. 

Click Yes and it will open.

If you were to open this file in a text editor you will see that it really is just the datagrid html output.

Not the most elegant solution but it is a quick way to throw an Export to Excel button on a report page.

My next blog post will show how to create an Excel file using a third party tool.

Filed under: asp.net   Tags: .NET, Excel, linq,
 2 Comments »