Solved

Export VB.NET DataViewGrid data and append to existing Excel spreadsheet_Coding help

Posted on 2013-05-29
15
489 Views
Last Modified: 2013-06-06
Hello Experts,

I need some coding help appending data to an existing Excel spreadsheet.  My code will append data, but it deletes existing rows in the process.  So I guess it's not really appending at all, just replacing rows.

Here is the code.  Thanks in advance.

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

        Dim xlApp As New Application
        'create a new workbook
        Dim xlWb As Workbook = xlApp.ActiveWorkbook
        Dim xlWs As 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
                    Next
                Next



                'Add the header the first time through
                If rowcount = 2 Then
                    For i As Integer = 0 To dgv.Columns.Count - 1
                        .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
                    Next
                End If

                'append values to a previously populated Spreadsheet
                If rowcount > 2 Then
                    Dim fileName As String = "c:\excelRTM2.xlxs"
                    Dim sb As StringBuilder = New StringBuilder
                    Dim dvgTraceability As dvgTraceability

                    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
                            sb.Append(dvgTraceability.ToString)
                        Next
                    Next



                    Using writer As StreamWriter = New StreamWriter(fileName, True)
                        writer.Write(sb.ToString())


                    End Using
                End If




            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
  • 9
  • 6
15 Comments
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
this code is based off the DGV . . . it needs to be based off the spreadsheet.

     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
                    Next
                Next
===================================================================



Dim RowCount as Integer = FindRowCount(xlWs )

public function FindRowCount(ByVal  xlWs As Worksheet)

dim i as integer = 0

'find the first blank row.  
while   Not String.IsNullOrEmpty(xlWS.Cells(1, i).Value)
  i = i  +1
wend

return i


end Function
0
 

Author Comment

by:brohjoe
Comment Utility
I got errors with this solution.  The line
while   Not String.IsNullOrEmpty(xlWS.Cells(1, i).Value)

Open in new window

throws an error.  Here is the exception snapshot:


Snagit capture
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
try value.ToString()

essentially you want to loop through the rows and look at the column until the value is blank.

Are you using closedXML?

If so there's a LastRowUsed property that will get that for you automatically.
0
 

Author Comment

by:brohjoe
Comment Utility
No, I'm not using closedXML.

Still getting errors on that same line with the Value.ToString() added.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
set a breakpoint on that line.

Do you get the value?  Not sure what you're using so hard to diagnose.
0
 

Author Comment

by:brohjoe
Comment Utility
I can send the project if youd like.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
not going to do it for you . . . the general layout of the program is correct.  Loop through the rows looking at each value until you get the first blank, that's the row you should begin inserting on.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:brohjoe
Comment Utility
Here is the stacktrace:  

Stacktrace.pdf

Here are the imports:
Imports System.Web.Services.Protocols
Imports System.Reflection
Imports System.Xml.Serialization
Imports System.IO
Imports System.Runtime.InteropServices
Imports System.Linq
Imports Microsoft.Office.Interop.Excel
Imports TestWebService.My.Resources
Imports TestWebService.WS
Imports System.Text

Here is my hardware system information:
Computer.pdf
0
 

Author Comment

by:brohjoe
Comment Utility
I need the code that loops through the rows and finds the last open row then appends the data into Excel from the dataviewgrid.  If I knew how to do it, I wouldn't be asking the question in the first place.
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
try this:

dim i as integer = 1

'find the first blank row.  
while   Not String.IsNullOrEmpty(xlWS.Cells(1, i).Value)
  i = i  +1
wend

Just realized the row might be one based instead of 0 based.
0
 

Author Comment

by:brohjoe
Comment Utility
Even if it works, it only finds the first blank row correct?  What about inserting data into that blank row?
0
 
LVL 39

Expert Comment

by:Kyle Abrahams
Comment Utility
Your code works, you just need to set your initial row to the integer that gets returned from the function.
0
 

Accepted Solution

by:
brohjoe earned 0 total points
Comment Utility
Ok, I finally figured it out and got it working.  Here is the code that takes data from a dataGridView, populates it into Excel, then appends new data from the dataGridView into the new blank rows below the existing Excel data.

One of the things you have to be aware of is currently running processes.  You have to stop the Excel.exe processes, (which happens during garbage collection) because after running a few iterations of tests of the code, you end up with a lot of memory being used if not garbage collected.  I have code that takes care of that, but since I never got that far during debugging, I had to stop the processes manually.

