对于 OpenXML Excel 文档，MemoryStream 为空 [英] MemoryStream is empty for an OpenXML Excel document

问题描述

我通常会使用 ClosedXML 来生成 Excel 文件.核心项目迫使我只使用 OpenXML.

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

将下载生成的 Excel 文件.

The resulting Excel file will be downloaded.

我遇到了一个问题，即提供给 SpreadsheetDocument 的内存流为空.

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

我尝试了多种方法来生成流.下面的代码代表了一个精简版，只创建了最基本的东西.同样，内存流的长度是 0.

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.

我知道我应该使用 using() 等.这只是精简代码以尝试让它工作.

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

生成excel的代码；这是来自 File 对象的构造函数.AsMemoryStream 是一个声明为 public MemoryStream AsMemoryStream { get;私人订制；}

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

文件下载，但长度为 0 字节.与正在使用的任何内存流一样.

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?

推荐答案

SpreadSheetDocument 跟踪需要写入流的内容.它仅在 Close 或 Dispose 被调用.在那之前，它不会创建任何 OpenXmlPackage，因为它无法确定文档是否完整.

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

这篇关于对于 OpenXML Excel 文档，MemoryStream 为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！