We help IT Professionals succeed at work.

Export Gridview to Excel in VB.net

566 Views
Last Modified: 2012-03-29
I have a gridview call gvfamilies on a page with a button called button1. I populate the gridview and I want to export the contents of the gridview to an excel file called test.xls
I have searched the internet and have found several examples of code but I can't seem to get any to work. Can you tell me what is wrong with the following code. I get an error message of
Microsoft JScript runtime error: Sys.WebForms.PageRequestManagerParserErrorException: The message received from the server could not be parsed. Common causes for this error are when the response is modified by calls to Response.Write(), response filters, HttpModules, or server trace is enabled.
Details: Error parsing near '<div>
      <table cells'.

Here is my code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Call ExportToExcel("test.xls", gvFamilies)
       
    End Sub
    Private Sub ExportToExcel(ByVal strFileName As String, ByVal dg As GridView)

        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=Demand_Report_" & Date.Today.ToString("MM-dd-yyyy") & ".xls")
        Response.Charset = ""
        Response.ContentType = "application/vnd.xls"
        Dim StringWriter As New System.IO.StringWriter()
        Dim HtmlTextWriter As New HtmlTextWriter(StringWriter)
        gvFamilies.RenderControl(HtmlTextWriter)
        Response.Write(StringWriter.ToString())    'This is where it errors
        Response.End()

    End Sub

    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
The button needs to be outside of an update panel.

Author

Commented:
I tried that and I get the same error

Commented:
Try adding Response.ClearHeaders(); above your Response.Clear() line to make sure there are no headers that have already been generated.

Author

Commented:
I added response.clearheaders but I get the same error. This is crazy.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Show us the gridview markup.

Author

Commented:
Here is the whole page

<%@ Page Language="VB" EnableEventValidation = "false" MasterPageFile="~/master pages/maintenance.master" AutoEventWireup="false" CodeFile="Tuition Credit 2.aspx.vb" Inherits="maintenance_Reports_Tuition_Credit_2008_2009" title="Tuition Credit" %>

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

<%@ Register assembly="CrystalDecisions.Web, Version=10.5.3700.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>
 



