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

 
 
 
Splecell
Converting SCS Spooled Files to Excel
  Requires HSSFCGI by Giovanni B. Perotti (Italy)
If you are already sending to your clients some PDFs generated from spooled files, how would you like to perform one more step and send some of your spooled files also as EXCEL spreadsheet?

That doesn't take much and may highly improve your customers satisfaction.
If you already have our HSSFCGI utility, just install this add-on utility.

1-About it

Our HSSFCGI utility allows to generate Excel spreadsheets from single record format database files without writing any code, just executing a HSSFCGI command.
This process starts from the fact that IBM i database are just tables, where the field mapping of the table rows (records) can be immediately known through system command (DSPFFD).
Printouts are instead some more complex objects, and the question here is whether it would be possible to read only its list rows in order to generate an Excel spreadsheet.

A printout is generally created through a number of different record formats, such as headers, sub-headers, list rows, footers and so on.

  1. If the printout is generated through printer files, one could run system commands DSPFD TYPE(*RCDFMT) and DSPFFD to obtain the names of the record formats and their field mappings.
    The remaining technical problem would just be to identify the list rows generated from a given record format.
  2. If no information is available about the record formats used to generate the printout, one can still make it by
    1. specifying manually the field map a list row, or
    2. specifying a user-defined printer file and record format name
  3. The next problem would that to establish the horizontal section of a page containing the list rows.
    Usually the first page is not the right one, as it may contain a number of headers and column descriptions. The second page of the printout usually provides this type of information. Once you know the first and the last page row number of this "body" section, your investigation is over.

    Once the information about the "list body" of a page is available (vertical columns and orizontal boundaries), a program of this utility can read the spooled file, process the list rows and generate an Excel spreadsheet.

    As an example, let us take the case of a spooled file output from command DSPLIB LIB(HSSFCGI) OUTPUT(*PRINT),
    displayed by command DSPSPLF FILE(QPDSPLIB) SPLNBR(*LAST).

    • Figure 1 displays the example of its DSPSPLF.
      Figure 1- Displaying a DSPLIB printout
    • As no record format list can be found from commands DSPFD FILE(QPDSPLIB), in order to run SPLECELL over this QPDSPLIB spooled file one should either
      1. manually map the blank columns in the page body rows as
        (1 1) (14 15) (24 25) (40 41) (52 53) (103 133)
        see the source of the example program in SPLECELL/QCLSRC member SAMPLEB1,
        OR
      2. specify the printer file name and the record format name of a user-defined printer file created from DDS source member, such as the following (SPLECELL/WDDSSRC member SAMPLEC1):
             A          R DSPLIBROW                 SPACEB(1)                 
             A            DSPLIBOBJ     10A        4TEXT('object name')       
             A            DSPLIBTYPE     8A       16TEXT('object type')       
             A            DSPLIBATTR    10A       26TEXT('object attribute')  
             A            DSPLIBSIZE    14S 0     38TEXT('object size')       
             A            DSPLIBDESC    50A       54TEXT('object description')
      see the source of the example program in SPLECELL/QCLSRC member SAMPLEC1.
    • Figure 2 displays the Excel spreadsheet that a command of this utility would generate. You may click the picture there to download that spreadsheet the browser.
      Figure 2- Excel spreadsheet generated by command SPLECELL/SPLECELL

    2-Prerequisites

    The main prerequisite is our HSSFCGI utility.
    The following are the HSSFCGI prerequisites:
    • Library QSYSINC, product 57xx-SS1, opt. 13
    • Library QSHELL, product 57xx-SS1, opt. 30
    • Library QPASE, product 57xx-SS1, opt. 33
    • Library QHTTPSVR, product 57xx-DG1
    • Compiler ILE RPG IV, product 57xxWDS, opt. 31
    • If you want to create just XLS (Office 2003) Excel spreadsheets, you need
      • Microsoft Office 2003 or subsequent
      • OS/400 release V5R3 or subsequent
      • Library QJAVA, product 57xx-JV1, opt. 6 (Java 1.4) or opt. 7 (Java 1.5) or subsequent.
    • If you want to create also XLSX (Office 2007) Excel spreadsheets, you need
      • Microsoft Office 2007 or subsequent
      • OS/400 release V5R4 or subsequent
      • Library QJAVA, product 57xx-JV1, opt. 7 (Java 1.5) or subsequent.

    3-Installation

    • Download file SPLECELL.zip from the Easy400 download page and unzip it.
    • Follow the SPLECELL.txt instructions to upload and to restore library SPLECELL.
    • On the IBMi run the following procedure:
      STRREXPRC SRCMBR(INSTALL) SRCFILE(SPLECELL/QREXSRC) .
      It does the following:
      • checks if library HSSFCGI is installed
      • creates objects in library SPLECELL
      • restores IFS directory /hssfcgi, which includes this manual both in HTML and in PDF format.

    4-Maintenance

    This utility is maintained on request, both for applying fixes and for adding enhancements
    . Send your requests to the author. To know what is new, just take a look at its Change Log.