Re: sql2xls program problem

Bob, 

last time I looked, there was a problem with formulas (this comes from the 
API docs included with POI):

 void setCellType(int cellType) 
          set the cells type (numeric, formula or string) -- DONT USE 
FORMULAS IN THIS RELEASE WE'LL THROW YOU A RUNTIME EXCEPTION IF YOU DO 
Don't know if that's been changed since then...

HTH,

Peter Colpaert
Application Developer
Massive - Kontich, Belgium
-----
Yoda of Borg are we.  Futile is resistance, assimilated will you be.
-----




"Bob O." <otis_the_cat@xxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
07/03/2005 15:45
Please respond to RPG programming on the AS400 / iSeries

 
        To:     rpg400-l@xxxxxxxxxxxx
        cc: 
        Subject:        sql2xls program problem


   I am trying to get this POI thing to create a formula in an Excel
   spreadsheet cell.  JK was kind enough to point me in the right 
direction
   (Thanks John).  As I am unfamiliar with RPGILE and Java, you can
   understand that I am "strugling" my way through this.

   I added the following PR to the sql2xls program:
    * Cell.setCellFormula(String) ------------------------

   D setCellFormula PR          ExtProc(*JAVA
   D                            :'org.apache.poi.hssf.usermodel-
   D                            .HSSFCell'
   D                            :'setCellFormula')
   D                            cellFormula O Class(*JAVA
   D                            :'java.lang.String')

   I then added the following "If" statement to the program:

   If %subst(Cell_Alfa:1:1) = '=';
    setCellType(cell:2);
    Cell_Alfa = %subst(Cell_Alfa:2:1023); // strip out the '='
    string = createString(Cell_Alfa); // create java string
    tstring = trimString(String); // trim string
    setCellFormula(cell:tstring); // set cell value
   Else;
    setCellType(cell:1);
    string = createString(Cell_Alfa); // create java string
    tstring = trimString(String); // trim string
    setCellValStr(cell:tstring); // set cell value
   EndIf;
    freeJavaObject(env:string);

   When I try to run the program, I get the following error:

    Message . . . . :   Java exception received when calling Java method 
(C G
   D
   F).  
  
    Cause . . . . . :   RPG procedure SQL2XLSR in program TGC400/SQL2XLSR
   received
      Java exception "java.lang.NumberFormatException: You cannot get a
   numeric 
      value from a String based cell" when calling method "setCellType" 
with 
      signature "(I)V" in class "org.apache.poi.hssf.usermodel.HSSFCell". 

    Recovery  . . . :   Contact the person responsible for program
   maintenance  to determine the cause of the problem. 

    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. 

   Technical description . . . . . . . . :   If the exception indicates 
that
   the
     Java class was not found, ensure the class for the method is in the
   class 
     path. If the exception indicates that the Java method was not found,
   check 
     the method name and signature. If the signature is not correct, 
change
   the 
     RPG prototype for the method, or change the Java method, so that the
   return
     type and parameter types match. You can determine the signatures for
   all 
     the methods in class XYZ using command QSH CMD('javap -sXYZ').
   Reply  . . . : 
   C 
    Press Enter to
   continue. 

   Does anybody have a suggestion about what I may be missing or doing 
wrong?
-- 
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.