Vishwamohan

Welcome to Vishwa's blog - Technology, Spirituality and More...

Export GridView Data to MS-Excel

29. December 2006 Vishwa .NET (18)

In the following example, I will demonstrate how to export GridView Data into MS-Excel using VB.NET. It uses the same business object to select data from database. I have simply formatted the GridView control from previous example.

Page Name: ExportGridViewToExcel.Aspx

------------------------------------

Note: You will be required to add - EnableEventValidation = "False" at Page Directive in order to Export to work.

-------------------------------------

Code Snippet
  1. <%@ Page Language="VB" AutoEventWireup="false" EnableEventValidation = "False" CodeFile="ExportGridViewToExcel.aspx.vb"
  2.         Inherits="ExportGridViewToExcel" %>
  3.  
  4. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
  5.  
  6. <html xmlns="http://www.w3.org/1999/xhtml" >
  7. <head runat="server">
  8.     <title>Export Customers Data</title>
  9. </head>
  10. <body>
  11.     <form id="frmCustomer" runat="server">
  12.     <div  style="text-align:center">   
  13.         <asp:Button ID="btnExportToExcel" runat="server" Text="Export to Excel" /><br />  
  14.         <asp:Label ID="lblMessage" runat="server" Text=""></asp:Label> <br />
  15.         <asp:GridView ID="gdvCustomer" DataKeyNames="CustID" runat="server" AllowPaging="True" DataSourceID="odsCustomer"  
  16.             AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE" BorderStyle="None"
  17.             BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Vertical"
  18.             RowStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left">
  19.             <Columns>                
  20.                 <asp:BoundField DataField="CustID" HeaderText="Cust ID"  />                                    
  21.                 <asp:BoundField DataField="CustName" HeaderText="Name" SortExpression="CustName" />                    
  22.                 <asp:BoundField DataField="CustAddress" HeaderText="Address" SortExpression="CustAddress"/>                
  23.             </Columns>
  24.             <EmptyDataTemplate>                
  25.                 No Customer Record Found.                
  26.             </EmptyDataTemplate>            
  27.             <FooterStyle BackColor="#CCCC99" />
  28.             <RowStyle BackColor="#F7F7DE" />
  29.             <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
  30.             <PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
  31.             <HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
  32.             <AlternatingRowStyle BackColor="White" />
  33.         </asp:GridView>
  34.                
  35.           <asp:ObjectDataSource ID="odsCustomer" runat="server"
  36.               SelectMethod="GetCustomers"
  37.             TypeName="Vishwa.Example.Business.CustomerFac" >
  38.          </asp:ObjectDataSource>
  39.         </div>
  40.     </form>
  41. </body>
  42. </html>
 

Code Behind : ExportGridViewToExcel.Aspx.vb

Code Snippet
  1. Option Explicit On
  2. Option Strict On
  3. Imports System.IO
  4. ' Author : Vishwa Mohan
  5. ' Date : 12/15/2006
  6. ' Class : ExportCustomer
  7. ' Purpose: To Export Customers Record into Excel
  8. Partial Class ExportGridViewToExcel
  9.     Inherits System.Web.UI.Page
  10.     Protected Sub btnExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExportToExcel.Click
  11.         If gdvCustomer.Rows.Count + 1 < 65536 Then
  12.             Me.lblMessage.Text = ""
  13.             Me.gdvCustomer.AllowPaging = False
  14.             'If you are binding GridView under Code Behind
  15.             'Me.gdvCustomer.DataSource = GetCustomer
  16.  
  17.             Me.gdvCustomer.DataBind()
  18.             Dim tw As New StringWriter()
  19.             Dim hw As New System.Web.UI.HtmlTextWriter(tw)
  20.             Dim frm As HtmlForm = New HtmlForm()
  21.             Page.Response.ContentType = "application/vnd.ms-excel"
  22.             Page.Response.AddHeader("content-disposition", "attachment;Customer.xls")
  23.             Page.Response.Charset = ""
  24.             Page.EnableViewState = False
  25.             frm.Attributes("runat") = "server"
  26.             Controls.Add(frm)
  27.  
  28.             frm.Controls.Add(gdvCustomer)
  29.             frm.RenderControl(hw)
  30.             Response.Write(tw.ToString())
  31.             Response.End()
  32.             Me.gdvCustomer.AllowPaging = True
  33.  
  34.             'If you are binding GridView under Code Behind
  35.             'Me.gdvCustomer.DataBind()
  36.         Else
  37.             Me.lblMessage.Text = "Too many rows - Export to Excel not possible"
  38.         End If
  39.     End Sub
  40. End Class

 