<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
    <asp:Button ID="Button1" runat="server" Text="Button"
                CausesValidation="False" />

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
 <ContentTemplate>
 
    <table style="width: 100%">
    <tr>
           
        <td style="text-align: center" colspan="3">
            <CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
                AutoDataBind="true" />
           
     
            <asp:Label ID="lblHeading" runat="server" CssClass="HeadingLabel"
                Text="Tuition Credit Reports"></asp:Label>
        </td>
    </tr>
    <tr>
        <td style="text-align: left; height: 26px;" colspan="3">
            <asp:Menu ID="MenuPrint" runat="server" BackColor="#B5C7DE" BorderColor="Black"
                BorderStyle="Outset" BorderWidth="2px" DynamicHorizontalOffset="3"
                DynamicVerticalOffset="3" Font-Names="Verdana" Font-Size="0.9em"
                ForeColor="#000066" MaximumDynamicDisplayLevels="4" Orientation="Horizontal"
                StaticSubMenuIndent="10px">
                <StaticSelectedStyle BackColor="#507CD1" />
                <StaticMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
                <DynamicHoverStyle BackColor="#284E98" BorderColor="Black" BorderStyle="Ridge"
                    BorderWidth="1px" ForeColor="Black" />
                <DynamicMenuStyle BackColor="#B5C7DE" BorderColor="Black" BorderStyle="Ridge"
                    BorderWidth="1px" />
                <DynamicSelectedStyle BackColor="#507CD1" />
                <DynamicMenuItemStyle BackColor="#B5C7DE" BorderColor="Black"
                    HorizontalPadding="5px" VerticalPadding="6px" />
                <StaticHoverStyle BackColor="#284E98" ForeColor="White" />
                <Items>
                    <asp:MenuItem Text="Create Report" Value="Create Report"></asp:MenuItem>
                    <asp:MenuItem Text="Print Report" Value="Print Report"></asp:MenuItem>
                </Items>
            </asp:Menu>
           
        </td>
    </tr>
        <tr>
            <td style="text-align: center" colspan="3">
                <asp:Label ID="Label2" runat="server" CssClass="LabelsSmall"
                    Text="Select School Year:"></asp:Label>
                <asp:DropDownList ID="ddlSchoolYear" runat="server" Width="200px">
                </asp:DropDownList>
            </td>
        </tr>
        <tr>
            <td style="text-align: center" colspan="3">
                <asp:Label ID="Label3" runat="server" CssClass="LabelsSmall"
                    ForeColor="#CC0000"
                    Text="*** Creating the report could take up to three minutes. ***"></asp:Label>
            </td>
        </tr>
        <tr>
            <td colspan="3" style="text-align: left; width: 400pt">
                &nbsp;</td>
        </tr>
        <tr>
            <td style="width: 30%">
                <asp:Label ID="lblTotScrip" runat="server" CssClass="LabelsSmaller"
                    Text="Total Scrip Purchased" Visible="False"></asp:Label>
            </td>
            <td style="width: 30%">
                <asp:Label ID="lblFundraising" runat="server" CssClass="LabelsSmaller"
                    Text="Total Fundraising" Visible="False"></asp:Label>
            </td>
            <td>
                <asp:Label ID="lblTotActiveFamilies" runat="server" CssClass="LabelsSmaller"
                    Text="Active Families" Visible="False"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="width: 20%">
                <asp:Label ID="lblTotMarketDay" runat="server" CssClass="LabelsSmaller"
                    Text="Total Market Day" Visible="False"></asp:Label>
            </td>
            <td>
                <asp:Label ID="lblTotFeesPaid" runat="server" CssClass="LabelsSmaller"
                    Text="Total Fundraising Fees Paid" Visible="False"></asp:Label>
            </td>
            <td>
                <asp:Label ID="lblTotOtherFamilies" runat="server" CssClass="LabelsSmaller"
                    Text="Other Families" Visible="False"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="width: 20%">
                <asp:Label ID="lblTotOther" runat="server" CssClass="LabelsSmaller"
                    Text="Total Other" Visible="False"></asp:Label>
            </td>
            <td>
                <asp:Label ID="lblTotDue" runat="server" CssClass="LabelsSmaller"
                    Text="Total Due" Visible="False"></asp:Label>
            </td>
            <td>
                <asp:Label ID="lblTotOrgs" runat="server" CssClass="LabelsSmaller"
                    Text="Total Organizations" Visible="False"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="width: 20%">
                <asp:Label ID="lblTotGoldScrip" runat="server" CssClass="LabelsSmaller"
                    Text="Total Gold Scrip" Visible="False"></asp:Label>
            </td>
            <td colspan="2">
                <asp:Label ID="lblTotDueOrgs" runat="server" CssClass="LabelsSmaller"
                    Text="Total Due To Orgs" Visible="False"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="width: 20%">
                <asp:Label ID="lblTotCredit" runat="server" CssClass="LabelsSmaller"
                    Text="Total Scrip Credit" Visible="False"></asp:Label>
            </td>
            <td colspan="2">
                <asp:Label ID="lblTotDueOtherFamilies" runat="server" CssClass="LabelsSmaller"
                    Text="Total Due Other Families" Visible="False"></asp:Label>
            </td>
        </tr>
        <tr>
            <td class="style1">
                <asp:Label ID="lblTotMdCredit" runat="server" CssClass="LabelsSmaller"
                    Text="Market Day Credit" Visible="False"></asp:Label>
            </td>
            <td colspan="2">
            </td>
        </tr>
        <tr>
            <td class="style1">
                <asp:Label ID="lblTotAllCredit" runat="server" CssClass="LabelsSmaller"
                    Text="Total Credit" Visible="False"></asp:Label>
            </td>
            <td colspan="2">
                &nbsp;</td>
        </tr>
        <tr>
            <td style="width: 20%">
                &nbsp;</td>
            <td colspan="2">
                &nbsp;</td>
        </tr>
    <tr>
        <td style="text-align: left; vertical-align: top" colspan="3">
            <asp:GridView ID="gvFamilies" runat="server" CellPadding="4"
                ForeColor="#333333" GridLines="None" AllowSorting="True"
                EnableSortingAndPagingCallbacks="True" >
                <FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <RowStyle BackColor="#E3EAEB" />
                <PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
                <SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
                <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
                <EditRowStyle BackColor="#7C6F57" />
                <AlternatingRowStyle BackColor="White" />
            </asp:GridView>
        </td>
    </tr>
    <tr>
        <td colspan="3">
            &nbsp;</td>
    </tr>
