Missing Data when Importing xlsx file to DataGridView (VB 2015)
Hello everyone
I am making a Visual Basic program and one of its vital functions is to display contents of an excel file to a DataGridView.
Everything works but there is something that bothers me. The first column is missing and that is the one that contains the most important piece of information for the whole program.
I have tried
IMEX = Yes; HDR = Yes
But it didn't work.
Can you please help me? Thanks!
Code:
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim openDialog As New OpenFileDialog()
Dim filename As String = ""
Dim sheetName As String = ""
Dim i As Integer
openDialog.Title = "Choose your file"
openDialog.Filter = "Excel 1997-2003 (*.xls)|*.xls|Excel 2007 On (*.xlsx)|*.xlsx"
If openDialog.ShowDialog() = DialogResult.Cancel Then
Exit Sub
End If
filename = openDialog.FileName
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + filename + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=No;TypeGuessRows=0;ImportMixedTypes=Text""")
Using cm As New OleDbCommand()
cm.Connection = con
con.Open()
Dim dtExcelSchema As DataTable = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
If dtExcelSchema.Rows.Count = 0 Then
MsgBox("No hay datos")
Exit Sub
End If
sheetName = dtExcelSchema.Rows(0)("TABLE_NAME").ToString()
con.Close()
End Using
End Using
'Read Data from the First Sheet.
Using con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + filename + ";Extended Properties=""Excel 12.0;IMEX=1;HDR=No;TypeGuessRows=0;ImportMixedTypes=Text""")
Using cm As New OleDbCommand()
Using oda As New OleDbDataAdapter()
Dim dt As New DataTable()
cm.CommandText = "SELECT * FROM [" & sheetName & "]"
cm.Connection = con
con.Open()
oda.SelectCommand = cm
oda.Fill(dt)
con.Close()
'Populate DataGridView.
DataGridView1.DataSource = dt
End Using
End Using
End Using
End Sub
Posted Feb 25, 2016, 1:41 AM UTC
10