GridView to Excel Export

Nishanthan Ganeshan

Im always getting error when i open my file "the file extension is invalid"

HttpResponse r = Response;

            //clean up the response.object

            r.Clear();
            r.Buffer = true;
            r.Charset = "UTF-8";
            //set the response mime type for excel
            r.ContentType = "application/vnd.ms-excel";
            string attachment = "attachment; filename=Report" + ".xls";
            r.AddHeader("content-disposition", attachment);
            r.ContentEncoding = new System.Text.UTF8Encoding();
            r.Write(content);
r.End();

Anyone whats the problem ?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Neboja12y ago
Hi, you are right, the file is in html format. If you want to create a real XLS file with binary content (BIFF8) then you will need to do a conversion from HTML formatted content to an XLS formatted content with C#, for example like the following:

var sw = new StringWriter();
```
var htw = new HtmlTextWriter(sw);
gvdetails.AllowPaging = false;
gvdetails.DataBind();
gvdetails.RenderControl(htw);
 
ExcelFile excel;
var option = LoadOptions.HtmlDefault;
using (var htmlStream = new MemoryStream(option.Encoding.GetBytes(sw.ToString())))
    excel = ExcelFile.Load(htmlStream, option);
 
excel.Save(Response, "Report.xls");
```
Note that with this you do not have to explicitly set anything on current Response, it will stream the file to the client's browser with a Save method call.

Also this code uses a .NET library for excel transformations, you can achieve various requirements by processing your XLS file in C# with it.
0
Sanjeeb Lenka13y ago
hi Nishanthan,
Which office version you are using if you are using older version like 2003 then it will open and in newer version it will show warning.

so if you want to convert it to xlsx below below link.

http://aakarsoft.com/wordpress/index.php/export-gridview-data-to-excel-xlsx-without-using-htmltextwriter-in-asp-net/

in your code :
use this two name space for above link code:

using OfficeOpenXml;
using OfficeOpenXml.Style;

if any problem reply

0
Nishanthan Ganeshan13y ago
Still the same i dont get any error in the code..the problem is after download the file im getting message the file is not in excel extension.I believe its writing in html format.

Nishanth
0
Sanjeeb Lenka13y ago
check below code:

HttpResponse r = Response;

        //clean up the response.object

        r.Clear();
        r.Buffer = true;
        r.Charset = "UTF-8";
        //set the response mime type for excel
        r.ContentType = "application/vnd.ms-excel";
        string attachment = "attachment; filename=Report" + ".xls";
        r.AddHeader("content-disposition", attachment);
        StringWriter sw = new StringWriter();
        HtmlTextWriter htw = new HtmlTextWriter(sw);
        gvdetails.AllowPaging = false;
        gvdetails.DataBind();
        gvdetails.RenderControl(htw);
        r.Write(sw.ToString());
        r.End();

gvdetails is the grid you want to export.

or you can follow this link

http://www.aspdotnet-suresh.com/2011/04/how-to-export-gridview-data-to-excel-or.html

if any problem reply
0