Apache POI 3.6 - Help with RowShift

Scott Klement's open source interface to the POI HSSF/XSSF Spreadsheet Project for RPG Programmers. http://www.scottklement.com/poi/
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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.
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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
:lol: :roll:
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Apache POI 3.6 - Help with RowShift

Post by Scott Klement »

They say talking to yourself is a sign of high intelligence :)
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

My wife may have a differing opinion. :lol:
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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
Attachments
What I actually wanted
What I actually wanted
What_was_wanted.png (7.81 KiB) Viewed 20305 times
What is is actually doing
What is is actually doing
wrongidea.png (20.02 KiB) Viewed 20305 times
Template Rows
Template Rows
template.png (6.12 KiB) Viewed 20305 times
msddcb
Posts: 21
Joined: Wed Jul 28, 2021 5:12 am

Re: Apache POI 3.6 - Help with RowShift

Post by msddcb »

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
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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.
Scott Klement
Site Admin
Posts: 635
Joined: Sun Jul 04, 2021 5:12 am

Re: Apache POI 3.6 - Help with RowShift

Post by Scott Klement »

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.
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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.
jmaple79
Posts: 10
Joined: Thu Jul 29, 2021 12:27 pm

Re: Apache POI 3.6 - Help with RowShift

Post by jmaple79 »

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

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

Jason
Post Reply