NET - .NET Core WebAPI + Vue + Axios 导出Excel / CSV

这两种方法目前如果遇到0开头的数据，会默认把0去掉，可以考虑如果StartsWith(“0”)，则在前面加个单引号（即’）
不推荐Excel导出，因为Excel2003一个Sheet最多导出65535条，Excel2007是10万4000多条，数据量大会报错，所以推荐使用CSV导出

 

• 导出 Excel

/// <summary>
/// 将 DataTable 转换为 Excel
/// 需在 nuget 安装 DotNetCore.NPOI
/// </summary>
/// <param name="table">数据表</param>
/// <param name="sheetName">sheet名称</param>
/// <returns></returns>
public static byte[] DataTableToExcel(DataTable table, string title = null, string sheetName = "Sheet")
{
    try
    {
        IWorkbook workbook = new XSSFWorkbook();
        ISheet sheet = workbook.CreateSheet(sheetName);
        int cellsIndex = 0;
        // 标题
        if (!string.IsNullOrEmpty(title))
        {
            // 填充数据
            IRow cellsTitle = sheet.CreateRow(0);
            cellsTitle.CreateCell(0).SetCellValue(title);
            // 合并单元格
            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, table.Columns.Count - 1));
            cellsIndex = 2;
        }
        // 填充表头
        IRow cellsHeader = sheet.CreateRow(cellsIndex);
        for (int i = 0; i < table.Columns.Count; i++)
        {
            cellsHeader.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
        }
        // 填充数据
        cellsIndex += 1;
        foreach (DataRow dr in table.Rows)
        {
            IRow row = sheet.CreateRow(cellsIndex);
            for (int i = 0; i < table.Columns.Count; i++)
            {
                row.CreateCell(i).SetCellValue(StrHelper.ToString(dr[i]));
            }
            cellsIndex++;
        }
        byte[] buffer = null;
        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            buffer = ms.GetBuffer();
            ms.Close();
        }
        return buffer;
    }
    catch (Exception)
    {
        return null;
    }
}	

 

• 导出 CSV

/// <summary>
/// 将 DataTable 转换为 CSV
/// </summary>
/// <param name="table">数据表</param>
/// <returns></returns>
public static Stream DataTableToCsv(DataTable table)
{
    try
    {
        StringBuilder builder = new StringBuilder();
        DataColumn column;
        int iColCount = table.Columns.Count;
        // 处理表头
        for (int i = 0; i < iColCount; i++)
        {
            if (i != 0) builder.Append(",");
            builder.Append("\"" + table.Columns[i].ColumnName + "\"");
        }
        builder.AppendLine();
        // 处理内容
        foreach (DataRow row in table.Rows)
        {
            for (int i = 0; i < iColCount; i++)
            {
                column = table.Columns[i];
                if (i != 0) builder.Append(",");
                if (Convert.IsDBNull(row[column])) builder.Append("\"\"");
                //else if (row[column].ToString().StartsWith("0")) builder.Append("\"'" + row[column].ToString() + "\"");
                else builder.Append("\"" + row[column].ToString() + "\"");
            }
            builder.AppendLine();
        }
        byte[] bytes = Encoding.GetEncoding("GB2312").GetBytes(builder.ToString());
        Stream stream = new MemoryStream(bytes);
        return stream ;
    }
    catch (Exception)
    {
        return null;
    }
}

 

• .Net Core WebAPI Controller

/// <summary>
/// 导出 Excel
/// </summary>
/// <returns></returns>
[HttpGet("exportExcel")]
public async Task<IActionResult> ExportExcel()
{
    DataTable dataTable = new DataTable(); // 此处准备 dataTable 数据
    
    string name = "数据详情";
    byte[] bytes = Utils.DataTableToExcel(dataTable, name, name);
    return File(bytes, "application/octet-stream", $"{name}_{DateTime.Now:yyyyMMddHHmmssfff}.xlsx");
}

/// <summary>
/// 导出 CSV
/// </summary>
/// <returns></returns>
[HttpGet("exportCSV")]
public async Task<IActionResult> ExportCSV()
{
    DataTable dataTable = new DataTable(); // 此处准备 dataTable 数据

    System.IO.Stream stream = Utils.DataTableToCsv(dataTable);
    return File(stream, "application/octet-stream", $"数据详情_{DateTime.Now:yyyyMMddHHmmssfff}.csv");
}

 

• .Net Core Startup.cs 配置

这里配置 .WithExposedHeaders("Content-Disposition") 是因为 Vue Axios response.headers 中默认获取不到 content-disposition 属性

启用跨域请求（CORS）

public void ConfigureServices(IServiceCollection services)
{
	// ......
	
    // 处理 api 请求跨域
    services.AddCors(options =>
    {
        options.AddPolicy("cors",
            builder =>
            {
                builder.AllowAnyMethod()
                .AllowAnyOrigin()
                .AllowAnyHeader()
                .WithExposedHeaders("Content-Disposition");
            });
    });

	// ......
}

 

• Vue Axios

service.interceptors.response.use(
  response => {
    // blob 直接返回文件流数据
    if (response.config.responseType === 'blob') {
      console.log(response.headers);
      const fileName = decodeURI(
      	// response.headers['content-disposition'] 默认获取不到，需要在 .Net Core Startup.cs 中进行配置
        response.headers['content-disposition'].split('filename*=UTF-8\'\'')[1]
      )
      return Promise.resolve({ data: response.data, fileName: fileName })
    }

    // ......
  },
  error => {
    return Promise.reject(error)
  }
)

 

• Vue 请求接口

export function exportCSV(param) {
    return request({
        url: '/controller/exportCSV',
        method: 'get',
        params: { ...param },
        responseType: 'blob'
    })
}

 

• Vue 下载实现

export default {
  data() {
    return {
      listQuery: {},
    };
  },
  created() {},
  methods: {
    // 导出报表
    onExport() {
      exportCSV(this.listQuery).then((response) => {
        let blob = new Blob([response.data], { type: response.data.type });
        // 针对于 IE 浏览器的处理, 因部分 IE 浏览器不支持 createObjectURL
        if (window.navigator && window.navigator.msSaveOrOpenBlob) {
          window.navigator.msSaveOrOpenBlob(blob, response.fileName);
        } else {
          var downloadElement = document.createElement("a");
          var href = window.URL.createObjectURL(blob); // 创建下载的链接
          downloadElement.href = href;
          downloadElement.download = response.fileName; // 下载后文件名
          document.body.appendChild(downloadElement);
          downloadElement.click(); // 点击下载
          document.body.removeChild(downloadElement); // 下载完成移除元素
          window.URL.revokeObjectURL(href); // 释放掉 blob 对象
        }
      });
    },
  },
};