如何使用(Core 2.2)Web API返回生成的XLSX? [英] How do I return a generated XLSX using (Core 2.2) Web API?

问题描述

现在，作为序言:为此，我已经看了许多明显的答案，无论出于何种原因，它们似乎都不适合我-至少对于EPPlus 4.5.3.2/Core 2.2 Web API

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

问题:

我的任务是将结果 List< ExpandObject> 的结果集转换为通过Web API使用的XLSX(使用EPPlus).

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

解决方案(?)

这是我目前的解决方案-问题是它仅返回结构化的json并忽略XLSX附件:

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;
}

样品响应

{
    "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
}

我尝试使用 [Produces("application/ms-excel")] 指令，但这只会导致406响应代码.任何建议将不胜感激

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

推荐答案

通过一些小的更改，它对我有效:

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");
        }
    }

我使用了这篇文章: https://www.c-sharpcorner.com/article/using-epplus-to-import-and-export-data-in-asp-net-core/

这篇关于如何使用(Core 2.2)Web API返回生成的XLSX?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！