Generate excel or html with coldfusion from JSON format

Question

I need to generate a report with data in JSON format. Here is the action file that receives parameters and displays either html table with data or excel. I need help with parsing JSON data I am getting into required format (html or excel). I know how to work with excel formatting, I just need help with parsing JSON data and outputing it.

<cfajaxproxy cfc="components.acct.accttrx">
<script src="../jQuery/js/jquery-1.7.2.min.js"></script>

<cfparam name="prD" default="0_0">
<cfparam name="sM" default="1">
<cfparam name="sY" default="2013">
<cfparam name="status" default="0">
<cfparam name="FORM.fileformat" default="html">

<script type="text/javascript">
  jQuery(document).ready(function(){        
    var myURL = '../components/acct/accttrx.cfc';

  function populateReport(){            
  $.ajax({
  url: myURL + "?method=GetTranactions",
  type: 'POST',
  data: { 
    prID: '<cfoutput>#prD#</cfoutput>',
    mn: '<cfoutput>#sM#</cfoutput>',
    yr: '<cfoutput>#sY#</cfoutput>',
    st: '<cfoutput>#status#</cfoutput>',
    ea_adm:'1',
    slist:'',
    lang: '1'
  },
  dataType: "json",
  success: 
    function(data){
    $.each(data, function(index, item) {
      //(item.description,item.balance,item.accttrx_status);
      // I need help from this point to pass 
      // data to cfml part of my code
      });
    },
  error: 
    function(){
      alert("An error has occurred while fetching records");
    }
});
populateReport();
});
</script>
</HEAD>
<body>
<cfif FORM.fileformat Eq 'excel'>
    <cfset VARIABLES.vcFilename = "thisreport_" & DateFormat(Now(), "yyyymmdd") & "-" &  TimeFormat(Now(), "HHmmss") & ".xls">
    <cfsetting enablecfoutputonly="Yes">
    <cfcontent type="application/vnd.ms-excel">
    <cfheader name="Content-Disposition" value="inline;filename=""#VARIABLES.vcFilename#""">

     <cfoutput>
      <!--- here display data from JSON object --->
     </cfoutput>
  <cfsetting enablecfoutputonly="No">
  <cfelse>
    <div id="reportcontents" />  <!--- display row html --->
</cfif>

Here is the data from Response:

{"ROWS":[ { "balance":-642.04000, "description":"ABC Company", "ee_name":"Jon, Doe", "plan_id":0, "debit":25000.22000, "accttrx_year":2013, "sponsor_id":5, "division_id":0, "accttrx_month":1, "credit":26591.25000, "trx_employee_id":1, "cheque_amt":25000.00000, "cheque_no":"", "accttrx_status":"B", "accttrx_id":15 }, { "balance":-642.04000, "description":"ABC Company", "ee_name":"James, Done", "plan_id":0, "debit":32233.07000, "accttrx_year":2013, "sponsor_id":5, "division_id":0, "accttrx_month":1, "credit":30000.00000, "trx_employee_id":1, "cheque_amt":32233.07000, "cheque_no":"", "accttrx_status":"P", "accttrx_id":14 } ], "PAGE":1, "RECORDS":2, "TOTAL":1.0 }

score 1 · Answer 1 · answered Jan 29 '13 at 19:01

1

If you are using ColdFusion 8 or later, you can use DeserializeJSON() to turn the JSON into a structure. You can then loop over this structure using cfloop and turn the content of the structure into a query (using QueryNew(), QueryAddRow() and QuerySetCell() inside the loop). From there, you can either use the cfspreadsheet tag to convert that query into an Excel file (or csv file) if you are using ColdFusion 9, or you can output plain old HTML using any version of ColdFusion and set the header to trick the browser into thinking it's an Excel file, as follows:

<cfcontent type="application/msexcel">

answered Jan 29 '13 at 19:01

Brian

565
3
5

I am on CF8. How do I grab the data into DeserializeJSON(). I figure, its done here function(data){ $.each(data, function(index, item) { //(item.description,item.balance,item.accttrx_status); }); – user1706426 Jan 29 '13 at 19:26
Outputting html and tricking the browser into thinking it's an Excel file became a bad idea when MS introduced Office 2007. – Dan Bracuk Jan 29 '13 at 23:40
Sorry, I did not make it clear. I need help to complete my code. I am having hard time to finish up this function to pass the data to the output from here or if there is a better way to extract and push data to display. function(data){ $.each(data, function(index, item) { //(item.description,item.balance,item.accttrx_status); }); – user1706426 Jan 30 '13 at 14:02
I forgot to mention I can not deserializeJSON() in cffunction as it is used in another process with jqgrid. So I have to deserializeJSON in the client when data is extracted. – user1706426 Jan 30 '13 at 14:26

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

Don't use AJAX. Simply create a JS function to push the report params to your processing code. See this post for an example. Essentially:
```
 window.location="process.cfm?param1=&param2=";
```
Use deserializeJSON() in the '../components/acct/accttrx.cfc' file to parse the posted JSON into a CF structure.

Use the following CF to get the browser to recognize the file download:

 <cfheader name="Content-Disposition" value="attachment;filename=XXXX">
 <cfcontent type="application/vnd.ms-excel" reset="false">

edited May 23 '17 at 12:04

Community

1
1

answered Jan 30 '13 at 00:52

malpaso

177
1
8

I did #1 and #3 but, I can not #2 deserializeJSON() in cffunction as it is used in another process with jqgrid. So I have to deserializeJSON in the client when data is extracted. Sorry, I did not make it clear. I need help to complete my code. I am having hard time to finish up this function to pass the data to the output from here or if there is a better way to extract and push data to display. function(data){ $.each(data, function(index, item) { //(item.description,item.balance,item.accttrx_status); }); – user1706426 Jan 30 '13 at 14:53

Generate excel or html with coldfusion from JSON format

2 Answers2