透過NPOI匯入資料(VB.NET)
透過NPOI匯入/匯出Excel資料，是蠻常用的手法，不過網路上的範例都是 C#，而且我沒有查到處理Excel日期型別的匯入問題，偏偏我這邊的需求是一定會用到滴...
下面是要匯入的Excel簡單版：
這裡面要處理的有三種狀況：
- 日期，要正確匯入，不能變成純數字，且格式要符合 yyyy/MM/dd。
- 子編號要正確顯示，不能變成科學記號。
- 公式欄位，只顯示公式的結果，而不是公式本身。
範例程式碼摘要如下：(主要是第 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
因為這篇文章主要是在說明匯入的日期欄位判斷，程式碼沒有貼的很完整，這裡補充說明一下。
1. ReadExcelUsedNPOI 其實就是上述這兩個方法所屬的類別。所以各位請自行調整為你們自己的類別名稱。
2. CellType 那邊是用一個 Enum，我漏貼到程式碼中了，以下補上：
Public Enum HSSFCellType
Unknown = -1
NUMERIC = 0
[STRING] = 1
FORMULA = 2
BLANK = 3
[BOOLEAN] = 4
[ERROR] = 5
End Enum3. Imports 的部份：
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.Use匯入結果：
--------
沒什麼特別的~
不過是一些筆記而已
