Excel reporting in ASP.NET using C# and DataGrid

Introduction

Excel is a very powerful tool for data analysis and reporting. People who work in the financial industry know that one way or the other Excel spreadsheets are used heavily. The focus of this article is on exposing a few techniques and concepts when dealing with ASP.NET and MS Excel.

Report Generation:

The following sample code explains about Excel report generation from a DataGrid, and also how to add HTML code in that report:

private void btnExcelReport_Click(object sender, System.EventArgs e)
{
  DataSet dsExport = GetData();
  System.IO.StringWriter tw = new System.IO.StringWriter();
  System.Web.UI.HtmlTextWriter hw = 
     new System.Web.UI.HtmlTextWriter(tw);
  DataGrid dgGrid = new DataGrid();
  dgGrid.DataSource = dsExport;

  //Report Header
  hw.WriteLine("<b><u><font size='5'>" + 
     "Report for the Fiscal year: " + txtFY.Text + 
     "</font></u></b>");
  hw.WriteLine("<br>&mp;nbsp;");
  // Get the HTML for the control.
  dgGrid.HeaderStyle.Font.Bold = true; 
  dgGrid.DataBind();
  dgGrid.RenderControl(hw);

  // Write the HTML back to the browser.
  Response.ContentType = "application/vnd.ms-excel";
  this.EnableViewState = false;
  Response.Write(tw.ToString());
  Response.End();
}