.net core webapi 导出excel(两种方式EPPLUS、NPOI)，返回下载地址或文件流

香煎三文鱼

已于 2024-12-03 14:18:44 修改

阅读量1.9w

点赞数 2

分类专栏： .net core C# 文章标签： .netcore导出excel api返回文件流

于 2020-02-21 14:05:59 首次发布

本文链接：https://blog.csdn.net/qq_39569480/article/details/104426928

版权

C# 同时被 2 个专栏收录

178 篇文章

订阅专栏

.net core

62 篇文章

订阅专栏

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

.net core webapi 导出excel

nuget安装 EPPlus.

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

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

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

/// <summary>
///  导出
/// </summary>
/// <param name="input"></param>
[Route("api/Test/Export")]
public async Task<dynamic> ExportAsync(TGetListInput input)                         
{
            try
            { 
                List<T实体> Exportlist = GetExportList(input);//获取到list数据
                string FileName = await ExcelHelper<T实体>.OutPutExcel("文件名", Exportlist);//调用封装好的导出方法
                //string DownloadUrl = _configuration["App:SelfUrl"];
                return new { data = $"http://123.123.123.123/export/{FileName}"};

                //导出文件流方式
                //var stream = System.IO.File.OpenRead(Path.Combine(filePath, fileName));
                //var provider = new FileExtensionContentTypeProvider();
                //string contentType = provider.Mappings[Path.GetExtension(fileName)]; 
                //return File(stream, contentType, fileName);  
            }
            catch (Exception e)
            {
                throw new UserFriendlyException(e.Message);
            } 
}


/*[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; 
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace PublicHealth.Helper
{ 
     public class ExcelHelper<T> where T : class, new()
    {
        /// <summary>
        /// 获取类的属性值
        /// </summary>
        /// <param name="obj">类</param>
        /// <param name="property">属性</param>
        /// <returns></returns>
        private static object GetPropertyValue(object obj, string property)
        {
            return obj.GetType().GetProperty(property).GetValue(obj);
        }

        /// <summary>
        /// 获取类的全部属性
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        private static PropertyInfo[] GetProperties(T t)
        {
            PropertyInfo[] properties = t.GetType().GetProperties();
            return properties;
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="fileInfo">FileInfo</param>
        /// <param name="tList">数据</param>
        /// <returns></returns>
        public static async Task<string> OutPutExcel(string FileName, string Merge,List<T> tList)
        {
            string WebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "wwwroot\\export");
            if (!Directory.Exists(WebRootFolder))
            {
                Directory.CreateDirectory(WebRootFolder);
            }
            FileName = $"{FileName}{DateTime.Now.ToString("yyyyMMddhhmmssffff")}.xlsx";
            FileInfo fileInfo = new FileInfo(Path.Combine(WebRootFolder, FileName));
            //指定EPPlus使用非商业化许可证
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (ExcelPackage package = new ExcelPackage(fileInfo))
            {
                //工作簿
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                //实体属性
                PropertyInfo[] properties = GetProperties(new T());
                if (!string.IsNullOrWhiteSpace(Merge))
                {//如果有标题添加标题
                    worksheet.Cells[$"{Merge}"].Merge = true;
                    worksheet.Cells[1, 1].Value = FileName;
                    worksheet.Cells[$"A1"].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                }
                //填充表头
                for (int i = 1; i < properties.Length + 1; i++)
                {
                    //如果有标题那么从第二行开始写列名
                    if (!string.IsNullOrWhiteSpace(Merge)) worksheet.Cells[2, i].Value = properties[i - 1].Name;
                    else worksheet.Cells[1, i].Value = properties[i - 1].Name;
                }

                //填充行(从第二行开始)
                for (int i = 2; i < tList.Count + 2; i++)
                {
                    //填充行内列
                    for (int j = 1; j < properties.Length + 1; j++)
                    {
                        var property = properties[j - 1].Name;
                        worksheet.Cells[i, j].Value = GetPropertyValue(tList[i - 2], property);
                    }
                }

                //列宽自适应
                worksheet.Cells.AutoFitColumns();

                //保存
                await package.SaveAsync();

                return FileName;
            }
        }
    }

}






/*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);
        }
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;//5.0之后的epplus需要指定 商业证书 或者非商业证书。低版本不需要此行代码
        using (ExcelPackage package = new ExcelPackage(file))
        {
            //创建sheet
            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
            worksheet.Cells.LoadFromCollection(dataList, true);

            //worksheet.Cells["A1:J1"].Merge = true;合并a1至j1单元格
            //worksheet.Cells[1, 1].Value = filename;//设置合并单元格的值
            //worksheet.Cells[$"A1"].Style.HorizontalAlignment =  ExcelHorizontalAlignment.Center;//设置单元格值居中
            //表头字段
            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);
                    }
                }


            //复制sheet
            //package.Workbook.Worksheets.Copy("sheet1", "sheet2");
            package.Save();
        }
        return path;//这是返回文件的方式
        //return sFileName ;    //如果用浏览器url下载的方式  这里直接返回生成的文件名就可以了
    }

} */

//基础样式参考
/*
using (var package = new ExcelPackage())
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

//1，赋值
worksheet.Cells[int row, int col].Value = "xxx";
//或者
worksheet.Cells["A1"].Value = "xxx";
//或者
worksheet.SetValue(row,col,value);

//2，单元格合并

//指定开始行，开始列，结束行，结束列
worksheet.Cells[fromRow, fromCol, toRow, toCol].Merge = true;

//行合并
worksheet.Cells["A1:A5"].Merge = true;//合并A列的1-5行

//列合并
worksheet.Cells["A1:G1"].Merge = true;//合并第1行的A-G列

 

//3，样式

worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;//水平居中，全局
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中，全局
worksheet.Cells.AutoFitColumns();//全局
worksheet.Cells.Style.WrapText = true;//自动换行,全局
worksheet.Cells.Style.Font.Name = "宋体";//全局

worksheet.Cells["A1"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中,只针对特定单元格

worksheet.Cells["A1:A5"].Style.VerticalAlignment = ExcelVerticalAlignment.Center;//垂直居中,只针对某范围单元格

 

worksheet.Cells["A5"].Style.TextRotation = 180;//内容旋转

worksheet.Cells["P5"].Style.SetTextVertical(); //文字竖排

 

//调整行高
double rowHeight = 15;
worksheet.Row(1).Height = rowHeight;

//调整列宽
double columnWidth = 50;
worksheet.Column(1).Width = columnWidth;

//自动适应长宽

worksheet.Column(1).BestFit = true;

}
*/

返回文件形式调用

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

三.生成文件后或现有的文件返回文件流

[HttpGet]
        public ActionResult GetExportExcel(string 参数)
        { 
            return File(
                new FileStream(excelFilePath/*这个参数是文件路径*/, FileMode.Open),
                "application/octet-stream",
                "花名册.xlsx"
            );
        }