• 1. The XLSUPD language
• 2. The XLSUPD and the XLSXUPD commands
• 3. An example
• 4. The XLSUPD language rules
• 5. Facilities and restrictions
• 6. Refreshing workbook formulas
• 7. Facilities for CGIDEV2-based programs

1. The XLSUPD language

The XLSUPD language is a special version of the XLSGEN language that may be used to update cells of existing XLS or XLSX spreadsheets, whatever way they may have been created (through XLSGEN or in some other way).
Existing cells can be updated and new cell added to a spreadsheet.

2. The XLSUPD command

Command XLSUPD can update both XLS and XLSX Excel spreadsheets.

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 XLS / XLSX 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...

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:

    *MDY	mm/dd/yy)
    *DMY	dd/mm/yy
    *DMYY	dd/mm/yyyy
    *YMD	yy/mm/dd
    *YYMD	yyyy/mm/dd
    *ISO	yyyy-mm-dd
    *USA	mm/dd/yyyy
    *EUR	dd.mm.yyyy
    *JIS	yyyy-mm-dd
    *JUL	yyyy-ddd

3. 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" ftype="number" dec-pos="0">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')
Figure 2

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.

4. 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 features -
   1. Existing cells with data-type ...TEXT..., COLUMN_HEADER, NUMBER, PCENT, FORMULA, DATE and TIME can be updated.
      Existing cells with data-type PICTURE cannot be updated.
   2. If cell or data properties specified (example: cell keyword border; data keywords bold underline, italic, wrap, valign, f-color, b-color), the existing cell to be updated receives these new properties.
5. Column widths
   When a cell is either updated or created, the related column width may be shrinked to the width of that cell. To keep a column at a desired width, it is recommended to specify the column width (in pixel) in the <cell ...> statement.
   Example: <cell name="B5" cell-width="80"><data type="formula" ftype="pcent" dec-pos="2">A1/B1</cell>

5. Facilities and restrictions

1. Update or create?
   If the cell, indicated by cellname="...", or by col="..." and row="..." already exists, it is updated, otherwise it is created.
2. Update restrictions
   The XLSUPD statement intended to update an existing cell, must have the same data type ( as the cell to be updated.
   
3. Formulas
   • The formula value may reference cells in the same spreadsheet and/or cells of different spreadsheets existing in the same Excel workbook.
   • The formula data type must be consistent with the result of the formula.

6. Refreshing workbook formulas

When you work with an Excel workbook on a PC and you update a cell directly or indirectly referenced by some formulas, all referencing formulas are re-evaluated, thus displaying updated values. This automated formulas update feature is a default option in File->Options->Formulas of PC Excel menu.

On the other side, if you import to IBMi an Excel workbook and use command HSSFCGI/XLSUPD to update the values of some cells, no automated formulas re-evaluation feature is available. Therefore, when you download the imported workbook back to PC, while the updated cells display their new values, the values of the non-updated formula cells are not re-evaluated and you would be dispalyed thir previous figures. This is why, to force formula re-evaluation, on PC you would have to press CTRL+SHIFT+ALTGR+F9.

In order to avoid asking the PC client user to press CTRL+SHIFT+ALTGR+F9 every time he downloads an IBMi imported and updated Excel workbook, on the IBMi you may use command HSSFCGI/RFSWBOOK to force formulas re-evaluation before making the workbook available for download.

                      Refresh workbook formulas (RFSWBOOK)
 Type choices, press Enter.                                                    
 Workbook stream file . . . . . .  WORKBOOK                                  
              
 Spreadsheet name:                 SHEETNAME  _                                 
                                                *AUTO                        
              
                          + for more values   _ 
                

• Parameter SHEETNAME
  You may specify up to 50 names of spreadsheets existing in the workbook that are to be refreshed.
  Instead of specifying spreadsheet names, you may leave the default single name *AUTO. In this way all the spreadsheets existing in the workbook are refreshed. However, SHEETNAME(*AUTO) requires library XLPARSE2 in order to retrieve the contents of the workbook.

7. 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>