LarsS LarsS - 9 months ago 32
Vb.net Question

Disregard or replace character in datagridview vb.net

I have a code to color the cells in a datagridview based on defined criteria for several different pollutants, and it works well. However, there will often be occurrences of the character '<' in cases like "<0.005", meaning "below detection limit", and that crashes the routine with the message "Operator '<' is not defined for type 'DBNull' and type 'Double'."

Edit: This is the latest code as supplied by JohnG. I still get error messages when the subs encounter empty cells or invalid characters

Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports System.Text.RegularExpressions

Public Class Form1
Public Property gridResults As Object

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
OpenFileDialog2.Title = "Velg fil ..."


OpenFileDialog2.InitialDirectory = "C:users\<currentuser>\Documents"
OpenFileDialog2.Filter = "Alle filer|*.*|Excel 2003|*.xls|Excel|*.xlsx"
OpenFileDialog2.FilterIndex = 2

OpenFileDialog2.ShowDialog()


End Sub


Private Sub OpenFileDialog2_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog2.FileOk


Dim strm As System.IO.Stream
strm = OpenFileDialog2.OpenFile()

TextBox2.Text = OpenFileDialog2.FileName.ToString()

If Not (strm Is Nothing) Then
strm.Close()
End If

Me.Button5_Click(sender, e)

End Sub

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click

If String.IsNullOrEmpty(TextBox2.Text) Then
MessageBox.Show("Klikk ""Bla gjennom"" for å velge en fil", "Ingen inndatafil")
Exit Sub
End If

Dim FilePath As String = OpenFileDialog2.FileName

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim DtSet As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
MyConnection = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & FilePath & ";Extended Properties=Excel 8.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)
MyCommand.TableMappings.Add("Table", "Net-informations.com")
DtSet = New System.Data.DataSet
MyCommand.Fill(DtSet)
DataGridView2.DataSource = DtSet.Tables(0)
MyConnection.Close()

End Sub

Public Function GetElementColorsValues(elementName As String) As Decimal()
Dim ULArray(4) As Decimal
Select Case elementName
Case "As (Arsen)"
ULArray(0) = 8
ULArray(1) = 20
ULArray(2) = 50
ULArray(3) = 600
ULArray(4) = 1000
Case "Cd (Kadmium)"
ULArray(0) = 1.5
ULArray(1) = 10
ULArray(2) = 15
ULArray(3) = 30
ULArray(4) = 1000
Case "Cu (Kopper)"
ULArray(0) = 100
ULArray(1) = 200
ULArray(2) = 1000
ULArray(3) = 8500
ULArray(4) = 25000
Case "Cr (Krom)"
ULArray(0) = 50
ULArray(1) = 200
ULArray(2) = 500
ULArray(3) = 2800
ULArray(4) = 25000
Case "Hg (Kvikksølv)"
ULArray(0) = 1
ULArray(1) = 2
ULArray(2) = 4
ULArray(3) = 10
ULArray(4) = 1000
Case "Ni (Nikkel)"
ULArray(0) = 60
ULArray(1) = 135
ULArray(2) = 200
ULArray(3) = 1200
ULArray(4) = 2500
Case "Pb (Bly)"
ULArray(0) = 60
ULArray(1) = 100
ULArray(2) = 300
ULArray(3) = 700
ULArray(4) = 2500
Case "Zn (Sink)"
ULArray(0) = 200
ULArray(1) = 500
ULArray(2) = 1000
ULArray(3) = 5000
ULArray(4) = 25000
End Select
Return ULArray
End Function

'Fargeleggingsrutine - gir feilmelding
Private Sub SetDGVColColor()

Dim ULArray As Decimal()
Dim curValue As String
Dim decimalValue As Decimal
Dim colName = ""
For col As Integer = 2 To DataGridView2.ColumnCount - 1
colName = DataGridView2.Columns(col).Name
ULArray = GetElementColorsValues(colName)
For Each row As DataGridViewRow In DataGridView2.Rows
If (Not row.IsNewRow) Then
curValue = row.Cells(colName).Value
If (curValue IsNot Nothing) Then
Decimal.TryParse(curValue, decimalValue)
' the above TryParse line will set decimalValue to 0 if curValue is not a valid decimal i.e `<0.005`
Select Case decimalValue
Case >= ULArray(4)
row.Cells(colName).Style.BackColor = Color.BlueViolet
Case >= ULArray(3)
row.Cells(colName).Style.BackColor = Color.Red
Case >= ULArray(2)
row.Cells(colName).Style.BackColor = Color.Orange
Case >= ULArray(1)
row.Cells(colName).Style.BackColor = Color.Yellow
Case >= ULArray(0)
row.Cells(colName).Style.BackColor = Color.LawnGreen
Case Else
row.Cells(colName).Style.BackColor = Color.DodgerBlue
End Select
End If ' ignore empty cell
End If ' ignore the new row
Next
Next

