blue       earthtones
Emitting JSON from MS Excel

Discussion: JSON (pronounced "Jason") is an acronym for JavaScript Object Notation, an approach which has gained significant traction in recent times as a lightweight alternative to XML. Based on JavaScript semantics, it's a well-understood format for encoding and transferring collections of objects that contain name/value pairs and nested arrays of such. Perhaps its most endearing property is that it can be directly parsed either with JSON.parse() in those browsers which support the JSON object, or with the js eval() function (from trusted sources). Consequently JSON provides ready access to data encoded in this format. As you might also expect, transporting a .json file via the XMLHttpRrequest is pleasantly mundane.

The JSON format is specified in RFC 4627 by Douglas Crockford. The accepted filename extension is .json, and the official Internet media type for JSON is application/json. JSON lint is a very useful tool for validating .json files, and another indispensible tool for developers can be found in Firebug (a Firefox plugin) which formats json responses in it's 'net' panel; this greatly simplifies examining responses which are served with a Content-Type header of application/json.

For an interesting perspective on the history of JSON, read Discovering JavaScript Object Notation, a recent article published by the IEEE Computer Society:

Solution: It's a relatively straightforward process to emit valid JSON directly from an Excel workbook using a VB module. This is a similar process to emitting well-formed XML, considering only some differences in syntax. In view of the surprising number of technical, scientific, and other datasets which have been compiled in MS Excel over the years, JSON provides a useful solution for preparing this data for presentation on the web.

The topics below illustrate one technique for exporting a valid .json file from an Excel workbook, and subsequently displaying its content with client-side JavaScript. For more background on the example presented here, see my discussion on Emitting XML from MS Excel elsewhere on this site.



Example: The Emitted Mineral Database in JSON format

Code: A Visual Basic Module to Emit the Mineral Database from MS Excel

Usage: An Example of Selecting One Mineral from the Dataset Using Client-Side JavaScript

This example uses client-side JavaScript to retrieve the demo database from the server with XMLhttpRequest (AJAX), then select and display a subset of the name/values for one mineral each time you select a different one from the drop-down list below. The object of this example is simply to illustrate one method of parsing, extracting, post-processing, and displaying elements from a JSON file. Consequently there is no server page involved since the demo version of the mineral database is managably small. In practice with a large JSON object (dataset), a jsp, asp, php, or other server page should be used to extract and return only the target object.

A simple cross-browser solution to parse the resulting JSON once it's been retrieved from a server via an AJAX or XMLHttpRequest call is to use a ternary operstor to determine the most efficient way to parse the response in the visitor's browser (JSON.parse() is 'way faster if it's available). The following JavaScript snippet llustrates this:

	var theJsonObject = (window.JSON) ? JSON.parse(data) : eval(data);
	
Select a Mineral: 

Name:  

Formula:  

Crystal Sysyem:  

Hardness:  

Specific Gravity:  

Optical Properties:  

 

JavaScript: The Code Snippet that Displays the Results

The salient bits from the javascript code are:


	(snip...)
	// persistent global variables:
	var rawMin = '';  /* json string loaded via XMLhttpRequest is here */
	var minDB = {};	/* json object containing the entire string transformed to object notation */
	var minArray = {}; /* json object containing only the minerals, in object notation */
	var minCnt = 0;	/* number of minerals in the database */ 
	var targetMin = {}; /* this mineral sliced from the json object */

	(snip...)
	minDB = eval('(' + rawMin + ')');  // transform to object; disambiguate with extra parens
	minArray = minDB.mineral_database.mineral; //only the minerals
	minCnt = minArray.length; //number of minerals


	// This function is invoked when a mineral is selected from the list
	function showMin (selector) {
	var dropDown = document.getElementById(selector);
	var minName = dropDown.options[dropDown.selectedIndex].text.toUpperCase();

	for (i=0;i<minCnt;i++) { //search the array for a matching mineral name
		if (minArray[i].name == minName) {
			targetMin = minArray[i];
			break;
			}
		};
	if (i == minCnt) {	/* invalid mineral name or valid and not found in the demo file*/
		//(put an error message here) 
		return;
		};
	document.getElementById('jName').innerHTML = targetMin.name;
	document.getElementById('jFormula').innerHTML = targetMin.formula;
	chemFormat ('jFormula', 'jFormula'); //reformat flat string to canonical form
	document.getElementById('jXlSys').innerHTML = targetMin.crystal_system;
	document.getElementById('jHardness').innerHTML = targetMin.hardness;
	document.getElementById('jSpecificGravity').innerHTML = targetMin.specific_gravity;
	document.getElementById('jOptical').innerHTML = targetMin.optical_properties;
	} // mineral data is now visible on the page
		

Workbook: A Complete Working Example

The key to understanding the VB code lies in the name stack used for the json name/value pairs. It is simply a hierarchy of name tags from which the code infers the nesting level for each column in the spreadsheet. This approach greatly simplifies associating a full node path with each spreadsheet column. In particular, study the name stack used at worksheet column "R" to produce the chemical class name/value pairs:

EXCEL Worksheet:

"chemical_class":      
"dana":   "strunz":  
"class": "member": "class": "member":

json Output:

	"chemical_class": {
                    "dana": {
                            "class": "Native Elements",
                            "member": "1.1.1.1"
                            },
                    "strunz": {
                            "class": "Elements",
                            "member": "I\/A.01-40"
                            }
                    }
	   

You can download a complete working copy of this example here. Note that the code shown in the second tab above has been modified very slightly in order to run in this demo workbook. (50kb download, produced with Excel 2003)