Command TABLEXLS2 is the Junior version of command XLSTABLE.
It is 1.3 times faster than TABLEXLS, does not require the ILE-RPG compiler to run, but has less features.

• 1-Highlights
• 2-About the XLSGEN2 language
• 3-Generating the XLSGEN2 script from your ILE-RPG program
• 4-Commands XLSGEN2 and XLSXGEN2
• 5-Command WEBXLSGEN2 for CGI programs
• 6-XLSTABLE2 for the WEB

1-Highlights

• While command TABLEXLS generates - for each database file to be read - an ad hoc ILE-RPG program, command TABLEXLS2 does not need that.
  Therefore, to run command TABLEXLS2 you do not need an ILE-RPG compiler. This may help if you want to run it on a production box hosting no compilers.
  TABLEXLS2 generates an intermediate XLSGEN2 script made of some XML-like XLSGEN statements and data exported from a CPYTOIMPF command. The generation of this script is quicker than the XLSGEN script generated from command TABLEXLS, and this is the performance advantage of XLSTABLE2.
• The script is then read by program XLSGEN2, similar to program XLSGEN, which creates an XLS or XLSX Excel workbook.
• The following parameters, available in command TABLEXLS, are missing in command TABLEXLS2:
  • RCDFMTNBR (only single formatted files are supported in parameter FROMFILE)
  • INCLUDEXML (Include also an existing XML stream file)
  • NULLDATE (null date display option)
  • NULLTIME (null time display option)
  • SKPFLDS (Skip fields by field test)
  • IGNFLDS (Fields to be omitted)
  • LINKFLDS (Hyperlink fields)
  • PICTURES, VSPAN, WIDTH (Ability to display pictures)
  • Restriction -A maximum of 256 columns (cells) per row is supported.

2-About the XLSGEN2 language

A XLSGEN2 script begins as a XLSGEN script, defining the workbook, the worksheet and the first row of cells containing the column headers.
However, instead of defining the row cells (containing the record field data) through the XLSGEN <row> <cell> and <data> tags, XLSGEN2 appends to the script stream file the output from a CPYTOIMPF command over the database file member. The result is a sequence of lines, one for each database record, where data fields are represented by semicolumn-separated strings.
Character strings are enclosed in double quotes (example: ;"...text...";), while numbers are not (example: ;1827;).
In XLSGEN2, this bunch of lines representing the database member records are preceded by the tag <Start_of_rows> and followed by the tag <End_of_rows>.

Please take a look at this XLSGEN2 script.
It was generated by the following command:

TABLEXLS2 FROMFILE(HSSFCGI/UTILITIES) TOXML('/hssfcgi/tmp/uti2.txt')
          TOXLS('/hssfcgi/tmp/uti2.xls') SHEETNAME(DT150903)
          PRTORIENT(*LANDSCAPE) PRTSCALE(75)
          HDGCOLOR(LAVENDER) XLSGENRPT(*NO)

After generating the XLSGEN2 script stream file, TABLEXLS2 inputs that XLSGEN2 stream file to command XLSGEN2, in order to generate the Excel workbook.
In the above example, the XLSGEN2 command executed from TABLEXLS2 is:

XLSGEN2 INPSTMF('/hssfcgi/tmp/uti2.txt')
             OUTSTMF('/hssfcgi/tmp/uti2.xls') 
             FROZEN(1 0) HDGCOLOR(LAVENDER) XLSGENRPT(*NO)

3-Generating the XLSGEN2 script from your ILE-RPG program

• If you are familiar with CGIDEV2-based ILE-RPG programs, you may still use the template /hssfcgi/html/XLSGEN_template.txt to generate the initial part of the XLSGEN2 script (defining the workbook, the worksheet and the first row of cells containing the column headers) in the client output buffer and send it to your script stream file using CGIDEV2 procedure WrtHtmlToStmf().
  
• Otherwise, if you are familiar with opening, closing and writing to stream files, you can do write that initial part of the script stream file using standard open(), write() and close() procedures.
  If you need some example, please refer to member TABLEXLS2 in HSSFCGI/QRPGLESRC.
• Next, you have to
  • open the script stream file for append
  • write the tag <Start_of_rows>
  • use command CPYTOIMPF (referring to a given database file member) to add its output to the script stream file
  • write the tag <End_of_rows>
  • Last, you must write the following final tags to the script stream file:
    

      </worksheet>
     </workbook>
    </XML>

4-Commands XLSGEN2 and XLSXGEN2

Command XLSGEN2 generates a XLS Excel workbook from a XLSGEN2 script stream file.
Command XLSXGEN2 generates a XLSX Excel workbook from a XLSGEN2 script stream file.
These commands are similar to commands XLSGEN and XLSXGEN (see this page), except that they do no support parameter CVTDATE.

5-Command WEBXLSGEN2 for CGI programs

In writing you CGI program you may also take advantage of command HSSFCGI/WEBXLSGEN2. This command is very similar to commands XLSGEN2 and XLSXGEN2, but provides further functions:

1. Creates the XLS or XLSX stream file from the XLSGEN2 script stream file (as commmands XLSGEN2 or XLSXGEN2 would do). Then, if running in a HTTP instance, ...
2. Sends the XLS or XLSX stream file to the browser
3. Submits a job to delete the XLS/XLSX stream file after 5 minutes.
   (Note that it is a program responsibility to decide to delete the XLSGEN2 script stream file.)

6-XLSTABLE2 for the WEB

Command TABLEXLS2 is for a 5250 black-green screen or for execution within a program.
Then, how to run this command from a WEB browser?
If you have installed the Apache HTTP directives in stream file /hssfcgi/apache/http_directives.txt, just try one of the following URL's:

Figure 7 - Command TABLEXLS2 from a WEB browser

1. Press the button "create spreasheet" without checking the check-box "run in a separate batch job".
   In this way, the TABLEXLS2 command is executed from the HTTP server job.
   As soon as the spreadsheet has been created, it is sent to the browser for download then it is deleted.
2. Press the button "create spreasheet" after checking the check-box "run in a separate batch job".
   In this way the HTTP server job submits the execution of the TABLEXLS2 to a batch job.
   Buttons are made available to inquiry about the status of the submitted job.
   Once the submitted job completes, a download button shows up so that the spreadsheet can be sent for download to the browser and then deleted.

   Notes.

   1. The WEB page includes a yellow "demo" button in the bottom left corner. This can be used to get some familiarity with the tool.
      You may try now, just use the "demo" button. Press this link to go.
   2. If, after pressing the "submit" button, command TABLEXLS2 - launched from the CGI program - fails, the command in error is displayed at the bottom of the page. You are suggested to copy, paste and run it from a green screen session in order to receive some diagnostic messages.