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 spreadsheets may increase the integration role of the iSeries. Excel inputs could be sent by e-mail and received by the iSeries: MMAIL freeware 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 spreadsheet to a standard database file, that can subsequently be processed by iSeries applications.

Command XLPARSE/XLSCONVERT is exactly that tool.

2- Command XLSCONVERT

Command XLPARSE/XLSCONVERT converts an Excel XLS spreadsheet - 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 user profile running command XLSCONVERT must have special authority *JOBCTL. This is required by command ADDENVVAR which is executed by the program.
• The IFS stream file must contain an Excel XLS spreadsheet in ASCII characters (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, or 1252-IBM PC).
• The XLS spreadsheet must be an Excel workbook of one or more sheets.
• The database file containing the converted data is physical file QTEMP/XLSOUTF.

                    Convert an Excel spreadsheet (XLSCONVERT)         

 Type choices, press Enter.
                           
 .xls stream file . . . . . . . . XLS                                             
                                                                                  
 Display the database file  . . . DSPDBF      *NO           *YES, *NO
 Print results  . . . . . . . . . PRINT       *NO           *YES, *NO
 Decimal places . . . . . . . . . DEC         2             0-6
 Date format  . . . . . . . . . . DATEFMT     *YMD          *YMD, *MDY, *DMY
 Date columns . . . . . . . . . . DATECOL     *AUTO         Number, *AUTO, *NONE
                + for more values                  

                           Additional Parameters                      
                                                                      
Set output record ID . . . . . . SETOUTID     *NO           *YES, *NO            
Classpath for java tools . . . . CLASSPATH    *DTAARA       *NONE, *DTAARA       
Java version (e.g. 1.4)  . . . . JAVAVER      1.4           Character value, *DFT

• .xls stream file (XLS) - This is the qualified name of the stream file (CCSID 819-ASCII ISO Latin 1, 1212-PC USA, or 1252-IBM PC) containing the Excel XLS spreadsheet to be converted to database file QTEMP/XLSOUTF.
  IFS directory '/xlparse/samples' contains a number of spreadsheet stream files that can be used for testing command XLSCONVERT.
• 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 places (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.
• Date format (DATEFMT) - Date fields are generated as 10 bytes character strings. The date separator character is "-" .
  Select one of the available formats:
  • *YMD - Date representation is yyyy-mm-dd
  • *MDY - Date representation is mm-dd-yyyy
  • *DMY - Date representation is dd-mm-yyyy
• 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.
  • Type *AUTO to let the program establish which columns contain numeric values to be converted to date values.
  • Enter up to 50 column numbers to identify the columns where numeric values must be converted to date values.
• 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.
• Classpath for java tools (CLASSPATH) - Specifies the classpath to locate the Java tools used. These are the 'POI' tools, created by the Apache Jacarta Open source, and xlparse.jar, created by Scott Klement.
  The classes are located in directory /xlparse/java. The default is to use them, however if you have the POI classes installed, you may prefer to use them instead.
  The classpath is set using the *JOB level environment variable 'CLASSPATH'.
  The classpath, if one is used, is stored in the data area XLSCONVERT. This will be located using the library list.
  • *NONE - No classpath environment variable is created.
  • *DTAARA - The contents of the data area XLSCONVERT are set for the job level environment variable CLASSPATH. If this environment variable already exists, it's value is replaced.
• Java version (JAVAVER) - Specifies the Java version to be used when running the Java component of the conversion tools. The minimum Java version required is 1.4 .
  • *DFT - The default version of Java will be used.
  • character-value - Specify the version of Java to be used.

3- The output file QTEMP/XLSOUTF

This database file contains the data converted from an Excel XLS spreadsheet via command XLSCONVERT. There is a record for each row. 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 sime 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 (500 subfields, each 4B 0) - Each subfield contains the size of a column in chars.
7. Field name OUTCOLTYPE (500 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

4- Printing an XLS spreadsheet on the iSeries

Command XLSCONVERT does that for you, whatever the layout of the XLS spreadsheet is.
Just enter command XLSCONVERT XLS(...) PRINT(*YES) and you are done!
Check out our example.

5- Reading an XLS spreadsheet on the iSeries

To read an XLS 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 XLPARSE).
2. Use my command XLSCONVERT to convert the spreadsheet to database file QTEMP/XLSOUTF.
   Then use a simple subprocedure (named XlsGetCell): 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:
   
   

   H BNDDIR('XLPARSE/XLPARSE') 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 D rc 10i 0 * Return code: 0=cell found, -1=No more cells. D xsheet 50 * Sheet name D xrownbr 5s 0 * Row number of this cell D xID 1 * Type of row: B=Sheet title, H=Column headers, D=Data columns D xnbrcol 3s 0 * Number of columns in this row D xcolnbr 3s 0 * Column nymber of this cell D xcoldtatyp 1 * Type of the data in this cell: C=character, N=numeric (edited), S=null value D xcoldtalen 3s 0 * Length of the data in this cell D xcoldta 500 * Data in this cell *======================================================================== * Main line *======================================================================== /free eval 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 eval *inlr=*on; return; /end-free