I want to import my Excel .xlsx file into a datatable in vb.net in order to process the data later. My Excel file called Data.xlsx looks like this:

and tried to use the code given by Ciarán here:

Am I missing here something or is my approach totally wrong?

CodePudding user response：

This

While oleExcelReader.Read
End While

will let you process each row inside the loop. This is good if you want to read and handle each field for each row individually, e.g. with oleExcelReader.GetString(0) etc.

If you just want to read the entire table, one way is:

...
dtTablesList.Clear()
dtTablesList.Dispose()

Dim dt As DataTable

If sSheetName <> "" Then
    oleExcelCommand = oleExcelConnection.CreateCommand()
    oleExcelCommand.CommandText = "Select * From [" & sSheetName & "]"
    oleExcelCommand.CommandType = CommandType.Text
    Using da As New OleDb.OleDbDataAdapter(oleExcelCommand)
        dt = New DataTable
        da.Fill(dt)
    End Using
End If

oleExcelConnection.Close()