× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.