Excel to DataGridView with OleDbConnection using two DataAdapter

Question

1- Create C:\Book1.xlsx file into C disk.

2- Ensure that you have one sheet in the C:\Book1.xlsx file named Sheet1.

3- Fill Sheet1 cells from A1 cell to E20 cell with some data.

4- Close C:\Book1.xlsx file.

5- Put one DataGridView into the Form1 and named it as DataGridView1.

6- Run code in order to see if you are able to get excel data to DataGridView correctly.

As you can see I have used two OleDbDataAdapters in order to get excel data to DataGridView.

I have prefer to use two OleDbDataAdapters because I come across out of memory exception if excel data is so big.

If you examine my code you will see that myDataAdapter1 gets excel data from A1 cell to E10 cell. and myDataAdapter2 supposed to get excel data from A11 cell to E20 cell.

If you examine my code you will see that I tried to merge two DataTables and bind to DataGridView1.DataSource with no success.

Please correct my codes and show me how to merge two DataTables and bind to DataGridView1.DataSource?

I want to get excel data from A1 cell to E20 cell and put the DataGridView1 by using two DataAdapters and two DataTables.

If I use one data adapter then my app crashes with big data.

So I try to use two data adapters and two data tables.

Answer 1

The grid must first add columns to accomadate the data. Then add the rows in a While loop. I only have 3 columns in the test data from Excel.

Private Sub PrepareGrid()
    DataGridView1.Columns.Add("col1", "Column 1")
    DataGridView1.Columns.Add("col2", "Column 2")
    DataGridView1.Columns.Add("col3", "Column 3")
End Sub

Private Sub FillFromExcel()
    Dim FileName As String = "Book1.xlsx" '"C:\Book1.xlsx"
    Using cn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & FileName & ";" & "Extended Properties=""Excel 12.0 Xml;HDR=NO;IMEX=1"";")
        Using cmd As New OleDbCommand("SELECT * FROM [Sheet1$];", cn)
            cn.Open()
            Using reader = cmd.ExecuteReader
                While reader.Read
                    DataGridView1.Rows.Add(reader(0), reader(1), reader(2))
                End While
            End Using
        End Using
    End Using
End Sub

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    PrepareGrid()
    FillFromExcel()
End Sub