Solved

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

Posted on 2013-05-25
1
1,105 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 500 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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
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…

630 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