xlsgen > overview > Conditional Formatting

Conditional formatting is a feature of Excel that combines formulas and formatting styles. One or more formatting changes are applied if one out of one or more conditions are valid.

Conditional formatting is applied at the cell level, or to an entire range of cells. In Excel's user interface, after you have selected one or more cells, you can bring the conditional formatting dialog box by opening the Format menu and choosing Conditional Formatting....

The xlsgen object model has a comprehensive functionality set for conditional formattings :



 

Inside conditional formattings

A single cell conditional formatting object is created from the current worksheet. See the IXlsWorksheet interface for more information.

Once the properties of the conditional formatting object are set, the object must be passed to the current worksheet along with the row and column of the target cell. Alternatively, you may want to create a conditional formatting object from a range.

More conditional formatting rules can be associated to an existing conditional formatting object. See the IXlsConditionalFormatting interface for more information. If more than one conditional formatting is passed, then both are logically checked for validness.

The actual condition for a conditional formatting object must be true for the formatting style changes to be applied. The changes are passed with a style object. See the IXlsStyle interface for more information.

When applied, formatting changes are restricted to the font, borders and pattern tabs.

A condition is either a cell condition or a plain formula. Cell conditions are special case of formulas which often occur in practice. Cell conditions are easier to set up than full-fledged formulas, since they require less information.

To decide whether you are using a cell condition or a formula condition, the IXlsConditionalFormatting interface must be used.

The screen above depicts a double cell condition, where the background color of cell C3 is changed to green if the content is greater than 5, and changed to red if it's less than 2. The following code shows how to achieve this :

VB code

wksht.Number(3, 2) = 6

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Pattern.BackgroundColor = colorGreen
style.Pattern.ForegroundColor = colorGreen
style.Pattern.Pattern = pattern_solid
style.Apply

Dim style2 As IXlsStyle
Set style2 = style.Duplicate
style2.Pattern.BackgroundColor = colorRed
style2.Pattern.ForegroundColor = colorRed
style2.Pattern.Pattern = pattern_solid
style2.Apply

Dim cf As IXlsConditionalFormatting
Set cf = wksht.NewConditionalFormatting
cf.CellCondition.GreaterThan 5
cf.Style = style

Dim cf2 As IXlsConditionalFormatting
Set cf2 = cf.AdditionalCondition
cf2.CellCondition.LessThan 2
cf2.Style = style2

wksht.ConditionalFormatting(3, 2) = cf
C# code

wksht.set_Number(3,2, 6);

IXlsStyle style = wksht.NewStyle();
style.Pattern.BackgroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.ForegroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.Pattern = xlsgen.enumPattern.pattern_solid;
style.Apply();

IXlsStyle style2 = style.Duplicate();
style2.Pattern.BackgroundColor = (int) xlsgen.enumColorPalette.colorRed;
style2.Pattern.ForegroundColor = (int) xlsgen.enumColorPalette.colorRed;
style2.Pattern.Pattern = xlsgen.enumPattern.pattern_solid;
style2.Apply();

IXlsConditionalFormatting cf = wksht.NewConditionalFormatting();
cf.CellCondition.GreaterThan("5");
cf.Style = style;

IXlsConditionalFormatting cf2 = cf.AdditionalCondition();
cf2.CellCondition.LessThan("2");
cf2.Style = style2;

wksht.set_ConditionalFormatting(3,2, cf);
C/C++ code

wksht->Number[3][2] = 6;

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Pattern->BackgroundColor = xlsgen::colorGreen;
style->Pattern->ForegroundColor = xlsgen::colorGreen;
style->Pattern->Pattern = xlsgen::pattern_solid;
style->Apply();

xlsgen::IXlsStylePtr style2 = style->Duplicate();
style2->Pattern->BackgroundColor = xlsgen::colorRed;
style2->Pattern->ForegroundColor = xlsgen::colorRed;
style2->Pattern->Pattern = xlsgen::pattern_solid;
style2->Apply();

xlsgen::IXlsConditionalFormattingPtr cf = wksht->NewConditionalFormatting();
cf->CellCondition->GreaterThan(L"5");
cf->Style = style;

xlsgen::IXlsConditionalFormattingPtr cf2 = cf->AdditionalCondition();
cf2->CellCondition->LessThan(L"2");
cf2->Style = style2;

wksht->ConditionalFormatting[3][2] = cf;

 