</table>
</ContentTemplate>

 </asp:UpdatePanel>
       
   
   
</asp:Content>

<asp:Content ID="Content2" runat="server" contentplaceholderid="head">
    <style type="text/css">
    .style1
    {
        width: 20%;
    }
</style>



</asp:Content>
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I removed the update panel. I get the same error.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
The error is usually seen when AJAX is used with response.write.

Remove the

Response.Charset = ""

Author

Commented:
I tried that but it didn't work.

Author

Commented:
I created a new page with very little on it. It does the same thing. Here is the simplified version:
<%@ Page Language="VB" MasterPageFile="~/Master Pages/Maintenance.master" AutoEventWireup="false" CodeFile="Tuition Credit 2.aspx.vb" Inherits="maintenance_Reports_Tuition_Credit_2" title="Untitled Page" %>

<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">

</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

  <div>
  <asp:GridView ID="GridView1" runat="server">
  </asp:GridView>
  </div>
  <br />
  <asp:Button ID="BtnExport" runat="server" OnClick="BtnExport_Click"
  Text="Export to Excel" />

</asp:Content>


Imports System
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Web.UI.WebControls.WebParts
Imports System.Text
Imports System.Windows.Forms
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.IO
Partial Class maintenance_Reports_Tuition_Credit_2
    Inherits System.Web.UI.Page
    Public dtWorktable As New DataTable
    Public NewRow As DataRow = dtWorktable.NewRow()
   
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If IsPostBack = False Then

            dtWorktable.Columns.Add("First Name", Type.GetType("System.String"))
            dtWorktable.Columns.Add("Last Name", Type.GetType("System.String"))
            NewRow = dtWorktable.NewRow
            With NewRow
                NewRow("First Name") = "Tom"
                NewRow("Last Name") = "Smith"
            End With
            dtWorktable.Rows.Add(NewRow)
            NewRow = dtWorktable.NewRow
            With NewRow
                NewRow("First Name") = "Tom"
                NewRow("Last Name") = "Jones"
            End With
            dtWorktable.Rows.Add(NewRow)
            GridView1.DataSource = dtWorktable
            GridView1.DataBind()


        End If
    End Sub
   

    Protected Sub BtnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnExport.Click
        Response.ClearHeaders()
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=Demand_Report_" & Date.Today.ToString("MM-dd-yyyy") & ".xls")
        'Response.Charset = ""
        'EnableEventValidation = False
        'EnableViewState = False
        Response.ContentType = "application/vnd.xls"


        Dim StringWriter As New System.IO.StringWriter()
        Dim HtmlTextWriter As New HtmlTextWriter(StringWriter)
        GridView1.RenderControl(HtmlTextWriter)
        Response.Write(StringWriter.ToString())
        Response.End()
    End Sub
    Public Overrides Sub VerifyRenderingInServerForm(ByVal control As System.Web.UI.Control)

    End Sub
End Class

Author

Commented:
If I remove the masterpage it works. Why would this be?

Commented:
If there is an updatepanel on your masterpage surrounding the <asp:contentplaceholder where your page resides, try removing it and see if that allows the export to work.

Author

Commented:
Thanks for the help but I never got it working

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.