CODEWITHSUNDEEP
c#epplus
sanmis
sanmis

Reputation: 525

Epplus conditional formatting based on a rule

How can we add three icon sets to each cell in excel using epplus conditional formatting. I'm using following code to add three icon set:

using (ExcelRange scoreRange = workSheet.Cells[1, 2, 1, 10])
            {
                ExcelAddress rangeAddress = new ExcelAddress(scoreRange.Address);
                var ruleIconSet = workSheet.ConditionalFormatting.AddThreeIconSet(rangeAddress, eExcelconditionalFormatting3IconsSetType.Arrows); // This calculates based on the value in the range
            }

I want to create a rule like if value in a cell is less than 0, the green color icon should be displayed, if value is greater than 0, the red color icon should be displayed.

What should be the rule statement that can perform this stuff?

Upvotes: 0

Views: 2763

Answers (1)

sanmis
sanmis

Reputation: 525

for(int j =2; j <=9; j++) //Loop through columns
{
   for(int i = 3; i <= 12; i++) // Loop through rows
   {
   // gets only the current cell as range
   ExcelRange rng = worksheet.Cells[i, j, i, j]; 
   ExcelAddress address = new ExcelAddress(rng.Address);
   // Get the value of the current cell
   if(Convert.ToDouble(worksheet.Cells[i, j].Value) >= 4.0)
   {
      var v = worksheet.ConditionalFormatting.AddThreeIconSet(address, eExcelconditionalFormatting3IconSetType.Arrows);
      v.Reverse = true;
      v.Icon1.Type = eExcelConditionalFormattingValueObjectType.Num;
   }
   else if(Convert.ToDouble(workSheet.Cells[i, j].Value) > 1.0 && Convert.ToDouble(workSheet.Cells[i, j].Value) < 4.0)
   {

      var v = worksheet.ConditionalFormatting.AddThreeIconSet(address , eExcelconditionalFormatting3IconsSetType.Arrows);
      v.Icon3.Type = eExcelConditionalFormattingValueObjectType.Num;

   }
   else (Convert.ToDouble(workSheet.Cells[i, j].Value)  < 1.0)
   {
      var v = worksheet.ConditionalFormatting.AddThreeIconSet(address , eExcelconditionalFormatting3IconsSetType.Arrows);
      v.Icon2.Type = eExcelConditionalFormattingValueObjectType.Num;
   }
}
}

This works for me.

Upvotes: 0

Related Questions