using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.IO.Compression;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace Mall.Common.Plugin
{
    /// <summary>
    /// NPOI Excel 带图片的数据导入
    /// </summary>
    public class ImportExcelNPOIHelper
    {
        /// <summary>
        /// 文件解压路劲
        /// </summary>
        private string RootPath;

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="unzipPath"></param>
        public ImportExcelNPOIHelper(string unzipPath)
        {
            RootPath = unzipPath;
        }

        /// <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>
        /// <returns></returns>
        public  DataTable ExcelToDataTable(String strFileName, int SheetIndex, int HeaderRowIndex, bool needHeader, List<int> imgColList = null)
        {
            //图片位置数据
            List<Tuple<int, int, string>> pictureList = new List<Tuple<int, int, string>>();
            if (imgColList != null && imgColList.Count > 0)
            {
                ExtarctExcel(strFileName);
                // 先读出图片对应位置
                pictureList = FindPicCell();
            }
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            DataTable table = AnalysisExcelToDataTable(sheet, HeaderRowIndex, needHeader, pictureList, imgColList: imgColList);
            return table;
        }

        /// <summary>
        /// 将制定sheet中的数据导出到datatable中
        /// </summary>
        /// <param name="sheet">需要导出的sheet</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <param name="needHeader">是否导出表头</param>
        /// <param name="pictureList">图片信息</param>
        /// <param name="imgColList">图片所在的列</param>
        /// <returns></returns>
        private DataTable AnalysisExcelToDataTable(ISheet sheet, int HeaderRowIndex, bool needHeader, List<Tuple<int, int, string>> pictureList, List<int> imgColList = null)
        {
            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 (imgColList != null && imgColList.Count > 0 && imgColList.Contains(j))
                                {
                                    var tempList = pictureList.Where(qitem => qitem.Item1 == i && qitem.Item2 == j)?.ToList();
                                    dataRow[j] = string.Join(",", tempList?.Select(qitem => qitem.Item3));
                                }
                                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 exception1)
                            {
                                Console.WriteLine("异常1::" + exception1.Message);
                                LogHelper.Write(exception1, "AnalysisExcelToDataTable_1");
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    catch (Exception exception)
                    {
                        Console.WriteLine("异常2::" + exception.Message);
                        LogHelper.Write(exception, "AnalysisExcelToDataTable_2");
                    }
                }
            }
            catch (Exception exception)
            {
                LogHelper.Write(exception, "AnalysisExcelToDataTable_3");
            }
            return table;
        }
        //public void ExcelToString(string filePath)
        //{
        //    Console.WriteLine("开始.............");

        //    // 解压Excel文件
        //    ExtarctExcel(filePath);

        //    // 先读出图片对应位置
        //    List<Tuple<int, int, string>> PictureInfo = FindPicCell();

        //    IWorkbook wk = null;
        //    string extension = Path.GetExtension(filePath);  // 接收文件扩展名，需要判断.xls还是.xlsx

        //    using (FileStream fs = File.OpenRead(filePath))
        //    {
        //        if (extension.Equals(".xls"))
        //        {
        //            wk = new HSSFWorkbook(fs);
        //        }
        //        if (extension.Equals(".xlsx"))
        //        {
        //            wk = new XSSFWorkbook(fs);
        //        }

        //        // 读取数据
        //        ISheet sheet = wk.GetSheetAt(0);  // 读当前表
        //        IRow row = sheet.GetRow(0);  // 读当前行
        //        // LastRowNum是当前表的总行

        //        int offset = 0;
        //        for (int i = 0; i < sheet.LastRowNum; i++)
        //        {
        //            row = sheet.GetRow(i);  // 循环读取每一个行
        //            if (row != null)
        //            {
        //                // LastCellNum是当前行的总列数
        //                for (int j = 0; j < row.LastCellNum; j++)
        //                {
        //                    // 读取该cell的数据
        //                    string value = row.GetCell(j).ToString();
        //                    Console.Write(value + " ");
        //                }
        //                Console.WriteLine();
        //            }
        //        }
        //        // 读取图片数据List中的图片及Cell位置
        //        foreach (var picInfo in PictureInfo)
        //        {
        //            Console.WriteLine("row: " + picInfo.Item1 + " column: " + picInfo.Item2 + " ,path: " + picInfo.Item3);
        //        }
        //    }
        //    Console.WriteLine("完成！");

        //    // 这里可以开始下一步操作，save to DB or other.
        //}

        /// <summary>
        /// 解压Excel文件
        /// </summary>
        /// <param name="_file"></param>
        private void ExtarctExcel(string _file)
        {
            if (Directory.Exists(RootPath))
            {
                // 如果目录存在文件，直接全部删除
                DirectoryInfo di = new DirectoryInfo(RootPath);
                di.Delete(true);
            }
            ZipFile.ExtractToDirectory(_file, RootPath);
        }

        /// <summary>
        /// 查找图片及图片位置
        /// </summary>
        /// <returns></returns>
        private List<Tuple<int, int, string>> FindPicCell()
        {
            string _file = Path.Combine(RootPath, "xl/drawings/drawing1.xml");  // 图片信息文件
            List<Tuple<int, int, string>> PictureInfo = new List<Tuple<int, int, string>> { };  // 存放返回的图片信息格式(row, column, path)
            List<Tuple<string, string>> PictureTargetList = new List<Tuple<string, string>> { };  // 存放图片ID和路径对应关系的List

            // 先获取图片文件的路径信息
            FindPicPathByID(ref PictureTargetList);

            // 默认xml命名空间
            XNamespace xdr = "http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing";
            XNamespace a = "http://schemas.openxmlformats.org/drawingml/2006/main";
            XNamespace r;

            //string xml = Read(_file);
            XDocument xDoc = XDocument.Load(_file);

            // 给xml命名空间赋文件中的当前值
            var root = xDoc.Root;
            foreach (var item in root.Attributes())
            {
                if (item.Name.LocalName == "xdr")
                {
                    xdr = item.Value;
                }
                else if (item.Name.LocalName == "a")
                {
                    a = item.Value;
                }
            }

            foreach (var node in xDoc.Descendants(xdr + "twoCellAnchor"))
            {
                var nFrom = (XElement)node.FirstNode;
                var nTo = (XElement)nFrom.NextNode;
                var nPic = ((XElement)((XElement)((XElement)nTo.NextNode).FirstNode.NextNode).FirstNode);
                // 找到起始行和列
                string StartRow = ((XElement)((XElement)nFrom).FirstNode.NextNode.NextNode).Value;
                string StartCol = ((XElement)((XElement)nFrom).FirstNode).Value;
                // 找节点中的r的命名空间，如果找不到返回默认命名空间
                r = nPic.FirstAttribute.IsNamespaceDeclaration ? nPic.FirstAttribute.Value : "http://schemas.openxmlformats.org/officeDocument/2006/relationships";
                string nPicId = (nPic.Attribute(r + "embed") != null ? nPic.Attribute(r + "embed") : nPic.Attribute(r + "link")).Value.ToString();

                // 通过图片ID找到路径
                string PicPath = "";
                foreach (var tupleItem in PictureTargetList)
                {
                    if (tupleItem.Item1 == nPicId)
                    {
                        PicPath = tupleItem.Item2;
                        if (PicPath.StartsWith(".."))
                        {
                            PicPath = PicPath.Replace("..", Path.Combine(RootPath, "xl"));
                        }
                    }
                }
                PictureInfo.Add(new Tuple<int, int, string>(int.Parse(StartRow), int.Parse(StartCol), PicPath));
            }
            return PictureInfo;
        }

        private void FindPicPathByID(ref List<Tuple<string, string>> PictureTargetList, int _id = 1)
        {
            string _file = Path.Combine(RootPath, $"xl/drawings/_rels/drawing{_id}.xml.rels");  // 图片对应关系文件
            XDocument xDoc = XDocument.Load(_file);
            var root = xDoc.Root;

            foreach (XElement node in root.Nodes())
            {
                var attrs = node.Attributes();
                string Id = "";
                string Target = "";
                foreach (var attr in attrs)
                {
                    if (attr.Name == "Id")
                    {
                        Id = attr.Value.ToString();
                    }

                    else if (attr.Name == "Target")
                    {
                        Target = attr.Value.ToString();
                    }
                }
                PictureTargetList.Add(new Tuple<string, string>(Id, Target));
            }
        }
    }
}
