//-------------------------------------------------------------------------------------
// All Rights Reserved , Copyright (C) 2013 , DZD , Ltd .
//-------------------------------------------------------------------------------------

using System;
using System.Data;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;
using System.Text;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.Util;

namespace HH.ExcelHelper
{
    /// <summary>
    /// ExcelHelp
    /// <![CDATA[2016-10-17]]>
    /// <creator>FJ</creator>
    /// <remarks>ÓÃÓÚµ¼ÈëºÍµ¼³öExcel£¬Ö§³Öxls,xlsx¸ñʽµÄExcelÎļþ</remarks>
    /// </summary>
    public class NpoiHelper
    {
        #region DataTableµ¼³öµ½Excel
        /// <summary>
        /// DataTableµ¼³öµ½Excel
        /// </summary>
        /// <param name="dtSource">DataTableÊý¾ÝÔ´</param>
        /// <param name="strHeaderText">µÚÒ»ÐбíÍ·</param>
        /// <param name="SheetName">SheetIndexÒ³Ãû³Æ£¬Ä¬ÈÏΪ"sheet1"</param>
        /// <param name="isShowHeader">ÊÇ·ñĬÈÏÏÔʾµÚÒ»ÐеıíÍ·ÐÅÏ¢  ĬÈÏ£ºÏÔʾ</param>
        /// <returns>MemoryStream</returns>
        public static void DataTableToExcel(ref MemoryStream ms, DataTable dtSource, string strHeaderText, string SheetName = "sheet1", bool isShowHeader = true)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(SheetName);

            #region ÓÒ»÷Îļþ ÊôÐÔÐÅÏ¢
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "http://www.yongfa365.com/";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author = "ºººÍÖÇÄÜ"; //Ìî¼ÓxlsÎļþ×÷ÕßÐÅÏ¢   
                si.ApplicationName = "MESϵͳÊý¾Ýµ¼³ö"; //Ìî¼ÓxlsÎļþ´´½¨³ÌÐòÐÅÏ¢   
                si.LastAuthor = "ºÏ·ÊºººÍÖÇÄÜÎïÁ÷¿Æ¼¼ÓÐÏÞ¹«Ë¾"; //Ìî¼ÓxlsÎļþ×îºó±£´æÕßÐÅÏ¢   
                si.Comments = "MESÏîÄ¿×é"; //Ìî¼ÓxlsÎļþ×÷ÕßÐÅÏ¢   
                si.Title = "MES"; //Ìî¼ÓxlsÎļþ±êÌâÐÅÏ¢   
                si.Subject = "ºÏ·ÊºººÍÖÇÄÜÎïÁ÷¿Æ¼¼ÓÐÏÞ¹«Ë¾";//Ìî¼ÓÎļþÖ÷ÌâÐÅÏ¢   
                si.CreateDateTime = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");

            //È¡µÃÁÐ¿í   
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                int len = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length + 2;
                arrColWidth[item.Ordinal] = len;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }

            int rowIndex = 0;
            //´´½¨Ñùʽ
            ICellStyle rowHeadStyle = workbook.CreateCellStyle();

