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;

namespace BLL.Common
{
    public class NPOIHelper
    {
        /// <summary>
        /// Excel转换成DataTable（.xls）
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <returns></returns>
        public static DataTable ExcelToDataTable(string filePath)
        {
            var dt = new DataTable();
            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var hssfworkbook = new HSSFWorkbook(file);
                var sheet = hssfworkbook.GetSheetAt(0);
                for (var j = 0; j < 5; j++)
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());
                }
                var rows = sheet.GetRowEnumerator();
                while (rows.MoveNext())
                {
                    var row = (HSSFRow)rows.Current;
                    var dr = dt.NewRow();
                    for (var i = 0; i < row.LastCellNum; i++)
                    {
                        var cell = row.GetCell(i);
                        if (cell == null)
                        {
                            dr[i] = null;
                        }
                        else
                        {
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    dr[i] = "[null]";
                                    break;
                                case CellType.Boolean:
                                    dr[i] = cell.BooleanCellValue;
                                    break;
                                case CellType.Numeric:
                                    dr[i] = cell.ToString();
                                    break;
                                case CellType.String:
                                    dr[i] = cell.StringCellValue;
                                    break;
                                case CellType.Error:
                                    dr[i] = cell.ErrorCellValue;
                                    break;
                                case CellType.Formula:
                                    try
                                    {
                                        dr[i] = cell.NumericCellValue;
                                    }
                                    catch
                                    {
                                        dr[i] = cell.StringCellValue;
                                    }
                                    break;
                                default:
                                    dr[i] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    dt.Rows.Add(dr);
                }
            }
            return dt;
        }


        public static DataTable ExcelToDataTable1(string filePath)
        {
            var dt = new DataTable();
            using (var file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                var hssfworkbook = new HSSFWorkbook(file);
                var sheet = hssfworkbook.GetSheetAt(0);
                var rows = sheet.GetRowEnumerator();
                rows.MoveNext();
                var row = (HSSFRow)rows.Current;
                try
                {
                    for (var j = 0; j < row.LastCellNum; j++)
                    {                       
                        var cell = row.GetCell(j);
                        if (cell != null)
                        {
                            dt.Columns.Add(cell.StringCellValue);
                        }
                        else
                        {
                            dt.Columns.Add("collum" + j);
                        }

                    }
                    while (rows.MoveNext())
                    {
                        row = (HSSFRow)rows.Current;
                        var dr = dt.NewRow();
                        for (var i = 0; i < row.LastCellNum; i++)
                        {
                            var cell = row.GetCell(i);
                            if (cell == null)
                            {
                                dr[i] = null;
                            }
                            else
                            {
                                switch (cell.CellType)
                                {
                                    case CellType.Blank:
                                        //dr[i] = "[null]";
                                        break;
                                    case CellType.Boolean:
                                        dr[i] = cell.BooleanCellValue;
                                        break;
                                    case CellType.Numeric:
                                        dr[i] = cell.ToString();
                                        break;
                                    case CellType.String:
                                        dr[i] = cell.StringCellValue;
                                        break;
                                    case CellType.Error:
                                        dr[i] = cell.ErrorCellValue;
                                        break;
                                    case CellType.Formula:
                                        try
                                        {
                                            dr[i] = cell.NumericCellValue;
                                        }
                                        catch
                                        {
                                            dr[i] = cell.StringCellValue;
                                        }
                                        break;
                                    default:
                                        dr[i] = "=" + cell.CellFormula;
                                        break;
                                }
                            }
                        }
                        dt.Rows.Add(dr);
                    }
                }
                catch (Exception ex)
                {                   
                }
            }
            return dt;
        }



