Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to export DataTable to Excel in ASP.NET

Author: Rachel Nicole
Download Source Code : 1325_ExportDataSetToExcel.zip

The purpose of this article is to explain how we can export DataSet or DataTable to Excel spreadsheet.

 

Sometime you need to export your data from database table to excel spreadsheet. There are many solutions available to export DataSet or DataTable to Excel spreadsheet. I will explain a simple solution to achieve this task.

I will get data in DataTable object from Customers table of NORTHWIND database and convert this DataTable data into Excel spreadsheet. You must have Excel 97 or later installed in your system to run this code successfully. In this code, I am using (.xls) extension for excel. If you try to change extension to (.xlsx), it will give error and file will not open. You need to use third party library to export data to excel 2007.
 

  1. Open Visual Studio 2010
  2. File > New > Web Site
  3. Visual Basic or Visual C#  > ASP.NET Empty Web Site
  4. Right click on web site > Add New Item > Web Form
  5. Open code behind file and include following namespaces

    C#
     
    using System.IO;
    using System.Data;
    using System.Data.SqlClient;
     
    VB.NET
     
    Imports System.IO
    Imports System.Data
    Imports System.Data.SqlClient
     
  6. Write below function is code behind file

    C#
     
    protected void ExportDataSetToExcel() {
        string cmdText = "SELECT * FROM Customers";
        string myConnection = "Data Source=Local;Initial Catalog=C:\\SQL SERVER 2000 SAMPLE DATABASES\\NORTHWND.MDF;Integrated Security=True";
        SqlConnection connection = new SqlConnection(myConnection);
        SqlCommand command = new SqlCommand(cmdText, connection);
        connection.Open();
        SqlDataAdapter da = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        da.Fill(dt);
        connection.Close();
     
        GridView GridView1 = new GridView();
        GridView1.DataSource = dt;
        GridView1.DataBind();
     
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        Response.AddHeader("content-disposition", "attachment;filename=Customers.xls");
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.RenderControl(hw);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
     
     
    VB.NET
     
    Protected Sub ExportDataSetToExcel()
        Dim cmdText As String = "SELECT * FROM Customers"
        Dim myConnection As String = "Data Source=Local;Initial Catalog=C:\SQL SERVER 2000 SAMPLE DATABASES\NORTHWND.MDF;Integrated Security=True"
        Dim connection As New SqlConnection(myConnection)
        Dim command As New SqlCommand(cmdText, connection)
        connection.Open()
        Dim da As New SqlDataAdapter(command)
        Dim dt As New DataTable()
        da.Fill(dt)
        connection.Close()
     
        Dim GridView1 As New GridView()
        GridView1.DataSource = dt
        GridView1.DataBind()
     
        Response.Clear()
        Response.Buffer = True
        Response.Charset = ""
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("content-disposition", "attachment;filename=Customers.xls")
        Dim sw As New StringWriter()
        Dim hw As New HtmlTextWriter(sw)
        GridView1.RenderControl(hw)
        Response.Output.Write(sw.ToString())
        Response.Flush()
        Response.End()
     
    End Sub
     
    Here is the explanation of the code.
    We are using Customers table in NORTHWIND database to get data.
    Write query to get data from database table.
    Establish your connection to the server.
    Create SqlCommand object by passing query and SqlConnection object.
    Get data in SqlDataAdapter object.
    Create DataTable Object and fill DataTable.
    I am creating a dummy GridView control to bind DataTable to it.
    Now start the important part of the code to export data to excel spreadsheet.
    First clear all content output from buffer stream.
    Set Buffer property of Response object to “True” so that it buffer output and send it after complete response processing.
    Set the Charset property to empty.
    Set the ContentType property of Response object for MS Excel.
    Call the AddHeader() method of Response object. The parameters provided in AddaHeader("content-disposition", "attachment;filename=Customers.xls") method will force the browser to download the specified file.
    Create a StringWrite object.
    Now create HtmlTexWrite object by passing StringWriter object as parameter. HtmlTexWrite object writes text to ASP.NET Server control output stream.
    Now call the RenderControl() method of the server control which outputs the content of server control to provided HtmlTextWriter object.
    Call the Write() method of Response object which writes string to output stream.
    Flush() method will send all currently buffered output to the client.
    End() method will stop execution. 
     
  7. Now above method in Page Load event

    C#
     
    protected void Page_Load(object sender, EventArgs e) {
        ExportDataSetToExcel();
    }
     
    VB.NET
     
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        ExportDataSetToExcel()
    End Sub
     
  8. Right Click on website > View web site in browser.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to use ObjectDataSource with GridView in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net