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
midrange.com
