Read Data From Excel Sheet(VB.NET)

Read Data From Excel Sheet(VB.NET)

Version: VB 2005
Compatibility: VB 2005
Category: Miscellaneous

Get Data From Excel Sheet To Datagridview.

This Is Simple Code..

Declarations:
Imports System
Imports System.Data
Imports System.Data.Odbc


Code:
Public Class FormTest

'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

Me.Cursor = Cursors.WaitCursor

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

'Loop the data until finds EOF
While rdr.Read()
If rdr.GetValue(0).ToString().Trim().Length <> 0 Then
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 If
End While
'After execute close the datareader object
rdr.Close()

Me.Cursor = Cursors.Default

'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