

using System;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
using System.Net;

public class NPOIHelper
{
    /// <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>
    /// 由DataTable导出Excel
    /// </summary>
    /// <param name="sourceTable">要导出数据的DataTable</param> 
    /// <returns>Excel工作表</returns>    
    public 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">要保存导出数据的工作表的路径和名</param> 
    /// <param name="sheetName">要导出数据的表的第一表的表名</param> 
    /// <returns>Excel工作表</returns>    
    public static void ExportDataTableToExcel(DataTable sourceTable, string fileName, 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;
        FileStream fileS = new FileStream(fileName, FileMode.Create);//修改为含路径保存
        workbook.Write(fileS);
        fileS.Close();
        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 ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName)
    {
        int rowIndex = 2;//从第二行开始，因为前两行是模板里面的内容 
        int colIndex = 0;
        FileStream file = new FileStream(modelpath + modelName + ".xls", FileMode.Open, FileAccess.Read);//读入excel模板
        HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
      
        HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet("Sheet1");
        //sheet1.GetRow(0).GetCell(0).SetCellValue("excelTitle");      //设置表头
        foreach (DataRow row in sourceTable.Rows)
        {   //双循环写入sourceTable中的数据
            rowIndex++;
            colIndex = 0;
            IRow xlsrow = sheet1.CreateRow(rowIndex);
            foreach (DataColumn col in sourceTable.Columns)
            {
                xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString());
                colIndex++;
            }
        }
        sheet1.ForceFormulaRecalculation = true;
        //FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存
        FileStream fileS = new FileStream(fileName, FileMode.Create);//修改为含路径保存
        hssfworkbook.Write(fileS);
        fileS.Close();
        file.Close();
    }







    /// <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)
    {
        HSSFWorkbook 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 = (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++)
            {
                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导入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;
    }

}


