using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using VueWebCoreApi.Models;

namespace VueWebCoreApi.Tools
{
    public class ImportExcel
    {
        public static ToMessage mes = new ToMessage(); //定义全局返回信息对象

        /// <summary>
        /// Excel导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static DataTable ExcelToTable(string file)
        {
            IWorkbook Workbook;
            DataTable table = new DataTable();
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //定位在第一个sheet
            ISheet sheet = Workbook.GetSheetAt(0);
            //第一行为标题行
            IRow headerRow = sheet.GetRow(0);
            if (headerRow == null)
            {
                return table;
            }
            int cellCount = headerRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            //循环添加标题列
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            //数据
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                if (row != null && row.Cells.Count > 0)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                        }
                    }
                }
                table.Rows.Add(dataRow);
            }
            //清除最后的空行
            //清除最后的空行
            for (int i = 0; i < table.Rows.Count; i++)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                    {
                        if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                    i = 0;
                }
            }
            return table;
        }

        /// <summary>
        /// Excel 2个Sheet导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<DataTable> ExcelToTableList(string file)
        {
            List<DataTable> list = new List<DataTable>();
            IWorkbook Workbook;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            int count = Workbook.NumberOfSheets; //获取所有SheetName
            if (count < 2)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(0);
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null)
                {
                    return list;
                }
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null && row.Cells.Count > 0)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                        i = 0;
                    }
                }
                list.Add(table);
            }
            else
            {
                for (int m = 0; m < count; m++)
                {
                    DataTable table = new DataTable();
                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(m);
                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        return list;
                    }
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null && row.Cells.Count > 0)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    list.Add(table);
                }
            }
            return list;
        }

        /// <summary>
        /// Excel 3个Sheet导入成Datable
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<DataTable> ExcelToThreeTableList(string file)
        {
            List<DataTable> list = new List<DataTable>();
            IWorkbook Workbook;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            int count = Workbook.NumberOfSheets; //获取所有SheetName
            if (count < 3)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(0);
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                if (headerRow == null)
                {
                    return list;
                }
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null && row.Cells.Count > 0)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                        i = 0;
                    }
                }
                list.Add(table);
            }
            else
            {
                for (int m = 0; m < count; m++)
                {
                    DataTable table = new DataTable();
                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(m);
                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    if (headerRow == null)
                    {
                        return list;
                    }
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null && row.Cells.Count > 0)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    list.Add(table);
                }
            }
            return list;
        }




        /// <summary>
        /// 获取单元格类型
        /// </summary>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                    //Cell为非NUMERIC时，调用IsCellDateFormatted方法会报错，所以先要进行类型判断
                    if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
                        return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd");
                    else
                    {
                        return cell.NumericCellValue.ToString();
                    }
                //if (format != 0) { return Convert.ToDateTime(cell.DateCellValue).ToString("yyyy-MM-dd"); } else { return cell.NumericCellValue.ToString(); }
                case CellType.Unknown:
                default:
                    return cell.ToString();
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }


        /// <summary>
        /// 单个Excel 不为空验证、重复数据验证
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<ExcelErro> ExcelToTableErro(string file)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            IWorkbook Workbook;
            DataTable table = new DataTable();
            bool iscell = false;
            int iscellCount = 0;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            //定位在第一个sheet
            ISheet sheet = Workbook.GetSheetAt(0);
            //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
            //第一行为标题行
            IRow headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            int rowCount0 = sheet.FirstRowNum;
            //int rowCount = sheet.LastRowNum;
            //int rowCount = sheet.PhysicalNumberOfRows;
            int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)

            if (rowCount - rowCount0 >= 1)
            {

                //-------------判断必填项是否为空----------------//
                //循环数据行
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    iscell = false;
                    iscellCount = 0;
                    IRow row = sheet.GetRow(i);
                    //判断是否有空行
                    if (row == null)
                    {
                        ExcelErro erro = new ExcelErro();
                        erro.RoeNumber = (i + 1).ToString();
                        erro.ErrorField = "空行";
                        erro.ErrorCont = "空行";
                        list.Add(erro);
                        continue;
                    }
                    //判断是否存在空数据行
                    for (int c = 0; c < cellCount; c++)
                    {
                        if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
                        {
                            iscell = false;
                        }
                        else
                        {
                            iscell = true;
                            iscellCount++;
                        }
                    }
                    if (iscell == true && iscellCount == cellCount)  //空行
                    {
                        continue;
                    }
                    else
                    {
                        //循环标题列
                        for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                        {
                            //获取单元格的填充色
                            string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
                            if (rgb == "51")  //必填项字段列头填充色
                            {
                                if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "")  //存在空行
                                {
                                    ExcelErro erro = new ExcelErro();
                                    erro.RoeNumber = i.ToString();
                                    erro.ErrorField = "{第" + i.ToString() + "行}";
                                    erro.ErrorCont = "{第" + i.ToString() + "}行为空";
                                    list.Add(erro);
                                    break;
                                }
                                else
                                {
                                    string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim();   //GetCell:返回包括空列值  Cells:只返回有值的列
                                    if (cellValue == "" || cellValue == null)
                                    {
                                        ExcelErro erro = new ExcelErro();
                                        erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
                                        erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                        erro.ErrorCont = "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
                                        list.Add(erro);
                                    }
                                }
                            }

                        }
                    }
                }
                //-------------判断唯一项是否重复----------------//
                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                        i = 0;
                    }
                }
                DataView view = new DataView(table);
                //获取所有唯一列
                List<string> list0 = new List<string>();
                //循环标题列
                for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                {
                    bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一");  //标题列中的必填列
                    if (isContain == true)
                    {
                        //
                        if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue.Trim() + "" }).Rows.Count < table.Rows.Count)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = "/";
                            erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                            erro.ErrorCont = "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
                            list.Add(erro);
                        }
                    }
                }
            }
            List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
            return list;
        }
        /// <summary>
        /// Excel 2个Sheet 数据验证
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<ExcelErro> ExcelToTableListErro(string file)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            IWorkbook Workbook;
            bool iscell = false;
            int iscellCount = 0;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            for (int mm = 0; mm < 2; mm++)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(mm);
                //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount0 = sheet.FirstRowNum;
                //int rowCount = sheet.LastRowNum;
                int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)
                if (rowCount - rowCount0 >= 1)
                {
                    //-------------判断必填项是否为空----------------//
                    //循环数据行
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        iscell = false;
                        iscellCount = 0;
                        IRow row = sheet.GetRow(i);
                        //判断是否有空行
                        if (row == null)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = (i + 1).ToString();
                            erro.ErrorField = "空行";
                            erro.ErrorCont = "空行";
                            list.Add(erro);
                            continue;
                        }
                        //判断是否存在空数据行
                        for (int c = 0; c < cellCount; c++)
                        {
                            if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
                            {
                                iscell = false;
                            }
                            else
                            {
                                iscell = true;
                                iscellCount++;
                            }
                        }
                        if (iscell == true && iscellCount == cellCount)  //空行
                        {
                            continue;
                        }
                        else
                        {
                            //循环标题列
                            for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                            {
                                //获取单元格的填充色
                                string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
                                if (rgb == "51")  //必填项字段列头填充色
                                {
                                    if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "")  //存在空行
                                    {
                                        ExcelErro erro = new ExcelErro();
                                        erro.RoeNumber = i.ToString();
                                        erro.ErrorField = "{第" + i.ToString() + "行}";
                                        erro.ErrorCont = "{第" + i.ToString() + "}行为空";
                                        list.Add(erro);
                                        break;
                                    }
                                    else
                                    {
                                        string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim();   //GetCell:返回包括空列值  Cells:只返回有值的列
                                        if (cellValue == "" || cellValue == null)
                                        {
                                            ExcelErro erro = new ExcelErro();
                                            erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
                                            erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                            erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
                                            list.Add(erro);
                                        }
                                    }
                                }

                            }
                        }
                    }
                    //-------------判断唯一项是否重复----------------//
                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    DataView view = new DataView(table);
                    //获取所有唯一列
                    List<string> list0 = new List<string>();
                    //循环标题列
                    for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                    {
                        bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一");  //标题列中的必填列
                        if (isContain == true)
                        {
                            //
                            if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
                            {
                                ExcelErro erro = new ExcelErro();
                                erro.RoeNumber = "/";
                                erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
                                list.Add(erro);
                            }
                        }
                    }
                }
            }
            List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
            return list;
        }

        /// <summary>
        /// Excel 3个Sheet 数据验证
        /// </summary>
        /// <param name="file">导入路径(包含文件名与扩展名)</param>
        /// <returns></returns>
        public static List<ExcelErro> ExcelToThreeTableListErro(string file)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            IWorkbook Workbook;
            bool iscell = false;
            int iscellCount = 0;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            for (int mm = 0; mm < 3; mm++)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(mm);
                //sheet.ShiftRows(sheet.FirstRowNum-1, sheet.LastRowNum,1);  
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount0 = sheet.FirstRowNum;
                //int rowCount = sheet.LastRowNum;
                int rowCount = Rownum(headerRow, cellCount, sheet, sheet.LastRowNum); //获取删除空行之后的数据行 (只针对于最后数据项有空行的)
                if (rowCount - rowCount0 >= 1)
                {
                    //-------------判断必填项是否为空----------------//
                    //循环数据行
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        iscell = false;
                        iscellCount = 0;
                        IRow row = sheet.GetRow(i);
                        //判断是否有空行
                        if (row == null)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = (i + 1).ToString();
                            erro.ErrorField = "空行";
                            erro.ErrorCont = "空行";
                            list.Add(erro);
                            continue;
                        }
                        //判断是否存在空数据行
                        for (int c = 0; c < cellCount; c++)
                        {
                            if (GetCellValue(row.GetCell(c)).Trim() != null && GetCellValue(row.GetCell(c)).Trim() != "")
                            {
                                iscell = false;
                            }
                            else
                            {
                                iscell = true;
                                iscellCount++;
                            }
                        }
                        if (iscell == true && iscellCount == cellCount)  //空行
                        {
                            continue;
                        }
                        else
                        {
                            //循环标题列
                            for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                            {
                                //获取单元格的填充色
                                string rgb = headerRow.Cells[j].CellStyle.FillForegroundColor.ToString();
                                if (rgb == "51")  //必填项字段列头填充色
                                {
                                    if (sheet.GetRow(i) == null || sheet.GetRow(i).ToString() == "")  //存在空行
                                    {
                                        ExcelErro erro = new ExcelErro();
                                        erro.RoeNumber = i.ToString();
                                        erro.ErrorField = "{第" + i.ToString() + "行}";
                                        erro.ErrorCont = "{第" + i.ToString() + "}行为空";
                                        list.Add(erro);
                                        break;
                                    }
                                    else
                                    {
                                        string cellValue = GetCellValue(sheet.GetRow(i).GetCell(j)).Trim();   //GetCell:返回包括空列值  Cells:只返回有值的列
                                        if (cellValue == "" || cellValue == null)
                                        {
                                            ExcelErro erro = new ExcelErro();
                                            erro.RoeNumber = (sheet.GetRow(i).RowNum + 1).ToString();
                                            erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                            erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段不能为空";
                                            list.Add(erro);
                                        }
                                    }
                                }

                            }
                        }
                    }
                    //-------------判断唯一项是否重复----------------//
                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                            i = 0;
                        }
                    }
                    DataView view = new DataView(table);
                    //获取所有唯一列
                    List<string> list0 = new List<string>();
                    //循环标题列
                    for (int j = headerRow.FirstCellNum; j < cellCount; j++)
                    {
                        bool isContain = headerRow.Cells[j].StringCellValue.Contains("唯一");  //标题列中的必填列
                        if (isContain == true)
                        {
                            //
                            if (view.ToTable(true, new string[] { "" + headerRow.Cells[j].StringCellValue + "" }).Rows.Count < table.Rows.Count)
                            {
                                ExcelErro erro = new ExcelErro();
                                erro.RoeNumber = "/";
                                erro.ErrorField = "{" + headerRow.Cells[j].StringCellValue + "}";
                                erro.ErrorCont = mm == 0 ? "主表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据" : "子表中：" + "{" + headerRow.Cells[j].StringCellValue + "}字段有重复数据";
                                list.Add(erro);
                            }
                        }
                    }
                }
            }
            List<ExcelErro> stuList = list.OrderBy(s => s.RoeNumber).ToList<ExcelErro>();
            return list;
        }







        #region【数据量验证】
        public static ToMessage ExcelCheckCountSum(string file)
        {
            IWorkbook Workbook;
            try
            {
                using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
                {
                    //XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
                    string fileExt = Path.GetExtension(file).ToLower();
                    if (fileExt == ".xls")
                    {
                        Workbook = new HSSFWorkbook(fileStream);
                    }
                    else if (fileExt == ".xlsx")
                    {
                        Workbook = new XSSFWorkbook(fileStream);
                    }
                    else
                    {
                        Workbook = null;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            int count = Workbook.NumberOfSheets; //获取所有SheetName
            if (count < 2)
            {
                DataTable table = new DataTable();
                //定位在第一个sheet
                ISheet sheet = Workbook.GetSheetAt(0);
                //第一行为标题行
                IRow headerRow = sheet.GetRow(0);
                int cellCount = headerRow.LastCellNum;
                int rowCount = sheet.LastRowNum;

                //循环添加标题列
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                //数据
                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null)
                            {
                                dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                }
                //清除最后的空行
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    bool isnull = true;
                    for (int j = 0; j < table.Columns.Count; j++)
                    {
                        if (table.Rows[i][j] != null)
                        {
                            if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                            {
                                isnull = false;
                                break;
                            }
                        }
                    }
                    if (isnull)
                    {
                        table.Rows[i].Delete();
                    }
                }
                if (table.Rows.Count <= 0)
                {

                    mes.code = "300";
                    mes.message = "文件数据不能为空！";

                }
            }
            else
            {
                for (int m = 0; m < 2; m++)
                {
                    DataTable table = new DataTable();
                    //定位在第一个sheet
                    ISheet sheet = Workbook.GetSheetAt(m);
                    //第一行为标题行
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    int rowCount = sheet.LastRowNum;

                    //循环添加标题列
                    for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                    {
                        DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                        table.Columns.Add(column);
                    }

                    //数据
                    for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        DataRow dataRow = table.NewRow();
                        if (row != null)
                        {
                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                {
                                    dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                                }
                            }
                        }
                        table.Rows.Add(dataRow);
                    }
                    //清除最后的空行
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        bool isnull = true;
                        for (int j = 0; j < table.Columns.Count; j++)
                        {
                            if (table.Rows[i][j] != null)
                            {
                                if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                                {
                                    isnull = false;
                                    break;
                                }
                            }
                        }
                        if (isnull)
                        {
                            table.Rows[i].Delete();
                        }
                    }
                    if (table.Rows.Count <= 0)
                    {
                        mes.code = "300";
                        mes.message = "主子表文件中数据不能为空！";
                    }
                }
            }
            return mes;
        }

        #endregion


        #region【去除空行后获取行数】
        public static int Rownum(IRow headerRow, int cellCount, ISheet sheet, int rowCount)
        {
            DataTable table = new DataTable();
            //循环添加标题列
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            //数据
            for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = GetCellValue(row.GetCell(j)).Trim();
                        }
                    }
                }
                table.Rows.Add(dataRow);
            }
            //清除最后的空行
            for (int i = 0; i < table.Rows.Count; i++)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j].ToString() != null && table.Rows[i][j].ToString() != "")
                    {
                        if (table.Rows[i][j].ToString().Trim() != "" && table.Rows[i][j].ToString().Trim() != null)
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
                {
                    table.Rows[i].Delete();
                    i = 0;
                }
            }
            return table.Rows.Count;
        }
        #endregion


        #region
        public static List<ExcelErro> InportExcelToTableListErro(List<DataTable> excelTable)
        {
            List<ExcelErro> list = new List<ExcelErro>();
            for (int i = 0; i < excelTable.Count; i++)
            {
                var emptyColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
                    column => column.ColumnName.Contains("*") && excelTable[i].AsEnumerable().Any(row => row.IsNull(column))
                    );

                if (emptyColumn != null)
                {
                    int columnIndex = excelTable[i].Columns.IndexOf(emptyColumn);
                    int rowIndex = excelTable[i].AsEnumerable().ToList().FindIndex(row => row.IsNull(emptyColumn));
                    ExcelErro erro = new ExcelErro();
                    erro.RoeNumber = (rowIndex + 1).ToString();
                    erro.ErrorField = emptyColumn.ColumnName;
                    erro.ErrorCont = "模板表头带*的列中存在空值,第" + (rowIndex + 1).ToString() + "行,必填字段：" + emptyColumn.ColumnName + "为空";
                    list.Add(erro);
                }
                if (excelTable[i].Columns.Cast<DataColumn>().Any(column => column.ColumnName.Contains("M")))
                {
                    var duplicateColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
                        column => column.ColumnName.Contains("M") && excelTable[i].AsEnumerable().GroupBy(row => new { Field1 = row[column], Field2 = column.ColumnName.Contains("唯一") }).Any(group => group.Count() > 1));
                    if (duplicateColumn != null)
                    {
                        int columnIndex = excelTable[i].Columns.IndexOf(duplicateColumn);
                        var duplicateRows = excelTable[i].AsEnumerable()
                            .Where(row => row[duplicateColumn] != DBNull.Value)
                            .GroupBy(row => row[duplicateColumn])
                            .Where(group => group.Count() > 1)
                            .SelectMany(group => group.ToList())
                            .ToList();
                        foreach (var row in duplicateRows)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = excelTable[i].Rows.IndexOf(row).ToString();
                            erro.ErrorField = duplicateColumn.ColumnName;
                            erro.ErrorCont = "模板表头字段：" + duplicateColumn.ColumnName + "下同一值,对应带(唯一)的列中存在重复值";
                            list.Add(erro);

                        }
                    }
                }
                else 
                {
                    var duplicateColumn = excelTable[i].Columns.Cast<DataColumn>().FirstOrDefault(
                        column => column.ColumnName.Contains("唯一") && excelTable[i].AsEnumerable().GroupBy(row => row[column]).Any(group => group.Count() > 1));
                    if (duplicateColumn != null)
                    {
                        int columnIndex = excelTable[i].Columns.IndexOf(duplicateColumn);
                        var duplicateRows = excelTable[i].AsEnumerable()
                            .Where(row => row[duplicateColumn] != DBNull.Value)
                            .GroupBy(row => row[duplicateColumn])
                            .Where(group => group.Count() > 1)
                            .SelectMany(group => group.ToList())
                            .ToList();
                        foreach (var row in duplicateRows)
                        {
                            ExcelErro erro = new ExcelErro();
                            erro.RoeNumber = excelTable[i].Rows.IndexOf(row).ToString();
                            erro.ErrorField = duplicateColumn.ColumnName;
                            erro.ErrorCont = "模板表头带(唯一)的列中存在重复值,第" + excelTable[i].Rows.IndexOf(row).ToString() + "行,必填字段：" + duplicateColumn.ColumnName + "重复";
                            list.Add(erro);

                        }
                    }
                }
               
               
            }
            return list;
        }
        #endregion
    }
}