Compare two excel files using ClosedXML

[luckydead]

Hello,
Can someone help me how i can compare two excel files.

What i want to do is to take from File1.xlsx file specific columns to read
Then to check does in File2 in specific column exists this records if not display in datagridview the error

here is my code

Using workBook As XLWorkbook = New XLWorkbook(TxtPathLocation.Text + "\ConnectorListMST.xlsx")
                Using workBook2 As XLWorkbook = New XLWorkbook(TxtPathLocation.Text + "\Production modules.xlsx")
                    Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
                    Dim workSheet2 As IXLWorksheet = workBook2.Worksheet(1)
                    Dim dt As DataTable = New DataTable()
                    Dim dt2 As DataTable = New DataTable()
                    Dim firstRow As Boolean = True
                    For Each row As IXLRow In workSheet.Rows()
                        For Each row2 As IXLRow In workSheet2.Rows()
                            If firstRow Then
                                For Each cell As IXLCell In row.Cells()
                                    dt.Columns.Add(cell.Value.ToString())
                                Next
                                For Each cell2 As IXLCell In row2.Cells()
                                    dt2.Columns.Add(cell2.Value.ToString())
                                Next
                                firstRow = False
                            Else
                                Dim cellA As IXLCell = row.Cell(1) 'Yazaki Number
                                Dim cellB As IXLCell = row.Cell(2) 'TAB Folder
                                Dim cellC As IXLCell = row.Cell(3) 'SYM File
                                Dim cellE As IXLCell = row2.Cell(3) 'Material
                                Dim cellD As IXLCell = row2.Cell(4) 'Yazaki Number
                                Dim cellF As IXLCell = row2.Cell(6) 'Note
                                Dim isLowerLetter As Boolean = False
                                Dim isLowerLetter2 As Boolean = False

                                If Not String.IsNullOrEmpty(cellA.Value) Then
                                    If String.IsNullOrEmpty(cellB.Value) Or String.IsNullOrEmpty(cellC.Value) Then
                                        isLowerLetter = True
                                    End If
                                End If

                                If String.IsNullOrEmpty(cellA.Value) Then
                                    If Not String.IsNullOrEmpty(cellB.Value) Or Not String.IsNullOrEmpty(cellC.Value) Then
                                        isLowerLetter = True
                                    End If
                                End If

                                If String.IsNullOrEmpty(cellA.Value) And String.IsNullOrEmpty(cellB.Value) And String.IsNullOrEmpty(cellC.Value) Then
                                    isLowerLetter = True
                                End If

                                If cellF.Value.ToString.Contains("Connector") Or cellF.Value.ToString.Contains("connector") Then
                                    If Not String.IsNullOrEmpty(cellE.Value) And Not String.IsNullOrEmpty(cellD.Value) Then
                                        If cellA.Value.ToString() <> cellD.Value.ToString() Then
                                            isLowerLetter2 = True
                                        End If
                                    End If
                                End If


                                If isLowerLetter Then
                                    If String.IsNullOrEmpty(cellA.Value) Then
                                        DataGridView2.Rows.Add("", "ConnectorListMST", "", cellB.Value.ToString() & "/" & cellC.Value.ToString(), "Found issue with missing connector information")
                                    Else
                                        DataGridView2.Rows.Add("", "ConnectorListMST", "", cellA.Value.ToString(), "Found issue with missing connector information")
                                    End If
                                End If
                                If isLowerLetter2 Then
                                    DataGridView2.Rows.Add("", "ConnectorListMST", "Production Modules", cellE.Value.ToString(), "Found Mismatch connector information")
                                End If
                            End If
                        Next
                    Next

https://ibb.co/QdbWLnD
https://ibb.co/ZY9Ny0D

So img1 is File1.xlsx and i want to read from column "D" the numbers and that answers to column "F" contains letter "connector".
Then to check this numbers do they exists in File2.xlsx in column A
Forgot to mention that in File1.xlsx it can contain same connectors with same numbers multiple times, to not use duplicates results in the end.
Example:
C88 6743434553
C88 6743434553
C88 6743434553
C88 6743434553
To read it only 1 time as result in the end if this number 6743434553 not exists in File2 column "A"
Thats all

[ChrisE]

use OLEDB to load and Filter the Data
I have posted this in the past for someone, see if it helps

see comments in the Code

Option Strict On

