Export Asp.net Gridview to Excel error message

Question

I am exporting a GridView to excel by using RenderControl Method below is some part of the code.

gridExcel.HeaderRow.BackColor = System.Drawing.Color.FromArgb(0, 255, 255, 204);
gridExcel.HeaderRow.Font.Bold = false;
gridExcel.HeaderRow.Height = Unit.Pixel(30);

StringWriter sw = new StringWriter();
gridExcel.RenderControl(new HtmlTextWriter(sw));

string renderedGridView = sw.ToString();

//filename will be like xyz.xls

System.IO.File.WriteAllText(@filename, renderedGridView);

It successfully creates excel document but when this document gets downloaded from website and user opens it from downloads folder , every time s\he gets below mentioned error but user is able to open the document.

Can anyone please suggest what can be done to avoid this issue.

score 1 · Accepted Answer · edited May 23 '17 at 10:28

Cause

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. Thus originally it is not an Excel file hence the Excel Application throws the warning / error “The file you are trying to open is in a different format than specified by the file extension”.

Such an exported Excel file can only be used for display purpose only and you cannot use it for Excel operations that you can generally perform in a valid Excel file.

Reference taken from here

SOLUTION

You should use CLOSEXML to resolve it.

public static void ExportToExcel(IEnumerable<dynamic> data, string sheetName)
{
  /****** YOUR ORIGINAL CODE *******/

    gridExcel.HeaderRow.BackColor = System.Drawing.Color.FromArgb(0, 255, 255, 204);
    gridExcel.HeaderRow.Font.Bold = false;
    gridExcel.HeaderRow.Height = Unit.Pixel(30);
    StringWriter sw = new StringWriter();
    gridExcel.RenderControl(new HtmlTextWriter(sw));

    string renderedGridView = sw.ToString();

    //filename will be like xyz.xls
    System.IO.File.WriteAllText(@filename, renderedGridView);



    /**************** Set it accordingly by below reference ****************************/
    XLWorkbook wb = new XLWorkbook();
    var ws = wb.Worksheets.Add(sheetName);
    ws.Cell(2, 1).InsertTable(data);
    HttpContext.Current.Response.Clear();
    HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", sheetName.Replace(" ", "_")));

    using (MemoryStream memoryStream = new MemoryStream())
    {
        wb.SaveAs(memoryStream);
        memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
        memoryStream.Close();
    }

    HttpContext.Current.Response.End();

}

Code reference took from HERE

score 0 · Answer 2 · answered Nov 27 '15 at 06:31

0

You can try this approach

    gridExcel.HeaderRow.BackColor = System.Drawing.Color.FromArgb(0, 255, 255, 204);
    gridExcel.HeaderRow.Font.Bold = false;
    gridExcel.HeaderRow.Height = Unit.Pixel(30);
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename="@filename+".xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    gridExcel.RenderControl(new HtmlTextWriter(sw));
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();

answered Nov 27 '15 at 06:31

Riddhi Shukla

16
4

Thanks..I will check and will share result of this code. – Sagar Shirke Nov 27 '15 at 08:38
Hi did you include this method ? public override void VerifyRenderingInServerForm(Control control) { /* Confirms that an HtmlForm control is rendered for the specified ASP.NET server control at run time. */ } – Riddhi Shukla Nov 30 '15 at 06:16
@Riddhi..I have included that in my class file. – Sagar Shirke Nov 30 '15 at 06:49

Export Asp.net Gridview to Excel error message

2 Answers2