r/learnprogramming•
betogm

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
Loading comments…