.neT: Export excel sheet data from dataset in ASP.NET

Environment :

- ASP.NET 1.1v
- Visual C#
- SQL Server2000
- MS Excel2003
- Microsoft Visual Studio2003

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("server=chsridhar;database=NAACP;uid=sa;pwd=mavin");
        SqlCommand cmd = new SqlCommand("select * from Branches", conn);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable ds = new DataTable();            try
            {
            da.Fill(ds);
            }
            catch(Exception ex)
               {
                System.Diagnostics.Trace.WriteLine(ex.Message);
                throw ex;
            }
            finally
            {
                conn.Close();            }
           ExcelUtility.DataSetToExcel.Convert(ds, Response);
        }            }

5. Create required class name as “DataSetToExcel”

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(DataTable ds, HttpContext Response)
        {
            Response.Response.Clear();
            Response.Response.Charset = "";
            Response.Response.ContentType = "application/vnd.ms-excel";
            Response.Response.AddHeader("Content-Disposition", "attachment;filename=\\xxx.xls");
            System.IO.StringWriter stringWrite=new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite=new System.Web.UI.HtmlTextWriter(stringWrite) ;
            System.Web.UI.WebControls.GridView dg = new System.Web.UI.WebControls.GridView();
            dg.DataSource=ds;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
            Response.Response.Write(stringWrite.ToString());
            Response.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(DataTable ds, HttpContext Response)
        {
            Response.Response.Clear();
            Response.Response.Charset = "";
            Response.Response.ContentType = "application/vnd.ms-excel";
            Response.Response.AddHeader("Content-Disposition", "attachment;filename=\\xxx.xls");
            System.IO.StringWriter stringWrite=new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite=new System.Web.UI.HtmlTextWriter(stringWrite) ;
            System.Web.UI.WebControls.GridView dg = new System.Web.UI.WebControls.GridView();
            dg.DataSource=ds;
            dg.DataBind();
            dg.RenderControl(htmlWrite);
            Response.Response.Write(stringWrite.ToString());
            Response.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.

http://balanagaraj.wordpress.com/2007/12/07/export-excel-sheet-data-from-dataset-in-aspnet/