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 );  
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s