Export GridView into Excel

Question

I'm trying to export my GridView into a 2010 Excel file .xlsx format. It works with an .xls I was wondering if anyone could help me out. Here is what I have so far:

protected void Button2_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition",
        "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        GridView1.AllowPaging = false;
        GridView1.DataBind();

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

        GridView1.HeaderRow.Cells[0].Style.Add("background-color", "#D1EEEE");

        GridView1.HeaderRow.Cells[1].Style.Add("background-color", "#D1EEEE");

        GridView1.HeaderRow.Cells[2].Style.Add("background-color", "#D1EEEE");

        GridView1.HeaderRow.Cells[3].Style.Add("background-color", "#D1EEEE");
        GridView1.HeaderRow.Cells[4].Style.Add("background-color", "#D1EEEE");
        GridView1.HeaderRow.Cells[5].Style.Add("background-color", "#D1EEEE");
        GridView1.HeaderRow.Cells[6].Style.Add("background-color", "#D1EEEE");
        GridView1.HeaderRow.Cells[7].Style.Add("background-color", "#D1EEEE");
        GridView1.HeaderRow.Cells[8].Style.Add("background-color", "#D1EEEE");
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {

            GridViewRow row = GridView1.Rows[i];
            row.BackColor = System.Drawing.Color.White;
            row.Attributes.Add("class", "textmode");
            if (i % 2 != 0)
            {

                row.Cells[0].Style.Add("background-color", "#FFFFFF");
                row.Cells[1].Style.Add("background-color", "#FFFFFF");
                row.Cells[2].Style.Add("background-color", "#FFFFFF");
                row.Cells[3].Style.Add("background-color", "#FFFFFF");

            }
        }
        GridView1.RenderControl(hw);
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();

    }

Answer 1

Please, do the following :

                protected void ExportExcel(object sender, EventArgs e)
                {
                    DataTable dt = new DataTable("GridView_Data");
                    foreach(TableCell cell in GridView1.HeaderRow.Cells)
                    {
                        dt.Columns.Add(cell.Text);
                    }
                    foreach (GridViewRow row in GridView1.Rows)
                    {
                        dt.Rows.Add();
                        for (int i=0; i<row.Cells.Count; i++)
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = row.Cells[i].Text;
                        }
                   }
                    using (XLWorkbook wb = new XLWorkbook())
                    {
                        wb.Worksheets.Add(dt);

                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                        Response.AddHeader("content-disposition", "attachment;filename=GridView.xlsx");
                        using (MemoryStream MyMemoryStream = new MemoryStream())
                        {
                            wb.SaveAs(MyMemoryStream);
                            MyMemoryStream.WriteTo(Response.OutputStream);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }

and these links show step by step how to do that:

http://www.aspsnippets.com/Articles/Solution-ASPNet-GridView-Export-to-Excel-The-file-you-are-trying-to-open-is-in-a-different-format-than-specified-by-the-file-extension.aspx

Answer 2

See below article, it uses nine methods to export data to excel on asp.net, it must help:

9 Solutions to Export Data to Excel for ASP.NET