iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
spooled file mapping
 the command
some examples
 
 PDF of this tutorial
 
 Download
 
 

 
 
 
Splecell
Command SPLECELL
 

Command SPLECELL is the one to be used for generating an Excel spreadsheet from an SCS spooled file.

1. The process of command SPLECELL
The process of this command is about the same as that of command HSSFCGI/TABLEXLS2:

  • Information from parameters BDYROWS, RCDFMT and BLKCOLS are used to map the list rows and data fields of the spooled file as they were database records
  • Spooled file list records are read as they were database file recordsmf
  • An interim work stream file (the one mentioned in parameter TOXML) is created and filled with:
    • XSLGEN statements for the spreadsheet banner
    • XLSGEN statements for the spreadsheet column headers
    • pseudo CPYTOIMPF statements representing the contents of the list print rows
  • Then the same process as command HSSFCGI/TABLEXLS2 takes place:
  • Command HSSFCGI/XMLFORMAT2 is used to trasform the interim work file TOXML to a regular XLSGEN stream file
  • This regular XLSGEN stream is set as input of HSSFCGI program XLSGEN or XLSXGEN to create the final spreadsheet.

2. Parameters of command SPLECELL
There are two class of parameters in this command:
  • Parameters used to make up the TOXML temporary stream file
  • Parameters used to run HSSFCGI programs XLSGEN / XLSXGEN

A. Parameters used to make up the TOXML temporary stream file
  • To identify the spooled file: FILE, JOB, SPLFNBR.
  • Name of the interim work stream file: TOXLS
  • Name of the EXCEL workbook to be generated: TOXLS
  • To identify the spooled file list rows: BDYROWS.
  • To identify list print row data fields:
    • If a printer file record format available: RCDFMT
    • If no printer file record format available: BLKCOLS, IGNCOLS
  • Not to generate spreadsheet rows containing only blank fields: SKPBLKROWS
  • To identify the interim work stream file: TOXML
  • For an optional banner: BANNERID, BANNERCLR
  • For optional column headers: COLHDG, COLHDGCLR
  • To freeze spreasheet initial data rows and/or columns: FROZEN
  • Whether spreasheet cells should have borders: CELLBORDER
  • Whether and where spreadsheet blank columns should be skipped: DSKIPBLANK
  • Decimal positions for all numeric spreadsheet cells: DECPOS
  • Whether numeric zero values should be displayed: ZERODSP
  • To select the format of negative numbers: NEGFMT
  • Delimiters used in simulating CPYTOPIMPF command: FLDDLM, STRDLM
  • To edit the interim work file TOXML before its conversion to regular XLSGEN format: EDITXML
  • To print the interim work file TOXML after its conversion to regular XLSGEN format: PRINTXML
Note 1. For detail information about these parameters, use the command help key (F1).
Note 2. Banners must be defined with command HSSFCGI/WRKBAN.
They are stored in file HSSFCGIDTA/BANNERS.
For more information about banners, see page /hssfcgi/html/page6.htm.

B. Parameters used to run HSSFCGI programs XLSGEN / XLSXGEN
The following parameters are displayed only by pressing F10=Additional parameters.
  • Workbook and worksheet sizing parameters: SHEETSIZE
  • Worksheet print parameters: PAPERSIZE, PRTORIENT, PRTFIT, PRTSCALE, PRTHDR
  • Run identifier for use of HSSFCGI/ZIPWKB command: RUNID
  • Log some data for debug: LOG
  • Execute EXCEL workbook generation in a separate batch job: SBMJOB
  • Provide EXCEL workbook download when running from a WEB browser: DOWNLOAD.
Note 3. For detail information about these parameters, use the command help key (F1) or refer to command HSSFCGI/TABLEXLS.
                     Generate XLS/X from a SplF (SPLECELL)                   

 Type choices, press Enter.

 Spooled file . . . . . . . . . . FILE                      Name
 Job name . . . . . . . . . . . . JOB         *             Name, *
   User . . . . . . . . . . . . .                           Name
   Number . . . . . . . . . . . .                           000000-999999
 Spooled file number  . . . . . . DSPLNBR     *ONLY         1-999999, *ONLY, *LAST
 Printer file record format . . . RCDFMT      *NONE         Name, *NONE
 To .xml stream file  . . . . . . TOXML       *AUTO                                       
               
 To .xls or .xlsx stream file . . TOXLS                                                   
               
 Page body rows:                                                                
 Page body rows:                  BDYRWS                                              
   Starting row . . . . . . . . .                           1-200
   Ending row . . . . . . . . . .                           1-200
 Sheet name . . . . . . . . . . . SHEETNAME   *FILE                          
 
 Display sheet right to left  . . SETRTL      *NO           *YES, *NO
 Blank columns:                   BLKCOLS      
   Starting column  . . . . . . .                
   Ending column  . . . . . . . .                
                           + for more values   
 Columns to be ignored:           IGNCOLS      
   Starting column  . . . . . . .                
   Ending column  . . . . . . . .                
                           + for more values   
 Skip blank rows  . . . . . . . . SKPBLKROWS  *NO           *YES, *NO
 Banner identifier  . . . . . . . BANNERID    *NONE  
 Banner cell colors:              BANNERCLR
   Font color . . . . . . . . . .             BLACK                  
   Background color . . . . . . .             WHITE                  
 Column headers . . . . . . . . . COLHDG                                                  
                                                                                
                + for more values                                                         
                                                                                
 Columh header colors:            COLHDGCLR
   Font color . . . . . . . . . .             BLACK                  
   Background color . . . . . . .             WHITE                  
 Frozen pane:                     FROZEN                                        
   no. of frozen initial rows . .             0             0-99999
   no. of frozen initial columns              0             0-99999
 Cell colors:                     CELLCLR 
   Font color . . . . . . . . . .             BLACK                     
   Background color . . . . . . .             WHITE                  
 Cell border  . . . . . . . . . . CELLBORDER  *NO           *NO, *YES, *COLOR, *FCOLOR...
 Skip blank cells . . . . . . . . SKIPBLANK   *NO           1-999, *NO, *YES
                + for more values
 Decimal positions  . . . . . . . DECPOS      *AUTO         Number, *AUTO
 Display zero values  . . . . . . ZERODSP     *YES>         *YES, *NO
 Negative numbers format  . . . . NEGFMT      *A            *A, *B, *C
 CPYTOIMPF field delimiter  . . . FLDDLM      '|'
 CPYTOIMPF string delimiter . . . STRDLM      '^'
                 
 Edit the .xml stream file  . . . EDITXML     *NO 
 Print the .xml stream file . . . PRINTXML    *NO 


Sizing parameters: SHEETSIZE Max no. of cells per workbook 2000000 Max no. of sheets per workbook *AUTO Max no. of rows per sheet . . 100000 Print paper-size . . . . . . . . PAPERSIZE 'A4_PAPERSIZE' Print-orientation . . . . . . . PRTORIENT *PORTRAIT Print-fit: PRTFIT Sheet fit-width (nbr. pages) 0 0-99999 Sheet fit-height (nbr. pages) 0 0-99999 Print-scale . . . . . . . . . . PRTSCALE 100 10-400 Print-header: PRTHEADER 'Header' . . . . . . . . . . . *DFT Show also on worksheet . . . . *YES *YES, *NO Generate log . . . . . . . . . . LOG *NO *YES, *NO Generate XLSGEN report . . . . . XLSGENRPT *NO *YES, *NO Run XLSGEN in a submitted job . SBMJOB *NO *YES, *NO Download Excel stmf if CGI job DOWNLOAD *YES *YES, *NO
Command SPLECELL