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 with the js eval() function (from trusted sources) and consequently 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 he official Internet media type for JSON is application/json. But note that, as a pratical matter this mime type can be problematic, so I typically serve it with the mime type text/json instead. JSON lint is a very useful tool for validating .json files.
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.
This page illustrates 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, 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.
You'll perhaps notice in the data below that I next need to correct the usual quirks
(the square box or 
depending on browser, which should be the vulgar fraction ½) incurred by rendering
UTF-8 (specified in Content-Type) from UTF-16 in the emitted JSON dataset...
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 the 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)
