Hello,
Can you give me advice and help how to convert the code to use existing DataGridView to display the results.
If I change dt.rows.add to Datagridview2.rows.add it start show duplications
If I use Datagridview.datasource = dt it works correct but if i use multiple stuff in the datagridview (more checks to display) then the old records are gone
Using dt1 As DataTable = ImportExceltoDataTable(TxtPathLocation.Text + "\Production modules.xlsx")
Using dt2 As DataTable = ImportExceltoDataTable(TxtPathLocation.Text + "\ConnectorListMST.xlsx")
Dim partNos = (From data In dt2.AsEnumerable()
Select data("Part No")).Distinct().ToArray()
Dim dt As DataTable = New DataTable()
' dt.Columns.Add("Position")
'dt.Columns.Add("Information")
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("Part No").Equals(dr("Yazaki name"))
Select data("Part No")).ToArray()
If isExist.Length = 0 Then
If Array.IndexOf(partNos, dr("Yazaki name").ToString()) < 0 Then
DataGridView2.Rows.Add(dr("Yazaki name").ToString(), "missing number %s")
Else
DataGridView2.Rows.Add(dr("Yazaki name").ToString(), "Exists")
End If
End If
End If
End If
Next
DataGridView2.Rows.Add(dt)
DataGridView2.Rows.RemoveAt(DataGridView2.Rows.Count - 1)
'DataGridView2.DataSource = dt
End Using
End Using
Function ImportExceltoDataTable(ByVal filePath As String) As DataTable
Using wb As XLWorkbook = New XLWorkbook(filePath)
Dim ws As IXLWorksheet = wb.Worksheet(1)
Dim tl_Row As Integer = ws.FirstCellUsed().Address.RowNumber
Dim tl_Col As Integer = ws.FirstCellUsed().Address.ColumnNumber
Dim br_Row As Integer = ws.LastCellUsed().Address.RowNumber
Dim br_Col As Integer = ws.LastCellUsed().Address.ColumnNumber
Dim dt As DataTable = New DataTable()
' add dt columns using the first row of data
For i As Integer = tl_Col To br_Col
dt.Columns.Add(ws.Cell(tl_Row, i).CachedValue.ToString())
Next
Dim currentRow As IXLRow
'add data from the worksheet to dt - we already used the first row of data for the columns
For dtRow As Integer = 0 To br_Row - tl_Row - 1
currentRow = ws.Row(tl_Row + dtRow + 1)
dt.Rows.Add()
For dtCol As Integer = 0 To br_Col - tl_Col + 1 - 1
dt.Rows(dtRow)(dtCol) = currentRow.Cell(tl_Col + dtCol).CachedValue
Next
Next
Return dt
End Using
End Function
By using the old function that was this one I receive error with index column 21, my guest was that the function does not create the column names when reads from the file. Here is the old view the function
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
