Get information in a Exel sheet to a data grid

[kimbula...]

Hi all,

I need a big favor to carry out a task.

I want to get information in a exel sheet to a datagrid in VB.Net.

For example lets say Excel sheet contains following information.

ID,Name,Telephone

I want to get these data into the datagrid.

Can anyone suggest me a way to do this. Would be great help for me.
Thanx

[kvprajapati]

Take a look at - http://www.codeproject.com/KB/vb/Senthil_S__Software_Eng_.aspx

[sknake]

You can also use OleDb to display the data:

Imports System.IO
Imports System.Data.OleDb

Public Class frmExcel

	Private Shared Function GetExcelConnString(ByVal FileName As String, ByVal FirstRowContainsHeaders As Boolean)
		Dim sFirstRow As String
		If (FirstRowContainsHeaders) Then
			sFirstRow = "Yes"
		Else
			sFirstRow = "No"
		End If

		Dim ext As String = Path.GetExtension(FileName)
		If (String.Compare(ext, ".xls", True) = 0) Then
			Return String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source='{0}';Extended Properties=""Excel 8.0;HDR={1};""", _
				 FileName.Replace("'", "''"), _
				 sFirstRow)
		ElseIf (String.Compare(ext, ".xlsx", True) = 0) Then
			Return String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='{0}';Extended Properties=""Excel 12.0;HDR={1};""", _
				 FileName.Replace("'", "''"), _
					sFirstRow)
		End If
		Throw New Exception("Unknown File Type")
	End Function


	Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim fileName As String = "C:\data\Spreadsheet.xls"
		Dim connStr As String = GetExcelConnString(fileName, True)

		Dim conn As New OleDbConnection(connStr)
		conn.Open()
		Dim cmd As New OleDbCommand("Select * From [Sheet1$]", conn)
		Dim dr As OleDbDataReader = cmd.ExecuteReader()
		Dim dt As New DataTable()
		dt.Load(dr)
		cmd.Dispose()
		conn.Close()
		conn.Dispose()

		DataGridView1.DataSource = dt
	End Sub
End Class