For the past four years, I've been writing articles about creating Excel spreadsheets using the HSSF Java classes. I've demonstrated many of the features of HSSF, including reading a spreadsheet, creating a spreadsheet, formatting cells, inserting formulas, and working with fonts. This article explains yet another interesting feature: the ability to put pictures in your spreadsheet. Why would you want to do that? Perhaps you'd like to put your company logo in the spreadsheet, or maybe you have a photograph that belongs with your business data. HSSF lets you insert .JPG and .PNG pictures with relative ease.

Introduction to HSSF and POI

HSSF is part of POI, an open-source toolkit for working with Microsoft Office documents. It is produced by Apache Software Foundation (ASF), the same foundation that creates the Apache HTTP server (which forms the core of the IBM HTTP for i), as well as other popular open-source tools such as Tomcat, SpamAssassin, and Struts.

The name POI originally stood for "Poor Obfuscation Implementation," a humorous name that poked fun at the file formats Microsoft used for Office. Later, developers of the POI project deemed that name unprofessional. Now the name is simply POI and doesn't stand for anything. Likewise, HSSF originally stood for "Horrible Spread Sheet Format" but now is simply HSSF.

The POI project is also working on tools to read and write documents for Word, PowerPoint, Visio, and Publisher. However, at this point, these tools are not complete enough to be useful. The Excel support provided by HSSF is much more complete! The POI team is also developing something called XML Spread Sheet Format (XSSF), which will be compatible with the newer XML format available in the latest versions of Excel. The first version of POI with XSSF support is in beta testing stages now and is expected to be released some time in 2009.

POI is implemented as a set of Java classes that can run on any computer platform. Since ILE RPG is capable of prototyping and calling Java methods, you can use them from your RPG programs, and that means you can generate native Excel documents on the IBM i platform for free from your RPG programs! For more information about using HSSF from RPG, please see the links at the end of this article.

Images in HSSF

The HSSF documentation claims that HSSF will eventually support the Windows Metafile (.WMF), Enhanced Metafile (.EMF), Macintosh PICT, and Windows Device Independent Bitmap (.DIB/.BMP) formats in addition to the Portable Network Graphics (.PNG) and Joint Photographic Experts Group (JPEG) formats. However, so far, only support for .PNG and .JPG is complete.

For the sake of demonstration, I'm going to create a spreadsheet that looks like this:

First of all, I need to create a spreadsheet and put my name and address into it in large letters. I do that using the techniques I've described in previous articles. Here's the code I use:

     H DFTACTGRP(*NO) BNDDIR('HSSF')

      /copy hssf_h

     D book            s                   like(HSSFWorkbook)
     D sheet           s                   like(HSSFSheet)
     D row             s                   like(HSSFRow)
     D NameStyle       s                   like(HSSFCellStyle)
     D AddrStyle       s                   like(HSSFCellStyle)

      /free
          hssf_begin_object_group(10000);
          book = new_HSSFWorkbook();
          sheet = hssf_newSheet(book: 'Test');

          // ------------------------------------------
          // Create a large bold font for my name
          // ------------------------------------------

          NameStyle = HSSFWorkbook_createCellStyle( book );
          HSSFCellStyle_setFont( NameStyle
                               : hssf_CreateFont( book
                                                : 'Arial'
                                                : 36
                                                : BOLDWEIGHT_BOLD
                                                : *OMIT
                                                : *OMIT
                                                : *OMIT
                                                : *OMIT
                                                : *OMIT ) );

          // ------------------------------------------
          // Create a medium-sized italic font for
          //  my address
          // ------------------------------------------

          AddrStyle = HSSFWorkbook_createCellStyle( book );
          HSSFCellStyle_setFont( AddrStyle
                               : hssf_CreateFont( book
                                                : 'Arial'
                                                : 16
                                                : *OMIT
                                                : *OMIT
                                                : *ON
                                                : *OMIT
                                                : *OMIT
                                                : *OMIT ) );

          // ------------------------------------------
          //  Load my logo into the workbook
          // ------------------------------------------

          Pic = IFSDIR + '/SKLogo.png';
          idx = HSSF_addPicture(book: Pic: HSSF_PIC_PNG);

          // ------------------------------------------
          //   Put my name and address in cells
          // ------------------------------------------

          row = HSSFSheet_createRow(sheet: 0);
          hssf_text(row: 0: 'Scott Klement': NameStyle);

          row = HSSFSheet_createRow(sheet: 1);
          hssf_text(row: 0: '123 Main Street': AddrStyle);

          row = HSSFSheet_createRow(sheet: 2);
          hssf_text(row: 0: 'Milwaukee, WI 53201': AddrStyle);

