Re: Deleting a xls sheet using HSSF

I needed to delete a sheet in a spreadsheet using HSSF. Scott Klement has
a set of procedures to do a great deal of stuff using RPG to manipulate
xls, but it did not have anything to delete a sheet. Scott suggested I
use the RemoveSheetAt method. After a few rounds of trying to get it to
work, I finally got it. So I thought I would post the code in case anyone
else ever needs to do this - use at own risk. :-)

Basically this opens the xlsx called myreport, deletes the sheet/tab
called deleteme & saves it back out.

H OPTION(*SRCSTMT: *NODEBUGIO: *NOSHOWCPY)
H THREAD(*SERIALIZE)
H BNDDIR('HSSF')
/copy qrpglesrc,hssf_h

d DeleteSheet Pr

d Ifsdir c '/QNTC/HPIA/Stuff'
d book s like(SSWorkbook)
d Sheet s like(SSSheet)
d SheetIndex s 10I 0

/free

ss_begin_object_group(100);
book = ss_open(Ifsdir + '/myreport.xlsx');
sheet = ss_getSheet(book: 'deleteme');
DeleteSheet();
ss_save(book: Ifsdir + '/myreport.xlsx');
ss_end_object_group();
*Inlr = *on;

/end-free

p DeleteSheet B
d
d DeleteIt PR
d EXTPROC(*JAVA
d : WORKBOOK_CLASS
d : 'removeSheetAt')
d SheetIndex 10I 0 Value
/Free
SheetIndex = ss_find_sheet(book:Sheet);
DeleteIt(Book:SheetIndex);
/End-Free
p E

Steve Jones