none
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
            DataGridView1.Rows.Clear()
        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)
            Me.Close()
            If result = DialogResult.Yes Then
                Me.Close()
            End If
        End Sub
    
        Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
            DATAGRIDVIEW_TO_EXCEL((DataGridView1))
        End Sub
        Private Sub DATAGRIDVIEW_TO_EXCEL(ByVal DGV As DataGridView)
            Try
                Dim DTB = New DataTable, RWS As Integer, CLS As Integer
    
                For CLS = 0 To DGV.ColumnCount - 1 ' 
                    DTB.Columns.Add(DGV.Columns(CLS).Name.ToString)
                Next
    
                Dim DRW As DataRow
    
                For RWS = 0 To DGV.Rows.Count - 1 ' 
                    DRW = DTB.NewRow
    
                    For CLS = 0 To DGV.ColumnCount - 1
                        Try
                            DRW(DTB.Columns(CLS).ColumnName.ToString) = DGV.Rows(RWS).Cells(CLS).Value.ToString
                        Catch ex As Exception
    
                        End Try
                    Next
    
                    DTB.Rows.Add(DRW)
                Next
    
                DTB.AcceptChanges()
    
                Dim DST As New DataSet
                DST.Tables.Add(DTB)
                Dim FLE As String = "C:\Temp\ExcelTest\Test1.xml"
                DTB.WriteXml(FLE)
                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
                MsgBox(ex.ToString)
            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.SelectWorksheet(pSheetName) 
            doc.ImportDataTable(1, SLConvert.ToColumnIndex("A"), pDataTable, pColumnHeaders) 
            doc.SaveAs(pFileName) 
        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
    Moderator
  • 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
    Moderator