Cell condition operators
Between2 arguments
Not Between2 arguments
EqualTo1 argument
NotEqualTo1 argument
GreaterThan1 argument
LessThan1 argument
GreaterThanOrEqualTo1 argument
LessThanOrEqualTo1 argument

 

While cell condition operators meet a number of business cases, formula conditions are a more general form of conditional formatting, a greater abstraction. The formatting is governed by a formula. Below is an example where the formatting style of cell C3 is ruled by the content of cell A1. The background of cell C3 turns green if cell A1 is greater than 5 :


 

And the code to produce it is :

VB code

wksht.Number(1, 1) = 4 ' used as a conditional number
wksht.Number(3, 3) = 6 ' cell where the condition applies

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Pattern.BackgroundColor = colorGreen
style.Pattern.ForegroundColor = colorGreen
style.Pattern.Pattern = pattern_solid
style.Apply

Dim cf As IXlsConditionalFormatting
Set cf = wksht.NewConditionalFormatting
cf.FormulaCondition.Formula = "$A$1 > 5"
cf.style = style

wksht.ConditionalFormatting(3, 3) = cf
C# code

wksht.set_Number(1,1, 4); // used as a conditional number
wksht.set_Number(3,3, 6); // cell where the condition applies

IXlsStyle style = wksht.NewStyle();
style.Pattern.BackgroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.ForegroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.Pattern = xlsgen.enumPattern.pattern_solid;
style.Apply();

IXlsConditionalFormatting cf = wksht.NewConditionalFormatting();
cf.FormulaCondition.Formula = "$A$1 > 5";
cf.Style = style;

wksht.set_ConditionalFormatting(3,3, cf);
C/C++ code

wksht->Number[1][1] = 4; // used as a conditional number
wksht->Number[3][3] = 6; // cell where the condition applies

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Pattern->BackgroundColor = xlsgen::colorGreen;
style->Pattern->ForegroundColor = xlsgen::colorGreen;
style->Pattern->Pattern = xlsgen::pattern_solid;
style->Apply();

xlsgen::IXlsConditionalFormattingPtr cf = wksht->NewConditionalFormatting();
cf->FormulaCondition->Formula = L"$A$1 > 5";
cf->Style = style;

wksht->ConditionalFormatting[3][3] = cf;

 

Where do conditional formattings? to individual cells or range of cells. The following code presents an example of range-based conditional formatting. See ranges for more information on range objects.

VB code

' create a range made of 3 areas
Dim range As IXlsRange
Set range = wksht.NewRange("R2C2:R4C3")
range.AddSubRange("R8C2:R10C5")
range.AddCellsRange(13,3,13,6) ' R12C2:R12C5

' create a conditional formatting
' and define the formatting style when the condition is true
Dim cf As IXlsConditionalFormatting
Set cf = range.NewConditionalFormatting
cf.CellCondition.GreaterThan("3.5")
cf.Style = style

' done
C# code

// create a range made of 3 areas
IXlsRange range = wksht.NewRange("R2C2:R4C3");
range.AddSubRange("R8C2:R10C5");
range.AddCellsRange(13,3,13,6); // R12C2:R12C5

// create a conditional formatting
// and define the formatting style when the condition is true
IXlsConditionalFormatting cf = range.NewConditionalFormatting();
cf.CellCondition.GreaterThan("3.5");
cf.Style = style;

// done
C/C++ code

// create a range made of 3 areas
xlsgen::IXlsRangePtr range = wksht->NewRange("R2C2:R4C3");
range->AddSubRange("R8C2:R10C5");
range->AddCellsRange(13,3,13,6); // R12C2:R12C5

// create a conditional formatting
// and define the formatting style when the condition is true
xlsgen::IXlsConditionalFormattingPtr cf = range->NewConditionalFormatting();
cf->CellCondition->GreaterThan(L"3.5");
cf->Style = style;

 

Predefined conditional formattings

xlsgen predefines a number of conditional formattings that are commonly used :

Predefined conditions can be combined as well, in fact what each one does is simply automatically fill a formula condition for you.

Here are corresponding screenshots :



Alternate rows


Alternate columns


Top x values


Bottom x values


Hide (calculation) errors


Is Number


IsFormula (cells showing 3 actually holds formula =SUM(B3:B4) )


Group by


Text-based conditions (for example : begins with "abc")

 

And here is, for example, how to setup alternate rows :

Java code

