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.

Community · Accepted Answer · 2017-05-23 10:28:23Z

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

Riddhi Shukla · Accepted Answer · 2015-11-27 06:31:17Z

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, 2015 at 6:31

Riddhi Shukla

164 bronze badges

3 Comments

Sagar Shirke Over a year ago

Thanks..I will check and will share result of this code.

Riddhi Shukla Over a year ago

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. */ }

Sagar Shirke Over a year ago

@Riddhi..I have included that in my class file.

Collectives™ on Stack Overflow

Export Asp.net Gridview to Excel error message

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related