HSSF Image question
Last Post 21 Mar 2013 02:20 PM by Dan Devoe. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Dan Devoe
Advanced Member
Advanced Member

28 Jan 2013 12:24 PM

We have several programs that we use to e-mail spreadsheets to our users.  The majority of them contain images, which are confined to fit in one cell (the row height is set in the program to be 4000 (SSRow_setHeight(row: 4000).  The process works fairly well for us - the images are all reduced to fit the cells, maintaining the aspect ratio.

We are presently using .xls format.

I'm attempting to change to .xlsx format - all works well, except for the images.

The following lines of code work properly when writing to .xls.  When writing to .xlsx, the images don't even appear at all, or appear as one or two pixels.  Changing the first 5 parameters from book: 0: 0: 0: 0: to book & non-zeros values results in the images appearing, but doesn't retain aspect ratio.

IBM i 7.1

java version "1.6.0"
Java(TM) SE Runtime Environment (build pap3260sr11-20120806_01(SR11))
IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 OS/400 ppc-32 jvmap3260sr11-20120806_01 (JIT enabled, AOT enabled)
J9VM - 20120801_118201
JIT  - r9_20120608_24176ifx1
GC   - 20120516_AA)
JCL  - 20120713_01

POI 3.6

Any advise is appreciated. Thanks

<p>Pic = pathname;<br />
r = rowcounter;<br />
idx = SS_addPicture(book: Pic: SS_PIC_JPEG);<br />
Anc = new_SSClientAnchor( book: 0: 0: 0: 0: 0: r: 0: r);<br />
SSClientAnchor_setAnchorType(Anc: SS_ANCHOR_MOVE);<br />
img = SSDrawing_createPicture(Drw: anc: idx);<br />

Dan Devoe
Advanced Member
Advanced Member

28 Jan 2013 01:29 PM
Also, just to see if it made a difference, I also set my *JOB JAVA_HOME  environment variable to use jdk70 (after logging off) - same results.
Dan Devoe
Advanced Member
Advanced Member

29 Jan 2013 07:50 AM
After doing some research on this picture issue through POI forums, it appears that the issue I'm describing is a bug in resizing the image - especially if attempting to insert into one "cell". Someone came up with a work-around, which was introduced in POI3.8-beta1 (or beta2).
I downloaded & installed POI 3.7, 3.8, and 3.9, and set my classpath appropriately
Running under 3.7, the problem still existed for .xlsx, but no issues with .xls.
Running under 3.8 or 3.9, the problem no longer exists for .xlsx - but java errors get thrown when trying to add pictures to .xls

Exception in thread "main" java.lang.NoClassDefFoundError: org.apache.common
at org.apache.poi.hssf.usermodel.HSSFWorkbook.addPicture(HSSFWorkboo
Caused by: java.lang.ClassNotFoundException: org.apache.commons.codec.digest
at java.net.URLClassLoader.findClass(URLClassLoader.java:434)
at java.lang.ClassLoader.loadClass(ClassLoader.java:660)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:358)
at java.lang.ClassLoader.loadClass(ClassLoader.java:626)
... 1 more

Looking at the job log & then pressing F1 on MSGID RNQ0202, I see the following:

Call to Workbook.addPicture() failed.

Additional Message Information

Message ID . . . . . . : RNQ0202 Severity . . . . . . . : 99
Message type . . . . . : Inquiry
Date sent . . . . . . : 01/29/13 Time sent . . . . . . : 09:37:46

Message . . . . : The call to SS_ADDPICT ended in error (C G D F).
Cause . . . . . : RPG procedure BWTPPS in program SCLIBMA/BWTPPS at
statement 021300 called program or procedure SS_ADDPICT, which ended in
error. If the name is *N, the call was a bound call by procedure pointer.
Recovery . . . : Check the job log for more information on the cause of the
error and contact the person responsible for program maintenance.
Possible choices for replying to message . . . . . . . . . . . . . . . :
D -- Obtain RPG formatted dump.
S -- Obtain system dump.
G -- Continue processing at *GETIN.
C -- Cancel.
F -- Obtain full formatted dump.

I suppose that I could set the CLASSPATH at runtime if I'm to produce an .XLSX vs an .XLS - but the issue is we have dozens of programs that produce spreadsheet output (and a handful that read/update), and because once the JVM has been started, changing the CLASSPATH between various reports probably won't yield the desired results...

Has anyone else gotten around this particular picture error when using POI 3.8 / 3.9 ?

Scott Klement
Editorial Staff Member
Editorial Staff Member

02 Feb 2013 06:30 AM

I think I tested everything with POI 3.6.   Do you have better luck with that version?

I haven't done anything with POI recently, I'm afraid, and haven't tried to update to a newer version.

Dan Devoe
Advanced Member
Advanced Member

21 Mar 2013 02:20 PM
The majority of our programs that utilize POI are run as batch jobs.

What I've done as a work-around to the two issues I described (images in 3.6/3.7 not maintaining aspect ratio when in one cell, and inserting an image utilizing HSSF (.XLS) format with POI 3.8 & higher), I am calling a program to set the classpath to utilize poi 3.6 or 3.9 for .XLS or .XLSX sheets, respectively, prior to the JVM being started.

As I stated, since the majority of the affected programs are run as batch jobs, it doesn't seem to have any adverse effects.

While not the most elegant solution, it works. And, it will allow me to "modernize" many of our POI programs to utilize .XLSX at my leisure.
You are not authorized to post a reply.

Acceptable Use Policy