Join two worksheet Excel to Datagridview

[mipakteh]

I have data from two sheet in excel want combine.First sheet1 then followed by sheet2.

I use "UNION" to combine that but all data peel off after debug in Datagridview.

somebody can show me make data in datagridview organized.

sheet1 figure bellow;



sheet2 like bellow;




Result show in datagridview;




Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim _filename As String = "C:\Users\Uset\Documents\Book3.xlsx"

Dim _conn As String
_conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _filename & ";Extended Properties='Excel 12.0;IMEX=1;'"

Dim _connection As OleDbConnection = New OleDbConnection(_conn)
Dim da As OleDbDataAdapter = New OleDbDataAdapter()
Dim _command As OleDbCommand = New OleDbCommand()

_command.Connection = _connection
_command.CommandText = "SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet1$] Union SELECT [Nombor], [Date_Received], [Lab_ID] FROM [Sheet2$]"
da.SelectCommand = _command

Try
da.Fill(ds1, "Sheet1" & "Sheet2")
MessageBox.Show("The import is complete!")
Me.DataGridView1.DataSource = ds1
Me.DataGridView1.DataMember = "Sheet1" & "Sheet2"

Catch e1 As Exception
MessageBox.Show("Import Failed, correct Column name in the sheet!")
End Try

End Sub

Continue reading...