In the newsletter from September 16, 2004, I demonstrated how to create an Excel spreadsheet from an ILE RPG program. I have since received numerous requests to provide more information about the cell styles that can be applied in HSSF.

BUG FOUND IN PREVIOUS SOURCE CODE
Before I delve into cell styles, there was a bug in the code from the previous newsletter. I forgot to close the spreadsheet after writing it! The original code for the HSSF_save() routine in the HSSFR4 service program looked like this:

    P hssf_save       B                   EXPORT
    D hssf_save       PI
    D   peBook                            like(HSSFWorkbook)
    D   peFilename                1024A   const varying

    D wwStr           s                   like(jString)
    D wwFile          s                   like(jFileOutputStream)
     /free
        wwStr = new_String(peFilename);
        wwFile = new_FileOutputStream(wwStr);
        HSSFWorkbook_write(peBook: wwFile);
        hssf_freeLocalRef(wwFile);
        hssf_freeLocalRef(wwStr);
     /end-free
    P                 E

The "new_FileOutputStream" line of code will open a new stream file for output, and the HSSFWorkbook_write() routine will write the workbook to the stream file. But, somehow, I forgot to close it!

To fix it, I needed to add the prototype for the close() method of the FileOutputStream class to my HSSF_H source member. The prototype follows:

     *-----------------------------------------------------------------
     * FileOutputStream_close():  Close a stream file that was opened
     *            for output.
     *-----------------------------------------------------------------
    D FileOutputStream_close...
    D                 pr                  EXTPROC(*JAVA
    D                                     :'java.io.FileOutputStream'
    D                                     :'close')

This prototype is then used in the hssf_save() routine to close the stream file after it has been written, as follows:

    P hssf_save       B                   EXPORT
    D hssf_save       PI
    D   peBook                            like(HSSFWorkbook)
    D   peFilename                1024A   const varying

    D wwStr           s                   like(jString)
    D wwFile          s                   like(jFileOutputStream)
     /free
        wwStr = new_String(peFilename);
        wwFile = new_FileOutputStream(wwStr);
        HSSFWorkbook_write(peBook: wwFile);
        FileOutputStream_close(wwFile);
        hssf_freeLocalRef(wwFile);
        hssf_freeLocalRef(wwStr);
     /end-free
    P                 E

Thank you, Thomas Pataki, for pointing out this bug.

DOCUMENTATION FROM APACHE
Reference documentation called "javadocs" can be found by navigating to http://jakarta.apache.org/poi and clicking the "javadocs" link in the navigation menu on the left side of the page. In the main frame of the javadocs page, it will list several different categories of documentation. Everything that I refer to in this article falls into the org.apache.poi.hssf.usermodel category. The classes in this category are referred to as the "user model" because they are the ones that are intended to be called by application programs.

To find the "users guide" style documentation, you should start at http://jakarta.apache.org/poi, then click the link to the HSSF project. On the HSSF project's page, there are links such as "Overview," "Quick Guide", and "HOWTO." Click on one of these links for the documentation.

CELL STYLES, OVERVIEW
A "cell style" is an object that stores attributes about how text in a cell is displayed in Excel. It contains settings for things such as font, color, indentation, word wrap, number formatting, borders, and alignment. In HSSF, a cell style is implemented in the HSSFCellStyle class.

There are two "helper" classes that go with HSSFCellStyle. They are HSSFFont, which is used to set information about the font that is used to render the text, and HSSFDataFormat, which is used to specify the way a number is formatted. HSSFDataFormat is analogous to an edit code or edit word in RPG.

To find out information about cell styles, navigate to the javadocs page, as described above, and click on "org.apache.poi.hssf.usermodel." Next, click on "HSSFCellStyle."

