We previously looked at how to embed a motion chart in excel, as well as how to serialize data as input to one. As a matter of fact, the interaction with the visualization API and the format of the input data is rather standard across different kinds of charts, so it is very simple to build on what we’ve previously done to create a general purpose tool that can create any visualization chart. Adding new charts to the repertoire will therefore be very simple using this framework. You can find the generalized tool in the downloads section. This means that the previous single use implementations have now been replaced with this single tool.

Recap

We now know how to create a Google Motion Chart , Embed it in our Excel Sheet, or serialize data for input to a web page or gadget containing a motion chart. All this is achieved by a single click in our example Excel Form. To take it to the next level, we want to create a framework to make it easy to add new visualizations. The example so far includes at least Motion Charts and Intensity Maps. By the time you download it, there may have been more added.

Example Application

The downloadable example provides a form to create all the necessary web pages and data, and optionally to display the chart. It looks like this.

This application does the following;
  1. Identifies and processes the columns of data on your active sheet that are to be used as input to Google Visualization.
  2. Generates an html file containing the code and data combined. This can be directly loaded in a browser locally (assuming you have followed the instructions on enabling Flash locally), or it could be loaded on a web site and executed from there.
  3. Navigates to that local file and shows the chart embedded in the form.
  4. Creates a serialized data file that you can use as input to a Google visualization Gadget or some other script that requires JSON serialized data using  Google Wire Protocol. An example wrapper script is provided so you can customize it.
  5. Provides a command line with the parameters needed to your wrapper script in order to generate the required chart and link it to the serialized data. This would look something like this

Structure of the example application

  1.  A form as above to select options and parameters
  2. An application module to execute the options and parameters – googleChartModule.
  3. cGoogleChartInput class. This class does all the work to serialize the Excel data as well as to generate the required javascript.
  4. cDataSet and related classes. This is a set of utility classes that abstract data from their spreadsheet location, and are used by the cGoogleChartInput class. They are not the subject of this article, but more information can be found here if required.

cGoogleChartInput class

We are going to use a single class with common methods to create and serialize data.  I have introduced an eNum in the cGoogleChartInput Class, as follows. This will be expanded as new chart types are added.
It is initialized with these parameters. The headOrderArray argument is provided so that the data from the sheet can be re-organized into a different order in case it does not meet the needs of the various Google visualizations. I have not implemented this in the example application, but columns can be omitted or reorganized by specifying and array of column headings that should be included, for example array(“Country”, “Cost per Employee”) would use only 2 of the columns in that order. If the argument is missing, all columns identified by the column heading range are used. Note also that there is no validation that the input data is of the correct type for the selected visualization. You will need to read up on them to see the type of data expected.
Aside from minor changes in the methods that construct the javascript syntax, there was really not much more new  in this class from the previous excercise. Only the google.visualization method name and the package name varies from chart to chart, so this is easily abstracted as follows
along with some parameterizations to create the javascript
In order to minimize name conflicts should the output of multiple charts be combined, function, data, div ID, and variable names are changed according to the type of chart we are creating

Example output

Here is what the generated script for an embedded IntensityMap comes out like.

Wrapper

The wrapper in the previous section is an example of a web page you might want to write to include serialized data, where you want to separate the data and the chart.  You can download it (googleVisWrapper.html), or use the code below as a starter. You will note it takes 5 arguments. The example application provided generates a command line that can be used directly in a browser as follows

For help and more information join our forum, follow the blog, follow me on Twitter