| ||||
|
In part 1 and part 2 of this series, I demonstrated how RPG can call Java functions to create an Excel spreadsheet. In this article, I will demonstrate how to put headings and footers on the printouts that those spreadsheets can generate and how to set rows and columns to repeat on each printed page. The source members that are referred to in this article are extended versions of the ones that I wrote for the September articles. At the end of this article, I will provide a link so that you can download the updated source members. Each Excel document has a header that is printed at the top of the page when the spreadsheet is printed. In Excel, you set this by clicking View -> Header and Footer. To do the same thing in code using HSSF, you need to retrieve the header object from the spreadsheet and make changes to it. The Header is a Java object that belongs to the HSSFHeader class. In HSSF_H, I have defined the following so that it can be used with the LIKE keyword to define HSSFHeader objects references:
D HSSFHeader S O CLASS(*JAVA
D :'org.apache.poi.hssf.usermodel-
D .HSSFHeader')
To retrieve the header from a spreadsheet, you need to call the getHeader() method from the HSSFSheet class. I have the following prototype defined in HSSF_H so that I can call this method:
D HSSFSheet_getHeader...
D PR like(HSSFHeader)
D EXTPROC(*JAVA
D :'org.apache.poi.hssf.usermodel-
D .HSSFSheet'
D :'getHeader')
The following is an example of how you might call this method to retrieve the header:
D myHdr s like(HSSFHeader)
myHdr = HSSFSheet_getHeader(sheet);
The data in the header object is represented by three strings of text: one is for the left-hand side of the page, one is for the right-hand side of the page, and one is for the center of the page. To set each of these, you have to create a Java object of type String from your RPG string. Then, you can call the HSSFHeader's setLeft(), setCenter() or setRight() method. The following code illustrates creating a simple header:
D text s 30A varying
D LeftStr s like(jString)
D MidStr s like(jString)
D RightStr s like(jString)
/free
text = 'Big Big Corp';
LeftStr = new_String(text);
text = 'Weekly Sales Figures';
MidStr = new_String(text);
RightStr = new_String('Dude, Beer!');
HSSFHeader_setLeft(LeftStr);
HSSFHeader_setRight(RightStr);
HSSFHeader_setCenter(MidStr);
If you're familiar with Excel, you know that you can do a lot more than just set simple text strings in the header. You can also insert page numbers, the file's name, the current date, and other special fields that Excel will fill in for you. The way this works beneath the covers is that Excel inserts a special hex code that corresponds to the particular field that needs to be printed. When Excel prints this field, it looks up the field that corresponds to the hex code and prints it. For example, when x'2650' is found in a header string, Excel prints the current page number. To make it easy for Java developers to insert these special hex codes into their documents, the HSSFHeader class contains methods that return the correct codes. The following table lists the functions you can call and explains what each function inserts into the document: HSSFHeader.date() The current date HSSFHeader.file() The document's filename HSSFHeader.numPages() Number of pages in the printout HSSFHeader.page() The current page number HSSFHeader.tab() The name of the current tab (aka worksheet) HSSFHeader.time() The current time There are also special hex codes that change the way the text that follows them are displayed. The following functions in the HSSFHeader class can be called to get the correct hex codes for those as well: HSSFHeader.font(name,style) Changes the font of text that follows HSSFHeader.fontSize(size) Changes the font size of text that follows The tricky part of calling these functions from RPG is that RPG doesn't know how to concatenate Java strings; it only knows how to concatenate its own native character strings. To use the methods above in an RPG program, it's necessary to convert the result to an RPG string and then concatenate it with any text or other elements that you want to include in the header. Once you have the string the way you want it in RPG, you can create a new Java string that contains the results in order to set it in the actual header. The getBytes() method of Java's String class can be used to convert a Java style string to an RPG string. For example, the following sets the header for the right-hand side of the page so that it shows the page and page count:
D text s 300A varying
D RightStr s like(jString)
/free
text = 'Page'
+ String_getBytes(HSSFHeader_page())
+ ' of '
+ String_getBytes(HSSFHeader_numPages());
RightStr = new_String(text);
HSSFHeader_setRight(RightStr);
To eliminate as much of the repetitive coding as possible, I created subprocedures to simplify the use of headers in my programs. They automate the work of converting the RPG strings into Java strings and back again as needed. They also help simplify my code by retrieving the HSSFHeader object from the HSSFSheet object automatically. The following is a list of header-related functions that I added to the HSSFR4 service program:
HSSF_header_setLeft() HSSF_header_fontSize()
HSSF_header_setCenter() HSSF_header_numPages()
HSSF_header_setRight() HSSF_header_page()
HSSF_header_date() HSSF_header_sheetname()
HSSF_header_file() HSSF_header_time()
HSSF_header_font()
To demonstrate the techniques that I present in this article, I've added a HDRDEMO member to the ZIP file that contains the sample HSSF code. The following snippet from that member shows the use of the HSSF_header functions above:
HSSF_header_setLeft (sheet: HSSF_header_date() + ' '
+ HSSF_header_time());
HSSF_header_setCenter(sheet: HSSF_header_font('Arial': 'Italic')
+ HSSF_header_fontSize(16)
+ 'Weekly Sales Figures');
HSSF_header_setRight (sheet: HSSF_header_sheetName());
When this spreadsheet is printed, it will display the date & time in the upper left-hand corner, the words "Weekly Sales Figures" in a large, italic, Arial font in the top center of the page, and the name of the spreadsheet in the upper right corner. In addition to the header, Excel also provides a footer. The footer works exactly like the header with the exception that it prints at the bottom of the page instead of the top. HSSF provides an HSSFSheet_getFooter() method and an HSSFFooter class that work the same way the functions for setting the header work. I've also provided footer functions in HSSFR4. Since they're identical to the header functions above, I won't explain them. Here's another snippet from the HDRDEMO program that prints the filename in the bottom left corner of the document and prints the page number in the bottom right:
HSSF_footer_setLeft (sheet: HSSF_footer_file());
HSSF_footer_setRight (sheet: 'Page ' + HSSF_footer_page()
+ ' of ' + HSSF_footer_numPages());
Another useful function when printing the workbook is the ability to set repeating rows and columns. This function is useful when you have text in cells in the spreadsheet that describe the data. For example, at the top of the spreadsheet, you might have a row that provides headings for each column of data. If you tell Excel that this row is to be repeated, it will be printed on every page of the printout. To set repeating rows and columns, you have to call the setRepeatingRowsAndColumns() method of the HSSFWorkbook object. My RPG prototype for this method follows:
D HSSFWorkbook_setRepeatingRowsAndColumns...
D PR EXTPROC(*JAVA
D :'org.apache.poi.hssf.usermodel-
D .HSSFWorkbook'
D :'setRepeatingRowsAndColumns')
D sheetno like(jint) value
D startcol like(jint) value
D endcol like(jint) value
D startrow like(jint) value
D endrow like(jint) value
The SHEETNO parameter specifies the number of the sheet within the workbook that this is to be applied to. If you specify zero, it is the first sheet that was added, one is the second sheet added, and so on. The STARTCOL and ENDCOL parameters specify a range of columns that you'd like to repeat on every page of the printout. Like most cell references in HSSF, they are numbers starting at zero. Column zero is the column labeled "A" in Excel, column one is "B", and so on. You can specify a value of -1 for both the start and end columns if you do not want any columns to repeat. Likewise, the STARTROW and ENDROW parameters specify a range of rows numbered from zero. You can specify -1 for both of these parameters if you want to tell HSSF that no rows should repeat. For example, to tell HSSF to repeat the first row of the first sheet, and not to repeat any columns, I'll code the following:
HSSFWorkbook_setRepeatingRowsAndColumns( book
: 0
: -1
: -1
: 0
: 0 );
What I don't like about this function is that you specify the sheet as a number instead of supplying the HSSFSheet object. When I'm working with HSSF in my RPG programs, I don't usually keep track of the sequence that each sheet was added, so I don't know what number to specify in the SHEETNO parameter. I wrote the following code to search through all of the sheets in my workbook to find the sheet number of the HSSFSheet object named "sheet":
D count s 10I 0
D x s 10I 0
D testsheet s like(HSSFSheet)
/free
count = getNumberOfSheets(workbook);
for x = 0 to (count - 1);
testsheet = getSheetAt(workbook: x);
if testEqual(testsheet: sheet);
// AT THIS POINT, X IS THE SHEET NO!
endif;
endfor;
Naturally, I don't want to code that routine every place that I want to add a repeating row or column, so I wrapped it up into another subprocedure. The following is the prototype for the HSSF_setRepeating() subprocedure in the HSSFR4 service program:
D HSSF_setRepeating...
D PI
D workbook like(HSSFWorkbook) const
D sheet like(HSSFSheet) const
D startcol 10I 0 value
D endcol 10I 0 value
D startrow 10I 0 value
D endrow 10I 0 value
Now that I have this handy little subprocedure, it's easy for me to set the repeating rows and columns in my HSSFSheet. All I have to do is execute the following code:
HSSF_setRepeating(book: sheet: -1: -1: 0: 0);
You can download the code for this article from http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip . The following are links to the previous articles in this series: Part 1 Part 2 |