Multiple datagrids to one excel sheet

Question

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 31-Jul-14 20:22pm

Satish Narayanan

Updated 2-Aug-14 2:01am

Nirav Prabtani

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Jaroslav Mitrovic · Answer 1 · 2014-08-02T01:38:00

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. ;)