Compare today's date to a column and highlight the cell

Question

In my winform app , there is a column called "Next_Calibration_On"(in the format "dd-MM-yyyy") with which i have to compare today date if it is less than it then i want highlight the cell in the datagrid view in red . Fr this i have the below code :

        private void dataGridView1_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
    {
        DateTime currentToday = (DateTime)this.dataGridView1.Rows[e.RowIndex].Cells["Next_Calibration_On"].Value;

        if (currentToday <= DateTime.Now.Date)
        {
            e.CellStyle.ForeColor = Color.Red; //Font Color
            e.CellStyle.SelectionForeColor = Color.Red; //Selection Font color
        }

However this is showing the error message as below:

System.ArgumentException: 'Column named Next_Calibration_On cannot be found.Parameter name: columnName'

But i do have column in my table ..how to solve this?

Answer 1

Don't edit your cells. Tell your DataGridView where to fetch its data

Don't put the data directly in the DataGridView, tell it where to fetch its data, and how to display the fetched data. This separates your data from how it is displayed. Whenever you decide to display it differently you don't have to change your original data.

You can see how this separation between data and display is done in a spreadsheet. The spreadsheet data can be shown as a collection of cells layed out in columns and rows, and displaying textual representation of the values in the cells.

Another method to show the same data would be in a graph: even though the display is entirely different than a collection of cells, the data behind is the same.

Same data, differently displayed.

In winforms you have the same separations with all Controls that show sequences of data: ListBox, ListView, DataGridView, Chart, etc.

All these classes offer the possibility to directly add the data to the component, but they also have a property DataSource.

If you assign a sequence to the DataSource, something like a List or Array, the Data is displayed. other properties decide how each element of the DataSource must be displayed.

In a DataGridView the DataGridViewColumns define which property of each row must be displayed, and how they must be displayed.

In its most easy form:

Suppose you have a class Customer:

class Customer
{
    public int Id {get; set;}
    public string Name {get; set;}
    public Address Address {get; set;}
    public string PhoneNr {get; set;}
};

Suppose you also have a DataGridView that shows only the Id and Name of Customers

DataGridViewColumn columnCustomerId = new DateGridViewColumn
{
    DataPropertyName = nameof(Customer.Id),
    ValueType = typeof(int),
};
DataGridViewColumn columnCustomerName = new DateGridViewColumn
{
    DataPropertyName = nameof(Customer.Name),
    ValueType = typeof(string),
};
DataGridView customersDataGridView = new DataGridView(...) {...}
customersDataGridView.Columns.Add(columnCustomerId);
customersDataGridView.Columns.Add(columnCustomerName);

Now to show Customers:

List<Customer> customers = FetchCustomers(...);
customersDataGridview.DataSource = customers;

This is enough to show the customer's Id and Name. If you also want to show the Telephone numbers, just add a Column. You don't have to change your original data.

Similarly if you want to format the cells of certain customers differently: this has no influence on your original customer collection. Only the Display changes.

Side Remark

Although the above works, you still have problems if operators edit the displayed data. If you want to automatically update changes made by operators, do not put the data in a List, but in a System.ComponentModel.BindingList:

BindingList<Customer> customers = new BindingList<Customer>(FetchCustomers(...));
customerDataGridView.DataSource = customers;

Now if the operator adds / removes / changes Customers from the DataGridView, they are automatically updated in the BindingList.

And a handy tip: if you want automatic sorting by clicking on the column header, and if you want easy filtering: "show only Customers who live in 'Amsterdam'" without having to change the Customers list, consider to use Nuget package BindingListView

BindingListView<Customer> customers = new BindingListView<Customer>(FetchCustomers(...));
customerDataGridView.DataSource = customers;

And presto: you've got sortable columns; editable customers. Filtering is easy:

// show only Amsterdam customers:
customers.ApplyFilter( customer => customer.Address.City == "Amsterdam");

Even though you don't show the City of the customer in the DataGridView, you can easily filter on it.

By the way: did you notice how easy I switched from DataSource without even changing my DataGridView? This is because I separated the display from the source data.

Back to your question

You want to change the layout of the cells in column NextCalibration. Every Cell with a value higher than xxx should be Red.

The layout of a cell is done in a DataGridViewCellStyle. If your Cell doesn't have its own CellStyle, it uses the CellStyle of the DataGridViewColumn.

You want to change the cell style whenever the value of the displayed property meets some requirement. In this case: foreground color red if it is less than today's date, otherwise the default value. Make a Clone of the default cell style of the column.

DataGridViewColumn columnNextCalibration = ...
DataGridViewCellStyle specialCellStyle = (DataGridViewCellStyle)columnNextCalibration
    .CellTemplate
    .CellStyle
    .Clone();

If your column has no special style (it is null), use the InheritedStyle.

// adjust the CellStyle to the style it should have when the value meets the requirements
specialCellStyle.ForeColor = Color.Red;
// if desired: change the Font, the fontSize, or the BackColor, the frame whatever!

You want to select either the default CellStyle (null) or this special cell style, depending on the displayed DateTime value:

Now that you have a special CellStyle wait until the displayed value changes:

this.customersDataGridView.CellValueChanges += OnCellValueChanged;

private void OnCellValueChanged(object sender, DataGridViewCellEventArgs eventArgs)
{
    DataGridView dgv = (DataGridView)sender;

    // is the changed cell from my column in my DataGridView?
    if (Object.ReferencEquals(sender, customersDataGridView)
    && e.ColumnIndex == columnNextCalibation.DisplayIndex)
    {
        DataGridViewCell changedCell = dgv.Rows[e.RowIndex].Column[e.ColumnIndex];
        DateTime cellValue = (DateTime)cell.Value;
        DateTime today = ...
        if (cellValue < today)
        {
            // use the special CellStyle:
            changedCell.CellStyle = specialCellStyle;
        }
        else
        {
            // use the default CellStyle of the column, not a special one for this cell
            changedCell.CellStyle = null;
        }
    }
}