• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Read Data From Excel Spread Sheet To Datagridview. This Is Simple Code.

Read Data From Excel Spread Sheet To Datagridview. This Is Simple Code.

Read Data From Excel Spread Sheet To Datagridview. This Is Simple Code.

API Declarations


.Data
.Data.Odbc



Rate Read Data From Excel Spread Sheet To Datagridview. This Is Simple Code.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

    'create odbc connection object with WithEvents Feature
    Private WithEvents con As Odbc.OdbcConnection
    'Create odbc command object
    Private com As Odbc.OdbcCommand
    'Create odbc datareader object
    Private rdr As Odbc.OdbcDataReader

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Create the odbc connection object instance and set the connectionstring
        ' ------ here I created DSN. 
        con = New Odbc.OdbcConnection("Dsn=MyTestWorkBook")
        'Open the connection object
        con.Open()

        'Create the odbc command object instance
        com = New Odbc.OdbcCommand()
        With com
            'Set the active connection to the command
            .Connection = con
            'Set the command type
            .CommandType = CommandType.Text
            'Set the SQL statement
            .CommandText = "SELECT * FROM [Employees$]"
            'set the datareader object
            rdr = .ExecuteReader()
        End With

        'Clear the datagridview control before populate
        DataGridView1.Rows.Clear()

        'Loop the data until finds EOF
        While rdr.Read()
            With DataGridView1
                'Create the Empty Row
                .Rows.Add()
                'Set the Value for first coloumn
                DataGridView1.Rows((DataGridView1.Rows.Count - 1)).Cells(0).Value = rdr.GetValue(0).ToString()
                'Set the Value for second coloumn
                DataGridView1.Rows((DataGridView1.Rows.Count - 1)).Cells(1).Value = rdr.GetValue(1).ToString()
                'Set the Value for third coloumn
                DataGridView1.Rows((DataGridView1.Rows.Count - 1)).Cells(2).Value = rdr.GetValue(2).ToString()
            End With
        End While
        'After execute close the datareader object
        rdr.Close()

        'Cancel and Dispose the command object
        com.Cancel()
        com.Dispose()

        'Close and Dispose the connection object
        con.Close()
        con.Dispose()

        'After done everything, display the message box to the user end
        MessageBox.Show("Done")

    End Sub
    Private Sub con_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles con.StateChange

        'if connection object change the event, user can get capture from this code.
        Me.Text = "Read Excel Sheet :- " + e.CurrentState.ToString()

    End Sub

End Class