Visual Baisc DataGridViewExportToExcel RRS feed

  • Question

  • hi isaw that this community is very positive and helpful so iwant to ask a question ive been searching for his answer for three days iam working on a program that saves data input in textbox/combo box to datagridview then exports it to excel every thing is going well but when i export a second time without saving the data in the datagridview it exports to excel deleting the previous data in there and replaces it with the newer one ireally want to solve this problem this is the code that iam using 

     Private Sub ClearRES_Click(sender As Object, e As EventArgs) Handles ClearRES.Click
        End Sub
        Private Sub btnFindDes_Click(sender As Object, e As EventArgs) Handles btnFindDes.Click
            Dim Pct As String = (txtPartNo.Text)
            If (Pct = "11 WATER-P NSL-LT60-11-XX-DIF") Then
                txtDescription.Text = "LED TUBE 11W,60CM & WAP FITTING"
            End If
        End Sub
        Private Sub BtnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
            Dim result = MessageBox.Show(" Are you sure you want to quit", "Are you sure?", MessageBoxButtons.YesNoCancel)
            If result = DialogResult.Yes Then
            End If
        End Sub
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        End Sub
        Private Sub DATAGRIDVIEW_TO_EXCEL(ByVal DGV As DataGridView)
                Dim DTB = New DataTable, RWS As Integer, CLS As Integer
                For CLS = 0 To DGV.ColumnCount - 1 ' 
                Dim DRW As DataRow
                For RWS = 0 To DGV.Rows.Count - 1 ' 
                    DRW = DTB.NewRow
                    For CLS = 0 To DGV.ColumnCount - 1
                            DRW(DTB.Columns(CLS).ColumnName.ToString) = DGV.Rows(RWS).Cells(CLS).Value.ToString
                        Catch ex As Exception
                        End Try
                Dim DST As New DataSet
                Dim FLE As String = "C:\Temp\ExcelTest\Test1.xml"
                Dim EXL As String = "C:\Program Files\WindowsApps\Microsoft.Office.Desktop.Excel_16030.10228.20104.0_x86__8wekyb3d8bbwe\Office16" E
                Shell(Chr(34) & EXL & Chr(34) & " " & Chr(34) & FLE & Chr(34), vbNormalFocus)
            Catch ex As Exception
            End Try
        End Sub

    thank you

    • Edited by OmarFay Thursday, July 19, 2018 9:23 AM
    Thursday, July 19, 2018 9:22 AM

All replies

  • ignore the e in the 

    Dim EXL As String = "C:\Program Files\WindowsApps\Microsoft.Office.Desktop.Excel_16030.10228.20104.0_x86__8wekyb3d8bbwe\Office16" E

    Thursday, July 19, 2018 9:26 AM
  • You can use SpreadSheetLight to do this. I have a code sample, following this link for an overview then if interested there is a link to the source code. SpreadSheetLight is installed via Nuget here.

    The key is a method, ImportDataTable, first time pass 1 for RowIndex, before appending for next import use SLWorkSheetStatistics to get the last used row via EndRowIndex (this is all in the code sample), increment EndRowIndex by one an use if for RowIndex for the next import. Note IncludeHeader is optional which will include the DataColumn names as the first row for the import.

    This is a sample for the first import

    Public Sub SimpleExportRaw( 
        ByVal pFileName As String, 
        ByVal pSheetName As String, 
        ByVal pDataTable As DataTable, 
        ByVal pColumnHeaders As Boolean) 
        Using doc As New SLDocument() 
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders) 
        End Using 
    End Sub 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, July 19, 2018 11:37 AM
  • Well, the code you are using just recreates the workbook so you'll need to use a method of appending the data as Karen mentions.  You can use a 3rd party assembly or the MS provided Office.Interop assembly for Excel.  Either way you will need to open the existing workbook, find the last used row, and then insert the new data.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Thursday, July 19, 2018 12:29 PM