[SOLVED] Conditional Formatting With Icons Epplus

Conditional Formatting With Icons Epplus

I need to achieve something like this with Epplus.

enter image description here

Can someone guide me with the code I need to use.

Solution

Following is the code to do exactly what you want but it is for three icon set you can change it based on your icons. I'm setting red color arrow if value is greater than 4,yellow color arrow if value is between 1 and 4 and, finally, green color if it is less than 1. Just change "AddThreeIconSet" to your icons. You should get the idea with this.

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, eExcelconditionalFormatting3IconsSetType.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 if (Convert.ToDouble(workSheet.Cells[i, j].Value) < 1.0)
       {
          var v = worksheet.ConditionalFormatting.AddThreeIconSet(address , eExcelconditionalFormatting3IconsSetType.Arrows);
          v.Icon2.Type = eExcelConditionalFormattingValueObjectType.Num;
       }
    }
}