2

While attempting to construct an Excel file using ClosedXML, I have come across some unexpected behavior regarding memory streams. In the debugger, I can see that MemoryStream ms is being populated by the wb.SaveAs() function. However, when passing it directly to the DotNetStreamReference, the file downloads as a blob with 0 bytes.

Strangely enough, when I convert the stream to a byte array and back to a stream, the function works as expected.

Am I missing something fundamental here?

This was derived using this MS example

Tech:

  • Blazor Server .Net 6
  • ClosedXML 0.96

The C# code

async Task ExportToExcel()
{
    string fileName = $"DocumentsExport{DateTime.UtcNow.Ticks}.xlsx";
    var wb = new ClosedXML.Excel.XLWorkbook();
    var ws = wb.AddWorksheet("Documents");

    // construct headers
    ws.Cell(1, 1).SetValue<string>("Document Name");
    ws.Cell(1, 2).SetValue<string>("Description");
    ws.Cell(1, 3).SetValue<string>("Sort Order");
    ws.Cell(1, 4).SetValue<string>("Category Name");
    ws.Cell(1, 5).SetValue<string>("Group Name");
    ws.Cell(1, 6).SetValue<string>("Date Modified");

    // construct worksheet contents
    for (int i = 0; i < documents.Count; i++)
    {
        var document = documents[i];
        int rowIndex = i + 2;
        ws.Cell(rowIndex, 1).SetValue<string>(document.Name);
        ws.Cell(rowIndex, 2).SetValue<string?>(document.Description);
        ws.Cell(rowIndex, 3).SetValue<int?>(document.SortOrder);
        ws.Cell(rowIndex, 4).SetValue<string>(document.DocumentCategory.Name);
        ws.Cell(rowIndex, 5).SetValue<string>(document.DocumentCategory.DocumentGroup.Name);
        ws.Cell(rowIndex, 6).SetValue<DateTime?>(document.DateModified);
    }

    // apply formatting and filters
    ws.RangeUsed().SetAutoFilter();
    ws.Columns().AdjustToContents();

    // save file and convert to byte array
    // passing this stream to the stream reference causes the file to be downloaded with 0 bytes, thus no content
    using MemoryStream ms = new MemoryStream();
    wb.SaveAs(ms);

    // this line fails
    //using var streamRef = new DotNetStreamReference(stream: ms);

    // this line works
    using var streamRef = new DotNetStreamReference(stream: new MemoryStream(ms.ToArray()));

    // execute javaScript to download file
    await JS.InvokeVoidAsync("downloadFileFromStream", fileName, streamRef);
}

The JavaScript code:

<script>
// script function used to download small files, like excel reports
// https://learn.microsoft.com/en-us/aspnet/core/blazor/file-downloads?view=aspnetcore-6.0
window.downloadFileFromStream = async (fileName, contentStreamReference) => {
    const arrayBuffer = await contentStreamReference.arrayBuffer();
    const blob = new Blob([arrayBuffer]);
    const url = URL.createObjectURL(blob);
    const anchorElement = document.createElement('a');
    anchorElement.href = url;
    anchorElement.download = fileName ?? '';
    anchorElement.click();
    anchorElement.remove();
    URL.revokeObjectURL(url);
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Steve W
  • 416
  • 1
  • 3
  • 19

1 Answers1

4

You have to reset the stream position. After you save, the stream position is at the end of the stream, and when you pass that stream, other methods start reading from the current position, so there is nothing to read. ToArray convert the whole stream, from the start position, that's why you can see all the data. When you create a new MemoryStream from that array, it will have all the data, but it's posistion will be at the begining, that's why it works.

So, all you have to do is

using MemoryStream ms = new MemoryStream();
wb.SaveAs(ms);
ms.Position=0;

And it should work.

Magnetron
  • 7,495
  • 1
  • 25
  • 41