ASP.Net ClosedXML: Compare two Excel files using C# and VB.Net

evoteam2...

SOLVED CLOSED ACTIVE ANSWERED
User: evoteam21
Posted: on Jul 26, 2023 07:53 AM
Forum: ASP.Net Thirdparty controls
Answer: 3
Views: 3565
Sample Code: Download

Hello,

How i can make compare between two excel files specific columns.

So i have two files

File1 i want to search column "F" that contains letters "connector" then to read the information from column "D"

Then to remove the duplicates in the finded all records (no duplications to have)

Then in File2 to check does this numbers exist in column A

If not exist to display in DataGridView error it has missing number %s

Thats all

Code

VB.Net

Using dt1 As DataTable = ReadExcelToDataTable(TxtPathLocation.Text + "\Production modules.xlsx")
    Using dt2 As DataTable = ReadExcelToDataTable(TxtPathLocation.Text + "\ConnectorListMST.xlsx")
        Dim partNos = (From data In dt1.AsEnumerable()
                        Where data("Note").ToString().ToLower().Contains("connector") Or data("Note").ToString().ToLower().Contains("Connector")
                        Select data("Yazaki name")).Distinct().ToArray()
        Dim dt As DataTable = New DataTable()
        For Each dr As DataRow In dt2.Rows
            If Array.IndexOf(partNos, dr("Part No").ToString()) >= 0 Then
                'DataGridView2.Rows.Add(dr("PartNo").ToString(), "Exists")
            Else
                DataGridView2.Rows.Add("", "ConnectorListMST", "", dr("Part No").ToString(), "Found issue with missing connector information")
                'DataGridView2.Rows.Add(dr("PartNo").ToString(), "missing number %s")
            End If
        Next
        DataGridView2.Rows.Add(dt)
        DataGridView2.Rows.RemoveAt(DataGridView2.Rows.Count - 1)
    End Using
End Using

Download FREE API for Word, Excel and PDF in ASP.Net

evoteam2...

0 17

Reply
ANSWER
User: evoteam21
Replied: on Jul 31, 2023 02:41 AM
Report

I think i manage to make it how it should be.

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") Or dr("Note").ToString().ToLower().Contains("connector") Then
                If Not String.IsNullOrEmpty(dr("Yazaki name").ToString()) Then
                    If Array.IndexOf(partNos, dr("Yazaki name").ToString()) >= 0 Then
                        dt.Rows.Add(dr("Yazaki name").ToString(), "Exists")
                    Else
                        dt.Rows.Add(dr("Yazaki name").ToString(), "missing number %s")
                    End If
                End If
            End If
        Next
        dataGridView1.DataSource = dt
    End Using
End Using

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

ASP.Net ClosedXML: Compare two Excel files using C# and VB.Net

Company

Explore

Follow us

Contact Us