欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-08-06 09:23:22
最近在做 MVC 项目的时候遇到项目的导出,下面总结下两种导出到excel 的方法 第一种方法: 将文件写到本地,然后返回这个File 或者返回这个 File 的绝对地址 其中 _hostingEnvironment 可以在全局读取的配置文件的工具类 Globals 中配置 直接上代码: 第二种方法 ......

最近在做 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));
        }