Data Import/Export – Read Excel Sheet to DataTable
Reads an Excel worksheet into a DataTable, starting from the specified cell.
VB
Public Function xlReadExcelSheetToDataTable(sheet As Worksheet,
Optional startCellAddress As String = "A1") As Data.DataTable
' Get the starting cell
Dim startCell As Range = sheet.Range(startCellAddress)
Dim startRow As Integer = startCell.Row
Dim startCol As Integer = startCell.Column
' Try to detect the used range from the starting cell
Dim lastRow As Integer = sheet.Cells(sheet.Rows.Count, startCol).End(XlDirection.xlUp).Row
Dim lastCol As Integer = sheet.Cells(startRow, sheet.Columns.Count).End(XlDirection.xlToLeft).Column
' Create the DataTable to return
Dim table As New Data.DataTable()
' Read header
For col = startCol To lastCol
Dim headerValue As Object = sheet.Cells(startRow, col).Value
If headerValue Is Nothing Then
table.Columns.Add("Column" & (col - startCol + 1).ToString())
Else
table.Columns.Add(headerValue.ToString())
End If
Next
' Read data
For row = startRow + 1 To lastRow
Dim newRow As DataRow = table.NewRow()
For col = startCol To lastCol
newRow(col - startCol) = sheet.Cells(row, col).Value
Next
table.Rows.Add(newRow)
Next
Return table
End Function