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.


I want to get these data into the datagrid.

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

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"
			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("'", "''"), _
		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("'", "''"), _
		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)
		Dim cmd As New OleDbCommand("Select * From [Sheet1$]", conn)
		Dim dr As OleDbDataReader = cmd.ExecuteReader()
		Dim dt As New DataTable()

		DataGridView1.DataSource = dt
	End Sub
End Class
commented: MyCollection.Add(AnotherCoolCode); +15
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.