| ||||
|
Q: I've been enjoying your articles about creating Excel documents from RPG, but have not been able to figure out how to set the background color of a cell. Is this possible with HSSF? A: Yes. In fact, in addition to setting the color of the background, you can set different patterns that are displayed in the cells. The colors that fill a cell are call "fill colors," and the pattern is called a "fill pattern." So, for example, if you wanted to have a cell with yellow polka dots on a black background, the fill's background color would be black, the fill's foreground color would be yellow, and the pattern would be polka dots. The following are the RPG prototypes used to set the foreground color, background color, and pattern of a fill:
D HSSFCellStyle_setFillBackgroundColor...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillBackgroundColor')
D bgcolor like(jShort) value
D HSSFCellStyle_setFillForegroundColor...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillForegroundColor')
D fgcolor like(jShort) value
D HSSFCellStyle_setFillPattern...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillPattern')
D fillpattern like(jShort) value
Don't confuse the fill's foreground color with the color of the text in the cell. The text's color is set by changing the color of the font and is not related to the fill colors at all. In HSSF, all of the colors are stored in a color palette. There's a standard color palette that Excel uses by default, and you can also create your own custom palette. A palette is really just an array of colors that are loaded into the document. When you call the prototypes that I listed above, you pass an index into the color palette. Excel will take that index, look up the correct color, and display it. These same color indexes can be used for font colors as well as fill colors. The following sample program demonstrates setting the fill and font colors in an HSSF document:
* Demonstration of using colors in cell styles in HSSF
*
* To compile:
* Make sure you've already created HSSFR4. See the instructions
* on that source member for details.
*
* CRTBNDRPG PGM(COLORDEMO) SRCFILE(xxx/xxx) DBGVIEW(*LIST)
*
*
H DFTACTGRP(*NO)
H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
H THREAD(*SERIALIZE)
H BNDDIR('HSSF')
/copy tipsnl/qrpglesrc,hssf_h
D BIG_SPOTS...
D C 9
D SOLID_FOREGROUND...
D C 1
D HSSFCOLOR_AQUA...
D C 49
D HSSFCOLOR_ORANGE...
D C 53
D HSSFCOLOR_BLACK...
D C 8
D HSSFCOLOR_WHITE...
D C 9
D HSSFCellStyle_setFillBackgroundColor...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillBackgroundColor')
D bgcolor like(jShort) value
D HSSFCellStyle_setFillForegroundColor...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillForegroundColor')
D fgcolor like(jShort) value
D HSSFCellStyle_setFillPattern...
D PR ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFCellStyle':
D 'setFillPattern')
D fillpattern like(jShort) value
D book s like(HSSFWorkbook)
D sheet s like(HSSFSheet)
D row s like(HSSFRow)
D style s like(HSSFCellStyle)
D font s like(HSSFFont)
/free
hssf_begin_object_group(100);
book = new_HSSFWorkbook();
sheet = hssf_newSheet(book: 'new sheet');
row = HSSFSheet_createRow(sheet: 1);
// Aqua background
style = HSSFWorkbook_createCellStyle(book);
HSSFCellStyle_setFillBackgroundColor( style
: HSSFColor_AQUA);
HSSFCellStyle_setFillPattern(style: BIG_SPOTS);
hssf_text(row: 1: 'X': style);
// Orange "foreground" (foreground meaning the fill color,
// rather than the font color.)
style = HSSFWorkbook_createCellStyle(book);
HSSFCellStyle_setFillForegroundColor( style
: HSSFColor_ORANGE);
HSSFCellStyle_setFillPattern(style: SOLID_FOREGROUND);
hssf_text(row: 2: 'X': style);
// White text on a black background
// Note that the text color is a "font color."
// It is NOT the foreground color of the cell.
style = HSSFWorkbook_createCellStyle(book);
HSSFCellStyle_setFillForegroundColor( style
: HSSFColor_BLACK);
HSSFCellStyle_setFillPattern(style: SOLID_FOREGROUND);
font = HSSFWorkbook_createFont(book);
HSSFFont_setColor(font: HSSFColor_White);
HSSFCellStyle_setFont(style: font);
hssf_text(row: 3: 'X': style);
// save results to a file
hssf_save(book: '/tmp/filltest.xls');
// cleanup and exit
hssf_end_object_group();
*inlr = *on;
/end-free
In the above example, I have defined a few color indexes as named constants. You can get a list of other available colors at the following link: http://jakarta.apache.org/poi/apidocs/constant-values.html#org.apache.poi.hssf.util.HSSFColor.AQUA.index |