Writing Excel file to output stream

Jan 3, 2011 at 6:47 PM

I would like to write a workbook as an Excel 2007 file to the HTTP output stream. Here is how I'm currently trying to do it:

Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=\"MyFile.xlsx\"");
Response.BinaryWrite(xlPackage.GetAsByteArray());

However, when I try to run this, Excel prompts the following error:

"Excel found unreadable content in 'MyFile.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of the workbook, click Yes."

If I bypass this message, the workbook displays properly, but obviously I don't want this error to occur.

Any ideas?

Coordinator
Jan 4, 2011 at 8:45 AM

Hi,

try this first...

http://epplus.codeplex.com/Thread/View.aspx?ThreadId=223843

if you still have the same problem, make sure the workbook works if you save it to the harddrive.

If it doesn't, check the FAQ, problem with numberformats and formulas.

Jan

Jan 4, 2011 at 8:24 PM

Thanks, that solved the problem.

Aug 2, 2011 at 2:23 PM

I am having this same problem, but applying the response.clear, did not solve the problem.  Any other ideas?

Editor
Aug 2, 2011 at 2:29 PM
Edited Aug 2, 2011 at 2:38 PM

Maybe it is another problem, like bad formula format, etc. Open your .xlsx file with notepad, if you didnt see html code from your page, it isnt html trash. So, you need to check other things.

 

From FAQ... http://epplus.codeplex.com/wikipage?title=FAQ&referringTitle=Documentation

I get an error that Excel has found unreadable content when I open my generated document?
Check your Formulas and Numberformats. Formulas and numberformats are not validated by the library so if you enter anything wrong the package will be corrupt. Use the English formula names. Formula parameters are separated by commas (,), string parameters use double quotes (").

worksheet.Cells["A1"].Formula="CONCATENATE(\"string1_\",\"test\")";

Numberformats:
Use culture independent number formats: dot (.) for decimal, comma (thousand). 
The easiest way to check a format or a formula is to create an Excel file --> Do the formatting / Add the formulas --> Save it as XLSX ---> Rename the file *.zip. Extract the file and have a look at the number formats in the \xl\styles.xml file and formulas in \xl\worksheets\sheet#.xml.

Aug 2, 2011 at 4:04 PM

That helped. THanks so much.  I have a database table that I am converting to a spreadsheet and I was doing a db row at a time.  I was having to add 1 to the db column number, since there is no column 0 in an xls.  I missed one of the formatting that I didnt do the +1 to. 

Aug 2, 2011 at 5:15 PM
Edited Aug 2, 2011 at 5:18 PM

Furthermore the WorkSheet name mustn't exceed 31 characters.

 

http://www.excelforum.com/excel-programming/501663-length-of-worksheet-name.html

Oct 13, 2011 at 3:53 AM

Call response.clear before send stream back to client.

                    Response.Clear();
                    Response.AddHeader("content-disposition", "attachment;  filename=file.xlsx");
                    Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";                   
                    Response.BinaryWrite(pck.GetAsByteArray());
                    Response.End();

Nov 11, 2011 at 4:55 PM

 Response.Clear(); did the trick. Thank you very much.

Jan 29, 2012 at 9:01 PM

I had the same issue but I was missing the line setting the file size in the header, see my code below:

response.Clear();           

memoryStream.Seek(0,SeekOrigin.Begin);
response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";           
response.AddHeader("content-disposition", "attachment;  filename=" + fileName);           
response.AddHeader("Content-Length", memoryStream.Length.ToString());           
response.BinaryWrite(memoryStream.GetBuffer());           
response.End();