using ExcelDataReader;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CommonBase
{
    public class ExcelExport
    {
        private static void QuertExcel()
        {
            Process[] excels = Process.GetProcessesByName("EXCEL");
            foreach (var item in excels)
            {
                item.CloseMainWindow();
                //item.Kill();
            }
        }//进程查询
        //public static string XLSSavesaCSV(string FilePath)
        //{
        //    QuertExcel();
        //    string _NewFilePath = "";

        //    Microsoft.Office.Interop.Excel.Application excelApplication;
        //    Microsoft.Office.Interop.Excel.Workbooks excelWorkBooks = null;
        //    Microsoft.Office.Interop.Excel.Workbook excelWorkBook = null;
        //     Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = null;

        //    try
        //    {
        //        excelApplication = new  Microsoft.Office.Interop.Excel.ApplicationClass();
        //        excelWorkBooks = excelApplication.Workbooks;
        //        excelWorkBook = ((Microsoft.Office.Interop.Excel.Workbook)excelWorkBooks.Open(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
        //        excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
        //        excelApplication.Visible = false;
        //        excelApplication.DisplayAlerts = false;
        //        _NewFilePath = FilePath.Replace(".xls", ".csv");
        //        // excelWorkSheet._SaveAs(FilePath,  Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Missing.Value, Missing.Value, Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value);
        //        excelWorkBook.SaveAs(_NewFilePath,  Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
        //        QuertExcel();
        //        //ExcelFormatHelper.DeleteFile(FilePath);
        //    }
        //    catch (Exception exc)
        //    {
        //        throw new Exception(exc.Message);
        //    }
        //    QuertExcel();
        //    return _NewFilePath;
        //}//excel转csv
        static IWorkbook hssfworkbook;
        /// <summary>  
        /// 使用DataTable导出数据  
        /// </summary>  
        /// <param name="dt">数据</param>  
        /// <param name="FileName">文件名</param>  
        /// <param name="msg">提示消息</param> 
        public static void DtToExcel(DataTable dt, string FileName = "", string SheetName = null, bool report = true)
        {
            //try
            //{
            if (dt != null && dt.Rows.Count > 0)
            {
                SaveFileDialog dlg = new SaveFileDialog();//申明保存对话框  
                dlg.Filter = "Excel文件|*.xlsx";
                dlg.FileName = FileName;//默认文件名称  
                if (FileName == "")
                    if (dlg.ShowDialog() == DialogResult.Cancel) return;

                dlg.InitialDirectory = Directory.GetCurrentDirectory();//返回文件路径  
                string filename = dlg.FileName;//输出的文件名称  
                if (filename.Trim() == " ")//验证strFileName是否为空或值无效  
                { return; }
                hssfworkbook = new XSSFWorkbook();
                ISheet sheet;
                if (SheetName == null)
                    sheet = hssfworkbook.CreateSheet("Sheet1");
                else
                    sheet = hssfworkbook.CreateSheet(SheetName);

                int colscount = dt.Columns.Count;//定义表格内数据的列数                 
                ArrayList arr = new ArrayList();//储存头信息  


                ICellStyle cstyle_Datetime = hssfworkbook.CreateCellStyle();
                ICellStyle cstyle_Numeric = hssfworkbook.CreateCellStyle();
                ICellStyle cstyle_String = hssfworkbook.CreateCellStyle();

                IDataFormat format_Datetime = hssfworkbook.CreateDataFormat();
                IDataFormat format_Numeric = hssfworkbook.CreateDataFormat();
                IDataFormat format_String = hssfworkbook.CreateDataFormat();

                cstyle_Datetime.DataFormat = format_Datetime.GetFormat("yyyy-MM-dd HH:mm:ss");
                cstyle_Numeric.DataFormat = 0;
                cstyle_String.DataFormat = 0;
                IRow rowHeader = sheet.CreateRow(0);//创建列头数据  
                int displayColumnsCount = 0;//用于处理隐藏列头不被显示           

                XSSFFont hssffont = hssfworkbook.CreateFont() as XSSFFont;
                hssffont.FontHeightInPoints = 11;

                //设置单元格边框   
                for (int i = 0; i < dt.Columns.Count; i++)//生成表头信息  
                {
                    ICell cell = rowHeader.CreateCell(displayColumnsCount);
                    cell.SetCellValue(dt.Columns[i].ToString());
                    sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
                    displayColumnsCount++;
                    arr.Add(dt.Columns[i].ToString());
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow irow = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell;
                        if (dt.Columns[j].DataType == typeof(DateTime))
                        {
                            cell = irow.CreateCell(j, CellType.Numeric);
                            try { cell.SetCellValue(DateTime.Parse(dt.Rows[i][j].ToString())); cell.CellStyle = cstyle_Datetime; }
                            catch { cell.SetCellValue(dt.Rows[i][j].ToString()); }
                        }
                        else if (dt.Columns[j].DataType == typeof(double))
                        {
                            cell = irow.CreateCell(j, CellType.Numeric);
                            cell.CellStyle = cstyle_Numeric;
                            double d;
                            if (double.TryParse(dt.Rows[i][j].ToString(), out d))
                                cell.SetCellValue(d);
                            else
                                cell.SetCellValue(d.ToString());
                        }
                        else
                        {
                            cell = irow.CreateCell(j);
                            cell.CellStyle = cstyle_String;
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                }
                using (Stream stream = File.OpenWrite(filename))
                {
                    hssfworkbook.Write(stream);
                    if (report) MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }

            }
            else
            {
                if (report) MessageBox.Show("没有数据可供导出", "提示");
            }
            //}
            //    catch
            //    {
            //        MessageBox.Show("操作失败", "提示");
            //    }
        }//输出Excel
        public static void DsToExcel(DataSet ds, string FileName = "")
        {
            try
            {
                if (ds != null)
                {
                    #region  验证ds非空 & 保存对话框
                    SaveFileDialog dlg = new SaveFileDialog();//申明保存对话框  
                    dlg.Filter = "Excel文件|*.xls";
                    dlg.FileName = FileName;//默认文件名称  
                    if (dlg.ShowDialog() == DialogResult.Cancel)
                        return;
                    dlg.InitialDirectory = Directory.GetCurrentDirectory();//返回文件路径  
                    string filename = dlg.FileName;//输出的文件名称  
                    if (filename.Trim() == " ")//验证strFileName是否为空或值无效  
                    {
                        return;
                    }
                    #endregion

                    hssfworkbook = new HSSFWorkbook();
                    ISheet sheet;

                    for (int l = 0; l < ds.Tables.Count; l++)
                    {

                        var dt = ds.Tables[l];
                        if (dt != null && dt.Rows.Count > 0)
                        {

                            if (dt.TableName == null)
                                sheet = hssfworkbook.CreateSheet("Sheet" + l.ToString());
                            else
                                sheet = hssfworkbook.CreateSheet(dt.TableName);
                            int colscount = dt.Columns.Count;//定义表格内数据的列数                 
                            ArrayList arr = new ArrayList();//储存头信息  
                            ICellStyle cstyle = hssfworkbook.CreateCellStyle();

                            IRow rowHeader = sheet.CreateRow(0);//创建列头数据  
                            int displayColumnsCount = 0;//用于处理隐藏列头不被显示           

                            HSSFFont hssffont = hssfworkbook.CreateFont() as HSSFFont;
                            hssffont.FontHeightInPoints = 11;

                            //设置单元格边框   
                            for (int i = 0; i < dt.Columns.Count; i++)//生成表头信息  
                            {
                                ICell cell = rowHeader.CreateCell(displayColumnsCount);
                                cell.SetCellValue(dt.Columns[i].ToString());
                                sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
                                displayColumnsCount++;
                                arr.Add(dt.Columns[i].ToString());
                            }
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                IRow irow = sheet.CreateRow(i + 1);
                                for (int j = 0; j < dt.Columns.Count; j++)
                                {
                                    ICell cell = irow.CreateCell(j);
                                    cell.SetCellValue(dt.Rows[i][j].ToString());
                                }
                            }

                        }


                    }

                    using (Stream stream = File.OpenWrite(filename))
                    {
                        hssfworkbook.Write(stream);
                        MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }

                }
                else
                {
                    throw new Exception("没有数据可供导出");
                }
            }
            catch
            {
                throw new Exception("操作失败");
            }
        }//DataSet输出Excel


    }

    public class ExcelReader
    {



        public static DataTable ExcelToDt(string filePath = null, Boolean firstRowNames = true, object sheetTag = null, int RowLimit = -1)

        {
            if (filePath == null)
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel文件(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv|所有文件|*.*";
                ofd.ValidateNames = true;
                ofd.CheckPathExists = true;
                ofd.CheckFileExists = true;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    filePath = ofd.FileName;
                    //其他代码            
                }
                else
                {
                    return null;
                }
                var extension = Path.GetExtension(filePath).ToLower();

                if (filePath.Length <= 0 || !(extension == ".xls" || extension == ".xlsx" || extension == ".csv"))
                {
                    throw new Exception("文件名称错误或者格式错误");
                    //return null;
                }
                int r = 0;

            }
            if (sheetTag == null)
                sheetTag = 0;
            var ds = ExcelToDs(filePath, firstRowNames);
            if (sheetTag.GetType() == typeof(int))
                return ds.Tables[(int)sheetTag];
            else
            if (sheetTag.GetType() == typeof(string))
            {
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    if (sheetTag != null && (string)sheetTag != ds.Tables[i].TableName)
                        continue;
                    return ds.Tables[i];
                }
            }
            return null;



        }
        /// <summary>
        /// 根据Excel返回DataSet
        /// </summary>
        /// <param name="filePath">Excel文件地址</param>
        /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
        /// <returns>DataSet</returns>
        public static DataSet ExcelToDs(string filePath = null, Boolean firstRowNames = true)
        {
            DataSet ds;
            var extension = Path.GetExtension(filePath).ToLower();
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var sw = new Stopwatch();
                sw.Start();
                IExcelDataReader reader = null;
                if (extension == ".xls")
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (extension == ".xlsx")
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else if (extension == ".csv")
                {
                    reader = ExcelReaderFactory.CreateCsvReader(stream);
                }

                if (reader == null)
                    return null;

                var openTiming = sw.ElapsedMilliseconds;
                // reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
                using (reader)
                {
                    //ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    //{
                    //    UseColumnDataType = true,
                    //    ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                    //    {
                    //        UseHeaderRow = firstRowNames
                    //    }
                    //});
                    ds = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        UseColumnDataType = true,
                        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = firstRowNames
                        }
                    });
                }
            }


            return ds;

        }

        public static DataTable GetSheetInfo(string filePath = null, Boolean firstRowNames = true)
        {
            DataTable dt;
            var extension = Path.GetExtension(filePath).ToLower();
            using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
            {
                var sw = new Stopwatch();
                sw.Start();
                IExcelDataReader reader = null;
                if (extension == ".xls")
                {
                    reader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else if (extension == ".xlsx")
                {
                    reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }
                else if (extension == ".csv")
                {
                    reader = ExcelReaderFactory.CreateCsvReader(stream);
                }

                if (reader == null)
                    return null;

                var openTiming = sw.ElapsedMilliseconds;
                // reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
                using (reader)
                {

                    dt = reader.AsDataSet(new ExcelDataSetConfiguration()
                    {
                        UseColumnDataType = true,
                        ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
                        {
                            UseHeaderRow = firstRowNames
                        }
                    }).Tables[0];
                }
            }


            return dt;

        }

        #region 历史
        /// <summary>
        /// 根据Excel和Sheet返回DataTable
        /// </summary>

        /// <param name="sheetIndex">Sheet索引</param>
        /// <param name="T"> 0是HSSF，1是XSSF
        /// <returns>DataTable</returns>
        private static DataTable GetDt(int sheetIndex, IWorkbook fileWorkbook, int T)
        {
            Random rd = new Random();

            DataTable dt = new DataTable();
            ISheet sheet = fileWorkbook.GetSheetAt(sheetIndex);
            //获取数据类型
            int tryTime = 3;

            List<Type> Types = new List<Type>();
            for (int ts = 0; ts < tryTime; ts++)
            {
                IRow firstDataRow;
                if (ts == 0)
                    firstDataRow = sheet.GetRow(sheet.FirstRowNum + 1);
                else
                    firstDataRow = sheet.GetRow(rd.Next(sheet.FirstRowNum, sheet.PhysicalNumberOfRows));
                Boolean HashValue = false;
                for (int j = 0; j < firstDataRow.LastCellNum; j++)
                {
                    if (firstDataRow.GetCell(j) != null) HashValue = true;
                    object obj;
                    if (T == 0)
                        obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as HSSFCell);
                    else
                        obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as XSSFCell);

                    if (ts == 0)
                        if (obj == DBNull.Value || obj == null) Types.Add(typeof(string));
                        else
                        {
                            Types.Add(obj.GetType());

                        }

                    else
                        if (obj == DBNull.Value || obj == null || obj.ToString() == "")
                        continue;
                    else
                            if (Types[j] != obj.GetType())
                        Types[j] = typeof(string);
                }
                if (!HashValue) ts--;
            }
            //表头
            IRow header = sheet.GetRow(sheet.FirstRowNum);
            List<int> columns = new List<int>();
            for (int j = 0; j < header.LastCellNum; j++)
            {
                object obj;
                if (T == 0)
                    obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell, typeof(string));
                else
                    obj = GetValueTypeForXLS(header.GetCell(j) as XSSFCell, typeof(string));
                if (obj == null || obj.ToString() == string.Empty)
                {
                    dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
                }
                else
                    if (j < Types.Count)
                    dt.Columns.Add(new DataColumn(obj.ToString(), Types[j] == typeof(DateTime?) ? typeof(DateTime) : Types[j]));
                else
                    dt.Columns.Add(new DataColumn(obj.ToString()));


                columns.Add(j);
            }
            //数据
            IEnumerator rows = sheet.GetEnumerator();
            int RowIndex = sheet.FirstRowNum + 1;
            while (rows.MoveNext())
            {

                DataRow dr = dt.NewRow();
                bool hasValue = false;
                foreach (int K in columns)
                {
                    if (T == 0)
                        dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as HSSFCell, Types[K]);//应改写，强制按类型读取
                    else
                        dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as XSSFCell, Types[K]);

                    if (dr[K] != null && dr[K].ToString() != string.Empty)
                    {
                        hasValue = true;
                    }
                }
                if (hasValue)
                {
                    dt.Rows.Add(dr);
                }
                RowIndex++;
                if (RowIndex >= sheet.PhysicalNumberOfRows) break;
            }

            return dt;
        }
        /// <summary>
        /// 根据Excel返回DataSet
        /// </summary>
        /// <param name="filePath">Excel文件地址</param>
        /// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
        /// <returns>DataSet</returns>
        private static DataSet ExcelToDs0(string filePath, object sheetTag = null)
        {
            DataSet ds = new DataSet();
            IWorkbook fileWorkbook;
            int T = 1;
            using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                if (filePath.Last() == 's')
                {
                    try
                    {
                        fileWorkbook = new HSSFWorkbook(fs);
                        T = 0;
                    }
                    catch (Exception ex)
                    {
                        try
                        {
                            fileWorkbook = new XSSFWorkbook(fs);
                            T = 1;
                        }
                        catch
                        {
                            throw (ex);
                        }
                    }
                }
                else
                {

                    try
                    {
                        fileWorkbook = new XSSFWorkbook(fs);
                        T = 1;
                    }
                    catch
                    {
                        fileWorkbook = new HSSFWorkbook(fs);
                        T = 0;
                    }
                }
            }

            if (sheetTag != null)//单个sheet
                for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
                {

                    if (sheetTag.GetType() == typeof(int))
                    {
                        if (sheetTag != null && (int)sheetTag != i)
                            continue;
                        var dt = GetDt(i, fileWorkbook, T);
                        ds.Tables.Add(dt);
                    }
                    else
                    if (sheetTag.GetType() == typeof(string))
                    {
                        if (sheetTag != null && (string)sheetTag != fileWorkbook.GetSheetAt(i).SheetName)
                            continue;
                        var dt = GetDt(i, fileWorkbook, T);
                        ds.Tables.Add(dt);
                    }


                }
            else//多个sheet
            {
                for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
                {
                    var dt = GetDt(i, fileWorkbook, T);
                    ds.Tables.Add(dt);
                }
            }

            return ds;
        }


        /// <summary>
        /// 根据Excel和Sheet返回DataTable
        /// </summary>
        /// <param name="filePath">Excel文件地址</param>
        /// <param name="sheetName">Sheet名称</param>
        /// <returns>DataTable</returns>
        private static DataTable ExcelToDt0(string filePath = null, Boolean firstRowNames = true, object sheetTag = null)

        {
            if (filePath == null)
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
                ofd.ValidateNames = true;
                ofd.CheckPathExists = true;
                ofd.CheckFileExists = true;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    filePath = ofd.FileName;
                    //其他代码            


                }
                else
                {
                    return null;
                }
                var strFileNme = filePath;
                //strFileNme.LastIndexOf(".");

                if (strFileNme.Length <= 0 || !(strFileNme.Substring(strFileNme.LastIndexOf(".")).ToLower() == ".xls" || strFileNme.Substring(strFileNme.LastIndexOf(".")).ToLower() == ".xlsx"))
                {
                    throw new Exception("文件名称错误或者格式错误");
                    //return null;
                }
                int r = 0;

            }
            if (sheetTag == null)
                sheetTag = 0;
            return ExcelToDs(filePath, firstRowNames).Tables[0];
        }

        #endregion 历史
        #region nullable GetDt

        ///// <summary>
        ///// 根据Excel和Sheet返回DataTable
        ///// </summary>

        ///// <param name="sheetIndex">Sheet索引</param>
        ///// <param name="T"> 0是HSSF，1是XSSF
        ///// <returns>DataTable</returns>
        //private static DataTable GetDt(int sheetIndex, IWorkbook fileWorkbook, int T)
        //{
        //    Random rd = new Random();

        //    DataTable dt = new DataTable();
        //    ISheet sheet = fileWorkbook.GetSheetAt(sheetIndex);
        //    //获取数据类型
        //    int tryTime = 3;

        //    List<Type> Types = new List<Type>();
        //    for (int ts = 0; ts < tryTime; ts++)
        //    {
        //        IRow firstDataRow;
        //        if (ts == 0)
        //            firstDataRow = sheet.GetRow(sheet.FirstRowNum + 1);
        //        else
        //            firstDataRow = sheet.GetRow(rd.Next(sheet.FirstRowNum, sheet.PhysicalNumberOfRows));
        //        Boolean HashValue = false;
        //        for (int j = 0; j < firstDataRow.LastCellNum; j++)
        //        {
        //            if (firstDataRow.GetCell(j) != null) HashValue = true;
        //            object obj;
        //            if (T == 0)
        //                obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as HSSFCell);
        //            else
        //                obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as XSSFCell);

        //            if (ts == 0)
        //                if (obj == DBNull.Value || obj == null) Types.Add(typeof(string));
        //                else
        //                {
        //                    Types.Add(GetNullType(obj));

        //                }

        //            else
        //                if (obj == DBNull.Value || obj == null || obj.ToString() == "")
        //                continue;
        //            else
        //                    if (Types[j] != GetNullType(obj))
        //                Types[j] = typeof(string);
        //        }
        //        if (!HashValue) ts--;
        //    }
        //    //表头
        //    IRow header = sheet.GetRow(sheet.FirstRowNum);
        //    List<int> columns = new List<int>();
        //    for (int j = 0; j < header.LastCellNum; j++)
        //    {
        //        object obj;
        //        if (T == 0)
        //            obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell, typeof(string));
        //        else
        //            obj = GetValueTypeForXLS(header.GetCell(j) as XSSFCell, typeof(string));
        //        if (obj == null || obj.ToString() == string.Empty)
        //        {
        //            dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
        //        }
        //        else
        //            if (j < Types.Count)
        //            dt.Columns.Add(new DataColumn(obj.ToString(), Types[j] == typeof(DateTime?) ? typeof(DateTime) : Types[j]));
        //        else
        //            dt.Columns.Add(new DataColumn(obj.ToString()));


        //        columns.Add(j);
        //    }
        //    //数据
        //    IEnumerator rows = sheet.GetEnumerator();
        //    int RowIndex = sheet.FirstRowNum + 1;
        //    while (rows.MoveNext())
        //    {

        //        DataRow dr = dt.NewRow();
        //        bool hasValue = false;
        //        foreach (int K in columns)
        //        {
        //            if (T == 0)
        //                dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as HSSFCell, Types[K]);//应改写，强制按类型读取
        //            else
        //                dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as XSSFCell, Types[K]);

        //            if (dr[K] != null && dr[K].ToString() != string.Empty)
        //            {
        //                hasValue = true;
        //            }
        //        }
        //        if (hasValue)
        //        {
        //            dt.Rows.Add(dr);
        //        }
        //        RowIndex++;
        //        if (RowIndex >= sheet.PhysicalNumberOfRows) break;
        //    }

        //    return dt;
        //}
        #endregion

        /// <summary>
        /// 根据DataTable导出Excel
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="file">保存地址</param>
        public static void GetExcelByDataTable(DataTable dt, string file)
        {
            DataSet ds = new DataSet();
            ds.Tables.Add(dt);
            GetExcelByDataSet(ds, file);
        }

        /// <summary>
        /// 根据DataSet导出Excel
        /// </summary>
        /// <param name="ds">DataSet</param>
        /// <param name="file">保存地址</param>
        public static void GetExcelByDataSet(DataSet ds, string file)
        {
            IWorkbook fileWorkbook = new HSSFWorkbook();
            int index = 0;
            foreach (DataTable dt in ds.Tables)
            {
                index++;
                ISheet sheet = fileWorkbook.CreateSheet("Sheet" + index);

                //表头
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ColumnName);
                }

                //数据
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row1 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        ICell cell = row1.CreateCell(j);
                        cell.SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
            }

            //转为字节数组
            MemoryStream stream = new MemoryStream();
            fileWorkbook.Write(stream);
            var buf = stream.ToArray();

            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }
        }

        /// <summary>
        /// 根据单元格将内容返回为对应类型的数据
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>数据</returns>
        private static object GetValueTypeForXLS(HSSFCell cell, Type type = null)
        {

            DateTime t = DateTime.FromOADate(0);
            if (cell == null)
                return DBNull.Value;
            if (type != null)
            {
                if (type == typeof(bool?))
                    return (Boolean?)cell.BooleanCellValue;
                if (type == typeof(string))
                    return cell.ToString();
                if (type == typeof(DateTime?))
                    return TimeExTreat(cell.DateCellValue);
                if (type == typeof(double?))
                    return (double?)cell.NumericCellValue;
            }
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return DBNull.Value;
                case CellType.Boolean: //BOOLEAN:
                    return (Boolean?)cell.BooleanCellValue;
                case CellType.String: //STRING:
                    if (DateTime.TryParse(cell.StringCellValue, out t))
                        return t;
                    else
                        return cell.StringCellValue;
                case CellType.Numeric: //NUMERIC:
                    if (DateUtil.IsCellDateFormatted(cell))
                        return TimeExTreat(cell.DateCellValue);
                    else
                        return (double?)cell.NumericCellValue;

                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        /// <summary>
        /// 根据单元格将内容返回为对应类型的数据
        /// </summary>
        /// <param name="cell">单元格</param>
        /// <returns>数据</returns>
        private static object GetValueTypeForXLS(XSSFCell cell, Type type = null)
        {
            DateTime t = DateTime.FromOADate(0);
            if (cell == null)
                return DBNull.Value;
            if (type != null)
            {
                if (type == typeof(bool?))
                    return (Boolean?)cell.BooleanCellValue;
                if (type == typeof(string))
                    return cell.ToString();
                if (type == typeof(DateTime?))
                    return TimeExTreat(cell.DateCellValue);
                if (type == typeof(double?))
                    return (double?)cell.NumericCellValue;
            }
            switch (cell.CellType)
            {
                case CellType.Blank: //BLANK:
                    return DBNull.Value;
                case CellType.Boolean: //BOOLEAN:
                    return (Boolean?)cell.BooleanCellValue;
                case CellType.String: //STRING:
                    if (DateTime.TryParse(cell.StringCellValue, out t))
                        return t;
                    else
                        return cell.StringCellValue;
                case CellType.Numeric: //NUMERIC:  
                    if (DateUtil.IsCellDateFormatted(cell))
                        return TimeExTreat(cell.DateCellValue);
                    else
                        return (double?)cell.NumericCellValue;

                case CellType.Error: //ERROR:
                    return cell.ErrorCellValue;
                case CellType.Formula: //FORMULA:
                default:
                    return "=" + cell.CellFormula;
            }
        }
        private static DateTime? TimeExTreat(DateTime d)
        {
            if (d.Millisecond >= 500) d = d.AddSeconds(1);
            return (DateTime?)d;
        }
        private static Type GetNullType(object obj)
        {
            Type Tobject = obj.GetType();
            if (Tobject == typeof(double)) Tobject = typeof(double?);
            if (Tobject == typeof(DateTime)) Tobject = typeof(DateTime?);
            return Tobject;
        }
        private static Type GetNullTypeOf(object obj)
        {
            Type Tobject = obj.GetType();
            if (Tobject == typeof(double)) Tobject = typeof(double?);
            if (Tobject == typeof(DateTime)) Tobject = typeof(DateTime?);
            return Tobject;
        }
    }

}