• a top section
• a row section (that you will be using to generate table rows in the spreadsheet), and
• a bottom section.

Working with the XLSMDL utility

The following appears:

Figure 1 - Getting started with XLSMDL

Press the button "new" to start a new XML script. You are requested to enter a title for it:

Figure 2 - Title for a new script

Next, you are requested to define the fields (in other words, the columns) of the table making up your spreadsheet:

Figure 3 - Defining a field (column)- Part 1 of 2

• sequence establishes the order of the column (first, second, etc. column)
• header will become the column header
• name will be the output variable (for instance, in the example above the output variable will be /%cstcode%/ .
• data type. Four data type are supported:

  data type	supported formats	length	decimal positions
  character	---	500 max	---
  number	±nnnnnnnnnnn.nnnnnn	any	any
  date	yyyy-mm-dd	10	---
  	dd/mm/yyyy	10
  	mm/dd/yyyy	10
  	dd/mm/yy	8
  	mm/dd/yy	8
  time	hh.mm.ss	8	---
  	hh.mm	5

The following screen allows to complete a field definition:

Figure 4 - Defining a field (column)- Part 2 of 2

Of course, the information requested changes according to the data type selected.

While colums are being defined, in the right part of the screen a list of the existing columns is displayed:

Figure 5 - Columns defined so far

You may remove or update columns at any time.

When all the columns have ben defined, press the "generate spreadsheet model button.
The following is then displayed:

Figure 6 - Ready to generate the XML stream file

Before pressing the "generate" button, make sure about

• the section name delimiter: it must be the same as that of other HTML scripts that you may need to getHtmlIfsMult in your CGI program. We strongly suggest <as400> as such delimiter.
• the three section names of the XML script: you may want them to be similar (but not identical!) to the ones used for providing HTML response;
• maximum number of rows: this is a critical parameter for Excel. It must never be lower than the number of rows generated for the spreadsheet.

As soon as you press the "generate" button, the XML script stream file is generated in the IFS directory
/xlscgi/xlsmodels
and you receive the following screen:

Figure 7 - XML script stmf generated

Please note the following:

• You may display the generated XML script
• You may get a copy of this XML script in an IFS directory of yours. We recommend that you save a copy of it in the same directory that your CGI would use to getHtmlIfsMult from, so that later on you do not have to know where that piece went.

As soon as you click the "yes" or the "no" radio button, you go back to the initial screen you started from. Now your XML script (model) is listed.

Figure 8 - Maintaining XML models

From here you can perform several tasks:

• Display the script. For instance, you may not recall the section names.
• Display the field (column) list, in case you do not remember their detail structures.
• Test the script. This is very important, as it would generate a real spreadsheet and send it to Excel, so that you may be confident that also your CGI has good chances to work with Excel.
• Rework. This would allow to change column definitions, reorder columns, generate new section names, and even a stream file witha different name.
• Remove. Sometime you would like to get rid of scripts you no longer need.

Note
If in your HTTP directives installation you have chosen to maintain access protection to program XLSMDL, then when you try to access it you are asked to enter your identification (user name and password). In such a case, your script files are given an internal qualifier with your user name and , as a consequence, only the ones with your qualifier are listed in the table of Figure 8. This is good, as each separate user( name) would just have access to her/his own scripts.
The script titled "Test on all types of fields" is made visible to all users. It can be used to test the correctnes of Excel spreadsheet generation. It cannot be reworked nor removed.

How to write the CGI program

This topic is in the next page.