Re: Fw: Re: POI / Excel and setting borders around a range of cells -- RPG400-L

Mark, I realize now that your code specifically applies to POI 3.16.

Paul

On 2017-03-10 12:14, Paul Therrien wrote:

Mark,

Your code and examples are excellent, but I am having an issue. I have
downloaded POI 3.15 and cannot find the class
'org.apache.poi.ss.usermodel.BorderExtent'.

I find the 'org.apache.poi.ss.usermodel.BorderStyle', but not the
.BorderExtent. I have gone though the POI folder structure and just do
not see a 'BorderExtent.class' (and neither does the IBM i when i try to
execute the code.).

Do I need to move uptp POI 3.16? (This seems to be a beta release).

Paul

On 2017-03-10 09:17, Mark Murphy/STAR BASE Consulting Inc. wrote:

Originally found this on the Java list, cross posting from there. concerning the Enums. If you are creating something new, do not use the int or short constants if an Enum is available. It will make upgrading things harder in the future. So if you do use the ReginUtil Classes, make sure you use the versions with the Enums. I do not recommend those classes though as they generate extra unused styles in your worksheet as you build up the borders. PropertyTemplate applies all borders to each cell in a single shot so no intermediate styles are created. I have had 80 or 90 styles in my spreadsheets when all I really was using was about a dozen.

Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx

-----Forwarded by Mark Murphy/STAR BASE Consulting Inc. on 03/10/2017 09:10AM -----
To: Java Programming on and around the IBM i <java400-l@xxxxxxxxxxxx>
From: "Mark Murphy/STAR BASE Consulting Inc." <mmurphy@xxxxxxxxxxxxxxx>
Date: 03/10/2017 09:05AM
Subject: Re: POI / Excel and setting borders around a range of cells

I would use PropertyTemplate. There is a Java Example here: https://poi.apache.org/spreadsheet/quick-guide.html#DrawingBorders

One issue with Scott's prototypes is that at POI 3.15 they started converting int constants to Enums. and at 3.17, the methods deprecated in 3.15 will be removed. Enums are simply a class and can be defined in Java as if they were just another class. You need a few other definitions to access them though.

This defines a way to retrieve a specific enum given a string.

// Enum Names
dcl-c BorderExtent_ENUM
'org.apache.poi.ss.usermodel.BorderExtent';
dcl-c BordetStyle_ENUM
'org.apache.poi.ss.usermodel.BorderStyle';

// Enum Objects
dcl-s BorderExtent
Object(*Java: BorderExtent_ENUM);
dcl-s BorderStyle
Object(*Java: BorderStyle_ENUM);

// Enum Prototypes
dcl-pr BorderExtent_valueOf
Like(BorderExtent)
ExtProc(*Java: BorderExtent__ENUM:
'valueOf')
Static;
value Like(jString) const;
end-pr;

dcl-pr BorderStyle_valueOf
Like(BorderStyle)
ExtProc(*Java: BorderStyle__ENUM:
'valueOf')
Static;
value Like(jString) const;
end-pr;

// Class Constants
dcl-c CellRangeAddress_CLASS
'org.apache.poi.ss.util.CellRangeAddress';
dcl-c PropertyTemplate_CLASS
'org.apache.poi.ss.util.PropertyTemplate';

// Objects
dcl-s CellRangeAddress
Object(*Java: CellRangeAddress_CLASS);
dcl-s PropertyTemplate
Object(*Java: PropertyTemplate_CLASS);

// Prototypes
dcl-pr new_CellRangeAddress
Like(CellRangeAddress)
ExtProc(*Java: CellRangeAddress_CLASS:
*Constructor);
firstRow Like(jint) value;
lastRow Like(jint) value;
firstCol Like(jint) value;
lastCol Like(jint) value;
end-pr;

dcl-pr new_PropertyTemplate
Like(PropertyTemplate)
ExtProc(*Java: PropertyTemplate_CLASS:
*Constructor);
end-pr;

dcl-pr PropertyTemplate_drawBorders
ExtProc(*Java:PropertyTemplate_CLASS:
'drawBorders');
range Like(CellRangeAddress) const;
type Like(BorderStyle) const;
extent Like(BorderExtent) const;
end-pr;

dcl-pr PropertyTemplate_applyBorders
ExtProc(*Java: PropertyTemplate_CLASS:
'applyBorders');
sheet Like(Sheet) const;
end-pr;

To draw single outside borders around cells B2:D4

dcl-s range Like(CellRangeAddress);
dcl-s pt Like(PropertyTemplate);
dcl-s sh Like(Sheet);
dcl-s bType Like(BorderStyle);
dcl-s bExt Like(BorderExtent);

bType = BorderStyle_valueOf(new_String('MEDIUM'));
bExt = BorderExtent_valueOf(new_String('OUTSIDE'));
range = new_CellRangeAddress(1:3:1:3);
pt = new_PropertyTemplate();
PropertyTemplate_drawBorders(pt: range: bType: bExt);
PropertyTemplate_applyBorders(pt: sh);

The same set of borders can be stamped on multiple sheets if you wish.

Mark Murphy
Atlas Data Systems
mmurphy@xxxxxxxxxxxxxxx

-----Paul Therrien <paultherrien@xxxxxxxxxxxxxxxxxx> wrote: -----
To: java400-l@xxxxxxxxxxxx
From: Paul Therrien <paultherrien@xxxxxxxxxxxxxxxxxx>
Date: 03/09/2017 03:44PM
Subject: POI / Excel and setting borders around a range of cells

Has anyone used POI to generate Excel from RPG and implemented the
RegionUtil.setBorderBottom, .setBorderTop, .etc?

I have been trying to mimic Scott Klement's HSSF prototypes and code for
this function, but I constantly failing.

It appears that there is an object RegionUtil that has methods for
setting border attributes. I am not certain how to invoke RegionUtil
and its methods.

Any help would be appreciated.

I am using POI 3.15.

Paul
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.
--
This is the Java Programming on and around the IBM i (JAVA400-L) mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.