.NET CORE 2.1 导出excel文件的两种方法

最近在做 MVC 项目的时候遇到项目的导出，下面总结下两种导出到excel 的方法

第一种方法： 将文件写到本地，然后返回这个File 或者返回这个 File 的绝对地址

　　 其中  _hostingEnvironment  可以在全局读取的配置文件的工具类 Globals 中配置

 　　直接上代码：

private IHostingEnvironment _hostingEnvironment;
        public XlsxController(IHostingEnvironment hostingEnvironment)
        {
            _hostingEnvironment = hostingEnvironment;
        }
        public IActionResult ExportData(int programmeId)
        {
            //获取方案名称
            var programmeName = ProgrammeService.GetProgrammeNameById(programmeId);
            //获取列表内容
            var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId);
            var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel
            {
                UserName = a.UserName,
                Phone = a.Phone,
                EnrolTime = a.EnrolTimeStr
            }).ToList();

            var sWebRootFolder = _hostingEnvironment.WebRootPath;

            var sFileName = $"XXX-{programmeName}.xlsx";

            var file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));
            using (var package = new ExcelPackage(file))
            {

                //如果之前有同名的文件先删除然后重新创建 
                var count = package.Workbook.Worksheets.Count; if (count > 0)
                {
                    for (var i = 0; i < count; i++)
                    {
                        package.Workbook.Worksheets.Delete(i + 1);
                    }
                    package.File.Delete();
                }

                // 添加worksheet 
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");

                //添加头 
                worksheet.Cells[1, 1].Value = "姓名";
                worksheet.Cells[1, 2].Value = "手机号";
                worksheet.Cells[1, 3].Value = "报名时间";

                //添加值 
                for (var i = 0; i < data.Count; i++)
                {
                    worksheet.Cells[$"A{i + 2}"].Value = data[i].UserName;
                    worksheet.Cells[$"B{i + 2}"].Value = data[i].Phone;
                    worksheet.Cells[$"C{i + 2}"].Value = data[i].EnrolTime;
                }
                package.Save();
            }
            var returnFile = File(sFileName, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            returnFile.FileDownloadName = sFileName;
            return returnFile;
        }

 

第二种方法： 用写流文件的方法 将文件写入浏览器

 

　　这里首先我们需要一个导出的工具类 如下：

    /// <summary>
    /// 公共工具类
    /// </summary>
    public class CommonHelper
    {

        //获取列名委托方法
        public delegate string GetColumnName(string columnName);

        #region 导入导出Excel相关

        /// <summary>
        /// 将泛类型集合List类转换成DataTable
        /// </summary>
        /// <param name="list">泛类型集合</param>
        /// <returns>返回转换后的DataTable</returns>
        public static DataTable ListToDataTable<T>(List<T> entitys)
        {
            //生成DataTable的structure
            var dt = new DataTable();
            try
            {
                //检查泛型实体是否为空
                if (entitys == null || entitys.Count < 1)
                {
                    return dt;
                }
                //取出第一个实体的所有Propertie
                var entityType = entitys[0].GetType();
                var entityProperties = entityType.GetProperties();
                for (var i = 0; i < entityProperties.Length; i++)
                {
                    dt.Columns.Add(entityProperties[i].Name);
                }
                //将所有entity添加到DataTable中
                foreach (object entity in entitys)
                {
                    //检查所有的的实体都为同一类型
                    if (entity.GetType() != entityType)
                    {
                        throw new Exception("要转换的集合元素类型不一致");
                    }
                    var entityValues = new object[entityProperties.Length];
                    for (var i = 0; i < entityProperties.Length; i++)
                    {
                        entityValues[i] = entityProperties[i].GetValue(entity, null);
                    }
                    dt.Rows.Add(entityValues);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
            return dt;
        }

        /// <summary>
        /// 将dataTable转换为Excel字节流
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="getColumnName"></param>
        /// <returns></returns>
        public static byte[] GetExcelForXLSX(DataTable dt, GetColumnName getColumnName)
        {
            var xssfworkbook = new XSSFWorkbook();
            var sheet = xssfworkbook.CreateSheet("Sheet");
            //表头
            var row = sheet.CreateRow(0);

            for (var i = 0; i < dt.Columns.Count; i++)
            {
                var cell = row.CreateCell(i);
                //列名称,数据库中字段
                var columnName = dt.Columns[i].ColumnName;
                var convertColumnName = getColumnName(columnName);
                cell.SetCellValue(convertColumnName);
            }

            //数据
            for (var i = 0; i < dt.Rows.Count; i++)
            {
                var row1 = sheet.CreateRow(i + 1);
                for (var j = 0; j < dt.Columns.Count; j++)
                {
                    var cell = row1.CreateCell(j);
                    cell.SetCellValue(dt.Rows[i][j].ToString());
                }
            }
            //转为字节数组
            var stream = new MemoryStream();
            xssfworkbook.Write(stream);
            var buf = stream.ToArray();
            return buf;
        }

       #endregion
    }

　　

　　然后我们就可以导出了

 

 　　　　/// <summary>
        /// 表格列名称
        /// </summary>      
        private static string GetColumnNameForDetail(string columnName)
        {
            switch (columnName)
            {

                case "UserName":
                    return CommonConst.Column_Programme_Detail_UserName;
                case "Phone":
                    return CommonConst.Column_Programme_Detail_Phone;
                case "EnrolTime":
                    return CommonConst.Column_Programme_Detail_EnrolTime;
                default:
                    return String.Empty;
            }
        }

        /// <summary>
        /// 导出excel
        /// </summary>
        [HttpGet]
        public void ExportData(int programmeId)
        {
            //获取方案名称
            var programmeName = ProgrammeService.GetProgrammeNameById(programmeId);
            //获取列表内容
            var dataFromDb = ProgrammeService.GetProgrammeStatisticEnrolmentExportList(programmeId);
            var data = dataFromDb.Select(a => new ProgrammeStatisticsEnrolmentDetailsExportViewModel
            {
                UserName = a.UserName,
                Phone = a.Phone,
                EnrolTime = a.EnrolTimeStr
            }).ToList();

            var sFileName = string.Format(CommonConst.Export_Programme_Detail_Excel_Name, programmeName);
            var dataTable = CommonHelper.ListToDataTable(data);
            var result = CommonHelper.GetExcelForXLSX(dataTable, GetColumnNameForDetail);
            Response.ContentType = ResponseConfigure.ContentTypeExcel;
            SetResponseHeaderForDetail(sFileName);
            Response.Body.Write(result);
            Response.Body.Flush();
            Response.Body.Close();
        }

        /// <summary>
        /// 设定导出的标头内容
        /// </summary>
        /// <param name="fileName">导出的文件名</param>
        private void SetResponseHeaderForDetail(string fileName)
        {
            Response.Headers.Add(ResponseConfigure.ContentDisposition,
                ResponseConfigure.Attachment + HttpUtility.UrlEncode(fileName));
        }