Excel file into DataTable
dt As New System.Data.DataTable()
Dim dr As DataRow
Dim dtreturn As New System.Data.DataTable()
Dim oExcel As Excel.Application
Dim oWorkBook As Excel.Workbook
Dim oWorkSheet As Excel.Worksheet
Dim oExcel = New Excel.Application()
oWorkBook = oExcel.Workbooks.Open(varExcelFileName, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,Missing.Value, Missing.Value, Missing.Value, Missing.Value)
oWorkSheet = DirectCast(oWorkBook.Worksheets(1), Excel.Worksheet)
Dim r As Integer
Dim c As Integer
Dim intRows As Integer
Dim intCols As Integer
Dim excelCell As Excel.Range = oWorkSheet.UsedRange
Dim values As Object(,) = DirectCast(excelCell.Value2, Object(,)) intRows = values.GetLength(0)
If intRows <> 0 Then
intCols = values.GetLength(1)
If intCols <> 0 Then
For c = 1 To intCols
dt.Columns.Add(New DataColumn(DirectCast(values(1, c), String)))
Next
For r = 2 To intRows
dr = dt.NewRow()
For c = 1 To intCols
dr(DirectCast(values(1, c), String)) = values(r, c)
Next
dt.Rows.Add(dr)
Next
End If
End If
dtreturn = dt
dt = Nothing
oWorkSheet = Nothing
oWorkBook.Close(Missing.Value, Missing.Value, Missing.Value)
oWorkBook = Nothing
oExcel.Quit()
oExcel = Nothing