using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System.Collections;
using System.Data;

namespace CommonUtils
{
    /// <summary>
    /// Excel操作相关
    /// </summary>
    public class ExcelHelper
    {
        #region 读取Excel到DataTable

        /// <summary>
        /// 读取Excel文件的内容
        /// </summary>
        /// <param name="path"></param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns></returns>
        public static DataTable GetDataTable(string path, string sheetName = null)
        {
            if (path.ToLower().EndsWith(".xlsx"))
                return EPPlusHelper.WorksheetToTable(path, sheetName);

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                return GetDataTable(file, sheetName);
            }
        }

        /// <summary>
        /// 从Excel文件流读取内容
        /// </summary>
        /// <param name="file"></param>
        /// <param name="sheetName"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(Stream file, string contentType, string sheetName = null)
        {
            //载入工作簿
            IWorkbook workBook = null;
            if (contentType == "application/vnd.ms-excel")
            {
                workBook = new HSSFWorkbook(file);
            }
            else if (contentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
            {
                workBook = new XSSFWorkbook(file);
            }
            else
            {
                try
                {
                    workBook = new HSSFWorkbook(file);
                }
                catch
                {
                    try
                    {
                        workBook = new XSSFWorkbook(file);
                    }
                    catch
                    {
                        throw new Exception("文件格式不被支持!");
                    }
                }
            }

            //获取工作表(sheetName为空则默认获取第一个工作表)
            var sheet = string.IsNullOrEmpty(sheetName) ? workBook.GetSheetAt(0) : workBook.GetSheet(sheetName);
            //生成DataTable
            if (sheet != null)
                return GetDataTable(sheet);
            else
                throw new Exception(string.Format("工作表{0}不存在!", sheetName ?? ""));

        }

        /// <summary>
        /// 读取工作表数据
        /// </summary>
        /// <param name="sheet"></param>
        /// <returns></returns>
        private static DataTable GetDataTable(ISheet sheet)
        {
            IEnumerator rows = sheet.GetRowEnumerator();

            DataTable dt = new DataTable(sheet.SheetName);

            //默认第一个非空行为列头
            bool isTitle = true;
            //标题行索引
            int titleRowIndex = 0;
            //默认列头后的第一个数据行，作为DataTable列类型的依据
            IRow firstDataRow = null;

            while (rows.MoveNext())
            {
                IRow row = null;
                if (rows.Current is XSSFRow)//*.xlsx
                {
                    row = (XSSFRow)rows.Current;
                }
                else//*.xls
                {
                    row = (HSSFRow)rows.Current;
                }

                //是否空行
                if (IsEmptyRow(row))
                {
                    if (isTitle)
                    {
                        titleRowIndex++;
                    }
                    continue;
                }
                else
                {
                    if (isTitle)
                    {
                        firstDataRow = sheet.GetRow(titleRowIndex + 1);//默认列头后的第一个数据行，作为DataTable列类型的依据
                    }
                }

                DataRow dr = dt.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    var cell = row.GetCell(i);

                    if (isTitle)
                    {
                        var firstDataRowCell = firstDataRow.GetCell(i);
                        if (firstDataRowCell != null || cell != null)
                        {
                            dt.Columns.Add(cell.StringCellValue.Trim());
                        }
                        else
                        {
                            dt.Columns.Add(string.Format("未知列{0}", i + 1));
                        }
                    }
                    else
                    {
                        if (i > dt.Columns.Count - 1) break;
                        dr[i] = GetCellValue(cell, dt.Columns[i].DataType);
                    }

                }
                if (!isTitle && !IsEmptyRow(dr, dt.Columns.Count))
                {
                    dt.Rows.Add(dr);
                }
                isTitle = false;
            }

            return dt;
        }

        /// <summary>
        /// 获取单元格值
        /// </summary>
        /// <param name="cell"></param>
        /// <param name="colType"></param>
        /// <returns></returns>
        private static object GetCellValue(ICell cell, Type colType)
        {
            if (cell == null || cell.ToString().ToUpper().Equals("NULL") || cell.CellType == NPOI.SS.UserModel.CellType.Blank)
                return DBNull.Value;

