Skip to main content  
        iSeries home   |   Easy400     |   CGIDEV2     |   MMAIL  
Public-Source
 
Introduction
XLSGEN
Program development
TABLEXLS
TABLEXLS2
TABLEXLS Banners
Right to left
 XLSUPD
Scott's demos
 
Appendixes:
Java products
Alternate COLHDG
 
 PDF of this tutorial
 
 Download
 
 

 
HSSFCGI
The XLSUPD language


The XLSUPD language

The XLSUPD language is a special version of the XLSGEN language that is used to update existing XLS or XLSX spreadsheets, whatever way they may have been created (through XLSGEN or in some other way).

1. The XLSUPD and the XLSXUPD commands

These two commands are exactly the same: XLSUPD is used to update a XLS spreadsheet, while XLSXUPD is used to update a XLSX spreadsheet.

An IFS stream file, containing XLSUPD language control statements for each spreadsheet cell to be updated, must be provided in parameter SHEETIN.

The following types of update can be performed:

  1. Update the value of an existing cell, while preserving its properties (like data type, font, colors, etc.)
  2. Update the value and all the properties of an existing cell: the cell is re-created.
  3. Create a new cell in an existing or in a new spreadsheet row.

                       Update a XLS Excel spreadsheet (XLSUPD)
        
 Type choices, press Enter.

 Workbook to be updated . . . . . WRKBOOKIN                                               
                   
 Updated workbook . . . . . . . . WRKBOOKOUT                                              
                   
 Name of sheet to be updated  . . SHEETIN                                                 
 Input XML stream file  . . . . . INPSTMF                                                 
                   
 Decimal positions  . . . . . . . DECPOS      *AUTO         0-9, *AUTO, *NOC
 Display zero values  . . . . . . ZERODSP     *YES          *YES, *NO
 Format all date cells as . . . . CVTDATE     *NO           *NO, *MDY, *DMY, *YMD...

Figure 1- Command HSSFCGI/XLSUPD

Command parameters

  • Workbook to be updated (WRKBOOKIN) - This is the IFS stream file of the Excel workbook containing the spreadsheet to be updated.
  • Updated workbook (WRKBOOKOUT) - This is the IFS stream file that will contain the updated spreadsheet:
    • Enter *SAME if you want the update be performed on the workbook specified in parameter WRKBOOKIN
    • If you want the original workbook be preserved from changes, in this parameter specify the name of an IFS stream file that will contain the updated copy of the original workbook.
      If already existing, this stream file will be deleted and re-created.
  • Name of sheet to be updated (SHEETIN) - The name of the spreadsheet - in workbook WRKBOOKIN - that will be updated.
    This name is not case sensitive.
  • Input XML stream file (INPSTMF) - The name of the XML-like IFS stream file containing the XLSUPD control statements used to perform the spreadsheet cell updates.
  • The following parameters are used only when re-creating an existing cell or creating a new cell and its data type is NUMBER or DATE:
    • Decimal positions (DECPOS) - This parameter defines the number of decimal positions for numeric cells
      • If you specify a number from 0 to 9, the numeric cells display that number of decimal positions.
        The thousand separator is displayed.
      • If you specify *AUTO, the numeric cells display the number of decimal positions specified in the control statements for that cell in the XLSUPD stream file specified in parameter INPSTMF.
        The thousand separator is displayed.
      • If you specify *NOC, the numeric cells display the number of decimal positions specified in the control statements for that cell in the XLSUPD stream file specified in parameter INPSTMF.
        This is the same as *AUTO, but the thousand separator is not displayed.
    • Display zero values (ZERODSP) - This parameter tells whether numeric cells containing a zero value should display a zero or just nothing. Select:
      • *YES (default value) to have the numeric cells containing a zero value displaying a zero
      • *NO to have the numeric cells containing a zero value displaying nothing. This option is recommended when a large number of numeric cells may contain zero values, because this makes user reading much easier.
    • Format date cells as ... (CVTDATE) - This parameter tells whether date cells should be formatted for a given date format, overriding the format specified for a given date cell in the XLSUPD control statements (parameter STMFIN).
      Leave *NO if no override must take place.
      Available overriding formats are:
      *MDYmm/dd/yy)
      *DMYdd/mm/yy
      *DMYYdd/mm/yyyy
      *YMDyy/mm/dd
      *YYMDyyyy/mm/dd
      *ISOyyyy-mm-dd
      *USAmm/dd/yyyy
      *EURdd.mm.yyyy
      *JISyyyy-mm-dd
      *JULyyyy-ddd


2. An example

The following example may help understanding how these commands (XLSUPD and XLSXUPD) work.
The example shows the case where spreadsheet 10-05 of workbook /hssfcgi/tmp/famacc.xls is updated to workbook /tmp/famacc2.xls.
 