Here's a quick review of what the preceding code does: It creates an HSSFWorkbook object (representing an Excel document) by calling new_HSSFWorkbook() and adds a new sheet to it by calling hssf_sheet(). You can have as many sheets as you want in a workbook. In Excel, the sheets appear as "tabs." If you review the screenshot of the Excel document shown earlier in this article, you'll see that it has one tab labeled Test. That tab corresponds to my sheet variable.

I created two cell styles. The first is called NameStyle and specifies a 32-point bold Arial font; the other is called AddrStyle and has a 16-point italic Arial font. I created three rows by calling HSSFSheet_createRow() three times. After creating each row, I inserted one cell in it by calling hssf_text(). The second parameter to hssf_text() is the column number, the third is the text to place in the cell, and the fourth is the cell style I created earlier.

Now it's time to add an image. Here's the code to do that:

     D IFSDIR          C                   '/tmp'
     D Pic             s            500a   varying
     D idx             s             10i 0
          .
          .
          // ------------------------------------------
          //  Load my logo into the workbook
          // ------------------------------------------

          Pic = IFSDIR + '/SKLogo.png';
          idx = HSSF_addPicture(book: Pic: HSSF_PIC_PNG);

HSSF_addPicture() copies a stream file from the IFS into an Excel document. I literally mean that it makes a copy of your picture from the IFS into the Excel workbook. Once that copy has been made, neither HSSF nor Excel will need to reference your IFS file again.

• The first parameter to is the workbook you want to copy the image into.

• The second parameter is the pathname to the IFS object you'd like to load. HSSF does not validate that this IFS object is a valid JPG or PNG file; it's up to you to provide a valid picture.

• The third and final parameter is how you tell HSSF what type of image it is. The possible values are HSSF_PIC_PNG or HSSF_PIC_JPEG.

HSSF_addPicture() is a new routine, written in RPG, that's part of the HSSFR4 service program. To use it, you need to download the updated copy of HSSFR4 provided in the code download for this article. Unlike some of the other examples I've seen on the web, it does not impose a 32 KB (or a 64 KB) limit on the size of the image file.

Now that the picture has been copied into the workbook, you can tell HSSF to display it on one or more of your sheets. To do that, you first need to gain access to the "patriarch" object for the sheet. A patriarch is a tool for drawing graphics such as lines, shapes, and pictures on a sheet. To get the patriarch, you need to call the createDrawingPatriarch() method of the sheet object.

Once you have the patriarch for a given sheet, you'll need to create a client anchor. The client anchor tells Excel where an image should be drawn. Think of it as "anchoring" the image to particular cells on the sheet. If you move or resize the cells, the image can (optionally) move or resize along with them. You create a client anchor by calling the new_HSSFClientAnchor() prototype, which calls the constructor for the HSSFClientAnchor class.

     D img             s                   like(HSSFPicture)
     D Pat             s                   like(HSSFPatriarch)
     D Anc             s                   like(HSSFClientAnchor)
          .
          .
          // ------------------------------------------
          //   Anchors are always specified by the
          //   upper-left cell and the lower-right
          //   cell to create a rectangle.
          //
          //   Draw the logo from cells 6,0 (G1) to
          //   cells 8,6 (I7)
          // ------------------------------------------

          Pat = HSSFSheet_createDrawingPatriarch(sheet);
          Anc = new_HSSFClientAnchor( 0: 0: 0: 0: 6: 0: 8: 6);
          HSSFClientAnchor_setAnchorType(Anc: HSSF_ANCHOR_MOVE);
          img = HSSFPatriarch_createPicture(pat: anc: idx);

