using System;
using System.Data;
using System.IO;
using System.Collections.Generic;
using System.Web;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System.Web.UI.WebControls;
using System.Web.UI;
using NPOI.XSSF.UserModel;
using System.Data.OleDb;

namespace Pcb.Common
{
    public partial class ExcelHelper
    {
        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="sheetName">工作表名称</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataSetToExcel(DataSet sourceDs, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            string[] sheetNames = sheetName.Split(',');
            for (int i = 0; i < sheetNames.Length; i++)
            {
                ISheet sheet = workbook.CreateSheet(sheetNames[i]);
                IRow headerRow = sheet.CreateRow(0);
                // handling header.
                foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

                // handling value.
                int rowIndex = 1;

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

                    foreach (DataColumn column in sourceDs.Tables[i].Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }

                    rowIndex++;
                }
            }
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            workbook = null;
            return ms;
        }

        /// <summary>
        /// 由DataSet导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <returns>Excel工作表</returns>
        public static void ExportDataSetToExcel(DataSet sourceDs, string fileName, string sheetName)
        {
            MemoryStream ms = ExportDataSetToExcel(sourceDs, sheetName) as MemoryStream;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }

        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataTableToExcel(DataTable sourceTable, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                foreach (DataColumn column in sourceTable.Columns)
                {
                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                }

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 由DataTable导出Excel
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <returns>Excel工作表</returns>
        public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, string sheetName)
        {
            MemoryStream ms = ExportDataTableToExcel(sourceTable, sheetName) as MemoryStream;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }

        /// <summary>
        /// 由DataTable导出Excel，导出淘宝发货订单专用
        /// add by sunyichao 2019-03-13
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <param name="fileName">指定Excel工作表名称</param>
        /// <returns>Excel工作表</returns>
        public static void ExportDataTableToExcelForTaoBao(DataTable sourceTable, string fileName, string sheetName)
        {
            MemoryStream ms = ExportDataTableToExcelForTaoBao(sourceTable, sheetName) as MemoryStream;
            HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
            HttpContext.Current.Response.BinaryWrite(ms.ToArray());
            HttpContext.Current.Response.End();
            ms.Close();
            ms = null;
        }

        /// <summary>
        /// 由DataTable导出Excel，导出淘宝发货订单专用
        /// add by sunyichao 2019-03-13
        /// </summary>
        /// <param name="sourceTable">要导出数据的DataTable</param>
        /// <returns>Excel工作表</returns>
        private static Stream ExportDataTableToExcelForTaoBao(DataTable sourceTable, string sheetName)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms = new MemoryStream();
            ISheet sheet = workbook.CreateSheet(sheetName);
            IRow headerRow = sheet.CreateRow(0);
            // handling header.
            foreach (DataColumn column in sourceTable.Columns)
                headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = 1;

            foreach (DataRow row in sourceTable.Rows)
            {
                IRow dataRow = sheet.CreateRow(rowIndex);

                //淘宝单号包含“-”
                if (row[0].ToString().Contains("-") || row[0].ToString().Contains("/"))
                {
                    var strTemp = row[0].ToString().Split(new string[] { "-" }, StringSplitOptions.RemoveEmptyEntries);
                    if (strTemp.Length == 1)
                        strTemp = row[0].ToString().Split(new string[] { "/" }, StringSplitOptions.RemoveEmptyEntries);

                    int index = 0;
                    foreach (var str in strTemp)
                    {
                        if (index > 0)
                        {
                            rowIndex++;
                            dataRow = sheet.CreateRow(rowIndex);
                        }

                        foreach (DataColumn column in sourceTable.Columns)
                        {
                            if (column.Ordinal == 0)
                            {
                                dataRow.CreateCell(column.Ordinal).SetCellValue(str);
                            }
                            else
                            {
                                if (column.Ordinal == sourceTable.Columns.Count - 1)
                                {
                                    var expNo = row[sourceTable.Columns.Count - 1].ToString();
                                    if (row[sourceTable.Columns.Count - 1].ToString().Contains(","))
                                        expNo = expNo.Substring(0, expNo.IndexOf(","));

                                    dataRow.CreateCell(column.Ordinal).SetCellValue(expNo);
                                }
                                else
                                {
                                    dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                                }
                            }
                        }

                        index++;
                    }
                }
                else
                {
                    foreach (DataColumn column in sourceTable.Columns)
                    {
                        dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
                    }

                }

                rowIndex++;
            }

            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;

