Re: HSSF Question

[sjones@xxxxxxxxxxxxxxx]

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.