Reading Excel generated XML with ActionScript 3

Excel files can be saved as XMLs. In ActionScript 2, reading Excel generated XML (EGX) files is really tedious and unreliable because EGX files’ data hierarchy is not fixed and they have lots of formatting/styling information. To ensure not missing any data, it requires traversing every single node. It’s very inefficient. With AS3, its XPath like approach allows to find nodes located anywhere in the document by using the “..” notation which is “//” in XPath (ActionScript 3 changed all the “/” into “.” which makes more sense for object oriented programmers”. But that’s not all for reading EGX files.

When I first started using ActionScript 3’s XML class, I tried to locate data in a EGX file using excelXml..Cell.Data. Surprisingly, nothing showed up. I searched online and posted in forums but no answers were found. My conclusion was that AS3 can’t handle messy XML files.

Recently I was working on a project using web services then the magical “.” notation in AS3 failed me again. After a bit researching I found out the problem was caused by namespaces that used in the XML file. The .node_name notation doesn’t work if an XML uses namespaces. If the XML uses xmlns=”hellworld” as its namespace, refering to the node will be xmlFile.helloworld::node_name. To get the namespace of a file, simply call xmlFile.namespace(). It explains well why my excelXml..Cell.Data failed. With namespaces in place, AS3 works like a charm.

Here’s an example of loading an Excel generated XML file. Enjoy>>source<<.
Since AS3 can read Excel generated XML easily, there is a high possibility that EGX files become an important component for Flash content management system (CMS). Double click at EGX files will open the system’s default Office software and it’s so easy to edit an Excel file in Office. CMS users won’t be forced to use notepad or other “scary” text editors (most of non-IT people really find text editors scary). Besides, there’s much less chance that a CMS user mess up some XML file, leaving unclosed tags, wrongly nested nodes etc. Althogh the file size is substantially bigger than “clean” XML files, but comparing with the SWF file size and taking broad band speed into consideration, file size is nothing in this age.p.s There’s an interesting bug when reading an EGM in AS2. XML Class in AS2 can’t handle the second line in EGM which is <?mso-application progid=”Excel.Sheet”?>. To Solve the bug, do not use the onLoad function of XML. Use onData function instead. Remove line <?mso-application progid=”Excel.Sheet”?> using string manipulation (split/pop the kind of thing) then call this.parseXML(xmlStr) function.

This entry was posted on Wednesday, December 12th, 2007 at 12:21 am and is filed under Tips. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.