Questions and Answers
Answers to some questions asked by XLPARSE2 users.
- Q: When running command XLPARSE2/XLSTABLE or XLPARSE2/XLSCONVERT, the following error is displayed:
What can I do About it?
- Escape program message:
Program XLSCONVERT failed, see previous messages.
- 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*.
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
- 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
Recovery . . . : Contact the person responsible for program maintenance to
determine the cause of the problem.
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:
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:
- A search is made in IFS directory /QIBM/UserData/Java400/ext. This directory works like a system library list.
- 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.
Once the above is understood, the ways to solve the NoClassDefFoundError are quite obvious:
- 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.
- 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.
See also page JVMSTARTUP.
- Make sure that IFS directory /QIBM/UserData/Java400/ext does not contain any POI-related and any XLPARSE-related objects.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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().
- Q: Is XLPARSE2 able to process OpenOffice spreadsheets?
- Q: Are XLPARSE2 commands XLSCONVERT and XLSTABLE able to retrieve the value (true/false) of a logical cell?