Hi All
I am using closedXML in vb.net (VS2022) to open an Excel file and place it in a data table or Datagridview.
Although the following code works, if there are blank cells in the rows of the target spreadsheet then data is inserted into the incorrect column.
I got this code snippet from searching on Google so it is not my own code
Code:
Private Sub MnuFileOpen_Click(sender As Object, e As EventArgs) Handles MnuFileOpen.Click
Dim OFG As New OpenFileDialog
Using OFG
With OFG
.Title = "Select Target File"
.Filter = "Excel Files|*.xls;*.xlsx"
If .ShowDialog = DialogResult.OK Then
TargetFileName = .FileName
End If
End With
End Using
'Open the Excel file using ClosedXML.
Using workBook As New XLWorkbook(TargetFileName)
'Read the first Sheet from Excel file.
Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
Dim dt As New DataTable
'Loop through the Worksheet rows.
Dim firstRow As Boolean = True
For Each row As IXLRow In workSheet.Rows()
'Use the first row to add columns to DataTable.
If firstRow Then
For Each cell As IXLCell In row.Cells()
dt.Columns.Add(cell.Value.ToString())
Next
firstRow = False
Else
'Add rows to DataTable.
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
DataGridView1.DataSource = dt
SourceDatatable = dt
End Using
I believe the issue is somewhere here:
Code:
For Each cell As IXLCell In row.Cells()
dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
i += 1
Next
Any assistance would be greatly appreciated