Solved

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

Posted on 2013-05-29
15
578 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 40

Expert Comment

by:Kyle Abrahams
ID: 39204717
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
ID: 39208525
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 40

Expert Comment

by:Kyle Abrahams
ID: 39208537
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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:brohjoe
ID: 39208641
No, I'm not using closedXML.

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

Expert Comment

by:Kyle Abrahams
ID: 39209054
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
ID: 39209588
I can send the project if youd like.
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39210821
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
 

Author Comment

by:brohjoe
ID: 39210930
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
ID: 39210936
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 40

Expert Comment

by:Kyle Abrahams
ID: 39211166
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
ID: 39211454
Even if it works, it only finds the first blank row correct?  What about inserting data into that blank row?
0
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 39211561
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
ID: 39212179
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
ID: 39221526
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
ID: 39227217
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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 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