Here is a little known technique that yields very powerful and practical results. On this page I illustrate how you can use CGIDEV2 and XML to automate the creation of Excel Spreadsheets.
Here is an static example. This file was created directly on the
IFS as an XML/Excel Worksheet using RPG and the CGIDEV2 toolkit.
It was generated from a green screen menu option.
Here is another example which illustrates how you can use this technique interactively.
Writing to Excel .xls files using CGIDEV2
It is commonly known that Microsoft Excel (2003) can read from, and write to the popular Hypertext Markup Language (HTML) and Extensible Markup Language (XML). What is not well know, is that if you save an HTML file with the .xls extension, Excel will read in the HTML data and do its best to convert the contents to an Excel spreadsheet complete with cell width and most formatting. Similarly if you create an XML file with an .xls extension, using appropriate Excel XML tags, the resulting file should read into Excel as a regular Excel file.
Although XML files are typically much larger than HTML files and harder to work with, Microsoft has announced that the next version of Microsoft Office (Office 12) will start using XML as the default format for saving Excel and other Office documents making it a better bet for future compatibility. XML also permits greater precision in cell formatting and additional functionality such as multiple worksheets.
To get a quick understanding of the XML format, try saving your favorite Excel spreadsheet in XML Spreadsheet format. Next open the resulting document in a text editor. To use this generated file as a CGIDEV2 template, all you have to do is add section headers and substitution variables.
Some Selected XML/Excel Syntax
<?mso-application progid="Excel.Sheet"?>
The mso-application tag tells Windows which application should open this file.
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
Spreadsheet can only have one Workbook tag and it sets all the name spaces for the XML document.
<Styles>...</Styles>
The Styles tag and sub-tags hold the formatting information for the spreadsheet.
This is similar to a Cascading Style Sheet in CSS.
<Style ss:ID="s23">
<Font ss:Size="10" ss:Color="#0000CC" x:Family="Swiss" ss:Bold="1"/>
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
</Style>
Style Sub-Tag. Multiple <Style> tags are permitted within <Styles> tag.
This tag is identified at Style "s23".
<Worksheet ss:Name="Sheet1">
There can be multiple Worksheet tags. Name holds the name of each sheet.
Name has to be unique and some special characters cannot be used.
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="12"
x:FullColumns="1" x:FullRows="1">
The table tag starts the table (worksheet). ExpandedColumnCount and ExpandedRowCount define number of columns and rows permitted. Be very careful to ensure ExpandedColumnCount and ExpandedRowCount are correctly set. This is a common reason that XML file cannot be opened in Excel.
<Column ss:Width="93"/>
Set column width.
<Row>
The row tag starts the row.
< Cell ss:Formula="=R[-3]C+R[-2]C+R[-1]C"></Cell>
Specifies the formula to be used to produce the content for the cell. The syntax is the basic RC syntax that uses relative locations. R means row and C means cell. A Cell with a Formula does not need a Data tag since the data will be generated from the formula.
<Cell ss:StyleID="s23"><Data ss:Type="String">Salesperson</Data></Cell>
The Cell tag starts each cell. The StyleID attribute associates a style with this field. The Data tag holds the actual data for the cell. Data type here is identified as String.
<Cell ss:MergeAcross="4" ss:StyleID="s23"/>
Merge text across 4 Cells.
<Data ss:Type="String">
<Data ss:Type="Number">
Specifies the data type of the content of the cell.
<TabColorIndex>47</TabColorIndex>
Set the tab colour for Excel tabs.
Some Hints:
1. Referencing cells in formulas are not like Excel. Take a good look at the RC notation used in XML.
2. Make sure data types match the contents of the cell.
3. Some special characters like square brackets [ ] have to be substituted with ASCII value.
4. Consider sending generated XML files to a shared network drive with FTP or directly to your users with a product like Gumbo SpoolMail or mmail.
5. For the sake of your network administrator, try to limit the size of files created. XML files can be several times larger than a .csv file or .xml file. This techniques works well for creating summary documents, however if you try to create a document with all inventory in all stores, you will likely quickly run into network, memory or email limitations.