× 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.



Sorry, I have been out. Let me dig something up.

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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.