[VB.NET]使用NPOI將Excel匯入(xls and xlsx 格式)範例

使用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