            object val = null;
            switch (cell.CellType)
            {
                case NPOI.SS.UserModel.CellType.Boolean:
                    val = cell.BooleanCellValue;
                    break;
                case NPOI.SS.UserModel.CellType.Numeric:
                    var cellValueStr = cell.ToString().Trim();
                    if (cellValueStr.IndexOf('-') >= 0 || cellValueStr.IndexOf('/') >= 0)
                    {
                        DateTime d = DateTime.MinValue;
                        DateTime.TryParse(cellValueStr, out d);
                        if (!d.Equals(DateTime.MinValue)) val = cellValueStr;
                    }
                    if (val == null)
                    {
                        decimal vNum = 0;
                        decimal.TryParse(cellValueStr, out vNum);
                        val = vNum;
                    }
                    break;
                case NPOI.SS.UserModel.CellType.String:
                    val = cell.StringCellValue;
                    break;
                case NPOI.SS.UserModel.CellType.Error:
                    val = cell.ErrorCellValue;
                    break;
                case NPOI.SS.UserModel.CellType.Formula:
                default:
                    val = "=" + cell.CellFormula;
                    break;
            }

            return val;
        }

        /// <summary>
        /// 检查是否空数据行
        /// </summary>
        /// <param name="dr"></param>
        /// <returns></returns>
        private static bool IsEmptyRow(DataRow dr, int colCount)
        {
            bool isEmptyRow = true;
            for (int i = 0; i < colCount; i++)
            {
                if (dr[i] != null && !dr[i].Equals(DBNull.Value))
                {
                    isEmptyRow = false;
                    break;
                }
            }
            return isEmptyRow;
        }

        /// <summary>
        /// 检查是否空的Excel行
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        private static bool IsEmptyRow(IRow row)
        {
            bool isEmptyRow = true;
            for (int i = 0; i < row.LastCellNum; i++)
            {
                if (row.GetCell(i) != null)
                {
                    isEmptyRow = false;
                    break;
                }
            }

            return isEmptyRow;
        }
        #endregion

        #region 生成DataTable到Excel

        /// <summary>
        /// 生成Excel数据到路径
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        public static void GenerateExcel(DataTable data, string path)
        {
            var workBook = GenerateExcelData(data);
            //保存至路径
            using (FileStream fs = File.OpenWrite(path)) //打开一个xls文件，如果没有则自行创建，如果存在则在创建时不要打开该文件！
            {
                workBook.Write(fs);   //向打开的这个xls文件中写入mySheet表并保存。
            }
        }

        /// <summary>
        /// 生成Excel数据到字节流
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        public static byte[] GenerateExcel(DataTable data)
        {
            var workBook = GenerateExcelData(data);
            using (MemoryStream ms = new MemoryStream())
            {
                workBook.Write(ms);
                return ms.GetBuffer();
            }
        }

        /// <summary>
        /// 生成DataTable到Excel
        /// </summary>
        /// <param name="data"></param>
        /// <param name="path"></param>
        private static IWorkbook GenerateExcelData(DataTable data)
        {
            //创建工作簿
            var workBook = new HSSFWorkbook();
            //生成文件基本信息
            GenerateSummaryInformation(workBook);
            //创建工作表
            var sheet = workBook.CreateSheet("Sheet1");
            //创建标题行
            if (data != null && data.Columns.Count > 0)
            {
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < data.Columns.Count; i++)
                {
                    var cell = row.CreateCell(i);
                    cell.SetCellValue(data.Columns[i].ColumnName);
                }
            }
            //创建数据行
            if (data != null && data.Rows.Count > 0)
            {
                for (int rowIndex = 1; rowIndex <= data.Rows.Count; rowIndex++)
                {
                    IRow row = sheet.CreateRow(rowIndex);
                    for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++)
                    {
                        var cell = row.CreateCell(colIndex);
                        var cellValue = data.Rows[rowIndex - 1][colIndex];
                        switch (data.Columns[colIndex].DataType.Name)
                        {
                            case "Byte":
                            case "Int16":
                            case "Int32":
                            case "Int64":
                            case "Decimal":
                            case "Single":
                            case "Double":
                                double doubleVal = 0;
                                if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
                                {
                                    double.TryParse(cellValue.ToString(), out doubleVal);
                                    cell.SetCellValue(doubleVal);
                                }
                                break;
                            case "DateTime":
                                DateTime dtVal = DateTime.MinValue;
                                if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
                                {
                                    DateTime.TryParse(cellValue.ToString(), out dtVal);
                                    if (dtVal != DateTime.MinValue)
                                    {
                                        cell.SetCellValue(dtVal);
                                    }
                                }
                                break;
                            default:
                                if (cellValue != null && !cellValue.Equals(System.DBNull.Value))
                                {
                                    cell.SetCellValue(cellValue.ToString());
                                }
                                break;
                        }

                    }
                }
            }

            return workBook;
        }

        /// <summary>
        /// 创建文档的基本信息(右击文件属性可看到的)
        /// </summary>
        /// <param name="workBook"></param>
        private static void GenerateSummaryInformation(HSSFWorkbook workBook)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "Company";

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "Subject";//主题
            si.Author = "Author";//作者

            workBook.DocumentSummaryInformation = dsi;
            workBook.SummaryInformation = si;
        }

        #endregion
    }
}

