NPOI元件撈取Excel範例

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace WA_AspNetEofficeAdmin.bin.database
{
    public class TbHolidayDal
    {
        //宣告一個輕型物件集合(collection)
        private IList<entity.HchgXXHoliday> _list;

        public TbHolidayDal() {
            _list = new List<entity.HchgXXHoliday>();
        }

        //用以返回_list集合物件的操作
        public IList<entity.HchgXXHoliday> LoadInternalDataAtFirstIndex(byte[] bContent)
        {
            //如果讀入的位元組陣列長度為0, 表示為空值, 直接返回null
            if (bContent.Length==0)
                return null;
            //將bContent轉換到記憶體串流
            MemoryStream memoryStream = new MemoryStream(bContent);
            //由於bContent要屬於Excel文件資料, 所以workBook將memoryStream物件轉乘Excel相關操作物件
            HSSFWorkbook workBook = new HSSFWorkbook(memoryStream);
            //從workbook物件取出第一個sheet
            HSSFSheet sheet = workBook.GetSheetAt(0) as HSSFSheet;
            //取出表頭
            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
            //讀取表頭的行(column)數量為何
            int cellCount = headerRow.LastCellNum;
            //取得一個sheet的總列數為多少
            int rowCount = sheet.LastRowNum;
            //從第一個列數走訪每一列, 到最後的列數(rowCount)
            for (int rowIndex = (sheet.FirstRowNum + 1); rowIndex < rowCount; rowIndex++) {
                HSSFRow row = sheet.GetRow(rowIndex) as HSSFRow;
                DateTime? hdate=null;
                string name=null;
                //flag用來判斷是否是空row, 只要flag取得為true, 代表他不是空row
                bool flag=false;
                //從取得的row查詢存在的cell
                for (int cellIndex = row.FirstCellNum; cellIndex < cellCount; cellIndex++) {
                    if (row.GetCell(cellIndex) != null) {
                        Cell cell = row.GetCell(cellIndex);
                        //確認目前cell的資料型態
                        switch (cell.CellType) {
                            case CellType.NUMERIC:
                                try
                                {
                                    flag = checkKeyValue(cell.DateCellValue);
                                    if (flag)
                                        hdate = cell.DateCellValue;  
                                }
                                catch (Exception ex) {
                                    ex = null;
                                }
                                break;
                            case CellType.STRING:
                                name = cell.StringCellValue;
                                break;
                        }
                    }
                }//end loop: for 2

                //如果flag為true, 非空row, 建立一個輕型物件並且賦值後, 加入對應的集合物件
                if (flag) {
                    entity.HchgXXHoliday holiday = new entity.HchgXXHoliday();
                    holiday.HDATE = hdate;
                    holiday.Name = name;
                    _list.Add(holiday);
                }
            }//end loop: for 1

            sheet = null;
            workBook = null;
            //記憶體串流物件必須被關閉
            memoryStream.Close();
            //返回集合物件
            return _list;
        }//end method: LoadInternalDataAtFirstIndex

        //檢查關鍵值的操作, 這表示類似主鍵檢查, 如果返回是false, 表示row根本沒有任何資料
        private bool checkKeyValue(object value) {
            if (value == null)
                return false;
            return true;
        }//end method: checkKeyValue
    }//end class: TbHolidayDal
}//end namesapce