Apply Conditional Formatting in Worksheet

Possible Usage Scenario

Aspose.Cells allows you to add all sorts of Conditional Formatting e.g. Formula, Above Average, Color Scale, Data Bar, Icon Set, Top10, etc. It provides the IFormatCondition class which has all the necessary methods to apply conditional formatting of your choice. Here is the list of a few of Get methods.

• GetIAboveAverage()
• GetIColorScale()
• GetIDataBar()
• GetIIconSet()
• GetITop10()

Apply Conditional Formatting in Worksheet

The following sample code shows how to add a Cell Value conditional formatting on cells A1 and B2. Please see the output excel file generated by the code and the following screenshot which explains the effect of the code on the output excel file. If you will put some value greater than 100 in cell A2 and B2, the Red fill color from cell A1 and B2 will disappear.

Sample Code

//For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-C

//Path of output excel file
StringPtr outputApplyConditionalFormattingInWorksheet = outPath->StringAppend(new String("outputApplyConditionalFormattingInWorksheet.xlsx"));

//Create an empty workbook
intrusive_ptr<IWorkbook> wb = Factory::CreateIWorkbook();

//Access first worksheet
intrusive_ptr<IWorksheet> ws = wb->GetIWorksheets()->GetObjectByIndex(0);

//Adds an empty conditional formatting
int idx = ws->GetIConditionalFormattings()->Add();
intrusive_ptr<IFormatConditionCollection> fcs = ws->GetIConditionalFormattings()->GetObjectByIndex(idx);

//Set the conditional format range
intrusive_ptr<ICellArea> ca = ICellArea::CreateICellArea(new String("A1"), new String("A1"));
fcs->AddArea(ca);
ca = ICellArea::CreateICellArea(new String("B2"), new String("B2"));
fcs->AddArea(ca);

//Add condition and set the background color
idx = fcs->AddCondition(FormatConditionType_CellValue, OperatorType_Between, new String("=A2"), new String("100"));
intrusive_ptr<IFormatCondition> fc = fcs->GetObjectByIndex(idx);
fc->GetIStyle()->SetBackgroundColor(Systems::Drawing::Color::GetRed());

//User friendly message to test the output excel file.
StringPtr msgStr = new String("Red color in cells A1 and B2 is because of Conditional Formatting. Put 101 or any value >100 in cell A2 and B2, you will see Red background color will be gone.");
ws->GetICells()->GetObjectByIndex(new String("A10"))->PutValue(msgStr);

//Save the output excel file
wb->Save(outputApplyConditionalFormattingInWorksheet);