VB.NET SQL Populated datagrid to Excel template

Question

1.00/5 (1 vote)

See more:

I’m a newbie and I have a problem in hand. I have four datagrids in my application which gets populated by a sql command. The datagrids are

Datagridview1, Datagridview2, Datagridview3 and Datagridview4.

I have assigned a button to transfer these to an excel template which is where I’m struggling. Basically when I click the transfer button I want the records displayed on my datagrid to go to a specific location in my excel template. For example

My excel sheet name is LossType.xlsx and tabname in excel is Type1

Datagridview1 should start populating tabname Type1 from B3:G10 Datagridview2 should start populating same tabname Type1 but from B13:G20 Datagridview3 should start populating same tabname Type1 but from B26:G40 Datagridview4 should start populating same tabname Type1 but from B60:G80

Sorry for asking so many things but I have no idea how to get this done. Any help would be greatly appreciated.

Thanks Satish

Posted 22-Jul-14 19:42pm

Satish Narayanan

Add a Solution

Comments

Maciej Los 23-Jul-14 1:54am

What it is: tabname? Do you mean Sheet?

Satish Narayanan 23-Jul-14 2:45am

Yes ..Sorry for the confusion.

nilesh sawardekar 23-Jul-14 2:03am

Are you sure about, there will be only 8 rows in all cases?

Maciej Los 23-Jul-14 2:40am

Good question! My virtual 5!

Satish Narayanan 23-Jul-14 2:45am

It can Grow..Sorry

2 solutions

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)

nilesh sawardekar · Accepted Answer · 2014-07-22T20:31:00

VB

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New Microsoft.Office.Interop.Excel.Application
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")


For i = 0 To 7
    For j = 0 To 5
            xlWorkSheet.Cells(i + 3, j + 2) = datagridview1(j, i).Value.ToString()
    Next
Next

For i = 0 To 7
    For j = 0 To 5
            xlWorkSheet.Cells(i + 13, j + 2) = datagridview2(j, i).Value.ToString()
    Next
Next

For i = 0 To 14
    For j = 0 To 5
            xlWorkSheet.Cells(i + 13, j + 2) = datagridview3(j, i).Value.ToString()
    Next
Next

For i = 0 To 20
    For j = 0 To 5
            xlWorkSheet.Cells(i + 60, j + 2) = datagridview4(j, i).Value.ToString()
    Next
Next

xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

Dim res As MsgBoxResult
res = MsgBox("Process completed, Would you like to open file?", MsgBoxStyle.YesNo)
If (res = MsgBoxResult.Yes) Then
    Process.Start("d:\vbexcel.xlsx")
End If

Maciej Los · Accepted Answer · 2014-07-22T21:02:00

Solution 2

You can use Interop[^] to create new instance of MS Excel, open existing file and save data from DataGridViews into it. By saying "save data", i mean: loop through the collection of rows and columns in each grid.

Pseudo code:

VB

'DataGridView1
j = 3
for r=0 to DataGridView1.Rows.Count -1
    for c=0 to DataGridView1.Rows(r).Columns.Count -1
        SheetType1.Cells(j, c) = DataGridView1.Cell(r,c).Value
    next
    j += 1
next
'DataGridView2
j = 13
for r=0 to DataGridView2.Rows.Count -1
    for c=0 to DataGridView2.Rows(r).Columns.Count -1
        SheetType1.Cells(j, c) = DataGridView2.Cell(r,c).Value
    next
    j += 1
next
'and so on....

Note:
As you can see, some piece of code is repeated. There are two changes: DataGridView (respectively DataGridView1 to DataGridView4) on which we operate and starting row in Sheet (j). In that case it's strongly recommended to separate this code into procedure or function.

I suggest to read this: How to automate Microsoft Excel from Visual Basic .NET[^]

Try! Come back here when you get stuck and ask detailed question.

Posted 22-Jul-14 21:02pm

Maciej Los

Updated 22-Jul-14 21:05pm

v2

Comments

Satish Narayanan 1-Aug-14 2:09am

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.

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

VB.NET SQL Populated datagrid to Excel template

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us