Monday, September 21, 2020 :: Login  

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

 

 

Privacy Policy www.made4dotnet.com 2020

Map IP Address
Powered byIP2Location.com

   Terms Of Use   ::   Privacy Statement   ::   Copyright (c) 2020 www.made4dotnet.com - .NET Development