Tag: excel

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!