Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

How to Export Data to Excel from Datatable or Gridview in Asp.net using c#, vb.net

Nov 5, 2014
Introduction

Here I will explain how to export data to excel from datatable or dataset in
asp.net using c# and vb.net or export data to excel from gridview in asp.net using c# and vb.net.

Description:

In previous posts I explained export gridview data to csv file in asp.net, export gridview data to pdf file in asp.net, export data from excel to gridview in asp.net, import excel data to sql server in asp.net,
asp.net Google currency converter and many articles relating to gridview, asp.net, c#. Now I will explain how to export data to excel from datatable or dataset in asp.net using c# and vb.net.

To export data to excel from datatable or gridview we need to write the code like as shown below in aspx page


<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Export Data to Excel from datatable or gridview in Asp.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="gvDetails" AutoGenerateColumns="false" CellPadding="5" runat="server">
<Columns>
<asp:BoundField HeaderText="UserId" DataField="UserId" />
<asp:BoundField HeaderText="UserName" DataField="UserName" />
<asp:BoundField HeaderText="Education" DataField="Education" />
<asp:BoundField HeaderText="Location" DataField="Location" />
</Columns>
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
<asp:Button ID="btnExport" runat="server" Text="Export to Excel from Gridview"
onclick="btnExport_Click" />
<asp:Button ID="btnExportTable" runat="server" Text="Export to Excel from Datatable"
onclick="btnExportTable_Click" />
</form>
</body>
</html>
Now in code behind we need to write the code as like shown below

C#.NET Code


using System;
using System.Data;
using System.IO;
using System.Web.UI;

public partial class ExportExceldatatoDatatable : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            gvDetails.DataSource = BindDatatable();
            gvDetails.DataBind();
        }
    }
    protected DataTable BindDatatable()
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("UserId", typeof(Int32));
        dt.Columns.Add("UserName", typeof(string));
        dt.Columns.Add("Education", typeof(string));
        dt.Columns.Add("Location", typeof(string));
        dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai");
        dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur");
        dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu");
        dt.Rows.Add(4, "Rohini", "MSC", "Chennai");
        dt.Rows.Add(5, "Mahendra", "CA", "Guntur");
        dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur");
        return dt;
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
    // Export data to Excel from Gridview
    protected void btnExport_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
        Response.ContentType = "application/ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gvDetails.AllowPaging = false;
        gvDetails.DataSource = BindDatatable();
        gvDetails.DataBind();
        //Change the Header Row back to white color
        gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
        //Applying stlye to gridview header cells
        for (int i = 0; i < gvDetails.HeaderRow.Cells.Count; i++)
        {
            gvDetails.HeaderRow.Cells[i].Style.Add("background-color", "#df5015");
        }
        gvDetails.RenderControl(htw);
        Response.Write(sw.ToString());
        Response.End();
    }
    //Export data to excel from datatable
    protected void btnExportTable_Click(object sender, EventArgs e)
    {
        Response.ClearContent();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", "Customers.xls"));
        Response.ContentType = "application/ms-excel";
        DataTable dt = BindDatatable();
        string str = string.Empty;
        foreach (DataColumn dtcol in dt.Columns)
        {
            Response.Write(str + dtcol.ColumnName);
            str = "\t";
        }
        Response.Write("\n");
        foreach (DataRow dr in dt.Rows)
        {
            str = "";
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                Response.Write(str + Convert.ToString(dr[j]));
                str = "\t";
            }
            Response.Write("\n");
        }
        Response.End();
    }
}
If you observe above code I added one function that is VerifyRenderingInServerForm this function is used to avoid the error like “control must be placed in inside of form tag”. If we set VerifyRenderingInServerForm function then compiler will think that controls rendered before exporting and our functionality will work perfectly.

VB.NET Code


Imports System.Data
Imports System.IO
Imports System.Web.UI

Partial Class _Default
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            gvDetails.DataSource = BindDatatable()
            gvDetails.DataBind()
        End If
    End Sub
    Protected Function BindDatatable() As DataTable
        Dim dt As New DataTable()
        dt.Columns.Add("UserId", GetType(Int32))
        dt.Columns.Add("UserName", GetType(String))
        dt.Columns.Add("Education", GetType(String))
        dt.Columns.Add("Location", GetType(String))
        dt.Rows.Add(1, "SureshDasari", "B.Tech", "Chennai")
        dt.Rows.Add(2, "MadhavSai", "MBA", "Nagpur")
        dt.Rows.Add(3, "MaheshDasari", "B.Tech", "Nuzividu")
        dt.Rows.Add(4, "Rohini", "MSC", "Chennai")
        dt.Rows.Add(5, "Mahendra", "CA", "Guntur")
        dt.Rows.Add(6, "Honey", "B.Tech", "Nagpur")
        Return dt
    End Function
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
        ' Verifies that the control is rendered

    End Sub
    ' Export data to Excel from Gridview
    Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As EventArgs)
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Customers.xls"))
        Response.ContentType = "application/ms-excel"
        Dim sw As New StringWriter()
        Dim htw As New HtmlTextWriter(sw)
        gvDetails.AllowPaging = False
        gvDetails.DataSource = BindDatatable()
        gvDetails.DataBind()
        'Change the Header Row back to white color
        gvDetails.HeaderRow.Style.Add("background-color", "#FFFFFF")
        'Applying stlye to gridview header cells
        For i As Integer = 0 To gvDetails.HeaderRow.Cells.Count - 1
            gvDetails.HeaderRow.Cells(i).Style.Add("background-color", "#df5015")
        Next
        gvDetails.RenderControl(htw)
        Response.Write(sw.ToString())
        Response.[End]()
    End Sub
    'Export data to excel from datatable
    Protected Sub btnExportTable_Click(ByVal sender As Object, ByVal e As EventArgs)
        Response.ClearContent()
        Response.Buffer = True
        Response.AddHeader("content-disposition", String.Format("attachment; filename={0}", "Customers.xls"))
        Response.ContentType = "application/ms-excel"
        Dim dt As DataTable = BindDatatable()
        Dim str As String = String.Empty
        For Each dtcol As DataColumn In dt.Columns
            Response.Write(str + dtcol.ColumnName)
            str = vbTab
        Next
        Response.Write(vbLf)
        For Each dr As DataRow In dt.Rows
            str = ""
            For j As Integer = 0 To dt.Columns.Count - 1
                Response.Write(str & Convert.ToString(dr(j)))
                str = vbTab
            Next
            Response.Write(vbLf)
        Next
        Response.[End]()
    End Sub
End Class
Demo

Export Data to Excel from Datatable or Gridview in Asp.net using c#, vb.net

Output

Export to excel from gridview or datatable in asp.net

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

8 comments :

Anonymous said...

How to export to Excel 2013 using windows form application? I am using Microsoft.Office.Interop.Excel

Anonymous said...

Well done....

It helps

Naveen said...

sir can you help me out for the cell to cell mapping of sql and excel sheet.? i have already a format of excel file and i would like to have fill data through mapping

nasir said...

Thank for your valuable source code its working
Can Any help in importing data from excel to sql server 2008 R2

Anonymous said...

There is not error, but i am not able to export the Xls file, Browser is not showing up to save or open the file.
No issue with browser because for other website it allows me to export.

Unknown said...

Sir can you tell me the code for how to create multi sheet excel dynamically

Unknown said...

On Response.End() Giving Excepetion Thread was being aborted

Unknown said...

How to give color/formatting for the header row when exporting from data table?

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.