.net C# NPOI导出Excel简单封装记录

Dto

using NPOI.HSSF.UserModel;

namespace Commons
{
    /// <summary>  
    /// 列表数据转Excel工具 
    /// </summary>  
    public class ListToExcel
    {
        /// <summary>
        /// excel工作薄
        /// </summary>
        public HSSFWorkbook wb { get; set; }
        /// <summary>
        /// 状态
        /// </summary>
        public bool status { get; set; }
    }

    public class ListToExcelThead
    {
        /// <summary>
        /// 标题
        /// </summary>
        public string[] title { get; set; }

        /// <summary>
        /// 需要合并的标题
        /// </summary>
        public string[] mergeTitle { get; set; }

        /// <summary>
        /// 需要合并的标题起始位置
        /// </summary>
        public int[][] mergeColumn { get; set; }

        /// <summary>
        /// 字段
        /// </summary>
        public string[] field { get; set; }

        /// <summary>
        /// 表格宽度
        /// </summary>
        public int[] ColumnWidth { get; set; }

        /// <summary>
        /// 需要枚举判断的字段
        /// </summary>
        public string[] enumList { get; set; }
    }

}

实现方法

using System;
using System.Collections.Generic;
using System.Linq;

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace Commons.Helpers
{
    /// <summary>  
    /// 列表数据转Excel工具 
    /// </summary>  
    public class ListToExcelHelper
    {
        /// <summary>
        /// 列表数据添加Excel。
        /// </summary>
        /// <param name="HSSFWorkbook">excel工作薄</param>  
        /// <param name="pageData">数据原名</param>  
        /// <param name="listData">需要插入的字段</param>  
        /// <param name="sheetName">标题名称</param>  
        /// <returns></returns>
        public static ListToExcel ListToExcel<T>( List<T> pageData, ListToExcelThead thead, string sheetName )
        {
            ListToExcel model = new ListToExcel();

            //  没传值
            if ( pageData == null && pageData.Count == 0 || thead == null&& thead?.title == null && thead?.field == null)
            {
                model.status = false;
                return model;
            }
            if(string.IsNullOrWhiteSpace(sheetName))
            {
                sheetName = "Sheet1";
            }

            int len = thead.title?.Length ?? 0; // 一共添加多少个字段
            int pageDataRow = 2;// 默认其实第二行

            //创建excel工作薄  
            HSSFWorkbook wb = new HSSFWorkbook();
            
            try
            {
                #region 设置单元格样式
                //创建excel 单元格式1
                ICellStyle cellStyle = wb.CreateCellStyle();
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                //水平对齐
                cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //垂直对齐
                cellStyle.VerticalAlignment = VerticalAlignment.Center;
                //设置字体
                IFont font = wb.CreateFont();
                font.FontHeightInPoints = 10;
                font.FontName = "宋体";
                cellStyle.SetFont(font);

                #endregion

                //创建表
                ISheet sh = wb.CreateSheet(sheetName);

                #region 设置列的宽度
                for (int i = 0; i < len; i++)
                {
                    if (i == 0)  // 第一个宽度小一点
                    {
                        sh.SetColumnWidth(i, 10 * 256);
                    }
                    else
                    {
                        int w = (i < thead.ColumnWidth?.Length && thead.ColumnWidth[i] > 0) ? thead.ColumnWidth[i]  : 18;
                        sh.SetColumnWidth(i, w * 256);

                    }
                }
                #endregion

                #region 创建第一行 标题名称
                //创建第一行
                IRow row = sh.CreateRow(0);
                ICell rowICell = null;
                for (int i = 0; i < len; i++)
                {
                    if (i == 0)
                    {
                        rowICell = row.CreateCell(i);
                    }
                    else
                    {
                        ICell rowICell1 = row.CreateCell(i);
                        rowICell1.SetCellValue("");
                        rowICell1.CellStyle = cellStyle;
                    }
                }

                //给第一单元格添加内容
                rowICell.SetCellValue(sheetName);
                rowICell.CellStyle = cellStyle;
                sh.AddMergedRegion(new CellRangeAddress(0, 0, 0, len - 1));
                #endregion

                #region 创建标题行是否合并  第二行
                if(thead.mergeTitle == null && thead.mergeColumn == null)
                {
                    // 不合并
                    IRow row1 = sh.CreateRow(1);
                    for (int i = 0; i < len; i++)
                    {
                        ICell cells = row1.CreateCell(i);
                        cells.SetCellValue(thead.title[i]);
                        cells.CellStyle = cellStyle;
                    }
                }
                else
                {
                    // 有合并
                    pageDataRow = 3;
                    
                    IRow row1 = sh.CreateRow(1);
                    for (int i = 0; i < len; i++)
                    {
                        ICell cells = row1.CreateCell(i);
                        cells.SetCellValue(thead.mergeTitle[i]);
                        cells.CellStyle = cellStyle;
                    }
                    IRow row2 = sh.CreateRow(2);
                    
                    for (int i = 0; i < len; i++)
                    {
                        ICell cells = row2.CreateCell(i);
                        cells.SetCellValue(thead.title[i]);
                        cells.CellStyle = cellStyle;
                    }
                  
                    // 列表合并
                    for (int i = 0; i < thead.mergeTitle?.Length; i++)
                    {
                        if (thead.mergeTitle[i] != ""&& thead.title[i] =="" )
                        {
                            sh.AddMergedRegion(new CellRangeAddress(1, 2, i, i));
                        }
                    }

                    // 行合并
                    for (int i = 0; i < thead.mergeColumn?.Length; i++)
                    {
                        sh.AddMergedRegion(new CellRangeAddress(1, 1, thead.mergeColumn[i][0], thead.mergeColumn[i][1]));
                    }
                }

                #endregion


                #region 添加数据
                if (pageData != null && pageData.Count > 0)
                {
                    for (int i = 0; i < pageData.Count; i++)
                    {
                        var item = pageData[i];
                        IRow rowTemp = sh.CreateRow(i + pageDataRow);

                        for (int j = 0; j < len; j++) // 添加每一列数据
                        {
                            ICell cell = rowTemp.CreateCell(j);
                            if (j == 0)  // 每一列第一条处理
                            {
                                
                                cell.SetCellValue(i + 1);
                                cell.CellStyle = cellStyle;
                            }
                            else
                            {
                                //object value = typeof(T).GetProperty(thead.field[j])?.GetValue(item);
                                var prop = typeof(T).GetProperty(thead.field[j]);
                                // 没有字段参数
                                if( prop == null)
                                {
                                    cell.SetCellValue(string.Empty);
                                }
                                else
                                {
                                    object val = prop?.GetValue(item);
                                    // 判断是否有需要枚举的字段
                                    if (thead.enumList!= null&&thead.enumList.Contains(prop.Name))
                                    {
                                        Type enumType = typeof(ListToExcelEnum).GetNestedType(prop.Name); //  根据字段查找ListToExcelEnum中的枚举
                                        object enumVal = Enum.ToObject(enumType, int.Parse(val?.ToString() ?? "0") );   
                                        cell.SetCellValue(enumVal.ToString());
                                    }
                                    else
                                    {
                                        // val为空
                                        switch (val)
                                        {
                                            case null:
                                                cell.SetCellValue(string.Empty);
                                                break;
                                            case decimal de:
                                                cell.SetCellValue((double)de);
                                                break;
                                            case DateTime dt:
                                                cell.SetCellValue(dt.ToString("yyyy-MM-dd"));
                                                break;
                                            default:
                                                cell.SetCellValue(val.ToString());
                                                break;
                                        }
                                    }
                                }
                                cell.CellStyle = cellStyle;
                            }
                        }

                    }

                }
                #endregion
            }
            catch (Exception ex)
            {
                model.status = false;
                return model;
            }

            model.wb = wb;
            model.status = true;

            return model;
        }
    }
    
    /// <summary>
    /// 涉及到的枚举
    /// </summary>
    public class ListToExcelEnum
    {
        #region  公共枚举
        
        public enum Useing
        {
            启用 = 1,
            禁用 = 0
        }
        #endregion
    }
}

