I've shown how easy it is to push spreadsheet data into CRX (in such a way that there is one content node per row of data, where properties on that node correspond to column data). The reverse is also possible: It's easy to write a script that converts sibling nodes to row data formatted as CSV (comma-separated values per RFC 4180). Such a script, csv.esp, looks something like this:
// Given a list of sibling nodes (presumably
// similar in structure), and an array of
// property names, convert each node
// to one "row" of CSV data, where
// columns correspond to properties.
// We will encode all property data as
// comma-separated values per RFC 4180.
function nodesToCSV( nodes, propertyNames ) {
var records = new Array( );
for ( var i = 0; i < nodes.length; i++ ) {
var aRecord = new Array( );
// suck in the data for each property:
for ( var k = 0; k < propertyNames.length; k++ ) {
var data = nodes[ i ][ propertyNames[ k ] ];
var escaped = escapeData( data );
aRecord.push( escaped );
}
records.push( aRecord.join( "," ) );
}
var CRLF = String.fromCharCode(13) +
String.fromCharCode(10);
return records.join( CRLF );
}
// Return an array of property names for this node
function getOrderedProperties( node ) {
var array = new Array();
for ( var i in node )
array.push( i );
return array;
}
// Escape field data per RFC 4180
function escapeData( data ) {
// replace " with ""
data = String(data).replace( /"/g, "\"\"" );
// if data contains comma, CRLF, or "
// we need to wrap the entire thing in double quotes
var escapables = /,|(\r\n)|"/;
if ( data.match( escapables ) )
return "\"" + data + "\"";
return data;
}
%>
<% nodes = currentNode.getNodes( );
// get a list of property names
propertyNames =
getOrderedProperties( nodes[0] );%>
<%= nodesToCSV( nodes, propertyNames ) %>
The rules for escaping data for CSV are extremely simple. First, any data string that contains the double-quote (") character needs to have each such character converted to two double-quotes (""). Secondly, if the data contains a comma, the entire data string needs to be wrapped in quotation marks. The same is true for any data that contains double-quotes or line breaks (which RFC 4180 defines as CRLF -- carriage return followed by linefeed). The following very simple function enforces these escaping rules:
// Escape field data per RFC 4180
function escapeData( data ) {
// replace " with ""
data = String(data).replace( /"/g, "\"\"" );
// if data contains comma, CRLF, or "
// we need to wrap the entire thing in double quotes
var escapables = /,|(\r\n)|"/;
if ( data.match( escapables ) )
return "\"" + data + "\"";
return data;
}
The function that actually converts nodes to records is very straightforward as well:
function nodesToCSV( nodes, propertyNames ) {
var records = new Array( );
for ( var i = 0; i < nodes.length; i++ ) {
var aRecord = new Array( );
// suck in the data for each property:
for ( var k = 0; k < propertyNames.length; k++ ) {
var data = nodes[ i ][ propertyNames[ k ] ];
var escaped = escapeData( data );
aRecord.push( escaped );
}
records.push( aRecord.join( "," ) );
}
var CRLF = String.fromCharCode(13) +
String.fromCharCode(10);
return records.join( CRLF );
}
Note that we need to explicitly provide the function a list of property names, rather than (say) let the function iterate through property names on an introspective basis. The reason for this is that if we simply try gathering property names with a for/in loop, we will get back property names in no particular order. And the order will, in fact, vary from content node to content node even if all of the content nodes have properties with exactly the same names. The unorderedness of the properties (as obtained through simple iteration) would scramble the column data in our CSV file. We don't want that. Hence, we pass in an array of property names, and march through the array in orderly fashion when pulling property data from each node.
When I placed csv.esp in my repository under /apps/films and then navigated to http://localhost:7402/content/films.csv, CRX dutifully fired my script and produced a CSV file containing all of the data from my /films content nodes, causing my browser (in turn) to inform me that I was downloading a file of type "csv" (it then asked me what program I wanted to use to open the file; I specified scalc.exe, and OpenOffice dutifully loaded the file as a spreadsheet).
So far, I've shown how to render /films data as HTML, SVG, and CSV. Next time, I want to show a simple trick for rendering the data as PDF. It's easier than you think!
Additionally, when using CQ5, there is a great JSON output class:
com.day.cq.commons.TidyJSONWriter
With that you can do the following:
<%
TidyJSONWriter json = new TidyJSONWriter(response.getWriter());
json.object();
json.key("foo").value("Hello World");
json.key("bar").array().value(1).value(3).value(5).value(7).endArray();
json.endObject();
%>
Which will output the following JSON:
{
"foo": "Hello World",
"bar": [1, 3, 5, 7]
}