Recently, I was browsing the Internet to find some useful technical tips, and I came across a site that presented a set of Java APIs with a very curious name: HSSF, which stands for Horrible Spreadsheet Format. Despite this comic name, these powerful APIs are just one component of a more important project called POI, which lets programmers manipulate various file formats based upon Microsoft's OLE 2 Compound Document. Specifically, HSSF is a set of APIs for creating, reading, and writing Microsoft Excel (.xls) file formats.

HSSF APIs are written in 100% Pure Java and are offered by the Jakarta Project, which creates and maintains open-source Java solutions for free distribution to the public. Java is platform-independent and can be easily ported to any computer on which the Java Virtual Machine (JVM) is installed. On the iSeries, we have the JVM, and since V5R1, we can easily use Java classes and call their methods. Does this mean that we can call these Java APIs from a standard RPG program without knowing Java? Yes, it does. And it's really easy. Furthermore, it's a good challenge to improve our programming techniques and learn a little bit of Java. Chapter 11 of the WebSphere Development Studio: ILE RPG Programmer's Guide includes a section called "RPG and Java" that provides detailed information on using Java objects and calling Java methods from RPG.

To see these HSSF APIs in action, download this simple utility that lets you create Excel (.xls) files (like in Figure 1) from an SQL statement (like in Figure 2).

Figure 1: You'll get this Excel spreadsheet from the SQL statement shown in Figure 2. (Click images to enlarge.)

Figure 2: This SQL statement generates the Excel spreadsheet shown in Figure 1.

Before running this utility, you must first download the POI 2.0 group of APIs directly from Apache. Choose a download site from Apache Download Mirrors, open subfolder jakarta/poi, and download file POI-2.0-pre1-2003mmdd.jar. You can rename this file POI-2.0.jar for your convenience.

Next, create an IFS folder. From a command line, type md excel and move the downloaded .jar file into it. In addition, to achieve the best performance, compile the jar file with
CRTJVAPGM CLSF(/excel/POI-2.0.jar) OPTIMIZE(40). Don't be impatient! This step will take several minutes (up to 20 on a little box!).

Now, download the SQL2XLS utility; you can either directly restore the compiled objects from SAVF or compile the RPG IV program and the command from source files.

The first time you run the utility, you may find it a bit slow because it has to create the SQL package and start a new JVM. Subsequent calls run much faster. However, this utility is intended to create well-formatted, small to medium-sized Excel sheets; it works with large ones too, but more slowly (for comparison, you can download and run the simple 100% Pure Java version included in the downloadable file).

What's in the Code?

Let's look at the code of this utility. You can find section identifiers at the leftmost part of the line source.

At the beginning, all the declare sections have been included in the SQL2XLSR code to make an easier download and a quick install; however, if you plan to implement your own versions using these APIs, consider moving the declares into separate /copy members for best reuse.

Section 1: SQL2XLS Command Parameters

Section 1 defines the entry parameters from command SQL2XLS.

Parameter SQLSTMT contains the SQL statement. This can be any SELECT statement: joins, subqueries, functions, unions, etc. To make coding easier, you can include all strings between double quotes (" ") and optional column headers between square brackets ([ ]). If you want to create a two-line column header, just put a backslash () where you want the header text to wrap.

Parameter TOXLS specifies the name of the .xls file that will contain the result of the SQL query. You can write your .xls into any IFS folder except QDLS. (IFS folders are accessed through java.io.* classes with threaded jobs, but QDLS is not thread-safe, so you cannot use it in the TOXLS parameter.)

The optional FROMXLS parameter specifies from which .xls template to start creating a new .xls file. You can create your own template with company logos, fixed rows, print areas, headers, footers, etc. Because the utility writes the result of the query past existing data in the template, you can easily divide your .xls into sections with different groups of data. Just call the utility for the first group of data with FROMXLS=*NONE and then repeatedly call it again for each other group, using as a template the same .xls that you specified in TOXLS parameter.

Parameter COLHDRS is optional. You can have column headers with field names, the first line of colhdg, or any label that exists. You can specify a run-time column header to be used only within this query by using the SQL clause 'as [columnheader]'.

Parameters TITLE, TITLEALIGN, and TITLECOLS allow you to put a title at the top of the sheet, specify alignment options, and specify the number of columns to merge, respectively.

The following optional parameters can be prompted with F10:

Parameter NAMING specifies the naming convention used for naming objects in SQL statements: *SYS means the iSeries standard library/file syntax; *SQL means the collection/table syntax.