The solution for me was how to identify the last row in the existing Excel sheet with data effectively and then how to insert data in the row below the last row (lastRow + 1).

 I hope this helps someone because I've been working on this for some time and a solution like this is not readily available online.  

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

        Dim xlApp As New Application
        'create a new workbook
        Dim xlWb As Workbook = xlApp.ActiveWorkbook
        Dim xlWs As Worksheet
        Dim lastRow As Long



        With xlApp
            .Visible = True
            'open workbook
            xlWb = .Workbooks.Open(path)

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

        With xlWs

            '~~> Check if there is any data in the sheet
            If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
                lastRow = .Cells.Find(What:="*", _
                              After:=.Range("A1"), _
                              LookAt:=XlLookAt.xlPart, _
                              LookIn:=XlFindLookIn.xlFormulas, _
                              SearchOrder:=XlSearchOrder.xlByRows, _
                              SearchDirection:=XlSearchDirection.xlPrevious, _
                              MatchCase:=False).Row
            Else
                lastRow = 1
            End If


            'Add the header the first time through
            If lastRow < 2 Then
                For i As Integer = 0 To dgv.Columns.Count - 1
                    .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
                Next
            End If

            'append values to a previously populated Spreadsheet
            If lastRow >= 2 Then
                Dim fileName As String = path.ToString()
                Dim dvgTraceability As dvgTraceability
                Dim curRow As Integer = lastRow + 1
                For rowlength As Integer = 0 To dgv.Rows.Count - 1
                    For columnlength As Integer = 0 To dgv.Columns.Count - 1
                        xlWs.Cells(curRow, columnlength + 1).Value =
                            dgv.Rows(rowlength).Cells(columnlength).Value




                    Next
                Next
            End If

        End With




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


        releaseObject(xlApp)
    End Sub

Open in new window

0
 

Author Closing Comment

by:brohjoe
Comment Utility
My solution was the only solution that worked.  I received very little assistance from the Expert and did not use his code snippet as it did not work for me and he did not explain how to populate the current row.
0
 

Author Comment

by:brohjoe
Comment Utility
Here is the final solution. I deleted the 'For Each' block.

 Public Sub ExportToExcel(ByVal dgv As DataGridView, ByVal path As String)
        Dim lastRow As Long
        Dim sPath As String = String.Empty
      

        Try
            
            If xlApp.Visible = False Then
                With xlApp
                    .Visible = True
                    'open workbook
                    xlWb = .Workbooks.Open(path)

                    'set it to the relavant sheet
                    xlWs = xlWb.ActiveSheet
                    xlWs.Name = "Sheet1"
                End With
                End If



               

                With xlWs
                    '~~> Check if there is any data in the sheet: If yes:
                    If xlApp.WorksheetFunction.CountA(.Cells) <> 0 Then
                        lastRow = .Cells.Find(What:="*", _
                                      After:=.Range("A1"), _
                                      LookAt:=XlLookAt.xlPart, _
                                      LookIn:=XlFindLookIn.xlFormulas, _
                                      SearchOrder:=XlSearchOrder.xlByRows, _
                                      SearchDirection:=XlSearchDirection.xlPrevious, _
                                      MatchCase:=False).Row
                    Else
                        lastRow = 0


                    '~~>If no data in the sheet, then:

                        'Add the header the first time through
                        For i As Integer = 0 To dgv.Columns.Count - 1
                            .Cells(1, i + 1).Value = dgv.Columns(i).HeaderText
                            .Cells(1, i + 1).font.bold = True
                            xlApp.ActiveWindow.SplitRow = 1
                            xlApp.ActiveWindow.FreezePanes = True
                        Next

                        '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

                    End If
                    'reset the progressbar
                    ProgressBar1.Value = 0
                    ProgressBar1.Visible = False
                    MsgBox("Data successfully exported.", MsgBoxStyle.Information)

                End With
            Catch ex As Exception
                MsgBox(ex.ToString())
            End Try




            'append values to a previously populated Spreadsheet
            If lastRow >= 2 Then
                Try
                    For rowCount As Integer = 0 To dgv.RowCount - 1
                        For columnCount As Integer = 0 To dgv.ColumnCount - 1
                            xlWs.Cells((rowCount + (lastRow + 1)), columnCount + 1).Value =
                            dgv.Rows(rowCount).Cells(columnCount).Value
                        Next columnCount

                    Next rowCount

                Catch ex As Exception
                    MsgBox(ex.ToString())
                End Try

        End If



    End Sub

Open in new window

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now