Re: sql2xls program problem -- RPG400-L

I don't know Pete. I create the formulas in my RPG program and save them to a 80 character field in my file. When I run the sql2xls, the formulas are inserted and do their calculations just fine. Turns out though that I may not need to do the formulas after all. The spread sheet I was trying to use as a template has too many things going on and causes the POI program to blow up when it tries to load the cells. Its packed with formulas of it's own with drop-down lists and everything. This file gets sent to the State of Illinois so I don't think I can fudge with it too much.

Im realizing that my only solution at this point is that I am going to have to create a seperate spreadsheet with just the data and the user is going to have to copy it in to the main excel file.

From: Peter.Colpaert@xxxxxxxxxx
Reply-To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
To: RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>
Subject: Re: sql2xls program problem
Date: Tue, 8 Mar 2005 07:37:14 +0100

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.

--
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.