使用案例

using System;
using Commons;
using Commons.Helpers;
using System.IO;

 #region 下载xlsx
    /// <summary>
    /// 下载xlsx
    /// </summary>
    /// <returns></returns>
 [HttpPost]
 public ActionResult DownLoadXlsx (QueryDto queryDto){
     List<OutputDto> pageData = null;
     MessageModel<bool> WsResult = new MessageModel<bool>();
     ...
     pageData = GetDtoPagedList(queryDto);
     
     #region 导出xlsx
      ListToExcel model = new ListToExcel();
      ListToExcelThead thead = new ListToExcelThead();
       
      thead.mergeTitle = new[] { "序号"}; // 需要合并标题
      thead.mergeColumn = new[] { new[] { 4, 7 }};
      thead.title = new[] { "序号" }; 
      thead.field = new[] { "No",  };
      thead.enumList = new[] { "No",  };
      thead.ColumnWidth = new[] { 10, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18, 18 };
      model = ListToExcelHelper.ListToExcel(pageData, thead, "下载");
        if (model.status)
        {
            #region 输出文件流
            //输出的文件名称
            string fileName = "下载" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
            //把Excel转为流，输出
            //创建文件流
            MemoryStream bookStream = new MemoryStream();
            //将工作薄写入文件流
            model.wb.Write(bookStream);
            //输出之前调用Seek（偏移量，游标位置) 把0位置指定为开始位置
            bookStream.Seek(0, SeekOrigin.Begin);
            //Stream对象,文件类型,文件名称
            return File(bookStream, "application/vnd.ms-excel", fileName);
            #endregion
        }
        else
        {
            WsResult.Msg = "对不起,导出失败!";
            WsResult.Status = (int)SysState.异常;
            WsResult.Response = false;
        }
        #endregion
        return Content(Util.SerializeObject(WsResult));
 }