Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-05-25
1
Medium Priority
?
1,139 Views
Last Modified: 2013-05-28
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

0
Comment
Question by:brohjoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 9

Accepted Solution

by:
suvmitra earned 1500 total points
ID: 39197374
grid view export to excel provides you option to save / export the data in a new workbook. This is how it works.

If you want to write / append new data in excel file then you need to use .net stream writer class. Hope it helps.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

664 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question