C# NPOI复杂表头导出

1、优先通过管理Nuget程序包添加NPOI引用

2、添加引用

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

3、帮助类

 public class ExcelHelper
    {
        /// <summary>
        /// 导出数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="filePath">文件目录</param>
        /// <param name="fileName">文件名，为空时默认：新建Excel.xlsx</param>
        /// <param name="headers">表格头</param>
        /// <param name="data">表格数据</param>
        /// <returns></returns>
        public static string ExportExcel<T>(string filePath, string fileName, IEnumerable<ExcelHeader> headers, ExcelData<T> data)
        {
            //导出的excel文件地址
            string excelPath = string.Empty;
            #region 文件名称处理
            //为空时设置默认文件名
            if (string.IsNullOrWhiteSpace(fileName))
            {
                fileName = "新建Excel.xlsx";
            }
            //目录为空时取项目基目录
            if (string.IsNullOrWhiteSpace(filePath))
            {
                filePath = AppContext.BaseDirectory;
            }
            if (!Directory.Exists(filePath))
            {
                Directory.CreateDirectory(filePath);
            }
            if (!filePath.EndsWith("\\") && !filePath.EndsWith("/"))
            {
                filePath = filePath + "\\";
            }
            excelPath = $"{filePath}{fileName}";
            #endregion

            //初始化
            IWorkbook workbook = new HSSFWorkbook();
            //工作簿
            ISheet sheetTable = workbook.CreateSheet();
            //生成表头
            #region 表头
            foreach (var item in headers)
            {
                IRow headerRow = sheetTable.GetRow(item.FirstRow);
                if (headerRow == null)
                {
                    headerRow = sheetTable.CreateRow(item.FirstRow);
                    //行高，避免自动换行的内容将行高撑开
                    headerRow.HeightInPoints = 24;
                }
                ICell headerCell = headerRow.CreateCell(item.FirstCol);
                headerCell.SetCellValue(item.Value);
                //设置跨行
                if (item.FirstRow != item.LastRow || item.LastCol != item.FirstCol)
                {
                    //CellRangeAddress(开始行,结束行,开始列,结束列)
                    //行列索引由0开始
                    var region = new CellRangeAddress(item.FirstRow, item.LastRow, item.FirstCol, item.LastCol);
                    sheetTable.AddMergedRegion(region);
                }
                headerCell.CellStyle = HeaderStyle(workbook);
            }
            #endregion

            #region 表格数据
            var type = data.Data.First().GetType();//获取列表的字段的属性
            var properties = type.GetProperties();//筛选出需要
            //加载数据
            foreach (var item in data.Data)
            {
                IRow dataRow = sheetTable.GetRow(data.StartRow);
                if (dataRow == null)
                {
                    dataRow = sheetTable.CreateRow(data.StartRow);
                    //行高，避免自动换行的内容将行高撑开
                    dataRow.HeightInPoints = 20;
                }
                var startCol = data.StartCol;
                foreach (var item1 in properties)
                {
                    ICell dataCell = dataRow.CreateCell(startCol);
                    var dataValue = item1.GetValue(item);
                    dataCell.CellStyle = DataDefautStyle(workbook);
                    IDataFormat dataformat = workbook.CreateDataFormat();
                    //设置内容格式
                    if (dataValue?.GetType() == typeof(int)
                        || dataValue?.GetType() == typeof(decimal)
                        || dataValue?.GetType() == typeof(double)
                        || dataValue?.GetType() == typeof(float))
                    {
                        double dataValueD = 0;
                        double.TryParse(dataValue?.ToString(), out dataValueD);
                        dataCell.SetCellValue(dataValueD);
                        //精确到小数点后两位
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("0.00");
                        //添加千分位分割,并保留两位小数
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("#,##0.00");
                        //金钱格式-千分位分割,并保留两位小数
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("￥#,##0.00");
                        //中文大写(会有四舍五入的情况)
                        //dataCell.CellStyle.DataFormat = dataformat.GetFormat("[DbNum2][$-804]0");
                        //科学计数法
                        //dataCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
                    }
                    else if (dataValue?.GetType() == typeof(DateTime))
                    {
                        DateTime.TryParse(dataValue?.ToString(), out DateTime date);
                        dataCell.SetCellValue(date);
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("yyyy-MM-dd hh:mm:ss");
                    }
                    else
                    {
                        dataCell.SetCellValue(dataValue?.ToString());
                        dataCell.CellStyle.DataFormat = dataformat.GetFormat("text");
                    }
                    startCol++;
                }
                startCol = data.StartCol;
                data.StartRow++;
            }
            #endregion

            #region 生成文件
            FileStream fs = File.Open(excelPath, FileMode.Create, FileAccess.Write);
            workbook.Write(fs);
            sheetTable = null;
            workbook = null;
            fs.Close();
            fs.Dispose();
            #endregion

            return excelPath;
        }
        /// <summary>
        /// 数据单元格样式
        /// </summary>
        private static ICellStyle DataDefautStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center; //居中
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中 
            style.WrapText = true;//自动换行
             边框
            //style.BorderBottom = BorderStyle.Thin;
            //style.BorderLeft = BorderStyle.Thin;
            //style.BorderRight = BorderStyle.Thin;
            //style.BorderTop = BorderStyle.Thin;
            // 字体
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 12;
            font.FontName = "宋体";
            style.SetFont(font);

            return style;
        }
        /// <summary>
        /// 表头样式
        /// </summary>
        public static ICellStyle HeaderStyle(IWorkbook workbook)
        {
            ICellStyle style = workbook.CreateCellStyle();
            //居中
            style.Alignment = HorizontalAlignment.Center;
            //垂直居中
            style.VerticalAlignment = VerticalAlignment.Center;
            //自动换行 
            style.WrapText = true;
            //边框
            //style.BorderBottom = BorderStyle.Thin;
            //style.BorderLeft = BorderStyle.Thin;
            //style.BorderRight = BorderStyle.Thin;
            //style.BorderTop = BorderStyle.Thin;
            //边框颜色
            //style.TopBorderColor = HSSFColor.Black.Index;
            //style.BottomBorderColor = HSSFColor.Black.Index;
            //style.RightBorderColor = HSSFColor.Black.Index;
            //style.LeftBorderColor = HSSFColor.Black.Index;
            //字体
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 14;
            font.FontName = "宋体";
            font.IsBold = true;
            style.SetFont(font);
            return style;
        }
    }
    /// <summary>
    /// 表头格式
    /// </summary>
    public class ExcelHeader
    {
        /// <summary>
        /// 标题
        /// </summary>
        public string Value { get; set; }
        /// <summary>
        /// 开始行，索引0开始
        /// </summary>
        public int FirstRow { get; set; }
        /// <summary>
        /// 结束行，索引0开始
        /// </summary>
        public int LastRow { get; set; }
        /// <summary>
        /// 开始列，索引0开始
        /// </summary>
        public int FirstCol { get; set; }
        /// <summary>
        /// 结束列，索引0开始
        /// </summary>
        public int LastCol { get; set; }
    }
    /// <summary>
    /// 表数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class ExcelData<T>
    {
        /// <summary>
        /// 表数据起始行，索引0开始 
        /// </summary>
        public int StartRow { get; set; }
        /// <summary>
        /// 表数据起始列，索引0开始
        /// </summary>
        public int StartCol { get; set; }
        /// <summary>
        /// 行数据
        /// </summary>
        public IEnumerable<T> Data { get; set; }
    }

