1. HOW TO UPDATE AN HSSF SPREADSHEET Over the past two months, I have presented several articles on using RPG and Java to create Excel spreadsheets. I've explained how to create sheets, rows, and cells and have explained many different things that can be done with cell styles. This article explains another useful capability of HSSF, how to read and modify an existing spreadsheet. If you think about it, most of what I demonstrated in the past articles were things that happened in memory. You'd create a new workbook, add sheets to it, add cells to the sheet, and so on without ever giving it a pathname or telling it where to save things in the IFS. Indeed, what the new_HSSFWorkbook() function does is create a bunch of memory structures, mark everything as "empty," and let you build on it from there. When you read an existing sheet, it works pretty much the same way except that instead of starting with empty memory structures, the structures are populated from existing Excel spreadsheet data. The following code demonstrates the process of loading an Excel file into memory: D jFileInputStream... D S O CLASS(*JAVA D : 'java.io.FileInputStream') D jInputStream... D S O CLASS(*JAVA D : 'java.io.InputStream') D POIFSFilesystem... D S O CLASS(*JAVA D : 'org.apache.poi.poifs- D .filesystem.POIFSFileSystem') D new_FileInputStream... D pr O extproc(*JAVA D :'java.io.FileInputStream' D : *CONSTRUCTOR) D filename like(jString) const D new_POIFSFileSystem... D pr O extproc(*JAVA D :'org.apache.poi.poifs- D .filesystem.POIFSFileSystem' D : *CONSTRUCTOR) D stream like(jInputStream) D new_HSSFWorkbookFromPOIFS... D PR like(HSSFWorkbook) D ExtProc(*JAVA: D 'org.apache.poi.hssf.usermodel- D .HSSFWorkbook': D *CONSTRUCTOR) D poifs like(POIFSFileSystem) D closeFile PR EXTPROC(*JAVA D :'java.io.FileInputStream' D :'close') D wwStr s like(jString) D wwFile s like(jFileOutputStream) D wwPOIFS s like(POIFSFileSystem) D wwBook s like(HSSFWorkbook) /free wwStr = new_String('/tmp/xldemo.xls'); wwFile = new_FileInputStream(wwStr); wwPOIFS = new_POIFSFileSystem(wwFile); wwBook = new_HSSFWorkbookFromPOIFS(wwPOIFS); closeFile(wwFile); In Java, data is read from a file using a FileInputStream, so the first thing that program does is open a FileInputStream from the file that you've specified. It then uses that stream to create a POI file system, which is the underlying file system that all Microsoft Office objects use. Finally, it creates the memory structures of an HSSF spreadsheet from the file system. Rather than go through all of these steps each time I want to open an Excel file, I've extended the HSSFR4 service program to include an HSSF_open() subprocedure. Now when I want to load an Excel spreadsheet in my programs, I can just execute the following line of RPG code: myWorkbook = HSSF_open('/tmp/xldemo.xls'); Now that the spreadsheet has been loaded into memory, you could add new rows, sheets, cells, and cell styles to it using the methods that I've demonstrated in previous articles. In addition to being able to add new data to it, you can read the data that's already there and modify that data. This can be very useful when you only want to change certain cells of the spreadsheet and leave the rest alone. In order to do that, you need to be able to get access to the existing objects in the spreadsheet. The HSSF Java classes provide methods that do that. The following prototypes show how you'd reference those methods from an RPG program: D HSSFWorkbook_getSheet... D PR like(HSSFSheet) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFWorkbook' D :'getSheet') D SheetName like(jString) D HSSFSheet_getRow... D PR like(HSSFRow) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFSheet' D :'getRow') D RowNo like(jInt) value D HSSFRow_getCell... D PR like(HSSFCell) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFRow' D :'getCell') D ColNo like(jShort) value D HSSFCell_getCellType... D PR like(jInt) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFCell' D :'getCellType') D HSSFCell_getCellFormula... D PR like(jString) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFCell' D :'getCellFormula') D HSSFCell_getNumericCellValue... D PR like(jDouble) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFCell' D :'getNumericCellValue') D HSSFCell_getStringCellValue... D PR like(jString) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFCell' D :'getStringCellValue') D HSSFCell_getCellStyle... D PR like(HSSFCellStyle) D ExtProc(*JAVA D :'org.apache.poi.hssf- D .usermodel.HSSFCell' D :'getCellStyle') For your convenience, I've added these prototypes to the HSSF_H member of the code download for this article. For example, if you wanted to change the cell in Row 5, Column 1 (which would be cell "B6" using Excel's naming convention) to contain the string "Nifty New Value", you could do so with the following code: H DFTACTGRP(*NO) H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY) H THREAD(*SERIALIZE) H BNDDIR('HSSF') /copy qrpglesrc,hssf_h D book s like(HSSFWorkbook) D sheet s like(HSSFSheet) D row s like(HSSFRow) D cell s like(HSSFCell) D TempStr s like(jString) /free hssf_begin_object_group(100); // open Excel spreadsheet and get the cell // from row 5, column 1 of 'My Sheet' book = hssf_open('/tmp/xldemo.xls'); sheet = hssf_getSheet(book: 'My Sheet'); row = HSSFSheet_getRow(sheet: 5); cell = HSSFRow_GetCell(row: 1); // make sure this is a String cell, and set // it's value to 'Nifty New Value' HSSFCell_setCellType(cell: CELL_TYPE_STRING); TempStr = new_String('Nifty New Value'); HSSFCell_setCellValueStr(cell: TempStr); // Save changes back to disk hssf_save(book: '/tmp/xldemo.xls'); hssf_end_object_group(); *inlr = *on; /end-free Since you're able to retrieve existing rows and columns in the spreadsheet, you may be asking yourself, "Can I use this technique to read a spreadsheet as well?" Yes, you can. There are various types of cells in Excel, and depending on whether the cell is a number, a string, or a formula, you need to call a different method to get the value. The following program demonstrates retrieving the value of a cell: H DFTACTGRP(*NO) H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY) H THREAD(*SERIALIZE) H BNDDIR('HSSF') /copy qrpglesrc,hssf_h D book s like(HSSFWorkbook) D sheet s like(HSSFSheet) D row s like(HSSFRow) D cell s like(HSSFCell) D TempStr s like(jString) D StrVal s 52A varying D NumVal s 8F D type s 10I 0 D String_getBytes... D pr 1024A varying D extproc(*JAVA: D 'java.lang.String': D 'getBytes') /free hssf_begin_object_group(100); // // Load an existing spreadsheet into memory // book = hssf_open('/tmp/xldemo.xls'); // // See what the value of the cell in row 7, column 2 is: // sheet = hssf_getSheet(book: 'My Sheet'); row = HSSFSheet_getRow(sheet: 7); cell = HSSFRow_GetCell(row: 2); type = HSSFCell_getCellType(cell); StrVal = 'Cell C8 = '; select; when type = CELL_TYPE_STRING; StrVal += String_getBytes(HSSFCell_getStringCellValue(cell)); when type = CELL_TYPE_FORMULA; StrVal += String_getBytes(HSSFCell_getCellFormula(cell)); when type = CELL_TYPE_NUMERIC; NumVal = HSSFCell_getNumericCellValue(cell); StrVal += %char(%dech(NumVal:15:2)); endsl; dsply StrVal; hssf_end_object_group(); *inlr = *on; /end-free I've added the demonstration code above to the ZIP file containing the downloadable code from the previous articles. You can retrieve it from the following link: http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip