Get cell range/location based on value at excel sheet and colour the cell datagridview in c#

Question

I'm displaying the imported excel sheet in data grid view. based on the row name, I'm replacing the specific cell strings. Now I want to colour the specific cells based on their values.

 private void Replace_strings()
    {
        TextInfo textInfo = new CultureInfo("en-US", false).TextInfo;
        DataTable dt = dataGridView1.DataSource as DataTable;

        foreach (DataRow r in dt.Select("[SET 1] = 'First Name'"))
            for (int y = 1; y < r.ItemArray.Length; y++)
            {
                String str = r[y].ToString();
                r[y] = str.Replace('0', 'o');
                r[y] = textInfo.ToUpper(r[y].ToString());

                if (r[y].ToString().Contains("%"))
                {

                   //If a cell contains "%"I want to mark that cell in red colour
                  //CellStyle.BackColor = Color.Red;
                    //CellStyle.ForeColor = Color.White;


                }

            }
    }

How do I efficiently correct this

EXPECTED

Click to view the expected output image

Answer 1

There are numerous ways to do this. I would loop through the “Grid” instead of the data source. The DataTable contains the data, however the “grid” displays it. Because of this, it is the “grid” where you want to change the color not the data table.

However, looping through the grid may not be necessary if you wire up a couple of the grids events.

The grid has numerous “CellFormatting/Painting” events you could use, however, you want to be careful which one you use. Most of these events get fired often, even if a cells value has not changed. In other words, if the grid gets repainted because the grid is scrolled, it is a wasted effort to re-format the cell since it has not changed.

Given this, I suggest you wire up two (2) of the grid’s events… the CellEndEdit event... this event will fire when a cells value changes and the user attempts to leave the cell. When this event fires, the code should look for the “%” and format accordingly.

The CellEndEdit event will work when changes are made “after” the grid has been loaded with data. Unfortunately, the CellEndEdit event will NOT fire when the data is “initially” loaded into the grid. To help, the second event, RowsAdded is used, this event will fire when “new” rows are added to the grid. It is in this event where we can loop through each cell in the row and set the color accordingly.

Example…

Wire up the grids two events…

public Form1() {
  InitializeComponent();
  dataGridView1.CellEndEdit += new DataGridViewCellEventHandler(dataGridView1_CellEndEdit);
  dataGridView1.RowsAdded += new DataGridViewRowsAddedEventHandler(dataGridView1_RowsAdded);
}

Since we need to format the cells “independently” is makes sense to create a method such that given a DataGridViewCell the method will check the cells value and format it accordingly. This method will come in handy in both events. The method may look like…

private void FormatACell(DataGridViewCell cell) {
  if (cell.Value != null && cell.Value.ToString().Contains("%")) {
    cell.Style.BackColor = Color.Red;
  }
  else {
    cell.Style.BackColor = Color.White;
  }
}

Next the two events…

private void dataGridView1_RowsAdded(object sender, DataGridViewRowsAddedEventArgs e) {
  DataGridViewRow row = dataGridView1.Rows[e.RowIndex];
  foreach (DataGridViewCell cell in row.Cells) {
    FormatACell(cell);
  }
}

private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) {
  DataGridViewCell cell = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex];
  FormatACell(cell);
}

Lastly, to complete the example…

DataTable dt;

private void Form1_Load(object sender, EventArgs e) {
  dt = GetDataFromDB();
  dataGridView1.DataSource = dt;
}

private DataTable GetDataFromDB() {
  DataTable dt = new DataTable();
  dt.Columns.Add("Col1", typeof(string));
  dt.Columns.Add("Col2", typeof(string));
  dt.Columns.Add("Col3", typeof(string));
  dt.Rows.Add("Data1", "Dat%a2", "Data3");
  dt.Rows.Add("Data2%", "Data3", "Data1");
  dt.Rows.Add("Data3", "Data2", "Da%ta1");
  return dt;
}