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.

Advertisements

49 thoughts on “Export dataset to Excel file in ASP.NET

  1. Hi,

    If I have to esport more than 65536 rows of data what do i do? I can have only 65536 rows of data in a single excel sheet. So can i split the data to get exported in two sheets or something like that?

    Regards,
    Rahul

  2. Thanks a lot for the code,Please tel me
    my problem is export data through store procedure
    then facing a problem
    because i write ur both code in a class like ExportData(),ConvertData(DataSet ds, HttpResponse Response)
    when i call convertdata(ds,responce) in exportdata()
    then error is
    the name responce can not exit in current context
    pls replay me my mail id is ashutoshis2cool@hotmail.com

  3. Hi Balu,

    1) I am using 64bit machine windows server 2008, VS 2005, SQL 2005 .

    2) I tried with instructions provided by you for “Export excel sheet data from dataset in ASP.NET” but when i build the application following 3 error messages are displayed

    3)Can you send the solution to correct this error.

    Error 1 ‘ASP.datasettoexcel_aspx.GetTypeHashCode()’: no suitable method found to override c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\kumar\c6ace00d\f0d80076\App_Web_1rqf1ik-.2.cs 358

    Error 2 ‘ASP.datasettoexcel_aspx.ProcessRequest(System.Web.HttpContext)’: no suitable method found to override c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\kumar\c6ace00d\f0d80076\App_Web_1rqf1ik-.2.cs 363

    Error 3 ‘ASP.datasettoexcel_aspx’ does not implement interface member ‘System.Web.IHttpHandler.IsReusable’ c:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\kumar\c6ace00d\f0d80076\App_Web_1rqf1ik-.2.cs 136

  4. I would like to know whether the default name in the
    File Download dialog box can be changed to a name different from that of the corresponding aspx file

  5. Response.AddHeader(“content-disposition”, string.Format(“attachment;filename={0}”, rsFileName));

    Add this line to add header and add the file name

  6. hi balanagaraj
    your code is nice. how can we do same thing in Asp.net 2.0. please help me if have idea about it.
    i tried but i am getting error in using System.IO;namespace ExcelUtility

  7. hi
    I want to export gridview data to excel file cells. Using ur code im getting only the total no. of rows and columns grid contains in excel file ,missing other cells. And the cells are not looking like excel cells.

  8. 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();
    }

  9. Thanks a lot for the code,Please mail me the code samples for that
    one question I have

    If I have to e sport more than 65536 rows of data what do i do? I can have only 65536 rows of data in a single excel sheet. So can i split the data to get exported in two sheets or something like that?

    I am facing problem
    plz give me the solution for it

  10. I want to make to excel files but in the fun reponse.end
    can not make two file ,Execution will be stopped because of response.end

  11. Thanks a lot for the code. Its just like a miracle for me.
    Is any one familiar with the formatting the Heading names of the Table fields in excel file.

  12. i want the code which is directly save the excel file without the open/save/close dialog box…

    have you that code???
    so please inform me….

    • this code is save directly to the particular folder.

      Dim sw As New StringWriter()
      Dim hw As New HtmlTextWriter(sw)
      GridView1.DataBind()
      GridView1.RenderControl(hw)
      Dim style As String = “.textmode{mso-number-format:\@;}”
      System.IO.File.WriteAllText(”F:\\Test.xls”, style & sw.ToString())

  13. hi,
    i have used ur code to save the excel file directly but im gettion access denied issue.
    though i have given full permission sto that path.
    is there any way to convert the datattable data directly into image data type to storeit in database

    • Hi, Can you please tell me briefly about your second question?
      “is there any way to convert the datattable data directly into image data type to storeit in database”

  14. Interesting read. There is currently quite a lot of information around this subject around and about on the net and some are most defintely better than others. You have caught the detail here just right which makes for a refreshing change – thanks.

  15. hi bal,
    code is nice but it is making difficult to implement it.
    i am doing this in
    1)WEB developer 2008 express edition

    2) C# .net Express Edition

    but it is giving errors.
    can u please solve my problem;;;
    it is damnnnn urgent;;;;

    anyone can solve my problem with this code;
    thank u,

  16. Hi bala,

    This code is very nice and it is use ful to me.
    But i require if there is multiple Tables in DataSet i wish to export each Table in seperate worksheet in that same workbook

    Please help me

  17. i applied this code ,but the popup is not coming up to ask to save the file ,and also the excel file is also not saved
    kindly tell me if i am missing any DLL reference

    help me ………………

  18. thanks !!!!
    i got the things going ,it was because of update panel
    kindly note that this wil not work if the button is placed within
    update panel ,keep it out of the update panel button

    but then also a very helpful code for me
    thanks once again

  19. i got the following error, what todo?
    please respond!
    The XML page cannot be displayed
    Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

    ——————————————————————————–

    The operation completed successfully. Error processing resource ‘http://localhost:1030/Default.aspx’. Line 5, Position 284

  20. sorry, but may i ask?
    what about exporting excel using vb.net?
    maybe you know how to do it?

    i’am currently using vb 2010 and office 2010

  21. Pingback: free ebook

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s