Re: hssf poi conditional formatting.

This code implies that you already have a workbook and sheet created and that you have a style in a field named sty_num.

dcl-s row Like(ssRow);
dcl-s sheetCf Like(SheetConditionalFormatting);
dcl-s rule Like(ConditionalFormattingRule);
dcl-s regions Like(CellRangeAddress) Dim(1);
dcl-s fmtPattern Like(PatternFormatting);
dcl-s fmtFont Like(FontFormatting);

// Constants I use here (and have defined, not sure if I defined them or scott did)
dcl-c COLOR_DARK_RED 16;
dcl-c COLOR_ROSE 45;
dcl-c HSSF_PATTERN_SOLID_FOREGROUND 1;

rowNum = 0;
row = ssSheet_createRow(sheet: rowNum);
ss_num (row: 1: 1: sty_num);
rowNum += 1;
row = ssSheet_createRow(sheet: rowNum);
ss_num (row: 1: 2: sty_num);


sheetCf = ssSheet_getSheetConditionalFormatting(sheet);
rule = ssSheetCF_createConditionalFormattingRule(sheetCf:
new_String('=if(B2 = 2, TRUE, FALSE)'));
fmtPattern = ssRule_createPatternFormatting(rule);
ssRulePattern_setFillPattern(fmtPattern:
HSSF_PATTERN_SOLID_FOREGROUND);
ssRulePattern_setFillBackgroundColor(fmtPattern: COLOR_ROSE);
fmtFont = ssRule_createFontFormatting(rule);
ssRuleFont_setFontColorIndex(fmtFont: COLOR_DARK_RED);
regions(1) = new_CellRangeAddress(1:2:1:1);
ssSheetCf_addConditionalFormatting(sheetCf: regions: rule);

This shows the fill pattern bug in Excel, even though the pattern is solid foreground, you need to set the background color. If you use this pattern in a cell style, you need to set the foreground color instead. In the cell style case it is helpful to set the background color to SYSTEM_AUTO


Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----broehmer@xxxxxxxxxxxxxxx wrote: -----
To: "RPG programming on the IBM i \(AS/400 and iSeries\)" <rpg400-l@xxxxxxxxxxxx>
From: broehmer@xxxxxxxxxxxxxxx
Date: 07/13/2016 10:43AM
Subject: Re: hssf poi conditional formatting.


Mark,

Do you have any example calc specs that show the sequence of setting up a
rule for a cell?

-B







From: "Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i \(AS/400 and iSeries\)"
<rpg400-l@xxxxxxxxxxxx>
Date: 07/08/2016 08:46 AM
Subject: Re: hssf poi conditional formatting.
Sent by: "RPG400-L" <rpg400-l-bounces@xxxxxxxxxxxx>



I've done it, I just have to dig up where. Some caveats first, I am using
POI v3.14. POI v3.15 has refactored some of the constants into Enums, so
if you go there you may need to do some refactoring to get everything
working again. I haven't tested that theory yet.

Here are some prototypes I added to Scotts file to accomplish this:

dcl-c CELLRANGEADDRESS_CLASS
'org.apache.poi.ss.util.CellRangeAddress';
dcl-c CONDITIONALFORMATTINGRULE_CLASS
'org.apache.poi.ss.usermodel.ConditionalFormattingRule';
dcl-c FONTFORMATTING_CLASS
'org.apache.poi.ss.usermodel.FontFormatting';
dcl-c PATTERNFORMATTING_CLASS
'org.apache.poi.ss.usermodel.PatternFormatting';
dcl-c SHEETCONDITIONALFORMATTING_CLASS
'org.apache.poi.ss.usermodel.SheetConditionalFormatting';

dcl-s CellRangeAddress Object(*JAVA: CELLRANGEADDRESS_CLASS);
dcl-s ConditionalFormattingRule
Object(*JAVA:
CONDITIONALFORMATTINGRULE_CLASS);
dcl-s FontFormatting Object(*JAVA: FONTFORMATTING_CLASS);
dcl-s PatternFormatting Object(*JAVA: PATTERNFORMATTING_CLASS);
dcl-s SheetConditionalFormatting
Object(*JAVA:
SHEETCONDITIONALFORMATTING_CLASS);

