.net core webapi 导出excel(两种方式EPPLUS、NPOI)

.net core webapi 导出excel(两种方式EPPLUS、NPOI)

一、EPPLUS(此方式较简单，缺点导出为97-2003列数有限制。针对小数据量)

.net core webapi 导出excel

nuget安装 EPPlus.Core .

一种是返回文件形式,一种是返回url方式     注意看清代码注释

使用起来也很简单,我们构造 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"
//下边这种是返回下载连接,可通过浏览器直接访问下载   因为生成的excel文件保存在了程序根目录里的tempExcel文件夹里可直接返回网站路径   注意  如果以这种方式 一定要将存放excel的文件夹放在wwwroot下  也就是网站首页的文件夹
//return Json(new { ok = true, msg = "sucess", data = "http://********/tempExcel/"+ excelFilePath });
    );
} 

下面是 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, "wwwroot\\tempExcel");//如果用浏览器url下载的方式  存放excel的文件夹一定要建在网站首页的同级目录下！！！
        if (!Directory.Exists(sWebRootFolder))
        {
            Directory.CreateDirectory(sWebRootFolder);
        }
        string sFileName = $@"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];
            }
            for (int i = 0; i < headers.Count+1; i++)
                {//删除不需要的列
                    string aa= worksheet.Cells[1,i+1].Value.ToString();
                    if (aa == "总行数")
                    {
                        worksheet.DeleteColumn(i+1);
                    }
                }
            package.Save();
        }
        return path;//这是返回文件的方式
        //return sFileName ;    //如果用浏览器url下载的方式  这里直接返回生成的文件名就可以了
    }
 
} 

返回文件形式调用

返回文件形式需要前端处理,顺便附带一下 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)
  })
} 

返回url方式下载，可直接复制url在浏览器中下载

二、NPOI(较复杂，可以导入模板、列数多)

.net core webapi 导出excel

nuget安装 DotNetCore.NPOI .

返回文件形式,也可根据EPPLUS方式返回url

构造 NpoiExcelUtility类，里边是生成excel的方法

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
 
namespace ***.Web.Host.Model
{
    public class NpoiExcelUtility
    {
        private string _xlsPath = string.Empty;
        private XSSFWorkbook _workBook = null;//.xlsx
        //private HSSFWorkbook _workBook = null;//.xls如果需要这种格式可用HSSFWorkbook
 
 
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="xlsPath">xls保存路径</param>
        /// <param name="TempletFileName">xls模板路径</param>
        public NpoiExcelUtility(string xlsPath, string TempletFileName)
        {
            _xlsPath = this.CheckFilePath(xlsPath);
 
            FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
            _workBook = new XSSFWorkbook(file);
        }
 
        /// <summary>
        /// 将DataTable保存到sheet里
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="sheet"></param>
        private void DataTableToExcel(DataTable dt, ISheet sheet)
        {
            ICellStyle style = _workBook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Left;
            style.VerticalAlignment = VerticalAlignment.Center;
 
            ICellStyle colStyle = _workBook.CreateCellStyle();
            colStyle.Alignment = HorizontalAlignment.Left;
            colStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font = _workBook.CreateFont();
            font.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
            colStyle.SetFont(font);
 
 
            //列名
            //IRow row = sheet.CreateRow(0);
            //for (int i = 0; i < dt.Columns.Count; i++)
            //{
            //    sheet.SetDefaultColumnStyle(i, style);
 
            //    ICell cell = row.CreateCell(i);
            //    cell.SetCellValue(dt.Columns[i].ToString());
 
            //    cell.CellStyle = colStyle;
            //}
            //内容
            //var headerRow = (HSSFRow)sheet.GetRow(0);
            var headerRow = (XSSFRow)sheet.GetRow(0);
            for (int i = 1; i <= dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 6);//这里是从第几行开始写数据，我的模板前几行都是样式  所以从第六行开始写格式
                row.Height = 50 * 20;
                //ICell numcell = row.CreateCell(0);
                //numcell.SetCellValue(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    object obj = dt.Rows[i - 1][j];
                    if (obj != null)
                    {
                        //string ColumnName = dt.Columns[j].ToString();
                        //var _Column = headerRow.Cells.Find(t => !string.IsNullOrEmpty(t.StringCellValue) && t.ToString().ToLower() == ColumnName.ToLower());
                        //ICell cell = row.CreateCell(j + 1);             
                        //if (_Column != null)
                        //{
                            ICell cell = row.CreateCell(j/*_Column.ColumnIndex*/);
                            if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
                            {
                                cell.SetCellValue(Convert.ToDouble(obj));
                            }
                            else if (obj is bool)
                            {
                                cell.SetCellValue((bool)obj);
                            }
                            else
                            {
                                cell.SetCellValue(obj.ToString());
                            }
                       // }
                    }
                }
            }
        }
 
        /// <summary>
        /// 保存Excel
        /// </summary>
        public void SaveExcel()
        {
            FileStream file = new FileStream(_xlsPath, FileMode.Create);
            _workBook.Write(file);
            file.Close();
        }
 
        /// <summary>
        /// 创建Sheet
        /// </summary>
        /// <param name="sheetName">sheet名称</param>
        /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
        public void CreatExcelSheet(string sheetName, DataTable tbl)
        {
            string sName = this.CheckSheetName(sheetName);
 
            int rowMax = 65535;
            int intNum = tbl.Rows.Count / rowMax;
            int remainder = tbl.Rows.Count % rowMax;
 
            for (int i = 0; i < intNum; i++)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < 65535; j++)
                {
                    int rowIndex = i * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (i + 1);
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
            if (remainder > 0)
            {
                DataTable subTbl = tbl.Clone();
                for (int j = 0; j < remainder; j++)
                {
                    int rowIndex = intNum * rowMax + j;
                    subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                }
                string subSheet = sName + "_" + (intNum + 1);
                if (intNum < 1)
                {
                    subSheet = sName;
                }
                //ISheet sheet = _workBook.CreateSheet(subSheet);
                ISheet sheet = _workBook.GetSheetAt(0);
                this.DataTableToExcel(subTbl, sheet);
            }
        }
 
        /// <summary>
        /// 检查sheet名称是否合法，并去掉不合法字符
        /// </summary>
        /// <param name="sheetName"></param>
        private string CheckSheetName(string sheetName)
        {
            string rlt = sheetName;
            string[] illegalChars = { "*", "?", "\"", @"\", "/" };
            for (int i = 0; i < illegalChars.Length; i++)
            {
                rlt = rlt.Replace(illegalChars[i], "");
            }
            return rlt;
        }
 
        /// <summary>
        ///  检查xls路径是否合法，并去掉不合法字符
        /// </summary>
        /// <param name="filePath"></param>
        private string CheckFilePath(string filePath)
        {
            string dir = Path.GetDirectoryName(filePath);
            string fileName = Path.GetFileNameWithoutExtension(filePath);
            string ext = Path.GetExtension(filePath);
 
            string[] illegalChars = { ":", "*", "?", "\"", "<", ">", "|", @"\", "/" };
            for (int i = 0; i < illegalChars.Length; i++)
            {
                fileName = fileName.Replace(illegalChars[i], "");
            }
            string rlt = Path.Combine(dir, fileName + ext);
            return rlt;
        }
    }
}

