- Command XLSCONVERT
- Spreadsheets containing formulas
- An XLSCONVERT failure
- The output file QTEMP/XLSOUTF
- Printing an XLS/XLSX spreadsheet on the iSeries
- Reading an XLS/XLSX spreadsheet on the iSeries
Use of Excel is very widely spread in small companies and in large companies departments
for local processes. Allowing to receive inputs from Excel XLS/XLSX spreadsheets
may increase the integration role of the iSeries. Excel inputs could be sent by e-mail and received by the iSeries:
MMAIL utility provides a way to receive e-mail messages and to detach Excel attachments
as individual IFS stream files.
On the iSeries, what is needed is a process able to convert any Excel XLS/XLSX spreadsheet
to a standard database file, that can subsequently be processed by iSeries applications.
Command XLPARSE2/XLSCONVERT is exactly that tool.
2- Command XLSCONVERT
Command XLPARSE2/XLSCONVERT converts a given spreadsheet of an Excel XLS or XLSX workbook -
residing on the IFS as a stream file - to a database file in library
QTEMP. Once the conversion is finished, the QTEMP database file can
be read by a local application.
- The Java job execution environment must not have been already set (through the command ADDENVVAR ENVVAR(CLASSPATH) ...).
If a classpath other than the expected one was set, you will receive a Java escape message saying that a class was not found.
- The user profile running command XLSCONVERT must have special authority *JOBCTL.
This is required by command ADDENVVAR which is executed by the program.
- If the command XLSCONVERT is executed from a program, the program is recommended be created with ACTGRP(XLPARSE2).
- The IFS stream file must be an Excel XLS or XLSX ASCII workbook containing at least one spreadsheet.
- The database file containing the converted data is physical file
Convert an Excel spreadsheet (XLSCONVERT)
Type choices, press Enter.
.xls/xlsx stream file . . . . . XLS
Sheet sequence number . . . . . SHEETNBR 1 1-999, *ALL
'Sheet name' . . . . . . . . . . SHEETNAME *SHEETNBR
Display the database file . . . DSPDBF *NO *YES, *NO
Print results . . . . . . . . . PRINT *NO *YES, *NO
Decimal precision . . . . . . . DEC 2 0-6
Columns forced as numeric . . . FRCNUMCOLS *NONE 1-500, *NONE
+ for more values
Date columns . . . . . . . . . . DATECOL *AUTO Number, *AUTO, *NONE
+ for more values
Date format . . . . . . . . . . DATEFMT *ISO *ISO
Time columns . . . . . . . . . . TIMECOL *NONE Number, *NONE
+ for more values
Time separator . . . . . . . . . TIMESEP * *, :, :, ., ,, -
Run in a sync submitted job . . SBMJOB *YES *YES, *NO
Job queue . . . . . . . . . . . JOBQ QSYSNOMAX Name
Library . . . . . . . . . . . *LIBL Name, *LIBL
Type of failure message . . . . FAILMSG *ESCAPE *ESCAPE, *DIAG
Set output record ID . . . . . . SETOUTID *NO *YES, *NO
- .xls/xlsx stream file (XLS) - This is the qualified name of the
stream file Excel Workbook.
IFS directory '/xlparse2/samples' contains a number of Excel workbooks
that can be used for testing command XLSCONVERT.
- Sheet sequence number (SHEETNBR) - The sequence number of the spreadsheet you want to process.
Specify *ALL if you want to convert all the spreadsheets together.
Instead of specifying the spreadsheet sequence number in parameter SHEETNBR,
you may specify the spreadsheet name in parameter SHEETNAME.
This is allowed only if:
Note. When you specify a sheet name in parameter SHEETNAME, the value specified in parameter SHEETNBR is ignored.
- the IBM i OS release is at least V76R1,
- utility HSSFCGI is installed, and
- service program HSSFCGI/XLPARSERJP is available.
- Sheet name (SHEETNAME) - The name of the sheet to be processed.
This value is case sensitive and must be specified within quotes (').
When you specify the spreadsheet name in parameter SHEETNAME, the sheet sequence number in parameter SHEETNBR is ignored.
Parameter SHEETNAME requires
- at least IBM i OS release V6R1,
- utility HSSFCGI must be installed, and
- service program HSSFCGI/XLPARSERJP must be available.
- Display the database file (DSPDBF) - Select one of the following:
- *YES to display file QTEMP/XLSOUTF once the conversion is complete.
This option can be used for testing.
- *NO to avoid displaying file QTEMP/XLSOUTF once the conversion is complete.
- Print results (PRINT) - Whether a printout of the converted spreadsheet is desired.
- Decimal precision (DEC) - Number of decimal digits to be shown after the decimal point.
When a spreadsheet cell contains a numeric value (example 142.27),
this value is retrieved as a floating point number (example
1.422700000000E+002). That does not tell how many decimal digits
should be displayed after conversion.
This is why this piece of information must be supplied through this parameter.
This parameter applies to all cells containing numeric values.
- Columns forced as numeric (FRCNUMCOLS) - Columns containing number-format cells and text-format cells do
result into a character-data fields in the generated database file record format.
To force these columns to generate numeric-data fields in the generated database file record format,
you must mention their numbers (1 for column A, etc.) in the entries of this parameter.
Up to 50 such columns may be specified.
Of course, if a specified column happens to contain just number-format cells, a numeric-data record format field is generated.
A text-format cell, to be converted to a number must containg a string where
- the minus ("-") sign, if needed, is the first character
- thousand separators are not used
- the "decimal point", if needed, is either character (".") or character (",") according to the national language of the Excel spreadsheet.
- English language spreadsheet: -8927.353 or 8927.36
- Italian language spreadsheet: -8927,353 or 8927,36
- Date columns (DATECOL) - In Excel spreadsheets, a date is stored as a floating point number.
This number represents the number of days elapsed since January 1, 1900.
In order to have numeric values converted back to dates, do one of the following:
- Type *NONE to avoid converting any numeric value to a date value.
This is the recommended choice unless you know that the spreadsheet contains at least one date column.
- Enter up to 50 column numbers to identify the columns where numeric values must be converted to date values.
This is the recommended choice when you know that the spreadsheet contains at least one date column.
- Type *AUTO to let the program establish which columns contain numeric values to be converted to date values.
This choice may provide unexpected results on numeric columns and is suggested only for test purposes, in order to find out if any date columns exist in the spreadsheet.
- Date format (DATEFMT) - Only *ISO date format (yyyy-mm-dd) is supported.
- Time columns (TIMECOL) - In Excel spreadsheets, the internal representation of a time value
(example: 16:48:56) is a number (example: 700648). This number measures
the time in millionths of a day (example of computation: there are 84,600 seconds in 24 hours;
time 16:48:56 is 69,536 seconds; therefore the Excel internal representation of time 16:48:56 is
In order to have Excel time numeric values converted back to a conventional time format (example: hh.mm.ss),
do one of the following:
- Type *NONE to avoid converting any numeric value to a time value.
This is the recommended choice unless you know that the spreadsheet contains at least one time column.
- Enter up to 50 column numbers to identify the columns where numeric values must be converted to time values.
This is the recommended choice when you know that the spreadsheet contains at least one time column.
- Time separator (TIMESEP) - This parameter is not made available when *NONE is specified for parameter TIMECOL.
Select one of the following:
|| * || ||to use the time separator defined in system value QTIMSEP.|
| : || ||to use a colon (:) as a time separator (example of result: 16:48:56)||
| . || ||to use a period (.) as a time separator (example of result: 16.48.56)||
| , || ||to use a comma (,) as a time separator (example of result: 16,48,56)||
| - || ||to use a blank ( ) as a time separator (example of result: 16 48 56) .||
- Run in a sync submitted job (SBMJOB) - If you specify SBMJOB(*YES), the XLSCONVERT command is executed in a separate submitted job.
The current job waits until the submitted job completes, then it resumes execution.
This can be useful in two cases:
Should you absolutely need to run this command in the current job, though the JVM has already been started by a previous Java application,
read about command JVMSTARTUP in Appendix JVMSTARTUP.
- you do not want XLSCONVERT to start a Java Virtual Machine in the current job, OR
- you do not want XLSCONVERT to run in the current job because it may conflict with a Java Virtual Machine already active in this job.
Important note for SBMJOB(*YES)
When SBMJOB(*YES) specified, the QTEMP files (example:XLSOUTF) created in the QTEMP library of the submitted job
are saved and restored to the QTEMP library of the submitting job.
- Job queue - If you specify SBMJOB(*YES), this is the job queue where the synchronized job is submitted.
- Type of failure message (FAILMSG) - When you specify SBMJOB(*YES), the XLSCONVERT command is executed in a synchronized submitted job.
It may however happen that the XLSCONVERT command fails during its execution.
In such a case a message is sent to the current program in the current job.
You may choose whether this message would be an *ESCAPE or a *DIAG (diagnostic) one.
- Set output record ID (SETOUTID) - The records of the output file
QTEMP/XLSOUTF have a field named OUTID. The value of this field is
intended to represent the data type of a record:
If you specify SETOUTID(*NO), no attempt is made to differentiate the
record data type, and all records are assigned value D in field OUTID.
- B means "sheet title"
- H means "column headers"
- D means "data columns"
If you specify SETOUTID(*YES), an attempt is made to identify the record
data types according to the three values previously listed.
3- Spreadsheets containing formulas
XLSCONVERT is able to parse three types of cells: text, number, formula.
Formulas are Excel expressions that are computed at Excel execution time.
When you look at an Excel spreadsheet, you cannot tell whether the cell values that you read come from some formulas.
To find it out, on the Excel tool bar you have to:
XLSCONVERT can tell whether the value resulting from a formula is a number or a character string, but - with the exception of a few cases -
cannot compute the value of a formula. In such cases, the conversion result is blank.
- press the Formula tab
- press the Show formulas button
When you have a spreadsheet containing formulas, the best thing is to copy all the spreasheet cells to the clipboard and paste "the cell values" to an empty sheet of another workbook.
Details of this operation are documented in Microsoft Office Support page
Copy cell values, not formulas.
Commands XLSCONVERT, XLSTABLE and XLSTABLE2 will have no problems in converting the spreadsheet resulting from this copy operation.
4- An XLSCONVERT failure
In some cases, XLSCONVERT may send the escape message "No significant cells detected in this sheet".
This may happen when a spreadsheet was generated from some software tool and some properties are missing.
You can manually fix the Excel workbook on a PC in this way:
Should you need that be done in an IBM i procedure of yours, you may bypass this problem by specifying in parameter
SHEETNAME the name of the spreadsheet you want to process.
- Open the spreadsheet with Excel (2003 or subsequent)
- Save the spreadsheet; Excel will add the missing properties.
Of course, utility HSSFCGI with service program XLPARSERJP is required.
5- The output file QTEMP/XLSOUTF
This database file contains the data converted from an Excel XLS or XLSX spreadsheet via command XLSCONVERT.
There is a record for each row.
A record contains information about the first 256 cells of a row (subsequent cells are ignored).
The record format is as follow:
- Field name OUTSHEET (50A) - The sheet name this row belongs to.
- Field name OUTSEQ (5S 0) - The number of this row within the sheet.
- Field name OUTID (1A) - The content-type of this row.
Possible values are:
- B - Sheet title
- H - column headers
- D - data columns
- Field name OUTNBRCOL (3S 0) - Number of columns in this row.
- Field name OUTDTA (5000A) - The contents of all the columns of this row.
A given column has always the same size across all rows in the same sheet.
The data-type and the length of each column are documented in the next two fields.
- Field name OUTCOLLEN (256 subfields, each 4B 0) - Each subfield contains the size (number of bytes) of a column.
- Field name OUTCOLTYPE (256 subfields, each 1A) - Each subfield contains the data-type of a column.
Possible values are:
- C - Character
- N - Numeric value in a character string;
Please note that the decimal point used is always a dot, character "." .
- S - Null value
6- Printing an XLS/XLSX spreadsheet on the iSeries
Command XLSCONVERT does that for you, whatever the layout of the XLS or XLSX spreadsheet is.
Just enter command XLSCONVERT XLS(...) PRINT(*YES) and you are done!
Check out our example.
7- Reading an XLS/XLSX spreadsheet on the iSeries
To read an XLS or XLSX spreadsheet (residing on the IFS as a stream file), you have the following options:
- Write your own program and use directly the Scott Klement's utilities provided in service program
XLPARSER4 (see this article).
This is what I did to write program XLSCONVERT. Sample exercises are provided by Scott
with programs XLPDEMO and XLPDEMOF (included in library XLPARSE2).
- Use my command XLSCONVERT to convert the spreadsheet to database file QTEMP/XLSOUTF.
Your ILE-RPG program supposed to read the spreadsheet, must
In order to demonstrate how this is easy, I wrote a small sample program that does exactly that.
It is named GETCELLS and here is its source:
- Be created with ACTGRP(XLPARSE2)
- Invoke a simple subprocedure (named XlsGetCell) to receive the spreadsheet cells.
- XlsGetCell('STR') to position before the first cell of the converted
spreadsheet available in file QTEMP/XLSOUTF
- XlsGetCell('GET') to retrieve all the cells, one at a time
- XlsGetCell('END') to reset positioning.
* Create this program as follow:
* CRTBNDRPG PGM(XLPARSE2/GETCELLS) SRCFILE(XLPARSE2/QRPGLESRC)
* DFTACTGRP(*NO) ACTGRP(XLPARSE2) DBGVIEW(*SOURCE)
* NOTE that activation group MUST be XLPARSE2
H option(*srcstmt : *nodebugio)
*Prototype of XLSGETCELL subprocedure
D XLSGETCELL PR 565
D Action 3 value options(*nopass)
* Procedure XLSGETCELL, any time it is called,
* returns a data structure containing information
* about the next spreadsheet cell.
* This information is retrieved from physical file QTEMP/XLSOUTF.
* This file contains the database version of the last spreadsheet
* converted by command XLSCONVERT.
* The following is the layout of the information data structure
* returned from this subprocedure:
D InfoDS ds
* Return code: 0=cell found, -1=No more cells.
D rc 10i 0
* Sheet name
D xsheet 50
* Row number of this cell
D xrownbr 11s 0
* Type of row: B=Sheet title, H=Column headers, D=Data columns
D xID 1
* Number of columns in this row
D xnbrcol 9s 0
* Column number of this cell
D xcolnbr 9s 0
* Type of the data in this cell: C=character, N=numeric (edited), S=null value
D xcoldtatyp 1
* Estimated column length of this cell
D xcollen 10i 0
* Data in this cell
D xcoldta 500
* Main line
InfoDS=xlsgetcell('STR'); //Start process
//loop getting spreadsheet cells, until no more cells (rc=-1)
eval InfoDS=xlsgetcell('GET'); //get the info about the next cell
InfoDS=xlsgetcell('END'); //END process