How to compare remove duplicate row in cell datagridview visual basic studio 2015

[aguswibowo]

hey,all
i'm new in vb.net
i try some code when i googling about that, but has not succeeded
please anyone help me to find solution for this problem



if button compare on click in datagridview show unique row cell, duplicate row deleted

and can be saved in file excel



in this code i used

Option Explicit On
Imports System.IO
'Imports Microsoft.Office.Interop
'Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Dim excelLocation As String = "C:\hasiltest.xlsx"
    Dim MyConn As OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook


    Private Sub btnBrowse1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse1.Click
        Dim myFileDlogOri As New OpenFileDialog()
        myFileDlogOri.InitialDirectory = "c:\"
        'specifies what type of data files to look for
        myFileDlogOri.Filter = "All Files (*.*)|*.*" &
            "|Data Files (*.xlsx)|*.xlsx"
        'specifies which data type is focused on start up
        myFileDlogOri.FilterIndex = 2
        'Gets or sets a value indicating whether the dialog box restores the current directory before closing.
        myFileDlogOri.RestoreDirectory = True
        'seperates message outputs for files found or not found
        If myFileDlogOri.ShowDialog() =
            DialogResult.OK Then
            If Dir(myFileDlogOri.FileName) <> "" Then
                'MsgBox("File Exists: " & myFileDlogOri.FileName, MsgBoxStyle.Information)
            Else
                MsgBox("File Not Found", MsgBoxStyle.Critical)
            End If
        End If
        'Adds the file directory to the text box
        txtFile1.Text = myFileDlogOri.FileName
    End Sub

    Private Sub btnBrowse2_Click(sender As Object, e As EventArgs) Handles btnBrowse2.Click
        Dim myFileDlogCam As New OpenFileDialog()
        myFileDlogCam.InitialDirectory = "c:\"
        'specifies what type of data files to look for
        myFileDlogCam.Filter = "All Files (*.*)|*.*" & "|Data Files (*.xlsx)|*.xlsx"
        'specifies which data type is focused on start up
        myFileDlogCam.FilterIndex = 2
        'Gets or sets a value indicating whether the dialog box restores the current directory before closing.
        myFileDlogCam.RestoreDirectory = True
        'seperates message outputs for files found or not found
        If myFileDlogCam.ShowDialog() =
            DialogResult.OK Then
            If Dir(myFileDlogCam.FileName) <> "" Then
                'MsgBox("File Exists: " & myFileDlogCam.FileName, MsgBoxStyle.Information)
            Else
                MsgBox("File Not Found", MsgBoxStyle.Critical)
            End If
        End If
        'Adds the file directory to the text box
        txtFile2.Text = myFileDlogCam.FileName
    End Sub
    Private Sub btnView1_Click(sender As Object, e As EventArgs) Handles btnView1.Click
        Dim exConn As OleDbConnection
        Dim dt As DataSet
        Dim cmd As OleDbDataAdapter
        Dim sConn As String
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & "'C:\File1.xlsx';Extended Properties=Excel 12.0;"
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFile1.Text & ";Extended Properties=Excel 8.0;"
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFileOri.Text & ";Extended Properties=" & cmbExtFile.Text & ";"
        exConn = New System.Data.OleDb.OleDbConnection(sConn)
        cmd = New System.Data.OleDb.OleDbDataAdapter(
              "select * from [Sheet1$]", exConn)
        'cmd.TableMappings.Add("Table1", "Tabel O")
        dt = New System.Data.DataSet
        cmd.Fill(dt)
        dgvFile1.DataSource = dt.Tables(0)
        exConn.Close()
    End Sub

    Private Sub btnView2_Click(sender As Object, e As EventArgs) Handles btnView2.Click
        Dim exConn As OleDbConnection
        Dim dt As DataSet
        Dim cmd As OleDbDataAdapter
        Dim sConn As String
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & "'C:\File2.xlsx';Extended Properties=Excel 12.0;"
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFile2.Text & ";Extended Properties=Excel 8.0;"
        exConn = New System.Data.OleDb.OleDbConnection(sConn)
        cmd = New System.Data.OleDb.OleDbDataAdapter(
              "select * from [Sheet1$] [Sheet2$]", exConn)
        'cmd.TableMappings.Add("Table2", "Tabel C")
        dt = New System.Data.DataSet
        cmd.Fill(dt)
        dgvFile2.DataSource = dt.Tables(0)
        exConn.Close()
    End Sub
    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        End
    End Sub
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        Dim writer As TextWriter = New StreamWriter("C:\report.xls")
        For i As Integer = 0 To dgvCompare.Rows.Count - 2 Step +1
            For j As Integer = 0 To dgvCompare.Columns.Count - 1 Step +1
                writer.Write(dgvCompare.Rows(i).Cells(j).Value.ToString())
            Next
            writer.WriteLine("")
            'writer.WriteLine("---------------------------------------------")
        Next
        writer.Close()
        MessageBox.Show("Data Exported")
    End Sub


