Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: Excel on the Fly

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    TechTip: Excel on the Fly

    If the colum headers disappear, try using COLHDRS(*ANY). To use a *MDY you can change the "datefmt" subfield in "sqlp0100". The original version has a bug in SQLLOCVAL. See the previous postings for a fix. Giuseppe.

    Comment


    • #17
      TechTip: Excel on the Fly

      As I mentioned in my first post, I had already tried changing the "datefmt" subfield in "sqlp0100". It had no effect, dates still coming out DMY. I'll look for more info about the SQLLOCVAL bug, maybe it is related. Charles.

      Comment


      • #18
        TechTip: Excel on the Fly

        When I changed the pSQLDA = %alloc in section sB line 731 to use %realloc. I received a "CEE0810 - The starting address for reallocation is not valid." error. I changed it back to %alloc and it continues to work. Did I miss something? I don't understand the reason for this change. According to the RPG manual neither %alloc or %realloc initialize storage. Thanks, Charles Wilt

        Comment


        • #19
          TechTip: Excel on the Fly

          As long as date format and other environvent parameters are stored in *sqlpkg, that is created only once in QTEMP, you should delete it or re-signon, and then run the utility again to let your changes take effect. Giuseppe.

          Comment


          • #20
            TechTip: Excel on the Fly

            I would like to use the full 60 character labels (3 20-byte segments) for the spreadsheet column headings, however the SQLDA documentation says that only the first 20 bytes are returned in the SQLVAR. I'm looking for any ideas or suggestions as to the best method to do this. Thanks, Dave Senger

            Comment


            • #21
              TechTip: Excel on the Fly

              I'm a descent RPG programmer that is completely out of my element. I have litte to no experience wiht JAVA. I have tried to follow the instructions here, but i cannot find the file POI-2.0.jar on the Apache Mirror sites. Instead, I locate subfolders (at the locations indicated) containg ZIP files... so I downloaded "poi-bin-2.5.1-final-20040804.zip". This huge file contained all kinds of PDF and HTML files along with "poi-2.5.1-final-20040804.zip". I renamed this file dropping the final & CCYYMMDD and placed it on my IFS in /excel. I ran the CRTJVAPGM, downloaded and compiled the source. When I tried to run the SQL2XLS command I received the following message in my job log: SEE CODE BELOW I tried changing the RPGLE pgm to point to my JAR file and received the same error. I am in the process of CRTJVAPGM using the "poi-2.0.jar" I found in your ZIP file. I'm going to try that. I am running v5r2 on a model 270. FYI - the CRTJVAPGM on the file I downloaded took over 2 hours.
              Code

              Comment


              • #22
                TechTip: Excel on the Fly

                Hi, you can do one of the following - download the 2.5 final jar from apache and rename it to poi-2.0.jar - download the 2.5 final jar and rename to poi-2.5.jar and change the references in the classpath into the rpg program - download the poi-2.0.jar from MC article of november The 2.5 had a big bug with images. Probably it has been fixed with 2.5.1 but I'm not sure. Use 2.0 and you are right. Very soon I will present another implementation in pure java with a different set of api's especially suitable for large xls creation at hi-speed. Don't miss it. CRTJVAPGM takes a long time. Submit in batch and be patient.

                Comment


                • #23
                  TechTip: Excel on the Fly

                  Thank you... I simply downloaded the 2.0 jar and things seem to be working. (I had already tried using the 2.5.1 and changing the RPG code - I had no luck with this). Several things I've noticed when using this. 1. If the excel file already exists in the target folder, it does not simply over-write with new data. 2. I have several excel templates (i currently use with iSeries Data Transfer) that contain excel macros. If I use these in the "From XLS" parameter of SQL2XLS, it fails to create the spreadsheet. Other templates w/out macros seem to work fine. (note: These macros simply subtotal several columns). Thanks again for your help.

                  Comment


                  • #24
                    TechTip: Excel on the Fly


                    Code

                    Comment


                    • #25
                      TechTip: Excel on the Fly

                      I have SQL2XLS working on my test box and would like to install it on other client systems. Is it possible to transfer the java "class" files by copying specific objects or stream files or is it required that you run the CRTJAVA command on each box you want to install this utility on. Joel

                      Comment


                      • #26
                        TechTip: Excel on the Fly

                        POI should preserve macros. I suggest to download the latest jar, put into /excel ifs, and rename to poi-2.5.jar, change the statement that adds the classpath into the rpg program, and recompile: // set CLASSPATH environment rc = putenv('CLASSPATH=/excel/POI-2.5.jar'); When you try the new version don't forget to signoff and signon again !! Pretty soon I will make available a pure java version and you can try this one. You can join the POI-Jakarta forum to get additional informations on this api usage. Giuseppe.

                        Comment


                        • #27
                          TechTip: Excel on the Fly

                          Joel, other system you mean another as400 ? Just create the ifs /excel and copy the poi jar into it. the crtjvapgm is suggested to get better performances but the Jit will create the program on the fly the first time you run it (and it will take some time).

                          Comment


                          • #28
                            TechTip: Excel on the Fly

                            I am trying to compile the source for the command SQL2XLS on my Iseries it does compile... although when I run the command it gets an error saying "Pointer not set for location referenced."! A more complete message then comes up that says "Pointer or parameter error (C G D F).". It seems that the command processing program SQL2XLSR is not receiving one of its parameters from the execution of the command! Do you know what might cause this to happen?

                            Comment


                            • #29
                              TechTip: Excel on the Fly

                              The cmd SQL2XLS from August article has some more parameters compared to the first version of November. Maybe you are using the new command with the old rpg or viceversa.

                              Comment


                              • #30
                                TechTip: Excel on the Fly

                                My RPGLE has 15 parameters and my command has 11 parameters. How do I get the most recent version. Do you have a web site that I can go to.

                                Comment

                                Working...
                                X