Stream large ExcelFile in chunks

GemBox.Spreadsheet
1

I generate a large excel file (70mb) which I want to stream via google cloudrun. They have a limit of 32mb (Cloud Run Quotas and Limits  |  Cloud Run Documentation  |  Google Cloud), except when it’s chunked

The response is created from a Controller and looks like this

var stream = new MemoryStream();
xls.Save(stream, SaveOptions.XlsxDefault);
return File(stream, MimeTypes.MimeTypeMap.GetMimeType(".xlsx"), "myfile.xlsx");

What is the official way to make sure that the output is chunked? Do I have to use another kind of stream or can I set this somewhere in the save options?

Thanks

2

Note that the following line will save your ExcelFile object to the MemoryStream:

xls.Save(stream, SaveOptions.XlsxDefault);

That MemoryStream will contain the whole Excel file, you’ll now need to output that in chunks.

But I’m afraid that I’m not familiar with Google Cloud Run so I can’t help you with that.
Nevertheless, perhaps you could try something like this:

public async Task<IActionResult> StreamExcel()
{
    // ...

    using var stream = new MemoryStream();
    var xlsxOptions = SaveOptions.XlsxDefault;
    xls.Save(stream, xlsxOptions);

    Response.Headers.Add("Content-Type", xlsxOptions.ContentType);
    Response.Headers.Add("Transfer-Encoding", "chunked");
    Response.Headers.Add("Content-Disposition", "attachment; filename=\"myfile.xlsx\"");

    const int chunkSize = 1024 * 1024;
    byte[] buffer = new byte[chunkSize];

    int bytesRead;
    while ((bytesRead = await stream.ReadAsync(buffer, 0, buffer.Length)) > 0)
    {
        await Response.Body.WriteAsync(buffer, 0, bytesRead);
        await Response.Body.FlushAsync();
    }

    Response.Body.Close();
    return new EmptyResult();
}

I hope this helps.

Regards,
Mario

3

Thanks Mario, this helped, I had to make a own IActionResult and used the following snipped (based on your input) to make it work

await using var bodyStream = response.BodyWriter.AsStream();
await response.StartAsync().ConfigureAwait(false);

while (true)
{
    var buffer = new byte[bufferSize];
    var bytesRead = await this.stream.ReadAsync(buffer, 0, buffer.Length).ConfigureAwait(false);
    if (bytesRead == 0)
    {
        break;
    }

    await bodyStream.WriteAsync(buffer, 0, bytesRead).ConfigureAwait(false);
    await bodyStream.FlushAsync().ConfigureAwait(false);
}

await response.CompleteAsync().ConfigureAwait(false);