c# XSSFWorkbook Unreadable Content

Question

I hope you can help me here. I'm having a few issues with XSSFWorkbook. It generates the file without problem but when i open in in Excel, i get the following warning: Excel found unreadable content in 'blah blah, blah.xlsx' Dou you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes. If i click yes, it opens the file with no problem. However, if i run it through notepad++, right at the end of the file, i get my page markup.

If i use HSSFWorkbook, it opens in excel without error but i'm limited to 36k lines. My export has over 300k so i switched to XSSFWorkbook. Below is my method for exporting the workbook.

    private void SaveXLSWorkbook(XSSFWorkbook workbook)
    {
        using (MemoryStream ms = new MemoryStream())
        {

            Response.Buffer = true;              
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition","attachment;filename=ProductExport_" + _dateTime + ".xlsx");
            workbook.Write(ms);
            Response.BinaryWrite(ms.ToArray());
        }
    }

Can anyone shed any light on this please? Thanks in advance. :) - Andy

Answer 1

I am using similar approach and it works for me. For Response object please use Clear() and End() as in below code.

using (MemoryStream ms = new MemoryStream())
               {
                Response.Clear();
                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                Response.AddHeader("content-disposition","attachment;filename=ProductExport_" + _dateTime + ".xlsx");

                workbook.Write(ms);
                Response.BinaryWrite(ms.ToArray());                
                Response.End();
             }