WebApi Core Export to Excel, return Angular as download, corruped file, cannot open

Question

I am using the EPPlus on my webapi core to return a FileContentResult. When the object returns to my Angular v12 app, I get the following error when I try to open my excel file

Excel cannot open the file because the file XXX-8_3_2021.xlsx format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.

I following a few old post on how to implement export to Excel and save the downloaded Excel.

Any help is appreciated.

[HttpPost("Export")]
public async Task<ActionResult<ActionResult<FileContentResult>>> Export([FromBody] ExportDto request)
{
if (request != null)
{

    byte[] fileBytes = await GetExcelFile(request);

    return Ok(
            File(fileBytes,
            "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            "MyExcel-" + DateTime.Now.ToShortDateString() + ".xlsx"));

}
else
{
    return BadRequest(responseMessage);
}

}

My Angular Service is

export(dto: ExportDto): Observable<ExportDto> {

return this.http.post<any>(this.exportUrl, dto)
    .pipe(map(
        response => { 

          return this.handleFileDownload(response, dto)
        }
    ));      

}

private handleFileDownload(response: any, dto: ExportDto) {
let file = new Blob([response.fileContents],
{type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'});
let fileName = response.fileDownloadName;
saveAs(file, fileName);

return dto;

}

I tried but with http.post, the responseType is throwing an error

this.http.post<any>(this.exportUrl, dto, { responseType: "arrayBuffer"})

Answer 1

Hi,

I tried using with Memory Stream

public MemoryStream ExportToExcel()
{

MemoryStream fileStream = new();
using (ExcelPackage package = new())
{
.. worksheet code

package.SaveAs(fileStream);

}

return fileStream;

}

Back at the Controller

I am returning FileStream instead of byte[]

return OK(fileStream, contentType, fileName);

But at the Client, I am getting size 0 for the body.

Thanks

Answer 2

Hi @Marvelocity ,

In your previous reply (using EPPlus), in the API method, try to return the File object, without the Ok method. Code like this:

 return File(fileBytes,  
         "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  
         "MyExcel-" + DateTime.Now.ToShortDateString() + ".xlsx");  

Then, use Swagger or Postman to check the API method, whether you could download the excel file or not, and whether you can open the excel file or not?

If the excel file still is broken, perhaps the issue relates the file content or the EPPlus.

Besides, I suggest you could refer the following sample to use ClosedXML to export data to excel:

[Route("api/[controller]")]  
[ApiController]  
public class ToDoController : ControllerBase  
{   
    [HttpGet("exporttoexcel")]  
    public async Task<ActionResult> ExportToExcel()  
    {  
        using (var workbook = new XLWorkbook())  
        {  
            var worksheet = workbook.Worksheets.Add("Users");  
            var currentRow = 1;  
            worksheet.Cell(currentRow, 1).Value = "Id";  
            worksheet.Cell(currentRow, 2).Value = "Username";  

            var users = new List<User>();  
            for (var i = 1; i < 10; i++)  
            {  
                users.Add(new Models.User() { UserId = i, UserName = "User" + i.ToString() });  
            }  
            foreach (var user in users)  
            {  
                currentRow++;  
                worksheet.Cell(currentRow, 1).Value = user.UserId;  
                worksheet.Cell(currentRow, 2).Value = user.UserName;  
            }  

            using (var stream = new MemoryStream())  
            {  
                workbook.SaveAs(stream);  
                var content = stream.ToArray();  

                return File(  
                    content,  
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",  
                    "users.xlsx");  
            }  
        }  
    }  

Then, in the main page, use the following hyperlink to call the API method and download excel file:

<a href="~/api/Todo/exporttoexcel" download>Download File</a>  

The above code works well on my side, you can refer it.

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best Regards,
Dillion