End Sub

Private Sub Button6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button6.Click
SetDGVColColor()
End Sub

'Første svar fra JohnG
'Fjerde forsøk på eksport
Private Sub ExportToExcel()
Dim excel As Microsoft.Office.Interop.Excel._Application = New Microsoft.Office.Interop.Excel.Application()
Dim workbook As Microsoft.Office.Interop.Excel._Workbook = excel.Workbooks.Add(Type.Missing)
Dim worksheet As Microsoft.Office.Interop.Excel._Worksheet = Nothing
excel.Visible = True
Try
worksheet = workbook.ActiveSheet
worksheet.Name = "ExportedFromDataGrid"
Dim cellRowIndex As Integer = 1
Dim cellColumnIndex As Integer = 1
'gets header rows.
For Each column In DataGridView2.Columns
worksheet.Cells(1, column.Index + 1).Value = column.Name
Next
'gets all other rows
Dim rowIndex = 2
For Each row As DataGridViewRow In DataGridView2.Rows
If Not row.IsNewRow Then
For colIndex As Integer = 0 To DataGridView2.Columns.Count - 1
worksheet.Cells(rowIndex, colIndex + 1).Value = row.Cells(colIndex).Value.ToString
Next
End If
rowIndex += 1
Next

' Substituted code below that loops through each column with data
' then sets the color for each of those columns by calling the SetColColor method
For index As Integer = 2 To DataGridView2.Columns.Count
Dim colName = DataGridView2.Columns(index).Name
SetExcelColColor(worksheet, colName, index + 1)
Next
MessageBox.Show("Closing excel: save if needed!")
'workbook.SaveAs("YourFileName..",)
workbook.Close()
excel.Quit()
Marshal.ReleaseComObject(worksheet)
Marshal.ReleaseComObject(workbook)
Marshal.ReleaseComObject(excel)
Catch
MessageBox.Show("Error")
End Try
End Sub

'andre eksportrutine med fargelegging fra JohnG
Private Sub SetExcelColColor(worksheet As Microsoft.Office.Interop.Excel._Worksheet, colName As String, colIndex As Integer)
Dim rIndex = 2
Dim cIndex = colIndex
Dim ULArray = GetElementColorsValues(colName)
Dim curValue As String
Dim decimalValue As Decimal

For Each row As DataGridViewRow In DataGridView2.Rows
If (Not row.IsNewRow) Then
curValue = row.Cells(colName).Value
If (curValue IsNot Nothing) Then
Decimal.TryParse(curValue, decimalValue)
Select Case decimalValue
Case >= ULArray(4)
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
Case >= ULArray(3)
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
Case >= ULArray(2)
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
Case >= ULArray(1)
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
Case >= ULArray(0)
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
Case Else
worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
End Select
rIndex += 1
End If ' ignore empty cell
End If ' ignore new row
Next
End Sub


Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
Me.ExportToExcel()
End Sub

Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
System.Windows.Forms.Application.Exit()
End Sub


End Class

Answer Source

I am not completely sure if I follow what you are asking so correct me if I am wrong. I am guessing the value “<0.005” is a value in a DataGridView Cell. If this is the case then you will need to change this “String” value to a “Decimal” The previous code I supplied did not check for empty or invalid numbers before the comparison is made. Since the cell value could be anything, the code needs to check for two things: An empty or null cell value or an invalid number. The error you are getting could be coming from either case.

Your comment

How can I make the routine disregard the < character, replace it with "" or replace the entire string with zero?

In this case when the cell contains the value “<0.005” will throw the error you see because comparing a string to a double won’t work. Since you state above that setting this value to zero (0) is sufficient, then I recommend you use a TryParse method. If the TryParse method is given an invalid number it will return zero (0). You could use this knowledge to implement what you describe.

I would recommend you use the same strategy you used to color the Excel cells. I changed the GetElementColorsValues method to return a Decimal array. This change is necessary if the values in the DataGridView are decimal values.

Public Function GetElementColorsValues(elementName As String) As Decimal()
  Dim ULArray(4) As Decimal
  Select Case elementName
    Case "Arsenic"
      ULArray(0) = 8
      ULArray(1) = 20
      ULArray(2) = 50
      ULArray(3) = 600
      ULArray(4) = 1000
    Case "Cadmium"
      ULArray(0) = 1.5
      ULArray(1) = 10
                    ………..

