Data Import/Export – Convert ListObject to DataTable
Converts an Excel.ListObject (Excel table) into a DataTable.
VB
Public Function xlConvertListObject2Table(ListObject As ListObject) As System.Data.DataTable
If ListObject Is Nothing Then Return Nothing
Dim dt As New Data.DataTable
Dim rHeaders As Excel.Range
Dim rBody As Excel.Range
'Set Columns
rHeaders = ListObject.HeaderRowRange
For Each cCell As Range In rHeaders
dt.Columns.Add(cCell.Value)
Next
'Set Rows
rBody = ListObject.DataBodyRange
Dim dataRow As Data.DataRow = dt.NewRow()
Dim iColIndex As Integer
Try
For Each row As Excel.ListRow In ListObject.ListRows
iColIndex = 0
For Each cCell As Range In row.Range
If cCell.Value IsNot Nothing Then
dataRow(iColIndex) = IIf(String.IsNullOrEmpty(cCell.Value), ".", cCell.Value.ToString) : iColIndex += 1
Else
dataRow(iColIndex) = String.Empty
End If
Next
dt.Rows.Add(dataRow)
dataRow = dt.NewRow()
Next
Return dt
Catch ex As Exception
Dim szFunctionName As String = New Diagnostics.StackTrace().GetFrame(0).GetMethod().Name
'ShowMessageBox(szModuleName + "." + szFunctionName + vbLf + vbLf + ex.ToString, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Return Nothing
End Function