.net core webapi 导出excel

.net core webapi 导出excel

nuget安装 EPPlus.Core .

使用起来也很简单,我们构造 ExcelHelper 类，并在controller里面使用。

比如 person类有 id，name，age 3个属性，则 在controller里面这样调用

[Route("ExportExcel")]
[HttpGet]
public IActionResult ExportExcel(){

    var personList=new List<Person>(){
        new Person(){id=1,name="wufan",age=25},
        new Person(){id=2,name="you",age=26}
    }

    var heads=new List<string>() { "编号", "姓名", "年龄"};

    var excelFilePath = ExcelHelper.CreateExcelFromList(personList,heads)

    return File(
        new FileStream(excelFilePath, FileMode.Open),
        "application/octet-stream",
        "ExcelNameHere.xlsx"
    );
}

下面是 ExcelHelper参考代码

using OfficeOpenXml;

public class ExcelHelper
{
    /// <summary>
    /// 
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="dataList">数据</param>
    /// <param name="headers">表头</param>
    /// <returns></returns>
    public static string CreateExcelFromList<T>(List<T> dataList, List<string> headers)
    {
        string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
        if (!Directory.Exists(sWebRootFolder))
        {
            Directory.CreateDirectory(sWebRootFolder);
        }
        string sFileName = [email protected]"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
        var path = Path.Combine(sWebRootFolder, sFileName);
        FileInfo file = new FileInfo(path);
        if (file.Exists)
        {
            file.Delete();
            file = new FileInfo(path);
        }
        using (ExcelPackage package = new ExcelPackage(file))
        {
            //创建sheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
            worksheet.Cells.LoadFromCollection(dataList, true);
            //表头字段
            for (int i = 0; i < headers.Count; i++)
            {
                worksheet.Cells[1, i + 1].Value = headers[i];
            }
            package.Save();
        }
        return path;
    }

}

顺便附带一下 vue axios 前端下载该excel

static exportExcel(params) {
    return request({
        url: '/api/Person/ExportExcel',
        method: 'get',
        params,
        responseType: 'blob'
    })
}

<el-button  @click="handleExportExcel()" >导出excel</el-button>

handleExportExcel() {
  exportExcel(params).then(res => {
    let blob = new Blob([res], { type: res.type })
    if (window.navigator && window.navigator.msSaveOrOpenBlob) {
      window.navigator.msSaveOrOpenBlob(res, `用户列表_${moment().format('YYYYMMDDHHmmss')}.xlsx`);
    }
    else {
      let downloadElement = document.createElement('a')
      let href = window.URL.createObjectURL(blob); //创建下载的链接
      downloadElement.href = href;
      downloadElement.download = `用户列表_${moment().format('YYYYMMDDHHmmss')}.xlsx`; //下载后文件名
      document.body.appendChild(downloadElement);
      downloadElement.click(); //点击下载
      document.body.removeChild(downloadElement); //下载完成移除元素
      window.URL.revokeObjectURL(href); //释放blob对象
    }
  }).catch(err => {
    console.log(err)
  })
}