using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Windows;

using ExportExcelTest.Models;

using GeologyToolkit;

using Microsoft.Win32;

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace ExportExcelTest
{
    /// <summary>
    /// MainWindow.xaml 的交互逻辑
    /// </summary>
    public partial class MainWindow : Window
    {
        public MainWindow() { InitializeComponent(); }

        private void CreateCell(IRow row, int column, object value)
        {
            var cell = row.CreateCell(column);
            if (value is int intValue)
            {
                cell.SetCellValue(intValue);
            }
            if (value is double doubleValue)
            {
                cell.SetCellValue(doubleValue);
            }
            else if (value is string stringValue)
            {
                cell.SetCellValue(stringValue);
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }
        }

        private static ICellStyle CreateCellStyle(IWorkbook workbook)
        {
            // 创建一个新的单元格样式
            var style = workbook.CreateCellStyle();
            // 创建一个新的字体样式
            var font = workbook.CreateFont();
            // 设置字体为
            font.FontName = "宋体";
            // 设置字体为加粗
            //font.IsBold = true;
            // 设置字体颜色为白色
            //font.Color = IndexedColors.Black.Index;
            // 将创建的字体样式应用到单元格样式中
            style.SetFont(font);
            // 设置单元格填充前景色为蓝色
            //style.FillForegroundColor = IndexedColors.Blue.Index;
            // 设置填充模式为实心
            //style.FillPattern = FillPattern.SolidForeground;
            // 设置上边框为细线样式
            style.BorderTop = BorderStyle.Thin;
            // 设置下边框为细线样式
            style.BorderBottom = BorderStyle.Thin;
            // 设置左边框为细线样式
            style.BorderLeft = BorderStyle.Thin;
            // 设置右边框为细线样式
            style.BorderRight = BorderStyle.Thin;
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // 添加水平居中
            // 设置垂直对齐方式为居中
            style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;

            return style;
        }

        private static ICellStyle CreateHeaderStyle(IWorkbook workbook)
        {
            ICellStyle headerStyle = workbook.CreateCellStyle();
            headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
            headerStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderTop = BorderStyle.Thin;
            headerStyle.BorderLeft = BorderStyle.Thin;
            headerStyle.BorderRight = BorderStyle.Thin;
            IFont font = workbook.CreateFont();
            font.FontName = "宋体";
            font.IsBold = true;
            font.FontHeightInPoints = 11;
            headerStyle.SetFont(font);
            return headerStyle;
        }
        //创建数据示例
        private SheetTable CreateSampleData(TypeOfSurrounding surrounding)
        {
            var st = new SheetTable(surrounding);
            //一级开项
            var kw = st.AddMain("开挖");
            //二级开项
            var tf = kw.AddSub("土方");
            //细项
            var pt = tf.AddDetail("普通开挖");
            //计量方式，计量单位
            pt.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 45.463).SetProof(4, 481.95);
            pt.AddMeature(MeasurementUnit.Kilogram).SetProof(1, 48.596).SetProof(3, 78.894);
            //细项
            var jx = tf.AddDetail("机械开挖");
            //计量方式，计量单位
            jx.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 78.562).SetProof(5, 484.89);
            //二级开项及细项
            var sf = kw.AddSub("石方").AddDetail("爆破开挖");
            //计量方式，计量单位
            sf.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 487.84).SetProof(6, 15.6);
            sf.AddMeature(MeasurementUnit.Kilogram).SetProof(1, 153.26).SetProof(3, 48.596);


            var cqwg = st.AddMain("衬砌圬工");
            var ec = cqwg.AddSub("二衬");

            var c30 = ec.AddDetail("C30钢筋砼");
            c30.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 48.596).SetProof(3, 78.894);
            c30.AddMeature(MeasurementUnit.Kilonewton).SetProof(2, 48.6).SetProof(4, 78.894);

            var c30F = ec.AddDetail("C30钢筋砼 防水型");
            c30F.AddMeature(MeasurementUnit.Kilonewton).SetProof(0, 48.596).SetProof(3, 78.894);
            c30F.AddMeature(MeasurementUnit.CubicMeter).SetProof(2, 48.6).SetProof(4, 78.894);

            var c40 = ec.AddDetail("C40钢筋砼");
            c40.AddMeature(MeasurementUnit.Kilonewton).SetProof(1, 48.596).SetProof(3, 78.894);

            return st;
        }
        private void CreateSheetTitle(ISheet sheet, Measurement measurement)
        {
            var proofs = measurement.Proofs;
            TypeOfSurrounding typeOfSurrounding = measurement.Detail.SubItem.Primary.Table.TypeOfSurrounding;
            // 创建字体和样式
            var workbook = sheet.Workbook;
            var style = CreateHeaderStyle(workbook);

            // 创建第一行（合并行）
            IRow row0 = sheet.CreateRow(0);
            string[] headerTitles = { "项目及建议材料说明", "单位", "支护类型", $"{typeOfSurrounding.GetDescription()}合计", $"TC-300-围岩类型：{typeOfSurrounding.GetDescription()}", "BIM算量规则", "备注及说明" };
            //项目及建议材料说明
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
            CreateCell(row0, 0, headerTitles[0]);
            //单位
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, 3, 3));
            CreateCell(row0, 3, headerTitles[1]);

            // 支护类型合并水平方向（跨proofs.Count列）
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 4, proofs.Count + 3));
            CreateCell(row0, 4, headerTitles[2]);

            IRow row1 = sheet.CreateRow(1);
            for (int i = 0; i < proofs.Count; i++)
            {
                var item = proofs.ElementAt(i);

                CreateCell(row1, i + 4, item.TypeOfSupport);
            }
            //合计
            sheet.AddMergedRegion(new CellRangeAddress(0, 1, proofs.Count + 4, proofs.Count + 4));
            CreateCell(row0, proofs.Count + 4, headerTitles[3]);

            //围岩类型
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, proofs.Count + 5, proofs.Count + 6));
            CreateCell(row0, proofs.Count + 5, headerTitles[4]);
            //BIM算量规则及描述
            CreateCell(row1, proofs.Count + 5, headerTitles[5]);
            CreateCell(row1, proofs.Count + 6, headerTitles[6]);
            for (int i = 0; i < 2; i++)
            {
                IRow row = sheet.GetRow(i);
                for (int j = 0; j < measurement.Proofs.Count + 7; j++)
                {
                    ICell cell = row.GetCell(j) ?? row.CreateCell(j);
                    cell.CellStyle = style;
                    sheet.AutoSizeColumn(j);
                }
            }
        }

        private void ExportButton_Click(object sender, RoutedEventArgs e)
        {
            var table = CreateSampleData(TypeOfSurrounding.LevelThree);
            var table1 = CreateSampleData(TypeOfSurrounding.LevelFive);
            var saveDialog = new SaveFileDialog
            {
                Filter = "Excel文件|*.xlsx",
                FileName = $"工程量表.xlsx",
                InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
            };

            if (saveDialog.ShowDialog() == true)
            {
                ExportToExcel(new List<SheetTable>() { table, table1 }, saveDialog.FileName, 2, 0);
                MessageBox.Show("导出成功！", "完成", MessageBoxButton.OK, MessageBoxImage.Information);
            }
        }

        private void ExportToExcel(List<SheetTable> tables, string filePath, int startRowIndex, int startColumnIndex)
        {
            var workbook = new XSSFWorkbook();
            // 创建样式
            var style = CreateCellStyle(workbook);
            //var borderStyle = CreateBorderStyle(workbook);

            foreach (SheetTable table in tables)
            {
                int startRowIndexTemp = startRowIndex;
                int startColumnIndexTemp = startColumnIndex;

                var sheet = workbook.CreateSheet(table.Name);
                var measurement = table.MainCategories.FirstOrDefault().SubCategories.FirstOrDefault().DetailCategories.FirstOrDefault().MeasureTypes.FirstOrDefault();
                CreateSheetTitle(sheet, measurement);
                foreach (var mainCat in table.MainCategories)
                {
                    int mainStartRow = startRowIndexTemp;

                    foreach (var subCat in mainCat.SubCategories)
                    {
                        int subStartRow = startRowIndexTemp;

                        foreach (var detailCate in subCat.DetailCategories)
                        {
                            int detailStartRow = startRowIndexTemp;
                            foreach (var measureType in detailCate.MeasureTypes)
                            {
                                int measureStartRow = startRowIndexTemp;
                                IRow row = sheet.CreateRow(startRowIndexTemp);
                                // 只在第一次出现时创建主分类单元格
                                if (startRowIndexTemp == mainStartRow)
                                {
                                    CreateCell(row, startColumnIndexTemp, mainCat.Name);
                                }
                                // 只在第一次出现时创建子分类单元格
                                if (startRowIndexTemp == subStartRow)
                                {
                                    CreateCell(row, startColumnIndexTemp + 1, subCat.Name);
                                }
                                // 只在第一次出现时创建细项分类单元格
                                if (startRowIndexTemp == detailStartRow)
                                {
                                    CreateCell(row, startColumnIndexTemp + 2, detailCate.Name);
                                }
                                // 只在第一次出现时创建计量方式单元格
                                if (startRowIndexTemp == measureStartRow)
                                {
                                    CreateCell(row, startColumnIndexTemp + 3, measureType.Unit.GetDescription());
                                }

                                for (int i = 0; i < measureType.Proofs.Count; i++)
                                {
                                    var proof = measureType.Proofs.ElementAt(i);
                                    CreateCell(row, startColumnIndexTemp + 4 + i, proof.Amount);
                                }
                                //合计
                                CreateCell(row, startColumnIndexTemp + 4 + measureType.Proofs.Count, measureType.Total);

                                CreateCell(row, startColumnIndexTemp + 5 + measureType.Proofs.Count, measureType.CalculationRule);
                                CreateCell(row, startColumnIndexTemp + 6 + measureType.Proofs.Count, measureType.Description);

                                startRowIndexTemp++;

                            }
                            //合并细项单元格
                            if (startRowIndexTemp > detailStartRow + 1)
                            {
                                sheet.AddMergedRegion(new CellRangeAddress(
                                    detailStartRow,
                                    startRowIndexTemp - 1,
                                    startColumnIndexTemp + 2,
                                    startColumnIndexTemp + 2));

                                //sheet.GetRow(detailStartRow).GetCell(startColumnIndexTemp + 2).CellStyle = style;
                            }
                        }

                        // 合并子分类单元格
                        if (startRowIndexTemp > subStartRow + 1)
                        {
                            sheet.AddMergedRegion(new CellRangeAddress(
                                subStartRow,
                                startRowIndexTemp - 1,
                                startColumnIndexTemp + 1,
                                startColumnIndexTemp + 1));
                            //sheet.GetRow(subStartRow).GetCell(startColumnIndexTemp + 1).CellStyle = subStyle;

                        }
                    }

                    // 合并主分类单元格
                    if (startRowIndexTemp > mainStartRow + 1)
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(mainStartRow, startRowIndexTemp - 1, startColumnIndexTemp, startColumnIndexTemp));
                        //sheet.GetRow(mainStartRow).GetCell(startColumnIndexTemp).CellStyle = mainStyle;
                    }
                }

                // 从第二行开始调整样式
                for (int i = 2; i < sheet.LastRowNum + 1; i++)
                {
                    IRow row = sheet.GetRow(i);
                    for (int j = 0; j < measurement.Proofs.Count + 7; j++)
                    {
                        ICell cell = row.GetCell(j) ?? row.CreateCell(j);
                        cell.CellStyle = style;
                        sheet.AutoSizeColumn(j);
                    }
                }
                // 遍历所有合并区域并设置样式
                //for (int i = 0; i < sheet.NumMergedRegions; i++)
                //{
                //    CellRangeAddress region = sheet.GetMergedRegion(i);
                //    IRow row = sheet.GetRow(region.FirstRow);
                //    ICell cell = row.GetCell(region.FirstColumn);
                //    cell.CellStyle = style;
                //}
            }

            using (var fs = new FileStream(filePath, FileMode.Create))
            {
                workbook.Write(fs);
            }
        }

        private void Button_Click(object sender, RoutedEventArgs e)
        {
            var dialog = new OpenFileDialog() { };
            var result = dialog.ShowDialog();
            if (result == true)
            {
                //using (OleDbConnection oleDbConnection = new OleDbConnection())
                //{
                //    oleDbConnection.ConnectionString = $"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={dialog.FileName}";
                //    OleDbCommand oleDbCommand = new OleDbCommand();
                //    oleDbCommand.CommandText = "select * from [zksd]";
                //    oleDbCommand.Connection = oleDbConnection;

                //    using (OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
                //    {
                //        DataTable dt = new DataTable();
                //        da.Fill(dt);
                //        dg.DataContext = dt;
                //    }
                //}
                MdbHelper mdbHelper = new MdbHelper(dialog.FileName);
                mdbHelper.Open();
                var tables = mdbHelper.GetTables();

                foreach (DataRow row in tables.Rows)
                {
                    var tableName = row["TABLE_NAME"].ToString();
                    var ds = mdbHelper.GetDataSetByTableName(tableName);
                    var dt = ds.Tables[0];
                    foreach (DataRow r in dt.Rows)
                    {
                        foreach (DataColumn c in dt.Columns)
                        {
                            Console.WriteLine(r[c]);
                        }
                    }
                }
            }
        }

    }
}