Export dataset to Excel file in ASP.NET


Environment :

  

    • ASP.NET
    • Visual C#
    • SQL Server
    • MS Excel
    • Microsoft Visual Studio

1. Create one webapplication using visual studio.

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

excel

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.

FileDisplay

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

EmbedExcel

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