Problem:
How can I import Excel data using XML?
Resolution:
Modern versions of Microsoft Excel have the ability to save a spreadsheet in the form of an XML document. This is accomplished by using the 'XML Spreadsheet' option of the 'Save as type:'. The resulting XML document is quite rich in the detail saved for the spreadsheet, including formulae used to produce cells, as well as cell values. You may refer to the Microsoft web site for more information about this particular XML specification, named SpreadsheetML. There are also several books devoted to using XML in the Office suite.
Using XML Extensions, one can import virtually any detail of the resulting spreadsheet. The attached example demonstrates the most common requirement: importing the actual data values from the sheet. The example XSLT (XML translating stylesheet) assumes that the first row contains column labels, and determines which column index to use for the various data values. It then processes the remaining rows, extracting the data of interest.
The simple COBOL program demonstrates the data import from the XML spreadsheet.
(The XSLT was developed using Stylus Studio, one of several XSLT editors available in the commercial market. Other XSLT editors may be available in the open source community.)
