I have some code that will be generating an excel report and I am planning on using it for some scheduled tasks but always wanted to return it from my API endpoint as well. My code for my controller is as follows:
[HttpGet("Export")]
[EnableRateLimiting("api")]
public async Task<IActionResult> TestExport()
{
using (var memoryStream = await _reviewSummaryService.GetExcelReportAsync())
{
var fileStream = new FileStreamResult( memoryStream , "application/ms-excel" )
{
FileDownloadName = "ReviewsExport.xlsx"
};
return Ok(new ResponseDTO<FileStreamResult>(){
Succeeded = true,
StatusCode = 200,
Message = "The data has been successfully downloaded.",
Data = fileStream,
Pagination = null});
}
}
Then my service is as follows:
public async Task<MemoryStream> GenerateExcelReportAsync()
{
var reviewSummaryPOCO = await _reviewSummaryRepository.GetAllAsync();
var reviewSummaryDTO = _mappers.Map(reviewSummaryPOCO);
using var wb = new XLWorkbook();
var ws = wb.AddWorksheet();
// Inserts the collection to Excel as a table with a header row.
ws.Cell("A1").InsertTable(reviewSummaryDTO);
// Adjust column size to contents.
ws.Columns().AdjustToContents();
var memoryStream = new MemoryStream();
wb.SaveAs(memoryStream);
return memoryStream;
}
public async Task ExportExcelReportAsync()
{
using (var memoryStream = await GenerateExcelReportAsync())
{
var boxDocumentTask = await _boxRepository.UploadNewVersionAsync(memoryStream, _configuration["Box:ARMSExtractTrackerFileId"]!, "ReviewsExport.xlsx");
}
}
public async Task<MemoryStream> GetExcelReportAsync()
{
return await GenerateExcelReportAsync();
}
My error I get from the controller is cannot access a closed stream. However, I have removed the using statements from my service functions so it does not close the stream so it is unclear how its closed. Also, I do not want to introduce any memory leaks if thats even possible?
You need add the memoryStream.Position = 0; to reset stream position to the beginning
public async Task<MemoryStream> GenerateExcelReportAsync()
{
//...
var memoryStream = new MemoryStream();
wb.SaveAs(memoryStream);
memoryStream.Position = 0; // Reset stream position to the beginning
return memoryStream;
}
Then in your controller should be:
public async Task<IActionResult> TestExport()
{
var memoryStream = await _reviewSummaryService.GetExcelReportAsync();
var fileStream = new FileStreamResult(memoryStream, "application/ms-excel")
{
FileDownloadName = "ReviewsExport.xlsx"
};
return fileStream;
}
2
I think the issue is that the the function reaches the return, attempts to return the stream then closes the stream by finishing the function as you returned, and then the stream is closed.
to return a stream you can do this:
return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
alternatively you can use return type: application/octet-stream
1