Export a GridView to Excel - C# : The Official Microsoft ASP.NET Forums

Reply

njwriter08

Member

7 Points

Export a GridView to Excel - C#

May 01, 2008 04:35 PM|LINK

I need to export a grid view to Excel in a new window. I used code I obtained from aspnetlibrary.com, translating it into C#:

protected void btnExportToExcel_Click(object sender, ImageClickEventArgs e)
{

//Clear content of buffer stream

Response.Clear();

//Set the type and filename
Response.AddHeader("CSXEquipmentBreakdownReport", "CSXEquipmentBreakdownReport.xls");
Response.Charset = "iso-8859-2";
Response.ContentType = "application/vnd.xls";
// Add the HTML from the GridView to a StringWriter to write it out later
StringWriter sw = new StringWriter();HtmlTextWriter htw = new HtmlTextWriter(sw);
grdReport.RenderControl(htw);

//Write out the data

Response.Write(sw.ToString());

Response.End();

}

It builds successfully. Yet, when I run it and hit the Image button an exception is thrown: System.Web.HttpException: Control 'grdReport' of type 'GridView' must be placed inside a form tag with runat=server.

The line: grdReport.RenderControl(htw); is tagged as the culprit. The original gridview is in a form tag with runat=server.

What is my solution?

Moved by SGWellens on May 01, 2008 04:52 PM

Reply

allanhorwitz

Contributor

2517 Points

623 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 04:56 PM|LINK

Here is a working example:

GridViewExport.aspx:

<%@ Page Language="C#" AutoEventWireup="true" Codebehind="GridViewExport.aspx.cs"
    Inherits="ForForums.GridViewExport" %>


<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:GridView ID="CompanyListGridView" AutoGenerateColumns="false" CellPadding="3"
                CellSpacing="1" HeaderStyle-HorizontalAlign="Left" RowStyle-VerticalAlign="top"
                AlternatingRowStyle-VerticalAlign="top" RowStyle-BackColor="Lavender" AlternatingRowStyle-BackColor="LightBlue"
                HeaderStyle-BackColor="#006699" HeaderStyle-ForeColor="White" BorderStyle="None"
                BackColor="Black" GridLines="None" EmptyDataRowStyle-BackColor="Lavender" EmptyDataText="No records found."
                OnRowCreated="OnRowCreated" runat="server">
                <Columns>
                    <asp:BoundField DataField="CompanyName" ItemStyle-Width="300px" />
                </Columns>
            </asp:GridView>
            <br />
            <br />
            <asp:Button ID="Button1" runat="server" Text="Export" OnClick="Button1_Click" />
        </div>
    </form>
</body>
</html>

GridViewExport.aspx.cs:

 using System;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace ForForums
{
    public partial class GridViewExport : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Database db = DatabaseFactory.CreateDatabase();
                LoadCompanyList(db);
            }

        }

        protected void LoadCompanyList(Database db)
        {
            CompanyListGridView.DataSource = CompanyManager.GetCompanies(db);
            CompanyListGridView.DataBind();
        }

        protected void OnRowCreated(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.Header)
                foreach (TableCell headerCell in e.Row.Cells)
                    headerCell.Text = "Company Name";
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            GridViewExportUtil.Export("Companies.xls", this.CompanyListGridView);
        }
    }
}

And a class file, GridViewExport.cs (Thank you Matt Berseth):

using System;
using System.Data;
using System.Configuration;
using System.IO;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;


public class GridViewExportUtil
{

    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //  render the htmlwriter into the response
                HttpContext.Current.Response.Write(sw.ToString());
                HttpContext.Current.Response.End();
            }
        }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
        for (int i = 0; i < control.Controls.Count; i++)
        {
            Control current = control.Controls[i];
            if (current is LinkButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
            }
            else if (current is ImageButton)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
            }
            else if (current is HyperLink)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
            }
            else if (current is DropDownList)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
            }
            else if (current is CheckBox)
            {
                control.Controls.Remove(current);
                control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
            }

            if (current.HasControls())
            {
                GridViewExportUtil.PrepareControlForExport(current);
            }
        }
    }
}

Allan Horwitz

Edited by allanhorwitz on May 01, 2008 04:58 PM
Marked as answer by njwriter08 on May 01, 2008 08:01 PM

Reply

novicehere

Contributor

4654 Points

854 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 04:58 PM|LINK

Hey Hi,

you have override the rendercontrol like this

in C #

public override void VerifyRenderingInServerForm(Control control)

{

}

in Vb

Public Overloads Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)

End Sub