            foreach (DataRow row in dtSource.Rows)
            {
                #region н¨±í£¬Ìî³ä±íÍ·£¬Ìî³äÁÐÍ·£¬Ñùʽ
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region ±íÍ·¼°Ñùʽ
                    {
                        if (isShowHeader)
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;//ÉèÖø߶È

                            headerRow.CreateCell(0).SetCellValue(strHeaderText);

                            ICellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.FillBackgroundColor = 5;
                            headStyle.Alignment = HorizontalAlignment.Center; //ÉèÖþÓÖÐ

                            headStyle.FillPattern = FillPattern.SolidForeground;// FillPatternType.SOLID_FOREGROUND;//ÉèÖñ³¾°ÑÕÉ«

                            headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;

                            IFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);

                            headerRow.GetCell(0).CellStyle = headStyle;

                            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        }
                    }
                    #endregion

                    #region ÁÐÍ·¼°Ñùʽ
                    {
                        int indexLine = 0;
                        if (isShowHeader)
                        {
                            indexLine = 1;
                        }
                        IRow headerRow = sheet.CreateRow(indexLine);
                        headerRow.HeightInPoints = 22;//

                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index;

                        headStyle.FillPattern = FillPattern.SolidForeground;// FillPatternType.SOLID_FOREGROUND; ;//ÉèÖñ³¾°ÑÕÉ«
                        headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.SkyBlue.Index;

                        headStyle.Alignment = HorizontalAlignment.Center;//ÉèÖÃÁÐÍ·¾ÓÖÐ
                        headStyle.VerticalAlignment = VerticalAlignment.Center; //ÉèÖÃÉÏϾÓÖÐ
                        headStyle.BorderBottom = BorderStyle.Thin;//ÉèÖÃϱ߿ò
                        headStyle.BorderLeft = BorderStyle.Thin; //ÉèÖÃ×ó±ß¿ò
                        headStyle.BorderRight = BorderStyle.Thin;//ÉèÖÃÓұ߿ò
                        headStyle.BorderTop = BorderStyle.Thin; //ÉèÖÃÉϱ߿ò

                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;//ÉèÖÃ×ÖÌå¼ÓºÚ
                        headStyle.SetFont(font);

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //ÉèÖÃÁÐ¿í   
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        //headerRow.Dispose();
                    }
                    #endregion
                    if (isShowHeader)
                    {
                        rowIndex = 2;
                    }
                    else
                    {
                        rowIndex = 1;
                    }
                }
                #endregion

                #region Ìî³äÄÚÈÝ
                IRow dataRow = sheet.CreateRow(rowIndex);
                dataRow.HeightInPoints = 18;//
                //¶¨Òåµ¥Ôª¸ñÑùʽ
                rowHeadStyle.VerticalAlignment = VerticalAlignment.Center;//ÉèÖÃÉÏϾÓÖÐ

                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);

                    //ÉèÖõ¥Ôª¸ñÑùʽ
                    newCell.CellStyle = rowHeadStyle;

                    string drValue = row[column].ToString();

                    switch (column.DataType.ToString())
                    {
                        case "System.String"://×Ö·û´®ÀàÐÍ   
                            newCell.SetCellValue(drValue);
                            break;
                        case "System.DateTime"://ÈÕÆÚÀàÐÍ   
                            DateTime dateV;
                            if (!string.IsNullOrEmpty(drValue))
                            {
                                DateTime.TryParse(drValue, out dateV);
                                newCell.SetCellValue(dateV);
                            }
                            else
                            {
                                newCell.SetCellValue("");
                            }
                            newCell.CellStyle = dateStyle;//¸ñʽ»¯ÏÔʾ   
                            break;
                        case "System.Boolean"://²¼¶ûÐÍ   
                            bool boolV = false;
                            bool.TryParse(drValue, out boolV);
                            newCell.SetCellValue(boolV);
                            break;
                        case "System.Int16"://ÕûÐÍ   
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            int intV = 0;
                            int.TryParse(drValue, out intV);
                            newCell.SetCellValue(intV);
                            break;
                        case "System.Decimal"://¸¡µãÐÍ   
                        case "System.Double":
                            double doubV = 0;
                            double.TryParse(drValue, out doubV);
                            newCell.SetCellValue(doubV);
                            break;
                        case "System.DBNull"://¿ÕÖµ´¦Àí   
                            newCell.SetCellValue("");
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }

                }
                #endregion

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Seek(0, SeekOrigin.Begin);
            // sheet.Dispose();
            //workbook.Dispose();//Ò»°ãÖ»ÓÃдÕâÒ»¸ö¾ÍOKÁË£¬Ëû»á±éÀú²¢ÊÍ·ÅËùÓÐ×ÊÔ´£¬µ«µ±Ç°°æ±¾ÓÐÎÊÌâËùÒÔÖ»ÊÍ·Åsheet          
        }
        #endregion

        #region ExcelÊý¾Ýµ¼ÈëDataTable£¬Ö§³ÖËùÓÐsheetÒ³
        /// <summary>
        /// ExcelÊý¾Ýµ¼ÈëDataTable£¬Ö§³ÖËùÓÐsheetÒ³
        /// </summary>
        /// <param name="FilePath"></param>
        /// <returns></returns>
        public static DataTable ExcelToDataTableForSheet(string FilePath)
        {
            //±£´æÊý¾ÝµÄdatatable
            DataTable dt = new DataTable();

            HSSFWorkbook hssfworkbook = null;
            XSSFWorkbook xssfworkbook = null;
            string fileExt = Path.GetExtension(FilePath);//»ñÈ¡ÎļþµÄºó׺Ãû
            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xls")
                    hssfworkbook = new HSSFWorkbook(file);
                else if (fileExt == ".xlsx")
                    xssfworkbook = new XSSFWorkbook(file);//³õʼ»¯Ì«ÂýÁË£¬²»ÖªµÀÕâÊÇʲôbug
            }
            if (hssfworkbook != null)
            {
                //»ñÈ¡ËùÓÐSheetName
                int sheetCount = hssfworkbook.NumberOfSheets;
                for (int index = 0; index < sheetCount; index++)
                {
                    HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(index);
                    if (sheet != null)
                    {
                        #region
                        //»ñµÃDataTableÁÐÍ·
                        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                        int cellCount = headerRow.LastCellNum;
                        for (int j = 0; j < cellCount; j++)
                        {
                            HSSFCell cell = (HSSFCell)headerRow.GetCell(j);
                            if (!dt.Columns.Contains(cell.ToString()))
                            {
                                dt.Columns.Add(cell.ToString());
                            }
                        }
                        //sheetÒ³Ë÷Òý
                        if (!dt.Columns.Contains("SheetIndex"))
                        {
                            dt.Columns.Add("SheetIndex", typeof(string));
                        }
                        //sheetÒ³Ãû³Æ
                        if (!dt.Columns.Contains("SheetName"))
                        {
                            dt.Columns.Add("SheetName", typeof(string));
                        }
                        //»ñµÃÊý¾Ý
                        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                        {
                            HSSFRow row = (HSSFRow)sheet.GetRow(i);
                            if (row == null)
                            {
                                continue;
                            }
                            DataRow dataRow = dt.NewRow();
                            if (dataRow == null)
                            {
                                continue;
                            }

                            dataRow["SheetIndex"] = index;
                            dataRow["SheetName"] = xssfworkbook.GetSheetName(index);

                            for (int j = 0; j < cellCount; j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    //dataRow[j] = "";
                                }
                                else if (cell.CellType == CellType.Numeric)
                                {
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//ÆäËûÊý×ÖÀàÐÍ
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                }
                                else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
                                {
                                    dataRow[j] = "";
                                }
                                else if (cell.CellType == CellType.Boolean)
                                {
                                    dataRow[j] = cell.BooleanCellValue;
                                }
                                else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
                                {
                                    dataRow[j] = cell.StringCellValue;
                                }
                            }
                            dt.Rows.Add(dataRow);
                        }
                        #endregion
                    }
                }
            }
            else if (xssfworkbook != null)
            {
                //»ñÈ¡ËùÓÐSheetName
                int sheetCount = xssfworkbook.NumberOfSheets;
                for (int index = 0; index < sheetCount; index++)
                {
                    XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(index);
                    if (xSheet != null)
                    {
                        #region
                        //ÐÂÔöDataTableÁÐÍ·
                        XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                        int cellCount = headerRow.LastCellNum;
                        for (int j = 0; j < cellCount; j++)
                        {
                            XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                            if (!dt.Columns.Contains(cell.ToString()))
                            {
                                dt.Columns.Add(cell.ToString());
                            }
                        }
                        //sheetÒ³Ë÷Òý
                        if (!dt.Columns.Contains("SheetIndex"))
                        {
                            dt.Columns.Add("SheetIndex", typeof(string));
                        }
                        //sheetÒ³Ãû³Æ
                        if (!dt.Columns.Contains("SheetName"))
                        {
                            dt.Columns.Add("SheetName", typeof(string));
                        }

                        //»ñµÃÊý¾Ý
                        for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                        {
                            XSSFRow row = (XSSFRow)xSheet.GetRow(i);
                            if (row == null)
                            {
                                continue;
                            }
                            DataRow dataRow = dt.NewRow();
                            if (dataRow == null)
                            {
                                continue;
                            }

                            //±£´æSheetIndexλÖÃ
                            dataRow["SheetIndex"] = index;
                            dataRow["SheetName"] = xssfworkbook.GetSheetName(index);

                            for (int j = 0; j < cellCount; j++)
                            {
                                ICell cell = row.GetCell(j);
                                if (cell == null)
                                {
                                    continue;
                                }
                                else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
                                {
                                    dataRow[j] = "";
                                }
                                else if (cell.CellType == CellType.Numeric)
                                {
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
                                    {
                                        dataRow[j] = cell.DateCellValue;
                                    }
                                    else//ÆäËûÊý×ÖÀàÐÍ
                                    {
                                        dataRow[j] = cell.NumericCellValue;
                                    }
                                }

                                else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
                                {
                                    dataRow[j] = cell.StringCellValue;
                                }

                                //if (row.GetCell(j) != null)
                                //    dataRow[j] = row.GetCell(j).ToString();
                            }
                            dt.Rows.Add(dataRow);
                        }
                        #endregion
                    }
                }
            }
            return dt;
        }
        #endregion

        #region µ¼ÈëExcelµ½DataTableÖ§³Öxls  xlsx
        /// <summary>
        /// µ¼ÈëExcelµ½DataTableÖ§³Öxls  xlsx
        /// </summary>
        /// <param name="FilePath">Îļþ·¾¶+ÎļþÃû³Æ</param>
        /// <param name="sheetIndex">¶ÁÈ¡sheetÒ³µÄË÷Òý</param>
        /// <returns>DataTableÊý¾Ý¼¯</returns>
        public static DataTable ExcelToDataTable(string FilePath, int sheetIndex = 0)
        {
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook = null;
            XSSFWorkbook xssfworkbook = null;
            string fileExt = Path.GetExtension(FilePath);//»ñÈ¡ÎļþµÄºó׺Ãû
            using (FileStream file = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
            {
                if (fileExt == ".xls")
                    hssfworkbook = new HSSFWorkbook(file);
                else if (fileExt == ".xlsx")
                    xssfworkbook = new XSSFWorkbook(file);//³õʼ»¯Ì«ÂýÁË£¬²»ÖªµÀÕâÊÇʲôbug
            }
            if (hssfworkbook != null)
            {
                HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex);
                if (sheet != null)
                {
                    System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                    HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        HSSFCell cell = (HSSFCell)headerRow.GetCell(j);

                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        HSSFRow row = (HSSFRow)sheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        DataRow dataRow = dt.NewRow();
                        if (dataRow == null)
                        {
                            continue;
                        }
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                //dataRow[j] = "";
                            }
                            else if (cell.CellType == CellType.Numeric)
                            {
                                if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
                                {
                                    dataRow[j] = cell.DateCellValue;
                                }
                                else//ÆäËûÊý×ÖÀàÐÍ
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                            }
                            else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
                            {
                                dataRow[j] = "";
                            }
                            else if (cell.CellType == CellType.Boolean)
                            {
                                dataRow[j] = cell.BooleanCellValue;
                            }
                            else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
                            {
                                dataRow[j] = cell.StringCellValue;
                            }
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            else if (xssfworkbook != null)
            {
                XSSFSheet xSheet = (XSSFSheet)xssfworkbook.GetSheetAt(sheetIndex);
                if (xSheet != null)
                {
                    System.Collections.IEnumerator rows = xSheet.GetRowEnumerator();
                    XSSFRow headerRow = (XSSFRow)xSheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    for (int j = 0; j < cellCount; j++)
                    {
                        XSSFCell cell = (XSSFCell)headerRow.GetCell(j);
                        dt.Columns.Add(cell.ToString());
                    }
                    for (int i = (xSheet.FirstRowNum + 1); i <= xSheet.LastRowNum; i++)
                    {
                        XSSFRow row = (XSSFRow)xSheet.GetRow(i);
                        if (row == null)
                        {
                            continue;
                        }
                        DataRow dataRow = dt.NewRow();
                        if (dataRow == null)
                        {
                            continue;
                        }
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            ICell cell = row.GetCell(j);
                            if (cell == null)
                            {
                                continue;
                            }
                            else if (cell.CellType == CellType.Blank)//¿ÕÊý¾ÝÀàÐÍ
                            {
                                dataRow[j] = "";
                            }
                            else if (cell.CellType == CellType.Numeric)
                            {
                                if (HSSFDateUtil.IsCellDateFormatted(cell))//ÈÕÆÚÀàÐÍ
                                {
                                    dataRow[j] = cell.DateCellValue;
                                }
                                else//ÆäËûÊý×ÖÀàÐÍ
                                {
                                    dataRow[j] = cell.NumericCellValue;
                                }
                            }

                            else //ÆäËûÀàÐͶ¼°´×Ö·û´®ÀàÐÍÀ´´¦Àí
                            {
                                dataRow[j] = cell.StringCellValue;
                            }

                            //if (row.GetCell(j) != null)
                            //    dataRow[j] = row.GetCell(j).ToString();
                        }
                        dt.Rows.Add(dataRow);
                    }
                }
            }
            return dt;
        }

        #endregion
    }
}