GridView export to Excel

Question

I have an asp.net page in which I have a gridview and a button. On button click event I have wrote the code which exports the gridview to excel which is working very fine. But when I try to open that exported excel file it shows a dialog box saying:

"The file you are trying to open is in a different format than specified by the file extension.Verify that the file is not corrupted and is from a trusted source before opening the file".

Not only that, i sent that excel file as attachment in gmail and tried to open it in mobile, at that time it opens as an html file.What can I do for this because my client will be using mobile to view mails.

Exact duplicate http://stackoverflow.com/questions/9799726/why-showing-error-message-while-opening-xls-file — SMK, Aug 08 '12 at 10:31
You have exported a html table to a file with xls or xlsx extension(i assume via [`GridView.RenderControl` Method](http://msdn.microsoft.com/en-us/library/system.web.ui.control.rendercontrol.aspx)). Excel can interpret a html table, but it's not a real excel file nevertheless. Use EPPLus to create a real excel file. — Tim Schmelter, Aug 08 '12 at 10:33
Try this util http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html it is really good — Paras, Aug 08 '12 at 11:02
mathewtinus, a small bit of searching would have given you this linnk http://epplus.codeplex.com/ as the first result for epplus. I second Tim's suggestion in using this package as it creates a native excel document and not the html table hack that is used below. In later versions of excel the html table route will cause errors and formatting problems. — Jeff Turner, Aug 08 '12 at 11:57
@mathewtinus: Here's the link and an ASP.NET example: http://stackoverflow.com/questions/10547619/inserting-data-into-an-excel-sheet-from-a-datatable/10547727#10547727 — Tim Schmelter, Aug 08 '12 at 12:57

score 2 · Answer 1 · answered Aug 08 '12 at 12:53

I'd recommend creating an actual Excel file instead of a CSV or HTML using an Excel file name extension.

One easy way to accomplish this is using ClosedXML.

To do this, download the ClosedXML.dll and the DocumentFormat.OpenXml.dll from the codeplex site and add them as references in your ASP.NET project. Then, in your button click event, you can simply set up an Excel workbook, create a worksheet from the same DataTable that you are binding to the GridView, and save the workbook file in the HTTP response. Something like this:

var wb = new ClosedXML.Excel.XLWorkbook();
DataTable dt = GetTheDataTable();
dt.TableName = "This will be the worksheet name";

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();

I'd go with ClosedXML over other alternatives because the license is less restrictive, the documentation is superb, the developer is helpful and friendly, and the project is currently very active.

score 1 · Answer 2 · answered Aug 08 '12 at 10:42

In your button click event :

        protected void btnExcel_Click(object sender, ImageClickEventArgs e)
    {
        //export to excel
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment;filename=FileName.xls");
        Response.Charset = "";

        // If you want the option to open the Excel file without saving then
        // comment out the line below
        // Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
       //GV is the ID of gridview
        GV.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

and also override this method in your page code behind:

    public override void VerifyRenderingInServerForm(Control control)
{
    /* Confirms that an HtmlForm control is rendered for the specified ASP.NET
       server control at run time. */
}

score 0 · Answer 3 · answered Aug 08 '12 at 10:32

0

When I exported to excel, I simply created a comma separated file and gave it an excel file extension. That message went away when I started separating using commas with double quotes.

"one","two","three" rather than one,two,three

answered Aug 08 '12 at 10:32

Theo

2,609
1
26
27

GridView export to Excel

3 Answers3