How to export CQ5 report in CSV format

If you are reading this post then probably you want to create a report in CQ5 and export the contents of that report in CSV format. I once faced this requirement and even after searching a lot, I could not find a generic report exporting utility that exports the data along with headers (i.e. the keys present in JSON data).

If you have JSON data and want to export the data in CSV format you will need some Javascript methods in order to achieve it. I have listed four ways how you can provide the data and generate the report with/without the headers.

If you don’t have JSON data, then you will have to write a sling servlet that will create a JSON and supply that to these JS methods in order to create reports.

function json2csv(objArray, headers, showHeaders) {

 if (typeof headers == "boolean") {

  showHeaders = headers;

  headers = null;

 }

 var itens = typeof objArray != 'object' ? JSON.parse(objArray) : objArray;

 //separate fields   

 var fields = {};

 if (!headers) {

  for (var i = 0; i < itens.length; i++) {

   for (var prop in itens[i]) {

    if (!fields[prop]) {

     fields[prop] = [];

    }

   }

  }

 } else {

  headers.forEach(function(header) {

   fields[header] = [];

  });

 }

 //getting data  

 for (var i = 0; i < itens.length; i++) {

  for (var prop in fields) {

   if (typeof itens[i][prop] != "undefined") {

    fields[prop].push(itens[i][prop]);

   } else {

    fields[prop].push("");

   }

  }

 }

 //make the csv  

 var csvLines = [];

 if (showHeaders) {

  var lineFields = [];

  for (var prop in fields) {

   lineFields.push(prop);

  }

  var line = lineFields.join(", ");

  csvLines.push(line);

 }

 for (var i = 0; i < itens.length; i++) {

  var lineFields = [];

  for (var prop in fields) {

   lineFields.push(fields[prop][i]);

  }

  var line = lineFields.join(", ");

  csvLines.push(line);

 }

 var csvStr = "sep=,\n" + csvLines.join("\n");

 return csvStr;

}

function csvStr2file(csvStr) {

 var base64 = btoa(csvStr);

 return "data:text/csv;base64," + base64;

}

var data = [{

  country: "America",

  city: "Kansas",

  employe: 'Jacob'

 },

 {

  country: "Pakistan",

  city: "Lahore",

  employe: 'tahir'

 },

 {

  country: "Pakistan",

  city: "Islamabad",

  employe: 'fakhar',

  car: "cross fox"

 },

 {

  country: "Pakistan",

  city: "Lahore",

  employe: 'bilal'

 },

 {

  country: "India",

  city: "d"

 },

 {

  city: "Karachi",

  employe: 'eden'

 },

 {

  country: "America",

  city: "Kansas",

  employe: 'Jeen'

 },

 {

  country: "India",

  city: "Banglore",

  employe: 'PP'

 },

 {

  country: "India",

  city: "Banglore",

  employe: 'JJ'

 },

];

//example to create...   

var csv = "";

var pre = null;

var a = null;

/*  

 * Below i have listed four different ways you can generate the report with/without headers (Headers for me is Key of Json Object)  

 * and if you have provided your headers then the json data that matches to the provided headers appears in CSV  

 */

//When you have not provided the headers, and when csv generated does not show headers  

document.body.appendChild(h3 = document.createElement("h3"));

h3.innerText = "Without provide the headers, not showing the headers";

csv = json2csv(data); //<---- HERE  

pre = document.createElement("pre");

document.body.appendChild(pre);

pre.innerText = csv;

//example to put download on a link...  

a = document.createElement("a");

document.body.appendChild(a);

a.innerText = "download csv!";

a.download = "my-csv-file.csv";

a.href = csvStr2file(csv);

//When you have not provided the headers, and when csv generated shows the headers  

document.body.appendChild(h3 = document.createElement("h3"));

h3.innerText = "Without provide the headers, showing the headers";

csv = json2csv(data, true); //<---- HERE  

pre = document.createElement("pre");

document.body.appendChild(pre);

pre.innerText = csv;

//example to put download on a link...  

a = document.createElement("a");

document.body.appendChild(a);

a.innerText = "download csv!";

a.download = "my-csv-file.csv";

a.href = csvStr2file(csv);

//When you have provided the headers and CSV generated does not show the headers.  

document.body.appendChild(h3 = document.createElement("h3"));

h3.innerText = "Without provide the headers, not showing the headers";

csv = json2csv(data, ["car", "city", "employe"]); //<---- HERE  

pre = document.createElement("pre");

document.body.appendChild(pre);

pre.innerText = csv;

//example to put download on a link...  

a = document.createElement("a");

document.body.appendChild(a);

a.innerText = "download csv!";

a.download = "my-csv-file.csv";

a.href = csvStr2file(csv);

//When you have provided the headers and CSV generated shows the headers.  

document.body.appendChild(h3 = document.createElement("h3"));

h3.innerText = "Without provide the headers, showing the headers";

csv = json2csv(data, ["car", "city", "employe"], true); //<---- HERE  

pre = document.createElement("pre");

document.body.appendChild(pre);

pre.innerText = csv;

//example to put download on a link...  

a = document.createElement("a");

document.body.appendChild(a);

a.innerText = "download csv!";

a.download = "my-csv-file.csv";

a.href = csvStr2file(csv);

Source: https://cqwemblog.wordpress.com/2013/12/01/how-to-export-cq5-report-in-csv-format/