NPOI 讀取 EXCEL 資料

DataTable 與 Excel 之間互轉

using System; 
using System.Collections.Generic; 
using System.Data; 
using System.IO; 
using System.Linq; 
using System.Web; 
using NPOI; 
using NPOI.HPSF; 
using NPOI.HSSF; 
using NPOI.HSSF.UserModel; 
using NPOI.POIFS; 
using NPOI.Util; 
 
public class DataTableRenderToExcel  
{ 
    public static Stream RenderDataTableToExcel(DataTable SourceTable) 
    { 
        HSSFWorkbook workbook = new HSSFWorkbook(); 
        MemoryStream ms = new MemoryStream(); 
        HSSFSheet sheet = workbook.CreateSheet(); 
        HSSFRow 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) 
        { 
            HSSFRow 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; 
    } 
 
    public static void RenderDataTableToExcel(DataTable SourceTable, string FileName) 
    { 
        MemoryStream ms = RenderDataTableToExcel(SourceTable) as MemoryStream; 
        FileStream fs = new FileStream(FileName, FileMode.Create, FileAccess.Write); 
        byte[] data = ms.ToArray(); 
 
        fs.Write(data, 0, data.Length); 
        fs.Flush(); 
        fs.Close(); 
 
        data = null; 
        ms = null; 
        fs = null; 
    } 
 
    public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex) 
    { 
        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); 
        HSSFSheet sheet = workbook.GetSheet(SheetName); 
 
        DataTable table = new DataTable(); 
 
        HSSFRow 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); 
        } 
 
        int rowCount = sheet.LastRowNum; 
 
        for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) 
        { 
            HSSFRow 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; 
    } 
 
    public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, int SheetIndex, int HeaderRowIndex) 
    { 
        HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream); 
        HSSFSheet sheet = workbook.GetSheetAt(SheetIndex); 
 
        DataTable table = new DataTable(); 
 
        HSSFRow 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); 
        } 
 
        int rowCount = sheet.LastRowNum; 
 
        for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++) 
        { 
            HSSFRow row = sheet.GetRow(i); 
            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); 
        } 
 
        ExcelFileStream.Close(); 
        workbook = null; 
        sheet = null; 
        return table; 
    } 
}