//using EPPlus.Extensions;
using OfficeOpenXml;
using System.Data;


namespace CommonUtils
{
    /// <summary>
    /// 使用  EPPlus 第三方的组件读取Excel
    /// </summary>
    public class EPPlusHelper
    {
        private static string GetString(object obj)
        {

            if (obj == null)
                return "";

            return obj.ToString();

        }

        /// <summary>
        ///将指定的Excel的文件转换成DataTable （Excel的第一个sheet）
        /// </summary>
        /// <param name="fullFielPath">文件的绝对路径</param>
        /// <returns></returns>
        public static DataTable WorksheetToTable(string fullFielPath, string sheetName = null)
        {
            //如果是“EPPlus”，需要指定LicenseContext。
            //EPPlus.Core 不需要指定。
            //ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            FileInfo existingFile = new FileInfo(fullFielPath);

            ExcelPackage package = new ExcelPackage(existingFile);
            ExcelWorksheet worksheet = null;

            if (string.IsNullOrEmpty(sheetName))
            {
                //不传入 sheetName 默认取第1个sheet。
                //EPPlus 索引是0
                //EPPlus.Core 索引是1
                worksheet = package.Workbook.Worksheets[1];
            }
            else
            {                
                worksheet = package.Workbook.Worksheets[sheetName];
            }
             
            if (worksheet == null)
                throw new Exception("指定的sheetName不存在");

            return WorksheetToTable(worksheet);
        }

        /// <summary>
        /// 将worksheet转成datatable
        /// </summary>
        /// <param name="worksheet">待处理的worksheet</param>
        /// <returns>返回处理后的datatable</returns>
        public static DataTable WorksheetToTable(ExcelWorksheet worksheet)
        {
            //获取worksheet的行数
            int rows = worksheet.Dimension.End.Row;
            //获取worksheet的列数
            int cols = worksheet.Dimension.End.Column;

            DataTable dt = new DataTable(worksheet.Name);
            DataRow dr = null;
            for (int i = 1; i <= rows; i++)
            {
                if (i > 1)
                    dr = dt.Rows.Add();

                for (int j = 1; j <= cols; j++)
                {
                    //默认将第一行设置为datatable的标题
                    if (i == 1)
                        dt.Columns.Add(GetString(worksheet.Cells[i, j].Value));
                    //剩下的写入datatable
                    else
                        dr[j - 1] = GetString(worksheet.Cells[i, j].Value);
                }
            }
            return dt;
        }
    }
}

// See https://aka.ms/new-console-template for more information
using CommonUtils;
using System.Data;

Console.WriteLine("Hello, World!");


try
{
    string dir = AppContext.BaseDirectory;
    //2003
    string fullName = Path.Combine(dir, "测试excel.xls");
    DataTable dt = ExcelHelper.GetDataTable(fullName);

    Console.WriteLine("Hello, World!" + dir);
    //2007
    string fullName2 = Path.Combine(dir, "测试excel.xlsx");
    //dt = ExcelHelper.GetDataTable(fullName);
    //DataTable dt2 = ExcelHelper.GetDataTable(fullName2, "sheetf");
    DataTable dt2 = ExcelHelper.GetDataTable(fullName2);

    string saveFullName = Path.Combine(dir, "save_excel.xls");
    //ExcelHelper2.ExportExcelByMemoryStream(saveFullName, dt2);
    string saveFullName2 = Path.Combine(dir, "save_excel2.xls");
    ExcelHelper.GenerateExcel(dt2, saveFullName2);

    Console.WriteLine("Hello, World!" + dir);
}
catch (Exception ex)
{
    Console.WriteLine("ex:" + ex.Message);
}


Console.ReadKey();