brohjoe
asked on
Export VB.NET DataViewGrid data and append to existing Excel spreadsheet_Coding help
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.
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
ASKER
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.
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.
ASKER
No, I'm not using closedXML.
Still getting errors on that same line with the Value.ToString() added.
Still getting errors on that same line with the Value.ToString() added.
set a breakpoint on that line.
Do you get the value? Not sure what you're using so hard to diagnose.
Do you get the value? Not sure what you're using so hard to diagnose.
ASKER
I can send the project if youd like.
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.
ASKER
Here is the stacktrace:
Stacktrace.pdf
Here are the imports:
Imports System.Web.Services.Protoc ols
Imports System.Reflection
Imports System.Xml.Serialization
Imports System.IO
Imports System.Runtime.InteropServ ices
Imports System.Linq
Imports Microsoft.Office.Interop.E xcel
Imports TestWebService.My.Resource s
Imports TestWebService.WS
Imports System.Text
Here is my hardware system information:
Computer.pdf
Stacktrace.pdf
Here are the imports:
Imports System.Web.Services.Protoc
Imports System.Reflection
Imports System.Xml.Serialization
Imports System.IO
Imports System.Runtime.InteropServ
Imports System.Linq
Imports Microsoft.Office.Interop.E
Imports TestWebService.My.Resource
Imports TestWebService.WS
Imports System.Text
Here is my hardware system information:
Computer.pdf
ASKER
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.
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.
dim i as integer = 1
'find the first blank row.
while Not String.IsNullOrEmpty(xlWS.
i = i +1
wend
Just realized the row might be one based instead of 0 based.
ASKER
Even if it works, it only finds the first blank row correct? What about inserting data into that blank row?
Your code works, you just need to set your initial row to the integer that gets returned from the function.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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
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.
i = i +1
wend
return i
end Function