Click here to Skip to main content
15,901,666 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have the below code which will transfer the records from my datagrid to my excel spreadsheet.
Currently this code works for one datagrid to an excel sheet. Now I need to improve the below code so that it can work for multiple datagrids. I want help to extend this code so that I can pull the records from 3 data grids to the same excel sheet one below another.

VB
Dim excel As Microsoft.Office.Interop.Excel.Application
 
Try
excel = New Microsoft.Office.Interop.Excel.Application
excel.Workbooks.Open("C:\Satish\TestExcel\vbexcel.xlsx")
Dim i As Integer, j As Integer
Dim diff As Integer = 1
' if you want column header from dgv elese omit the block 
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For j = 0 To DataGridView1.ColumnCount - 1
excel.Worksheets(1).cells(1, j + 1) = DataGridView1.Columns(j).Name
Next
diff += 1
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
For i = 0 To DataGridView4.RowCount - 1
 
If DataGridView4.Rows(i).IsNewRow = False Then
For j = 0 To DataGridView4.ColumnCount - 1
 
excel.Worksheets(1).cells(i + diff, j + 1) = DataGridView4.Item(j, i).Value
Next
End If
Next
excel.Worksheets(1).select()
excel.ActiveWorkbook().Save()
excel.Workbooks.Close()
excel.Quit()
excel = Nothing
Catch ex As System.Runtime.InteropServices.COMException
MessageBox.Show("Error accessing Excel: " + ex.ToString())
Catch ex As Exception
MessageBox.Show("Error: " + ex.ToString())
End Try
Posted
Updated 2-Aug-14 2:01am
v2

1 solution

Hello Satish.

If your Datagrids are filled with similar Datasets, then just add another Iteration over your existing Excel filling.

Dim i As Integer, j As Integer, DatagridCycle as Integer

This will be then the counter for each Cycle of the Datagrids.

If needed, store the last Datagrid Index or add just a Counter for each Datagrid for Offset(s).


VB
Dim i As Integer, j As Integer, DatagridCycle as Integer


For DatagridCycle = 0 to 2 then


Dim diff As Integer = 1
' if you want column header from dgv elese omit the block
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For j = 0 To DataGridView1.ColumnCount - 1

    Select Case DatagridCycle ' 0 = 1. DGV ; 1 = 2.DGV ; 2 = 3. DGV 
    
    Case 0
       excel.Worksheets(1).cells(1, j + 1) = DataGridView1.Columns(j).Name
    Case 1
       excel.Worksheets(1).cells(1, j + 1) = DataGridView2.Columns(j).Name 'change DGV Control Name plz
    Case 2
       excel.Worksheets(1).cells(1, j + 1) = DataGridView3.Columns(j).Name 'change DGV Control Name plz
    
    End Select
    
Next
diff += 1  'Question? Read the Question a couple of lines later ;)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For i = 0 To DataGridView4.RowCount - 1

If DataGridView4.Rows(i).IsNewRow = False Then
For j = 0 To DataGridView4.ColumnCount - 1

excel.Worksheets(1).cells(i + diff, j + 1) = DataGridView4.Item(j, i).Value
Next


Next 'DataGridCycle Iteration END




Question? I asume You store in diff the next Column position?

If so, and you want to extend the Excel-Table later, you will need an entrypoint (Offset) where to restart.

You could store it inside another Variable or the most effective way is to use Excel API/Interop.

For that i would request inside the Target Table the next free cell from the Top, store then the index value inside your Application and use that as Offset.

excel.Worksheets(1).cells(1,offset+ j + 1) = DataGridView1.Columns(j).Name

I used that Years ago... But was tricky in the first times to get on with EXCEL Interop.

I used always a Representation of the Cell Positions inside my Application for later use.

I hope that will help.

BTW this is my first Quick Response. ;)
 
Share this answer
 
v2

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