XlsStyle style = wksht.NewStyle();
style.getPattern().putBackgroundColor(0xCCFFCC);

XlsRange r = wksht.NewRange("B5:I22");

XlsConditionalFormatting cf = r.NewConditionalFormatting();
cf.getPredefinedCondition().AlternateRows(0);
cf.putStyle(style);
VB code

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Pattern.BackgroundColor = &HCCFFCC

Dim r As IXlsRange 
Set r = wksht.NewRange("B5:I22")

Dim cf As IXlsConditionalFormatting
Set cf = r.NewConditionalFormatting
cf.PredefinedCondition.AlternateRows(0)
cf.style = style
C# code

IXlsStyle style = wksht.NewStyle();
style.Pattern.BackgroundColor = 0xCCFFCC;

IXlsRange r = wksht.NewRange("B5:I22");

IXlsConditionalFormatting cf = r.NewConditionalFormatting();
cf.PredefinedCondition.AlternateRows(0);
cf.Style = style;
C/C++ code

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Pattern->BackgroundColor = 0xCCFFCC;

xlsgen::IXlsRangePtr r = wksht->NewRange(L"B5:I22");

xlsgen::IXlsConditionalFormattingPtr cf = r->NewConditionalFormatting();
cf->PredefinedCondition->AlternateRows(0);
cf->Style = style;

 

Computing conditional formattings

The same helper function in xlsgen applies to conditional formattings than to conditional number formats. In other words, it is possible not only to create conditional formattings, it is possible to compute the conditional formattings and retrieve the corresponding formatting styles. This method is used for printing purposes for instance, where conditional formattings have to be computed.

To retrieve the actual formatting style after computing the conditional formatting(s) in a cell, simply do :

VB code

' put a number in cell (3,2)

wksht.Number(3, 2) = 6


' attach some conditional formatting in cell (3,2)

Dim style As IXlsStyle
Set style = wksht.NewStyle
style.Pattern.BackgroundColor = colorGreen
style.Pattern.ForegroundColor = colorGreen
style.Pattern.Pattern = pattern_solid
style.Apply


' for the formatting to hold, the value must be greater than 5 in cell (3,2)

Dim cf As IXlsConditionalFormatting
Set cf = wksht.NewConditionalFormatting
cf.CellCondition.GreaterThan 5
cf.Style = style

wksht.ConditionalFormatting(3, 2) = cf


' retrieve the style after computation of the conditional formatting

Dim styleComputed1 As IXlsStyle
Set styleComputed1 =  wksht.StyleComposedFromLocation(3,2)

Dim fillPatternColor
fillPatternColor = styleComputed1.Pattern.BackgroundColor ' returns &H00FF00 (green)


' change the value in cell (3,2), intentionally below the threshold


wksht.Number(3, 2) = 2


' retrieve the style after computation of the conditional formatting

Dim styleComputed2 As IXlsStyle
Set styleComputed2 =  wksht.StyleComposedFromLocation(3,2)

Dim fillPatternColor2
fillPatternColor2 = styleComputed2.Pattern.BackgroundColor ' returns &H000000 (black)
C# code

// put a number in cell (3,2)

wksht.set_Number(3,2, 6);


// attach some conditional formatting in cell (3,2)

IXlsStyle style = wksht.NewStyle();
style.Pattern.BackgroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.ForegroundColor = (int) xlsgen.enumColorPalette.colorGreen;
style.Pattern.Pattern = xlsgen.enumPattern.pattern_solid;
style.Apply();


// for the formatting to hold, the value must be greater than 5 in cell (3,2)

IXlsConditionalFormatting cf = wksht.NewConditionalFormatting();
cf.CellCondition.GreaterThan("5");
cf.Style = style;

wksht.set_ConditionalFormatting(3,2, cf);


// retrieve the style after computation of the conditional formatting

IXlsStyle styleComputed1 = wksht.get_StyleComposedFromLocation(3,2);

int fillPatternColor = styleComputed1.Pattern.BackgroundColor; // returns 0x00FF00 (green)


// change the value in cell (3,2), intentionally below the threshold


wksht.set_Number(3,2, 2)


// retrieve the style after computation of the conditional formatting

IXlsStyle styleComputed2 = wksht.get_StyleComposedFromLocation(3,2);

int fillPatternColor2 = styleComputed2.Pattern.BackgroundColor; // returns 0x000000 (black)
C/C++ code

// put a number in cell (3,2)

