iEXCEL

The utility iEXCEL is driven by a single command, iweb/iexcel, through which you may display a DB2 database file in an .xls Excel spreadsheet.

  • Field definitions are integrated in the Excel document
  • Column titles are taken from the database fields column headings
  • The display of the document is performed by the iWEB Runtime Applet in a new browser window

 

1.1 - Prequisites

iEXCEL requires the free of charge IBM License Product 5722JV1 with the following options:
  optiontitle
*baseDeveloper kit for Java
5Java Developer Kit 1.3

1.2 - Command iExcel

>>-IEXCEL--FILE(--library-name/file-name--)--------->
>--SELECT(--+-*-------------------+)------------>
            ‘-SQL Select clause-‘
>--WHERE(--+-*NONE-------------------+)------------>
            ‘-SQL Where clause-‘
>--ORDER(--+-*NONE-------------------+)------------>
            ‘-SQL Order By clause-‘
>--TOSTMF(--+-*NONE-------------------+)------------>
            ‘-to-stream-file-name.xls-‘
>--RUN(--+-*YES-+--)-------------------------------->
          -*NO-
Figure 1-1 Structure of command iExcel  

Command parameters

  • FILE - Qualified name of the input DB2 database file.
  • SELECT - Character string (2048 char max, within quotes) specifying an SQL SELECT rule.
    Default value is *.
    If used, it should mention the database field names to be used for selecting, separated by commas.
    Example:
    SELECT('BTYPE, BLEN, BPRICE')
  • WHERE - Character string (2048 char max, within quotes) specifying an SQL WHERE rule.
    Default value is *NONE.
    If used, it should mention the database field names to be investigated. SQL keywords AND and OR may be used. Each field name must be followed by a compare operator ("=", "<" or ">") and a comparison value.
    • comparison values related to alphanumeric fields must be within quotes (')
    • comparison values related to numeric fields must be without quotes (')
    Example:
    WHERE('BTYPE=''Superboat'' OR BLEN>100')
  • ORDER - Character string (2048 char max, within quotes) specifying an SQL ORDER BY rule.
    Default value is *NONE.
    If used, it should mention the database field names to be used for ordering, separated by commas.
    Example:
    ORDER('BTYPE,BLEN')
  • TOSTMF- Fully qualified name of the IFS stream file where the Excel document should be generated.
    Extension .xls is mandatory.
    The user profile running the iExcel command must have write authority over the specified IFS path.
    You must also make sure that the target directory is allowed to the HTTP instance (e.g. the iWEB Runtime instance) supposed to be used to display the graph.
    If you specify *NONE for this parameter, you must also specify RUN(*YES). In this case, just a temporary Excel document is created. Once displayed, it is gone.
  • RUN - This parameter tells whether the Excel document should be immediately displayed by the iWEB Runtime Applet. Select
    • *YES, to have the graph immediately displayed bt the iWEB Runtime Applet
    • *NO, to just save the Excel document as an IFS stream file. Such a graph could be shown at a later time, see section Displaying an iExcel document at the end of this chapter.

1.3 - Dynamic command generation

Usually the iweb/iExcel command is generated and run from an RPG or a CL program. Typically such a program would

  • run the iweb/iExcel command by calling program qsys/qcmdexc. 

D iExcelCmd       s           2000
D cmdLen          s             15p 5 inz(%len(iExcelCmd))

 * Build the iExcel command
C                   eval      iExcelCmd='iweb/iExcel +
C                             file(iwebdemo/boatsale) +
C                             tostmf(''/iwebdir/dir01/boatsale.xls'') +
C                             select(''btype,blen,bprice'') +
C                             where(''btype=''''Superboat'''' +
C                             or blen>100'') +
C                             order(''btype,blen'') +
C                             run(*yes)'

 * Run the iExcel command
C                   call      'QSYS/QCMDEXC'
C                   parm                    iExcelCmd
C                   parm                    cmdLen
Figure 1-2 Program generating the iExcel document

Figure 1-3 Sample iExcel document

1.4 - Try iExcel now

Trying iExcel trough an iWEBMaster button running a program of your own could initially result to an useless cumbersome experiment.

On the other way, trying iExcel "asis" through a simple native WEB interface may rise some excitement of yours and provide you with some adequate counseling as to provide similar functions to your users.

If you feel like trying iExcel now, and if you already did download and install the IWEBDEMO, you may try to run iExcel by entering the following in your WEB browser location line:
http://as400TcpAddress:portNumber/iwebp/doiexcel.pgm
where

  • as400TcpAddress is the TCP/IP address of your AS/400
  • portNumber is the port used by your iWEB Runtime instance
As a result you would get the page in Figure 1.4: try and enjoy it!

Figure 1-4 Try iExcel now

1.5 - Displaying an iEXCEL document through iWEB Runtime

To create an iEXCEL document, command iweb/iExcel must be executed. This is generally done through an RPG or a CL program (see Figure 1-1). The question is when and how such a program would be called.

One way could be that of generating the iExcel IFS stream file in advance, for instance through a daily batch job. The iExcel document could then be displayed through one of the following techniques implemented via iWEBMaster language:

  • Creating a "link" to be connected to some display file record format(s); example:
    IWEB/CRTOBJDFN OBJ(...) OBJTYPE(LINK) HREF('/iwebdir/dir01/boatsale.xls') ...
    In this case the iExcel document is displayed along with the 5250 screen page.
  • Creating a "button" to appear on some some display file record format(s); example:
    IWEB/CRTOBJDFN OBJ(...) OBJTYPE(BUTTON) BUTTONACT(LINK) HREF('/iwebdir/dir01/boatsale.xls target=_blank') ...
    In this case the graph is displayed in a new window when the user presses the button.

If, instead, the iExcel document should be temporarily generated and displayed at the time the user presses a button, the technique would be that of assigning the display file record format a iWEBMaster language button invoking the iExcel document generating program. Example:
IWEB/CRTOBJDFN OBJ(...) OBJTYPE(BUTTON) BUTTONACT(SYSCMD) SYSCMD('''call mylib/mypgm''')