Ant Design Pro V4.0结合.NET WebApi通过NPOI导出Excel功能

Ant Design Pro V4.0结合.NET WebApi通过NPOI导出Excel功能

整体思路
先说思路，通过WebApi生成二进制文件流，返给前端。前端通过Blob接收二进制文件流对象以及文件名，实现导出功能。

WebApi端实现
首先来个简单的，读取后端一个Excel文件并以文件流的形式导出

public HttpResponseMessage GetFileStream()
{
    //var path = @"C:\AZCode\AZEviewApiCodeFirst\AZ.EView\AZ.EView.API\wwwroot\Upload\316e9bf92ce14536b51ca52df57776c2.xlsx";
    var path = @"F:\webroot\AZEViewCodeFirst_api\wwwroot\Upload\d33652f18de840c689143408449cb4bc.xlsx";
    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
    var stream = new FileStream(path, FileMode.Open, FileAccess.Read);
    result.Content = new StreamContent(stream);
    result.Content.Headers.ContentType =
        new MediaTypeHeaderValue("application/octet-stream");
    return result;
}

通过Postman或者前端调用发现，这里的文件流可正常下载Excel，那么接下来考虑通过NPOI导出一个Excel文件流的实现。

[HttpPost]
[Route("TagAddUser/GetFileStream")]
public HttpResponseMessage GetFileStream(int tagid)
{
    //...获取要导出的数据转成DataTable
    NPOIHelper nPOIHelper = new NPOIHelper();
    return nPOIHelper.TableToExcel(dt, Guid.NewGuid().ToString().Replace("-", "") + ".xlsx");
}

NPOI生成文件流方法，这里注意转换二进制

/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file">文件名带扩展名</param>
public HttpResponseMessage TableToExcel(DataTable dt, string file)
{
    IWorkbook workbook;
    string fileExt = Path.GetExtension(file).ToLower();
    if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
    if (workbook == null) { return null; }
    ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);

    //表头  
    IRow row = sheet.CreateRow(0);
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        ICell cell = row.CreateCell(i);
        cell.SetCellValue(dt.Columns[i].ColumnName);
    }

    //数据  
    for (int i = 0; i < dt.Rows.Count; i++)
    {
        IRow row1 = sheet.CreateRow(i + 1);
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            ICell cell = row1.CreateCell(j);
            cell.SetCellValue(dt.Rows[i][j].ToString());
        }
    }

    //创建文件流  
    MemoryStream stream = new MemoryStream();
    //文件写入流（向流中写入字节序列）
    workbook.Write(stream);

    //转成二进制
    //stream.ToArray()

    HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);

    result.Content = new ByteArrayContent(stream.ToArray());
    result.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
    result.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
    {
        FileName = HttpUtility.UrlEncode(file, Encoding.UTF8),
    };

    return result;
}

Ant Design Pro前端实现
前端用Blob去接，并保存，这种方式可以很从容优雅的操作后端返回的文件流

exportExcel = (key: number) => {
  let filename: string = '';
  fetch(`${API_URL}/TagAddUser/GetFileStream?tagid=${key}`, {
    method: 'POST',
  })
    .then(res => {
      var tempfilename = res.headers.get('content-disposition')?.split(';')[1].split('=')[1];
      if (tempfilename === undefined) {
        filename = 'fans.xlsx';
      } else if (tempfilename != undefined) {
        filename = tempfilename;
      }
      return res.blob()
    })
    .catch(error => console.error('Error:', error))
    .then((data) => {
      let blobUrl = window.URL.createObjectURL(data);
      this.download(blobUrl, filename);
    });


}

download = (blobUrl: any, name: string) => {
  const a = document.createElement('a');
  a.style.display = 'none';
  a.download = name;
  a.href = blobUrl;
  const body = document.getElementsByTagName("body")
  body[0].appendChild(a);
  a.click();
  document.body.removeChild(a);
}

Ant Design Table的Column属性绑定导出功能

progressColumns = [
  {
    title: "标签ID",
    dataIndex: 'id',
  },
  {
    title: "标签名",
    dataIndex: 'name',
  },
  {
    title: "粉丝数",
    dataIndex: 'count',
  },
  {
    title: '操作',
    key: 'action',
    render: (text: string, record: TableListItem) => {
      if (record.count > 0) {
        return (
          <span>
            <a onClick={(e) => this.exportExcel(record.id)}>导出粉丝</a>
          </span>
        );
      }
    },
  },

];

总结
React前端其实有很多数据转成.xlsx .csv的类库，也能实现导出Excel等功能，那样就是数据返给前端，大部分的工作由前端来做，但是综合考虑我还是更喜欢后端处理数据多一些，如果数据量是百万级很难想象一个超大的数据包丢给前端去处理会发生什么。

ps：本人是热衷于React的.NET后端程序猿

仅供学习参考，如有侵权联系我删除