RESOLVED!!!!!!!!!: Excel/POI - Here We Go Again

It turns out you can't read a protected workbook (at least one with a 
password, anyway). I left the sheets protected but unlocked the workbook 
and all is well. I remain employed another day. WHEW! 

Arthur J. Marino
Southern Container Corporation
(631) 231-0400 x133



Arthur.Marino@xxxxxxxxxxxxxxxx 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/21/2006 01:11 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>, 
rpg400-l-bounces@xxxxxxxxxxxx
Subject
Re: Excel/POI - Here We Go Again






Yes, Scott, it was me who had the problem with the open. And you did tell 
me how to fix it. I'm using the 'access' API and it works beautifully. The 

problem is that the workbook DOES exist.

The only difference between all of my testing of the 'read' process and 
the current situation is that when I was testing I'd: 
create the workbook in the IFS, and use Navigator to drag it to my desktop
use my Excel (2002, if you can believe it) to enter some values in the 
sheet
save it back to my desktop, and drag it back to another ('inbound') folder 

in the IFS
The 'read' worked perfectly (after you advised me to download the latest 
version of your service pgm HSSFR4).

Now, in production, the original sheet has been emailed to the salesman, 
who's changed it and emailed it back to me. I save his sheet to my desktop 

and then drag it to the 'inbound' folder in the IFS. I don't know what 
version of Excel he uses to save his sheet. Should it matter? I've tried 
re-saving his sheet using MY Excel version and the read ('open') still 
fails.

I'll try the possible solutions you mention but I have a real bad feeling 
about this.

Arthur J. Marino
Southern Container Corporation
(631) 231-0400 x133



Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
09/21/2006 12:38 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
cc

Subject
Re: Excel/POI - Here We Go Again

Strangely, the 'open' returns an address of ZERO for 'book' but does not
halt. The exception occurs only when the HSSF_GETSHEET fails because the
'book' address is 0.

That's not strange at all.  In an earlier discussion someone (I thought it 


was you, actually?) was trying to handle situations where hssf_open() 
could fail.  I think he was trying to open a file that might not exist, 
and wanted a way to handle the error properly.  My memory is fuzzy, but it 


was something like that.

Hang on, I'll look up the message in the archives...  yes, it was you. 
Here are the key messages in the thread where we discussed it.
http://archive.midrange.com/rpg400-l/200608/msg00733.html
http://archive.midrange.com/rpg400-l/200608/msg00745.html

I probably didn't tell you that I changed it, though..  my fault.  The 
idea, though, is that instead of crashing (and taking the JVM down) when a 


file can't be opened for some reason, it now returns *NULL.  Your program 
and therefore check for that.

      if HSSF_open( '/path/to/myfile.xls' ) = *NULL;
           ErrMsg ='Unable to open Excel document';
           ShowErrorToUser(Errmsg);
           iter;  // try next document.
      endif;

The following part of your message is the REAL problem, the reason why 
it's failing and therefore returning zero:

  received Java exception
"org.apache.poi.hssf.record.RecordFormatException:
  Unable to construct record instance, the following" when calling 

method

  "<init>" with signature
  "(Lorg.apache.poi.poifs.filesystem.POIFSFileSystem;)V" in class
  "org.apache.poi.hssf.usermodel.HSSFWorkbook".

Excel documents are made up of specially formatted "records" (they aren't 
database records in the manner that we're used to thinking of, but they 
are a record of sorts.)

When the Java code (HSSF) is reading the file, it needs to create a new 
Java record object to handle the record that it just loaded from the Excel 


document.  However, for some reason it's unable to do that.

I would suspect that either it doesn't understand the format of the record 


(maybe it's a feature that HSSF doesn't support) or there's a bug in the 
Apache HSSF code.  Something like that.

Unfortunately, that's outside of my area of expertise.  You could try 
upgrading POI to see if it's fixed in a newer version, or you might try 
searching (Google) for the Java error to see if anyone else has 
encountered it and found a workaround.