Conditional Formatting

Conditional formatting is an advanced Microsoft Excel feature that allows you to apply formats to a cell or range of cells and have that formatting change depending on the value of the cell or the value of a formula. For example, you can have a cell appear bold only when the value of the cell is greater than 500. When the value of the cell meets the condition, the specified format is applied to the cell. If the value of the cell does not meet the condition, the default formatting is used. In Microsoft Excel, select Format, then Conditional Formatting to open the Conditional Formatting dialog.

Conditional formatting in Microsoft Excel

Aspose.Cells supports applying conditional formatting to cells at runtime. This article explains how.

Applying Conditional Formatting

Aspose.Cells supports conditional formatting in two ways:

• Using a designer spreadsheet.
• Creating conditional formatting at runtime.

Using Designer Spreadsheet

Developers can create a designer spreadsheet that contains conditional formatting in Microsoft Excel and then open that spreadsheet with Aspose.Cells. Aspose.Cells loads and saves the designer spreadsheet, keeping any conditional formatting setting. To find out more about designer spreadsheets, read What is a Designer Spreadsheet.

Applying Conditional Formatting at Runtime

Aspose.Cells supports applying conditional formatting at runtime.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Path to source file
String dataDir = Utils.getSharedDataDir(ConditionalFormattingatRuntime.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
ConditionalFormattingCollection cfs = sheet.getConditionalFormattings();

// The first method:adds an empty conditional formatting
int index = cfs.add();
FormatConditionCollection fcs = cfs.get(index);

// Sets the conditional format range.
CellArea ca1 = new CellArea();
ca1.StartRow = 0;
ca1.StartColumn = 0;
ca1.EndRow = 0;
ca1.EndColumn = 0;

CellArea ca2 = new CellArea();
ca2.StartRow = 0;
ca2.StartColumn = 0;
ca2.EndRow = 0;
ca2.EndColumn = 0;

CellArea ca3 = new CellArea();
ca3.StartRow = 0;
ca3.StartColumn = 0;
ca3.EndRow = 0;
ca3.EndColumn = 0;

fcs.addArea(ca1);
fcs.addArea(ca2);
fcs.addArea(ca3);

// Sets condition formulas.
int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "=A2", "100");

FormatCondition fc = fcs.get(conditionIndex);

int conditionIndex2 = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100");

// Saving the Excel file
workbook.save(dataDir + "CFAtRuntime_out.xls");

Set Font

Setting fonts in Microsoft Excel

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Path to source file
String dataDir = Utils.getSharedDataDir(SettingFontStyle.class) + "data/";
		
// Instantiating a Workbook object
Workbook workbook = new Workbook();
FormatCondition fc = null;
Style style = fc.getStyle();
Font font = style.getFont();
font.setItalic(true);
font.setBold(true);
font.setStrikeout(true);
font.setUnderline(FontUnderlineType.DOUBLE);
font.setColor(Color.getBlack());
fc.setStyle(style);
// Saving the Excel file
workbook.save(dataDir + "SFontStyle_out.xls");

Set Border

Setting borders in Microsoft Excel

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Path to source file
String dataDir = Utils.getSharedDataDir(SetBorder.class) + "data/";
		
// Instantiating a Workbook object
   Workbook workbook = new Workbook();
   Worksheet sheet = workbook.getWorksheets().get(0);

   // Adds an empty conditional formatting
   int index = sheet.getConditionalFormattings().add();
   FormatConditionCollection fcs = sheet.getConditionalFormattings().get(index);

   // Sets the conditional format range.
   CellArea ca = new CellArea();
   ca.StartRow = 0;
   ca.EndRow = 5;
   ca.StartColumn = 0;
   ca.EndColumn = 3;
   fcs.addArea(ca);
   // Adds condition.
   int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100");
   // Sets the background color.
   FormatCondition fc = fcs.get(conditionIndex);
   
Style style = fc.getStyle();
style.setBorder(BorderType.LEFT_BORDER, CellBorderType.DASHED, Color.fromArgb(0, 255, 255));
style.setBorder(BorderType.TOP_BORDER, CellBorderType.DASHED, Color.fromArgb(0, 255, 255));
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.DASHED, Color.fromArgb(0, 255, 255));
style.setBorder(BorderType.RIGHT_BORDER, CellBorderType.DASHED, Color.fromArgb(255, 255, 0));
fc.setStyle(style);

Set Pattern

Setting a cell pattern in Microsoft Excel

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Path to source file
String dataDir = Utils.getSharedDataDir(SetPattern.class) + "data/";

// Instantiating a Workbook object
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);

// Adds an empty conditional formatting
int index = sheet.getConditionalFormattings().add();
FormatConditionCollection fcs = sheet.getConditionalFormattings().get(index);

// Sets the conditional format range.
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 5;
ca.StartColumn = 0;
ca.EndColumn = 3;
fcs.addArea(ca);
// Adds condition.
int conditionIndex = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.BETWEEN, "50", "100");
// Sets the background color.
FormatCondition fc = fcs.get(conditionIndex);

Style style = fc.getStyle();
style.setPattern(BackgroundType.REVERSE_DIAGONAL_STRIPE);
style.setForegroundColor(Color.fromArgb(255, 255, 0));
style.setBackgroundColor(Color.fromArgb(0, 255, 255));
fc.setStyle(style);

Advance topics

• Add Conditional Icons Set with the Cell Text
• Adding 2-Color Scale and 3-Color Scale Conditional Formattings
• Apply Conditional Formatting in Worksheets
• Apply Shading to Alternate Rows and Columns with Conditional Formatting