Re: hssf poi conditional formatting.

Maybe this has some potential to be a GitHub project, or two. The second being a project that creates RPG prototypes from a Java Package.

Mark Murphy
STAR BASE Consulting, Inc.
mmurphy@xxxxxxxxxxxxxxx


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


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.