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

namespace JiepeiWMS.Common.Helper
{
    public class ExcelHelper
    {
        #region 导出Excel文件
        /// <summary>
        /// 导出Excel文件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList">数据</param>
        /// <param name="headers">表头</param>
        /// <returns></returns>
        public static string CreateExcelFromList<T>(List<T> dataList, List<string> headers)
        {
            string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
            if (!Directory.Exists(sWebRootFolder))
            {
                Directory.CreateDirectory(sWebRootFolder);
            }
            string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            var path = Path.Combine(sWebRootFolder, sFileName);
            FileInfo file = new FileInfo(path);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(path);
            }
            using (ExcelPackage package = new ExcelPackage(file))
            {
                //创建sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
                worksheet.Cells.LoadFromCollection(dataList, true);
                //表头字段
                for (int i = 0; i < headers.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = headers[i];
                }
                package.Save();
            }
            return path;
        }

        /// <summary>
        /// 生成Excle 内存流
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataList"></param>
        /// <param name="headers"></param>
        /// <returns></returns>
        public static Stream CreateExcelStreamFromList<T>(List<T> dataList, List<string> headers)
        {
            
            string sWebRootFolder = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "tempExcel");
            if (!Directory.Exists(sWebRootFolder))
            {
                Directory.CreateDirectory(sWebRootFolder);
            }

         
            string sFileName = $@"tempExcel_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx";
            var path = Path.Combine(sWebRootFolder, sFileName);
            FileInfo file = new FileInfo(path);
            if (file.Exists)
            {
                file.Delete();
                file = new FileInfo(path);
            }

            MemoryStream ms = new MemoryStream();    

            using (ExcelPackage package = new ExcelPackage(file))
            {
                //创建sheet
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("sheet1");
                worksheet.Cells.LoadFromCollection(dataList, true);
                //表头字段
                for (int i = 0; i < headers.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = headers[i];
                }
                package.SaveAs(ms);
                ms.Flush();
                ms.Position = 0;//指定当前流的位置从0开始
                return ms;
            }
         
        }


        #endregion

        #region 导入Excel文件

        /// <summary>
        /// Aspose.Cells 读取Excel文件 只读第一个sheet
        /// </summary>
        /// <param name="FileName"></param> 
        public static DataTable ImportExcel(string FileName)
        {

            Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(FileName);

            Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(FileName);
            Aspose.Cells.Cells cells = workbook.Worksheets[0].Cells;
            System.Data.DataTable dt = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxColumn + 1,true);
            return dt;
        }


        /// <summary>
        /// NPOI读取Excel文件
        /// </summary>
        /// <param name="fileName"></param>
        /// <param name="sheetname"></param>
        public static List<DataTable> Read(string fileName)
        {
            try
            {
                List<DataTable> tables = new List<DataTable>();
                if (!File.Exists(fileName))
                    return tables;
                var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
                IWorkbook workbook = null;
                if (fileName.IndexOf(".xlsx") > 0)
                {
                    // 2007版本
                    workbook = new XSSFWorkbook(fs);
                }
                else if (fileName.IndexOf(".xls") > 0)
                {
                    // 2003版本
                    workbook = new HSSFWorkbook(fs);
                }

                var workbooks = workbook.GetEnumerator();
                while (workbooks.MoveNext())
                {
                    ISheet sheet = workbooks.Current as ISheet;
                    DataTable dt = new DataTable(sheet.SheetName);
                    var rows = sheet.GetRowEnumerator();
                    while (rows.MoveNext())
                    {
                        IRow row = rows.Current as IRow;
                        if (row.RowNum == 0)
                        {
                            row.Cells.ForEach(cell =>
                            {
                                dt.Columns.Add(cell.StringCellValue);
                            });
                        }
                        else
                        {
                            var dr = dt.NewRow();
                            for (int i = 0; i < row.Cells.Count; i++)
                            {
                                dr[i] = row.Cells[i].ToString();
                            }
                            dt.Rows.Add(dr);
                        }
                    }

                    tables.Add(dt);
                }
                return tables;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        public static int GradeForSort(string classGrade)
        {
            var Level = classGrade switch
            {
                "一级" => 1,
                "二级" => 2,
                "三级" => 3,
                "四级" => 4,
                "五级" => 5,
                "六级" => 6,
                "七级" => 7,
                "八级" => 8,
                "九级" => 9,
                "十级" => 10,
                _ => 0,
            };
            return Level;
        }
        #endregion



    }
}