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 FunctionCode: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




Reply With Quote