The main frame of the resulting page will have a short description of the HSSFCellStyle class followed by a table that's titled "Field Summary." The fields listed here are the Java equivalents of named constants. RPG only gives you the ability to call methods, not to access fields, so in order to have the same functionality in the RPG source, you have to define your own named constants. Many of them have been defined for you in the HSSF_H source member that I've provided. There is also a "field detail" section that's lower on this page. It explains what each constant means. Unfortunately, they do not tell you what the values of these constants are. You have to download the source code for POI to find out.

The source code contains a "src" directory to distinguish the source code from the documentation and other included material. Inside that directory is the "Java" directory for the Java source code. Inside that are directories that correspond to the package. In this case, the package is called "org.apache.jakarta.poi.hssf.usermodel" and the class is called HSSFCellStyle. For example, if you have unzipped the POI source code into C:\POI on your PC, then you'll find answers in the HSSFCellStyle.java file in the following directory:

C:\POI\SRC\java\org\apache\poi\hssf\usermodel

In that file, you'll see code like the following:

   /**  Thin forward diagonal */
   public final static short     THIN_FORWARD_DIAG   = 14 ;

   /**  Squares */
   public final static short     SQUARES             = 15 ;

   /**  Diamonds */
   public final static short     DIAMONDS            = 16 ;

This converts to RPG code that looks like this:

     ** Thin Forward Diagonal
    D THIN_FORWARD_DIAG...
    D                 c                   14

     ** Squares
    D THIN_FORWARD_DIAG...
    D                 c                   15

     ** Diamonds
    D THIN_FORWARD_DIAG...
    D                 c                   16

WORKING WITH CELL STYLES
Cell styles are created inside a workbook and, once created, can be applied to individual cells to change the way that they're displayed. To add a new cell style to a workbook, you code the following:

lgHeading = HSSFWorkbook_createCellStyle(book);

This new cell style contains default settings. If you do not change it, using this cell style will provide the same formatting that you'll have on cells that do not have a cell style applied.

In the "javadocs," there are "method summary" and "method detail" sections that explain the methods that you can call in a cell style. For example, if you want to enable word wrap, you call the setWrapText method of the HSSFCellStyle class.

To do that in RPG, you'll need to create a prototype like this one:

    D HSSFCellStyle_setWrapText...
    D                 PR                  EXTPROC(*JAVA
    D                                     :'org.apache.poi.hssf.-
    D                                     usermodel.HSSFCellStyle'
    D                                     :'setWrapText')
    D   wrapped                       N   value

Chapter 11 of the WebSphere Development Studio ILE RPG Programmer's Guide has a lot of information about how to create these prototypes, including a table that explains how to map Java parameters to RPG data types.

The following code creates a new cell style called "Text" in a workbook. It modifies the style so that text will be left-aligned and words will wrap if necessary:

Text = HSSFWorkbook_createCellStyle(book); HSSFCellStyle_setAlignment(Text: ALIGN_LEFT); HSSFCellStyle_setWrapText(Text: *ON);

DATA FORMATS
As I mentioned above, data formats dictate how a number will be formatted when displayed on the screen. Two types of data formats are available; those that are built-in and those that need to be created.

The built-in data formats are part of Excel and are referenced as a number in your XLS file. No special code needs to be added to the file in order for Excel to interpret it. It's similar to an edit code in RPG, though when you specify it in your code, it actually looks more like an edit word.

If you look up the HSSFDataFormat class in the javadocs, it shows the built- in formats at the top of the page.

Custom data formats are even more like edit words. They consist of an expression that tells Excel how the number is to be formatted. When you create a new data format in the workbook, instructions are added to the workbook that tell Excel how to format numbers that use this format. A number that represents this new data format is used to refer to it from your cell styles.

The code for both built-in and custom data formats can be written the same way. The HSSFDataFormat will automatically use a built-in when one matches your format string and will create a custom one when it doesn't. In either case, a number is returned that you will use in your cell style.

The following code creates a cell style called "Numeric." It looks up a data format that matches the formatting string for the number. If the format is not a built-in, a new data format will be created. In either case, a reference number is returned that is then set in the cell style:

