NPOI导出

1、在服务层安装NPOI包

 2、直接在服务层创建.cs文件 using引用NPOI

3、在Dto中加入Description特性主要用于枚举和属性

 4、创建工作簿设置表格中样式以及格式等操作

public static void ExportToExcel<T> (List<T> data, string head, string sheetName)
        {
            IWorkbook workbook = new HSSFWorkbook();
            //设置工作簿的名称
            sheetName = string.IsNullOrEmpty(sheetName) ? "sheet1" : sheetName;
            //创建一个工作簿
            ISheet sheet = workbook.CreateSheet(sheetName);

            //全局索引
            int gloal_index = 0;

            //类型Type
            System.Reflection.PropertyInfo[] oProps = null;

            foreach (T entity in data)
            {
                Type type = entity.GetType();
                    oProps = type.GetProperties().Where(m => m.GetCustomAttributes<DescriptionAttribute>().Count() > 0).ToArray();
            }
            if (gloal_index == 0)
            {
                #region 标题信息
                var row = sheet.CreateRow(0);

                //var cell = row.CreateCell(0);                 
                //sheet1.AddMergedRegion(region);
                //设置第1行高度
                row.Height = 20 * 20;
                //创建第1行第1列
                var celltitle = row.CreateCell(0);
                //设置第1行第1列格式
                celltitle.CellStyle = Getcellstyle(workbook, "head");
                //设置第1行第1列内容
                celltitle.SetCellValue(head);
                //合并单元格
                CellRangeAddress region = new CellRangeAddress(0, 0, 0, oProps.Length - 1);
                sheet.AddMergedRegion(region);
                #endregion

                #region 表中字段
                //创建第2行
                IRow row1 = sheet.CreateRow(1);
                //设置高度
                row1.Height = 20 * 20;
                //columnt_index是列的索引
                int columnt_index = 0;
                foreach (PropertyInfo item in oProps)
                {
                    //获取T的字段名称
                    string name = item.Name;
                    //获取T的字段名称的描述
                    //string desc = "";
                    //if (name == "Id")
                    //{
                    //    desc = "ID";
                    //}
                    //else
                    //{
                    //    desc = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute))).Description;
                    //}

                    string desc = ((DescriptionAttribute)Attribute.GetCustomAttribute(item, typeof(DescriptionAttribute),true)).Description;

                    //string desc = name;

                    //创建第2行的第columnt_index列
                    ICell icell1top = row1.CreateCell(columnt_index);
                    //设置第2行的第columnt_index列的格式
                    icell1top.CellStyle = Getcellstyle(workbook, "");
                    //设置第2行的第columnt_index列的内容
                    if (!string.IsNullOrEmpty(desc))
                    {
                        icell1top.SetCellValue(desc);
                    }
                    else
                    {
                        icell1top.SetCellValue(name);
                    }
                    //设置第2行的第columnt_index列的宽度
                    sheet.SetColumnWidth(columnt_index, (int)((15 + 0.72) * 256));
                    columnt_index++;
                }
                #endregion

            }
            gloal_index = 2;
            foreach (T entity in data)
            {
                #region 这里是List<T>具体内容      
                //创建第gloal_index行,根据数据条数进行创建
                IRow row_zs = sheet.CreateRow(gloal_index);
                //根据字段创建列数
                int column_index = 0;
                foreach (PropertyInfo pi in oProps)
                {
                    //创建第gloal_index行的第columnt_index列
                    ICell icell1top = row_zs.CreateCell(column_index);
                    //设置第gloal_index行的第columnt_index列格式
                    icell1top.CellStyle = Getcellstyle(workbook, "");
                    //获取en字段值
                    string v_value = pi.GetValue(entity, null) == null ? "" : pi.GetValue(entity, null).ToString();
                    //设置第gloal_index行的第columnt_index列的内容
                    icell1top.SetCellValue(v_value);

                    column_index++;
                }
                gloal_index++;
                #endregion
            }
            //using (FileStream file = new FileStream(@"E:\xlsx\工作簿1.xls", FileMode.Create))
            //{
            //    workbook.Write(file);
            //    workbook.Close();
            //    file.Close();
            //    file.Dispose();
            //}
            var ExcelName = DateTime.Now.ToString("yyyyMMddHHmmss"); //获取当前时间
            string path = System.IO.Directory.GetCurrentDirectory();
            using (FileStream file = new(@$"{path}\wwwroot\Excel\{head}{DateTime.Now:yyyyMMddHHmmss}.xls", FileMode.Create))//根据当前时间创建文件名+日期格式的文件夹
            {
                workbook.Write(file,true);
            }
        }
        /// <summary>
        /// 格式设置
        /// </summary>
        static ICellStyle Getcellstyle(IWorkbook workbook, string type)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            //定义字体  
            IFont font = workbook.CreateFont();
            font.FontName = "微软雅黑";
            //水平对齐  
            cellStyle.Alignment = HorizontalAlignment.Left;
            //垂直对齐  
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            //自动换行  
            cellStyle.WrapText = true;
            //缩进
            cellStyle.Indention = 0;

            switch (type)
            {
                case "head":
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
                default:
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    break;
            }
            return cellStyle;
        }