Export excel sheet data from dataset in ASP.NET

1. Create one webapplication using visual studio.

2. Just put the button text as "Exports to Excel" in webform1.aspx.

3. Use the following namespaces for this application

using System;
using System.Collections;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

4. In button(Exports to Excel) click event

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["PubsConnection"]); SqlCommand cmd = new SqlCommand("select * from authors", conn); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); try { da.Fill(ds); } catch(Exception ex) { System.Diagnostics.Trace.WriteLine(ex.Message); throw ex; } finally { conn.Close(); } DataSetToExcel.Convert(ds, Response); } }

5. Create required class name as "DataSetToExcel"

using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI.WebControls; using System.IO;namespace ExcelUtility { /// <summary> /// Summary description for DataSetToExcel. /// </summary> public class DataSetToExcel { public DataSetToExcel() { // // TODO: Add constructor logic here // } } }

6. In DataSetToExcel class create the method name as called "Convert"

Convert method used for transfering dataset data to excel sheet and this method have requires two parameters are 'dataset' and 'Httpresponse'.

public static void Convert( DataSet ds, HttpResponse Response) { Response.Clear(); Response.Charset=""; Response.ContentType="application/vnd.ms-excel"; System.IO.StringWriter stringWrite=new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite=new System.Web.UI.HtmlTextWriter(stringWrite) ; System.Web.UI.WebControls.DataGrid dg=new System.Web.UI.WebControls.DataGrid(); dg.DataSource=ds.Tables[0]; dg.DataBind(); dg.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); }

Now the class with convert method.

using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI.WebControls; using System.IO;namespace ExcelUtility { /// <summary> /// Summary description for DataSetToExcel. /// </summary> public class DataSetToExcel { public DataSetToExcel() { // // TODO: Add constructor logic here // } public static void Convert( DataSet ds, HttpResponse Response) { Response.Clear(); Response.Charset=""; Response.ContentType="application/vnd.ms-excel"; System.IO.StringWriter stringWrite=new System.IO.StringWriter(); System.Web.UI.HtmlTextWriter htmlWrite=new System.Web.UI.HtmlTextWriter(stringWrite) ; System.Web.UI.WebControls.DataGrid dg=new System.Web.UI.WebControls.DataGrid(); dg.DataSource=ds.Tables[0]; dg.DataBind(); dg.RenderControl(htmlWrite); Response.Write(stringWrite.ToString()); Response.End(); } } }

after builded the solution run the project. Click the exports to excel button, new 'file download dialog box' will appear in front of the screen.

If you click open, the excel sheet embed with browser.

Otherwise you can save excel sheet file to your local system using click the save button.