Re: HSSF POI Copy or Clone

It took me about 6 months (in-between regular company projects) to make the necessary changes fro the SK’s POI implementation. POI4.x primarily went away from stand-alone constants to enumerated constants.

For example, in POI 3.x, to set a cell alignment one would use SSCellStyle_setAlignment(cellStyle, ALIGN_CENTER)

POI4.X implements an entirely new class (SSHorizontalAlignment) to specify the enumerated constants:

D hAlign s like(SSHorizontalAlignment)
D ColHeading s like(SSCellStyle)

/free
hAlign = SSHorizontalAlignment_valueOf(new_string(ALIGN_CENTER));
SSCellStyle_setAlignment(ColHeading: hAlign);
/end-free

I went through the entire HSSF_H /copy sourcefile to implement the new classes, and updated HSSFR4 to remove the deprecated calls, and modified HSSFR4 to reflect the new calls. I got very good at reading the POI manual (online) to research all the exact method calls and their parameters. It took me a while to figure out how to implement all the value_of enumerations. An obscure post by one person in a similar position shed light on it.

For example, there were tons of changes in embedding graphics. It was the hardest part of the conversion which is why I did that last. A new enum ClientAnchor.AnchorType is used for and trying to include it in the HSSF_H source file was really confusing, until I realized that a ‘$’ symbol was used for these kinds of enums when declaring it.


D ANCHORTYPE_CLASS...
D C 'org.apache.poi.ss.usermodel-
D .ClientAnchor$AnchorType'


There were minimal changes to HSSFR4, just slight updates to reflect the new method calls. The real meat was HSSF_H. All the new classes had to be included, along with the method calls that replaces the old POI3.6 versions.

I started with the most basic, simplest Excel sheet we used in our production environment, and literally compiled it under the new POI 4.1 environment, and addressed each and every compile error one-by-one, until all the errors were resolved. That simple “conversion” provided the insight I needed to tackle the much harder production workbooks we use, the hardest being the handling of embedded images.

CLASSPATH required updating to reflect the new java packages to use POI4.x. Some were included in the new POI4.x packages you can download from poi.apache.org <http://poi.apache.org/>, others I had to download from their appropriate sites. Took quite a bit of time to research.

Here’s my CLP with everything needed to get POI4.x environment up and running.

/* --------- Add necessary Java environment variables ----------------------- *

ADDENVVAR ENVVAR(CLASSPATH) +
VALUE('/java/poi-4.1.2/poi-4.1.2.jar+
:/java/poi-4.1.2/poi-ooxml-4.1.2.jar+
:/java/poi-4.1.2/poi-ooxml-schemas-4.1.2.jar+
:/java/poi-4.1.2/poi-scratchpad-4.1.2.jar+
:/java/poi-4.1.2/lib/commons-collections4-4.4.jar+
:/java/poi-4.1.2/lib/commons-compress-1.19.jar+
:/java/poi-4.1.2/ooxml-lib/xmlbeans-3.1.0.jar') +
LEVEL(*JOB) REPLACE(*YES)

ADDENVVAR ENVVAR(QIBM_RPG_JAVA_PROPERTIES) +
VALUE('-Djava.awt.headless=true;+
-Dos400.awt.native=true;') +
REPLACE(*YES)

ADDENVVAR ENVVAR(JAVA_HOME) +
VALUE('/QOpenSys/QIBM/ProdData/JavaVM/jdk80+
/64bit') REPLACE(*YES)


The primary reason for migrating to POI4 was primarily personal and for the challenge. POI4 provides more robust implementations, and the column autoSize() that actually works (mostly), and better performance. There’s still some bugs to work through (like any program), but it hasn’t stopped us from doing all new development work under POI4.x. I would never go back to POI 3.6. It’s a slick system and I’m happy (and relieved) to be in a position to be current with Apache’s POI system. When I finished implementing POI 4.1.1 back in January 2020, Apache came out with POI 4.1.2 in February 2020 and did not have to change anything. I changed the JAVA CLASSPATH to reflect the new POI package locations on the IFS, and everything worked. It’s a good feeling for sure! I wonder how many other AS/400 shops are using POI 4.1. I think we’re the only one (that I could find) using it.


Hope this helps. Good luck!

On Jun 17, 2020, at 10:09, Jordan Waterhouse <JordanWaterhouse@xxxxxxxxxxxx> wrote:

I'm using .xlsx (XSSF) also. I tried to add copyRows and cloneSheet to SK's POI implementation but neither is working. Can you give me a hint on how to add the new functions? I must be doing something wrong in my EXTPROCs.

Did you have to make any changes to SK's POI implementation to go to POI 4.1.2? Whenever I try using a new version of POI (3.17), I get a procedure pointer error on ss_getSheet. Everything works fine on POI 3.6.


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Eddie Gomez
Sent: Monday, June 15, 2020 7:36 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: HSSF POI Copy or Clone

We are using SK’s POI implementation in a POI 4.1.2 environment.

We used the .xls (HSSF) implementation at first many years ago when that format was still the predominant format, and now exclusively use .xlsx (XSSF) now that Office365 is the norm. Anyone using .xls is told to upgrade. HSSF resulted (for us) larger file sizes, with XSSF being roughly 40-50% of the size.

Vern is correct. SK’s package comprises of just a small fragment of the entire POI command-set. Only the most basic, common functions were included. It’s up to the developer to add other functions as needed. It took me a while to understand what’s going on under the hood, but once it “clicked”, adding new Java functions using the POI manual is actually quite simple providing it’s merely a straight call to the proper Java API. We don’t make the new code backwards-compatible with HSSF. It’s all XSSF going forward.

On Jun 15, 2020, at 16:32, Vernon Hamberg <vhamberg@xxxxxxxxxxxxxxx> wrote:

I checked the easy400.net site - there are some utilities for Excel there, but they are even more selective, I think, than the functions Scott selected.

Still, there is a kind of XML-language script for creating spreadsheets, both XLS and XLSX, that is available at easy400.

Regards
Vern

On 6/15/2020 6:21 PM, Vernon Hamberg wrote:

Scott's library is not limited to HSSF, it also does XSSF.

Now he has NOT implemented every function that you have in POI - people are welcome to add things to the SRVPGM - it's open source, after all.

There might be something at the easy400.net site, I think Giovanni Perotti or someone else used Scott's API to make something with more functions.

Regards
Vern

On 6/15/2020 3:38 PM, Jordan Waterhouse wrote:

I've been using Scott Klement's HSSF library to create and modify spreadsheets and it works great! Is there any type of copy or clone that would work to copy entire rows or sheets? I've tried to implement CopyRows and CloneSheet but cannot get either one to work. Has anyone been able to get these to work with POI and RPGFree?

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com