What causes a COMException with the message "Cannot access 'System.IO.MemoryStream'" to be thrown?

Question

I'm trying to export a DataTable to Microsoft Excel 2016 and I got my code to "successfully" run through once. When it did run, and I tried to open the Excel file, it told me that the file format was invalid (trying to export to XLSX). That's another issue entirely, but I'm including it just in case it's helpful in any way.

Upon each following attempt to export, the code throws a COMException with a message of:

Cannot access 'System.IO.MemoryStream'.

The stack trace shows:

at Microsoft.Office.Interop.Excel._Workbook.SaveAs(Object Filename, Object FileFormat, Object Password, Object WriteResPassword, Object ReadOnlyRecommended, Object CreateBackup, XlSaveAsAccessMode AccessMode, Object ConflictResolution, Object AddToMru, Object TextCodepage, Object TextVisualLayout, Object Local)

at MyNamespace.btnExportToExcel_Click(Object sender, EventArgs e) in C:\...\PageName.aspx.cs:line 279

Neither of which are helpful to debugging the process. I've experienced exceptions where the Stream object hadn't been closed before trying to reopen it, which would be easy to solve and there is a vast sea of questions here regarding that.

I'm creating the stream in a using statement, which should automatically close and dispose of the Stream even if an exception occurs. In spite of this, I've also tried manually adding a Close and Dispose to the finally block of my code, and still the exception gets thrown.

using (MemoryStream fs = new MemoryStream()) {
    try {
        Excel.Application xlApp = new Excel.Application();
        Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(Missing.Value);
        var xlSheets = xlWorkBook.Sheets as Excel.Sheets;
        var xlNewSheet = (Excel.Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
        xlNewSheet.Name = "newsheet";

        System.Data.DataTable dt = (System.Data.DataTable)excelGrid.DataSource;
        for (int x = 0; x < dt.Rows.Count; x++)
            for (int y = 0; y < dt.Columns.Count; y++)
                xlNewSheet.Cells[x + 1, y + 1] = dt.Rows[x][y];

        xlWorkBook.SaveAs((Stream)fs); // Exception is thrown here.
        fs.Position = 0;
        string myName = Server.UrlEncode(SearchTitle + ".xlsx");
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment; filename=" + myName);
        Response.ContentType = "application/vnd.ms-excel";
        Response.BinaryWrite(fs.ToArray());
        Response.End();

        xlWorkBook.Close(true, Missing.Value, Missing.Value);
        xlApp.Quit();
    } catch (ThreadAbortException) {
        // This exception is throw from Response.End by design. Gracefully ignore it.
    } catch (Exception ex) {
        // Log exception.
        throw ex;
    } finally {
        fs.Close();
        fs.Dispose();
    }
}

I thought perhaps restarting my debugging session would work, but this didn't fix it either.

---

What causes a COMException with the message Cannot access 'System.IO.MemoryStream' to be thrown?

Answer 1

The API here is a lie; it is a wrapper around what Excel can do, and Excel is a COM server than can talk to files. Although it is described as taking a Stream, in reality it is much more limited. It should work with FileStream, but : it cannot work with MemoryStream. So... don't do that. Write to a file in the temp directory instead.