How to create Excel file using OpenXML without creating a local file?

Question

Is it possible to create and edit an excel document using OpenXML SDK without creating a local file?

As per the documentation the Create method demands for a filepath, which creates a local copy of the file.

SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filepath, SpreadsheetDocumentType.Workbook);

I'm referring the MSDN article here: https://msdn.microsoft.com/en-us/library/office/ff478153.aspx

My requirement is to create the file, and it should be downloaded by the browser on clicking a button.

Any suggestion?

Solution 1

You could use the overload of SpreadsheetDocument.Create that takes a Stream and pass it a MemoryStream:

MemoryStream memoryStream = new MemoryStream();
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(memoryStream, SpreadsheetDocumentType.Workbook);

//add the excel contents...

//reset the position to the start of the stream
memoryStream.Seek(0, SeekOrigin.Begin);

return new FileStreamResult(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

Note that as per this StackOverflow question you don't need to dispose the Stream as it will be done for you by the FileStreamResult class.

Solution 2

Adding another answer as I spent way too much time searching for this: Another way to save a SpreadsheetDocument to a stream is by calling SpreadsheetDocument.Clone(Stream s)

It has the benefit of letting you save to a stream even if you created the document from a file or a stream you don't want to save to.

Comments

[Sal]

This will still buffer the data into memory. I found this option to create an excel file using a write-only non-seekable stream: codeproject.com/articles/347759/…

[Amrendra]

I am following the same approach but getting one corrupted file downloaded. could you please help me with this. What can be the reason?

[petelids]

It can be any number of things to be honest @Amrendra. If you open a new question I'll try to take a look.