In the preceding code:

• HSSFSheet_createDrawingPatriarch() creates a patriarch in the given sheet object. This patriarch can be used and reused to draw as many pictures and shapes as you need on that sheet.

• new_HSSFClientAnchor() sets up the coordinates in which to draw the picture.

• HSSFClientAnchor_setAnchorType() controls how the anchor is affected by the user resizing or moving the cells. Should the anchor (and therefore the picture) move with the cells? Should be it resized when the cell is resized? In this example, I told it to move with the cells but not be resized with them.

• HSSFPatriarch_createPicture() tells the patriarch to draw the picture on the sheet. You have to tell it which picture by passing the picture index returned by hssf_addPicture() as well as the client anchor to tell the patriarch where to draw the picture.

Note that you can draw the same picture in multiple places on the sheet by calling HSSFPatriarch_createPicture() multiple times, each time passing a different Client Anchor.

More About the Client Anchor

The new_HSSFClientAnchor() accepts eight parameters that represent the cells that the image should be drawn over. Ignore the first four zeroes for now and think about the last four parameters. These represent two cells in the spreadsheet in col,row notion. I've specified that the first cell is column 6, row 0, and that the second cell is column 8, row 6. Excel views these coordinates as the upper-left and lower-right corners of a rectangle and draws my image within that rectangle. You can think of these as the "anchor points" of my image.

The first four parameters to new_HSSFClientAnchor() are coordinates within the cells. The first two parameters represent the x and y coordinates within the cell that represents the upper-left corner of the picture. The next two parameters specify the x and y coordinates within the cell that represents the bottom-right corner. The x coordinates are specified in 1/1024th of the width of the cell. The y coordinates are specified in 1/256th of the height of the cell. Consequently, if I want the corners of the image to be in the center of the cells instead of the edge of the cells, I could code the following:

     Anc = new_HSSFClientAnchor( 512: 128: 512: 128  
                               :   6:   0:   8:   6);

Excel shrinks or stretches your picture, as needed, to make it fit into the area defined by your client anchor. Therefore, you should try to set the anchor points so that your picture won't be contorted into the wrong shape. Because calculating the correct anchor points can be a hassle, I've provided another tool that resets a picture to its original dimensions and changes the bottom-right anchor points accordingly. I called this routine HSSFPicture_resetSize(). It must be called after the HSSFPatriarch_createPicture() routine.

    img = HSSFPatriarch_createPicture(pat: anc: idx);
    HSSFPicture_resetSize(img);

In this example, HSSFPatriarch_createPicture() draws a picture on the sheet, and it's stretched or shrunk to fit to the dimensions of the anchor. The HSSFPicture_resetSize() routine then resets that size to be the original size of the picture. The coordinates for the lower-right corner of the anchor are changed appropriately so that the picture has its original shape and size. The upper-left corner of the anchor remains unchanged.

Previous Articles

To read my previous articles about using HSSF from RPG, please visit the following links.
http://systeminetwork.com/article/poi-updated-version-301
http://systeminetwork.com/article/new-functions-xlparser4

However, I do recommend downloading the source code from the link below instead of from the previous articles, as it contains the most up-to-date copy of all of the sample programs from the previous articles, as well as the new techniques presented herein.

Code Download

I've written an RPG service program to assist you with interfacing with the HSSF Java routines. It has been updated with each article I've written about HSSF. You can download an updated copy that contains the routines for working with pictures from the following link:
http://www.pentontech.com/IBMContent/Documents/article/57545_796_Hssf.zip

The code download contains all the sample code from the previous articles, as well as a new ADDPIC sample program that demonstrates the techniques that I discuss in this article.