ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Creating Excel Documents

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

  • Creating Excel Documents

    Hello Fellow RPGers,

    More and more, we are getting requests to create reports in Excel. Currently, we have two methods of doing so:

    1) GoAnywhere Director
    2) Scott Klements HSSF utilities

    Personally, I love the HSSF utilities because it if very flexible, and everything can be done right in one RPG program. But, not knowing all the details of what this utility is using behind the scenes, we are worried that at some point, it may become inoperable in a future release of the operating system. Then we would have to go back and update all of our programs that used it. With GoAnywhere Director, we are paying for service, which we would hope would always be supported and functional.

    So, my question is, does anyone that has knowledge of what the HSSF utilities use behind the scenes have any worries, that at some point, they could be rendered unusable for whatever reason?

    Thanks in advance,

    John

  • #2
    Re: Creating Excel Documents

    the OS upgrades will not break HSSF. HSSF uses a java library called POI and is an Apache project. as long as you have the same .jar file for POI you should be safe. behind the scenes Scott's service program is plain ol' RPG that "calls" the java classes in the POI jar file.
    I'm not anti-social, I just don't like people -Tommy Holden

    Comment


    • #3
      Re: Creating Excel Documents

      Another option is to create your own Excel spreadsheet and format it as much as you want. Save as XML document and then use that XML file as your template to work with CGIDEV2.
      Your friends list is empty!

      Comment


      • #4
        Re: Creating Excel Documents

        Thank you both for your comments & suggestions. Both very helpful.

        John

        Comment


        • #5
          Re: Creating Excel Documents

          I just ran into an issue that hopefully someone knows the solution for. I was creating a large spreadsheet using the HSSF stuff, and I got an error message.

          java/lang/OutOfMemoryError

          I found some things that mention about allocating more memory to the job, but not sure how to implement this. The spreadsheet I am creating has in excess of 300,000 records.

          Thanks!

          John

          Comment


          • #6
            Re: Creating Excel Documents

            this is way beyond my knowledge as far as Java goes but i'm sure someone here will be able to help. just as a wild guess might be worth trying thing....

            call the hssf_begin_object_group procedure specifying a BIG number of objects (guesstimate based on cell styles, number of columns & expected number of rows) and see if it helps.
            I'm not anti-social, I just don't like people -Tommy Holden

            Comment


            • #7
              Re: Creating Excel Documents

              Isn't Excel limited to 64k rows? Could it anything to do with that?
              Your friends list is empty!

              Comment


              • #8
                Re: Creating Excel Documents

                Originally posted by mjhaston View Post
                Isn't Excel limited to 64k rows? Could it anything to do with that?
                duh...yes it could lol

                unless you're using multiple worksheets...
                I'm not anti-social, I just don't like people -Tommy Holden

                Comment


                • #9
                  Re: Creating Excel Documents

                  You are correct in saying about the row limit being 65xxx, but I am using the newer version(XSSF) that produces the Excel 2007(.xlsx) file types. I think this limit was eliminated in that version.

                  Tom, I took your advice and changed the parameter in the ss_begin_object_group() statement from 100 to 1000, but still the same result. I think the sure size(300,000+ rows) of this is filling the memory allocated for the job. I found some non-400 articles about allocating a set amount of memory, but don't know how to apply them to RPG and the HSSF/XSSF programs.

                  Any other suggestions would be greatly appreciated.

                  John

                  Comment


                  • #10
                    Re: Creating Excel Documents

                    it might be a good idea to post this question on http://www.systeminetwork.com/forums/ since Scott Klement hangs out there. since he wrote the service program he will know if there's something inside the service program to address this.
                    I'm not anti-social, I just don't like people -Tommy Holden

                    Comment


                    • #11
                      Re: Creating Excel Documents

                      Will do. Thanks Tom.

                      Comment


                      • #12
                        Re: Creating Excel Documents

                        .xlsx has a limit of 1,048,576 rows if I remember correctly. I use XSSF quite a bit here, can you post the specifics of the error? perhaps a job log, the more info the better.

                        Andy

                        Comment


                        • #13
                          Re: Creating Excel Documents

                          I'll try. Here is part of the job log:

                          Statement . . . . . . . . . : 23700
                          Thread . . . . : 0000001B
                          Message . . . . : Java exception received when calling Java method.
                          Cause . . . . . : RPG procedure SS_TEXT in program GENLIB/HSSFR4 received
                          Java exception "java.lang.OutOfMemoryError:" when calling method
                          "setCellValue" with signature "(Ljava.lang.StringV" in class
                          "org.apache.poi.ss.usermodel.Cell". Recovery . . . : Contact the person
                          responsible for program maintenance to determine the cause of the problem.
                          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

                          Also, got a spoolfile with this in it:

                          JVMDUMP006I Processing dump event "systhrow", detail "java/lang/OutOfMemoryError" - please wait.
                          JVMDUMP032I JVM requested Heap dump using '/home/JOHN/heapdump.20120420.074418.5751.0001.phd' in response to an event
                          JVMDUMP010I Heap dump written to /home/JOHN/heapdump.20120420.074418.5751.0001.phd
                          JVMDUMP006I Processing dump event "systhrow", detail "java/lang/OutOfMemoryError" - please wait.
                          JVMDUMP032I JVM requested Java dump using '/home/JOHN/javacore.20120420.074418.5751.0002.txt' in response to an event
                          JVMDUMP010I Java dump written to /home/JOHN/javacore.20120420.074418.5751.0002.txt
                          JVMDUMP032I JVM requested Snap dump using '/home/JOHN/Snap.20120420.074418.5751.0004.trc' in response to an event
                          JVMDUMP010I Snap dump written to /home/JOHN/Snap.20120420.074418.5751.0004.trc
                          JVMDUMP013I Processed dump event "systhrow", detail "java/lang/OutOfMemoryError".
                          JVMDUMP032I JVM requested Heap dump using '/home/JOHN/heapdump.20120420.074511.5751.0003.phd' in response to an event
                          JVMDUMP010I Heap dump written to /home/JOHN/heapdump.20120420.074511.5751.0003.phd
                          JVMDUMP032I JVM requested Java dump using '/home/JOHN/javacore.20120420.074511.5751.0005.txt' in response to an event
                          JVMDUMP010I Java dump written to /home/JOHN/javacore.20120420.074511.5751.0005.txt
                          JVMDUMP032I JVM requested Snap dump using '/home/JOHN/Snap.20120420.074511.5751.0006.trc' in response to an event
                          JVMDUMP010I Snap dump written to /home/JOHN/Snap.20120420.074511.5751.0006.trc
                          JVMDUMP013I Processed dump event "systhrow", detail "java/lang/OutOfMemoryError".

                          I have dumps, and all the above listed files in the IFS. Let me know if I can show you something else.

                          Comment


                          • #14
                            Re: Creating Excel Documents

                            From the looks of it, ss_begin_object_group(1000) may not be nearly enough memory. I usually start with no less than 10000, but that may not be enough for the size of the file your handling. You have to remember that this particular process can be memory intensive, so having said that, there are a couple of ways you can deal with that particular problem. You can use multiple tabs in the .xlsx to represent different parts of the data. If that doesn't work, or isn't in the scope of the project, you might try xml (that one is beyond me though). And, if all else fails... forego XSSF all together and write it as a .csv.

                            I've never tried the XSSF stuff against files of this size however, so outside of the suggestions above I'm at a loss. I've got some free time today I'll try creating something huge (dou ftw) and see if I can get it to work.

                            Andy

                            Comment


                            • #15
                              Re: Creating Excel Documents

                              Andy,

                              I changed it to:

                              ss_begin_object_group(1000000)

                              No difference.

                              Comment

                              Working...
                              X