Parameter ACTION specifies what action should be taken in case of an error in the SQL statement (like syntax errors, tables not found, etc.). You can either ignore errors (obviously, the .xls file won't be created) or let the utility send an escape message.

Parameter LOGSQL monitors execution from interactive requests.

Parameter SQLLOCVAL overrides the predefined date, time, and separator SQL options with current system values.

Section 2: SQL Communication Area

Section 2 declares the SQL structures. This is not an SQLRPGLE program, so the standard SQL structure SQLCA must be included manually. Because you will run a dynamic SQL query, you also have to include the SQLDA, an SQL descriptor area that is used to map the columns of the SQL query. This structure has a fixed part and a variable part, one for each column, that must be large enough to accommodate all the columns requested. I declared only one occurrence, but I will actually allocate the space needed at run time and move across elements with pointers.

Section 3: API Declaration

Section 3 declares all the APIs used. The first one is the Process Extended Dynamic SQL (QSQPRCED) API that runs SQL. This API requires two SQL structures--SQLCA and SQLDA (defined in section 2)--and a function template. In this section, you will also find all other prototypes of the system APIs used by the utility.

Section 4: Built-In MI Functions

Section 4 declares some MI functions. CPYBLA moves data from the SQL buffer to Excel cells. PUTENV sets the environment variable CLASSPATH. SLEEP provides a one-second delay to allow the user to read the log messages.

Section 5: Java

Section 5 is the Java section. Here, I declared all the standard Java objects (strings, input/output streams and their constructors, etc.) as well as the Excel-specific Java objects (workbook, sheet, row, and cell with their constructors when needed). I also declared all the Java methods used by the utility to create and populate the .xls sheet.

Section 6: JVM and JNI

Section 6 defines the procedures needed to reclaim Java objects at the end.

How Does This Utility Work?

Now, I'll explain how this utility works.

Section A: Reformat the SQL Statement

Section A reformats the SQL statement by replacing hyphens, square brackets, and backslashes with the correct characters allowed into an SQL statement and displays the statement to the screen or prints it to the job log.

Section B: Create the SQL Package

Section B calls QSQPRCED to create the SQL package used as the repository for the extended dynamic SQL statements. SQL packages are permanent objects used to store information related to prepared SQL statements. I created the package in QTEMP library, but if you expect to run the same statements repeatedly, it would be better to create the package in a permanent library. This saves processing time, especially in an environment where many users are using the same or similar statements. Because SQL packages are permanent, this information is also saved across job initiation/termination and across IPLs. The PRTSQLINF command can be used to produce a formatted report that shows the contents of the package. To delete a specific SQL package, use the DLTSQLPKG command. To locate and then delete SQL packages, use the WRKOBJ command with TYPE(*SQLPKG) and select option 4.

Section C: Prepare the SQL Statement

Section C calls QSQPRCED to prepare the SQL statement; if there are no errors, it calls QSQPRCED again to describe this statement into the SQLDA. These steps are similar to embedded SQL statements Prepare and Describe (explained in the appendix of DB2 Universal Database for iSeries SQL Reference). Because I don't know in advance the number of columns requested, I'll allocate heap storage for 20 entries. If that's not enough, I'll reallocate it for the actual number of columns.

Section D: Set the CLASSPATH

Section D sets the CLASSPATH (the location and the name where the Excel Java APIs can be found) and creates a workbook. As I mentioned, you can create a workbook either from scratch or from an existing workbook. In this case, I chose to open the workbook and retrieve the rows already on the sheet, because I will write past these. The RPG code for creating a workbook with one sheet from a template is the equivalent of the following Java instructions:

Creating a workbook from scratch is simpler:

Section E: Formatting

In Section E, I prepared formatting patterns to apply to some cells, such as bold, center, and wordwrap. You can easily add colors and borders.

If the user needs column headers, write a new row. The next section adds cells with headers. The RPG code for creating rows is the equivalent of this Java code:

Section F: Set Record Buffer Addresses

When the program flow reaches Section F, each occurrence of the multiple SQLDA structure contains in each entry the field name, type, length and, if numeric, the precision. Before fetching rows, however, you must set the address where the SQL QSQPRCED API will put each column's data. I defined a raw record of 32,000 bytes whose address will be put in the first occurrence; other addresses will be increased by the length of the preceding field and stored in the following occurrences.

If the user needs a title, I create a row for it, and I also create the number of cells requested. These cells are then joined together in a Java object called region. This is the Java code; you can see how it has been ported to RPG:

If the user needs column headers, I use the information available in the SQLDA structure within subfield SQLNAME to write and format header cells and to store the length of each column header that will be used later to set each column width. The RPG code to create and populate header cells is the equivalent of this Java code:

Section G: Open the Cursor

At Section G, I'm ready to open the cursor and fetch records one a time with repeated QSQPRCED calls. For each record, I create a new row (you have just seen how to do it), and then I loop through the SQLDA occurrences to get each field value. If the nullfield indicator is on, I just write a cell with a blank value; otherwise, I use the addresses that I stored in the previous section to move data from the buffer into the cell with the CPYBLA MI function.

Characters can be moved as they are, but numbers are available in buffer in their original format (signed or packed) with no decimal point. These numbers are moved into a generic signed or packed 31-byte field, converted into a float number with the proper precision retrieved from SQLDA, and moved into the cell. Figures with two decimal positions are edited with sign, comma separators, and two digits after the decimal point; other figures are edited in float mode (only significant digits after the decimal point are printed).

Section H: Set the Column Width

At Section H, I set the column width, call QSQPRCED one last time to close the SQL cursor, and write out the .xls file with an RPG instruction equivalent to this JAVA code:

Finally, I free all Java objects and turn *inlr on.

Les jeux sont faits! You'll find your .xls ready to use in your IFS folder. Free!

Giuseppe Costagliola is a programmer in Turin, Italy. You can reach him at This email address is being protected from spambots. You need JavaScript enabled to view it..

Also Read

TechTip: Getting Started with Microsoft Visual Studio Express 2012

TechTip: Troubleshooting Team Foundation Server

TechTip: Installing Microsoft SQL Server 2012 Express on Your Local Machine