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?

Stringer

Answer 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.

Termitarium

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/… – Stickler

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

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

memoryStream.Seek(0, SeekOrigin.Begin); helped me...without this line file was corrupt. – Sagacity

Answer 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.

Tiepolo

Yes, the Clone method is very useful in scenarios with "template" files. – Excerpt