using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApplication3
{
    public class ExcelHelper
    {

        public static System.Data.DataSet GetTablesFromTxt(string path, string splitChar, int startLine, string endWith)
        {
            int i = 0;
            System.Collections.ArrayList tablelist = new System.Collections.ArrayList();
            System.Data.DataTable table = null;
            string s = "";
            System.Data.DataSet ds = new System.Data.DataSet();
            //using (FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            //{

            System.Data.DataTable tmp = null;
            using (StreamReader sr = new StreamReader(path, System.Text.Encoding.Default))
            {
                while (!string.IsNullOrEmpty(s = sr.ReadLine()))
                {

                    if (i >= startLine - 1)
                    {
                        string[] list = s.Split(new string[] { splitChar }, StringSplitOptions.None);
                        if (tmp == null)
                        {
                            tmp = new System.Data.DataTable();
                            //table = new System.Data.DataTable();
                            foreach (string t in list)
                            {
                                tmp.Columns.Add(new System.Data.DataColumn());
                            }
                            table = tmp.Clone();
                            if (!string.IsNullOrEmpty(endWith) && list[0].Contains(endWith))
                            {
                                break;
                            }
                            var row = table.NewRow();
                            for (var k = 0; k < list.Length; k++)
                            {

                                row[k] = list[k];
                            }
                            table.Rows.Add(row);

                        }
                        else
                        {
                            var row = table.NewRow();
                            for (var k = 0; k < list.Length; k++)
                            {
                                row[k] = list[k];
                            }
                            table.Rows.Add(row);
                            /*
                            if ((i + 1) % 200000 == 0)
                            {
                                ds.Tables.Add(table);
                                table = new System.Data.DataTable();
                                table = tmp.Clone();

                            }*/
                        }
                    }
                    i++;
                }
                if (table.Rows.Count > 0)
                {
                    ds.Tables.Add(table);
                }
            }

            //}
            return ds;
        }
        /// <summary>
        /// 读取指定Excel所有Sheet
        /// </summary>
        /// <param name="path">文件路径</param>
        /// <returns></returns>
        public static DataSet ReadDataSet(string path)
        {
            DataSet retSet = new DataSet();
            using (FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = WorkbookFactory.Create(stream);
                var sheetCount = workbook.NumberOfSheets;
                for (int i = 0; i < sheetCount; i++)
                {
                    var sheet = workbook.GetSheetAt(i);
                    retSet.Tables.Add(ReadTable(sheet, 0, 0));
                }
            }
            return retSet;
        }
        /// <summary>
        /// 读取指定索引Sheet的Excel文件内容，返回DataTable
        /// </summary>
        /// <param name="path">excel文件物理路径</param>
        /// <param name="sheetIndex">页签索引，从0开始</param>
        /// <param name="titleIndex">表头索引，从0开始，如果没有表头，请填-1，如果表头在第二行，请填1</param>
        /// <param name="lastRowDeduction">数据最后一行索引，如果后三行是统计之类的，请填-3</param>
        /// <returns>返回DataTable，TableName为对应SheetName</returns>
        public static DataTable ReadTable(string path, int sheetIndex, int titleIndex, int lastRowDeduction)
        {
            using (FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                IWorkbook workbook = WorkbookFactory.Create(stream);
                ISheet sheet = workbook.GetSheetAt(sheetIndex);
                return ReadTable(sheet, titleIndex, lastRowDeduction);
            }
        }




        private static DataTable ReadTable(ISheet sheet, int titleIndex, int lastRowDeduction)
        {
            var retDatTable = new DataTable();
            retDatTable.TableName = sheet.SheetName;
            if (titleIndex < -2)
            {
                throw new Exception("无效的表头索引值！最小值为-1！");
            }
            IRow headerRow = null;
            var hasHead = true;
            //无表头，纯数据
            if (titleIndex == -1)
            {
                headerRow = sheet.GetRow(0);//仅用于取列数用
                hasHead = false;
            }
            else
            {
                headerRow = sheet.GetRow(titleIndex);
            }
            if (headerRow == null)
            {
                return retDatTable;
            }
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                //无表头
                if (!hasHead)
                {
                    retDatTable.Columns.Add("Column" + i);
                    continue;
                }
                //处理有表头的
                var cell = headerRow.GetCell(i);
                var title = string.Empty;
                if (cell != null)
                {
                    headerRow.GetCell(i).SetCellType(CellType.String);
                    title = cell.StringCellValue;
                }
                else
                {
                    title = Guid.NewGuid().ToString();
                }
                retDatTable.Columns.Add(title);
            }


            //最后一行的标号  即总的行数
            int rowCount = sheet.LastRowNum;
            rowCount += lastRowDeduction;

            for (int i = (titleIndex + 1 - 1); i <= rowCount; i++)
            {
                var row = sheet.GetRow(i);
                DataRow dataRow = retDatTable.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    var cell = row.GetCell(j);
                    if (cell != null)
                        try
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Numeric:
                                    //NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//其他数字类型
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                    break;
                                case CellType.Formula:
                                    IFormulaEvaluator eva = null;
                                    var workType = sheet.Workbook.GetType();
                                    if (workType.Name == "XSSFWorkbook")
                                    {
                                        eva = new XSSFFormulaEvaluator(sheet.Workbook);
                                    }
                                    else
                                    {
                                        eva = new HSSFFormulaEvaluator(sheet.Workbook);
                                    }
                                    dataRow[j] = eva.Evaluate(cell).FormatAsString();
                                    break;
                                case CellType.Blank:
                                    dataRow[j] = "";
                                    break;
                                case CellType.Unknown:
                                case CellType.Boolean:
                                case CellType.Error:
                                case CellType.String:
                                    dataRow[j] = cell.StringCellValue; ;
                                    break;
                                default:
                                    break;
                            }
                        }
                        catch { }
                }
                retDatTable.Rows.Add(dataRow);
            }
            return retDatTable;
        }




    }
}