Contrary to what you might think, not every document is initially written in XML. Actually, most documents are prepared with some other tool and later converted to XML. Many documents originate from relational databases such as DB2, or from Microsoft Office applications such as Word or Excel.

I have found that many businesses use Excel to edit and prepare data. It's simple to use, widely available, and its tabular format is well-suited to all kinds of information, like lists of products, lists of names, financial data, or statistical data. Excel spreadsheets are often e-mailed to users who are asked to fill in the blanks.

Retrieve your data

One of the issues that arises when collecting and preparing data in a proprietary application is recovering the data. Fortunately, this is not an issue with Excel. Although the specifications for the Excel file format are not publicly available, a number of options can extract XML information from a spreadsheet. This tip reviews your options and highlights each solution's pros and cons.

XMLSS

The latest versions of Excel -- Excel 2002 and Excel XP -- can export a spreadsheet to XML. While you don't have any say over the choice of tags, you do get a valid XML document that you can post-process with any XML tool, including an XSLT stylesheet. The format is known as XMLSS.

This is the simplest solution if:

1. Your users have the latest version of Excel, and
2. You're processing XML data on a workstation.

The first condition is obvious, the second one might need more explanation.

Suppose you set up a server to collect and process statistical data. Your users prepare the data with Excel and later upload their spreadsheets to the server for further processing. The first step is to convert the spreadsheet to XML. One solution is to open the spreadsheet in Excel and export it in the XMLSS format. I have seen companies implement such a solution and, while it can run, it's important to keep in mind that Excel was designed for workstations, not servers.

Although functional, this solution might not be as stable as you would like. Among other issues, the spreadsheet conversion may become a bottleneck because it may be difficult to multithread the conversion. In the worst case, every request is queued into a single copy of Excel. It also limits your hosting options: Excel is only available on Windows and MacOS.

CSV files

The first alternative is to work not with actual spreadsheet files but with comma-separated value (CSV) files instead. CSV is a popular file format for exchanging spreadsheets. Any worthy spreadsheet can export to and import from CSV files.

Furthermore, many third-party products that work with spreadsheets also support CSV. For example, most accounting packages can work with CSV files. Although CSV files are not XML, it is simple to convert them into XML files with tools such as XI (see Resources). The TopXML site also includes algorithms for a pure XSLT solution (see Resources).

Using CSV files is much more attractive for servers. For one thing, you are no longer limited to certain platforms. Instead, you are broadening the options for your users (who can now work with Lotus 1-2-3 and other spreadsheets as well as many third-party tools that also recognize the CSV format).

About the only downside with this solution is that your users must take the extra step of saving their data in CSV. In practice I have found that this is seldom a problem, but your mileage may vary.

Plain conversion

Last but not least, you can turn to special libraries to read Excel documents. The Excel file format is not officially documented, but third-parties have been reverse-engineering it for some time. They have also produced libraries to decode Excel files (see Resources). Here's a list of some of the available APIs:

• Java Excel API is one of my favourite solutions because it is portable, does a fair job of reading XML documents, and includes a conversion to XML.
• XML::Excel is a Perl API for converting Excel documents into XML.
• OpenOffice includes C++ libraries for reading Excel documents. You can use these libraries as a basis for converting from Excel to XML.
• Apache POI supports reading Excel files in Java code. Again, you can use it as a basis for an Excel-to-XML converter.

This list is a representative sample only. Other tools are available for different platforms.

Converting Excel files has three benefits:

• The files will run on any platform.
• The libraries are easy to integrate with a server.
• Users don't need to remember to export the data.

The main danger with this solution is that no library is as good as the real thing and you will find that some spreadsheets do not convert well. You will need to test the best library for your project.

Back to top

Get going

Excel is a popular tool for preparing all kinds of data that you may want to process in XML. Fortunately, as this tip demonstrates, you have many options for retrieving data in XML. The best option will depend on the specifics of your project.

No matter how you convert the spreadsheet, you will find that the resulting XML document is never quite what you were expecting. Maybe it includes cells that you don't need or maybe the XML vocabulary is not quite what you were expecting. Don't worry -- in most cases, it is simple to prepare an XSLT stylesheet to filter out unwanted data or rename tags.

Resources

• Participate in the discussion forum for Benoit Marchal's Working XML column.
  
  
• For a quick training guide to XSLT, read Don Day's "Hands-on XSL" (developerWorks, March 2000). It introduces XSLT, the best tool for post-processing documents after a format conversion.
  
  
• Read "Importing text as XML with XI" (developerWorks, April 2002) by Benoit Marchal, which introduces a tool to convert CSV files into XML.
  
  
• Download the Java Excel API, a great tool for converting Excel files to XML.
  
  
• Try the XML::Excel Perl module, a simple tool for Excel processing with XML.
  
  
• Check out OpenOffice, which includes libraries for parsing Excel files.
  
  
• Apache's Jakarta POI is a Java API for Excel files. Use this as a basis for your own Excel-to-XML converter. Cocoon uses Jakarta POI to convert from XML to Excel.
  
  
• Find numerous useful XSLT algorithms, including methods for processing non-XML documents, at TopXML's XSLT Stylesheets page.
  
  
• Find more XML resources on the developerWorks XML zone. For a complete list of XML tips to date, check out the tips summary page.
  
  
• Find out how you can become an IBM Certified Developer in XML and related technologies.
  
  

About the author

Benoit Marchal is a Belgian consultant. He is the author of XML by Example, Second Edition and other XML books. Benoit is available to help you with XML projects. You can contact him at bmarchal@pineapplesoft.com or through his personal site at marchal.com.

Rate this article

Comments

Back to top

Table of contents

• Retrieve your data
• Get going
• Resources
• About the author
• Comments