Spreadsheet before update
/hssfcgi/tmp/famacc.xls
Spreadsheet after update
/tmp/famacc2.xls
XLSUPD WRKBOOKIN('/hssfcgi/tmp/famacc.xls')
 WRKBOOKOUT('/tmp/famacc2.xls')
 SHEETIN('2010-05')
 INPSTMF('/hssfcgi/tmp/famacc_upd.txt')
Command used to perform the spreasheet update
<XML>
 <XLSUPD>
  <cell col="H" row="3"><data type="number">1377</data></cell>
  <cell col="8" row="10" border>
   <data type="number" f-color="blue" b-color="gold" bold underline italic>502</data>
  </cell>
  <cell col="A" row="36"><data type="text">New formula -></data></cell>
  <cell col="B" row="36"><data type="formula">B21-B27</data></cell>
  <cell name="A37" hspan="11" border>
   <data type="largetextc" f-color="white" b-color="indigo" bold>END OF THIS SPREADSHEET</data>
  </cell>
 </XLSUPD>
</XML>
XLSUPD language stream file input to command XLSUPD above
INPSTMF('/hssfcgi/tmp/famacc_upd.txt')

Meaning of XLSUPD control statements in stream file /hssfcgi/tmp/famacc_upd.txt:

  • update of the cell H3:
    • <cell col="H" row="3">
      identifies the cell to be updated
    • <data type="number">1377</data>
      identifies the new cell value.
    • Notes
      1. The data type (keyword type="...") must always be specified and must match the data type of the cell to be updated.
      2. In this case, no properties are changed, the cell is updated with a new value and its original properties are retained.
  • update of the cell H10:
    • <cell col="H" row="10">
      identifies the cell to be updated
    • <data type="number" f-color="blue" b-color="gold" bold underline italic>502</data>
      identifies the new properties and the the new value of the cell.
    • Notes
      1. When some cell properties are specified (such as f-color, b-color, bold, underline, italic, ...) the cell is completely re-created, though it retains its original data type.
  • update of the cell A36:
    • This is a case similar to that of cell H3, except that the data type is now text.
      The cell is just updated with a new value.
  • update of the cell B36:
    • This is a case similar to that of cell H3, except that the data type is now formula.
      The cell is just updated with a new value.
  • creation of the cell A37:
    • In the original spreasheet there was no cell A37, therefore it had to br created with the properties and the value specified.

3. The XLSUPD language rules

  1. Prolog and epilog - Prolog <XML><XLSUPD> and Epilog </XLSUPD></XML> are optional.
  2. Tag structure -
    1. Tags are not case sensitive.
      Example: <cell ...> and <CELL ...> have the same meaning.
    2. Keywords and their values are not case sensitive.
      Example: type="number", TYPE="number" and TYPE="NUMBER" are the same.
    3. Keywords within a tag can be in any order.
      Example: col="A" row="37" and row="37" col="A" are the same.
    4. Keyword values must always be within double quotes.
      Example: row="37"
    5. <data>...</data> values are case sensitive.
      Example: <data type="text">apple</data> and <data type="text">APPLE</data> are different values.
  3. Cell identification -
    1. The tag sequence <cell ...><data ... ></data></cell>
      is mandatory. All the above four tags must be there in the appropriate sequence. If that is not done, the related cell update is discarded.
    2. A cell must be always be identified through
      • either keyword name, example:
        name="H10"
      • or keywords col (colunm number/name) and row (row number), examples:
        col="H" row="10" or
        col="8" row="10"
      • The data type of an existing cell must always be correctly mentioned in the <data type="..." > keyword.
        • An existing numeric cell can be referred to by a data type containing the string number
        • An existing character string cell can be referred to by a data type containing the string text or by data type column-header
        • An existing formula cell must be referred to by data type formula
  4. Cell update vs (re-)creation -
    1. Only existing cells with type numeric, string and formula can be updated.
      Existing cells with other types, such as date, picture and so on, are re-created instead of being updated (though in terms of final result this makes no difference if the new properties match the previous ones).
    2. If any cell or data properties specified (example: cell keyword border; data keywords bold underline, italic, wrap, valign, f-color, b-color), the cell is re-created and the new properties replace the existing ones.
NOTE- For the available keywords and their meanings, please refer to the XLSGEN language.

4. Facilities for CGIDEV2-based programs

If you are familiar ewith CGIDEV2-based programming, you may easily create a XLSUPD language control stream file by using procedures UpdHtmlVar(), WrtSection() and WrtHtmlToStmf().
The only thing you may be missing is an external template stream file to be loaded via procedure GetHtmlIfs() or procedure GetHtmlIfsMult().
In such a case, you may use as template the stream file /hssfcgi/html/XLSUPD_template.txt, which looks as follow:

<as400>top
<XML>
 <XLSUPD>
<as400>cell
  <cell row="/%row%/" col="/%col%/" /%cellkeywords%/>
   <data type="/%datatype%/" /%datakeywords%/>
     /%data%/
   </data>
  </cell>
<as400>bot
 </XLSUPD>
</XML>