Forums Ultimate UI for ASP.NET Web Forms
Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
Close
Nathon Dalton
185
Importing Values Into Wrong Columns Where Null
posted

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, ColD
R1CA, R1CB, R1CC, R1CD
R2CA, R2CB, Null, R2CD
Null, R3CB, R3CC, R3CD

It would import as this.

ColA, ColB, ColC, ColD
R1CA, R1CB, R1CC, R1CD
R2CA, R2CB, R2CD
R3CB, 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!

  • 4960
    Suggested Answer
    posted

    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 = 0
    For 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 If
    Next ' Cell