| ||||
|
In the January 8, 2004, issue of this newsletter, I demonstrated how to read an Excel spreadsheet in an RPG program. In this article, I intend to show the opposite, how to write an Excel spreadsheet. The Jakarta Project creates and maintains open-source solutions that are written in Java. Jakarta is brought to you by the Apache Software Foundation and contains many sub-projects, all of which are available to the public at no charge. The POI sub-project of Jakarta focuses on creating documents that use the OLE2 Compound Document format. At the time that I'm writing this document, Microsoft uses OLE2 for most Microsoft Office documents, including both Word and Excel. The HSSF component of the POI project is capable of creating Excel documents. Starting in V5R1, ILE RPG programs are capable of calling Java methods. This Java integration opens up the possibility of using the HSSF Java classes from an RPG program so that you can create your own spreadsheets without having to buy a commercial product. INSTALLING THE LICENSED PROGRAMS In order to use the sample code provided with this article, you must have the following installed on your iSeries: Java Developer Kit version 1.3 or later (5722-JV1, opt 5) OS/400 - System Openness Includes (5722-SS1, opt 13) These two licensed programs are included on the OS/400 CDs for V5R1 or later, but they aren't installed by default. If you haven't already installed them, you'll need to do so using option 11 on the GO LICPGM menu. GETTING THE POI JAVA CLASSES You can download the Java classes from the Jakarta Web site. To do that, follow these steps: a) Navigate to http://jakarta.apache.org/poi/. b) Click "Download." c) A list of "download mirrors" will be displayed. These all have the same software on them, so pick one that's close to you to get the best download speeds. d) On the mirror site, you'll have to choose between "dev" (development) or "release." The development code will have the latest and greatest features, but may have bugs in it. The released code has been tested more thoroughly, but may lag behind in features. For my company, I chose "release." e) The next choice will be "bin" or "src." These are the pre-compiled and "source code" versions of POI, respectively. Rather than compile it myself, I decided to click "bin." f) Finally, it lists the files for download. At this time, the latest version is 2.5.1. The file that I downloaded is called poi-bin-2.5.1- final-20040804.zip. g) Inside this ZIP file are the Java classes, documentation, and legal information. The documentation is in the "docs" subdirectory and the legal information is in the "legal" subdirectory. The JAR files in the main directory of the ZIP file are the Java classes themselves. h) The .JAR files are needed on the iSeries to create Excel documents. To keep things simple, upload them to the /QIBM/userdata/Java400/ext directory on the iSeries. i) If for some reason you're not allowed to put things in this directory, you can put them elsewhere in the IFS. If you do this, you must include the JAR files in your Java CLASSPATH. The CLASSPATH is not necessary if you put them in the location specified in step H, above. USING THE POI CLASSES IN YOUR RPG PROGRAM Java is an object-oriented ("OO") language. That means that in order to do things, you must create an object and then call routines in that object. The definition of an object that describes what attributes it has and what the object can do is called a "class." Perhaps the easiest way to envision a class in RPG is to think of it as a cross between a data structure and a service program. A class is really the blueprint for an object. It describes what data is stored in the object and what actions you can perform on that object. The data is stored in fields that you can manipulate, much like a data structure. The actions are carried out with subprocedures that you can call, much like a service program. In OO terminology, these subprocedures are called "member functions" or "methods." To create an object from a class, you call a special method known as a "constructor." If you think of a class as the blueprints for an object, then you can think of a constructor as constructing an object from the blueprint. When you use Java objects from RPG, the actual objects are stored in the Java Virtual Machine's ("JVM") memory. An "object reference" is stored in your RPG program and refers back to the actual object. It's this reference that you pass to and from the Java methods that you call. The following RPG code declares an object reference called "HSSFWorkbook": D*ame+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++
D HSSFWorkbook S O CLASS(*JAVA
D :'org.apache.poi.hssf-
D .usermodel.HSSFWorkbook')
The data type "O" tells the compiler that this is an object reference. The CLASS keyword specifies that it is a Java object reference and that the class that it refers to is called org.apache.poi.hssf.usermodel.HSSFWorkbook. The following prototype declares the constructor for the HSSFWorkbook class: D new_HSSFWorkbook...
D PR CLASS(*JAVA
D :'org.apache.poi.hssf-
D '.usermodel.HSSFWorkbook')
D ExtProc(*JAVA
D :'org.apache.poi.hssf-
D .usermodel.HSSFWorkbook'
D :*CONSTRUCTOR)
Again, I've included the CLASS keyword. This time, I'm declaring the return value from the subprocedure that I'm calling. The EXTPROC keyword on this D-spec starts with *JAVA to tell the compiler that I'd like to call a Java method. The second parameter to the EXTPROC keyword is the Java class. The third parameter is *CONSTRUCTOR, which tells the compiler that I want to call the constructor for that class. To make the code a little easier to read, and to save myself some typing, I prefer to use the LIKE keyword for the return value of the constructor. If I use this technique, the prototype changes to the following: D new_HSSFWorkbook...
D PR like(HSSFWorkbook)
D ExtProc(*JAVA
D :'org.apache.poi.hssf-
D .usermodel.HSSFWorkbook'
D :*CONSTRUCTOR)
I've put the above definitions, as well as other definitions that I need when working with HSSF, into a source member called HSSF_H. This makes it easy to reference these definitions in every RPG program that I use them in. The following source code demonstrates calling the constructor to create a new HSSF workbook object in an RPG program: /copy qrpglesrc,hssf_h
D book s like(HSSFWorkbook)
/free
book = new_HSSFWorkbook();
/end-free
Now that I have a workbook, I can call methods in the workbook class. In Excel, each workbook contains one or more spreadsheets. A spreadsheet is represented in Java by an HSSFSheet class. However, in this case, I don't want to call the constructor for the HSSFSheet class directly because I want it to be stored inside the HSSFBook class. Instead, I want to call the "createSheet" method in the workbook, which will create the spreadsheet and return the object reference. The following is the definition of an object reference for the HSSFSheet class that I put in my HSSF_H source member: D HSSFSheet S O CLASS(*JAVA
D :'org.apache.poi.hssf-
D .usermodel.HSSFSheet')
The following prototype is for the createSheet method of the HSSFWorkbook class: D HSSFWorkbook_createSheet...
D PR like(HSSFSheet)
D EXTPROC(*JAVA
D :'org.apache.poi.hssf-
D .usermodel.HSSFWorkbook'
D :'createSheet')
D sheetname like(jString)
Notice the difference between calling a method instead of a constructor. In the previous example, where I was calling a constructor, the third parameter to EXTPROC was *CONSTRUCTOR. Since this is not a constructor, I use the name of the method that I want to call; in this example, that is createSheet. This prototype also accepts a parameter called SHEETNAME. This parameter is an object of type jString. The definition of jString can be found in the member called JNI in QSYSINC/QRPGLESRC. That means that in order to call the createSheet method, I have to first call the constructor for the jString method to create a Java string object. This object can then be passed as a parameter, as follows: /copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Sheet s like(HSSFSheet)
D book s like(HSSFWorkbook)
/free
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
/end-free
Notice that even though only one parameter was defined on the prototype for the createSheet method, I passed two in the RPG program. This extra parameter tells the system which object you want to call the method for. Whenever you call a prototype for a Java method that's not a constructor, the object that you want to operate on is always the first parameter, but you never define that in the prototype. Now that I have a spreadsheet, I want to insert cells into that spreadsheet. This involves two more object types. The HSSFRow object type is a container for all of the rows in a spreadsheet. The HSSFCell object is a cell within that row. I have these object types declared in the HSSF_H source member in a similar fashion to the way that I declared the ones I showed above. The following code demonstrates creating a spreadsheet with one cell that says "Hello World": /copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Str2 s like(jString)
D Sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
/free
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
Row = HSSFSheet_createRow(Sheet: 0);
Cell = HSSFRow_createCell(Row: 0);
Str2 = new_String('Hello World');
HSSFCell_setCellValueStr(Row: Str2);
/end-free
HSSF numbers the rows and columns starting with zero. In Excel, cell A1 would be referred to as Row 0, Column 0 in HSSF. These numbers are passed to the createRow and createCell methods, as shown in the above example. Although the above sample creates a "Hello World" cell, it does not save the workbook to disk, so you'll never know if it worked properly. To write it out, you have to call the HSSFWorkbook_write method. This method needs a FileOutputStream object as a parameter, so before you can call it, you need to create a FileOutputStream object. The constructor for a FileOutputStream requires a String object to specify the filename in the IFS where you want to write the file to. The following RPG code illustrates the process of creating the "Hello World" cell and then writing the whole workbook to disk: H DFTACTGRP(*NO)
/copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Str2 s like(jString)
D Str3 s like(jString)
D Book s like(HSSFWorkbook)
D Sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
D outfile s like(jFileOutputStream)
/free
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
Row = HSSFSheet_createRow(Sheet: 0);
Cell = HSSFRow_createCell(Row: 0);
Str2 = new_String('Hello World');
HSSFCell_setCellValueStr(Cell: Str2);
Str3 = new_String('/tmp/hello.xls');
outfile = new_FileOutputStream(Str3);
HSSFWorkbook_write(Book: outfile);
/end-free
FREEING UP REFERENCES In all of the code that I've shown you so far, I've created objects by calling constructors or by calling methods that will call constructors for me under the covers. Each of the objects created is kept in the memory of the JVM, and a reference is stored in my RPG program. In Java, that works great. The JVM knows how Java programs work, and it'll know when those references are no longer needed. When that happens, it'll remove them from memory. However, the JVM does not know how an RPG program works! It won't clean up memory for an object if it's referred to by an RPG program unless you tell it to! The manual called "WebSphere Development Studio ILE RPG Programmer's Guide" contains sample code that demonstrates how to create a subprocedure called "freeLocalRef." You can call this subprocedure for each object reference when you're done with it. This tells the JVM that it can free up the memory. I've created my own, slightly modified, version of freeLocalRef called hssf_freeLocalRef. It is included in a service program as part of the code download for this article. The following is the same "Hello World" cell code that I demonstrated above, except this time I call hssf_freeLocalRef() to make sure that the objects get cleaned up: H DFTACTGRP(*NO) BNDDIR('HSSF')
/copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Str2 s like(jString)
D Str3 s like(jString)
D Book s like(HSSFWorkbook)
D Sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
D outfile s like(jFileOutputStream)
/free
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
Row = HSSFSheet_createRow(Sheet: 0);
Cell = HSSFRow_createCell(Row: 0);
Str2 = new_String('Hello World');
HSSFCell_setCellValueStr(Cell: Str2);
Str3 = new_String('/tmp/hello.xls');
outfile = new_FileOutputStream(Str3);
HSSFWorkbook_write(Book: outfile);
hssf_freelocalref(outfile);
hssf_freelocalref(Str3);
hssf_freelocalref(Cell);
hssf_freelocalref(Row);
hssf_freelocalref(Str2);
hssf_freelocalref(Str);
hssf_freelocalref(Sheet);
hssf_freelocalref(Book);
*inlr = *on;
/end-free
As you can see, deleting references this way can start to become tedious, even with a program this simple. There is another way that can make it much easier. The service program that I included with this article contains subprocedures called hssf_begin_object_group() and hssf_end_object_group(). The way they work is to create an "envelope." All new objects created after the "begin object group" will be stored in the envelope. Once you're done, you call the "end object group," which will discard the envelope and free up the object references to everything in it. Here's the same sample program, this time using an object group to free the references: H DFTACTGRP(*NO) BNDDIR('HSSF')
/copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Str2 s like(jString)
D Str3 s like(jString)
D Book s like(HSSFWorkbook)
D Sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
D outfile s like(jFileOutputStream)
/free
// create space for 100 or more object references
// in the object group.
hssf_begin_object_group(100);
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
Row = HSSFSheet_createRow(Sheet: 0);
Cell = HSSFRow_createCell(Row: 0);
Str2 = new_String('Hello World');
HSSFCell_setCellValueStr(Cell: Str2);
Str3 = new_String('/tmp/hello.xls');
outfile = new_FileOutputStream(Str3);
HSSFWorkbook_write(Book: outfile);
hssf_end_object_group();
*inlr = *on;
/end-free
CELL WIDTHS AND STYLES If you tried the example above, you may have noticed that the words "Hello World" do not fit very well in the cell. You can change the width of each cell to a more appropriate size by calling the setColumnWidth method of the HSSFSheet class. The width setting is measured in 1/256th of a character. If you set the width to 256, it will be large enough for one character. If you set it to 2560, it will be large enough for 10 characters, etc. For example, if you want to set the width of column 0 to be 50 characters wide, you could do so by including the following code in the program: HSSFSheet_setColumnWidth( sheet: 0: 50 * 256 ); The way that data in a cell is formatted depends on another class called HSSFCellStyle. A cell style is a property of a workbook. You can create many cell styles in the workbook and then apply them to cells as needed. There are a lot of things that you can do with cell styles, but here is a simple example of centering the text in a given cell: H DFTACTGRP(*NO) BNDDIR('HSSF')
/copy qsysinc/qrpglesrc,jni
/copy qrpglesrc,hssf_h
D Str s like(jString)
D Str2 s like(jString)
D Str3 s like(jString)
D Book s like(HSSFWorkbook)
D Sheet s like(HSSFSheet)
D row s like(HSSFRow)
D cell s like(HSSFCell)
D outfile s like(jFileOutputStream)
D Center s like(HSSFCellStyle)
/free
hssf_begin_object_group(100);
book = new_HSSFWorkbook();
Str = new_String('Sheet One');
Sheet = HSSFWorkbook_createSheet(Book: Str);
HSSFSheet_setColumnWidth(Sheet: 0: 50 * 256);
Row = HSSFSheet_createRow(Sheet: 0);
Cell = HSSFRow_createCell(Row: 0);
// create a "centered" cell style
Center = HSSFWorkbook_createCellStyle(book);
HSSFCellStyle_setAlignment(Center: ALIGN_CENTER);
Str2 = new_String('Hello World');
HSSFCell_setCellValueStr(Cell: Str2);
// Apply cell style to cell
HSSFCell_setCellStyle(Cell: Center);
Str3 = new_String('/tmp/hello.xls');
outfile = new_FileOutputStream(Str3);
HSSFWorkbook_write(Book: outfile);
hssf_end_object_group();
*inlr = *on;
/end-free
Cell styles can also be used to draw borders around a cell, change font properties, change colors, make things bold or italic, and do many more things. I do not have space to cover them all in this article. If you'd like to see more about cell styles in an upcoming article, please drop me a note at mailto:sklement@iseriesnetwork.com. MORE UTILITIES IN THE SERVICE PROGRAM In addition to the awkwardness of having to delete every individual object group, I also find it awkward to have to create string objects. I've added routines to the HSSFR4 service program that will simplify this process for methods that I call often. For example, to simplify the process of creating a new spreadsheet in a workbook, I've put the following subprocedure in the service program: P hssf_NewSheet B EXPORT
D hssf_NewSheet PI like(HSSFSheet)
D peBook like(HSSFWorkbook)
D peName 1024A const varying
D wwStr s like(jString)
D wwSheet s like(HSSFSheet)
/free
wwStr = new_String(peName);
wwSheet = HSSFWorkbook_createSheet(peBook: wwStr);
hssf_freeLocalRef(wwStr);
return wwSheet;
/end-free
P E
It creates a new string using the parameter that I've passed, uses that string to create a new sheet, frees up the memory that the string used, and returns the new sheet to the caller. It's very simple, but it simplifies my code, because now when I want to create a sheet in the main RPG program, all I have to do is code the following: sheet = hssf_NewSheet(book: 'Sheet Two'); Likewise, I've written a subprocedure that simplifies saving the workbook to disk. Instead of having to create a string and a FileOutputStream and then call the HSSFWorkbook_write method, I can call the following subprocedure: 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
To use this from my main program, I use the following code: hssf_save(book: '/tmp/hello.xls'); The service program also contains routines for converting dates, adding cells containing text, numbers, dates, and formulas to a sheet, and converting cell coordinates like "0,0" to the Excel names like "A1". The sample source code that I've written for this article demonstrates how to call this service program, and it also demonstrates a more complex sample workbook -- including one that has multiple sheets -- and shows you how to use larger fonts and merge cells. You can download this article from the iSeries Network Web site at 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 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 January 8, 2004, issue of this newsletter, where I demonstrated reading an Excel file in RPG: http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=17839 Geert Van Landeghem also has a tutorial online that demonstrates how to use these Java classes to create an Excel spreadsheet. You can read his tutorial at the following link: http://www.jasservices.com/articles/as400/001_genexcel.htm |