Features, discussions, tips, tricks, questions, problems and feedback

DataGridView cell colour based on value

Hi, Please could you assist me with applying a background colour to these cells in DataGridView. >80 green and <80 red. This DataGridView is used on a wizard and duplicated 15 times using a parameterized select query. Thanks in advance.
image

Hi Steve,

To accomplish this you will need to use a ScriptSpider in your wizard to change the cells backcolours depending on the values they are updated with.

Add a ScriptSpider into your wizard and add two inputs to it i.e. var 1 and var 2. Link your OLEDB table or QuerySpider result output to the ScriptSpider input var 1. Click on the DataGridView and on the right-side Property tree find its (Name) property under the Design category. Right-click the (Name) property and select Show property spider. Right-click on the DataGridView (Name) property spider header and check Propogate Outputs At Startup.

Screenshot%202024-04-18%20131635

Link the DataGridView (Name) property spider output to ScriptSpider input var 2 . Right-click on the ScriptSpider header and check Enable Silk Triggering.

Launch the script from the ScriptSpider by clicking on Edit Script.
In the script please add System.Data.dll as a reference and include a using System.Data; statement at the top of your script code as below:

Add the following lines of code into your script:

    //entry point for script engine
    public void Main()
    {
        //Initializes Event Handlers and Control Name Bindings
        InitializeVIZNETComponents();

        DataElement __de1 = MyInputs["var 1"] as DataElement;
        DataElement __de2 = MyInputs["var 2"] as DataElement;

        if (__de1.Value != null && __de1.Value is DataSet)
        {
            DataSet __ds = __de1.Value as DataSet;

            if (__de2.Value != null && __de2.Value.ToString() != string.Empty)
            {
                string dataGridViewName = __de2.Value.ToString();

                DataGridView dataGridView = MyController[dataGridViewName] as DataGridView;

                if (dataGridView != null)
                {
                    dataGridView.CellFormatting += DataGridView_CellFormatting;

                    dataGridView.DataSource = __ds.Tables[0];
                    dataGridView.AllowUserToAddRows = false;
                }
            }
        }
    }

    private void DataGridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    { 
        //Compare the value of second Column i.e. Column with Index 1,2,3.
        if (e.ColumnIndex > 0 && e.Value != null)
        {
            //Fetch the value of the second Column.
            double value = Convert.ToDouble(e.Value);

            //Apply Background color based on value.
            if (value >= 80)
            {
                e.CellStyle.BackColor = Color.Green;
				e.CellStyle.ForeColor = Color.White;
            }
            if (value < 80)
            {
                e.CellStyle.BackColor = Color.Red;
				e.CellStyle.ForeColor = Color.White;
            }
        }
    }

The code snippet above gets the data table from your var 1 input when its set. As the DataGridView name will change when adding the wizard to a graphic form, we must also get the new DataGridView name from your var 2 input. We then populate the DataGridView with data table using the new name. We then hook on to the DataGridView’s CellFormatting event so that we can change the backcolour of the cells depending on the values updated in it.

I have also attached the sample wizard below:

OEEGridWiz.vizw (16.1 KB)

1 Like

Hi Diego, Thanks for the support. I can get that solution working great on a single GF but when I apply it on my wizard I get numerous faults and it doesn’t load. Do I need a variable in the script?
I tried to attach my wizard for you to view but it won’t let me upload.

image

As discussed, I have updated the HowTo above and the sample graphic form and its script to cater for the fact the DataGridView name will change when pasting the wizard onto a graphic form. This should take care of the errors you are seeing. I have also sent through a modified version of your wizard that you are currently working on.

1 Like

This is working great now. Thank you for your support.