NPOI Excel文件无效的文件格式或扩展

Question

我正在尝试在.NET Core 6中创建一个应该返回Excel文件的端点。问题是，当我尝试打开Excel文件时，它说文件格式或扩展名无效。

那是我的代码：

控制器

[HttpPost("/api/report")]
public async Task ExportFile([FromBody] ReportDTO reportDTO)
{
    _excelService.GenerateReport(HttpContext, reportDTO);
}

服务

public void GenerateReport(HttpContext httpContext, ReportDTO reportDTO)
{
    IWorkbook workbook;
    workbook = new XSSFWorkbook();
    ISheet excelSheet = workbook.CreateSheet("Test");

    IRow row = excelSheet.CreateRow(0);
    row.CreateCell(0).SetCellValue("Test");
    row.CreateCell(1).SetCellValue("Hello");

    row = excelSheet.CreateRow(1);
    row.CreateCell(0).SetCellValue(1);
    row.CreateCell(1).SetCellValue("World");

    workbook.WriteExcelToResponse(httpContext, GetFileName(reportDTO));
}

扩展方法

public static void WriteExcelToResponse(this IWorkbook book, HttpContext httpContext, string templateName)
{
    var response = httpContext.Response;
    response.ContentType = "application/vnd.ms-excel";
    if (!string.IsNullOrEmpty(templateName))
    {
        var contentDisposition = new Microsoft.Net.Http.Headers.ContentDispositionHeaderValue("attachment");
        contentDisposition.SetHttpFileName(templateName);
        response.Headers[HeaderNames.ContentDisposition] = contentDisposition.ToString();
    }
    book.Write(response.Body);
}

Answer 1

根据您的描述，我建议您可以直接使用内存流并使用FileSult返回Excel文件。

更多详细信息，您可以参考以下示例：

    [HttpPost("/api/report")]
    public ActionResult ExportFile(/*[FromBody] ReportDTO reportDTO*/)
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet excelSheet = workbook.CreateSheet("Test");

        IRow row = excelSheet.CreateRow(0);
        row.CreateCell(0).SetCellValue("Test");
        row.CreateCell(1).SetCellValue("Hello");

        row = excelSheet.CreateRow(1);
        row.CreateCell(0).SetCellValue(1);
        row.CreateCell(1).SetCellValue("World");


        using (var exportData = new MemoryStream())
        {
            workbook.Write(exportData);
            string saveAsFileName = string.Format("Export-{0:d}.xlsx", DateTime.Now).Replace("/", "-");
            byte[] bytes = exportData.ToArray();
            return File(bytes, "application/vnd.ms-excel", saveAsFileName);
        }

    }