Re: Fwd: POI / Excel and setting borders around a range of cells

Hello Paul,

(continuing discussion originally on Midrange-L)

On 3/9/17 3:25 PM, Paul Therrien wrote:

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

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.

First, in my version of HSSF_H, I already have definitions for CellRangeAddress (which will be needed for RegionUtil), but I don't remember if I published those publicly or not, so... just in case you don't have them, they look like this:


D CELLRANGEADDRESS_CLASS...
D c 'org.apache.poi.ss.util-
D .CellRangeAddress'
D CellRangeAddress...
D S O CLASS(*JAVA
D : CELLRANGEADDRESS_CLASS)


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* new_CellRangeAddress()
* Creates a new CellRangeAddress object, identifying a range
* of cells on a spreadsheet
*
* firstRow = starting row number
* lastRow = ending row number
* firstCol = starting column number
* lastCol = ending column number
*
* returns new CellRangeAddress object
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D new_CellRangeAddress...
D PR like(CellRangeAddress)
D ExtProc(*JAVA
D : CELLRANGEADDRESS_CLASS
D : *CONSTRUCTOR)
D firstRow like(jint) value
D lastRow like(jint) value
D firstCol like(jint) value
D lastCol like(jint) value


For RegionUtil in POI 3.15 (some of this will change with 3.16 when it is released) the definitions are:


D REGIONUTIL_CLASS...
D c 'org.apache.poi.ss.util-
D .RegionUtil'


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* RegionUtil_setBorderXxxxxx(): Set the border for a range
*
* border = border style to set
* region = region to set the style in
* sheet = the sheet in which to set the border
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D RegionUtil_setBorderLeft...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setBorderLeft')
D static
D border 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setBorderRight...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setBorderRight')
D static
D border 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setBorderTop...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setBorderTop')
D static
D border 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setBorderBottom...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setBorderBottom')
D static
D border 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const


*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
* RegionUtil_setXxxxxBorderColor(): Set the color of a border
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
D RegionUtil_setLeftBorderColor...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setLeftBorderColor')
D static
D color 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setRightBorderColor...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setRightBorderColor')
D static
D color 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setTopBorderColor...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setTopBorderColor')
D static
D color 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const
D RegionUtil_setBottomBorderColor...
D PR extproc(*java
D :REGIONUTIL_CLASS
D :'setBottomBorderColor')
D static
D color 10i 0 value
D region like(CellRangeAddress) const
D sheet like(SSSheet) const



Please notice that these are STATIC routines, so you do not need to pass a RegionUtil object to them. All you have to do is call them, like this:

// Create a range of cells from rows 10-20, cols 1-5:

range = new_CellRangeAddress(10: 20: 1: 5);

// Give the whole region a red, dashed border

RegionUtil_setBorderTop(BORDER_DASHED: range: Sheet);
RegionUtil_setBorderBottom(BORDER_DASHED: range: Sheet);
RegionUtil_setBorderLeft(BORDER_DASHED: range: Sheet);
RegionUtil_setBorderRight(BORDER_DASHED: range: Sheet);

RegionUtil_setTopBorderColor(COLOR_RED: range: Sheet);
RegionUtil_setBottomBorderColor(COLOR_RED: range: Sheet);
RegionUtil_setLeftBorderColor(COLOR_RED: range: Sheet);
RegionUtil_setRightBorderColor(COLOR_RED: range: Sheet);


HTH