Now with this array we can compare the decimal values in the DataGridView. I used a Decimal.TryParse to get the Decimal value from a cells string value like below

Decimal.TryParse(curValue, decimalValue)

Above curValue is a string from the DataGridView cell and decimalValue is the retuned Decimal value from parsing the string to a decimal. The whole line Decimal.TryParse(curValue, decimalValue) will return true if the parse was successful and false if not successful.

The convenient aspect of this is that if the parse is unsuccessful (like with a value of <0.005) the TryParse will set the variable decimalValue to zero (0) as you are asking. Simply using the Decimal.TryParse will set the variable decimalValue to zero when it fails and will set it to a valid decimal number if it succeeds. This can be seen in the code below which checks for null or empty values then, if not null or empty uses the Decimal.TryParse to get the decimal value to be used in the comparison for coloring. It uses the same GetElementColorsValues(colName) method used when coloring the Excel cells... you will have to change the excel coloring code also to accommodate the Decimal array… below this method)

Update Edit to catch BDNULL cells in the data table

I was incorrect and technically, you CAN have a row in a DataTable that contains no column data. So the line: row.Cells(colName).Value will obviously throw the error you are getting. I am not saying this is the problem, but that was the only way I could reproduce your error. So the code below checks for these missing columns of data. I changed the code to use DataBoundItems since you are using this in your code; below that is the change needed without using the data bound item. Both worked, however if feel that may not be the case if the table is sorted or rows deleted etc. My next question would be why you would read these empty rows into the data table if they were well… EMPTY?

Obviously, you will need to make these changes when writing the grid to excel.

Private Sub SetDGVColColor()
  Dim ULArray As Decimal()
  Dim curValue As String
  Dim decimalValue As Decimal
  Dim colName = ""
  For col As Integer = 2 To dgvElements.ColumnCount - 1
    colName = dgvElements.Columns(col).Name
    ULArray = GetElementColorsValues(colName)
    Dim curDataBoundRow
    For Each row As DataGridViewRow In dgvElements.Rows
      If (Not row.IsNewRow) Then
        curDataBoundRow = row.DataBoundItem    ' <-- Added Code
        If (Not IsDBNull(curDataBoundRow(colName))) Then ' <-- Added Code
          curValue = curDataBoundRow(colName)
          If (curValue IsNot Nothing) Then
            Decimal.TryParse(curValue, decimalValue)
            ' the above TryParse line will set decimalValue to 0 if curValue is not a valid decimal i.e `<0.005`
            Select Case decimalValue
              Case >= ULArray(4)
                row.Cells(colName).Style.BackColor = Color.BlueViolet
              Case >= ULArray(3)
                row.Cells(colName).Style.BackColor = Color.Red
              Case >= ULArray(2)
                row.Cells(colName).Style.BackColor = Color.Orange
              Case >= ULArray(1)
                row.Cells(colName).Style.BackColor = Color.Yellow
              Case >= ULArray(0)
                row.Cells(colName).Style.BackColor = Color.LawnGreen
              Case Else
                row.Cells(colName).Style.BackColor = Color.DodgerBlue
            End Select
          End If ' cell is empty
        End If ' ignore null cells in data table  <-- Added Code
      End If ' ignore the new row if present
    Next
  Next
End Sub

Changes to code without using data bound items.

…….

For Each row As DataGridViewRow In dgvElements.Rows
  If (Not row.IsNewRow) Then
    If (Not IsDBNull(row.Cells(colName).Value)) Then  ' <-- ADDED code
      curValue = row.Cells(colName).Value
      If (curValue IsNot Nothing) Then
  …….

Changes to color excel cells method using Decimal value comparisons.

Private Sub SetExcelColColor(worksheet As Microsoft.Office.Interop.Excel._Worksheet, colName As String, colIndex As Integer)
  Dim rIndex = 2
  Dim cIndex = colIndex
  Dim ULArray = GetElementColorsValues(colName)
  Dim curValue As String
  Dim decimalValue As Decimal

  For Each row As DataGridViewRow In dgvElements.Rows
    If (Not row.IsNewRow) Then
      curValue = row.Cells(colName).Value
      If (curValue IsNot Nothing) Then
        Decimal.TryParse(curValue, decimalValue)
        Select Case decimalValue
          Case >= ULArray(4)
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.BlueViolet)
          Case >= ULArray(3)
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
          Case >= ULArray(2)
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)
          Case >= ULArray(1)
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
          Case >= ULArray(0)
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LawnGreen)
          Case Else
            worksheet.Cells(rIndex, cIndex).Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DodgerBlue)
        End Select
        rIndex += 1
      End If ' ignore empty cell 
    End If ' ignore new row
  Next
End Sub