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:
[URL unfurl="true"]http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip[/URL]