[NPOI] 讀取Excel 檔案內容

進行SI 時, 總會遇到比較經典的File base 整合案例, Excel 便是其中一例. 在此示範如何存取Excel 檔案.在此建立了一個ExcelHelper 進行Excel 相關的存取動作.

ExcelHelper.cs

 public class ExcelHelper
    {
public DataTable readExcelToDataTable(string filePath, int sheetNum, int headerLine, int contentLine)
        {
            this.loadExcelFile(filePath);
            this.resetCurrentDataTable();
            this.readExcelHeader(sheetNum, headerLine, contentLine);
            return this.readExcelData(sheetNum, contentLine);     //line 0 is header, data start from line 1
        }
 public void loadExcelFile(string filePath)
        {
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                currentExcelFileType = Path.GetExtension(filePath);

                if (currentExcelFileType == ExcelFileType.xls)
                {
                    currentWorkbook = new HSSFWorkbook(file);
                }
                else if (currentExcelFileType == ExcelFileType.xlsx)
                {
                    currentWorkbook = new XSSFWorkbook(file);
                }
            }
        }
 public void resetCurrentDataTable()
        {
            currentTable = new DataTable();
        }
public void readExcelHeader(int sheetNum, int headerline, int contentline)
        {
            ISheet sheet = currentWorkbook.GetSheetAt(sheetNum);
            IRow row = sheet.GetRow(headerline);
            IRow contentRow = sheet.GetRow(contentline);
            

            for (int i = 0; i < row.LastCellNum; i++)
            {
                ICell cell = row.GetCell(i);
                ICell contentCell = contentRow.GetCell(i);

                // Handle incorrect format import if 1st content row has empty data.
                while (contentCell.IsCellEmpty())
                {
                    contentRow = sheet.GetRow(contentline++);
                    contentCell = contentRow.GetCell(i);
                }

                // Base on content value to decide Data coulumn type.
                if ((cell != null) && (contentCell != null))
                {
                    // ToDo: Refactor repeatable code.
                    if (contentCell.CellType == CellType.Numeric)
                    {
                        // Handle date time format.
                        if (HSSFDateUtil.IsCellDateFormatted(contentCell))
                            this.addImportTableColumn(cell.StringCellValue, TableColumnType.DateTime);
                        else
                            this.addImportTableColumn(cell.StringCellValue, TableColumnType.Decimal);
                    }
                    else if (contentCell.CellType == CellType.Formula)
                    {
                        switch (contentCell.CachedFormulaResultType)
                        {
                            case CellType.String:
                                this.addImportTableColumn(cell.StringCellValue, TableColumnType.String);
                                break;
                            case CellType.Numeric:
                                if (HSSFDateUtil.IsCellDateFormatted(contentCell))
                                    this.addImportTableColumn(cell.StringCellValue, TableColumnType.DateTime);
                                else
                                    this.addImportTableColumn(cell.StringCellValue, TableColumnType.Decimal);
                                break;
                        }
                    }
                    else if (contentCell.CellType == CellType.String)
                    {
                        this.addImportTableColumn(cell.StringCellValue, TableColumnType.String);
                    }
                    else
                    {
                        this.addImportTableColumn(cell.StringCellValue, TableColumnType.String);
                    }
                }
            }

        }
 public DataTable readExcelData(int sheetNum, int offsetLineNum)
        {
            ISheet sheet = currentWorkbook.GetSheetAt(sheetNum);
            int numOfRows = sheet.LastRowNum;

            for (int r = offsetLineNum; r < numOfRows - 1; r++)
            {
                IRow row = sheet.GetRow(r);
                DataRow dr = currentTable.NewRow();

                for (int i = 0; i < row.LastCellNum; i++)
                {
                    ICell cell = row.GetCell(i);
                    
                    if (cell == null)
                    {
                        dr[i] = null;
                        
                    }
                    else
                    {
                        if ((cell.CellType == CellType.Numeric) && (currentTable.Columns[i].DataType.IsNumericType()))
                        {
                            if (HSSFDateUtil.IsCellDateFormatted(cell))
                                dr[i] = cell.DateCellValue;
                            else
                                dr[i] = cell.NumericCellValue;
                        }
                        else if (cell.CellType == CellType.Formula)
                        {
                            switch (cell.CachedFormulaResultType)
                            {
                                case CellType.String:
                                    dr[i] = cell.StringCellValue;
                                    break;
                                case CellType.Numeric:
                                    if (HSSFDateUtil.IsCellDateFormatted(cell))
                                        dr[i] = cell.DateCellValue;
                                    else
                                        dr[i] = cell.NumericCellValue;
                                    break;
                            }
                        }
                        else if (cell.CellType == CellType.String)
                        {
                            dr[i] = cell.StringCellValue;
                        }
                        // Create default empty value.
                        else if (cell.IsCellEmpty())
                        {
                            dr[i] = Activator.CreateInstance(currentTable.Columns[i].DataType);
                        }
                        else
                        {
                            dr[i] = cell.ToString();
                        }
                    }
                }

                currentTable.Rows.Add(dr);
            }

            return currentTable;
        }
}