End Class

thank you

[Peter Swinkels]

hey,all
i'm new in vb.net
i try some code when i googling about that, but has not succeeded
please anyone help me to find solution for this problem



if button compare on click in datagridview show unique row cell, duplicate row deleted

and can be saved in file excel



in this code i used

Option Explicit On
Imports System.IO
'Imports Microsoft.Office.Interop
'Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Dim excelLocation As String = "C:\hasiltest.xlsx"
    Dim MyConn As OleDbConnection
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim tables As DataTableCollection
    Dim source1 As New BindingSource
    Dim APP As New Excel.Application
    Dim worksheet As Excel.Worksheet
    Dim workbook As Excel.Workbook


    Private Sub btnBrowse1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse1.Click
        Dim myFileDlogOri As New OpenFileDialog()
        myFileDlogOri.InitialDirectory = "c:\"
        'specifies what type of data files to look for
        myFileDlogOri.Filter = "All Files (*.*)|*.*" &
            "|Data Files (*.xlsx)|*.xlsx"
        'specifies which data type is focused on start up
        myFileDlogOri.FilterIndex = 2
        'Gets or sets a value indicating whether the dialog box restores the current directory before closing.
        myFileDlogOri.RestoreDirectory = True
        'seperates message outputs for files found or not found
        If myFileDlogOri.ShowDialog() =
            DialogResult.OK Then
            If Dir(myFileDlogOri.FileName) <> "" Then
                'MsgBox("File Exists: " & myFileDlogOri.FileName, MsgBoxStyle.Information)
            Else
                MsgBox("File Not Found", MsgBoxStyle.Critical)
            End If
        End If
        'Adds the file directory to the text box
        txtFile1.Text = myFileDlogOri.FileName
    End Sub

    Private Sub btnBrowse2_Click(sender As Object, e As EventArgs) Handles btnBrowse2.Click
        Dim myFileDlogCam As New OpenFileDialog()
        myFileDlogCam.InitialDirectory = "c:\"
        'specifies what type of data files to look for
        myFileDlogCam.Filter = "All Files (*.*)|*.*" & "|Data Files (*.xlsx)|*.xlsx"
        'specifies which data type is focused on start up
        myFileDlogCam.FilterIndex = 2
        'Gets or sets a value indicating whether the dialog box restores the current directory before closing.
        myFileDlogCam.RestoreDirectory = True
        'seperates message outputs for files found or not found
        If myFileDlogCam.ShowDialog() =
            DialogResult.OK Then
            If Dir(myFileDlogCam.FileName) <> "" Then
                'MsgBox("File Exists: " & myFileDlogCam.FileName, MsgBoxStyle.Information)
            Else
                MsgBox("File Not Found", MsgBoxStyle.Critical)
            End If
        End If
        'Adds the file directory to the text box
        txtFile2.Text = myFileDlogCam.FileName
    End Sub
    Private Sub btnView1_Click(sender As Object, e As EventArgs) Handles btnView1.Click
        Dim exConn As OleDbConnection
        Dim dt As DataSet
        Dim cmd As OleDbDataAdapter
        Dim sConn As String
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & "'C:\File1.xlsx';Extended Properties=Excel 12.0;"
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFile1.Text & ";Extended Properties=Excel 8.0;"
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFileOri.Text & ";Extended Properties=" & cmbExtFile.Text & ";"
        exConn = New System.Data.OleDb.OleDbConnection(sConn)
        cmd = New System.Data.OleDb.OleDbDataAdapter(
              "select * from [Sheet1$]", exConn)
        'cmd.TableMappings.Add("Table1", "Tabel O")
        dt = New System.Data.DataSet
        cmd.Fill(dt)
        dgvFile1.DataSource = dt.Tables(0)
        exConn.Close()
    End Sub

    Private Sub btnView2_Click(sender As Object, e As EventArgs) Handles btnView2.Click
        Dim exConn As OleDbConnection
        Dim dt As DataSet
        Dim cmd As OleDbDataAdapter
        Dim sConn As String
        'sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & "'C:\File2.xlsx';Extended Properties=Excel 12.0;"
        sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " & txtFile2.Text & ";Extended Properties=Excel 8.0;"
        exConn = New System.Data.OleDb.OleDbConnection(sConn)
        cmd = New System.Data.OleDb.OleDbDataAdapter(
              "select * from [Sheet1$] [Sheet2$]", exConn)
        'cmd.TableMappings.Add("Table2", "Tabel C")
        dt = New System.Data.DataSet
        cmd.Fill(dt)
        dgvFile2.DataSource = dt.Tables(0)
        exConn.Close()
    End Sub
    Private Sub btnClose_Click(sender As Object, e As EventArgs) Handles btnClose.Click
        End
    End Sub
    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        Dim writer As TextWriter = New StreamWriter("C:\report.xls")
        For i As Integer = 0 To dgvCompare.Rows.Count - 2 Step +1
            For j As Integer = 0 To dgvCompare.Columns.Count - 1 Step +1
                writer.Write(dgvCompare.Rows(i).Cells(j).Value.ToString())
            Next
            writer.WriteLine("")
            'writer.WriteLine("---------------------------------------------")
        Next
        writer.Close()
        MessageBox.Show("Data Exported")
    End Sub


