16,554
社区成员
发帖
与我相关
我的任务
分享
''' <summary>
''' 将excel中的数据导入到DataTable中
''' </summary>
''' <param name="sheetName">excel工作薄sheet的名称</param>
''' <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
''' <returns>返回的DataTable</returns>
Public Function ExcelToDataTable(ByVal sheetName As String, ByVal isFirstRowColumn As Boolean) As DataTable
Dim sheet As ISheet = Nothing
Dim data As New DataTable()
Dim startRow As Integer = 0
Try
fs = New FileStream(fileName, FileMode.Open, FileAccess.Read)
If fileName.IndexOf(".xlsx") > 0 Then
' 2007版本
workbook = New XSSFWorkbook(fs)
ElseIf fileName.IndexOf(".xls") > 0 Then
' 2003版本
workbook = New HSSFWorkbook(fs)
End If
If sheetName IsNot Nothing Then
sheet = workbook.GetSheet(sheetName)
If sheet Is Nothing Then
'如果没有找到指定的sheetName对应的sheet,则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0)
End If
Else
sheet = workbook.GetSheetAt(0)
End If
If sheet IsNot Nothing Then
Dim firstRow As IRow = sheet.GetRow(0)
Dim cellCount As Integer = firstRow.LastCellNum
'一行最后一个cell的编号 即总的列数
If isFirstRowColumn Then
For i As Integer = firstRow.FirstCellNum To cellCount - 1
Dim cell As ICell = firstRow.GetCell(i)
If cell IsNot Nothing Then
Dim cellValue As String = cell.StringCellValue
If cellValue IsNot Nothing Then
Dim column As New DataColumn(cellValue)
data.Columns.Add(column)
End If
End If
Next
startRow = sheet.FirstRowNum + 1
Else
startRow = sheet.FirstRowNum
End If
'最后一列的标号
Dim rowCount As Integer = sheet.LastRowNum
For i As Integer = startRow To rowCount
Dim row As IRow = sheet.GetRow(i)
If row Is Nothing Then
Continue For
End If
'没有数据的行默认是null
Dim dataRow As DataRow = data.NewRow()
For j As Integer = row.FirstCellNum To cellCount - 1
If row.GetCell(j) IsNot Nothing Then
'同理,没有数据的单元格都默认是null
dataRow(j) = row.GetCell(j).ToString()
End If
Next
data.Rows.Add(dataRow)
Next
End If
Return data
Catch ex As Exception
'Console.WriteLine("Exception: " + ex.Message)
Return Nothing
End Try
End Function