• 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

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
        /xlscgi/html/goxls.htm
        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

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
  Example:

  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
       inpNumber=fieldname;
       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	hh:mm:ss	05:27:52
  hh.mm	hh:mm	05:27