using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text.RegularExpressions;

namespace Mall.Common.Plugin
{
    /// <summary>
    /// npoi帮助类
    /// </summary>
    public class NPOIHelper
    {

        #region 从excel中将数据导出到datatable

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="SheetIndex">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="needHeader">列头</param>
        /// <returns></returns>
        public static DataTable ImportExceltoDt(String strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader)
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            DataTable table = ImportDt(sheet, HeaderRowIndex, needHeader);
            return table;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="needHeader">是否导出表头</param>
        /// <returns></returns>
        static DataTable ImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader)
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            try
            {
                if (HeaderRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        DataColumn column = new DataColumn(Convert.ToString(i));
                        table.Columns.Add(column);
                    }
                }
                else
                {
                    headerRow = sheet.GetRow(HeaderRowIndex);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column);
                            }

                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        else
                        {
                            row = sheet.GetRow(i);
                        }

                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (row.GetCell(j) != null)
                                {
                                    CellType CellTypeEnum = row.GetCell(j).CellType;
                                    switch (CellTypeEnum)
                                    {
                                        case CellType.String:
                                            String str = row.GetCell(j).StringCellValue;
                                            if (str != null && str.Length > 0)
                                            {
                                                dataRow[j] = str.ToString();
                                            }
                                            else
                                            {
                                                dataRow[j] = null;
                                            }
                                            break;
                                        case CellType.Numeric:
                                            if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                            {
                                                dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                            }
                                            else
                                            {
                                                dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                            }
                                            break;
                                        case CellType.Boolean:
                                            dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                            break;
                                        case CellType.Error:
                                            dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                            break;
                                        case CellType.Formula:
                                            switch (row.GetCell(j).CachedFormulaResultType)
                                            {
                                                case CellType.String:
                                                    String strFORMULA = row.GetCell(j).StringCellValue;
                                                    if (strFORMULA != null && strFORMULA.Length > 0)
                                                    {
                                                        dataRow[j] = strFORMULA.ToString();
                                                    }
                                                    else
                                                    {
                                                        dataRow[j] = null;
                                                    }
                                                    break;
                                                case CellType.Numeric:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                    break;
                                                case CellType.Boolean:
                                                    dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                    break;
                                                case CellType.Error:
                                                    dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                    break;
                                                default:
                                                    dataRow[j] = "";
                                                    break;
                                            }
                                            break;
                                        default:
                                            dataRow[j] = "";
                                            break;
                                    }
                                }
                            }
                            catch (Exception exception)
                            {
                                LogHelper.Write(exception, "ImportDt_1");
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception)
                    {
                        LogHelper.Write(exception, "ImportDt_2");
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Write(exception, "ImportDt_3");
            }
            return table;
        }

        #endregion

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="SheetIndex">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="needHeader">列头</param>
        /// <param name="imgColList">图片所在的列</param>
        /// <param name="imgColList">图片保存路径</param>
        /// <returns></returns>
        public static DataTable ImportYBExceltoDt(String strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader,List<int> imgColList=null, string tempFilePath="")
        {
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            DataTable table = YBImportDt(sheet, HeaderRowIndex, needHeader,imgColList: imgColList, tempFilePath: tempFilePath);
            return table;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="needHeader">是否导出表头</param>
        /// <returns></returns>
        static DataTable YBImportDt(ISheet sheet, int HeaderRowIndex, bool needHeader, List<int> imgColList = null, string tempFilePath = "")
        {
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            try
            {
                List<NImgItem> imgList = new List<NImgItem>();
                if (imgColList != null && imgColList.Count > 0)
                {
                    imgList= ReadImageXSSF(sheet, tempFilePath);//新版本的Excel（.xlsx） 
                }
                if (HeaderRowIndex < 0 || !needHeader)
                {
                    headerRow = sheet.GetRow(0);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        DataColumn column = new DataColumn(Convert.ToString(i));
                        table.Columns.Add(column);
                    }
                }
                else
                {
                    headerRow = sheet.GetRow(HeaderRowIndex);
                    cellCount = headerRow.LastCellNum;

                    for (int i = headerRow.FirstCellNum; i <= cellCount; i++)
                    {
                        if (headerRow.GetCell(i) == null)
                        {
                            if (table.Columns.IndexOf(Convert.ToString(i)) > 0)
                            {
                                DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                                table.Columns.Add(column);
                            }
                            else
                            {
                                DataColumn column = new DataColumn(Convert.ToString(i));
                                table.Columns.Add(column);
                            }

                        }
                        else if (table.Columns.IndexOf(headerRow.GetCell(i).ToString()) > 0)
                        {
                            DataColumn column = new DataColumn(Convert.ToString("重复列名" + i));
                            table.Columns.Add(column);
                        }
                        else
                        {
                            DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                            table.Columns.Add(column);
                        }
                    }
                }
                int rowCount = sheet.LastRowNum;
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    try
                    {
                        IRow row;
                        if (sheet.GetRow(i) == null)
                        {
                            row = sheet.CreateRow(i);
                        }
                        else
                        {
                            row = sheet.GetRow(i);
                        }

                        DataRow dataRow = table.NewRow();

                        for (int j = row.FirstCellNum; j <= cellCount; j++)
                        {
                            try
                            {
                                if (imgColList != null && imgColList.Count > 0 && imgColList.Contains(j))
                                {
                                    var tempImg = imgList.Where(qitem => qitem.RowNum == i && qitem.ColNum == j)?.FirstOrDefault();
                                    dataRow[j] = tempImg?.ImgPath ?? "";
                                }
                                else
                                {
                                    if (row.GetCell(j) != null)
                                    {
                                        CellType CellTypeEnum = row.GetCell(j).CellType;
                                        switch (CellTypeEnum)
                                        {
                                            case CellType.String:
                                                String str = row.GetCell(j).StringCellValue;
                                                if (str != null && str.Length > 0)
                                                {
                                                    dataRow[j] = str.ToString();
                                                }
                                                else
                                                {
                                                    dataRow[j] = null;
                                                }
                                                break;
                                            case CellType.Numeric:
                                                if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
                                                {
                                                    dataRow[j] = DateTime.FromOADate(row.GetCell(j).NumericCellValue);
                                                }
                                                else
                                                {
                                                    dataRow[j] = Convert.ToDouble(row.GetCell(j).NumericCellValue);
                                                }
                                                break;
                                            case CellType.Boolean:
                                                dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                break;
                                            case CellType.Error:
                                                dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                break;
                                            case CellType.Formula:
                                                switch (row.GetCell(j).CachedFormulaResultType)
                                                {
                                                    case CellType.String:
                                                        String strFORMULA = row.GetCell(j).StringCellValue;
                                                        if (strFORMULA != null && strFORMULA.Length > 0)
                                                        {
                                                            dataRow[j] = strFORMULA.ToString();
                                                        }
                                                        else
                                                        {
                                                            dataRow[j] = null;
                                                        }
                                                        break;
                                                    case CellType.Numeric:
                                                        dataRow[j] = Convert.ToString(row.GetCell(j).NumericCellValue);
                                                        break;
                                                    case CellType.Boolean:
                                                        dataRow[j] = Convert.ToString(row.GetCell(j).BooleanCellValue);
                                                        break;
                                                    case CellType.Error:
                                                        dataRow[j] = ErrorEval.GetText(row.GetCell(j).ErrorCellValue);
                                                        break;
                                                    default:
                                                        dataRow[j] = "";
                                                        break;
                                                }
                                                break;
                                            default:
                                                dataRow[j] = "";
                                                break;
                                        }
                                    }
                                }
                            }
                            catch (Exception exception)
                            {
                                LogHelper.Write(exception, "YBImportDt_1");
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception)
                    {
                        LogHelper.Write(exception, "YBImportDt_2");
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Write(exception, "YBImportDt_3");
            }
            return table;
        }

        /// <summary>
        /// 读取excel .xlsx文件内图片
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="_sDirImg"></param>
        static  List<NImgItem> ReadImageXSSF(ISheet sheet, string _sDirImg)
        {
            List<NImgItem> imgList = new List<NImgItem>();
            if (!Directory.Exists(_sDirImg))
            {
                DirectoryInfo directoryInfo = new DirectoryInfo(_sDirImg);
                directoryInfo.Create();
            }
            // 读取图像信息
            foreach (XSSFShape shape in ((XSSFDrawing)sheet.DrawingPatriarch).GetShapes())
            {
                if (shape is XSSFPicture)
                {
                    XSSFPicture picture = (XSSFPicture)shape;

                    // 获取图片所在单元格的行号和列号
                    int rowIndex = picture.GetPreferredSize().Row1;
                    int colIndex = picture.GetPreferredSize().Col1;

                    // 获取图像文件格式
                    string imageFormat = picture.PictureData.MimeType switch
                    {
                        "image/jpeg" => "jpeg",
                        "image/png" => "png",
                        "image/gif" => "gif",
                        "image/bmp" => "bmp",
                        _ => "jpg"
                    };
                    // 保存图像文件
                    string outputFileName = _sDirImg+@"\" + $"{rowIndex}-{colIndex}-{Guid.NewGuid()}.{imageFormat}";
                    using (FileStream imageFile = new FileStream(outputFileName, FileMode.Create))
                    {
                        imageFile.Write(picture.PictureData.Data, 0, picture.PictureData.Data.Length);
                    }
                    imgList.Add(new NImgItem() { RowNum = rowIndex, ColNum = colIndex, ImgPath = outputFileName });
                }
            }
            return imgList;
        }

    }

    /// <summary>
    /// 解析NPOI中的图片
    /// </summary>
    class NImgItem
    {
        /// <summary>
        /// 行号
        /// </summary>
        public int RowNum { get; set; }

        /// <summary>
        /// 列号
        /// </summary>
        public int ColNum { get; set; }

        /// <summary>
        /// 图片地址
        /// </summary>
        public string ImgPath { get; set; }
    }
}