Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
XLSMDL utility
 CGI development

CGI development
by Giovanni B. Perotti (Italy)
Source file XLSCGI/QRPGLESRC contains the sources of some sample CGI programs:
  • SAMPLECGI1 - This is a simple traditional CGI program that provides an HTML table row for each record in a database file.
    Run this program.
    Display its source.
  • SAMPLECGI2 - This is the same program as SAMPLECGI1. In this case, however, the output HTML contains a button: if you press it, the same program will now generate an XML spreadsheet (as illustrated in this page) and send it to your browser.
    Run this program.
    Display its source.
    By comparing the the SAMPLECGI2 source vs the SAMPLECGI1 source, one can understand what has to be done in order to enable a CGI program to provide spreadsheet output.
  • SAMPLECGI3 - This program is a subset of SAMPLECGI2. It creates an XML spreadsheet and sends it right away to the browser.
    Run this program.
    Display its source.
  • SAMPLECGI4 - This program is a similar to SAMPLECGI3, but instead of sending the XML spreadsheet to the browser, it saves it on a stream file.
    This program must be run from a greenscreen terminal. Use command CALL XLSCGI/SAMPLECGI4 .
    Display its source.

1- Command XLSCGILIB

CGI programs using XLSCGI facilities to create Excel spreadsheets, need to use service program XLSCGI/XLSCGI. The best way to reference service program XLSCGI/XLSCGI is tho have the CGI programs referencing a binding directory that includes an entry for service program XLSCGI/XLSCGI.
Command XLSCGILIB SRCLIB(source_library_name) adds such an entry to the first binding directory found in the library mentioned in parameter SRCLIB.

2- ILE-RPG coding requirements

  1. Binding spec
    Make sure you have H binding specifications in place and that the binding directory - mentioned in the H binding spec- has an entry for service program XLSCGI/XLSCGI.
  2. Prototypes
    Prototypes for subprocedures of service program XLSCGI must be added through the following statement:
    /copy XLSCGI/qrpglesrc,xlscgiprot
  3. Loading external HTML
    • External HTML must be loaded through subprocedure getHtmlIfsMult
    • the common section delimiter mshould be <as400>
    • The IFS stream files to be loaded must include
      1. the XML-spreadsheet script generated from the XLSMDL utility (see previous page)
      2. stream file
        This is an HTML bootstrap file issued by subprocedure SndtoExcel (see later) for outputting to the browser the final .XLS script.
        Just add this stream file to the list of stream files to be loaded from subprocedure getHtmlIfsMult.
  4. Output sections
    The CGI should issue
    • The top section of the the XML-spreadsheet script, just once.
    • The row section of the the XML-spreadsheet script for each row of the spreadsheet. The output /%variables%/ should be set appropriately (read further).
    • The bottom section of the the XML-spreadsheet script, just once.
  5. Sending out the XML script to the browser
    At last, the CGI program should
    callp SndtoExcel()
    This is a local subprocedure to be included at the end of the CGI source program through the statement
    /copy XLSCGI/qrpglesrc,sndtoexcel
    This subprocedure
    1. uses the output buffer to create a temporary XML stream file (Excel compatible) in directory /xlscgi/tmp
    2. sends to the browser an HTML bootstrap for downloading the temporary XML /xlscgi/tmp stream file for Excel execution
    3. schedules deletion of the temporary XML /xlscgi/tmp stream file after 90 seconds

3- About the /%output%/ variables

Excel is extremely sensible to the format of the output variables (the variables that give values to the the row cells). Whenever a variable does not match the expected input format, Excel does not display the spreadsheet but displays an error box instead (see the next topic).

In order to avoid this from happening, the CGI program developer must pay maximum attention in applying the following rules:

  • character fields
    Some characters may not be accepted from Excel, for instance accented letters (like à è é ì ò ù) are not accepted.
    Therefore, character strings must be inspected, and unsupported characters must be changed, before being transmitted.
    This can be done through special subprocedure fixString
    udhtmlvar ('fieldname':fixString(fieldname));
    Subprocedure fixString retrieves unsupported characters from file XLSCGIDATA/UNSCHR, using the job CCSID as a key and replaces them with a question mark (?).
    It is a local responsibility to specify which characters are not supported by Excel in a given national language. This will become clear as soon as you hit cases where Excel sends out an error message instead of providing the expected table.
  • numeric fields
    They must be transmitted as character strings, preceeded by a minus sign if negative, with a dot (.) as decimal point.
    Use the following code:
    D inpNumber       s             30p 9
         callp updhtmlvar('fieldname':edtNumber(inpNumber));
    EdtNumber() is a subprocedure in service program XLSCGI that provides the string pattern expected by Excel for numbers.
  • date fields
    The format selected with XLSMDL is the format that Excel will output.
    However, Excel may require the XML input to have a format different from the one that Excel will display in output.
    format selected
    with XLSMDL
    and outputted
    by Excel
    format of output
    from the CGI
    and input
    to Excel
    example of the input
    to Excel
    yyyy-mm-dd yyyy-mm-dd 2009-03-27
    dd/mm/yyyy yyyy-mm-dd 2009-03-27
    mm/dd/yyyy mm/dd/yyyy 03/27/2009
    dd/mm/yy yyyy-mm-dd 2009-03-27
    mm/dd/yy mm/dd/yyyy 03/27/2009
  • time fields
    Also in this case Excel requires the XML input to have a format different from the one that Excel will display in output.
    format selected
    with XLSMDL
    and outputted
    by Excel
    format of output
    from the CGI
    and input
    to Excel
    example of the input
    to Excel hh:mm:ss 05:27:52 hh:mm 05:27

4- What to do if Excel bumps out

If Excel finds that something is wrong in its XML input, it does not display the spreadsheet, it issues a short error message like this:
Figure 9 - Excel error message

It may be very difficult to access the error log indicated in the error message, because usually some subdirectories are protected. The best way I found was to use the Internet browser. For instance, in the above case I used the following URL:


and I could read the following:
XML error in Table
REASON:	Wrong value
FILE:	C:\Documents and Settings\perotti\Desktop\GIOVANNI_FN4nxiqbKNXcnJB5UcG6GT3n5YoWil.xls
Group:	Cell
TAG:	Data
VALUE:	999-
Figure 10 - Excel error log entry
That made sense: 999 is a numeric value and the minus sign should stay at its left.