I struggled with this for a while.
When a Excel Spreadsheet is imported into a DGV, some blanks cells (in Excel) are imported as Null values. This causes exceptions with some code. So, my idea was to replace all Null values with default values, depending on the data type ("" for Text or False for Checkboxes, etc.).
I tried "TypeOf" test to determine the data type. This did not work, since the value in the the cell is Null it has no type. I eventually found "CellType" to determine the date type.
I did as following:
The code scans the whole DGV and replaces Null values with its default type. The "Case Else" replaces all unknown types with "???". This is test code for me so that I can easily detect other types I may have missed and eventually cater for the Type in the Case statement.
My question is this the correct way, or is there a way to simplify the code. Is there a better way to do this?
When a Excel Spreadsheet is imported into a DGV, some blanks cells (in Excel) are imported as Null values. This causes exceptions with some code. So, my idea was to replace all Null values with default values, depending on the data type ("" for Text or False for Checkboxes, etc.).
I tried "TypeOf" test to determine the data type. This did not work, since the value in the the cell is Null it has no type. I eventually found "CellType" to determine the date type.
I did as following:
VB.NET:
Public Sub ClearNullValues(ByRef _DataGridView As DataGridView)
Dim strCellType As String
Try
For y As Integer = 0 To _DataGridView.Rows.Count - 1
For x As Integer = 0 To _DataGridView.ColumnCount - 1
strCellType = _DataGridView.Columns(x).CellType.ToString()
If IsDBNull(_DataGridView.Rows(y).Cells(x).Value) Then
Select Case strCellType
Case "System.Windows.Forms.DataGridViewTextBoxCell"
_DataGridView.Rows(y).Cells(x).Value = ""
Case "System.Windows.Forms.DataGridViewCheckBoxCell"
_DataGridView.Rows(y).Cells(x).Value = False
Case Else
_DataGridView.Rows(y).Cells(x).Value = "???"
End Select
End If
Next
Next
Catch ex As Exception
MessageBox.Show(ex.Message, "Test For Null Values", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
The code scans the whole DGV and replaces Null values with its default type. The "Case Else" replaces all unknown types with "???". This is test code for me so that I can easily detect other types I may have missed and eventually cater for the Type in the Case statement.
My question is this the correct way, or is there a way to simplify the code. Is there a better way to do this?