CODEWITHSUNDEEP
javaexcelapache-poixlsx
CHHIBI AMOR
CHHIBI AMOR

Reputation: 1265

conditionally Coloring rows with Apache POI

I work by Apache poi API, I want to color the lines conditionally , the rule is alternating color after 5 lines: the line 1-5 red, line 6-10 blue, line 11-15 red. so on.

this rule :

 ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW(),2)");

alternate row in a different color.

how I can write my rule ??

Upvotes: 2

Views: 4197

Answers (2)

rgettman
rgettman

Reputation: 178243

You'll need two rules, one for red and one for blue. Subtract 1 from ROW() so that row 11 becomes 10 and row 15 becomes 14, so that the result of the mod is less than 5. Also, row 10 becomes 9 and the mod is greater than or equal to 5, so it will become blue. The pattern repeats after 10 rows, so that's why the mod operand is 10.

ConditionalFormattingRule red = sheetCF.createConditionalFormattingRule(
    "MOD(ROW() - 1, 10) < 5");
FontFormatting ffRed = red.createFontFormatting();
ffRed.setFontColorIndex(IndexedColors.RED.index);

ConditionalFormattingRule blue = sheetCF.createConditionalFormattingRule(
    "MOD(ROW() - 1, 10) >= 5");
FontFormatting ffBlue = blue.createFontFormatting();
ffBlue.setFontColorIndex(IndexedColors.BLUE.index);

Then you can add the conditional formatting to your sheet with your SheetConditionalFormatting object, with an appropriate CellRangeAddress.

For more detail, see the Quick Guide on Conditional Formatting.

Upvotes: 3

G&#225;bor Bakos
G&#225;bor Bakos

Reputation: 9100

In my opinion the following (or something similar, probably with (ROW()+1) or (ROW()-1) instead of ROW()) should work:

ConditionalFormattingRule rule1 = sheetCF.createConditionalFormattingRule("MOD(ROW() / 5,2)");

Upvotes: 0

Related Questions