Getting a corrupted XLSX file when writing it to Response.OutputStream

Question

In ASP.Net, I'm using NPOI to write save to an Excel doc and I've just moved to version 2+. It worked fine writing to xls but switching to xlsx is a little more challenging. My new and improved code is adding lots of NUL characters to the output file.

The result is that Excel complains that there is "a problem with some content" and do I want them to try to recover?

Here is a pic of the xlsx file that was created from my Hex editor: BadXlsxHexImage Those 00s go on for several pages. I literally deleted those in the editor until the file opened without an error.

Why does this code add so many NULs to this file??

using (var exportData = new MemoryStream())
{
     workbook.Write(exportData);
     byte[] buf = exportData.GetBuffer();

     string saveAsFileName = sFileName + ".xlsx";

     Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
     Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}; size={1}", saveAsFileName, buf.Length.ToString()));
     Response.Clear();
     Response.OutputStream.Write(buf, 0, buf.Length);
     exportData.Close();
     Response.BufferOutput = true;
     Response.Flush();
     Response.Close();
}

(I've already tried BinaryWrite in place of OutputStream.Write, Response.End in place of Response.Close, and setting Content-Length with the length of the buffer. Also, none of this was an issue writing to xls.)

Answer 1

The reason you are getting a bunch of null bytes is because you are using GetBuffer on the MemoryStream. This will return the entire allocated internal buffer array, which will include unused bytes that are beyond the end of the data if the buffer is not completely full. If you want to get just the data in the buffer (which you definitely do), then you should use ToArray instead.

That being said, why are you writing to a MemoryStream at all? You already have a stream to write to: the OutputStream. Just write the workbook directly to that.

Try it like this:

Response.Clear();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", saveAsFileName));
workbook.Write(Response.OutputStream);
Response.Flush();
Response.Close();