and also add AutoEventWireup="false" in aspx page like this

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="export.aspx.vb" Inherits="export" %>

hope this helps

Thanks CVM

Keyboard not found. Please Press < F1 > to RESUME

Please Remember to Mark as Answer for the post(s) that help you.....so it can help others......Thanks

Edited by novicehere on May 01, 2008 05:00 PM

Reply

njwriter08

Member

7 Points

32 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:11 PM|LINK

HI,

AutoEventWireup="false"

&

public override void VerifyRenderingInServerForm(GridView grdReport)

{

}

gets this build failed message: "...no suitable method found to override." I got the same messge with AutoEventWireup="true".

Reply

novicehere

Contributor

4654 Points

854 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:14 PM|LINK

Hey Please use the code as posted you dont have to metion GridView grdReport

just use

public override void VerifyRenderingInServerForm(Control control)

{

}

just use as posted , it will work then

Thanks

Keyboard not found. Please Press < F1 > to RESUME

Please Remember to Mark as Answer for the post(s) that help you.....so it can help others......Thanks

Reply

allanhorwitz

Contributor

2517 Points

623 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:16 PM|LINK

njwriter08
public override void VerifyRenderingInServerForm(GridView grdReport)
{

}

Should be:

public override void VerifyRenderingInServerForm(Control control)
        {
        }

I never needed to change the AutoEventWireup value:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ExcelExport.aspx.cs" Inherits="ForForums.ExcelExport" %>

Also, make sure you are allowing the client to cache the page.

Did you try the code in my earlier post?

Allan Horwitz

Edited by allanhorwitz on May 01, 2008 06:17 PM

Reply

njwriter08

Member

7 Points

32 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:24 PM|LINK

The AutoEventWireup property has to be set to "true" on the original page whertein is the gridview. There for of what page do you mean for "false"?

Reply

njwriter08

Member

7 Points

32 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:32 PM|LINK

Ok: the

public override void VerifyRenderingInServerForm(Control control)
{
}

is empty; refers to no control.

puting it in that way gets a successful build but gets this once the imagebutton control is hit:

System.InvalidOperationException: RegisterForEventValidation can only be called during Render();

Reply

allanhorwitz

Contributor

2517 Points

623 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:36 PM|LINK

njwriter08

Ok: the

public override void VerifyRenderingInServerForm(Control control)
{
}

is empty; refers to no control.

puting it in that way gets a successful build but gets this once the imagebutton control is hit:

System.InvalidOperationException: RegisterForEventValidation can only be called during Render();

If I was you, I would stop playing around and use the code in my first post. It is working for many people, just see this programmers blog:

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

Allan Horwitz

Reply

novicehere

Contributor

4654 Points

854 Posts

Re: Export a GridView to Excel - C#

May 01, 2008 06:37 PM|LINK

Hey There,

in regards to AutoEventWireUp.

this is taken from the MSDN library:

the ASP.NET page framework also supports an automatic way to associate page events and methods. If the AutoEventWireup attribute of the Page directive is set to true (or if it is missing, since by default it is true), the page framework calls page events automatically, specifically the Page_Init and Page_Load methods. In that case, no explicit Handles clause or delegate is needed.

The disadvantage of the AutoEventWireup attribute is that it requires that the page event handlers have specific, predictable names. This limits your flexibility in how you name your event handlers. Therefore, in Visual Studio, the AutoEventWireup attribute is set to false by default and the designer generates explicit code to bind page events to methods.

If you do set AutoEventWireup to true, Visual Studio will generate code to bind the events and the page framework will automatically call events based on their names. This can result in the same event code being called twice when the page runs. As a consequence, you should always leave AutoEventWireup set to false when working in Visual Studio.

i hope i am clearer now.

Keyboard not found. Please Press < F1 > to RESUME

Please Remember to Mark as Answer for the post(s) that help you.....so it can help others......Thanks

Export a GridView to Excel - C# RSS

30 replies

njwriter08

Export a GridView to Excel - C#

allanhorwitz

Re: Export a GridView to Excel - C#

novicehere

Re: Export a GridView to Excel - C#

njwriter08

Re: Export a GridView to Excel - C#

novicehere

Re: Export a GridView to Excel - C#

allanhorwitz

Re: Export a GridView to Excel - C#

njwriter08

njwriter08

Re: Export a GridView to Excel - C#

njwriter08

Re: Export a GridView to Excel - C#

allanhorwitz

Re: Export a GridView to Excel - C#

njwriter08

novicehere

Re: Export a GridView to Excel - C#

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft