gridview data export to excel in asp.net

Question

i tried to transfer grid view data to excel .... But the output is a blank excel sheet.How to solve this problem?Is there any code to transfer grid view value to excel sheet with data base?

protected void btnexcel_Click1(object sender, EventArgs e)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";

    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    gvdetails.AllowPaging = false;
    gvdetails.DataBind(); 
    gvdetails.HeaderRow.Style.Add("background-color", "#FFFFFF");
    gvdetails.HeaderRow.Cells[0].Style.Add("background-color", "green");
    gvdetails.HeaderRow.Cells[1].Style.Add("background-color", "green");
    gvdetails.HeaderRow.Cells[2].Style.Add("background-color", "green");
    for (int i = 0; i < gvdetails.Rows.Count;i++ )
    {
        GridViewRow row = gvdetails.Rows[i];
        row.BackColor = System.Drawing.Color.White;
        row.Attributes.Add("class", "textmode");
        if (i % 2 != 0)
        {
            row.Cells[0].Style.Add("background-color", "#C2D69B");
            row.Cells[1].Style.Add("background-color", "#C2D69B");
            row.Cells[2].Style.Add("background-color", "#C2D69B");
        }
    }

    string style = @"<style> .textmode { mso-number-format:\@; } </style>"; 
    Response.Write(style); 

    Response.Output.Write(sw.ToString());
    Response.End();
}

@Ratna : Excel sheet that is generating is blank. Please read the requirement correctly. — Pradip, Apr 05 '13 at 11:26
hope that you are aware that this is not excel export, you are serving html table and trick browser with wrong content type in response. There are numerous issues that you can get with this approach. http://stackoverflow.com/a/10245406/351383 — Antonio Bakula, Apr 05 '13 at 13:34
Almost all answer here write a (HtmlTextWriter) string or have Interop code. DO NOT USE EITHER. This will cause you problems later on with DateTime and Decimal formatting. Also Excel will give a warning because you are not generating a "real" Excel file but a HTML page with an .xls extension. Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus). [Example here](https://stackoverflow.com/a/47293207/5836671) and [here](https://stackoverflow.com/a/39513057/5836671). — VDWWD, Dec 21 '18 at 09:25

Ratna · Accepted Answer · 2013-04-05T12:11:47.607

13

Your sheet is blank because your string writer in null. Here is what may help

System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);

    GridView1.RenderControl(htmlWrite);

Here is the full code

protected void Button1_Click(object sender, EventArgs e)
{
    Response.Clear();

    Response.AddHeader("content-disposition", "attachment;
    filename=FileName.xls");


    Response.ContentType = "application/vnd.xls";

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

    System.Web.UI.HtmlTextWriter htmlWrite =
    new HtmlTextWriter(stringWrite);

    GridView1.RenderControl(htmlWrite);

    Response.Write(stringWrite.ToString());

    Response.End();

}

edited Apr 05 '13 at 12:11

answered Apr 05 '13 at 11:28

Ratna

2,289
3
26
50

1

What if it's a visual web part and there is no `form` control in the page? – SearchForKnowledge Jan 07 '15 at 15:11
How do I make this output to an attachment for an email? – Ortund Dec 11 '18 at 14:40

score 3 · Answer 2 · answered Apr 08 '13 at 13:13

may be problem in data binding in export excel . check that data properly bin to a gridview or not.

Use this code for export grid view in excel sheet and note that you must add iTextSharp dll in you project.

protected void btnExportExcel_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";

        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;

        // Re-Bind data to GridView 

        using (CompMSEntities1 CompObj = new CompMSEntities1())
        {
            Start = Convert.ToDateTime(txtStart.Text);
            End = Convert.ToDateTime(txtEnd.Text);

            GridViewSummaryReportCategory.DataSource = CompObj.SP_Category_Summary(Start, End);
            SP_Category_Summary_Result obj1 = new SP_Category_Summary_Result();
            GridView1.DataBind();
           GridView1.Visible = true;
            ExportTable.Visible = true;
        }

        //Change the Header Row back to white color

        GridView1.HeaderRow.Style.Add("background-color", "#FFFFFF");


       GridView1.Style.Add(" font-size", "10px");




        //Apply style to Individual Cells

        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "green");
        GGridView1.HeaderRow.Cells[1].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "green");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "green");

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

            //Change Color back to white

            row.BackColor = System.Drawing.Color.White;

            //Apply text style to each Row

        //    row.Attributes.Add("class", "textmode");

            //Apply style to Individual Cells of Alternating Row

            if (i % 2 != 0)
            {
                row.Cells[0].Style.Add("background-color", "#C2D69B");
                row.Cells[1].Style.Add("background-color", "#C2D69B");
                row.Cells[2].Style.Add("background-color", "#C2D69B");
                row.Cells[3].Style.Add("background-color", "#C2D69B");
                row.Cells[4].Style.Add("background-color", "#C2D69B");
            }
        }
        GridView1.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();
    }

score 3 · Answer 3 · answered Nov 18 '13 at 10:30

I think it will help you

string filename = String.Format("Results_{0}_{1}.xls", DateTime.Today.Month.ToString(), DateTime.Today.Year.ToString());
        if (!string.IsNullOrEmpty(GRIDVIEWNAME.Page.Title))
            filename = GRIDVIEWNAME.Page.Title + ".xls";

        HttpContext.Current.Response.Clear();

        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + filename);


        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.Charset = "";

        System.IO.StringWriter stringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);



        System.Web.UI.HtmlControls.HtmlForm form = new System.Web.UI.HtmlControls.HtmlForm();
        GRIDVIEWNAME.Parent.Controls.Add(form);
        form.Controls.Add(GRIDVIEWNAME);
        form.RenderControl(htmlWriter);

        HttpContext.Current.Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />");
        HttpContext.Current.Response.Write(stringWriter.ToString());
        HttpContext.Current.Response.End();

score 0 · Answer 4 · answered Apr 05 '13 at 11:31

Instead of doing all these.. cant you use a simpler approach as shown below.

Response.ClearContent();
            Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
            Response.ContentType = "application/excel";
            System.IO.StringWriter sw = new System.IO.StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            gv.RenderControl(htw);
            Response.Write(sw.ToString());
            Response.End();

You can get the entire walkthrough here

score 0 · Answer 5 · answered Apr 05 '13 at 12:21

0

Something else to check is make sure viewstate is on (I just solved this yesterday). If you don't have viewstate on, the gridview will be blank until you load it again.

answered Apr 05 '13 at 12:21

Jeff B

535
1
6
15

score 0 · Answer 6 · answered Jul 27 '15 at 20:09

The Best way is to use closedxml. Below is the link for reference

https://closedxml.codeplex.com/wikipage?title=Adding%20DataTable%20as%20Worksheet&referringTitle=Documentation

and you can simple use

    var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GeDataTable();//refer documentaion

wb.Worksheets.Add(dt);

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-disposition", "attachment;filename=\"FileName.xlsx\"");

using (var ms = new System.IO.MemoryStream()) {
    wb.SaveAs(ms);
    ms.WriteTo(Response.OutputStream);
    ms.Close();
}

Response.End();

Link is being closed, see: https://github.com/closedxml/closedxml (github location) — ransems, Jan 16 '20 at 21:36

score -1 · Answer 7 · answered Apr 05 '13 at 11:19

-1

I don't there there is any DataSource for the gridview
Though you have DataBind in your code as

gvdetails.DataBind();

answered Apr 05 '13 at 11:19

शेखर

17,412
13
61
117

I think DataBind is there, just below AllowPaging – Pradip Apr 05 '13 at 11:27
I am talking about `datasource`? – शेखर Apr 05 '13 at 11:30

gridview data export to excel in asp.net

7 Answers7

Linked