Excel and "unreadable content" when creating an Open XML spreadsheet with MemoryStream

Question

When creating an Excel spreadsheet using the Open XML SDK v2.0, our Excel output initially worked successfully for a number of months. Recently Excel (all versions) began to complain about "Excel found unreadable content in 'zot.xlsx'. Do you want to recover the contents of this workbook?". We are creating the file in a web application, using a MemoryStream as the store, which is then sent as a byte[] in an HTTP response with a MIME type of "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet". The unzipped contents of a bad file were identical to the unzipped contents of a file without errors.

Answer 1

Use Open XML SDK 2.5 Productivity Tool for Microsoft Office to open the .xlsx file and validate the document this will give you any validation errors causing the unreadable content

Answer 2

We chased this down for too many hours, picking up a couple of red herrings along the way, but in the end, resolved that the bad file was different in one respect. The file length was different. Before returning the MemoryStream and writing the byte[] to the HTTP response, ensure that you truncate the MemoryStream so that its capacity and length are the same, using a simple stream.Capacity = (int)stream.Length;.

It appears that Excel now detects the extra content in the file as a security risk as 'unreadable content' and throws the annoying error, when in the past it would accept the risk.

Note: answer taken from original poster, who previously had the answer in his question

Answer 3

Check the following one by one to remove "Unreadable content error" from excel.

1.Ensure that the correct data written in correct cell in correct way. do it for all the cells.It might happen that wrongly written data in once cell causing this issue.Cell index is used correctly.

2.Try using Cell.DataType = new EnumValue(CellValues.String) instead of shared string.this may help to remove the error.

3.if any cell contain #VALUE/#REF/#NAME? or #DIV error,remove those error .

4.This issue comes up while downloading the file from server. Creating excel spreadheet in a web application, using a MemoryStream and downloading the same.

use the following code: HttpContext.Current.Response.Clear()

  Response.ClearHeaders()  

  Response.Buffer = False

  msReportStream = CType(controller.GetFromSession  
       (Constants.SESSION_REPORT), MemoryStream)

  Response.ContentType = "application/vnd.openxmlformats-  
       officedocument.spreadsheetml.sheet"

   Response.AddHeader("Connection", "Keep-Alive")

   Response.AddHeader("Content-Disposition", String.Format("attachment;  
       filename={0}", strReportFileName))

   Response.ContentEncoding = Encoding.UTF8

   Response.BinaryWrite(msPNLReportStream.ToArray())

   Response.Flush()

   Response.Close()

   Response.End()--use this when the code is deployed in server only not required in local.gives error in local.

   msReportStream.Dispose()

   msReportStream.Close()

if you are using ASPOSE technology,use

Me.Response.Clear()

Me.Response.Buffer = False

Me.Response.AddHeader("Accept-Ranges", "bytes")

Response.ContentType = "application/octet-stream"

Response.AddHeader("Connection", "Keep-Alive")

Response.ContentEncoding = Encoding.UTF8

asposeReport.ShowSavePopUp(Me.Response, controller.GetFromSession(Constants.SESSION_REPORT), strReportFileName)
                                                                                                       Me.Response.Flush()
            Me.Response.Close()
            Me.Response.End()