CODEWITHSUNDEEP
c#asp.netgridview
r-magalhaes
r-magalhaes

Reputation: 458

Problems on export data from GridView to Excel

I'm try export my gridView to excel using the next source:

protected void btn_ExportExcel_Click(object sender, EventArgs e)
    {
        try
        {
           string filename = String.Format("Results_{0}_{1}.xls", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());
            if (!string.IsNullOrEmpty(grid_dadosEntidades.Page.Title))
                filename = grid_dadosEntidades.Page.Title + ".xls";
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=" + filename);
            Response.ContentType = "application/vnd.xls";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            grid_dadosEntidades.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();
            HttpContext.Current.ApplicationInstance.CompleteRequest();

        }
        catch (Exception ex)
        {
            throw;
        }
    }

    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }

I'm using framework 4.0, and my GridView is:

<div class="TableBlock1">
                <asp:GridView ID="grid_dadosEntidades" runat="server" AllowPaging="True" AllowSorting="True"
                    AutoGenerateColumns="False" DataKeyNames="CodSeguradora_PK" OnPageIndexChanging="grid_dadosEntidades_PageIndexChanging"
                    OnSorting="grid_dadosEntidades_Sorting" OnRowCommand="grid_dadosEntidades_RowCommand"
                    OnRowDataBound="grid_dadosEntidades_RowDataBound" Width="820px" ViewStateMode="Enabled"
                    EmptyDataText="Não existem registos a mostrar." ShowHeaderWhenEmpty="True">
                    <Columns>
                        <asp:TemplateField HeaderStyle-CssClass="hidden" ItemStyle-CssClass="hidden">
                            <ItemTemplate>
                                <asp:Button runat="server" ID="SelectButton" CommandName="Select" />
                            </ItemTemplate>
                            <HeaderStyle CssClass="hidden" />
                            <ItemStyle CssClass="hidden" />
                        </asp:TemplateField>
                        <asp:TemplateField ShowHeader="False">
                            <ItemTemplate>
                                <asp:RadioButton ID="rb_Grid" runat="server" />
                            </ItemTemplate>
                        </asp:TemplateField>
                        <asp:BoundField DataField="CodEstatistico" HeaderText="Código" ReadOnly="True" SortExpression="CodEstatistico" />
                        <asp:BoundField DataField="NomSocial" HeaderText="Denominação Social" ReadOnly="True"
                            SortExpression="NomSocial" />
                        <asp:BoundField DataField="DtInicioActividade" HeaderText="Início Atividade" SortExpression="DtInicioActividade"
                            DataFormatString="{0:dd/MM/yyyy}" ItemStyle-Wrap="false" />
                        <asp:BoundField DataField="DtEncerramento" HeaderText="Encerramento" SortExpression="DtEncerramento"
                            DataFormatString="{0:dd/MM/yyyy}" ItemStyle-Wrap="false" />
                    </Columns>
                    <SortedAscendingHeaderStyle CssClass="sortasc" />
                    <SortedDescendingHeaderStyle CssClass="sortdesc" />
                </asp:GridView>
            </div>

The problem is, when i try open the excel file only show some data from grid, because i have OnPageIndexChanging on my Grid View

Upvotes: 0

Views: 518

Answers (3)

Hitesh
Hitesh

Reputation: 3498

Try out this link

There its mentioned to turn off paging for your grid and then export.

GridView1.AllowPaging = false;
GridView1.DataBind();

Then it will give you all data.

Upvotes: 1

user2347528
user2347528

Reputation: 610

This code worked for me. Please try.

var grid = new System.Web.UI.WebControls.GridView();
IQueryable<Customer> result = _db.Customers;
var dataToExport = from b in result.AsEnumerable()
                               select new
                                   {                                       
                                       b.CustomerContactName,
                                       b.CustomerContactNo,
                                       b.CustomerContactEmail
                                   };
grid.DataSource = dataToExport.ToList();
grid.DataBind();

Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=FileName.xls");
Response.ContentType = "application/excel";
var sw = new StringWriter();
var htw = new HtmlTextWriter(sw);

grid.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

Upvotes: 0

Bura Chuhadar
Bura Chuhadar

Reputation: 3751

I can suggest you at least downloading Open XML SDK 2.0 Productivity Tool. I can show you errors by clicking the Validate button:

Validate

Open XML SDK tool:

http://www.microsoft.com/en-us/download/details.aspx?id=5124

Upvotes: 1

Related Questions