透過NPOI匯入資料(VB.NET)

Posted on 2010年10月11日 下午 06:46 | 我要推薦

Dotblogs 的標籤： VB.NET , NPOI , 隨手記 , Excel

透過NPOI匯入/匯出Excel資料，是蠻常用的手法，不過網路上的範例都是 C#，而且我沒有查到處理Excel日期型別的匯入問題，偏偏我這邊的需求是一定會用到滴...

下面是要匯入的Excel簡單版：

 

 

這裡面要處理的有三種狀況：

1. 日期，要正確匯入，不能變成純數字，且格式要符合 yyyy/MM/dd。
2. 子編號要正確顯示，不能變成科學記號。
3. 公式欄位，只顯示公式的結果，而不是公式本身。

範例程式碼摘要如下：(主要是第 21 ~ 38 行的處理，特別是日期的判定，且日期判定一定要在數值判定之前，否則日期都會被轉成數字)

   1:      Private Function ConvertToDataTable(ByVal Workbook As HSSFWorkbook, ByVal SheetName As String) As DataTable

   2:          Try

   3:              Dim dt As DataTable = Nothing

   4:              Dim sheet As HSSFSheet = CType(Workbook.GetSheet(SheetName), HSSFSheet)

   5:              If IsNothing(sheet) Then

   6:                  Throw New Exception("指定的Excel檔中並此活頁：" & SheetName)

   7:              End If

   8:              'Dim HSSFRows As System.Collections.IEnumerator = sheet.GetRowEnumerator()

   9:              Dim iRowCount As Integer = sheet.LastRowNum

  10:              Dim iCountCol As Integer

  11:              Dim formulaEvaluator As HSSFFormulaEvaluator = Nothing

  12:              For RowIdx As Integer = 0 To iRowCount

  13:                  Dim row As HSSFRow = CType(sheet.GetRow(RowIdx), HSSFRow)

  14:                  If IsNothing(dt) Then

  15:                      '第一列最為標題列

  16:                      iCountCol = row.LastCellNum

  17:                      dt = Me.CreateTableSchema(CType(sheet.GetRow(0), HSSFRow))

  18:                  ElseIf Not IsNothing(row) Then

  19:                      '第二列以後為內容列

  20:                      Dim dr As DataRow = dt.NewRow

  21:                      For i As Integer = 0 To iCountCol - 1

  22:                          If IsNothing(row.GetCell(i)) OrElse IsDBNull(row.GetCell(i)) Then

  23:                              dr.Item(i) = ""

  24:                          ElseIf row.GetCell(i).CellType = ReadExcelUsedNPOI.HSSFCellType.NUMERIC _

  25:                              AndAlso DateUtil.IsValidExcelDate(row.GetCell(i).NumericCellValue) _

  26:                              AndAlso DateUtil.IsCellDateFormatted(row.GetCell(i)) Then

  27:                              dr.Item(i) = row.GetCell(i).DateCellValue.ToString("yyyy/MM/dd")

  28:                          ElseIf row.GetCell(i).CellType = ReadExcelUsedNPOI.HSSFCellType.FORMULA Then

  29:                              If IsNothing(formulaEvaluator) Then

  30:                                  formulaEvaluator = New HSSFFormulaEvaluator(Workbook)

  31:                              End If

  32:                              dr.Item(i) = formulaEvaluator.Evaluate(row.GetCell(i)).FormatAsString

  33:                          ElseIf row.GetCell(i).CellType = ReadExcelUsedNPOI.HSSFCellType.NUMERIC Then

  34:                              dr.Item(i) = row.GetCell(i).NumericCellValue

  35:                          Else

  36:                              dr.Item(i) = row.GetCell(i).ToString

  37:                          End If

  38:                      Next

  39:                      dt.Rows.Add(dr)

  40:                  End If

  41:              Next

  42:              Return dt

  43:          Catch ex As Exception

  44:              Throw ex

  45:          End Try

  46:      End Function

  47:   

  48:      Private Function CreateTableSchema(ByVal row As HSSFRow) As DataTable

  49:          Dim dt As New DataTable

  50:          Dim iCountCol As Integer = row.LastCellNum

  51:          Dim sColName As String

  52:          For i As Integer = row.FirstCellNum To iCountCol - 1

  53:              If IsNothing(row.GetCell(i)) Then

  54:                  sColName = "Column" & i.ToString

  55:              Else

  56:                  sColName = row.GetCell(i).ToString

  57:              End If

  58:              Dim dc As DataColumn = New DataColumn(sColName, System.Type.GetType("System.String"))

  59:              dt.Columns.Add(dc)

  60:          Next

  61:          Return dt

  62:      End Function

 

匯入結果：

--------
沒什麼特別的~
不過是一些筆記而已