Exported file is returning as JSON instead of Excel file

Question

I'm trying to return an Excel file but its always trying to save a JSON file instead of Excel file. Can you tell me what is wrong with this code? I thought the content type was wrong but I tried several options there and yet still returning a JSON...

/Excel builder

public class ExcelFileBuilder
{
    public byte[] BuildEmployeeRecordFile(IEnumerable<EmployeeRecordDto> records)
    {
        byte[] fileContents;
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        using (var package = new ExcelPackage())
        {
            var worksheet1 = package.Workbook.Worksheets.Add("TEST");
            //ADD data here

            // Export it to byte array
            fileContents = package.GetAsByteArray();
        }
        return fileContents;
}

/Query class

public class ExportEmployeeRecordQuery : IRequest<ExportEmployeeRecordVm>
{

}

public class ExportEmployeeRecordQueryHandler : IRequestHandler<ExportEmployeeRecordQuery, ExportEmployeeRecordVm>
{
    private readonly IApplicationDbContext _context;
    private readonly IExcelFileBuilder _fileBuilder;

    public ExportEmployeeRecordQueryHandler(IApplicationDbContext context, IExcelFileBuilder fileBuilder)
    {
        _context = context;
        _fileBuilder = fileBuilder;
    }

    public async Task<ExportEmployeeRecordVm> Handle(ExportEmployeeRecordQuery request, CancellationToken cancellationToken)
    {
        var vm = new ExportEmployeeRecordVm();
        
        //get data from db here
        vm.Content = _fileBuilder.BuildEmployeeRecordFile(null); //pass null for now
        vm.ContentType = "application/octet-stream";
        vm.FileName = "TestFile.xlsx";

        return await Task.FromResult(vm);
    }
}

/VM

public class ExportEmployeeRecordVm
{
    public string FileName { get; set; }

    public string ContentType { get; set; }

    public byte[] Content { get; set; }
}

/Azure function

public class ExportAgentEmployeeRecordFunction
{
    private readonly IMediator _mediator;
    private readonly IHttpFunctionExecutor _httpFunctionExecutor;

    public ExportAgentEmployeeRecordFunction(IMediator mediator, IHttpFunctionExecutor httpFunctionExecutor)
    {
        _mediator = mediator;
        _httpFunctionExecutor = httpFunctionExecutor;
    }

    [FunctionName("ExportAgentEmployeeRecordFunction")]
    public async Task<IActionResult> Run(
       [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)]
        ExportEmployeeRecordQuery qry)
    {
        return await _httpFunctionExecutor.ExecuteAsync(async () =>
        {
            var res = await _mediator.Send(qry);
            return new OkObjectResult(res);
        });
    }
}

I'm trying to return an Excel file but its always trying to save a JSON file instead of Excel file. Can you tell me what's wrong with this code? I thought the content type was wrong but I tried several options there and yet still returning a JSON.

Does this answer your question? [What is correct content-type for excel files?](https://stackoverflow.com/questions/2937465/what-is-correct-content-type-for-excel-files) — quaabaam, Aug 05 '21 at 00:41
I'm confused. You're returning a view model - why do you expect anything other than JSON? — ProgrammingLlama, Aug 05 '21 at 01:27
If you want to just return a downloadable file then Nkosi's answer is correct. Is this what you want to do? — ProgrammingLlama, Aug 05 '21 at 02:23

Nkosi · Accepted Answer · 2021-08-05T02:29:40.153

You are using the wrong derived action result type

The OkObjectResult

An ObjectResult that when executed performs content negotiation, formats the entity body, and will produce a Status200OK response if negotiation and formatting succeed

Which mean it is wrapping the passed view model and returning that as JSON based on the request.

Given what you are trying to achieve you would need to use the FileContentResult Class,

Represents an ActionResult that when executed will write a binary file to the response.

and populate it using the members of the returned view model

For example

//...

[FunctionName("ExportAgentEmployeeRecordFunction")]
public async Task<IActionResult> Run(
   [HttpTrigger(AuthorizationLevel.Function, "get", Route = null)]
    ExportEmployeeRecordQuery qry) {
    return await _httpFunctionExecutor.ExecuteAsync(async () => {
        ExportEmployeeRecordVm response = await _mediator.Send(qry);
        FileContentResult result =  new FileContentResult(response.Content, response.ContentType){
            FileDownloadName = response.FileName
        };
        return result;
    });
}

//...

score 0 · Answer 2 · answered Aug 05 '21 at 02:20

You could use OfficeOpenXml library to export to an excel file. sample code :


  

     var data = YourDataSource ;
      ExcelPackage excel = new ExcelPackage();
     var workSheet = excel.Workbook.Worksheets.Add("Sheet1");
       workSheet.Cells[1, 1].LoadFromCollection(data, true);
      using (var memoryStream = new MemoryStream())
        {
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";  
      Response.AddHeader("content-disposition", $"attachment;  filename=myfile.xlsx");
       excel.SaveAs(memoryStream);
      memoryStream.WriteTo(Response.OutputStream);
         Response.Flush();
    Response.End();
                        }

Exported file is returning as JSON instead of Excel file

2 Answers2