• mvc npoi将List<实体>导出excel的最简单方法


    一、最屌丝的方法。只是临时导数据用的。方便。最基本的方法,  

         [HttpGet]
            [Route("ExportEnterprise")]
            public BaseResponse ExportEnterprise()
            {
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("onesheet");
                IRow row0 = sheet.CreateRow(0);
                row0.CreateCell(0).SetCellValue("顺序号");
                row0.CreateCell(1).SetCellValue("企业名");
                row0.CreateCell(2).SetCellValue("行业门类");
                row0.CreateCell(3).SetCellValue("行业大类");
                row0.CreateCell(4).SetCellValue("经营属地");
                row0.CreateCell(5).SetCellValue("法人");
                row0.CreateCell(6).SetCellValue("法人手机");
                row0.CreateCell(7).SetCellValue("法人固话");
    
                var enterprises = _enterpriseService.GetEnterprisesOfTest().ToList();
                var lineNo = 1;
                foreach (var enterprise in enterprises)
                {
                    IRow row = sheet.CreateRow(lineNo);
                    row.CreateCell(0).SetCellValue(lineNo);
                    row.CreateCell(1).SetCellValue(enterprise.EnterpriseName);
                    var doorDescr = _industryCategoryService.GetDescriptionBy(enterprise.IndustryCategoryCode);
                    row.CreateCell(2).SetCellValue(doorDescr);
                    var industryGeneraDescr = _industryCategoryService.GetDescriptionBy(enterprise.IndustryGeneraCode);
                    row.CreateCell(3).SetCellValue(industryGeneraDescr);
                    row.CreateCell(4).SetCellValue(_administrativeDivisionService.GetDescriptionBy(enterprise.BusinessAddressDivisonCode));
                    row.CreateCell(5).SetCellValue(enterprise.LegalPersonName);
                    row.CreateCell(6).SetCellValue(enterprise.LegalPersonPhone);
                    row.CreateCell(7).SetCellValue(enterprise.LegalPersonFixedPhone);
                             lineNo++;
                }
    
                //创建流对象并设置存储Excel文件的路径
                using (FileStream url = new FileStream(HttpContext.Current.Server.MapPath("/App_Data/test.xls"), FileMode.OpenOrCreate, FileAccess.ReadWrite))
                {
                    //导出Excel文件
                    workbook.Write(url);
                };
                return Success(new BaseResponse());
            }

    二、高大上的通用方法

    在baseController里写个通用方法,利用反射原理,获取对象的每一个属性的DisplayName作表头

    /// <summary>
            /// 
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="fileBaseName">不带后缀</param>
            /// <param name="datas"></param>
            /// <returns></returns>
            public ActionResult ExportToExcel<T>(string fileBaseName, List<T> datas) where T: ExcelModel
            {
                MemoryStream ms = new MemoryStream();
                IWorkbook workbook = new HSSFWorkbook();
                ISheet sheet = workbook.CreateSheet("导出数据");
                IRow headerRow = sheet.CreateRow(0);
    
                int rowIndex = 1, piIndex = 0;
                Type type = typeof(T);
                PropertyInfo[] pis = type.GetProperties();
                int pisLen = pis.Length;
                PropertyInfo pi = null;
                string displayName = string.Empty;
                while (piIndex < pisLen)
                {
                    pi = pis[piIndex];
                    var pName = pi.GetCustomAttribute<DisplayNameAttribute>();
                    displayName = pName?.DisplayName??string.Empty;
                    if (!displayName.Equals(string.Empty))
                    {//如果该属性指定了DisplayName,则输出
                        try
                        {
                            headerRow.CreateCell(piIndex).SetCellValue(displayName);
                        }
                        catch (Exception)
                        {
                            headerRow.CreateCell(piIndex).SetCellValue("");
                        }
                    }
                    piIndex++;
                }
                foreach (T data in datas)
                {
                    piIndex = 0;
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    while (piIndex < pisLen)
                    {
                        pi = pis[piIndex];
                        try
                        {
                            dataRow.CreateCell(piIndex).SetCellValue(pi.GetValue(data, null).ToString());
                        }
                        catch (Exception)
                        {
                            dataRow.CreateCell(piIndex).SetCellValue("");
                        }
                        piIndex++;
                    }
                    rowIndex++;
                }
                workbook.Write(ms);
                ms.Seek(0, SeekOrigin.Begin);
                return File(ms, "application/vnd.ms-excel", $"{fileBaseName}.xls");
            }

    对象值 需要加displayName注解

      public class ActivityGradeExcelModel: ExcelModel
        {
            [DisplayName("月份")]
            [DisplayFormat(DataFormatString = "yyyy-MM")]
            public DateTime ConductDate { get; set; }
            [DisplayName("活动")]
            public string ActivityName { get; set; }
            [DisplayName("姓名")]
            public string StudentName { get; set; }
            [DisplayName("分数")]
            public decimal Score { get; set; }
            [DisplayName("班级")]
            public string SchoolClassName { get; set; }
            [DisplayName("学号")]
            public string StudyNo { get; set; }
            [DisplayName("专业")]
            public string CollegeMajor { get; set; }
            [DisplayName("学期")]
            public int ConductYear { get; set; }
     
        }

    Action中代码

      var gradeModels = query.OrderByDescending(m => m.ConductDate).ThenBy(m => m.ActivityName)
                        .ThenBy(m => m.SchoolClassName)
                        .Select(m => new ActivityGradeExcelModel()
                        {
                            ConductDate = m.ConductDate,
                            ActivityName = m.ActivityName,
                            StudentName = m.StudentName,
                            Score = m.Score,
                            SchoolClassName = m.SchoolClassName,
                            StudyNo = m.StudyNo,
                            CollegeMajor = m.CollegeMajor,
                            ConductYear = m.ConductYear
                        }).ToList();
                    var fileBaseName = $"活动成绩表{DateTime.Now.ToString("yyyyMMdd")}";
                    return ExportToExcel(fileBaseName, gradeModels);
  • 相关阅读:
    分布式配置 SSH 免密登陆
    转载--宏观认识大数据圈
    转载--存储是怎样炼成的
    转载--关于hdfs
    绕不开的hadoop
    sqoop 使用
    Excel VBA解读(54):排序——Sort方法
    MSSQL附加数据库时提示以下错误: 无法打开物理文件“***.mdf”。操作系统错误 5:“5(拒绝访问。)”。 (Microsoft SQL Server,错误: 5120)
    Delphi Code Editor 之 编辑器选项
    解决StrToDateTime()不是有效日期类型的问题
  • 原文地址:https://www.cnblogs.com/taoshengyujiu/p/9139458.html
Copyright © 2020-2023  润新知