How to export excel from gridview?

Question

1.00/5 (2 votes)

See more:

Hi.....I have a data in nested gridview like when clicking '+' it will expand...the same way i want to export the excel from grid..

If I'm clicking '+' i want to see details in excel also..

Can any one help for this...

What I have tried:

public override void VerifyRenderingInServerForm(Control control)
      {
          //required to avoid the run time error "
          //Control 'GridView1' of type 'Grid View' must be placed inside a form tag with runat=server."
      }

protected void BtnexportExcel(object sender, EventArgs e)
        {
            Response.Clear();
            Response.Buffer = true;
            Response.ClearContent();
            Response.ClearHeaders();
            Response.Charset = "";
            string FileName = "Report" + DateTime.Now + ".xls";
            StringWriter strwritter = new StringWriter();
            HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", "attachment;filename=" + FileName);
            GvDetailPrimary.GridLines = GridLines.Both;
            GvDetailPrimary.HeaderStyle.Font.Bold = true;
            GvDetailPrimary.RenderControl(htmltextwrtter);
            Response.Write(strwritter.ToString());
            Response.End();

        }

Posted 27-Feb-17 22:15pm

Developer29

Updated 2-Mar-17 0:57am

v3

Add a Solution

Comments

Harshal Kale 28-Feb-17 6:00am

http://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-excel/

CHill60 28-Feb-17 8:34am

You could post that as a solution

Harshal Kale 28-Feb-17 8:38am

Thanks

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Harshal Kale · Answer 1 · 2017-02-28T02:41:00

Solution 3

Export Gridview to Excel Using ASP.Net C#[^]

Posted 28-Feb-17 2:41am

Harshal Kale

Updated 28-Feb-17 2:42am

v2

Comments

CHill60 28-Feb-17 8:43am

5'd (and I fixed the link for you)

Developer29 1-Mar-17 2:08am

Hi.....The code doesn't show any errors at the same time its not exporting files..

CHill60 1-Mar-17 11:23am

You didn't post any code! Have you tried debugging. Or use the Improve question link to add the code you are using to your question

Developer29 1-Mar-17 22:58pm

yeah I tried debugging also....I posted the code.....and i added EnableEventValidation="false" in <%@page register

CHill60 2-Mar-17 6:58am

I've verified that the code on that link isn't working - it may just be out of date and we're using a newer version. I've posted an alternative solution

CHill60 · Answer 2 · 2017-03-02T00:57:00

OP has commented that the example in the link from Solution 1 doesn't work. I have confirmed this. Excel loads but the data from the grid is not exported.

I can't be bothered to work out why that version is not working so here is an alternative
Export GridView to Excel in ASP.Net with Formatting using C# and VB.Net[^]
(I've verified that this code works, but I had to manually add the OnPageIndexChanging event - not sure why)

Here is my full code that worked:

ASP.NET

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

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Test</title>
</head>
<body>

<form runat="server" id="form1">
    <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
        RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
        runat="server" AutoGenerateColumns="false" AllowPaging="true" OnPageIndexChanging="GridView1_PageIndexChanging">
        <Columns>
            <asp:BoundField DataField="ContactName" HeaderText="Contact Name" ItemStyle-Width="150px" />
            <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="100px" />
            <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="100px" />
        </Columns>
    </asp:GridView>

    <br />
    <asp:Button ID="btnExport" runat="server" Text="Export To Excel" OnClick = "Button1_Click" />
    
</form>
</body>
</html>

C#

using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication4
{
    public partial class Default : Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                BindGrid();
            }
        }
        public override void VerifyRenderingInServerForm(Control control)  
        {  
             //required to avoid the run time error "  
             //Control 'GridView1' of type 'Grid View' must be placed inside a form tag with runat=server."  
        }  
        private void ExportGridToExcel()  
        {
            Response.Clear();
            Response.Buffer = true;
            Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";

            using (var sw = new StringWriter())
            {
                var hw = new HtmlTextWriter(sw);

                //To Export all pages
                GridView1.AllowPaging = false;

                BindGrid();

                GridView1.HeaderRow.BackColor = Color.White;

                foreach (TableCell cell in GridView1.HeaderRow.Cells)
                {
                    cell.BackColor = GridView1.HeaderStyle.BackColor;
                }

                foreach (GridViewRow row in GridView1.Rows)
                {
                    row.BackColor = Color.White;

                    foreach (TableCell cell in row.Cells)
                    {
                        cell.BackColor = row.RowIndex % 2 == 0 ? GridView1.AlternatingRowStyle.BackColor : GridView1.RowStyle.BackColor;
                        cell.CssClass = "textmode";
                    }
                }

                GridView1.RenderControl(hw);

                //style to format numbers to string
                const string style = @"<style> .textmode { } </style>";
                Response.Write(style);
                Response.Output.Write(sw.ToString());
                Response.Flush();
                Response.End();
            }   
        }  
        private void BindGrid()
        {
            var constr = ConfigurationManager.ConnectionStrings["ConnectToDB"].ConnectionString;

            using (var con = new SqlConnection(constr))
            {
                using (var cmd = new SqlCommand("SELECT CustomerId, ContactName, City, Country FROM Customers"))
                {
                    using (var sda = new SqlDataAdapter())
                    {
                        cmd.Connection = con;
                        sda.SelectCommand = cmd;
                        using (var dt = new DataTable())
                        {
                            sda.Fill(dt);
                            GridView1.DataSource = dt;
                            GridView1.DataBind();
                        }
                    }
                }
            }
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            ExportGridToExcel();  
        }
        protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
        {
            GridView1.PageIndex = e.NewPageIndex;
            BindGrid();
        }
    }
}