Re: Excel/POI - Please Help The Drowning Man -- RPG400-L

He also said the real 'fix' would be to use ExceptionClear() to reset
whatever is causing subsequent reads to fail. Well, now I need it. I got
an "HSSF_open" failure this morning while trying to read the first
EXISTING .xls file in the path. No idea why. When I immediately ran it
again using debug, the first file read successfully. No idea why. Then the
read of the second file failed. (Again, N.I.W.)

I've been trying to reproduce this problem so that I can fix my serviceprogram for everyone who uses it. But, so far, I've been unable toreproduce the problem you describe.


When my initial HSSF_open() fails, subsequent ones succeed for me!

Once any kind of exception occurs in the environment, subsequent "HSSF"
procs fail. Even if I run a different POI-related program, the
"HSSF-begin" fails. I'm thinking that the only way these error conditions
get cleared is when all our subsystems come down and restart at night.

Subsystems?! Yikes. Simply starting a new job (signing off and back onagain) should completely reset everything. If you need to reset thesubsystem, then there's a much bigger problem -- and one that I haven'tencountered!

Can anyone help??????? I'm so close!

Like I said, I can't reproduce the problem. Here's my current copy ofHSSF_open():


      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
      *  hssf_open():  Open an existing HSSF Workbook
      *
      *     peFilename = IFS path/filename of workbook to open
      *
      *  Returns the HSSFWorkbook object opened
      *+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     P hssf_open       B                   EXPORT
     D hssf_open       PI                  like(HSSFWorkbook)
     D   peFilename                1024A   const varying

     D jFileInputStream...
     D                 S               O   CLASS(*JAVA
     D                                     : 'java.io.FileInputStream')

     D jInputStream...
     D                 S               O   CLASS(*JAVA
     D                                     : 'java.io.InputStream')

     D POIFSFilesystem...
     D                 S               O   CLASS(*JAVA
     D                                     : 'org.apache.poi.poifs-
     D                                     .filesystem.POIFSFileSystem')

     D new_FileInputStream...
     D                 pr                  like(jFileInputStream)
     D                                     extproc(*JAVA
     D                                     :'java.io.FileInputStream'
     D                                     : *CONSTRUCTOR)
     D  filename                           like(jString) const

     D new_POIFSFileSystem...
     D                 pr                  like(POIFSFileSystem)
     D                                     extproc(*JAVA
     D                                     :'org.apache.poi.poifs-
     D                                     .filesystem.POIFSFileSystem'
     D                                     : *CONSTRUCTOR)
     D  stream                             like(jInputStream)

     D new_HSSFWorkbookFromPOIFS...
     D                 PR                  like(HSSFWorkbook)
     D                                     ExtProc(*JAVA:
     D                                     'org.apache.poi.hssf.usermodel-
     D                                     .HSSFWorkbook':
     D                                     *CONSTRUCTOR)
     D  poifs                              like(POIFSFileSystem)

     D closeFile       PR                  EXTPROC(*JAVA
     D                                     :'java.io.FileInputStream'
     D                                     :'close')

     D wwStr           s                   like(jString)
     D                                     inz(*NULL)
     D wwFile          s                   like(jFileOutputStream)
     D                                     inz(*NULL)
     D wwPOIFS         s                   like(POIFSFileSystem)
     D                                     inz(*NULL)
     D wwBook          s                   like(HSSFWorkbook)
     D                                     inz(*NULL)
     D wwRetVal        s                   like(HSSFWorkbook)
     D                                     inz(*NULL)

      /free

         hssf_begin_object_group(1000);

         monitor;
            wwStr   = new_String(peFilename);
            wwFile  = new_FileInputStream(wwStr);
            wwPOIFS = new_POIFSFileSystem(wwFile);
            wwBook  = new_HSSFWorkbookFromPOIFS(wwPOIFS);
         on-error 301;
            // ExceptionClear(JNIENV_p);
         endmon;

         if (wwFile <> *NULL);
            closeFile(wwFile);
         endif;

         if (wwBook = *NULL);
            hssf_end_object_group();
         else;
            hssf_end_object_group(wwBook: wwRetval);
         endif;

         return wwRetVal;
      /end-free
     P                 E

It doesn't seem to matter whether I have ExceptionClear() commented out ornot (it's commented out, above, but you can re-enable it by removing theslashes "//" from the start of the line.)

Please try this routine in your copy of HSSFr4. Tell me if it helps.Does enabling or disabling ExceptionClear() solve the problem?

Note that in this new version of HSSF_open(), it returns *NULL if the filecan't be opened, and a valid object reference if it can. This means thata calling routine would do something like this:



       foo = '/tmp/myfile.xls';
       book = HSSF_open(%trim(foo));
       if (book = *NULL);
           // Open failed!  Don't try to use "book" in any other
           // HSSF routines until you call HSSF_open() successfully
           // or you call new_HSSFWorkbook() successfully.
       endif;