-
Apr 20th, 2017, 01:35 AM
#1
Thread Starter
New Member
How to compare remove duplicate row in cell datagridview visual basic studio 2015
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
Code:
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
-
Apr 20th, 2017, 10:07 AM
#2
Re: How to compare remove duplicate row in cell datagridview visual basic studio 2015
Originally Posted by 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
Code:
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:
Code:
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.
Last edited by Peter Swinkels; Apr 20th, 2017 at 10:38 AM.
Reason: grammar
-
Apr 28th, 2017, 12:53 AM
#3
Thread Starter
New Member
Re: How to compare remove duplicate row in cell datagridview visual basic studio 2015
Peter Swinkels,
hi sir
thanks for your response
Sorry just come home from the village.
Thank you for the advice
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|