using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;
using System.Text.RegularExpressions;
namespace TestConsoleApp
{
    /// 

    /// 导出Excel
    /// 
    public static class ExportHelper
    {
        public static void Export()
        {
            var dt = CreteTable();
            var titles = GetExcelTitles(dt.Columns, out int maxTitleLevel);
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("Sheet1");
            var allRowCount = dt.Rows.Count + maxTitleLevel;
            //创建所有单元格
            for (int i = 0; i  0)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(tit.StartRow, tit.StartRow + tit.MergeRowCount, tit.StartColumn, tit.StartColumn + tit.MergeColumnCount));
                }
            }
            //生成数据行
            for (int i = 0; i  GetExcelTitles(DataColumnCollection columns, out int maxTitleLevel)
        {
            maxTitleLevel = 0;
            List levelExcelTitles = new List();
            for (var index = 0; index  new
                {
                    b.LevelCode,
                    b.Title
                })
               .Select(b => new ExcelTitle()
               {
                   Title = b.Key.Title,
                   StartRow = b.Min(c => c.RowIndex),
                   MergeRowCount = b.Min(c => c.RowIndex) + 1 == b.Max(c => c.TotalLevel) ? titleLevel - b.Max(c => c.TotalLevel) : 0,
                   StartColumn = b.Min(c => c.ColumnIndex),
                   MergeColumnCount = b.Count() - 1,//排除自身
               }).ToList();
            return excelTitles;
        }
    }
    public class ExcelTitle
    {
        /// 

        /// 标题
        /// 
        public string Title { get; set; }
        /// 

        /// 开始行
        /// 
        public int StartRow { get; set; }
        /// 

        /// 合并行
        /// 
        public int MergeRowCount { get; set; }
        /// 

        /// 开始列
        /// 
        public int StartColumn { get; set; }
        /// 

        /// 合并列
        /// 
        public int MergeColumnCount { get; set; }
    }
    public class LevelExcelTitle
    {
        /// 

        /// 标题
        /// 
        public string Title { get; set; }
        public string LevelCode { get; set; }
        /// 

        /// 第几行
        /// 
        public int RowIndex { get; set; }
        /// 

        /// 第几列
        /// 
        public int ColumnIndex { get; set; }
        /// 

        /// 总层
        /// 
        public int TotalLevel { get; set; }
    }
}