Page at the run Time

Tags :

Comments (18) -

  • nhurst

    12/19/2006 1:37:00 AM | Reply

    This code is awesome! Thank you so much for posting it!

  • Phil

    3/25/2007 2:52:10 AM | Reply

    I'm new to .NET and Web forms, it's a steep learning curve - this code has been a good lesson to me;
    Did you know the code may be incorrect;
    "If gdvCustomer.Rows.Count + 1 > 65536" should be
    "If gdvCustomer.Rows.Count + 1 < 65536....
    Many thanks for sharing this it...
    Phil

  • vishwa

    3/29/2007 8:01:02 AM | Reply

    Phil, Thanks for finding the mistake. I appreciate your feedback and fixed it.

  • Rohit

    8/9/2007 12:37:16 PM | Reply

    Hi
      Thanks for sharing this code
      But I am getting one Problem in that
      after opening the file in Excel its showing Html Tag also
       can you help me solve the problem
    Thanks
    Rohit

  • yatish

    10/24/2007 5:42:12 PM | Reply

    Hi this code is not working correctly in mozilla, it saves file as aspx page not xls please help

  • Vishwa

    1/22/2008 2:33:32 AM | Reply

    I tested in FireFox 2.0 and it worked, please make sure that you have right code.

  • PHS

    2/11/2008 1:15:59 PM | Reply

    Hi, Thanks for the code..
    Getting Runtime error message..

    "RegisterForEventValidation can only be called during Render();"

    Can yu please help me out..
    Thanks..

  • Vishwa

    2/11/2008 8:34:41 PM | Reply

    Please send me the code so that I can see what actually you are doing because you will need to  add - EnableEventValidation = "False" at Page Directive in order to Export to work.

  • Sim

    2/18/2008 1:20:10 PM | Reply

    do u mind upload the source for demo. i paste your code but i end up saving as an aspx page instead of a excel file

  • Sim

    2/18/2008 1:25:50 PM | Reply

    this line give me error frm.RenderControl(hw)
    error msg is "RegisterForEventValidation can only be called during Render();"
    how to i solve it?

  • GH

    3/13/2008 1:27:28 AM | Reply

    Thanks!! Your code is working great!

  • Sam

    8/2/2008 7:50:50 AM | Reply

    Vishwa,

    Your code works great...but I am running into a problem. My Gridview has a column with numbers starting with "0" (zero's), everytime I export my gridview to excel, I loose all the leading zero's ("0"). Is there a way I can preserve the leading zero's????


    Thanks in advance

    Sam

  • Vishwa

    8/3/2008 12:22:57 AM | Reply

    Hi Sam, Trimming leading zeros is default behavior of Excel file. I saw the following article, try if that makes sense and you can do it easily before binding to Gridview.
    www.pcreview.co.uk/forums/thread-1794670.php

  • MG

    9/24/2008 6:27:32 PM | Reply

    Im having probs with the stringwriter anyone knows y

  • skurra

    10/14/2008 11:27:07 PM | Reply

    Hi Viswa,

    I am getting following
    The Controls collection cannot be modified because the control contains code blocks (i.e. <% ... %>).

  • Kathleen

    3/25/2009 9:28:48 PM | Reply

    It has been working fine except now I want to move the script to a SharePoint server and cannot get it to work.

    I get the following error. missing file:

    c:\_layouts\1033\core.css C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033\STYLES\core.css



    The file does exist on the SharePoint server.



    Help.

  • Jefferson Litecky

    7/4/2010 3:03:31 AM | Reply

    I saw something about that on television yesterday. Thanks for explaining it more thoroughly

Add comment

Loading