How to Export DataSet to Excel

Have you ever wondered how to export your Data Set to an Excel Spreadsheet, I think this is an easy way of doing things you just need to use the HttpResponse, HtmlTextWriter and DataGrid to achieve that.

HttpResponse will encapsulates HTTP-response information from an ASP.NET operation, the HtmlTextWriter writes markup characters and text to an ASP.NET server control output stream while the DataGrid renders the DataSet.

Now here is the code:

public static void ExportDataSetToExcel(DataSet oInputData, string filename)
 {
 HttpResponse oResponse = HttpContext.Current.oResponse;

 // Initializing oResponse Object
 oResponse.Clear();
 oResponse.Charset = "";

 // Set the Response Mime type to Excel
 oResponse.ContentType = "application/ms-excel";
 oResponse.AddHeader("Content-Disposition", "attachment;filename="" + filename + """);

 using (StringWriter oStringWriter = new StringWriter())
 {
 using (HtmlTextWriter oOutputData = new HtmlTextWriter(oStringWriter))
 {
 DataGrid dgToExport = new DataGrid();
 dgToExport.DataSource = oInputData.Tables[0];
 dgToExport.DataBind();
 dgToExport.RenderControl(oOutputData);
 oResponse.Write(oStringWriter.ToString());
 oResponse.End();
 }
 }
 }