Unconfigured Ad Widget

Collapse

Announcement

Collapse
No announcement yet.

TechTip: Excel on the Fly

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

  • TechTip: Excel on the Fly

    Thanks Costagliola, I appreciate you taking the time to help me here. When I follow your instructions, I receive "Invalid class file format" errors, "wrong version: 46, expected 45". Is this due to the version of Java on my machine (1.1.7 on V4R4M0)?? Do I need to recompile/recreate the POI-2.0.jar for this version in order to make this thing work?

  • #2
    TechTip: Excel on the Fly

    I have tested both versions (rpg and java) at V5R1 and R2. You can't call java this way on earlier releases. JSQLXLS compiles and runs at 1.2 but I'm not sure that you can run at 1.1.

    Comment


    • #3
      TechTip: Excel on the Fly

      This utility shows how to run dynamic sql and how to call java from rpg. It's a general purpose free utility, that can be useful to create directly from your as400 small/medium spreadsheet (in final .xls format), with headers, formatting, etc. You can easily add colors, borders, footers and formulas. The problem with this RPG version is that is not suitable for very large spreadsheet, because you can easily find out that elapsed is not linear with number of rows: if you write 1,000 rows it takes, say half minute; 5,000 rows 5 minutes, but for 15,000 rows the utility could run for 30-40 minutes. I suppose that this is due to the fact that cells and rows (as java objects) are kept in memory and not reclaimed up to the end of the process. The gc has to inspect a larger number of objects at each interval. Maybe we could try to use a different way of grouping objecs to be cleaned. Following some of the requests I've received, I've prepared a version that has some fixes, including manual start/end of JVM, especially for bath running, but the limitations of rpg/java still remain. The pure java version is a simple program with the only purpose of performance comparison, but can be easily implemented because the Jakarta distribution contains many examples that show howto, that I've used for rpg porting. However, if you want a heavy duty program you can consider BUYING third party tools.

      Comment


      • #4
        TechTip: Excel on the Fly

        In order to fix some errors, please make some patches in the following sections: - Reallocate space for SQLDA if more than 20 fields Because %alloc() does NOT re-initialize storage, even if you set *inlr=*on from a previous call, storage allocated can contain garbage, we should replace this instruction with %realloc. - Create a new WorkBook If you create a Workbook from a template, you could get a Java exception. - Retrieve System Values Miscoding
        Code

        Comment


        • #5
          TechTip: Excel on the Fly

          How can you embed JPGS in a generated Excel spreadsheet? I can put in pictures, but when I re-sort the page, the pictures don't move.

          Comment


          • #6
            TechTip: Excel on the Fly

            You can join the POI user list at jakarta.apache.org where you can find some postings that could help using all the features of POI api's.

            Comment


            • #7
              TechTip: Excel on the Fly

              When I try to use SQL2XLS I get a class not found message: Message . . . . : Java exception received when calling Java method. Cause . . . . . : RPG procedure SQL2XLSR in program QGPL/SQL2XLSR received Java exception "java.lang.NoClassDefFoundError: org/apache/poi/hssf/usermodel/HSSFWorkbook" when calling method "" with signature "()V" in class "org.apache.poi.hssf.usermodel.HSSFWorkbook". I've check the classpath, '/excel/POI-2.0.jar' was the only thing there. I've tried changing to '/excel/:/excel/POI-2.0.jar' but I still get the same message. What am I missing? Thanks, Charles

              Comment


              • #8
                TechTip: Excel on the Fly

                Charles, the simplest way to know if the jar is in the right place is to run the following: CRTJVAPGM '/excel/poi-2.0.jar' OPTIMIZE(40) . . and remember that java is case sensitive, then "POI-2.0.jar" is NOT THE SAME as "poi-2.0.jar" !!! Giuseppe.

                Comment


                • #9
                  TechTip: Excel on the Fly

                  Costagliola, Interestingly enough, I signed on to a different session and it worked successfully. Also, seems to be working from any session this morning; note that I shut down my PC at night. However, I'm having difficulty with one of the SQL statements I'm trying to use. Take a look at the post titled "SQL2XLS fails using a table *UDF" Thanks, Charles

                  Comment


                  • #10
                    TechTip: Excel on the Fly

                    Ok, I've got SQL2XLS running with simple SQL statements. However, when I try and use the following: select * from table(cwlibr/PartsGoingOutOfStock()) as X I get the following set of messages: SQL0311 - Length in a varying-length or LOB host variable not valid. SQL0501 - Cursor CURSOR not open. SQL0501 - Cursor CURSOR not open. SQL0501 - Cursor CURSOR not open. The same statement in STRSQL works fine. Also, interestingly the excel spreadsheet is created, but it contains only column headers (*FLDNAM) without any data. I haven't looked at this in any more detail, but I wondered if this was a known limitation of the tool. Thanks, Charles Wilt

                    Comment


                    • #11
                      TechTip: Excel on the Fly

                      Charles, I' ve tried to create a simple UDTF as follows: Create Function GetFondi (Tipo varChar(1)) Returns Table (Codice Char(2), Descrizione Char(25)) Language Sql Disallow Parallel Set Option Commit=*None, Datfmt=*Iso Begin Return Select C6B8CD, C6HYTX From Affon00f Where C6COST = Tipo; End Than i ran the utility: SQL2XLS SQLSTMT('select * from table (GetFondi (''O'')) as ListaFondi') TOXLS('/home/costagliol/udtf.xls') and it works fine. No errors. Giuseppe. PS. I've have an updated version of the utility. Write me directly.

                      Comment


                      • #12
                        TechTip: Excel on the Fly

                        Giuseppe, I've sent you an email directly. Some additinoal info, I tried creating a very simple UDTF: create function cwlibr/TestUDTF() returns table ( Vendor_Number varchar(6), Vendor_Name varchar(40)) language SQL reads SQL data disallow parallel BEGIN return select vdcode, vdname from meldbf/mpvend; end I get the the same cursor not open messages. Charles

                        Comment


                        • #13
                          TechTip: Excel on the Fly

                          Charles, would you try this ? In section *sF* please add the following statements: when SQLTYPE = 448 or SQLTYPE = 449; // .. VarChar SQLLEN += 2; cSQLLEN = SQLLEN; SQLTYPE += 4; This should make your UDTF working properly. Giuseppe.

                          Comment


                          • #14
                            TechTip: Excel on the Fly

                            Giuseppe, That fix the problem. It runs without an error messages now. Thanks for your help, Charles

                            Comment


                            • #15
                              TechTip: Excel on the Fly

                              With the following select: select RunOutDate as [RunoutDate] from Myfile My Excel spreadsheet has the date in DD/MM/YY format with the correct column heading. Being in the USA, I'd prefer *USA or *MDY. Using SQLLOCVAL(*YES) on the command didn't seem to have an effect. Nor did changing the datefmt d-spec from inz('DMY') to inz('MDY'). Using the following instead: select char(RunOutDate, USA) as [RunoutDate] from myfile Gives me an Excel spreadsheet with a date in MM/DD/YY but the column heading disappears. Any Thoughts before I start looking at this in debug? Thanks, Charles

                              Comment

                              Working...
                              X