NPOI导出Excel合并行

先放两张图

//先获取到表格
try
            {
                string ParentGUID =“”;
                //离职人员工龄分析统计
                DataTable dt1 = GetDataTable();

                DataTable dtResult = new DataTable();
                //克隆表结构
                dtResult = dt1.Clone();
                foreach (DataColumn col in dtResult.Columns)
                {
                    //修改列类型
                    col.DataType = typeof(string);
                }
                foreach (DataRow row in dt1.Rows)
                {
                    DataRow newDtRow = dtResult.NewRow();
                    foreach (DataColumn column in dt1.Columns)
                    {
                        newDtRow[column.ColumnName] = Convert.ToString(row[column.ColumnName]);
                    }
                    dtResult.Rows.Add(newDtRow);
                }

                //插入列名
                int _type = 0;
                var _ColumnName = GetDataTable().Select();
                foreach (var _cln in _ColumnName)
                {
                    dtResult.Columns.Add(_cln["Name"].ToString());
                }
                foreach (DataRow _row in dtResult.Rows)
                {
                    var _deptId = _row["DeptGuid"].ToString();
                    if (_deptId == Guid.Empty.ToString())
                    {
                        _type = 1;
                        _deptId = ParentGUID;
                    }
                    var _Dt = GetTitleLevelDataTable(_deptId, _type);
                    foreach (DataRow _dr in _Dt.Rows)
                    {
                        _row[_dr["Name"].ToString()] = _dr["num"];
                    }
                }

            }
            catch (Exception ex)
            {
            }
//导出方法
IWorkbook excel = new HSSFWorkbook();//创建.xls文件
            ISheet sheet = excel.CreateSheet("员工离职分析表"); //创建sheet

            //获取动态列
             var _dtCount = GetDataTable();

            ICellStyle style = excel.CreateCellStyle();
            style.Alignment = HorizontalAlignment.Center;
            style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            IFont font = excel.CreateFont();
            font.Boldweight = 600;
            font.FontHeight = 16 * 16;
            style.SetFont(font);
            #region 构造表头
            IRow row = sheet.CreateRow(0);//创建行对象，填充表头
            ICell cell = row.CreateCell(0);
            cell.SetCellValue("员工离职分析表");
            cell.CellStyle = style;

            #region#创建第一行
            row = sheet.CreateRow(1);
            cell = row.CreateCell(0);
            cell.SetCellValue("年度");
            cell.CellStyle = style;
            cell = row.CreateCell(1);
            cell.SetCellValue(#34;{ DateTime.Now.Year}年");
            cell.CellStyle = style;
            cell = row.CreateCell(2);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(5);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = style;
            for (int i = 1; i <= _dtCount.Rows.Count; i++)
            {
                cell = row.CreateCell(i + 6);
                cell.SetCellValue("");
                cell.CellStyle = style;
            }
            #endregion
            #region#创建第二行
            row = sheet.CreateRow(2);
            cell = row.CreateCell(0);
            cell.SetCellValue("序号");
            cell.CellStyle = style;
            cell = row.CreateCell(1);
            cell.SetCellValue("组织名称");
            cell.CellStyle = style;
            cell = row.CreateCell(2);
            cell.SetCellValue("离职人员工龄统计(L)");
            cell.CellStyle = style;
            cell = row.CreateCell(3);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(4);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(5);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(6);
            cell.SetCellValue("");
            cell.CellStyle = style;
            for (int i = 1; i <= _dtCount.Rows.Count; i++)
            {
                cell = row.CreateCell(i + 6);
                cell.SetCellValue("职称统计");
                cell.CellStyle = style;
            }
            #endregion
            #region#创建第三行
            row = sheet.CreateRow(3);
            cell = row.CreateCell(0);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(1);
            cell.SetCellValue("");
            cell.CellStyle = style;
            cell = row.CreateCell(2);
            cell.SetCellValue("L<1");
            cell.CellStyle = style;
            cell = row.CreateCell(3);
            cell.SetCellValue("1<=L<3");
            cell.CellStyle = style;
            cell = row.CreateCell(4);
            cell.SetCellValue("3<=L<5");
            cell.CellStyle = style;
            cell = row.CreateCell(5);
            cell.SetCellValue("5<=L<10");
            cell.CellStyle = style;
            cell = row.CreateCell(6);
            cell.SetCellValue("10<=L");
            cell.CellStyle = style;
            for (int i = 1; i <= _dtCount.Rows.Count; i++)
            {
                cell = row.CreateCell(i + 6);
                cell.SetCellValue(_dtCount.Rows[i - 1]["Name"].ToString());
                cell.CellStyle = style;
            }
            #endregion


            #endregion
            //合并行
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 6 + _dtCount.Rows.Count));//标题
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 0, 0));//序号
            sheet.AddMergedRegion(new CellRangeAddress(2, 3, 1, 1));//组织名称
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 2, 6));//离职人员工龄统计(L)
            sheet.AddMergedRegion(new CellRangeAddress(2, 2, 7, 6 + _dtCount.Rows.Count));//职称统计
            //  报表开始
            DataTable dataTable = GetEmployeeLeaveReport().data as DataTable;

            ICellStyle _style = excel.CreateCellStyle();
            _style.Alignment = HorizontalAlignment.Center;
            _style.VerticalAlignment = VerticalAlignment.Center;//垂直居中
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                IRow drow = sheet.CreateRow(i + 4);
                ICell cellbody = drow.CreateCell(0, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["RowNum"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(1, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["DeptName"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(2, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["L<1"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(3, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["1<=L<3"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(4, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["3<=L<5"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(5, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["5<=L<10"]?.ToString());
                cellbody.CellStyle = _style;
                cellbody = drow.CreateCell(6, CellType.String);
                cellbody.SetCellValue(dataTable.Rows[i]["10<=L"]?.ToString());
                cellbody.CellStyle = _style;
                for (int j = 1; j <= _dtCount.Rows.Count; j++)
                {
                    var _Clname = _dtCount.Rows[j - 1]["Name"].ToString();
                    cellbody = drow.CreateCell(j + 6, CellType.String);
                    cellbody.SetCellValue(dataTable.Rows[i][_Clname]?.ToString());
                    cellbody.CellStyle = _style;
                }
            }
            //自适应列宽
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            HttpResponse httpResponse = HttpContext.Current.Response;
            httpResponse.Clear();
            httpResponse.Buffer = true;
            httpResponse.Charset = Encoding.UTF8.BodyName;
            httpResponse.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("员工离职分析表", System.Text.Encoding.UTF8) + ".xls");
            httpResponse.ContentEncoding = Encoding.UTF8;
            httpResponse.ContentType = "application/vnd.ms-excel; charset=UTF-8";
            excel.Write(httpResponse.OutputStream);
            httpResponse.End();