Excel Reporting with Datagrid Control

One easy way to generate Excel report is to use DataGrid control as a rendering engine. DataGrid has a rendering method named RenderControl that funnels output into a html text stream. The stream is then added to HttpResponse instance as an attachment. By properly set the header, user ends up get a Excel reporting file

The "engine" absorbs data from binding datasource and render into an Excel file stream. For simple reporting requirement, this is a perfect approach.

private void ExcelReport(HttpResponse resp){
System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
grid.HeaderStyle.Font.Bold = true;
grid.DataSource = somedatasource;
grid.DataMember = somedatamember_if_needed; // incase of dataset to specify which datatable to bind
grid.DataBind();

// render the DataGrid control to a file
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
grid.RenderControl(htw);

resp.Clear();
resp.AddHeader("Content-Disposition", "attachment;filename=" + "report.xls");
resp.ContentType = "application/vnd.ms-excel";
resp.Cache.SetCacheability(HttpCacheability.Private);
resp.Charset = "";
resp.Write(sw.ToString());
resp.End();
}

Rather than use DataGrid's RenderControl() method:

public static void ExportToSpreadsheet(DataTable table, string name)
{
if (table == null)
return;

string strDelimitor = ",";

HttpContext context = HttpContext.Current;

context.Response.Clear();

foreach (DataColumn column in table.Columns)
{
context.Response.Write(column.ColumnName + strDelimitor);
}

context.Response.Write(Environment.NewLine);

foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
context.Response.Write(row[i].ToString().Replace(strDelimitor, string.Empty) + strDelimitor);
}
context.Response.Write(Environment.NewLine);
}

context.Response.ContentType = "text/csv";
context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");
context.Response.End();
}