Numeric = HSSFWorkbook_createCellStyle(book); DataFmt = HSSFWorkbook_createDataFormat(book); TempStr = new_String('#,##0.00'); RefNo = HSSFDataFormat_getFormat(DataFmt: TempStr); HSSFCellStyle_setDataFormat(Numeric: RefNo);

Every time you create a new data format, you type those same three lines of code. To save myself a little bit of time, I've added the following routine to the HSSFR4 service program. Now, when I want to create a data format, I can simply code the following:

Numeric = HSSFWorkbook_createCellStyle(book); Refno = hssf_createDataFormat(book: '#,##0.00'); HSSFCellStyle_setDataFormat(Numeric: RefNo);

In Excel, dates are represented as the number of days since January 1, 1900. The HSSFR4 service program contains routines that can be used to convert from RPG's date format to the number that Excel requires and vice-versa. To make a date appear properly in the spreadsheet, you apply the proper data format. In the following example, the date will be formatted the style that's typically used in the United States:

Dates = HSSFWorkbook_createCellStyle(book); DateFmt = hssf_createDataFormat(book: 'm/d/yy'); HSSFCellStyle_setDataFormat(Dates: DateFmt);

FONTS AND THEIR ATTRIBUTES
An HSSFFont object can be used to create a font that controls how text in each cell of the spreadsheet is displayed. In addition to changing fonts, such as changing from "Arial" to "Courier," the font object also controls the size of a font and whether it's bold, underlined, superscript, underlined, or printed in red.

You create an HSSFFont object with default attributes by calling the HSSFWorkbook_createFont() method. Once a new font has been created, you change its attributes. The following example demonstrates how to create a 16-point Courier font and set it as the active font in the ColHeading cell style:

ColHeading = HSSFWorkbook_createCellStyle(book); ChFont = HSSFWorkbook_createFont(book); TempStr = new_String('Courier'); HSSFFont_setFontName(ChFont: TempStr); HSSFFont_setFontHeightInPoints(ChFont: 16); HSSFCellStyle_setFont(ColHeading: ChFont);

Again, to simplify the creation of fonts, I've created a utility routine in the HSSFR4 service program. The prototype for the routine follows:

    D hssf_CreateFont...
    D                 PR                  like(HSSFFont)
    D   peBook                            like(HSSFWorkbook) const
    D   peName                     100A   varying const options(*omit)
    D   pePointSize                  5I 0 const options(*omit)
    D   peBold                       5I 0 const options(*omit)
    D   peUnderline                  1A   const options(*omit)
    D   peItalic                     1N   const options(*omit)
    D   peStrikeout                  1N   const options(*omit)
    D   peColor                      5I 0 const options(*omit)
    D   peTypeOffset                 5I 0 const options(*omit)

Most of the parameters to the routine are omissible, meaning that if you don't want to set that attribute of the font, you can pass the special value *OMIT and it will be ignored.

The following code demonstrates the creation of the Arial font so that it is small, bold, red, and superscripted:

        ChFont = HSSF_createFont(book
                                : 'Arial': 8
                                : BOLDWEIGHT_BOLD
                                : *omit
                                : *omit
                                : *omit
                                : COLOR_RED
                                : SS_SUPER );

I have updated the source code from the September 16 article so that these new routines are available in the service program, and I have changed the code so that it closes the stream file properly. You can download the updated code from the following link:
http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip.

More information about calling Java methods from RPG programs can be found in Chapter 11 of the "WebSphere Development Studio ILE RPG Programmer's Guide," which is in the Information Center at the following link:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c0925074.pdf

The following is a link to the September 16, 2004, issue of this newsletter, where I first demonstrated creating an Excel file in RPG:
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=19274

The following is a link to the April 8, 2004, issue of this newsletter, where I demonstrated routines for converting RPG dates to Excel dates:
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=18396