CODEWITHSUNDEEP
c#asp.net.net
sterix24
sterix24

Reputation: 2400

Can't seem to export my gridview to an excel file

Basically I have a webcontrol that contains a gridview with an export button. When this export button is clicked I want to basically convert the gridview into an .xls file so I can view the data in excel.

I've actually managed to get everything to work using GridView.RenderControl(); The problem is that the entire usercontrol's data seems to be saved to this excel file (including the button/images/headings etc). This isn't want I want. I only want to render the GridView data and possibly the heading.

Is there any way I can choose what information gets rendered? It seems bizarre that calling a function on a single control causes all the controls to be rendered...

Anyway here is my export button code:

 protected void btnExport_Click(object sender, EventArgs e)
        {
            try
            {
                // Exports as excel spreadsheet
                Response.Clear();
                Response.Buffer = true;

                Response.AddHeader("content-disposition",
                "attachment;filename=registered_subscribers_" + System.DateTime.Now.ToShortDateString().Replace("/", "") + ".xls");
                Response.Charset = "";
                Response.ContentType = "application/vnd.ms-excel";
                StringWriter sw = new StringWriter();
                HtmlTextWriter hw = new HtmlTextWriter(sw);                

                for (int i = 0; i < gvInterest.Rows.Count; i++)
                {
                    GridViewRow row = gvInterest.Rows[i];

                    //Apply text style to each Row
                    row.Attributes.Add("class", "textmode");
                }

                gvInterest.RenderControl(hw);

                //style to format numbers to string
                string style = @"<style> .textmode { mso-number-format:\@; } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }
            catch
            {
                // Handle error
            }
        }

and here's my front end:

 <%@ Control Language="C#" AutoEventWireup="true" CodeBehind="ViewSubscribers.ascx.cs"
        Inherits="MyCode.ViewSubscribers" %>
    <div id="subscribers">
        <asp:Button ID="btnExport" runat="server" Text="Export" 
            onclick="btnExport_Click" />    
        <h2>
            Pre-registered subscribers for tickets</h2>
        <asp:GridView ID="gvInterest" runat="server" AutoGenerateColumns="false">
            <HeaderStyle CssClass="gv-header" ForeColor="#ffffff" BackColor="#333333" />
            <RowStyle BackColor="White" ForeColor="#333333" />
            <AlternatingRowStyle BackColor="#e6e6e6" ForeColor="#333333" />
            <Columns>
                <asp:BoundField DataField="Title" HeaderText="Title" />
                <asp:BoundField DataField="FirstName" HeaderText="First Name" />
                <asp:BoundField DataField="LastName" HeaderText="Last Name" />
                <asp:BoundField DataField="HouseNumber" HeaderText="House Name/Number" />
                <asp:BoundField DataField="Address1" HeaderText="Address 1" />
                <asp:BoundField DataField="Address2" HeaderText="Address 2" />
                <asp:BoundField DataField="Postcode" HeaderText="Post code" />
                <asp:BoundField DataField="Country" HeaderText="Country" />
                <asp:BoundField DataField="Phone" HeaderText="Phone" />
                <asp:BoundField DataField="Email" HeaderText="Email" />
                <asp:BoundField DataField="Comments" HeaderText="Comments" />
                <asp:BoundField DataField="DateCreated" HeaderText="Date Created" />
            </Columns>
            <EmptyDataTemplate>
                There are currently no subscribers
            </EmptyDataTemplate>
        </asp:GridView>      
    </div>

Anyone able to help?

Thanks!

Upvotes: 1

Views: 3658

Answers (3)

Cosmin
Cosmin

Reputation: 2385

The method I use is to export the data as a string with tab-separated values.

Upvotes: 0

rs.
rs.

Reputation: 27427

Check this post here http://www.c-sharpcorner.com/UploadFile/DipalChoksi/ExportASPNetDataGridToExcel11222005041447AM/ExportASPNetDataGridToExcel.aspx

You can use clearcontrols function to remove any html tags/controls/images etc before rendering them

Another link - http://csharpdotnetfreak.blogspot.com/2011/10/export-gridview-to-excel.html

check changecontroltovalue function

Upvotes: 3

brian chandley
brian chandley

Reputation: 1276

RenderControl is recursive, and there is no way that I know of to prevent it from rendering child controls/executing the gridview events you have implemented.

It is not really clear from your code where and how the images/markup that you do not want added top the export are being added. I have used 2 approaches to address similar issues:

1) Maintain a separate grid for exporting. render it instead of your display grid on button click.

2) Maintain an Property on your page that indicates a Mode. Skip any code rendering images/any other markup. You would set it to Export on the export button's click and look for that setting in your event handling.

Neither is a perfect solution, It is really based on the amount of coded you have handling to display, and teh amopunt of formatting control you need. You clutter your aspx with a grid, or you clutter up your code behind with if statements based on this mode.

Upvotes: 0

Related Questions