Click here to Skip to main content
14,920,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm new in vb.net . i have to import excel data to datagridview .I've tried but the problem is every time the header of datagridview is need to change based on columns in excel. But i want to import first row of excel as header in datagridview and except first row of excel other rows in datagridview rows.

What I have tried:

VB
Sub LoadData()        
        Dim rowcount As Integer = 0
        For xlRow = 2 To xlRange.Rows.Count
            If xlRange.Cells(xlRow, 1).text <> String.Empty And xlRange.Cells(xlRow, 2).text <> String.Empty And xlRange.Cells(xlRow, 3).text <> String.Empty And xlRange.Cells(xlRow, 4).text <> String.Empty Then
                rowcount += 1
                DataGridView1.Rows.Add(rowcount, xlRange.Cells(xlRow, 1).text, xlRange.Cells(xlRow, 2).text, xlRange.Cells(xlRow, 3).text, xlRange.Cells(xlRow, 4).text)
                
            End If
            Me.Refresh()
        Next
        
        xlWorkbook.Close()
        xlApp.Quit()
    End Sub
Posted
Updated 3-May-21 23:43pm
v3

1 solution

The simplest way is to use ADO.NET:

VB.NET
Dim sFileName As String = "FullFileNameHere"
	Dim sConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0;HDR=YES';", sFileName)
	Dim dt As DataTable = New DataTable()
	
	'1. create connection
	Using connection AS OleDbConnection = New OleDbConnection(sConStr)
		Dim sql As String = "SELECT * FROM [Sheet1$] WHERE TextColumn Like ?;"
		connection.Open()
		'2. create command
		Using command As OleDbCommand= New OleDbCommand(sql, connection)
			command.Parameters.Add(New OleDbParameter() With {.OleDbType = OleDbType.VarChar, .Value="%SomeValue%"})
			'3. read data
			Dim reader As OleDbDataReader = command.ExecuteReader()
			'4. load data into datatable
			dt.Load(reader)
		End Using
	End Using
'now, bind data
DataGridView1.DataSource = dt
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900