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.

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.

• .NET export dataset in to excel
• Convert dataset in to Excel using ASP.NET
• convert dataset to excel
• dataset and excel
• dataset converts excel in asp.net
• Dataset to excel
• Export dataset in to MS Excel in ASP.NET
• export dataset to excel
• export dataset to excel ASP.NET
• export dataset to excel C#
• Export excel sheet data from dataset in ASP.NET
• exports dataset to excel sheet
• How to export Dataset into Excel file in ASP.NET