Results 1 to 8 of 8

Thread: Compare two excel files using ClosedXML

Threaded View

  1. #7

    Thread Starter
    Addicted Member
    Join Date
    Jan 2017
    Posts
    199

    Re: Compare two excel files using ClosedXML

    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

    Code:
    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
    Code:
    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
    Last edited by luckydead; Aug 1st, 2023 at 01:39 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width