Link to home
Create AccountLog in
Avatar of brohjoe
brohjoeFlag for United States of America

asked on

Export VB.NET DataViewGrid data and append to existing Excel spreadsheet

Hello again Experts,

I'm attempting to export data from a VB.NET dataViewGrid and export this data to Excel.  I'm able to export the contents of the dvg just fine, but appending the data to existing data in the Excel spreadsheet has proven to be a bit of a challenge.  The code examples I've seen during my internet search have not proven to be correct so far.

Here is what I have.   Thanks in advance for your help.

  Public Sub ExportToExcel(ByVal dgv As DataGridView, ByVal path As String)

        Dim xlApp As New Excel.Application
        'create a new workbook
        Dim xlWb As Excel.Workbook = xlApp.ActiveWorkbook
        Dim xlWs As Excel.Worksheet
        Dim lastRow As Long
        Dim lastCol As Long
       
       
        'Check for existing data
        With xlApp
            .Visible = True
            'open workbook
            xlWb = .Workbooks.Open(path)

            'set it to the relavant sheet
            xlWs = xlWb.ActiveSheet

            With xlWs
                Dim rowcount As Integer = 1

                For Each gridRow As DataGridViewRow In dgv.Rows
                        rowcount += 1
                    For i As Integer = 0 To dgv.Columns.Count - 1
                        'Add the header the first time through
                        If rowcount = 2 Then
                            .Cells(1, i + 1).Value = dgv.Columns(i).HeaderText
                            .Cells(1, i + 1).font.bold = True

                            'export the values to a blank Spreadsheet
                            For rowlength As Integer = 0 To dgv.Rows.Count - 1
                                For columnlength As Integer = 0 To dgv.Columns.Count - 1
                                    .Cells(rowlength + 2, columnlength + 1).Value = dgv.Rows(rowlength).Cells(columnlength).Value
                                Next
                            Next

                            'append values to a previously populated Spreadsheet
                            If rowcount > 2 Then
                                lastRow = xlWs.Cells.Find(What:="*", After:=xlApp.Range("A1"), SearchOrder:=Excel.XlSearchOrder.xlByColumns, _
                                SearchDirection:=Excel.XlSearchDirection.xlPrevious).Row
                                For rowlength As Integer = 0 To dgv.Rows.Count - 1
                                    For columnlength As Integer = 0 To dgv.Columns.Count - 1
                                        .Cells(lastRow, columnlength + 1).Value = dgv.Rows(rowlength).Cells(columnlength).Value
                                    Next
                                Next
                            End If
                        End If

                    Next



                Next

            End With



        End With


       
       
        'save the workbook as the path specified
        xlWb.SaveAs(path)
        

        releaseObject(xlApp)

    End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of suvmitra
suvmitra
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account