Click here to Skip to main content
15,886,067 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
What am I trying to do
I want to give the user the ability to do a search using a C# asp.net webpage against a SQL Server and examine the data in a gridview. Then if the data is satisfactory they can click on export and copy the data to an excel spreadsheet.

My Code:
C#
Response.ClearContent();
    Response.ContentType = "application/excel";
    Response.AddHeader("content-disposition", "attachment; filename=\"FileName.xls\"");
    Response.Charset = "";
    System.IO.StringWriter OEStringWriter = new StringWriter();
    System.Web.UI.HtmlTextWriter OEHTMLTextWriter = new HtmlTextWriter(OEStringWriter);
    grdMyOEs.RenderControl(OEHTMLTextWriter);
    Response.Write(OEStringWriter.ToString());
    Response.Flush();
    Response.End();


Notes:
1. grdMyOEs above has about 30 rows of data populated from a SQL Server.
2. I am overriding VerifyRenderingInServerForm in my code.
3. Spent 12 hours looking over every example/sample code on the web.
4. I was going to copy in all of the mutations I tried within the code to get it to work but it would probably overload codeproject.com

Troublesome warning:
The file you are trying to open, 'FileName.xls' is in a different format than specified by the file extension...

I have tried all of the usual excel file extensions to no avail. If I change the file name to FileName.html I can open the file, however, it is in the format of a web page and not an excel file.
Posted
Updated 3-Sep-14 4:25am
v2
Comments
Herman<T>.Instance 3-Sep-14 10:50am    
find yourself: https://www.google.nl/search?q=asp.net+export+to+excel&oq=asp.net+eport+&aqs=chrome.1.69i57j0l5.3904j0j7&sourceid=chrome&es_sm=93&ie=UTF-8
mikwilmot 3-Sep-14 11:02am    
I have done that exact search multitudes of times. Go to the first few links in that search and you will see the code I have included in my enquiry.
Herman<T>.Instance 3-Sep-14 11:26am    
Contentype: application/vnd.ms-excel
mikwilmot 3-Sep-14 11:31am    
Thanks for the help. Tried that my friend. I have tried myriads of excel types:
Response.ContentType = application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.ContentType = "application/vnd.ms-excel";
Response.ContentType = "application/ms-excel";
I really don't think that is the problem.

1 solution

When I've had to skin this cat, this is the approach that I took:

C#
//returns a memorystream with an OpenXML.SpreadsheetDocument created on it
var stream = ExcelUtility.GetExcel(temp);
var filename = "MyFile.xlsx" // Usually a concat'd term

var cd = new System.Net.Mime.ContentDisposition
{
    FileName = filename,
    Inline = false,
};
Response.Clear();
Response.AppendHeader("Content-Disposition", cd.ToString());
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.BinaryWrite(stream.ToArray());
Response.Flush();
Response.End();


I suspect 2 possibilities.

First, without a proper Content-Disposition the browser isn't sure what to do with the file.

Second, the file may have issues since it is a text stream rather than a byte[]. You'd likely be best off building a function that leverages DocumentFormat.OpenXML to parse the grid, rather than trying to send it as a text stream.
 
Share this answer
 
v2
Comments
mikwilmot 4-Sep-14 8:34am    
Nathan, Thanks so much for the reply and time spent. I have tried so many ContentTypes I have to think the problem does not exist there. No matter what ContentType I try I get the error message, however, if I change the file extension to .html it works but opens my data(from the grid) as a webpage and the data looks spot on.
This is obviously my first time trying to do this so my next question is probably pretty dumb. In the code you wanted me to try where do I grab the contents of the gridview.
Nathan Minier 4-Sep-14 8:50am    
Not content-type, content disposition. When you're hijacking the request directly that field helps the browser determine exactly what to do with it.

The example I gave is actually my boilerplate code for attaching a file directly to a request, not one built for your specific application. What I can say for sure is that it has always worked for me.

Try the content-disposition section, but if that doesn't work what you'll likely have to do is build a spreadsheet from your gridview, attach it to a memory stream (not string), and send it down the pipe. This isn't too hard of a process, but involves getting into the weeds with OpenXML a bit
mikwilmot 4-Sep-14 9:02am    
Ok. One more question/example and maybe you can see the fundamental flaw that I can't understand.
HttpResponse Response = System.Web.HttpContext.Current.Response;
Response.ClearHeaders();
Response.AppendHeader("content-disposition", "attachment; filename=myfile.xls");
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
System.IO.StringWriter OEStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter OEHTMLTextWriter = new System.Web.UI.HtmlTextWriter(OEStringWriter);
grdMyOEs.RenderControl(OEHTMLTextWriter);
Response.Write(OEStringWriter);
Response.End();

If I execute this I get the warning from above and all of the data is loaded into an excel spreadsheet, opens it up and it looks great. However, when I go to save the spreadsheet in excel it only lets me save it as an html file.
I have a screen print with excel offering to save the spreadsheet as an html file if you would like to look at it.
Nathan Minier 4-Sep-14 9:27am    
Yes, I definitely see the issue. I've scrawled through some of MS document formatting library and can confirm that .xls is not a format that will accept structured XML in the file format, it must be byte[]. Therefore, you cannot use a StringWriter to build and format the workbook, and it must go downstream as byte[].

Excel might be opening the HTML table just fine, and you may be able to export/save as an excel format, but the format itself is not a byte[] so will not natively convert.

There is a workaround for this if you don't want to deal with OpenXML or memorystreams, and that would be to build a .csv file from your grid.

Technical documentation on the .xls file format can be downloaded as PDF from:
http://msdn.microsoft.com/en-us/library/office/cc313154%28v=office.12%29.aspx

mikwilmot 4-Sep-14 9:45am    
Alright. I think you have given me a direction to research. I will close out this issue and research the information you have provided. Thanks so much for all your help and time.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900