I needed to export some query results to an Excel workbook from an Asp.Net 4.0 webpage.
The simplest way I found is to exploit Excel for all the stinky work.
Excel is able to understand an html form and to render it to a worksheet, so this is the starting point.
Thus, I also needed to have this export available on several pages and with different ObjectResult.
Then I decided to write an Extender for an IQueryable object that can get the work done.
This is how the class look like, with some comments
// A static class
public static class ExcelExportExtender
{
static String C_CONTENT = "attachment;filename={0}.xls";
// static method that extend an IQueryable
public static void ExportToExcel(this IQueryable Data,
Page ParentPage, String FileName)
{
if (String.IsNullOrEmpty(FileName))
throw new ArgumentNullException("FileName is null");
HttpResponse ResponseObject = ParentPage.Response;
GridView gdvList = new GridView();
// Let's hide all unwanted stuffing
gdvList.AllowPaging = false;
gdvList.AllowSorting = false;
gdvList.EditIndex = -1;
// Let's bind data to GridView
gdvList.DataSource = Data;
// We exploit the data binding for columns name!
gdvList.DataBind();
// Let's output HTML of GridView
ResponseObject.Clear();
ResponseObject.ContentType = "application/vnd.xls";
String head = String.Format(C_CONTENT, FileName.Trim());
ResponseObject.AddHeader("content-disposition", head);
StringWriter swriter = new StringWriter();
HtmlTextWriter hwriter = new HtmlTextWriter(swriter);
HtmlForm frm = new HtmlForm();
ParentPage.Controls.Add(frm);
frm.Attributes["runat"] = "server";
frm.Controls.Add(gdvList);
frm.RenderControl(hwriter);
ResponseObject.Write(swriter.ToString());
ResponseObject.End();
}
}
and this is an example of how to utilize the ExcelExportExtender
protected void LinkButton1_Click(object sender, EventArgs e)
{
FakeDataAccess fda = new FakeDataAccess();
var DataToExport = from r in fda.RandomData
where (r.MyFirstProperty % 2 ==0)
select r;
// only for learning purposes =)
IQueryable result = DataToExport.AsQueryable();
// "this" refers to the Page where this method lives
// Let's rock!
result.ExportToExcel(this, "EvenRecordsFileName");
}
Job Done!
Download the source code and the example project here!