*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSSheet_getConditionalFormatting(): get conditional formatting
* object for a given sheet
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSSheet_getSheetConditionalFormatting...
D PR like(SheetConditionalFormatting)
D extproc(*JAVA
D : SHEET_CLASS
D :
'getSheetConditionalFormatting')


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSSheetCF_createConditionalFormattingRule(): create conditional
* formatting rule for a given sheet conditional formatting
* object
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSSheetCF_createConditionalFormattingRule...
D PR like(ConditionalFormattingRule)
D extproc(*JAVA
D :
SHEETCONDITIONALFORMATTING_CLASS
D :
'createConditionalFormattingRule')
D string Like(jString) const


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSSheetCF_createConditionalFormattingRule(): create conditional
* formatting rule for a given sheet conditional formatting
* object
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSSheetCF_addConditionalFormatting...
D PR like(jint)
D extproc(*JAVA
D :
SHEETCONDITIONALFORMATTING_CLASS
D : 'addConditionalFormatting')
D regions Like(CellRangeAddress) Dim(100)
D Options(*Varsize) const
D rule Like(ConditionalFormattingRule)
D const


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRule_createPatternFormatting(): create pattern formatting
* object for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRule_createPatternFormatting...
D PR like(PatternFormatting)
D extproc(*JAVA
D :
CONDITIONALFORMATTINGRULE_CLASS
D : 'createPatternFormatting')


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRulePattern_setFillPattern(): set fill pattern
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRulePattern_setFillPattern...
D PR extproc(*JAVA
D : PATTERNFORMATTING_CLASS
D : 'setFillPattern')
D pattern Like(jshort) value


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRulePattern_setFillForegroundColor(): set pattern foreground
color
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRulePattern_setFillForegroundColor...
D PR extproc(*JAVA
D : PATTERNFORMATTING_CLASS
D : 'setFillForegroundColor')
D color Like(jshort) value


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRulePattern_setFillBackgroundColor(): set pattern foreground
color
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRulePattern_setFillBackgroundColor...
D PR extproc(*JAVA
D : PATTERNFORMATTING_CLASS
D : 'setFillBackgroundColor')
D color Like(jshort) value


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRule_createFontFormatting(): create font formatting
* object for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRule_createFontFormatting...
D PR like(FontFormatting)
D extproc(*JAVA
D :
CONDITIONALFORMATTINGRULE_CLASS
D : 'createFontFormatting')


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRuleFont_setFontColorIndex(): set font color
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRuleFont_setFontColorIndex...
D PR extproc(*JAVA
D : FONTFORMATTING_CLASS
D : 'setFontColorIndex')
D color Like(jshort) value


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRuleFont_setFontHeight(): set font height
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRuleFont_setFontHeight...
D PR extproc(*JAVA
D : FONTFORMATTING_CLASS
D : 'setFontHeight')
D height Like(jint) value


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* SSRuleFont_setFontStyle(): set font to italic and/or bold
* for a given formatting rule
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D SSRuleFont_setFontStyle...
D PR extproc(*JAVA
D : FONTFORMATTING_CLASS
D : 'setFontStyle')
D italic 1N value
D bold 1N value

Be careful though, there is some inconsistency between style fills and
conditional formatting fills. In a style, the Solid Fill uses the
Foreground color to give the cell a solid background. I believe that in
conditional formatting, the solid fill uses the background color to give
the cell a solid background, but for the rest of the fill patterns, the
foreground and background colors do the same thing as they do for the cell
style. I believe this is a strangeness attributable to Excel rather than
POI. If it doesn't do what you expected, you may have to tinker with the
foregraund and background colors for the fill.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


-----"Roche, Bob" <broche@xxxxxxxxxxxxxxxxx> wrote: -----
To: "rpg400-l@xxxxxxxxxxxx" <rpg400-l@xxxxxxxxxxxx>
From: "Roche, Bob" <broche@xxxxxxxxxxxxxxxxx>
Date: 07/07/2016 03:46PM
Subject: hssf poi conditional formatting.


Does anyone have sample of using conditional formatting in RPG when
building a spreadsheet using HSSF POI? I am trying to work thru a java
sample and converting it to RPGLE using RDI java method call prototype
creator, but it is a slow painful process so far. I thought I would check
to see if any already created this particular wheel.

I have a sheet with calculations. If the value is negative make it red and
bold. Sounds easy I thought.
Thanks for any help.