This project has moved. For the latest updates, please go here.

Conditional Formatting

Dec 4, 2012 at 3:43 PM
Edited Dec 5, 2012 at 11:45 AM

I am trying to put some conditional formatting on cells in given range. Unfortunately nothing happens. I want cells to change colour from white to red in an ascending manner. When exactly conditional formatting is applied? Do I have to do something more with the rule instance or Conditional Formatting is going to be applied at the same time it is created (when calling AddTwoColorScale) ?

here is a code:

I tried to follow the sample concerning conditional formatting which is published, but obviously I have to miss something...

Any suggestions?

Thanks

 

 

using (ExcelRange scoreRange = a_worksheet.Cells[fromRow, fromColumn, toRow, fromColumn])
{
   ExcelAddress rangeAddress = new ExcelAddress(scoreRange.Address);
   var rule = a_worksheet.ConditionalFormatting.AddTwoColorScale(rangeAddress);
   rule.LowValue.Type = eExcelConditionalFormattingValueObjectType.Min;
   rule.LowValue.Color = System.Drawing.Color.White;
   rule.HighValue.Type = eExcelConditionalFormattingValueObjectType.Max;                         
rule
.HighValue.Color = System.Drawing.Color.DarkRed;
}
Dec 5, 2012 at 12:42 PM
Edited Dec 5, 2012 at 12:43 PM

It is solved, The cause of this situation was the fact that in samples, when you show conditional formatting you assign to the cell.Value an Integer. In my case I assigned a string and set up NumberFormat which was not a proper approach. So I filled cells in this way:

 

foreach (Tokens tokens in m_model.Data)
{
  int colCounter = fromColumn;
  foreach (string dataValue in tokens)
  {
    long numericDataValue;
    using (ExcelRange cell = a_worksheet.Cells[rowCounter, colCounter])
    {
      cell.Value = dataValue;
      cell.Style.Border.BorderAround(ExcelBorderStyle.Thin);
      bool canConvert = long.TryParse(dataValue, out numericDataValue);
      if (canConvert == true)
        cell.Style.Numberformat.Format = "0";                    
    }
    colCounter++;
  }
  rowCounter++;
}

And it is supposed to be filled in this way if conditional formatting is supposed to work:

foreach (Tokens tokens in m_model.Data)
{
    int colCounter = fromColumn;
    foreach (string dataValue in tokens)
    {
        long numericDataValue;
        using (ExcelRange cell = a_worksheet.Cells[rowCounter, colCounter])
        {
            cell.Style.Border.BorderAround(ExcelBorderStyle.Thin);
            bool canConvert = long.TryParse(dataValue, out numericDataValue);
            if (canConvert == true)
            {
                cell.Style.Numberformat.Format = "0";
                cell.Value = numericDataValue;
            }
            else
            {
                cell.Value = dataValue;
            }             
        }
        colCounter++;
    }
    rowCounter++;
}