引用來源
Imports NPOI.HSSF.UserModel
Imports NPOI.SS.UserModel
Imports NPOI.XSSF.UserModel
''' <summary>
''' 获取EXCEL表格真正的值
''' </summary>
''' <param name="cell"></param>
''' <returns>不同的类型处理后的值</returns>
''' <remarks></remarks>
Private Shared Function GetCellValue(cell As ICell) As String
If cell Is Nothing Then
Return String.Empty
End If
Select Case cell.CellType
Case CellType.Blank
'空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
Return String.Empty
Case CellType.[Boolean]
'bool类型
Return cell.BooleanCellValue.ToString()
Case CellType.[Error]
Return cell.ErrorCellValue.ToString()
Case CellType.Numeric
'数字类型
If HSSFDateUtil.IsCellDateFormatted(cell) Then
'日期类型
Return cell.DateCellValue.ToString()
Else
'其它数字
Return cell.NumericCellValue.ToString()
End If
Case CellType.Unknown
'无法识别类型
'默认类型
Return cell.ToString()
'
Case CellType.[String]
'string 类型
Return cell.StringCellValue
Case CellType.Formula
'带公式类型
Try
Dim e As New HSSFFormulaEvaluator(cell.Sheet.Workbook)
e.EvaluateInCell(cell)
Return cell.ToString()
Catch
Return cell.NumericCellValue.ToString()
End Try
End Select
End Function
''' <summary>
''' 获取excel内容
''' </summary>
''' <param name="filePath">excel文件路径</param>
''' <returns></returns>
Public Shared Function ImportExcel(filePath As String) As DataTable
Dim dt As New DataTable()
Using fsRead As FileStream = System.IO.File.OpenRead(filePath)
Dim wk As IWorkbook = Nothing
'获取后缀名
Dim extension As String = filePath.Substring(filePath.LastIndexOf(".")).ToString().ToLower()
'判断是否是excel文件
If extension = ".xlsx" OrElse extension = ".xls" Then
'判断excel的版本
If extension = ".xlsx" Then
wk = New XSSFWorkbook(fsRead)
Else
wk = New HSSFWorkbook(fsRead)
End If
'获取第一个sheet
Dim sheet As ISheet = wk.GetSheetAt(0)
'获取第一行
Dim headrow As IRow = sheet.GetRow(0)
'创建列
For i As Integer = headrow.FirstCellNum To headrow.Cells.Count - 1
Dim datacolum = New DataColumn(headrow.GetCell(i).StringCellValue)
'Dim datacolum As New DataColumn("F" + CStr(i + 1))
dt.Columns.Add(datacolum)
Next
'读取每行,从第二行起
For r As Integer = 1 To sheet.LastRowNum
Dim result As Boolean = False
Dim dr As DataRow = dt.NewRow()
'获取当前行
Dim row As IRow = sheet.GetRow(r)
'读取每列
For j As Integer = 0 To row.Cells.Count - 1
Dim cell As ICell = row.GetCell(j)
'一个单元格
dr(j) = GetCellValue(cell)
'获取单元格的值
'全为空则不取
If dr(j).ToString() <> "" Then
result = True
End If
Next
If result = True Then
'把每行追加到DataTable
dt.Rows.Add(dr)
End If
Next
End If
End Using
Return dt
End Function
REM 调用
Dim 文件名 = "abc.xlsx"
Dim dt As New DataTable()
If File.Exists(文件名) Then
dt = ImportExcel(文件名)
Grid显示框.DataSource = dt
Else
MsgBox(文件名 & " 不存在,请检查!!", MsgBoxStyle.Critical)
End If
