While Web applications, such as Flex applications, usually read data coming from the server in a predefined form and thus do not necessarily need to read third party data formats (the server has digested them). Desktop applications, on the contrary, usually require the ability to read data from such sources.

This is especially true when it comes to data visualization applications where the end-user might want to, for example, import its data from a Microsoft Excel spreadsheet, and display them into a data grid or charting control.

The objective of this post is to see how your AIR (or Flex) data visualization application can read a Microsoft Excel spreadsheet and display its result.

There are several ways to proceed however the simplest one is probably the following:

1. Make sure the spreadsheet is available in Microsoft XML spreadsheet format
2. Read this XML into Flex
3. Create a representation of the initial XML that can be interpreted by Flex components
4. Pass the resulting object to a datagrid or a charting component depending on how you want to display the result

The first step is not really in your hands, it is up to the end-user to make sure his data are saved using Excel XML spreadsheet format using Save As... option. As an example let’s say the sheet contains the following data:

Column1	Column2	Column3	Column4
4	1	4	3
4	2	2	4
7	6	5	1
4	1	4	3
1	6	7	1

You can have a look at the corresponding Excel XML spreadsheet by downloading this file.

When it comes to read the XML data into Flex, you have several means. If the file is available as a URL you can simply use an URLLoader and use the data property of the URLLoader as the source for an XML object. If the file is somewhere on the file system and you are in an AIR application you can rely on the flash.filesystem APIs for that. You can find examples of this in Adobe documentation. The important thing is that in the end you must have an XML object that will be used to complete the next steps of the process.

The third step is to take the XML object and process it to build an in-memory data source that can be interpreted by Flex controls. For that we basically need to:

• Discover which are the fields of interest in the sheet by looking at the header (first) row
• Fill an array of objects with the fields computed in the step above and with the data of each of the subsequent rows in the sheet

For that we can use the following utility function that is leveraging E4X to process the XML:

private function createArrayFromExcelData(xml:XML):Array {
  var rows:XMLList = xml.Worksheet[0].Table.Row;
  var cell:XML;
  // make sure we use MS Excel namespace
  namespace msspreadsheet =
     "urn:schemas-microsoft-com:office:spreadsheet";
  use namespace msspreadsheet;
  // look at the first row of data = header for categories
  var header:XML = rows[0];
  var cells:XMLList = header.Cell;
  var categories:Array = [];
  for (var i:int = 0; i < cells.length(); i++) {
    cell = cells[i];
    // populate the categories array with each category
    categories.push(cell.Data[0].text().toString());
  }
  // now iterate over the next rows to populate the values
  // array with the data object for each row.
  // each data object is of the form:
  // { category1 : value1, category2 : value2, ... }
  var values:Array = [];
  for (var j:int = 1; j < rows.length(); j++) {
    var row:XML = rows[j];
    // create a new object placeholder for the row
    var value:Object = new Object();
    cells = row.Cell;
    var k:int = -1;
    for (i = 0; i < cells.length(); i++) {
      cell = cells[i];
      // for cells with index get the category index from the index field
      // for cells without index just increment the index
      if (cell.@Index.length() != 0) {
          k = cell.@Index - 1;
      } else {
          k++;
      }
      // get the value if any and put it in the object
      value[categories[k]] = cell.hasComplexContent() ?
        cell.Data[0].text().toString() : "";
    }
    values.push(value);
  }
  // return the array of values to be used with a Flex control
  return values;
}

Once we have the Array returned by createArrayFromExcelData we can directly use it to populate a Flex DataGrid as in the following MXML excerpt:

<mx:Script>
  <![CDATA[
  private function readXML():void {
    // whatever is needed to read the XML (either file or URL)
    var xml:XML = ...;
    var values:Array = createArrayFromExcelData(xml);
    dg.dataProvider = values;
  }

  private function createArrayFromExcelData(xml:XML):Array {
    // see above
  }
  ]]>
</mx:Script>
<mx:DataGrid id="dg" width="100%" height="100%"/>

And here is the result running in the browser:

Of course you can then use that Array as the data provider for different kind of components than the DataGrid such as ILOG Elixir 3D, 2D charts or treemap.

Tags: excel, microsoft, spreadsheet, xml

Posted by Christophe Jolif in AIR, Flex, Flex SDK. 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.