MemoryStream is empty for an OpenXML Excel document

Question

I would normally use ClosedXML to generate Excel files. A Core project is forcing me to use only OpenXML.

The resulting Excel file will be downloaded.

I am having an issue where the memory stream given to the SpreadsheetDocument is empty.

I have tried multiple ways to generate a stream. The code below represents a slimmed down version to create just the bare essentials. Again, memory streams are 0 in length.

I am aware i should be using using() etc. This is just slimmed down code to try and get it working.

Code to generate the excel; this is coming out of a constructor on a File object. AsMemoryStream is a property declared as public MemoryStream AsMemoryStream { get; private set; }

public File(IList<T> items)
        {
            if (!items.Any())
                throw new InvalidOperationException("items cannot be empty");


            MemoryStream documentStream = new MemoryStream();
            SpreadsheetDocument document = SpreadsheetDocument.Create(documentStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook);

            WorkbookPart workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();

            WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
            worksheetPart.Worksheet = new Worksheet();

            Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
            Sheet sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Export" };

            sheets.Append(sheet);

            // have tried various Save() calls
            // worksheetPart.Worksheet.Save(); 
            // workbookPart.Workbook.Save();

            AsMemoryStream = new MemoryStream();
            documentStream.CopyTo(AsMemoryStream);
}

I am, in a controller, downloading the file as:

public IActionResult TransactionDetails(int transactionId)
        {

            IList<Partner> details = _dataService.GetTransactionPartners(transactionId).Result;
            MemoryStream excelStream = _excelRepository.TransactionDetailExcel(details).AsMemoryStream;
            ContentDisposition contentDisposition = new ContentDisposition
            {
                FileName = "transactionDetails.xlsx",
                Inline = false
            };
            Response.Headers.Add("content-disposition", contentDisposition.ToString());
            return File(excelStream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        }

The file downloads, though is 0 bytes in length. As are any of the memory streams being used.

Can anybody point me in the right direction?

Answer 1

SpreadSheetDocument keeps track of the content that needs to be written to the stream. It only does so when either Close or Dispose is called. Until that moment it will not create any of the OpenXmlPackage as it can't be sure if the document is complete.

Here is how you can fix your code:

MemoryStream documentStream = new MemoryStream();
using(SpreadsheetDocument document = SpreadsheetDocument.Create(documentStream, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
{
    WorkbookPart workbookPart = document.AddWorkbookPart();

    workbookPart.Workbook = new Workbook();

    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
    worksheetPart.Worksheet = new Worksheet();

    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
    Sheet sheet = new Sheet { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Export" };

    sheets.Append(sheet);
    document.Close();
}

// the stream is complete here
AsMemoryStream = new MemoryStream();
documentStream.CopyTo(AsMemoryStream);