            sheet = null;
            headerRow = null;
            workbook = null;

            return ms;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            ISheet sheet = workbook.GetSheet(sheetName);

            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

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

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="sheetName">Excel工作表名称</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
            }
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表索引</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
        {
            IWorkbook workbook = new HSSFWorkbook(excelFileStream); ;
            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                {
                    // 如果遇到第一个空列，则不再继续向后读取
                    cellCount = i + 1;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)// || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == ""
                {
                    // 如果遇到第一个空行，则不再继续向后读取
                    break;
                }

                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (DBNull.Value.Equals(row.GetCell(j)))
                        dataRow[j] = string.Empty;
                    else
                        dataRow[j] = row.GetCell(j);
                }
                table.Rows.Add(dataRow);
            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="sheetName">Excel工作表索引</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcelByFile(string file, int sheetIndex, int headerRowIndex)
        {
            IWorkbook workbook = null;
            Stream excelFileStream = null;
            try
            {
                excelFileStream = System.IO.File.OpenRead(file);
                workbook = new HSSFWorkbook(excelFileStream);

            }
            catch (Exception ex)
            {
                excelFileStream = System.IO.File.OpenRead(file);
                workbook = new XSSFWorkbook(excelFileStream); ;

            }


            ISheet sheet = workbook.GetSheetAt(sheetIndex);

            DataTable table = new DataTable();

            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                try
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列，则不再继续向后读取
                        cellCount = i + 1;
                        break;
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Error(ex.ToString());
                    // 如果遇到第一个空列，则不再继续向后读取
                    cellCount = i + 1;
                    break;
                }
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            for (int i = (headerRowIndex + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                if (row == null)// || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == ""
                {
                    // 如果遇到第一个空行，则不再继续向后读取
                    break;
                }

                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (DBNull.Value.Equals(row.GetCell(j)))
                        dataRow[j] = string.Empty;
                    else
                        dataRow[j] = row.GetCell(j);
                }
                table.Rows.Add(dataRow);
            }
            excelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        /// <summary>
        /// 由Excel导入DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="sheetName">Excel工作表索引</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataTable</returns>
        public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
            }
        }

        /// <summary>
        /// 把excel转换成datatable
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <param name="suffix">文件后缀名</param>
        /// <param name="sheetName">工作区的名称</param>
        /// <param name="sheetTab">sheetTab一个还是多个 小于等于 1为 1个</param>
        /// <returns></returns>
        public static DataTable FromExcelToDataTable(string path, string suffix, string sheetName, int sheetTab)
        {
            OleDbConnection conn = null;
            try
            {
                path = HttpContext.Current.Server.MapPath(path);
                string strConn = string.Empty;
                if (suffix == ".xls") //如果excel为2003版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                else //如果excel为2007+版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                conn = new OleDbConnection(strConn);
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
                var dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                DataTable dt = new DataTable();
                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                if (sheetTab > 1)
                {
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {
                        strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                        OleDbDataAdapter myCommand = null;

                        //从指定的表明查询数据,可先把所有表明列出来供用户选择
                        string strExcel = "select * from [" + strTableNames[k] + "]";
                        myCommand = new OleDbDataAdapter(strExcel, strConn);
                        myCommand.Fill(dt);
                    }
                }
                else
                {
                    for (int k = 0; k < dtSheetName.Rows.Count; k++)
                    {
                        strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                    }

                    OleDbDataAdapter myCommand = null;
                    //从指定的表明查询数据,可先把所有表明列出来供用户选择
                    string strExcel = "select * from [" + strTableNames[0] + "]";
                    myCommand = new OleDbDataAdapter(strExcel, strConn);
                    myCommand.Fill(dt);
                }
                return dt;
            }
            catch (Exception e)
            {
                return new DataTable();
            }
            finally
            {
                conn.Close();
                System.IO.File.Delete(path);
            }
        }

        /// <summary>
        /// 把excel转换成datatable
        /// </summary>
        /// <param name="path">excel文件路径</param>
        /// <param name="suffix">文件后缀名</param>
        /// <param name="sheetTab">工作区的Table</param>
        /// <returns></returns>
        public static DataTable FromExcelToDataTable(string path, string suffix, int sheetTab)
        {
            OleDbConnection conn = null;
            try
            {
                path = HttpContext.Current.Server.MapPath(path);
                string strConn = string.Empty;
                if (suffix == ".xls") //如果excel为2003版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                else //如果excel为2007+版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                conn = new OleDbConnection(strConn);
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
                var dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                DataTable dt = new DataTable();
                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                strTableNames[sheetTab] = dtSheetName.Rows[sheetTab]["TABLE_NAME"].ToString();
                OleDbDataAdapter myCommand = null;
                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                string strExcel = "select * from [" + strTableNames[sheetTab] + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return new DataTable();
            }
            finally
            {
                conn.Close();
                //System.IO.File.Delete(path);
            }
        }

        public static DataTable FromExcelToDataTableNoDelete(string path, string suffix, string sheetName)
        {
            OleDbConnection conn = null;
            try
            {
                path = HttpContext.Current.Server.MapPath(path);
                string strConn = string.Empty;
                if (suffix == ".xls") //如果excel为2003版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                else //如果excel为2007+版本则使用下面的链接方式
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                conn = new OleDbConnection(strConn);
                conn.Open();
                //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等  
                var dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

                //包含excel中表名的字符串数组
                string[] strTableNames = new string[dtSheetName.Rows.Count];
                for (int k = 0; k < dtSheetName.Rows.Count; k++)
                {
                    strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
                }

                OleDbDataAdapter myCommand = null;
                DataTable dt = new DataTable();

                //从指定的表明查询数据,可先把所有表明列出来供用户选择
                string strExcel = "select * from [" + strTableNames[0] + "]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                myCommand.Fill(dt);
                return dt;
            }
            catch (Exception e)
            {
                return new DataTable();
            }
            finally
            {
                conn.Close();
            }
        }
        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFileStream">Excel文件流</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
        {
            DataSet ds = new DataSet();
            HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
            for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
            {
                ISheet sheet = workbook.GetSheetAt(a);
                DataTable table = new DataTable();

                IRow headerRow = sheet.GetRow(headerRowIndex);
                int cellCount = headerRow.LastCellNum;
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
                    {
                        // 如果遇到第一个空列，则不再继续向后读取
                        cellCount = i + 1;
                        break;
                    }

                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
                    {
                        // 如果遇到第一个空行，则不再继续向后读取
                        break;
                    }

                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        if (row.GetCell(j) != null)
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }

                    table.Rows.Add(dataRow);
                }
                ds.Tables.Add(table);
            }

            excelFileStream.Close();
            workbook = null;

            return ds;
        }

        /// <summary>
        /// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
        /// </summary>
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
        /// <param name="headerRowIndex">Excel表头行索引</param>
        /// <returns>DataSet</returns>
        public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ImportDataSetFromExcel(stream, headerRowIndex);
            }
        }

        /// <summary>
        /// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列名，如第0列为A，第1列为B...</returns>
        public static string ConvertColumnIndexToColumnName(int index)
        {
            index = index + 1;
            int system = 26;
            char[] digArray = new char[100];
            int i = 0;
            while (index > 0)
            {
                int mod = index % system;
                if (mod == 0) mod = system;
                digArray[i++] = (char)(mod - 1 + 'A');
                index = (index - 1) / 26;
            }
            StringBuilder sb = new StringBuilder(i);
            for (int j = i - 1; j >= 0; j--)
            {
                sb.Append(digArray[j]);
            }
            return sb.ToString();
        }

        /// <summary>
        /// 转化日期
        /// </summary>
        /// <param name="date">日期</param>
        /// <returns></returns>
        public static DateTime ConvertDate(string date)
        {
            DateTime dt = new DateTime();
            string[] time = date.Split('-');
            int year = Convert.ToInt32(time[2]);
            int month = Convert.ToInt32(time[0]);
            int day = Convert.ToInt32(time[1]);
            string years = Convert.ToString(year);
            string months = Convert.ToString(month);
            string days = Convert.ToString(day);
            if (months.Length == 4)
            {
                dt = Convert.ToDateTime(date);
            }
            else
            {
                string rq = "";
                if (years.Length == 1)
                {
                    years = "0" + years;
                }
                if (months.Length == 1)
                {
                    months = "0" + months;
                }
                if (days.Length == 1)
                {
                    days = "0" + days;
                }
                rq = "20" + years + "-" + months + "-" + days;
                dt = Convert.ToDateTime(rq);
            }
            return dt;
        }

        /// <summary>
        /// 导出Excel文件，并自定义文件名(IE下不出现乱码)
        /// </summary>
        public static void DataTable3Excel(System.Data.DataTable dtData, String FileName)
        {
            GridView dgExport = null;
            HttpContext curContext = HttpContext.Current;
            StringWriter strWriter = null;
            HtmlTextWriter htmlWriter = null;

            if (dtData != null)
            {
                HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8);
                curContext.Response.AddHeader("content-disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls");
                curContext.Response.ContentType = "application nd.ms-excel";
                curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
                curContext.Response.Charset = "GB2312";
                strWriter = new StringWriter();
                htmlWriter = new HtmlTextWriter(strWriter);
                dgExport = new GridView();
                dgExport.DataSource = dtData.DefaultView;
                dgExport.AllowPaging = false;
                dgExport.DataBind();
                dgExport.RenderControl(htmlWriter);
                curContext.Response.Write(strWriter.ToString());
                curContext.Response.End();
            }
        }
        /// <summary>
        /// 动态修改列名
        /// </summary>
        /// <returns></returns>
        public static DataTable DynamicAltrtDataTableColunNmae(List<string> sysHeadColums, DataTable dt)
        {
            //var dtheadcolumn = sysHeadColums;
            int i = 0;
            foreach (DataColumn dc in dt.Columns)
            {
                //dtheadcolumn.Add(dc.ColumnName);
                dc.ColumnName = sysHeadColums[i];
                i++;
            }
            //var indexHead = new List<int>();
            //for (var i = 0; i < sysHeadColums.Count; i++)
            //{
            //    for (var j = 0; j < dtheadcolumn.Count; j++)
            //    {
            //        if (dtheadcolumn[j].ToStr().Contains(sysHeadColums[i]))
            //        {
            //            indexHead.Add(j);
            //            break;
            //        }
            //    }
            //}
            //dt.Columns[indexHead[0]].ColumnName = sysHeadColums[0];
            //dt.Columns[indexHead[1]].ColumnName = sysHeadColums[1];
            //dt.Columns[indexHead[2]].ColumnName = sysHeadColums[2];
            //dt.Columns[indexHead[3]].ColumnName = sysHeadColums[3];
            //dt.Columns[indexHead[4]].ColumnName = sysHeadColums[4];
            //dt.Columns[indexHead[5]].ColumnName = sysHeadColums[5];
            //dt.Columns[indexHead[6]].ColumnName = sysHeadColums[6];
            //dt.Columns[indexHead[7]].ColumnName = sysHeadColums[7];
            //dt.Columns[indexHead[8]].ColumnName = sysHeadColums[8];
            //dt.Columns[indexHead[9]].ColumnName = sysHeadColums[9];
            //dt.Columns[indexHead[10]].ColumnName = sysHeadColums[10];
            //dt.Columns[indexHead[11]].ColumnName = sysHeadColums[11];
            //dt.Columns[indexHead[12]].ColumnName = sysHeadColums[12];
            //dt.Columns[indexHead[13]].ColumnName = sysHeadColums[13];
            //dt.Columns[indexHead[14]].ColumnName = sysHeadColums[14];
            //dt.Columns[indexHead[15]].ColumnName = sysHeadColums[15];

            return dt;
        }
    }

    /// <summary>
    /// 创建：by heyc @20151126
    /// </summary>
    public partial class ExcelHelper
    {
        public static readonly ExcelHelper instance = new ExcelHelper();

        HSSFWorkbook workbook;

        #region Excel导出

        /// <summary>
        /// For DataTable Excel 导出
        /// </summary>
        /// <param name="dtTable">导出表</param>
        /// <param name="dicHeadInfo">顶部统计信息</param>
        /// <param name="listColumns">报表自定义列</param>
        /// <param name="fileName">导出文件名称</param>
        /// <param name="sheetName">Sheet 名称</param>
        /// <param name="isAjax">isAjax=true 返回文件地址，否则输出流</param>
        public string ExportFromTable(DataTable dtTable, Dictionary<string, string> dicHeadInfo = null, List<ExcelColumns> listColumns = null, string fileName = "", string sheetName = "", bool isAjax = false)
        {
            if (dtTable == null || listColumns == null || listColumns.Count <= 0)
                return "";

            if (string.IsNullOrEmpty(sheetName))
                sheetName = "Sheet1";

            if (string.IsNullOrEmpty(fileName))
                fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(1000, 9999).ToString();

            workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet(sheetName);
            sheet.DefaultColumnWidth = 16;

            ICellStyle cellStyle = null;//单元格样式
            ICellStyle columnStyle = null;//报表列样式
            ICellStyle headStyle = null;//顶部统计信息样式
            SetCellStyle(out cellStyle, out columnStyle, out headStyle);

            string mergedCells = string.Empty;//纵向合并列集合
            int colIndex = 0, rowIndex = 0;//全局列索引，行索引
            int columnsRowIndex = 0;//标题列行索引
            ICell icell = null;

            #region 顶部统计等信息

            //顶部统计信息
            if (dicHeadInfo != null && dicHeadInfo.Count > 0)
            {
                IRow cellHeader = sheet.CreateRow(rowIndex);
                cellHeader.Height = 500;
                foreach (var col in dicHeadInfo)
                {
                    if (col.Value == "000")//换行
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, listColumns.Count - 1));//合并
                        rowIndex++;
                        colIndex = 0;
                        cellHeader = sheet.CreateRow(rowIndex);
                        cellHeader.Height = 500;
                    }
                    else
                    {
                        icell = cellHeader.CreateCell(colIndex);
                        icell.SetCellValue(col.Value);
                        icell.CellStyle = headStyle;
                        colIndex++;
                    }
                }
                sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, listColumns.Count - 1));
                rowIndex++;//行索引+1
            }

            #endregion

            #region 创建标题列

            IRow cellColumn = sheet.CreateRow(rowIndex);
            cellColumn.Height = 350;
            colIndex = 0;
            columnsRowIndex = rowIndex;//获取标题列所在行索引
            foreach (ExcelColumns col in listColumns)
            {
                icell = cellColumn.CreateCell(colIndex);
                icell.SetCellValue(col.FieldTitle);
                icell.CellStyle = columnStyle;
                colIndex++;
                if (col.IsMerged)//如果合并，记录下需要合并的列
                    mergedCells += col.FieldTitle + ",";
            }
            /*
             第一个参数表示要冻结的列数；
            第二个参数表示要冻结的行数；
            第三个参数表示右边区域可见的首列序号，从1开始计算；
            第四个参数表示下边区域可见的首行序号，也是从1开始计算；
             */
            sheet.CreateFreezePane(0, rowIndex + 1, 0, 1);
            rowIndex++;//行索引+1

            #endregion

            #region 创建单元格，填充内容

            //创建单元格
            string strValue = string.Empty;
            Dictionary<string, decimal> dicBottomInfo = null;
            foreach (DataRow row in dtTable.Rows)
            {
                IRow cellRow = sheet.CreateRow(rowIndex);//创建行
                cellRow.Height = 100 * 3;
                colIndex = 0;
                foreach (ExcelColumns col in listColumns)
                {
                    //判断自定义列 FieldName 是否与表字段名一致，不一致则单元格赋予空值
                    if (dtTable.Columns[col.FieldName] != null)
                    {
                        //数据格式化
                        if (col.FieldType == "Int")//整型
                            strValue = string.Format("{0:0.#}", string.IsNullOrEmpty(row[col.FieldName].ToString()) ? 0 : row[col.FieldName]);
                        else if (col.FieldType == "Dec")//小数
                            strValue = string.Format("{0:0.00}", string.IsNullOrEmpty(row[col.FieldName].ToString()) ? 0 : row[col.FieldName]);
                        else if (col.FieldType == "Date")//日期
                            strValue = string.Format("{0:yyyy年MM月dd日}", row[col.FieldName]);
                        else if (col.FieldType == "DateOne")//日期
                            strValue = string.Format("{0:yyyy-MM-dd}", row[col.FieldName]);
                        else if (col.FieldType == "DateTime")//时间
                            strValue = string.Format("{0:yyyy年MM月dd日 HH:mm:ss}", row[col.FieldName]);
                        else
                            strValue = row[col.FieldName].ToString();

                        //通过委托调用方法
                        if (col.Action != null)
                            strValue = col.Action(strValue);

                        //记录统计列信息
                        if (col.IsAccount)
                        {
                            if (dicBottomInfo == null)
                                dicBottomInfo = new Dictionary<string, decimal>();
                            //如果集合不包含该列则添加,以Excel标题列为键值
                            if (!dicBottomInfo.ContainsKey(col.FieldTitle))
                                dicBottomInfo.Add(col.FieldTitle, decimal.Parse(string.IsNullOrEmpty(strValue) ? "0" : strValue));
                            else//如果存在则累加
                                dicBottomInfo[col.FieldTitle] += decimal.Parse(string.IsNullOrEmpty(strValue) ? "0" : strValue);
                        }
                    }
                    else
                        strValue = string.Empty;

                    icell = cellRow.CreateCell(colIndex);
                    icell.SetCellValue(strValue);
                    icell.CellStyle = cellStyle;
                    colIndex++;
                }
                rowIndex++;
            }

            //纵向合并
            if (!string.IsNullOrEmpty(mergedCells))
                MergedCells(sheet, mergedCells, columnsRowIndex);

            #endregion

            #region 生成底部总计信息

            //添加底部总计信息
            //StringBuilder sbBottomInfo = new StringBuilder();
            //if (dtTable.Rows.Count > 0)
            //    sbBottomInfo.Append(string.Format("总计： 共{0}条记录", dtTable.Rows.Count));
            //if (dicBottomInfo != null && dicBottomInfo.Count > 0)
            //{
            //    foreach (var col in dicBottomInfo)
            //    {
            //        if (col.Value > 0)
            //            sbBottomInfo.Append(string.Format("     {0}：{1}", col.Key, col.Value));
            //    }

            //    colIndex = 0;
            //    IRow cellBottom = sheet.CreateRow(rowIndex);
            //    cellBottom.Height = 350;
            //    icell = cellBottom.CreateCell(colIndex);
            //    icell.SetCellValue(sbBottomInfo.ToString());
            //    icell.CellStyle = columnStyle;
            //    sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, listColumns.Count - 1));//合并
            //}

            #endregion

            #region 导出

            string tmpPath = "/upload/temp/export";
            string tmpFile = "";
            using (MemoryStream stream = new MemoryStream())
            {
                workbook.Write(stream);
                if (isAjax)
                {
                    if (!Directory.Exists(HttpContext.Current.Server.MapPath(tmpPath)))
                        Directory.CreateDirectory(HttpContext.Current.Server.MapPath(tmpPath));
                    tmpFile = string.Format("{0}\\{1}.xls", HttpContext.Current.Server.MapPath(tmpPath), fileName);
                    File.WriteAllBytes(tmpFile, stream.GetBuffer());
                }
                else
                {
                    string userAgent = System.Web.HttpContext.Current.Request.UserAgent.ToUpper();
                    if (userAgent.IndexOf("FIREFOX") < 0) fileName = System.Web.HttpContext.Current.Server.UrlEncode(fileName);
                    else fileName = System.Web.HttpContext.Current.Server.UrlDecode(fileName);

                    HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                    HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls", fileName));//如果去掉.xls 将导致火狐下 导出文件后缀丢失
                    HttpContext.Current.Response.Clear();
                    HttpContext.Current.Response.BinaryWrite(stream.GetBuffer());
                    HttpContext.Current.Response.Flush();
                    HttpContext.Current.Response.Close();
                }

                stream.Close();
            }

            return string.Format("{0}/{1}.xls", tmpPath, fileName);

            #endregion
        }

        /// <summary>
        /// 设置报表单元格样式
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="cellStyle"></param>
        /// <param name="columnStyle"></param>
        private void SetCellStyle(out ICellStyle cellStyle, out ICellStyle columnStyle, out ICellStyle headStyle)
        {
            cellStyle = columnStyle = headStyle = null;
            if (workbook == null) return;

            //单元格样式 
            cellStyle = workbook.CreateCellStyle();
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.Alignment = HorizontalAlignment.Left;
            cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
            cellStyle.LeftBorderColor = cellStyle.RightBorderColor
                = cellStyle.TopBorderColor = cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");//以文本形式存储

            //列样式
            columnStyle = workbook.CreateCellStyle();
            columnStyle.CloneStyleFrom(cellStyle);
            columnStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
            columnStyle.FillPattern = FillPattern.SolidForeground;
            //columnStyle.WrapText = true;//自动换行
            columnStyle.Alignment = HorizontalAlignment.Center;
            columnStyle.VerticalAlignment = VerticalAlignment.Center;

            //顶部统计样式
            headStyle = workbook.CreateCellStyle();
            headStyle.CloneStyleFrom(columnStyle);
            headStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.White.Index;
            headStyle.Alignment = HorizontalAlignment.Left;

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "jiepei.com";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "jiepei.com";
            workbook.SummaryInformation = si;
        }

        /// <summary>
        /// 单元格纵向合并
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="mergedCells">合并的列集合</param>
        /// <param name="headRowIndex">报表标题列 行索引</param>
        private void MergedCells(ISheet sheet, string mergedCells, int columnsRowIndex)
        {
            if (string.IsNullOrEmpty(mergedCells)) return;

            if (!mergedCells.StartsWith(",")) mergedCells = "," + mergedCells;
            if (!mergedCells.EndsWith(",")) mergedCells += ",";

            //需要合并列数量
            int iNumA = 0, iNumB = mergedCells.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Length;
            int colIndex = 0, rowIndex = 0, rowIndexMin = 0;
            string strValue = string.Empty;

            //获取标题行
            IRow rowHead = null;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            while (rows.MoveNext())
            {
                if (rowIndex == columnsRowIndex)
                {
                    rowHead = (HSSFRow)rows.Current;
                    break;
                }
                rowIndex++;
            }
            rowIndex = 0;
            rowHead.GetCell(0);
            IRow irow = null;//内容行
            for (colIndex = 0; colIndex < rowHead.LastCellNum; colIndex++) //foreach (ICell cell in rowHead.Cells)//遍历列
            {
                if (iNumB == iNumA) break;//避免不必要的遍历
                if (!mergedCells.Contains("," + rowHead.GetCell(colIndex).StringCellValue + ","))
                {
                    colIndex++;
                    continue;
                }
                rowIndexMin = columnsRowIndex;//标题列索引
                strValue = string.Empty;
                //直接从标题行下一行开始遍历
                for (rowIndex = columnsRowIndex; rowIndex <= sheet.LastRowNum; rowIndex++)
                {
                    irow = sheet.GetRow(rowIndex);//获取标题行下一行
                    if (rowIndexMin == columnsRowIndex)
                    {
                        strValue = irow.GetCell(colIndex).StringCellValue;
                        rowIndexMin = rowIndex;
                    }
                    else if (strValue != irow.GetCell(colIndex).StringCellValue)
                    {
                        if ((rowIndex - 1) >= rowIndexMin)
                            sheet.AddMergedRegion(new CellRangeAddress(rowIndexMin, rowIndex - 1, colIndex, colIndex));
                        strValue = irow.GetCell(colIndex).StringCellValue;
                        rowIndexMin = rowIndex;
                    }
                    else if (rowIndex == (sheet.LastRowNum))
                    {
                        if (strValue == irow.GetCell(colIndex).StringCellValue)
                        {
                            if ((rowIndex - 1) >= rowIndexMin)
                                sheet.AddMergedRegion(new CellRangeAddress(rowIndexMin, rowIndex, colIndex, colIndex));
                        }
                    }
                }
                //colIndex++;
                iNumA++;//合并列次数+1
            }
        }

        #endregion

        #region EXCEL导入

        /// <summary>
        /// 导入EXCEL数据
        /// </summary>
        /// <param name="listColumns">EXCEL 列对应表列集合</param>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        public DataTable ImportExcelToTable(List<ExcelColumns> listColumns, string filePath)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                workbook = new HSSFWorkbook(file);
                ISheet sheet = workbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

                DataTable dtTable = new DataTable();
                //添加表列
                foreach (ExcelColumns col in listColumns)
                {
                    dtTable.Columns.Add(col.FieldName);
                }

                int rowIndex = 0, colIndex = 0;
                IRow headRow = null;
                ICell icell = null;
                while (rows.MoveNext())
                {
                    if (rowIndex == 0)
                    {
                        headRow = (HSSFRow)rows.Current;//获取Excel 标题行
                        rowIndex++;
                        continue;
                    }
                    IRow row = (HSSFRow)rows.Current;
                    DataRow newRow = dtTable.NewRow();
                    colIndex = 0;
                    for (colIndex = 0; colIndex < headRow.LastCellNum; colIndex++) //foreach (ICell cell in headRow.Cells)
                    {
                        icell = headRow.GetCell(colIndex);
                        //只允许导出集合 listColumns 包含的列，且列队顺序一致
                        if (!icell.StringCellValue.Equals(listColumns[colIndex].FieldTitle))
                            newRow[colIndex] = row.Cells[colIndex].StringCellValue;
                        else
                            newRow[colIndex] = null;

                        colIndex++;
                    }

                    rowIndex++;
                    dtTable.Rows.Add(newRow);
                }

                return dtTable;
            }
        }

        #endregion
    }

    #region 报表自定义列

    /// <summary>
    /// 通过委托调用导出列处理方法（有且仅有一个string参数的方法，均可使用该委托，方法名不做限制）（导出使用）
    /// </summary>
    /// <param name="value"></param>
    public delegate string ColumnAction(string value);

    /// <summary>
    /// 通过委托调用导出列校验方法（导入使用）
    /// </summary>
    /// <param name="value">校验的内容</param>
    /// <param name="errMsg">错误信息</param>
    /// <returns></returns>
    public delegate string ColumnCheck(string value, out string errMsg);

    /// <summary>
    /// 导出报表自定义列
    /// </summary>
    public class ExcelColumns
    {
        /// <summary>
        /// 通过构造函数直接赋值
        /// </summary>
        /// <param name="fieldName">对应表列名</param>
        /// <param name="fieldTitle">对应Excel标题</param>
        public ExcelColumns(string fieldName = "", string fieldTitle = "")
        {
            this.FieldName = fieldName;
            this.FieldTitle = fieldTitle;
        }

        /// <summary>
        /// 对应表列名
        /// </summary>
        public string FieldName;

        /// <summary>
        /// 对应Excel标题
        /// </summary>
        public string FieldTitle;

        /// <summary>
        /// 单元格数据格式（Str:字符类型(默认)，Int:整数，Dec:小数类型10.00，Date:日期类型 2014年01月01日,DateTime:日期时间类型 2014年01月01日 20:13:14）
        /// </summary>
        public string FieldType;

        /// <summary>
        /// 是否纵向合并单元格（默认 false）
        /// </summary>
        public bool IsMerged;

        /// <summary>
        /// 是否需要统计 累加计算（默认 false）
        /// </summary>
        public bool IsAccount;

        /// <summary>
        /// 声明处理列委托对象（导出使用）
        /// </summary>
        public ColumnAction Action;

        /// <summary>
        /// 声明校验列委托对象（导入使用）
        /// </summary>
        /// <returns></returns>
        public ColumnCheck Check;
    }

    #endregion
}