using System;
using System.Collections.Generic;
using System.Reflection;
using System.Text.RegularExpressions;
using Assets.Native.tools.excel.Atrtribute;
using ClosedXML.Excel;


namespace Assets.Native.tools.excel.Util {
    using ColumnNameToIdMap = Dictionary<string, int>;
    class ExcelUtil {
        public static XLWorkbook GetWorkBook(string excelFilePath) { 
            XLWorkbook xlWorkBook = new XLWorkbook(excelFilePath);
            return xlWorkBook;
        } 
        public static IXLWorksheet GetWorkSheet(XLWorkbook xlWorkBook,string sheetName) { 
            IXLWorksheet workSheet;
            bool got = xlWorkBook.Worksheets.TryGetWorksheet(sheetName, out workSheet);
            if (got)
            {
                return workSheet;
            }
            else {
                return null;
            }
        }
        public static List<string> GetColumnNames(IXLWorksheet xlWorkSheet)
        {
            IXLRow firstRow = xlWorkSheet.FirstRow();
            int cellCount = firstRow.CellCount();
            List<string> colNames = new List<string>();
            for (int iCell = 1; iCell <= cellCount; ++iCell) {
                IXLCell cell = firstRow.Cell(iCell);
                string colName = cell.GetString();
                if (colName == "" || colName == null) {
                    break;
                }
                colNames.Add(colName);
            }
            return colNames;
        }
        public static IXLRow GetRowByValue(IXLWorksheet xlWorkSheet,string columnName,string value) {
            IXLRow firstRow = xlWorkSheet.FirstRow();
            int cellCount = firstRow.CellCount();
            int columnIndex = -1;
            for (int iCell = 1; iCell <= cellCount; ++iCell) {
                IXLCell cell = firstRow.Cell(iCell);
                string colName = cell.GetString();
                if (colName == columnName) { 
                    columnIndex = iCell;
                    break;
                }
                if (colName == "" || colName == null) {
                    break;
                }
            }
            if (columnIndex >= 0) { 
                int rowCount = xlWorkSheet.RowCount();
                for (int iRow = 2; iRow < rowCount; ++iRow)
                {
                    IXLRow row = xlWorkSheet.Row(iRow);
                    IXLCell cell = row.Cell(columnIndex);
                    string v = cell.GetString();
                    if (v == "" || v == null) {
                        return null;
                    }
                    if (v == value) {
                        return row;
                    }
                }
            }
            return null;
        }

        public static IXLRow GetRowByValue(List<IXLWorksheet> xlWorkSheet, string columnName, string value)
        {
            foreach(IXLWorksheet sheet in xlWorkSheet)
            {
                IXLRow row = GetRowByValue(sheet, columnName, value);
                if(row != null)
                {
                    return row;
                }
            }
            return null;
        }
        public static bool SetRowValue(IXLWorksheet xlWorkSheet,IXLRow row,string columnName,string value) {
            IXLRow firstRow = xlWorkSheet.FirstRow();
            int cellCount = firstRow.CellCount();
            int columnIndex = -1;
            for (int iCell = 1; iCell <= cellCount; ++iCell) {
                IXLCell cell = firstRow.Cell(iCell);
                string colName = cell.GetString();
                if (colName == columnName) { 
                    columnIndex = iCell;
                    break;
                }
            }
            if (columnIndex >= 0)
            {
                IXLCell cell = row.Cell(columnIndex);
                cell.SetValue(value);
                return true;
            }
            else {
                return false;
            }
        } 
        public static List<T> ReadExcelWithReflection<T>(string excelFilePath, string sheetName) {
            XLWorkbook xlWorkBook = GetWorkBook(excelFilePath);
            IXLWorksheet workSheet = GetWorkSheet(xlWorkBook,sheetName);

            if(workSheet != null) {
                var result = ReadExcelWithReflection<T>(workSheet);
                xlWorkBook.Dispose();
                return result;
            } else {
                xlWorkBook.Dispose();
                return new List<T>();
            }
        }

