1

Export to excel in .xls is working but export to .xlsx is not working after change content type- My code is below:

private void ExportToExcel()
{
    try
    {
        Response.Clear();
        Response.Buffer = true;

        //Response.AddHeader("content-disposition", "attachment;filename=LoanDataDeletion.xls");
        //Response.Charset = "";
       // Response.ContentType = "application/vnd.ms-excel";

        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.Charset = "";
        Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", "LoanDataDeletion.xlsx"));

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

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

        //Change the Header Row back to white color
        grdView.HeaderRow.Style.Add("background-color", "#FFFFFF");

        //Apply style to Individual Cells
        for (int i = 0; i < grdView.Columns.Count; i++)
        {
            grdView.HeaderRow.Cells[i].Style.Add("background-color", "green");
        }
        for (int i = 0; i < grdView.Rows.Count; i++)
        {
            GridViewRow row = grdView.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");
            }
        }
        grdView.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 (Exception)
    {
        throw;
    }
}
Colm Prunty
  • 1,595
  • 1
  • 11
  • 29
user2987108
  • 11
  • 1
  • 2

2 Answers2

2

I suppose the error you get is “The file you are trying to open is in a different format than specified by the file extension”. This happens because in the traditional Export to Excel method, the GridView is first converted to an HTML string and then that HTML string is exported to Excel, but Excel 2007/2010 cannot recognize the pure html format. There is a way to do it without using HtmlTextWriter, with EPPlus. It allows you to create Excel spreadsheets on the server. Check this posts: Create Excel workbook in asp.net or this one: Export Gridview data to Excel (.xlsx) without using HtmlTextWriter in Asp.NET

Sudarsh
  • 380
  • 4
  • 19
Flavia Obreja
  • 1,227
  • 7
  • 13
-1
            string path = System.IO.Path.GetTempPath() + System.DateTime.Now.Ticks + ".xls";
            GridView grdTemp = new GridView();
            grdTemp.DataSource = dt;
            grdTemp.Caption = "Products<br>  " + DateTime.Now;
            grdTemp.DataBind();

            using (StreamWriter sw = new StreamWriter(path))
            {
                using (HtmlTextWriter hw = new HtmlTextWriter(sw))
                {
                    grdTemp.RenderControl(hw);

                }

            }
            string save_path = path;
            Response.Clear();
            Response.ClearContent();
            Response.ClearHeaders();
            Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", System.DateTime.Now.Ticks + ".xls"));
            Response.ContentType = "application/excel";
            Response.WriteFile(save_path);
            Response.End();
            System.IO.File.Delete(save_path);
ALi
  • 1