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 ConsoleApp2.test1
{
public class Class6
{
public void test1()
{
//DataSet ds = ExcelToDataSet(true, "1.xlsx");
//DataSet ds2 = ExcelToDataSet(false, "1.xlsx");

        //Console.WriteLine(ds.Tables.Count);  
        //Console.WriteLine(ds.Tables\[0\].Rows.Count);

        Console.WriteLine(GetExcelColumnName());  
    }

    /// <summary>  
    /// Excel转换为DataSet  
    /// </summary>  
    /// <param name="isFirstRowColumn"></param>  
    /// <param name="fileName"></param>  
    /// <returns></returns>  
    public DataSet ExcelToDataSet(bool isFirstRowColumn, string fileName)  
    {  
        bool IsSupportFormula = true;//是否支持Excel公式  
        IWorkbook workbook = null;  
        DataSet ds = new DataSet();  
        var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);

        #region 初始化Excel

        if (fileName.IndexOf(".xlsx") > )  
        {  
            workbook = new XSSFWorkbook(fs);  
        }  
        else if (fileName.IndexOf(".xls") > )  
        {  
            workbook = new HSSFWorkbook(fs);  
        }  
        else  
        {  
            throw new Exception("格式错误！");  
        }  
        #endregion

        for (int sheetIndex = ; sheetIndex < workbook.NumberOfSheets; sheetIndex++)  
        {  
            DataTable myTable = new DataTable();  
            ISheet sheet = null;  
            int sheetNum = sheetIndex;  
            sheet = workbook.GetSheetAt(sheetNum);

            #region 工作表不能为空

            if (sheet == null)  
            {  
                string str = "";  
                for (int i = ; i < workbook.NumberOfSheets; i++)  
                {  
                    str += workbook.GetSheetAt(i).SheetName + ",";  
                }  
                str = workbook.NumberOfSheets + str;  
                throw new Exception($"sheet不能为空！参数：{sheetNum} 工作簿信息：{str}");  
            }  
            #endregion

            #region Excel最大列数

            int MaxColumnNum = ;  
            for (int i = ; i < sheet.LastRowNum; i++)  
            {  
                var row = sheet.GetRow(i);  
                if (row == null)  
                {  
                    continue;  
                }  
                if (row.LastCellNum > MaxColumnNum)  
                {  
                    MaxColumnNum = row.LastCellNum;  
                }  
            }  
            #endregion

            //Excel行数  
            int MaxRowNum = sheet.LastRowNum;

            #region table新增列

            for (int i = ; i < MaxColumnNum; ++i)  
            {  
                //首行为列  
                if (isFirstRowColumn)  
                {  
                    bool addEmptyCell = true;//是否添加空列  
                    ICell cell = sheet.GetRow().GetCell(i);  
                    if (cell != null)  
                    {  
                        //table列赋值  
                        string cellValue = "";//列名  
                        if (cell.CellType == CellType.Numeric)  
                        {  
                            cellValue = cell.NumericCellValue.ToString();  
                        }  
                        else  
                        {  
                            cellValue = cell.StringCellValue;  
                        }  
                        if (!string.IsNullOrWhiteSpace(cellValue))  
                        {  
                            //列数据为Excel的数据  
                            addEmptyCell = false;

                            if (myTable.Columns.Contains(cellValue))  
                            {  
                                throw new Exception($"已包含列：{cellValue}，工作表：{sheet.SheetName}");  
                            }  
                            myTable.Columns.Add(new DataColumn(cellValue));  
                        }  
                    }  
                    if (addEmptyCell)  
                    {  
                        myTable.Columns.Add(new DataColumn(""));//列数据为空  
                    }  
                }  
                else  
                {  
                    myTable.Columns.Add(new DataColumn(i + ""));  
                }  
            }  
            #endregion

            //起始行  
            int startRow = ;  
            if (isFirstRowColumn)  
            {  
                startRow = ;  
            }

            #region DataTable赋值

            for (int i = startRow; i <= MaxRowNum; ++i)  
            {  
                IRow row = sheet.GetRow(i);  
                if (row == null) continue;

                DataRow NewRow = myTable.NewRow();  
                for (int j = row.FirstCellNum; j < row.LastCellNum; ++j)  
                {  
                    ICell cell = row.GetCell(j);  
                    string value = "";  
                    if (cell != null)  
                    {  
                        //table行赋值  
                        if (cell.CellType == CellType.Numeric)  
                        {  
                            value = cell.NumericCellValue.ToString();  
                            //if ((j == 0) && ((i == 6) || (i == 12)))  
                            //{  
                            //    //特殊的几个单元格 转换为 日期格式  
                            //    value = ToDateTimeValue(cell.NumericCellValue.ToString());  
                            //}

                        }  
                        else if (cell.CellType == CellType.Formula)  
                        {  
                            if (IsSupportFormula)  
                            {  
                                try  
                                {  
                                    #region 公式计算

                                    if (fileName.IndexOf(".xlsx") > )  
                                    {  
                                        XSSFFormulaEvaluator e = new XSSFFormulaEvaluator(cell.Sheet.Workbook);  
                                        e.EvaluateInCell(cell);  
                                        value = cell.ToString();  
                                    }  
                                    else if (fileName.IndexOf(".xls") > )  
                                    {  
                                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);  
                                        e.EvaluateInCell(cell);  
                                        value = cell.ToString();  
                                    }  
                                    #endregion  
                                }  
                                catch  
                                {  
                                    //日期  
                                    if (DateUtil.IsCellDateFormatted(cell))  
                                    {  
                                        value = cell.DateCellValue.ToString("yyyy-MM-dd");  
                                    }  
                                    else  
                                    {  
                                        value = cell.NumericCellValue.ToString();  
                                    }  
                                }  
                            }  
                            else  
                            {  
                                throw new Exception($"Excel含有公式  fileName:{fileName} sheetIndex:{sheetIndex + 1}  rowIndex:{i + 1}  colIndex:{j + 1}\\r\\n");  
                            }

                        }  
                        else  
                        {  
                            //row.GetCell(j).SetCellType(CellType.String);  
                            value = cell.StringCellValue;  
                        }  
                    }  
                    NewRow\[j\] = value;  
                }  
                myTable.Rows.Add(NewRow);  
            }  
            #endregion

            ds.Tables.Add(myTable);  
        }  
        return ds;  
    }

    /// <summary>  
    /// 数字格式的时间 转换为 字符串格式的时间  
    /// 数字格式的时间 如： 42095.7069444444/0.650694444444444  
    /// </summary>  
    /// <param name="timeStr">数字,如:42095.7069444444/0.650694444444444</param>  
    /// <returns>日期/时间格式</returns>  
    public string ToDateTimeValue(string strNumber)  
    {  
        if (!string.IsNullOrWhiteSpace(strNumber))  
        {  
            Decimal tempValue;  
            //先检查 是不是数字;  
            if (Decimal.TryParse(strNumber, out tempValue))  
            {  
                //天数,取整  
                int day = Convert.ToInt32(Math.Truncate(tempValue));  
                //这里也不知道为什么. 如果是小于32,则减1,否则减2  
                //日期从1900-01-01开始累加  
                // day = day < 32 ? day - 1 : day - 2;  
                DateTime dt = new DateTime(, , ).AddDays(day <  ? (day - ) : (day - ));

                //小时:减掉天数,这个数字转换小时:(\* 24)  
                Decimal hourTemp = (tempValue - day) \* ;//获取小时数  
                                                          //取整.小时数  
                int hour = Convert.ToInt32(Math.Truncate(hourTemp));  
                //分钟:减掉小时,( \* 60)  
                //这里舍入,否则取值会有1分钟误差.  
                Decimal minuteTemp = Math.Round((hourTemp - hour) \* , );//获取分钟数  
                int minute = Convert.ToInt32(Math.Truncate(minuteTemp));  
                //秒:减掉分钟,( \* 60)  
                //这里舍入,否则取值会有1秒误差.  
                Decimal secondTemp = Math.Round((minuteTemp - minute) \* , );//获取秒数  
                int second = Convert.ToInt32(Math.Truncate(secondTemp));

                //时间格式:00:00:00  
                string resultTimes = string.Format("{0}:{1}:{2}",  
                        (hour <  ? ("" + hour) : hour.ToString()),  
                        (minute <  ? ("" + minute) : minute.ToString()),  
                        (second <  ? ("" + second) : second.ToString()));

                if (day > )  
                    return string.Format("{0} {1}", dt.ToString("yyyy-MM-dd"), resultTimes);  
                else  
                    return resultTimes;  
            }  
        }  
        return string.Empty;  
    }

    /// <summary>  
    /// 获取Excel的列名  
    /// 这个还是有很多bug 先将就用吧  
    /// </summary>  
    /// <param name="index"></param>  
    /// <returns></returns>  
    public string GetExcelColumnName(int index)  
    {  
        var arr = new List<string>() { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };  
        string name = "";

        if (index <= arr.Count)  
        {  
            for (int i = ; i < arr.Count; i++)  
            {  
                if ((i + ) == index)  
                {  
                    name = arr\[i\];  
                }  
            }  
        }  
        else  
        {  
            int num = ;

            num = index / arr.Count;  
            for (int i = ; i < num; i++)  
            {  
                name += "A";  
            }

            num = index % arr.Count;  
            name += arr\[num - \];  
        }

        if ((name.Length >= ))  
        {  
            //&& (name.Length - 2 <= arr.Count - 1)  
            name = arr\[name.Length - \] + name.Substring(name.Length - , );  
        }  
        return name;  
    }

    /// <summary>  
    /// DataTable 转换为 Html，点击单元格 输出 单元格所在的 行和列  
    /// </summary>  
    /// <param name="dt"></param>  
    /// <returns></returns>  
    public string GetHtmlString(DataTable dt)  
    {  
        StringBuilder sb = new StringBuilder();  
        sb.Append("<html><head>");  
        sb.Append("<title>Excel转换为Table</title>");  
        sb.Append("<meta http-equiv='content-type' content='text/html; charset=GB2312'> ");  
        sb.Append("<style type=text/css>");  
        sb.Append("td{font-size: 9pt;border:solid 1 #000000;}");  
        sb.Append("table{padding:3 0 3 0;border:solid 1 #000000;margin:0 0 0 0;BORDER-COLLAPSE: collapse;}");  
        sb.Append("</style>");  
        sb.Append("</head>");  
        sb.Append("<body>");  
        sb.Append("<table cellSpacing='0' cellPadding='0' width ='100%' border='1'>");  
        sb.Append("<tr valign='middle'>");  
        sb.Append("<td><b></b></td>");  
        foreach (DataColumn column in dt.Columns)  
        {  
            sb.Append("<td><b><span>" + column.ColumnName + "</span></b></td>");  
        }  
        sb.Append("</tr>");  
        int iColsCount = dt.Columns.Count;  
        int rowsCount = dt.Rows.Count - ;  
        for (int j = ; j <= rowsCount; j++)  
        {  
            sb.Append("<tr>");  
            sb.Append("<td>" + ((int)(j + )).ToString() + "</td>");  
            for (int k = ; k <= iColsCount - ; k++)  
            {  
                sb.Append("<td>");  
                object obj = dt.Rows\[j\]\[k\];  
                if (obj == DBNull.Value)  
                {  
                    obj = "&nbsp;";//如果是NULL则在HTML里面使用一个空格替换之  
                }  
                if (obj.ToString() == "")  
                {  
                    obj = "&nbsp;";  
                }  
                string strCellContent = obj.ToString().Trim();  
                sb.Append("<span>" + strCellContent + "</span>");  
                sb.Append("</td>");  
            }  
            sb.Append("</tr>");  
        }  
        sb.Append("</table>");

        //点击单元格 输出 行和列  
        sb.Append("<script src='https://cdn.bootcss.com/jquery/1.12.4/jquery.min.js'></script>");  
        sb.Append("<script type='text/javascript'>");  
        sb.Append("$('table tbody').on('click', 'td', function (e) {");  
        sb.Append("var row = $(this).parent().prevAll().length-1 ;");  
        sb.Append("var column = $(this).prevAll().length-1 ;");  
        sb.Append("var str = 'dt.Rows\[' + row + '\]\[' + column + '\].ToString()';");  
        sb.Append("console.log(str);alert(str);");  
        sb.Append("});");  
        sb.Append("</script>");

        sb.Append("</body></html>");  
        return sb.ToString();  
    }

}  

}