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?

Shill

@rene - no. Though i did try flushing the documentStream before copying. I will try disposing the document now. – Shill

@rene - thank you so much. Disposing resolves that issue for me. Now trying to figure why my row data isn't committed to the sheet.. urgh. Thanks again! Feel free to add an answer and I will mark it, if you like. – Shill

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);

Martensite

Also add : documentStream.Position = 0; after using block. I use it in web api app that create excel in server memory and send it back to an angular app which download the file to the user downloads folder. I couldn't open the downloaded Excel until I add document.close() in the using block and documentStream.Position = 0 after it. Thanks rene ! – Leann