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.Text;
using System.Threading.Tasks;

namespace DataSendApi.Program.BLL
{
    /// <summary>
    /// Excel操作类
    /// </summary>
    public class HandleExcel
    {
        /// <summary>
        /// 读取ExcelFile
        /// </summary>
        /// <param name="filePath">文件地址</param>
        public List<List<string>> ReadFromExcelFile(string filePath)
        {
            IWorkbook wk = null;
            List<List<string>> returnLst = new List<List<string>>();
            string extension = Path.GetExtension(filePath);

            FileStream fs = File.OpenRead(filePath);
            if (extension.Equals(".xls"))
            {
                //把xls文件中的数据写入wk中
                wk = new HSSFWorkbook(fs);
            }
            else
            {
                //把xlsx文件中的数据写入wk中
                wk = new XSSFWorkbook(fs);
            }

            fs.Close();
            //读取当前表数据
            ISheet sheet = wk.GetSheetAt(0);
            IRow row = sheet.GetRow(0);  //读取当前行数据
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);  //读取当前行数据
                if (row != null)
                {
                    List<string> _temp = new List<string>();
                    //LastCellNum 是当前行的总列数
                    for (int j = 0; j < row.LastCellNum; j++)
                    {
                        string value = string.Empty;
                        if (row.GetCell(j) != null)
                            //读取该行的第j列数据
                            value = row.GetCell(j).ToString();
                        _temp.Add(value);
                    }
                    returnLst.Add(_temp);
                }
            }
            return returnLst;
        }

    }
}