Public Class Form4

    Public Function ExcelOleDb(ByVal strTextPath As String, _
      ByVal sSQL As String) As System.Data.DataTable
        Dim con As New System.Data.OleDb.OleDbConnection
        Dim myCmd As New System.Data.OleDb.OleDbCommand
        Dim myadp As New System.Data.OleDb.OleDbDataAdapter
        Dim mydt As New System.Data.DataTable
        With con
            .ConnectionString = "provider=microsoft.ACE.OLEDB.12.0;"
            .ConnectionString &= "data source=" & strTextPath & ";"
            .ConnectionString &= "Extended Properties = ""Excel 12.0 XML"";"
        End With
        With myCmd
            .Connection = con
            .CommandType = CommandType.Text
            .CommandText = sSQL
        End With
        With myadp
            .SelectCommand = myCmd
            Try
                .Fill(mydt)

            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End With
        Return (mydt)
    End Function


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'Fill the ComboBox with Distinct Product names from you first sheet
        ComboBox1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT Distinct Product FROM [Sheet1$A:A]")
        ComboBox1.DisplayMember = "Product"
        '###############################
        'Sample.2) select Range: A1:C4 and show in  DGV
        DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * FROM [Sheet1$A1:C4]")
        '################################
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        'Sample.3) Filter Data with Combobox and show in DGV
        'this can be your other Excelsheet, just change the Path
        DataGridView1.DataSource = ExcelOleDb("E:\TestFolder\excelFilter.xlsx", "SELECT * From [Sheet1$] Where Product Like '" & ComboBox1.Text & "'")

    End Sub
End Class

[luckydead]

any suggestions?

[jdelano]

Here is one take on it, a super simple approach.

Given these files:

Imports ClosedXML.Excel
Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim colFile1 As String
        Dim rowStartFile1 As Int16
        Dim rowEndFile1 As Int16

        Dim colFile2 As String
        Dim rowStartFile2 As Int16
        Dim rowEndFile2 As Int16

        Dim file1Path As String = "c:\users\jd310\documents\file1.xlsx"
        Dim file2Path As String = "c:\users\jd310\documents\file2.xlsx"

        Dim file1IDs As String = ""
        Dim file2IDs As String = ""


        Dim wb As XLWorkbook
        Dim ws As IXLWorksheet
        Dim rw As IXLRow

        ' where to start in each file
        colFile1 = "A"
        rowStartFile1 = 3
        rowEndFile1 = 7

        colFile2 = "A"
        rowStartFile2 = 2
        rowEndFile2 = 3

        ' open file 1
        wb = New XLWorkbook(file1Path)
        ws = wb.Worksheet(1)

        ' just put the IDs in a comma delimited string
        For r = rowStartFile1 To rowEndFile1
            rw = ws.Row(r)

            ' only add the id if it hasn't already been added to the string
            If rw.Cell(colFile1).GetString().Trim.Length > 0 And
               file1IDs.IndexOf(rw.Cell(colFile1).GetString()) = -1 Then
                file1IDs += rw.Cell(colFile1).GetString() & ","
            End If

        Next

        wb.Dispose()

        wb = New XLWorkbook(file2Path)
        ws = wb.Worksheet(1)

        ' just put the IDs in a comma delimited string
        For r = rowStartFile2 To rowEndFile2
            rw = ws.Row(r)

            ' only add the id if it hasn't already been added to the string
            If file2IDs.IndexOf(rw.Cell(colFile2).GetString()) = -1 Then
                file2IDs += rw.Cell(colFile2).GetString() & ","
            End If

        Next

        wb.Dispose()

        ' compare them

        ' remove the last comma before splitting the IDs
        Dim file1ID() As String = file1IDs.Substring(0, file1IDs.Length - 1).Split(",")
        Dim idFound As String

        dgv1.Rows.Clear()

        For Each fID In file1ID

            If file2IDs.IndexOf(fID & ",") > -1 Then
                idFound = "Found"
            Else
                idFound = "Not Found"
            End If

            dgv1.Rows.Add(fID, idFound)

        Next

    End Sub
End Class

[luckydead]

your advice is kind similar to what i want to achieve
but i have a different stuff that i need to do.

1. In File1 i need to get from column "F" words that contain "connector" inside and then get for this rows the information from column "D"
2. Then remove the duplicates in the found results
3. Then check in File2 column "A" does it exists this record in rows.

That's all

[luckydead]

I have issue with the code. I receive error message: System.IndexOutOfRangeException: 'Cannot find column 21.'
and it stops here
dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()

The column exists in file, but it gives error.
Using ClosedXML 0.96.0 version for .net framework 4.5

Private Function ReadExcelToDataTable(ByVal filePath As String) As DataTable
        Dim dt As DataTable = New DataTable()
        Using workBook As XLWorkbook = New XLWorkbook(filePath)
            Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
            Dim firstRow As Boolean = True
            For Each row As IXLRow In workSheet.Rows()
                If firstRow Then
                    For Each cell As IXLCell In row.Cells()
                        dt.Columns.Add(cell.Value.ToString())
                    Next
                    firstRow = False
                Else
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                End If
            Next
        End Using

        Return dt
    End Function

