scottklement.com

Posted: **Tue Aug 10, 2021 3:53 pm**

I am just coming back to RPGLE after a long stint away from development so I am rusty (like 10 years of rust). Apache POI and java are brand new concepts to my new company and I was selected to learn them for us. So far it has been fun but I am stuck at the moment and it is probably just lack of a basic understanding that has me caught.

I am trying to use the RowShift method in the HSSFSheet class, but I keep hitting a wall. the error is
...
Cause . . . . . : RPG procedure INSROWTEST in program POI36/INSROWTEST
received Java exception "java.lang.NoSuchMethodError:
org/apache/poi/hssf/usermodel/HSSFSheet.shiftRows(III)Lorg/apache/poi/h"
when calling method "shif" with signature "" in class
"org.apache.poi.hssf.usermodel.HSSFSheet". ...

Classpaths are correct and tested with the included example code provided in Scott's latest HSSF36 pack

I have defined the method

Code: Select all

D RowShift...                                               
 D                 PR              O   Class(*java           
 D                                     : HSSF_SHEET_CLASS)   
 D                                     ExtProc(*java         
 D                                     : HSSF_SHEET_CLASS    
 D                                     : 'ShiftRows')        
 D   StartRow                          like(jint) value      
 D   EndRow                            like(jint) value      
 D   RowQty                            like(jint) value      
 D

usage in the program

Code: Select all

RowShift(Sheet: RowCount: RowAdd: RowQty);                    
 // RowShift=new_XSSFRowshift(firstrow :lastRow :rowqty);      
 SSSheet_createRow(sheet: Rowcount);                           
                                                               
   hssf_save(book: IFSDIR + '/test1.xlsx');

Everything else is stock Hssf36 package

I understand this is probably a basic lack of understanding.

Posted: **Tue Aug 10, 2021 4:51 pm**

I have got to stop answering my own questions AFTER asking a group of peers.

The answer was, I needed to remove the 'Class' statement in the definition blow as well are the 'O'.

SMH. lol

Posted: **Tue Aug 10, 2021 5:06 pm**

They say talking to yourself is a sign of high intelligence

Posted: **Tue Aug 10, 2021 5:24 pm**

My wife may have a differing opinion.

Posted: **Tue Aug 10, 2021 9:01 pm**

New question....Old topic this time I am stumped

I am trying to shift rows down in a spreadsheet to insert a new row.

Overall goal -
Using existing Template Spreadsheet. Recreate an account monthly customer statement in Excel from the iseries.
Create an account statement by inserting detail records in the middle of a predesigned excel statement.

How can I accomplish this without clearing a row? What am I missing with this?

Code: Select all

RowCount = 11;                                                 
RowAdd   = RowCount + 16;                                       
RowQty   = 1;                                                  
                                                               
RowShift(Sheet: RowCount: RowAdd: RowQty);                     
SSSheet_createRow(sheet: Rowcount);                            
                                                               
  hssf_save(book: IFSDIR + '/test1.xlsx');

I think what i am doing is shifting everything from row 11 to the end of the sheet down one row and writing a new row in the void created.

I attached a couple pics showing what it is actually doing.

Jason

Posted: **Tue Aug 10, 2021 11:28 pm**

While this response is not going to help you with RowShift my comment is WHY?

We provide transaction lists in Excel for clients and we just generate a new spreadsheet as required.

Is there a problem that the previous data is not available ?

If data is available why complicate the process ?

There may be reasons why you want to update an existing excel as opposed to creating a new one so apologies if this is the case.

Cheers

Posted: **Wed Aug 11, 2021 12:17 am**

Fair question. Maybe I am not thinking correctly on this. My thought process revolved around all the existing formatting in the spreadsheet to match the original PDF version of the statement. I thought, if I could simply fill in some details, it would cut down on complexity since we are brand new to Java let alone Apache POI. I could be wrong.

Posted: **Wed Aug 11, 2021 1:05 am**

Unfortunately, I have not used RowShift. (That's why there wasn't prototypes for it in the HSSF download.) Doing a quick Google, I can see that there is both the RowShift routine that you pointed to, as well as a whole class called "RowShifter", the docs seem to say that the RowShifter class is a utility that's needed because the process of shifting rows requires calling a bunch of routines that are scattered around.

I have no experience with this (so I may be completely off-base, but..) is it possible that's why RowShift isn't working for you? It requires more than just this one call, but also requires a few more things? You could consider the RowShifter class instead.

Posted: **Wed Aug 11, 2021 1:38 am**

I'll try that. I am not real confident with this yet, so i hope you all don't mind me bugging you with it as i get stuck.

Posted: **Thu Aug 12, 2021 1:51 pm**

Update*
Rowshift works as expected, when you are using the correct file type.

I switched to using a .xls file and everything clicked.

Jason

scottklement.com

Apache POI 3.6 - Help with RowShift

Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift

Re: Apache POI 3.6 - Help with RowShift