使用NPOI將Excel匯入(xls and xlsx 格式)範例
1.首先必須到NPOI的官網下載2.0以上版本才有支援xlsx的匯入(http://npoi.codeplex.com/releases)
2.將下載回來的dll加入參考
Public Function ReadExcelToDatatable(ByRef page As Page, ByRef fileup As FileUpload) As System.Data.DataTable
Dim ds As New DataSet()
Dim DT As New System.Data.DataTable
Try
Dim savePath As String = page.MapPath("~/temp/")
If (fileup.HasFile) Then
Dim fileName As String = fileup.FileName
Dim extension As String = System.IO.Path.GetExtension(fileName)
Dim IDName As String = System.Guid.NewGuid().ToString() & extension
savePath = savePath & IDName
fileup.SaveAs(savePath)
'開啟要讀取的Excel檔案
Dim file As New FileStream(savePath, FileMode.Open)
'自動判斷xls或xlsx
Dim workbook As IWorkbook = WorkbookFactory.Create(file)
file.Close()
IO.File.Delete(savePath)
'為每個WorkSeeh建立出一個table
For sheetIndex As Integer = 0 To workbook.NumberOfSheets - 1
Dim sheet As ISheet = workbook.GetSheetAt(sheetIndex)
'建立一個新的table
Dim dtNew As DataTable = ds.Tables.Add(workbook.GetSheetName(sheetIndex))
Dim excelRow As IRow = sheet.GetRow(0)
Dim excelCell As ICell
Dim formulaEvaluator As HSSFFormulaEvaluator = Nothing
If excelRow IsNot Nothing Then
'由第一列取標題做為欄位名稱
For columnIndex As Integer = 0 To excelRow.LastCellNum - 1
excelCell = excelRow.GetCell(columnIndex)
If excelCell IsNot Nothing Then
Dim dc As New DataColumn(excelCell.ToString())
dtNew.Columns.Add(dc)
End If
Next
Dim rowId As Integer = 0
Dim hasData As Boolean
'第一列以後為資料,一直讀到最後一行
While rowId <= sheet.LastRowNum - 1
Dim newRow As DataRow = dtNew.NewRow()
rowId += 1
hasData = False
'讀取所有column
For colIndex As Integer = 0 To dtNew.Columns.Count - 1
excelRow = sheet.GetRow(rowId)
If (excelRow Is Nothing) Then
Continue While
End If
excelCell = excelRow.GetCell(colIndex)
'若第一個 Cell 是 Nothing 就濾掉這筆
If (excelCell Is Nothing AndAlso colIndex = 0) Then
Continue While
End If
If (excelCell IsNot Nothing) Then
Dim strVal As String
If excelCell.CellType = NPOI.SS.UserModel.CellType.FORMULA Then
If formulaEvaluator Is Nothing Then
formulaEvaluator = New HSSFFormulaEvaluator(sheet, workbook)
End If
strVal = formulaEvaluator.Evaluate(excelCell).FormatAsString()
ElseIf excelCell.CellType = NPOI.SS.UserModel.CellType.NUMERIC _
AndAlso NPOI.SS.UserModel.DateUtil.IsValidExcelDate(excelCell.NumericCellValue) _
AndAlso NPOI.SS.UserModel.DateUtil.IsCellDateFormatted(excelCell) Then
strVal = excelCell.DateCellValue.ToString("yyyy/MM/dd")
ElseIf excelCell.CellType = NPOI.SS.UserModel.CellType.NUMERIC Then
strVal = excelCell.NumericCellValue
Else
strVal = excelCell.ToString()
End If
'若第一個 Cell 是空白值就濾掉這筆
If (colIndex = 0 AndAlso String.IsNullOrEmpty(strVal.Trim())) Then
Continue While
End If
newRow(dtNew.Columns(colIndex)) = strVal
hasData = True
End If
Next
If (hasData) Then
dtNew.Rows.Add(newRow)
End If
End While
End If
Next
DT = ds.Tables(0)
workbook = Nothing
Return DT
Else
GIUtil.showMsg(page, "訊息", "== 請先挑選檔案之後,再來上傳!")
End If
Catch ex As Exception
GIUtil.showMsg(page, "訊息", "== 匯入失敗!")
End Try
End Function