According to some writers (see for instance this article), the job of generating Excel spreadsheets from a CGI program based on the CGIDEV2 service program, is rather easy:

1. Use Excel to create a skeleton spreadsheet
2. Save it as an .xml file
3. Change the XML skeleton spreadsheet by inserting CGIDEV2 section names and output variables
4. Copy this XML skeleton to an IFS directory
5. Write a CGIDEV2-based CGI program that
   • loads the XML skeleton spreadsheet via subprocedure GetHtmlIfs() or GetHtmlIfsMult()
   • fills in the output variables and writes the appropriate XML sections
   • writes the output buffer to a stream file with extension .XLS
   and you are done.

After trying this approach several times with no success, I came to some conclusions:

1. The idea of using CGIDEV2 to generate Excel spreadsheets is really attractive and deserves success
2. A small problem is with delivering the spreadsheet file to the end user. I believe that the end user should immediately receive the spreadsheet through the browser, and that the spreadsheet IFS stream file should be temporary. This requires a small function be made available.
3. A big problem is generated from the fact that some output variable in the spreadsheet row cells may not comply with the format expected by Excel. In such cases Excel provides poor diagnostics. Developers may try to overcome the problem for hours or days without any hope.
   This requires some utility for generating the output variables in the formats expected by Excel.

1-About it

1. Allows you to define the XML skeleton spreadsheet you would like to have without using Excel, to define your output variables, to define your CGIDEV2 sections, and to save the skeleton into an IFS directory of your choice.
2. Provides ILE-RPG subprocedures to provide your output variables with the format expected by Excel
3. Provides an automated procedure to convert the output buffer to a temporary .xls stream file and to send that file to the browser.

Run this example of a CGI able to provide an Execel spreadsheet at the touch of a button.

2-Prerequisites

• Microsoft Office 2003 or subsequent (ability to accept XML files in input)
• OS/400 release V5R2 or subsequent
• Compiler ILE RPG IV, product 5722WDS, opt. 31
• Library CGIDEV2, the great enabler for ILE-RPG CGI programming, free from our site
• Experience with developing CGIDEV2-based CGI programs

3-Installation

• Download file xlscgi.zip from the Easy400 download page and unzip it.
• Follow the Readme.txt instructions to upload and to restore library XLSCGI.
• On the iSeries run the following procedure:
  STRREXPRC SRCMBR(INSTALL) SRCFILE(XLSCGI/QREXSRC)
  It does the following:
  • creates service program XLSCGI/XLSCGI
  • creates some sample programs
  • creates the CGI utility program XLSMDL
  • restores IFS directory /xlscgi
  • displays the the directives of the XLSCGI HTTP instance that you should enable as specified in the initial comments.
    NOTES
    1. To display these directives later on, use command XLSCGI/APACHE .
    2. These directives include a group to protect access to the utility program XLSMDL. The protection is based on system user profiles.
       You can use a validation list instead, or drop this protection by deleting the group.

4-The XLSMDL utility

That is documented in the next page.