        /// <summary>
        /// Excel转换成DataSet（.xlsx/.xls）
        /// </summary>
        /// <param name="filePath">Excel文件路径</param>
        /// <param name="strMsg"></param>
        /// <param firstTitle="strMsg">第一行是否为表头</param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string filePath, out string strMsg, bool firstTitle = false)
        {
            strMsg = "";
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            string fileType = Path.GetExtension(filePath).ToLower();
            string fileName = Path.GetFileName(filePath).ToLower();
            try
            {
                ISheet sheet = null;
                int sheetNumber = 0;
                FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
                if (fileType == ".xlsx")
                {
                    // 2007版本
                    XSSFWorkbook workbook = new XSSFWorkbook(fs);
                    sheetNumber = workbook.NumberOfSheets;
                    for (int i = 0; i < sheetNumber; i++)
                    {
                        string sheetName = workbook.GetSheetName(i);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet != null)
                        {
                            dt = firstTitle ? GetFirstSheetDataTable(sheet, out strMsg) : GetSheetDataTable(sheet, out strMsg);
                            if (dt != null)
                            {
                                dt.TableName = sheetName.Trim();
                                ds.Tables.Add(dt);
                            }
                            else
                            {
                                strMsg = "Sheet数据获取失败，原因：" + strMsg;
                            }
                        }
                    }
                }
                else if (fileType == ".xls")
                {
                    // 2003版本
                    HSSFWorkbook workbook = new HSSFWorkbook(fs);
                    sheetNumber = workbook.NumberOfSheets;
                    for (int i = 0; i < sheetNumber; i++)
                    {
                        string sheetName = workbook.GetSheetName(i);
                        sheet = workbook.GetSheet(sheetName);
                        if (sheet != null)
                        {
                            //dt = GetSheetDataTable(sheet, out strMsg);
                            dt = firstTitle ? GetFirstSheetDataTable(sheet, out strMsg) : GetSheetDataTable(sheet, out strMsg);
                            if (dt != null)
                            {
                                dt.TableName = sheetName.Trim();
                                ds.Tables.Add(dt);
                            }
                            else
                            {
                                strMsg = "Sheet数据获取失败，原因：" + strMsg;
                            }
                        }
                    }
                }
                return ds;
            }
            catch (Exception ex)
            {
                strMsg = ex.Message;
                return null;
            }
        }
        /// <summary>
        /// 获取sheet表对应的DataTable
        /// </summary>
        /// <param name="sheet">Excel工作表</param>
        /// <param name="strMsg"></param>
        /// <returns></returns>
        private static DataTable GetSheetDataTable(ISheet sheet, out string strMsg)
        {
            strMsg = "";
            DataTable dt = new DataTable();
            string sheetName = sheet.SheetName;
            int startIndex = 0;// sheet.FirstRowNum;
            int lastIndex = sheet.LastRowNum;
            //最大列数
            int cellCount = 0;
            IRow maxRow = sheet.GetRow(0);
            for (int i = startIndex; i <= lastIndex; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row != null && cellCount < row.LastCellNum)
                {
                    cellCount = row.LastCellNum;
                    maxRow = row;
                }
            }
            //列名设置
            try
            {
                for (int i = 0; i < maxRow.LastCellNum; i++)//maxRow.FirstCellNum
                {
                    dt.Columns.Add(Convert.ToChar(((int)'A') + i).ToString());
                    //DataColumn column = new DataColumn("Column" + (i + 1).ToString());
                    //dt.Columns.Add(column);
                }
            }
            catch
            {
                strMsg = "工作表" + sheetName + "中无数据";
                return null;
            }
            //数据填充
            for (int i = startIndex; i <= lastIndex; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow drNew = dt.NewRow();
                if (row != null)
                {
                    for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)
                    {
                        if (row.GetCell(j) != null)
                        {
                            ICell cell = row.GetCell(j);
                            switch (cell.CellType)
                            {
                                case CellType.Blank:
                                    drNew[j] = "";
                                    break;
                                case CellType.Numeric:
                                    short format = cell.CellStyle.DataFormat;
                                    //对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理
                                    if (format == 14 || format == 31 || format == 57 || format == 58)
                                        drNew[j] = cell.DateCellValue;
                                    else
                                        drNew[j] = cell.NumericCellValue;
                                    if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
                                        drNew[j] = cell.NumericCellValue.ToString("#0.00");
                                    break;
                                case CellType.String:
                                    drNew[j] = cell.StringCellValue;
                                    break;
                                case CellType.Formula:
                                    try
                                    {
                                        drNew[j] = cell.NumericCellValue;
                                        if (cell.CellStyle.DataFormat == 177 || cell.CellStyle.DataFormat == 178 || cell.CellStyle.DataFormat == 188)
                                            drNew[j] = cell.NumericCellValue.ToString("#0.00");
                                    }
                                    catch
                                    {
                                        try
                                        {
                                            drNew[j] = cell.StringCellValue;
                                        }
                                        catch { }
                                    }
                                    break;
                                default:
                                    drNew[j] = cell.StringCellValue;
                                    break;
                            }
                        }
                    }
                }
                dt.Rows.Add(drNew);
            }
            return dt;
        }

        /// <summary>
        /// 默认第一行为表头，获取sheet表对应的DataTable
        /// </summary>
        /// <param name="sheet">Excel工作表</param>
        /// <param name="strMsg"></param>
        /// <returns></returns>
        private static DataTable GetFirstSheetDataTable(ISheet sheet, out string strMsg)
        {
            strMsg = string.Empty;
            DataTable dt = new DataTable();
            FileStream file = null;
            try
            {

                //ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                //得到Excel工作表的行 
                IRow headerRow = sheet.GetRow(0);
                //得到Excel工作表的总列数  
                int cellCount = headerRow.LastCellNum;

                for (int j = 0; j < cellCount; j++)
                {
                    //得到Excel工作表指定行的单元格  
                    ICell cell = headerRow.GetCell(j);
                    if (cell != null)
                    {
                        dt.Columns.Add(cell.ToString().Trim());
                    }
                    else
                    {
                        dt.Columns.Add(j.ToString());
                    }
                }

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = dt.NewRow();

                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.PhysicalNumberOfCells != 0 && row.GetCell(j) != null)
                        {
                            if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j)))
                            {
                                dataRow[j] = row.GetCell(j).DateCellValue;
                            }
                            else
                            {
                                dataRow[j] = row.GetCell(j).ToString();
                            }
                        }
                    }
                    dt.Rows.Add(dataRow);
                }
                return dt;

            }
            catch (Exception ex)
            {
                strMsg = ex.Message;
                if (file != null)
                {
                    file.Close();//关闭当前流并释放资源
                }
                return null;
            }
        }

        /// <summary>读取excel 到datatable    
        /// 默认第一行为表头，导入第一个工作表   
        /// </summary>      
        /// <param name="strFileName">excel文档路径</param>      
        /// <returns></returns>      
        public static DataTable FirstTitleExcelToDataTable(string strFileName)
        {
            DataTable dt = new DataTable();
            FileStream file = null;
            IWorkbook Workbook = null;
            try
            {

                using (file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))//C#文件流读取文件
                {
                    if (strFileName.IndexOf(".xlsx") > 0)
                        //把xlsx文件中的数据写入Workbook中
                        Workbook = new XSSFWorkbook(file);

                    else if (strFileName.IndexOf(".xls") > 0)
                        //把xls文件中的数据写入Workbook中
                        Workbook = new HSSFWorkbook(file);

                    if (Workbook != null)
                    {
                        ISheet sheet = Workbook.GetSheetAt(0);//读取第一个sheet
                        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                        //得到Excel工作表的行 
                        IRow headerRow = sheet.GetRow(0);
                        //得到Excel工作表的总列数  
                        int cellCount = headerRow.LastCellNum;

                        for (int j = 0; j < cellCount; j++)
                        {
                            //得到Excel工作表指定行的单元格  
                            ICell cell = headerRow.GetCell(j);
                            dt.Columns.Add(cell.ToString());
                        }

                        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                        {
                            IRow row = sheet.GetRow(i);
                            DataRow dataRow = dt.NewRow();

                            for (int j = row.FirstCellNum; j < cellCount; j++)
                            {
                                if (row.GetCell(j) != null)
                                    dataRow[j] = row.GetCell(j).ToString();
                            }
                            dt.Rows.Add(dataRow);
                        }
                    }
                    return dt;
                }
            }
            catch (Exception)
            {
                if (file != null)
                {
                    file.Close();//关闭当前流并释放资源
                }
                return null;
            }

        }

        #region 导出
        /// <summary>
        /// DataSet导出excel，多个sheet
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="Path"></param>
        /// <returns></returns>
        public static string DataSetToExcel(DataSet ds, string Path)
        {
            var result = string.Empty;
            FileStream fs = null;
            XSSFWorkbook workbook = new XSSFWorkbook();
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);

                XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

                int rowIndex = 0;

                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 0)
                {
                    //#region 列头及样式
                    //{
                    //    XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
                    //    XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
                    //    //headStyle.Alignment = CellHorizontalAlignment.CENTER;
                    //    XSSFFont font = (XSSFFont)workbook.CreateFont();
                    //    font.FontHeightInPoints = 10;
                    //    font.Boldweight = 700;
                    //    headStyle.SetFont(font);
                    //}
                    //#endregion
                    //填充表头
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
                    foreach (DataColumn column in ds.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                    }

                    rowIndex = 1;
                }
                #endregion

                foreach (DataRow row in ds.Tables[i].Rows)
                {
                    XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

                    #region 填充内容
                    foreach (DataColumn column in ds.Tables[i].Columns)
                    {
                        XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
                        string type = row[column].GetType().FullName.ToString();
                        newCell.SetCellValue(GetValue(row[column].ToString(), type));
                    }
                    #endregion

                    rowIndex++;
                }
            }

            using (fs = File.OpenWrite(Path))
            {
                workbook.Write(fs);//向打开的这个xls文件中写入数据
                result = Path;
            }
            return result;
        }
		public static string DataSetToExcel(DataTable dt, string Path)
		{
			var result = string.Empty;
			FileStream fs = null;
			XSSFWorkbook workbook = new XSSFWorkbook();


			XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(dt.TableName);

			XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
			XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
			dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

			int rowIndex = 0;

			#region 新建表，填充表头，填充列头，样式
			if (rowIndex == 0)
			{
				//#region 列头及样式
				//{
				//    XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
				//    XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
				//    //headStyle.Alignment = CellHorizontalAlignment.CENTER;
				//    XSSFFont font = (XSSFFont)workbook.CreateFont();
				//    font.FontHeightInPoints = 10;
				//    font.Boldweight = 700;
				//    headStyle.SetFont(font);
				//}
				//#endregion
				//填充表头
				XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
				foreach (DataColumn column in dt.Columns)
				{
					dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
				}

				rowIndex = 1;
			}
			#endregion

			foreach (DataRow row in dt.Rows)
			{
				XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);

				#region 填充内容
				foreach (DataColumn column in dt.Columns)
				{
					XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
					string type = row[column].GetType().FullName.ToString();
					newCell.SetCellValue(GetValue(row[column].ToString(), type));
				}
				#endregion

				rowIndex++;
			}


			using (fs = File.OpenWrite(Path))
			{
				workbook.Write(fs);//向打开的这个xls文件中写入数据
				result = Path;
			}
			return result;
		}

		private static string GetValue(string cellValue, string type)
        {
            object value = string.Empty;
            switch (type)
            {
                case "System.String"://字符串类型
                    value = cellValue;
                    break;
                case "System.DateTime"://日期类型
                    System.DateTime dateV;
                    System.DateTime.TryParse(cellValue, out dateV);
                    value = dateV;
                    break;
                case "System.Boolean"://布尔型
                    bool boolV = false;
                    bool.TryParse(cellValue, out boolV);
                    value = boolV;
                    break;
                case "System.Int16"://整型
                case "System.Int32":
                case "System.Int64":
                case "System.Byte":
                    int intV = 0;
                    int.TryParse(cellValue, out intV);
                    value = intV;
                    break;
                case "System.Decimal"://浮点型
                case "System.Double":
                    double doubV = 0;
                    double.TryParse(cellValue, out doubV);
                    value = doubV;
                    break;
                case "System.DBNull"://空值处理
                    value = string.Empty;
                    break;
                default:
                    value = string.Empty;
                    break;
            }
            return value.ToString();
        }
        #endregion
    }
}