Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public source
 
Introduction
 
XLSCONVERT utility
XLSTABLE utility
Some CGI programs
 
Appendixes:
JVMSTARTUP
 Questions & Answers
 
 PDF of this tutorial
 
 Download
 
 

 
 
 
Questions and Answers

Answers to some questions asked by XLPARSE2 users.

  1. Q: When running command XLPARSE2/XLSTABLE or XLPARSE2/XLSCONVERT, the following error is displayed:
    1. Escape program message:
      Program XLSCONVERT failed, see previous messages.
    2. Previous diagnostic message:
      Message . . . . : Error occurred while parsing spreadsheet after cell (0,0) in *Unknown*.
      Last cell processed was row 0, column 0 in sheet *Unknown*. Joblog message Java exception received when calling Java method. Error occurred while parsing spreadsheet after cell (0,0) in *Unknown*. Message ID . . . . . . : RNX0301 Severity . . . . . . . : 50 Message type . . . . . : Escape Date sent . . . . . . : xx/xx/xx Time sent . . . . . . : xx:xx:xx
    3. Additional message information (via F1):
      Message . . . . : Java exception received when calling Java method.
      Cause . . . . . : RPG procedure XLPARSE_WO in program XLPARSE2/XLPARSER4 received Java exception "java.lang.NoClassDefFoundError:
      com.iseriesnetwork.clubtech.xlparse.ParseSheet" when calling method
      "parse201003" with signature "([B)V" in class
      "com.iseriesnetwork.clubtech.xlparse.ParseSheet".
      Recovery . . . : Contact the person responsible for program maintenance to
      determine the cause of the problem.
    What can I do About it?
    A: This is a very common case. The error is that a Java class, needed by service program XLPARSE2/XLPARSER4, cannot be found.
    The way a Java class is retrieved by the Java Virtual Machine (JVM) is as follow:
    1. A search is made in IFS directory /QIBM/UserData/Java400/ext. This directory works like a system library list.
    2. If the search is unsuccessful, then the search goes through the IFS directories mentioned in the environment variable CLASSPATH, which works like a job library list.
    One major problem is that once the JVM has been started in a job, the current CLASSPATH is stored in its data, and cannot be changed even by changing the job CLASSPATH. This restriction has the following consequences:
    1. If the job has to process two or more applications using different classes, and each application sets up a different CLASSPATH, only the first application will work, the subsequent ones will fail in retrieving their Java classes.
    2. To overcome this problem, Java application developers often do install their classes in the IFS directory /QIBM/UserData/Java400/ext. In this way, other problems may raise when different applications have classes with the same name, and class methods are different. This may happen when two applications, built over a given Java package (example: POI), require a different release of that package.
      Because of this, Java classes used by XLPARSE2 are not in IFS directory /QIBM/UserData/Java400/ext, they are in IFS directory /hssfcgi/java, and XLPARSE2 sets up the appropriate CLASSPATH environment variable before calling the JVM.
    Once the above is understood, the ways to solve the NoClassDefFoundError are quite obvious:
    1. Make sure that IFS directory /QIBM/UserData/Java400/ext does not contain any POI-related and any XLPARSE-related objects.
    2. Run command WRKENVVAR(*SYS) and, if system level CLASSPATH environment variable is listed, remove it. Java applications should instead create their own CLASSPATH env.var's.
    3. If your job must execute some other Java applications beside running XLPARSE2 commands XLSTABLE and/or XLSCONVERT, in these XLPARSE2 commands specify the parameter SBMJOB(*YES). This parameter submits the execution of the command in a separate batch job and the submitting job waits for the completion of the submitted one.
    See also page JVMSTARTUP.
  2. Q: In processing some Excel spreadsheets with command XLSCONVERT or XLSTABLE, error message "No significant cells detected in this sheet" is displayed. How can I fix that?
    A: See topic An XLSCONVERT failure.
  3. Q: Do commands XLSCONVERT and XLSTABLE work OK with cells containing data from a selection list?
    A: Yes, the selected value is correctly received, provided it is a number, a test, or a formula.
  4. Q: How to retrieve the name of a given cell?
    A: Cell names are made of a number representing its row and a letter representing its column. Example: a cell in row 10 and column 5 has name $10$E.
    In XLPARSE2 service program XLPSARSER4, procedure xlparse_workbook(), while returning the row number and the column number of a cell, does not make up its name.
    However, service program XLPARSER4 procedure GetCellName() (see HSSFCGI/QRPGLESRC member XLPROC) returns the name of a cell in a given row and in a given column. Program HSSFCGI/DOCELLNAME (see HSSFCGI/QRPGLESRC member DOCELLNAME) provides an example of using such a procedure.
  5. Q: Does command XLSTABLE (command XLSCONVERT) provide the cell value resulting from a formula?
    A: No. That is computed by MS Excel, not by XLPARSE2. See Spreadsheets containing formulas.
  6. Q: How to print from IBM i an Excel Spreadsheet residing in a IFS directory?
    A: Write a program getting a cell at a time by using XLPARSE2/XLPARSER4 service program procedure XlsGetCell().
  7. Q: Is XLPARSE2 able to process OpenOffice spreadsheets?
    A: No.
  8. Q: Are XLPARSE2 commands XLSCONVERT and XLSTABLE able to retrieve the value (true/false) of a logical cell?
    A: No.