Re: HSSF Question

I am trying to remove a sheet using the code below. It runs without
reporting any errors, but the sheet is still in there.
Anyone have some ideas on what I am missing?

Thanks
Steve Jones
H-P Products


d HSSF_Remove_Sheet...
d Pr
d book Like(HSSFWorkbook)
d SheetIndex Like(Jint)
d book s like(HSSFWorkbook)
d Sheet s like(HSSFSheet)
d SheetIndex s like(jint)
hssf_begin_object_group(1000);
book = hssf_open('/QNTC/HPIA/ixaadmin/Global/IXAADMIN/myxls.xls')
sheet = hssf_getSheet(book: 'rmreports');
SheetIndex = HSSF_find_sheet(book:Sheet);
HSSF_Remove_Sheet(book:SheetIndex);
hssf_save(book: '/QNTC/HPIA/ixaadmin/Global/IXAADMIN/myxls.xls')
hssf_end_object_group();

P HSSF_Remove_Sheet...
P B
D HSSF_Remove_Sheet...
D PI
D Book like(HSSFWorkBook)
D SheetIndex Like(Jint)
D RemoveSheet PR like(HSSFSheet)
D ExtProc(*JAVA:
D 'org.apache.poi.hssf.-
D usermodel.HSSFWorkbook':
D 'removeSheetAt')
D sheetIndex like(jint) value
P E

Steve Jones





Re: HSSF Question

Scott Klement
to:
RPG programming on the IBM i/System i
08/03/2011 07:55 PM


Sent by:
rpg400-l-bounces@xxxxxxxxxxxx
Please respond to RPG programming on the IBM i / System i






HSSF does provide cell-by-cell updating... in a manner of speaking.

HSSF provides routines that load the whole Excel workbook into memory
where it's stored as a collection of Java objects. There are objects
representing the workbook, each sheet in the workbook, each row in the
sheet, and each cell in the row.

so to update a cell, you do something like (this is using my RPG service
program to simplify a few things):

workbook = HSSF_open('/IFS/PATH/TO/FILE.XLS');
sheet = HSSF_getSheet(workbook: 'SheetName');

row = HSSFSheet_getRow(sheet: 5);
if (row = *null);
row = HSSFSheet_createRow(sheet: 5);
endif;

cell = HSSFRow_getCell(row: 0);
if (cell = *null);
cell = HSSFRow_createCell(row: 0);
endif;

HSSFCell_setCellType( cell: CELL_TYPE_NUMERIC);
HSSFCell_setCellValueD(cell: 12345);

HSSF_save(workbook: '/IFS/PATH/TO/FILE.XLS');

So... what this code essentially does is load a workbook into memory,
retrieve teh sheet object, retrieve a row from that sheet (or, if not
found, it creates it), retrieve a cell from the row (or, if not found,
create it) and set the value of that cell.

HSSF numbers row/columns starting with 0. So the above retrieves row 5,
cell 0. In Excel, that would be cell A6.

(Yes, it's a lot of code to set the value of a single cell -- but, you
could put it in a subproc and just call that subproc when needed)

Using this technique you could open the workbook, update all of the
cells you like, and then save it back to disk. You're right that this
is all done in memory, and it's all saved to disk at once... but it does
allow you to go cell-by-cell and make updates.

On 8/3/2011 6:20 PM, John Yeung wrote:

I am not familiar with HSSF personally, but I do deal with Excel files
a lot, and the file format is such that our intuitive concept of
"updating" is not applicable. You really copy all the data from the
Excel file into memory, then manipulate the data in memory as needed,
and then write a completely new file (even if this file has the same
name as the original). So I would be surprised (and impressed!) if
HSSF provides cell-by-cell updating.