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.

2 Answers

0
Mary On Best Solutions

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
0
Mary On

DataTable.Merge cannot work without a Primary Key. I fooled around with DataTable.Load(IDataReader) but that must use Merge underneath. So, you can either add a Primary Key to the Excel worksheet (just sort of a row number column) and set the appropriate properties in code or manually loop through the second DataTable adding the records to the grid. (slow)

Here is a slightly different method of filling the grid with all the data. I don't have a large Excel worksheet to test. It might address the memory problems but I am afraid it will use the same methods underneath.

Private Sub FillFromExcel()
    Dim FileName As String = "Book1.xlsx" '"C:\Book1.xlsx"
    Dim dt As New DataTable
    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()
            dt.Load(cmd.ExecuteReader)
        End Using
    End Using
    DataGridView1.DataSource = dt
End Sub