        public static List<T> ReadExcelWithReflection<T>(IXLWorksheet workSheet) {
            ColumnNameToIdMap columnNameToIdMap = new ColumnNameToIdMap();

            //解析列名
            IXLWorksheet excelSheet = workSheet;
            IXLRow firstRow = excelSheet.FirstRow();
            int cellCount = firstRow.CellCount();
            int maxColumn = 0;
            for (int iCell = 1; iCell <= cellCount; ++iCell) {
                IXLCell cell = firstRow.Cell(iCell);
                string excelValue = cell.GetString();
                if (excelValue != "") {
                    columnNameToIdMap[excelValue] = iCell;
                    maxColumn = iCell > maxColumn ? iCell : maxColumn;
                }
                else {
                    break;
                }
            }

            List<T> list = new List<T>();

            int rowCount = excelSheet.RowCount();
            for (int iRow = 2; iRow < rowCount; ++iRow) {

                IXLRow row = excelSheet.Row(iRow);
                bool allEmpty = true;
                for (int iTest = 1; iTest < maxColumn; ++iTest) {
                    IXLCell cell = row.Cell(iTest);
                    if (cell.GetString() != "") {
                        allEmpty = false;
                    }
                }
                if (allEmpty) {
                    break;
                }

                Type type = typeof(T);
                T newObj = (T)Activator.CreateInstance(type);
                list.Add(newObj);


                foreach (KeyValuePair<string, int> pair in columnNameToIdMap) {
                    int id = columnNameToIdMap[pair.Key];
                    IXLCell cell = row.Cell(id);

                    FieldInfo[] fields = type.GetFields();
                    foreach (FieldInfo fieldInfo in fields) {

                        object[] attributes = fieldInfo.GetCustomAttributes(false);
                        for (int iAttr = 0; iAttr < attributes.Length; iAttr++) {
                            object attr = attributes[iAttr];
            
                            if (attr is ExcelColumnAttribute && pair.Key.Contains(((ExcelColumnAttribute)attr).columnName) ) {
                                if (fieldInfo.FieldType == typeof(string))
                                {
                                    fieldInfo.SetValue(newObj, cell.GetString());
                                }
                                else if (fieldInfo.FieldType == typeof(int))
                                {
                                    fieldInfo.SetValue(newObj, int.Parse(cell.GetString()));
                                }
                                break;
                            }
                        }
                    }

                }
            }

            return list;
          
        }

        public static List<List<Dictionary<string, string>>> ReadExcelNoReflection(string excelFilePath) {
            XLWorkbook xlWorkBook = new XLWorkbook(excelFilePath);

            List<List<Dictionary<string, string>>> excelList = new List<List<Dictionary<string, string>>>();
            for (int i = 0; i < xlWorkBook.Worksheets.Count; i++) {
                //解析sheet
                IXLWorksheet workSheet = xlWorkBook.Worksheets.Worksheet(i + 1);
                if (workSheet != null) {
                    // 第一行列名做key
                    IXLRow firstRow = workSheet.FirstRow();
                    int cellCount = firstRow.CellCount();
                    List<string> columnNames = new List<string>();
                    for (int iCell = 1; iCell <= cellCount; ++iCell) {
                        IXLCell cell = firstRow.Cell(iCell);
                        string cellValue = cell.GetString();
                        columnNames.Add(cellValue);
                        if (cellValue == "") break;
                    }

                    //解析剩下的值
                    int rowCount = workSheet.RowCount();
                    //页数据
                    List<Dictionary<string, string>> sheetList = new List<Dictionary<string, string>>();
                    for (int iRow = 2; iRow < rowCount; ++iRow) {
                        //行数据
                        Dictionary<string, string> rowData = new Dictionary<string, string>();
                        IXLRow row = workSheet.Row(iRow);
                        cellCount = columnNames.Count;
                        bool rowAllEmpty = true;
                        for (int iCell = 1; iCell <= cellCount; ++iCell) {
                            IXLCell cell = row.Cell(iCell);
                            string cellValue = cell.GetString();
                            rowData[columnNames[iCell - 1]] = cellValue;
                            if (cellValue != "") rowAllEmpty = false;
                        }
                        sheetList.Add(rowData);
                        if (rowAllEmpty) break;
                    }
                    excelList.Add(sheetList);
                }
                else {
                    break;
                }
            }

            return excelList;
        }

        public static void ReadExcelWithReflection(string excelFilePath) {

        }
        public static List<string> GetAllWorkSheetNames( string excelFilePath ) {
            XLWorkbook xlWorkBook = new XLWorkbook(excelFilePath);
            List<string> sheetNames = new List<string>();
            for (int i = 0; i < xlWorkBook.Worksheets.Count; i++)
            {
                sheetNames.Add(xlWorkBook.Worksheets.Worksheet(i+1).Name);
            }
            return sheetNames;
        }
        public static List<string> GetWorkSheetNamesByPattern( string excelFilePath,string pattern ) {
            XLWorkbook xlWorkBook = new XLWorkbook(excelFilePath);
            List<string> sheetNames = new List<string>();
            for (int i = 0; i < xlWorkBook.Worksheets.Count; i++)
            {
                string name = xlWorkBook.Worksheets.Worksheet(i + 1).Name;
                if (Regex.IsMatch(name, pattern)) { 
                    sheetNames.Add(name);
                }
            }
            return sheetNames;
        }

        public static void SheetAddCell(IXLWorksheet sheet, List<List<string>> list)
        {
            int row = 0;
            foreach(List<string> subList in list)
            {
                row++;
                for(int i = 1; i <= subList.Count; ++i)
                {
                    sheet.Cell(row, i).SetValue(subList[i - 1]);
                }
            }

        }
    }
}
