using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Data;
using System.Text.RegularExpressions;
namespace TestConsoleApp
{
    /// <summary>
    /// 導(dǎo)出Excel
    /// </summary>
    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;
            //創(chuàng)建所有單元格
            for (int i = 0; i < allRowCount; i++)
            {
                var row = sheet.CreateRow(i);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    row.CreateCell(j);
                }
            }
            //合并創(chuàng)建表頭
            foreach (var tit in titles)
            {
                sheet.GetRow(tit.StartRow).GetCell(tit.StartColumn).SetCellValue(tit.Title);
                if (tit.MergeColumnCount + tit.MergeRowCount > 0)
                {
                    sheet.AddMergedRegion(new CellRangeAddress(tit.StartRow, tit.StartRow + tit.MergeRowCount, tit.StartColumn, tit.StartColumn + tit.MergeColumnCount));
                }
            }
            //生成數(shù)據(jù)行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    string cellValue = dt.Rows[i][j].ToString();
                   sheet.GetRow(maxTitleLevel + i).Cells[j].SetCellValue(cellValue);
                }
            }
            using FileStream stm = File.OpenWrite(@"D:\Drivers\Merge.xls");
            workbook.Write(stm);
        }
        private static DataTable CreteTable()
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("編號(hào)");
            dt.Columns.Add("收入-線上采購(gòu)-數(shù)量");
            dt.Columns.Add("收入-線上采購(gòu)-金額");
            dt.Columns.Add("收入-線下采購(gòu)-數(shù)量");
            dt.Columns.Add("收入-線下采購(gòu)-金額");
            dt.Columns.Add("回收-數(shù)量");
            dt.Columns.Add("回收-金額");
            dt.Columns.Add("支出-測(cè)試01-數(shù)量");
            dt.Columns.Add("支出-測(cè)試01-金額");
            dt.Columns.Add("支出-測(cè)試02-數(shù)量");
            dt.Columns.Add("支出-測(cè)試02-金額");
            dt.Columns.Add("其它-數(shù)量");
            dt.Columns.Add("其它-金額");
            dt.Columns.Add("備注");
            for (int i = 1; i <= 100; i++)
            {
                var row = dt.NewRow();
                row["編號(hào)"] = "編號(hào)" + i;
                row["收入-線上采購(gòu)-數(shù)量"] = i;
                row["收入-線上采購(gòu)-金額"] = i;
                row["收入-線下采購(gòu)-數(shù)量"] = i;
                row["收入-線下采購(gòu)-金額"] = i;
                row["回收-數(shù)量"] = i;
                row["回收-金額"] = i;
                row["支出-測(cè)試01-數(shù)量"] = i;
                row["支出-測(cè)試01-金額"] = i;
                row["支出-測(cè)試02-數(shù)量"] = i;
                row["支出-測(cè)試02-金額"] = i;
                row["其它-數(shù)量"] = i;
                row["其它-金額"] = i;
                row["備注"] = i;
                dt.Rows.Add(row);
            }
            return dt;
        }
        private static List<ExcelTitle> GetExcelTitles(DataColumnCollection columns, out int maxTitleLevel)
        {
            maxTitleLevel = 0;
            List<LevelExcelTitle> levelExcelTitles = new List<LevelExcelTitle>();
            for (var index = 0; index < columns.Count; index++)
            {
                var column = columns[index].ToString();
                var arr = column.Split("-");
                if (maxTitleLevel < arr.Length)
                {
                    maxTitleLevel = arr.Length;
                }
                for (int i = 0; i < arr.Length; i++)
                {
                    levelExcelTitles.Add(new LevelExcelTitle()
                    {
                        Title = arr[i],
                        LevelCode = string.Join("-", arr[..(i + 1)]),
                        RowIndex = i,
                        ColumnIndex = index,
                        TotalLevel = arr.Length
                    });
                }
            }
            var titleLevel = maxTitleLevel;
            var excelTitles = levelExcelTitles
                .GroupBy(b => 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
    {
        /// <summary>
        /// 標(biāo)題
        /// </summary>
        public string Title { get; set; }
        /// <summary>
        /// 開(kāi)始行
        /// </summary>
        public int StartRow { get; set; }
        /// <summary>
        /// 合并行
        /// </summary>
        public int MergeRowCount { get; set; }
        /// <summary>
        /// 開(kāi)始列
        /// </summary>
        public int StartColumn { get; set; }
        /// <summary>
        /// 合并列
        /// </summary>
        public int MergeColumnCount { get; set; }
    }
    public class LevelExcelTitle
    {
        /// <summary>
        /// 標(biāo)題
        /// </summary>
        public string Title { get; set; }
        public string LevelCode { get; set; }
        /// <summary>
        /// 第幾行
        /// </summary>
        public int RowIndex { get; set; }
        /// <summary>
        /// 第幾列
        /// </summary>
        public int ColumnIndex { get; set; }
        /// <summary>
        /// 總層
        /// </summary>
        public int TotalLevel { get; set; }
    }
}