1. Why
2. Command XLSCONVERT
3. Spreadsheets containing formulas
4. An XLSCONVERT failure
5. The output file QTEMP/XLSOUTF
6. Printing an XLS/XLSX spreadsheet on the iSeries
7. Reading an XLS/XLSX spreadsheet on the iSeries

1- Why

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 QTEMP/XLSOUTF.

                    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

                           Additional Parameters                      
                                                                      
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:
  • the IBM i OS release is at least V76R1,
  • utility HSSFCGI is installed, and
  • service program HSSFCGI/XLPARSERJP is available.
  Note. When you specify a sheet name in parameter SHEETNAME, the value specified in parameter SHEETNBR is ignored.
• 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
  1. the minus ("-") sign, if needed, is the first character
  2. thousand separators are not used
  3. the "decimal point", if needed, is either character (".") or character (",") according to the national language of the Excel spreadsheet.
  Valid examples:
  • 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 (69,536/84,600)*1,000,000=700648 .
  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:
  • 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.
  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.
  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:
  • B means "sheet title"
  • H means "column headers"
  • D means "data columns"
  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.
  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:

1. press the Formula tab
2. 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

• Open the spreadsheet with Excel (2003 or subsequent)
• Save the spreadsheet; Excel will add the missing properties.

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:

1. Field name OUTSHEET (50A) - The sheet name this row belongs to.
2. Field name OUTSEQ (5S 0) - The number of this row within the sheet.
3. Field name OUTID (1A) - The content-type of this row. Possible values are:
   • B - Sheet title
   • H - column headers
   • D - data columns
4. Field name OUTNBRCOL (3S 0) - Number of columns in this row.
5. 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.
6. Field name OUTCOLLEN (256 subfields, each 4B 0) - Each subfield contains the size (number of bytes) of a column.
7. 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:

1. 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).
2. Use my command XLSCONVERT to convert the spreadsheet to database file QTEMP/XLSOUTF.
   Your ILE-RPG program supposed to read the spreadsheet, must
   1. Be created with ACTGRP(XLPARSE2)
   2. Invoke a simple subprocedure (named XlsGetCell) to receive the spreadsheet cells.
      Use
      • 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.
   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:
   
   

   *======================================================================== * 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 BNDDIR('XLPARSE2/XLPARSE2') H optimize(*NONE) H decedit(*JOBRUN) H truncnbr(*NO) 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 *======================================================================== /free rc=0; InfoDS=xlsgetcell('STR'); //Start process //loop getting spreadsheet cells, until no more cells (rc=-1) dow rc=0; eval InfoDS=xlsgetcell('GET'); //get the info about the next cell enddo; InfoDS=xlsgetcell('END'); //END process *inlr=*on; return; /end-free