The HSSF APIs make creating spreadsheets a breeze!
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:
POIFSFileSystem fs =
new POIFSFileSystem(new FileInputStream("template.xls"));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Creating a workbook from scratch is simpler:
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet();
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:
HSSFRow row = sheet.createRow(rowNum);
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:
sheet.addMergedRegion(new Region
((int rowFrom, short colFrom, int rowTo, short colTo));
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:
cell = row.createCell(colNum); // colNum ranges from 0 to SQLD < 1
cell.setCellValue(colHeader); // set the header text
cell.setCellStyle(style); // apply the style (bold,center,etc)
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:
FileOutputStream outFile = new FileOutputStream("workbook.xls");
wb.write(outFile);
outFile.close();
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 e-mail address is being protected from spam bots, you need JavaScript enabled to view it
.
|