4、数据实体类

 public class EvectionData
{
            /// <summary>
            /// 姓名
            /// </summary>
            public string Name { get; set; }
            /// <summary>
            /// 出发地
            /// </summary>
            public string StartLocation { get; set; }
            /// <summary>
            /// 目的地
            /// </summary>
            public string EndLocation { get; set; }
            /// <summary>
            /// 交通工具
            /// </summary>
            public string Vehicle { get; set; }
            /// <summary>
            /// 交通费
            /// </summary>
            public decimal Transportation { get; set; }
            /// <summary>
            /// 总金额
            /// </summary>
            public decimal Amount { get; set; }
            /// <summary>
            /// 备注
            /// </summary>
            public string Remark { get; set; }
            /// <summary>
            /// 时间
            /// </summary>
            public DateTime? ItemDate { get; set; }
}

5、调用

    var filePath = $"C:\\Users\\Administrator\\Desktop\\";
    var fileName = $"{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
    var header = new List<ExcelHeader>() {
        new ExcelHeader (){ FirstCol=0, LastCol=7, FirstRow=0, LastRow=0, Value="xxxxxxxxxx公司" },//(第一行)(跨列0-6)
        new ExcelHeader (){ FirstCol=0, LastCol=7, FirstRow=1, LastRow=1, Value="费用报销明细表" },//(第二行)(跨列0-6)
        new ExcelHeader (){ FirstCol=0, LastCol=0, FirstRow=2, LastRow=3, Value="姓名" },//第一列，跨两行(2-3)
        new ExcelHeader (){ FirstCol=1, LastCol=2, FirstRow=2, LastRow=2, Value="行程" },//跨两列(1,2),同一行(第三行)
        new ExcelHeader (){ FirstCol=1, LastCol=1, FirstRow=3, LastRow=3, Value="出发地" },
        new ExcelHeader (){ FirstCol=2, LastCol=2, FirstRow=3, LastRow=3, Value="目的地" },
        new ExcelHeader (){ FirstCol=3, LastCol=4, FirstRow=2, LastRow=2, Value="交通费" },
        new ExcelHeader (){ FirstCol=3, LastCol=3, FirstRow=3, LastRow=3, Value="交通工具"},
        new ExcelHeader (){ FirstCol=4, LastCol=4, FirstRow=3, LastRow=3, Value="交通费"},
        new ExcelHeader (){ FirstCol=5, LastCol=5, FirstRow=2, LastRow=3, Value="总金额"},
        new ExcelHeader (){ FirstCol=6, LastCol=6, FirstRow=2, LastRow=3, Value="备注"},
        new ExcelHeader (){ FirstCol=7, LastCol=7, FirstRow=2, LastRow=3, Value="时间"},
    };
    var listData = new List<EvectionData>() {
        new EvectionData (){ Name="姓名1", StartLocation="长沙", EndLocation= "深圳",Vehicle="高铁", Transportation=388.5M, Amount=388.5M, Remark="备注1",ItemDate=DateTime.Now },
        new EvectionData (){ Name="姓名2", StartLocation="清远", EndLocation= "长沙",Vehicle="高铁", Transportation=279M, Amount=279M, Remark="备注1"  },
    };
    var data = new ExcelData<EvectionData>{ StartCol = 0, StartRow = 4, Data = listData };
    ExcelHelper.ExportExcel(filePath, fileName, header, data);

6、结果

 

参考文章：ExcelHelper: 生成复杂表头，导出数据 (gitee.com)