Dim file1 As String = System.AppDomain.CurrentDomain.BaseDirectory & "\Production modules.xlsx"
        Dim file2 As String = System.AppDomain.CurrentDomain.BaseDirectory & "\ConnectorListMST.xlsx"
        Using dt1 As DataTable = ReadExcelToDataTable(file1)
            Using dt2 As DataTable = ReadExcelToDataTable(file2)

                Dim partNos = (From data In dt2.AsEnumerable()
                               Select data("Part No")).Distinct().ToArray()
                Dim dt As DataTable = New DataTable()
                dt.Columns.Add("Partnumber")
                dt.Columns.Add("Status")

                For Each dr As DataRow In dt1.Rows
                    If dr("Note").ToString().ToLower().Contains("connector") Then
                        If Not String.IsNullOrEmpty(dr("Yazaki name").ToString()) Then
                            Dim isExist = (From data In dt.AsEnumerable()
                                           Where data("Partnumber").Equals(dr("Yazaki name"))
                                           Select data("Partnumber")).ToArray()
                            If isExist.Length = 0 Then
                                If Array.IndexOf(partNos, dr("Yazaki name").ToString()) < 0 Then
                                    dt.Rows.Add(dr("Yazaki name").ToString(), "missing number %s")
                                Else
                                    dt.Rows.Add(dr("Yazaki name").ToString(), "Exists")
                                End If
                            End If
                        End If
                    End If
                Next

                DataGridView3.DataSource = dt
            End Using
        End Using

[jdelano]

                If firstRow Then
                    For Each cell As IXLCell In row.Cells()
                        dt.Columns.Add(cell.Value.ToString())
                    Next
                    firstRow = False
                Else
                    dt.Rows.Add()
                    Dim i As Integer = 0
                    For Each cell As IXLCell In row.Cells()
                        dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                        i += 1
                    Next
                End If

The first part of the if should be adding all the fields (columns) to the data table required to hold the data in the cells from the Excel file.
If the line

 dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()

throws an error, then the data table doesn't have all the columns needed to hold the data from Excel.

[jdelano]

EDIT: I redid this in VS2019 - .Net 4.5 and ClosedXML 0.95.4 (as 0.102 isn't compatible with .Net 4.5)

I modified this to look for connector in col F, then lookup data in col D (in this via the connector id which I just designated as the first part of the connector name) - I gathered the unique values from col D and compared them to File 2 col A.

        Dim colFile1 As String
        Dim rowStartFile1 As Int16
        Dim rowEndFile1 As Int16

        Dim colFile2 As String
        Dim rowStartFile2 As Int16
        Dim rowEndFile2 As Int16

        Dim file1Path As String = "c:\users\jd310\documents\file1.xlsx"
        Dim file2Path As String = "c:\users\jd310\documents\file2.xlsx"

        Dim file1IDs As String = ""
        Dim file2IDs As String = ""
        Dim colFText As String = ""
        Dim colDText As String = ""

        Dim wb As XLWorkbook
        Dim ws As IXLWorksheet
        Dim rw As IXLRow

        ' ********************** where to start in each file
        ' ** first file - get the connectors from col F
        colFile1 = "F"
        rowStartFile1 = 2
        rowEndFile1 = 7

        colFile2 = "A"
        rowStartFile2 = 2
        rowEndFile2 = 3

        ' open file 1
        wb = New XLWorkbook(file1Path)
        ws = wb.Worksheet(1)

        ' just put the IDs in a comma delimited string
        For r = rowStartFile1 To rowEndFile1
            rw = ws.Row(r)

            colFText = rw.Cell(colFile1).GetString().Trim

            ' does the cell contain an item that has the word connector in it
            If colFText.ToUpper.IndexOf("CONNECTOR") > -1 Then

                ' if so get the data for it from col D
                For colD = rowStartFile1 To rowEndFile1

                    colDText = rw.Cell("D").GetString().Trim

                    ' does this match the connector code (for testing I'm using the first 2 characters)
                    ' just because, if it does match then add the data to what
                    ' will be looked for in file 2
                    If colFText.Substring(0, 2) = rw.Cell("C").GetString() Then
                        ' check if there is anything in the cell
                        ' has this already been selected 
                        If colDText.Length > 0 And file1IDs.IndexOf(colDText) = -1 Then
                            file1IDs += colDText & ","
                        End If
                    End If
                Next
            End If

        Next

        wb.Dispose()

        wb = New XLWorkbook(file2Path)
        ws = wb.Worksheet(1)

        ' just put the IDs in a comma delimited string
        For r = rowStartFile2 To rowEndFile2
            rw = ws.Row(r)

            ' only add the id if it hasn't already been added to the string
            If file2IDs.IndexOf(rw.Cell(colFile2).GetString()) = -1 Then
                file2IDs += rw.Cell(colFile2).GetString() & ","
            End If

        Next

        wb.Dispose()

        ' compare them

        ' remove the last comma before splitting the IDs
        Dim file1ID() As String = file1IDs.Substring(0, file1IDs.Length - 1).Split(",")
        Dim idFound As String

        dgv1.Rows.Clear()

        For Each fID In file1ID

            If file2IDs.IndexOf(fID & ",") > -1 Then
                idFound = "Found"
            Else
                idFound = "Not Found"
            End If

            dgv1.Rows.Add(fID, idFound)

        Next

Don't know why the attachment is so small this time, here is an imgur link https://imgur.com/IfqDUO1

Attachment 188436