XML Extensions - Microsoft Office and OpenOffice Documents

Microsoft Office and OpenOffice documents have closely related XML document formats. While these XML documents can become complex, using XML is probably the easiest mechanism to create and/or consume documents for these products. (See http://msdn.microsoft.com/en-us/library/aa338205%28office.12%29.aspx for additional information about Microsoft Office Open XML Formats, and http://xml.openoffice.org/#ODF for additional information about OpenOffice OASIS/ISO standard document formats.)

A simple way for creating Microsoft Office XML documents is creating a document using sample placeholder data. After examining the XML document, add XSLT processing instructions to the XML document to create an XSLT stylesheet that places values supplied by your extend application program during an XML EXPORT FILE into the overall structure of the sample document.

The sample XMLSpreadsheet is an example of creating Office documents. Copy the files from XMLSpreadsheet into a directory that is served by BIS for extend. For the purpose of the following description, this directory is assumed to be C:\inetpub\wwwroot\acuxbis\samples\XMLSpreadsheet.

A spreadsheet was created using Microsoft Excel 2007 and four placeholder data values. A formula was also included (summing the four values) to illustrate XML document capabilities.. The spreadsheet was saved using Save As > Other Formats and selecting ‘XML Spreadsheet 2003’ as the Save as type. The result is Book1.xml, located in the bin directory. Book1.xml is copied to numbers_to_xmlss.xsl. Using a text editor, XSLT processing instructions were added to numbers_to_xmlss.xsl to turn it into an XSLT stylesheet. You can see the extent of the changes in the difference.txt file.

A sample BIS extend service program was derived from tutorial3.cbl (which was functionally similar to the behavior desired for the spreadsheet applet) to produce getss.cbl. Similarly, tutorial3.srf was used to create GetXMLSS.srf, which was changed to call getss.acu in the {{StartService}} tag and add a {{ContentType}} tag. A significant amount of debug output was included in the COBOL program, both as tracing in the trace file (DISPLAY … UPON SYSERR) and as intermediate XML files which illustrate the changes created by the XSLT being used. The applet looks for query parameters named N1, N2, N3 and N4, and substitutes the numeric values associated with those query parameters for the default values of 1, 2, 3 and 4. You can view the spreadsheet applet in two ways, by fetching the spreadsheet directly using Microsoft Excel or indirectly by using a browser.

To view directly from Microsoft Excel, Start Microsoft Excel 2007 and select Open. In the Filename field, paste http://localhost/acuxbis/samples/XMLSpreadsheet/GetXMLSS.srf?N1=5&N2=10&N3=15&N4=20
Note: If Microsoft Excel warns that the srf type is a different format, click Yes to allow the spreadsheet to load. You can eliminate this warning on a per-machine basis by changing your registry to add the following registry value: [HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security] "ExtensionHardening"=dword:00000000 Likewise, you can eliminate the warning with a group policy. However, be aware that you will eliminate a warning that may be useful at times.

To view directly from a browser, use a compatible browser, such as Firefox or Opera, and enter the URL used above when opening the applet directly from Microsoft Excel. The browser recognizes the content type and suggests an appropriate application for opening the response data. Change the values of the query parameters N1, N2, N3 and N4 to see that the data changes in the returned spreadsheet, and also to see the formula recalculation by Microsoft Excel (not the extend service program).