using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;

namespace JavaCode.cs
{
    /// <summary>
    /// 读取Excel类
    /// </summary>
    public class ReadExcel
    {
        private string intName = typeof(int).Name;

        private string doubleName = typeof(double).Name;

        private string stringName = typeof(string).Name;

        /// <summary>
        /// 读取Excel
        /// </summary>
        public static DataTable ReadXls(string file, int index = 0)
        {
            if (!File.Exists(file)) return null;

            string ext = Path.GetExtension(file).ToLower();
            using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
            {
                //IWorkbook wk = ext == ".xls" ? new HSSFWorkbook(fs) : new XSSFWorkbook();
                IWorkbook wk = new HSSFWorkbook(fs);
                ISheet sheet = wk.GetSheetAt(index);
                if (sheet == null)
                {
                    return null;
                }

                return SheetToDataTable(sheet);
            }
        }

        /// <summary>
        /// Sheet转换为DataTable
        /// </summary>
        public static DataTable SheetToDataTable(ISheet sheet)
        {
            IRow firstRow = sheet.GetRow(0);
            int cellCount = firstRow.LastCellNum;
            int rowCount = sheet.LastRowNum;

            DataTable dt = CreateDataTable(cellCount, sheet.GetRow(sheet.FirstRowNum));
            //for (int i = sheet.FirstRowNum; i <= rowCount; i++)
            for (int i = sheet.FirstRowNum + 1; i <= rowCount; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)
                {
                    continue;
                }

                DataRow dtRow = dt.NewRow();
                for (int j = 0; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dtRow[j] = cell == null ? string.Empty : cell.ToString();
                }

                dt.Rows.Add(dtRow);
            }

            return dt;
        }

        /// <summary>
        /// 创建DataTable
        /// </summary>
        public static DataTable CreateDataTable(int columns, IRow row)
        {
            DataTable dt = new DataTable();
            for (int i = 0; i < columns; i++)
            {
                //DataColumn dc = new DataColumn("Col_" + i, typeof(string));
                string colName = null == row || null == row.GetCell(i) ? "Col_" + i : row.GetCell(i).ToString();
                DataColumn dc = new DataColumn(colName, typeof(string));
                dt.Columns.Add(dc);
            }

            return dt;
        }

        // 查找列位置
        private int FindColumn(DataRow firstRow, int columns, string name)
        {
            for (int i = 0, c = columns; i < c; i++)
            {
                string colName = firstRow[i] == null ? null : firstRow[i].ToString().Trim();
                if (string.IsNullOrEmpty(colName))
                {
                    break;
                }

                if (name == colName)
                {
                    return i;
                }
            }

            return -1;
        }
    }
}