list转datatable(数据源需要datatable格式，如果你的数据源是datatable可省略此部)

/**/
        /// <summary>
        /// 将泛型集合类转换成DataTable
        /// </summary>
        /// <typeparam name="T">集合项类型</typeparam>
        /// <param name="list">集合</param>
        /// <returns>数据集(表)</returns>
        public static DataTable ToDataTable<T>(List<T> list)
        {
            return ListToDataTable<T>(list);
        }
 
        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="list">泛类型集合</param>
        /// <returns></returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //检查实体集合不能为空
            if (entitys == null || entitys.Count < 1)
            {
                    return null;
                    //throw new Exception("需转换的集合为空");
            }
            //取出第一个实体的所有Propertie
            //Type entityType = entitys[0].GetType();
            PropertyInfo[] entityProperties = entitys[0].GetType().GetProperties();
 
            //生成DataTable的structure
            //生产代码中，应将生成的DataTable结构Cache起来，此处略
            DataTable dt = new DataTable("temp");
            for (int i = 0; i < entityProperties.Length; i++)
            {
                //dt.Columns.Add(entityProperties[i].Name, entityProperties[i].PropertyType);
                dt.Columns.Add(entityProperties[i].Name);
            }
            //将所有entity添加到DataTable中
            foreach (object entity in entitys)
            {
                检查所有的的实体都为同一类型
                //if (entity.GetType() != entityType)
                //{
                //    throw new Exception("要转换的集合元素类型不一致");
                //}
                object[] entityValues = new object[entityProperties.Length];
                for (int i = 0; i < entityProperties.Length; i++)
                {
                    entityValues[i] = entityProperties[i].GetValue(entity, null);
                }
                dt.Rows.Add(entityValues);
            }
            return dt;
        }

在controller里面这样调用 

[Route("ExportExcel/月报表.xlsx")]
        [HttpGet]
        public IActionResult GetMedicalMonthlyReportExcel()
        {
//var TempletFilePath = @"D:\谷歌下载\ASPNETCore操作Excel\ASPNETCoreExcel\Template";//模板地址
            var ExportFilePath = "D:";//导出后存放的地址           
            //string TempletFileName = string.Format("{0}\\调查表.xls", TempletFilePath);
            string ExportFileName = string.Format("{0}\\调查表_{1}.xlsx", ExportFilePath, DateTime.Now.ToString("yyyy年MM月dd日hh时mm分ss秒"));//生成后存放的地址 和文件名
 
            string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");模板地址  这里我把模板存到了程序执行路径
            string TempletFileName = string.Format("{0}\\报表.xlsx", sWebRootFolder);
 
            var _NpoiExcelUtility = new NpoiExcelUtility(ExportFileName, TempletFileName);
            List<T月报表导出> list=_StatisticalAnalysis.GetMedicalMonthlyReport("1");//这里是我的数据源
            DataTable dt = ToDataTable(list);//list转datatable
             if (dt!=null)
            {
                dt.Columns.Remove("序号"); //删除列
                dt.Columns.Remove("总行数");
                _NpoiExcelUtility.CreatExcelSheet("调查表", dt);//生成数据   这里需要注意  如果datatable没值会报错  所以要判断
            }  
            _NpoiExcelUtility.SaveExcel();
            var stream = System.IO.File.OpenRead(ExportFileName);
            return File(stream, "application/vnd.android.package-archive", Path.GetFileName(ExportFileName));
}