wksht->Number[3][2] = 6;


// attach some conditional formatting in cell (3,2)

xlsgen::IXlsStylePtr style = wksht->NewStyle();
style->Pattern->BackgroundColor = xlsgen::colorGreen;
style->Pattern->ForegroundColor = xlsgen::colorGreen;
style->Pattern->Pattern = xlsgen::pattern_solid;
style->Apply();


// for the formatting to hold, the value must be greater than 5 in cell (3,2)

xlsgen::IXlsConditionalFormattingPtr cf = wksht->NewConditionalFormatting();
cf->CellCondition->GreaterThan(L"5");
cf->Style = style;

wksht->ConditionalFormatting[3][2] = cf;



// retrieve the style after computation of the conditional formatting

xlsgen::IXlsStylePtr styleComputed1 = wksht->StyleComposedFromLocation[3][2];

int fillPatternColor = styleComputed1->Pattern->BackgroundColor; // returns 0x00FF00 (green)


// change the value in cell (3,2), intentionally below the threshold


wksht->Number[3][2] = 2;


// retrieve the style after computation of the conditional formatting

xlsgen::IXlsStylePtr styleComputed2 = wksht->StyleComposedFromLocation[3][2];

int fillPatternColor2 = styleComputed2->Pattern->BackgroundColor; // returns 0x000000 (black)

 

Enumerate / Edit / Delete

Conditional formattings can be individually enumerated, edited and deleted. The collection of conditional formattings is exposed with the IXlsConditionalFormattings interface available from the current worksheet.

Here is an example in C/C++ :

C/C++ code


// enumerate, edit and delete a worksheet containing 3 conditional formattings 
// the first conditional formatting has one rule with an alert background set to red
//
//
//

xlsgen::IXlsWorkbookPtr wbk = engine->Open( L"input\\Book1_condforms.xls", L"" );

xlsgen::IXlsWorksheetPtr wksht = wbk->WorksheetByIndex[2];

// expected to return 3, we have 3 separate conditional formatting objects in this worksheet
int nbCondForms = wksht->ConditionalFormattings->Count;
if (nbCondForms != 3)
  return;

// get a condition, by index
xlsgen::IXlsConditionalFormattingPtr cf1 =  wksht->ConditionalFormattings->Item[1];
// get the second rule in that conditional formatting
xlsgen::IXlsConditionalFormattingPtr cf1_next = cf1->AdditionalCondition();

_bstr_t range1 = cf1_next->Areas; // returns L"R5C5:R8C5"

// blue background
xlsgen::IXlsStylePtr s = wksht->NewStyle();
s->Pattern->BackgroundColor = 0x0000FF;

// change the style of the condition
cf1_next->Style = s;

// change the formula of the condition
cf1_next->FormulaCondition->Formula = L"$A$1 > 1";



// the second conditional formatting has two rules, one is cell-based the other formula-based
// the third conditional formatting has one rule, and the rule is cell-based
//
//
//

// second condition, by index
xlsgen::IXlsConditionalFormattingPtr cf2 =  wksht->ConditionalFormattings->Item[2];
_bstr_t range2 = cf2->Areas; // returns L"R5C5:R8C5"

// second condition, by location
xlsgen::IXlsConditionalFormattingPtr cf2_bis = wksht->ConditionalFormattings->ItemInCell[6][5];
_bstr_t range2_bis = cf2_bis->Areas; // returns L"R5C5:R8C5"

// third condition
xlsgen::IXlsConditionalFormattingPtr cf3 =  wksht->ConditionalFormattings->Item[3];
_bstr_t range3 = cf3->Areas; // returns L"R4C3:R7C3"

// delete the one and only condition rule : it will delete the cond formatting object
cf3->Delete();

int nbCondForms_afterdelete1 = wksht->ConditionalFormattings->Count;
if (nbCondForms_afterdelete1 != 2)
  ::MessageBox(NULL,"expected nbCondForms_afterdelete1 == 2","error",MB_OK);

// delete only one of the two condition rules : it will not delete the cond formatting object
cf2_bis->Delete();

int nbCondForms_afterdelete2 = wksht->ConditionalFormattings->Count;
if (nbCondForms_afterdelete2 != 2)
  ::MessageBox(NULL,"expected nbCondForms_afterdelete2 == 2","error",MB_OK);

wbk->Close();

 

xlsgen documentation. © ARsT Design all rights reserved.