I am importing an Excel spreadsheet into a data grid. I'm using the function listed here to import an Excel document into a dataset. However, where there are null values in a column, the value in the next column is "slid" into the current column with the null value.
For instance, given the following columns and values.
ColA, ColB, ColC, ColDR1CA, R1CB, R1CC, R1CDR2CA, R2CB, Null, R2CDNull, R3CB, R3CC, R3CD
It would import as this.
ColA, ColB, ColC, ColDR1CA, R1CB, R1CC, R1CDR2CA, R2CB, R2CDR3CB, R3CC, R3CD
I have removed the checking for the value being null...
if ((obj_HeaderCell.Value != null) && (obj_HeaderCell.Value.ToString().Length > 0))
However, that didn't work and stepping through the code the null cells are in the cells collection, but the foreach loop skips right over the cells that are null. I have the following.
foreach
(WorksheetCell obj_CurrCell in obj_Worksheet.Rows[int_CurrRow].Cells)
For instance in the above example on the first row with a null value it would populate with Cell1, then Cell 2, then Cell 4. It won't even populate obj_CurrCell with the null cell.
Any help would be greatly appreciated!
Like Excel, we save memory by not storing all of the empty Worksheet Cells. When the cell does not exist, it really does not exist in the WorksheetRows (et al) collections, although an empty WorksheetCell could be created in response to the application asking for it by indexing the collection.
You should be able to try something like the following highlighted code sections with that snippet of code you're using,
Dim iExpectedCol As Integer = 0For Each cell As WorksheetCell In w.Rows(iRow).Cells ' Makes explicit that I am checking how many columns I have skipped over If ( iExpectedCol < cell.ColumnIndex ) Then ' For each extra column skipped, add a Null cell For iNulls As Integer = iExpectedCol To cell.ColumnIndex cellList.Add("NULL") Next 'iNulls End If
If (Not cell.Value Is Nothing And cell.Value.ToString().Length > 0) Then cellList.Add(cell.Value.ToString()) iExpectedCol = cell.ColumnIndex + 1 End IfNext ' Cell