How do I return a generated XLSX using (Core 2.2) Web API?

Question

Now, to preface: I've looked at a number of apparent answers for this and, for whatever reason, none of them seem to work for me - at least with EPPlus 4.5.3.2 / Core 2.2 Web API

PROBLEM:

My task is to convert a result set of List<ExpandObject> to an XLSX (using EPPlus) which is consumed through Web API.

SOLUTION(?)

Here's my solution as it currently stands - the problem is it only returns a structured json and ignores the XLSX attachment:

[HttpGet]
public HttpResponseMessage GetInventoryExcel() {
   List<ExpandObject> results = MagicallyGenerateList();
   return ToExcelResponse("example", results.ToArray());
}

public HttpResponseMessage ToExcelResponse(string filename, dynamic[] results) {
    var response = new HttpResponseMessage(HttpStatusCode.OK);

    using (MemoryStream memoryStream = new MemoryStream()) {
        using (var package = new ExcelPackage(memoryStream)) {
            var worksheet = package.Workbook.Worksheets.Add(filename);
            int x = 1, y = 0;

            foreach (var result in results) {
                x = 1;
                y++;

                foreach (var key in ((IDictionary<string, object>)result).Keys) {
                    if (((IDictionary<string, object>)result).TryGetValue(key, out var value)) {
                        worksheet.Cells[x++, y].Value = value;
                    } else {
                        x++;
                    }
                }
            }

            package.Save();
        }

        response.Content = new StreamContent(memoryStream);
        response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") {
            FileName = filename + ".xlsx"
        };
    }

    return response;
}

SAMPLE RESPONSE

{
    "Version": {
        "Major": 1,
        "Minor": 1,
        "Build": -1,
        "Revision": -1,
        "MajorRevision": -1,
        "MinorRevision": -1
    },
    "Content": {
        "Headers": [
            {
                "Key": "Content-Type",
                "Value": [
                    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
                ]
            },
            {
                "Key": "Content-Disposition",
                "Value": [
                    "attachment; filename=example.xlsx"
                ]
            }
        ]
    },
    "StatusCode": 200,
    "ReasonPhrase": "OK",
    "Headers": [],
    "RequestMessage": null,
    "IsSuccessStatusCode": true
}

I tried to use the [Produces("application/ms-excel")] directive but that just results in a 406 response code. Any suggestions would be appreciated

Answer 1

With some small changes it works for me:

    [HttpGet]
    public IActionResult GetInventoryExcel()
    {
        List<ExpandoObject> results = MagicallyGenerateList();
        return ToExcelResponse("example", results.ToArray());
    }

    public IActionResult ToExcelResponse(string filename, dynamic[] results)
    {
        MemoryStream memoryStream = new MemoryStream();
        using (var package = new ExcelPackage(memoryStream))
        {
            var worksheet = package.Workbook.Worksheets.Add(filename);
            int x = 1, y = 0;

            foreach (var result in results)
            {
                x = 1;
                y++;

                foreach (var key in ((IDictionary<string, object>)result).Keys)
                {
                    if (((IDictionary<string, object>)result).TryGetValue(key, out var value))
                    {
                        worksheet.Cells[x++, y].Value = value;
                    }
                    else
                    {
                        x++;
                    }
                }
            }

            package.Save();

            memoryStream.Position = 0;
            return File(memoryStream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", filename + ".xlsx");
        }
    }

I used this article: https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/