newConditionalFormatting

Returns a new conditionalFormatting object to be applied to a workbook.

newConditionalFormatting()

Notes

• Conditional formatting allows cells to be highlighted or otherwise visually formatted when they contain particular values or when a given condition is met.
• ConditionalFormatting objects are created using a "builder" syntax (see examples below).
• You can apply the ConditionalFormatting object either by passing in the workbook to the addToWorkbook() builder method, or by returning the new ConditionalFormatting object and passing it to the library's addConditionalFormatting() method.

ConditionalFormatting builder methods

• when( required string formula ): use to specify any condition or formula that if true will cause the formatting to be applied.
• whenCellValueIs( required string comparisonOperator, required string valueOrFormula, string valueOrFormula2 ): use to apply the formatting when the cell's value meets a condition specified using a comparison operator and a value or formula to compare the cell's value with (see examples below).
  • Possible comparisonOperatorvalues are: EQ, NEQ, GT, LT, GTE, LTE, BETWEEN and NOT BETWEEN.
  • withBETWEEN and NOT BETWEEN, you must specify the valueOrFormula2 for the comparison.
• setFormat( required struct format ): pass in a struct which specifies the format to be applied when the condition is met (see options below).
• onCells( required string cellRangeReference ): specify which cells to apply the conditional formatting to as a cell range reference, e.g. "A1" or "A1:B4".
• onSheetName( required string sheetName ): By default the currently active sheet will be used, but you can specify a different sheet by passing the name.
• onSheetNumber( required numeric sheetNumber): By default the currently active sheet will be used, but you can specify a different sheet by passing the number.
• addToWorkbook( required workbook ): applies the conditionalFormatting object to the specified workbook.

Format options

Note: the options available for conditional formatting are more limited than those possible with the standard formatting methods.

• fontColor (string): Can be any of the following:
  • The name of a pre-set colour - full list of available colours, also viewable via the getPresetColorNames() method
  • an RGB triplet, e.g. 255,255,255. NB: Only XLSX files can use exact RGB specified colours. Binary XLS files will use the closest pre-defined colour.
  • a hex value, e.g. "4B0082" or "#4B0082", which will be converted to an RGB triplet
• fontSize (integer): size in points.
• bold (boolean)
• italic (boolean)
• underline (boolean OR string): Either true/false or one of the following: "none", "single", or "double".
• bottomBorder (string): One of the POI Border Styles
• bottomBorderColor(string): See fontColor for possible values
• leftBorder (string): See bottomBorder for possible values
• leftBorderColor(string): See fontColor for possible values
• rightBorder (string): See bottomBorder for possible values
• rightBorderColor(string): See fontColor for possible values
• topBorder (string): See bottomBorder for possible values
• topBorderColor(string): See fontColor for possible values
• backgroundFillColor (string): See fontColor for possible values, although the options may be more limited.
• foregroundFillColor (string): See fontColor for possible values, although the options may be more limited.
• fillPattern (string): One of the POI fill patterns

Examples

Example 1: Apply a format to cells when the cell value meets a condition

wb = spreadsheet.newXlsx();
spreadsheet.newConditionalFormatting()
  .onCells( "A1:A10" )
  .whenCellValueIs( "LT", 0 )
  .setFormat( { backgroundFillColor: "RED", bold: true } )
  .addToWorkbook( wb );
// set A1 to 1, the format is unchanged
spreadsheet.setCellValue( wb, 1, 1, 1 );
// set A2 to -1, it goes red/bold
spreadsheet.setCellValue( wb, -1, 2, 1 );

Example 2: Apply a format to cells when a custom formula is true

wb = spreadsheet.newXlsx();
spreadsheet.newConditionalFormatting()
  .onCells( "A1:B1" )
  .when( "$A1<0" )
  .setFormat( { backgroundFillColor: "RED", bold: true } )
  .addToWorkbook( wb );
// set B1 as a label
spreadsheet.setCellValue( wb, "Warning label", 1, 2 ); 
// set A1 to 1, the format is unchanged
spreadsheet.setCellValue( wb, 1, 1, 1 );
// set A1 to -1, both A1 and B1 go red/bold
spreadsheet.setCellValue( wb, -1, 1, 1 );

Example 3: Use with a chainable workbook

formatting = spreadsheet.newConditionalFormatting()
  .onCells( "A1:B1" )
  .when( "$A1<0" )
  .setFormat( { backgroundFillColor: "RED", bold: true } );
chainable = spreadsheet.newChainable( "xlsx" ).addConditionalFormatting( formatting );
);