How to write an Excel workbook to a MemoryStream in .NET?

Question

How do I write an Excel workbook to a MemoryStream without first saving it to the file system?

All options within the Microsoft.Office.Interop.Excel.WorkBook save options take a filename.

Answer 1

I have done extensive work with the PIA and with storing Excel files in a document repository and streaming it out to the browser, and I have not been able to find a solution to using the PIA without first writing the contents to the file system first.

I think that you are going to have to swallow the bullet and deal with the file system as an intermediary. The good news is that you can just give the file a unique name like a guid or use some other tempfilename method (not sure if one exists in .net) and just delete the contents when you are done.

Answer 2

If you only need basic functionality from Excel, you might want to create the Workbook as an Html stream. There is another question on SO that handless this.

This also solves some problems (you might not have yet) concerning the scalability of your solution.

Answer 3

Another option would be to use this free library for exporting the workbook. The save method on the workbook class can either take a file name or a stream.

Answer 4

You could try GemBox.Spreadsheet .NET Component for reading and writing Excel files of various formats like XLS, XLSX, ODS, CSV, HTML.

It supports saving spreadsheet data to stream.

Answer 5

The only way you could do this is if you were prepared to create a custom object that allowed you to store all the various bits and pieces of data/formulas/vba/links/ole objects that you wanted to keep, copy from your workbook to the object, and then persist that object to a memory stream. In affect using your proxy object as a go between.

There is no way (as others have said) of writing an Excel file straight to memory.

With Excel 2007 OpenXML format, you could use the BeforeSave event of the workbook to have a custom method that first sets the Cancel parameter of the BeforeSave event to True, and then instead serialises the resultant xml package (that represents the file) into an object in memory.

Answer 6

This can be done with Open XML SDK 2.0. Eric White has a great article on how to work with OOXML files in stream at Working with In-Memory Open XML Documents