End Class

thank you

Hello,

Looking at your code I can see multiple issues and have a few questions:

1. I've been guilty of this myself, however try to avoid using the "End" statement to close a program unless absolutely necessary. You could use the Me.Close() method.
2. Figuring out what type of controls btnBrowse1, btnBrowse2, btnClose, btnSave, btnView1, btnView2, txtFile1, and txtFile2 are wasn't that hard, but what type of controls are dvgCompare, dgvFile1, and dgvFile2?
3. It would seem that a lot of people on this forum dump code without the interface (GUI) that comes with it and expect others to figure it out. You might want to include the form's designer file.
4. And could you include the spreadsheet your program is refering to. (Or a mock up if you don't want to post its contents.)
5. Personally, I'm not a huge fan of the Microsoft.VisualBasic namespace and avoid using it if possible. It makes converting to and from C#(a lot samples on the internet are written with it) easier amongst other things. Almost everything in it maps to something else in the .Net framework.
6. "If Dir(path) <> "" Then" can be rewritten as: "If File.Exists(path) Then".
7. You might want to use property initializes. A lot of properties in your code are being initialized after the object has been created. Use the following syntax:
"Dim object-name As New object-type With {.property = value, ...}". For example:

Dim MyTimer As New Timer With {.Enabled = True, .Interval = 1000}

These things could make answering your question a lot easier and improve your code.

Thank you.

[aguswibowo]

Peter Swinkels,
hi sir
thanks for your response
Sorry just come home from the village.
Thank you for the advice