Lots of chart data originates in Excel, so it is only natural to want to directly read Excel files. In general, reading Excel files can be complicated and error prone. Many of the tools available for ActionScript are also fairly experimental. So for most applications, you will probably want to use CSV or XML files as mentioned in the previous recipes, or JSON as we'll discuss in the next recipe.
However, in circumstances where you have good control over the version(s) of Excel used and the layout of the Excel spreadsheet, reading them directly could be a huge time-saver for your users.
For this recipe, we will use the freely available ActionScript 3 XLSX reader by Ben Morrow. It can be downloaded from the GitHub page, https://github.com/childoftv/as3-xlsx-reader. If you are comfortable using Git and GitHub, it is easiest to clone the repository on your local drive. If you don't have the Git software installed, it is best to use the Downloads link on the page. This will enable you to download a file with the latest release.
No matter how you obtain the code, you should make sure the com source folder is available to your application. The quickest and most reliable way is to copy it into the src folder of your project (you can also add it to the classpath). Once copied, your project should look like the following screenshot:
Now create a
new Recipe5 class as before:
- Have the class extend
Sprite - Set up the
Graphas before
- First embed the Excel file and give it a class name:
[Embed(source="../lib/data.xlsx", mimeType="application/octet-stream")] private var DataClass:Class;
- The Excel loader class works similar to the
URLLoaderclass. It uses events and callbacks, so we create a private field to hold it:private var xlsLoader:XLSXLoader;
- Inside the
Recipe5constructor, after we have set up the graph, we start the loading of the Excel file:xlsLoader = new XLSXLoader(); xlsLoader.addEventListener(Event.COMPLETE, onComplete); xlsLoader.loadFromByteArray(new DataClass());
- Now it's a matter of adding the
onCompletemethod, which will do the actual graph drawing:private function onComplete(event:Event):void { var sheet:Worksheet = xlsLoader.worksheet("data"); var rowX:XMLList = sheet.getRowsAsValues("A"); var rowY1:XMLList = sheet.getRowsAsValues("B"); var rowY2:XMLList = sheet.getRowsAsValues("C"); for (var i:int = 0; i < rowX.length(); i++) { graph.drawPoint(rowX[i], rowY1[i]); graph.drawPoint(rowX[i], rowY2[i], 0x9933ff); } } - That's all there is to it. Running this program should, once again, yield the same result. Only now are we reading the data from an Excel file.
The bulk of the work is done by the XLSXLoader class. It will make sure the Excel file is converted into a data structure that we can use. It has a similar interface to the URLLoader class, so its usage should feel familiar.
You may notice a lot of trace output if you run the program. This alone should make clear that this code is experimental and you should think twice before using it in an actual product. There are other libraries available, but they have similar restrictions.
The onComplete method only uses a small part of the Worksheet interface. If you investigate that code a little, you'll notice that the Excel file is actually an XML file. So you can use ActionScript's XML handling shortcuts, such as accessing attributes with the @ symbol
and tags through the property of the same name.
Reading Excel files can be a major challenge. If you run into issues, you can try a different library, or you may want to find ways to remove Excel from the program altogether.
There is one other library for reading Excel files in ActionScript that deservers your attention, and that is as3xls. It can be found at http://code.google.com/p/as3xls/ and is a little more mature then the one used in the recipe. It hasn't been updated since 2008 and therefore will give you issues with the latest Excel versions.