MadIce
asked on
ColdFusion use JavaScript page to display query results and export to excel
I have a search page for user input that opens another page for viewing. That Page (call it test.cfm), is supposed to display the query results and display a button to export to excel. IF I use an cfinvoke to a component where I run the query and return it works. But trying to do that portion with an include to a javascript page using ajax and json to return the query. But never done this before and can't figure it out. Does anyone have a example how to do this?
I've attached my cfm and cfc page. I stripped out code to make it more simple to review. didn't include the javascript page because it's all wrong.
test.cfm
Mls.cfc
I've attached my cfm and cfc page. I stripped out code to make it more simple to review. didn't include the javascript page because it's all wrong.
test.cfm
Mls.cfc
ASKER
_agx_, Thank you for the response. When I get home tonight, I will give it a try.
As noted, this generates a "real" spreadsheet file. There are jQuery plugins that support psuedo-excel exports, using html, such as
https://datatables.net/extensions/buttons/
https://github.com/ranjithprabhuk/SlickGrid-Export-to-Excel
Since I tend not to use those, if that's what you're looking for, I'll leave the explanation to others :-)
https://datatables.net/extensions/buttons/
https://github.com/ranjithprabhuk/SlickGrid-Export-to-Excel
Since I tend not to use those, if that's what you're looking for, I'll leave the explanation to others :-)
ASKER
_agx_ thank you for this. I did give it a try it works. I'm new to web design and ColdFusion so have a follow-up question that I didn't explain in original question. Is it possible to have my cfm form control the table structure like the following:
<h1>MLS Search Results</h1>
<table id="tblMLS" class="display compact">
<thead>
<tr>
<th>Model</th>
<th>NNS</th>
<th>Loc</th>
</tr>
</thead>
add an included to a JavaScript page that handles the call to the cfc file and display? Its what I was trying to do but when trying to pass the parameters, it's always null. Trying something like the following:
I'm going to see if I can separate with what you provided.
<h1>MLS Search Results</h1>
<table id="tblMLS" class="display compact">
<thead>
<tr>
<th>Model</th>
<th>NNS</th>
<th>Loc</th>
</tr>
</thead>
add an included to a JavaScript page that handles the call to the cfc file and display? Its what I was trying to do but when trying to pass the parameters, it's always null. Trying something like the following:
<script type="text/javascript">
$(document).ready(function(){
"use strict";
var dtMls = $('#tblSirStatus').DataTable({
ajax: "../Components/MIS/MIS.cfc?method=readModelSearchJson&returnFormat=json<cfoutput>&Model=#FORM.Model#&NNS=#FORM.NNS#&Loc=#FORM.Loc#</cfoutput>"
, columns: [
{data: "Model"}
,{data: "NNS"}
,{data: "Loc"}
// end columns
]
});
// Add event listener for opening and closing details
$('#tblMlsStatus tbody').on('click','td.details-control',function() {
var objTR = $(this).closest('tr');
var objRow = dtMls.row(objTR);
if (objRow.child.isShown()) {
// This row is already open - close it
objRow.child.hide();
objTR.removeClass('shown');
}
else {
// Open this row
var lcTblDetail = formatDetail(objRow.data());
objRow.child(lcTblDetail).show();
objTR.addClass('shown');
}
// end $('#tblMlsStatus tbody').on
});
});
/*Formatting function */
function formatDetail(d){
"use strict";
return '<table align="center" width="93%" cellpadding="5" cellspacing="0" border="0">'+
'<tr>'+
'<td width="72">Model</td>'+
'<td>'+d.Model+'</td>'+
'</tr>'+
'<tr>'+
'<td>Developer:</td>'+
'<td>'+d.NNS+'</td>'+
'</tr>'+
'<tr>'+
'<td>Type:</td>'+
'<td>'+d.Loc+'</td>'+
'</tr>'+
'</table>';
}
</script>
I'm going to see if I can separate with what you provided.
ajax: "../Components/MIS/MIS.cfc?method=re adModelSea rchJson&re turnFormat =json<cfou tput>&Mode l=#FORM.Mo del#&NNS=# FORM.NNS#& Loc=#FORM. Loc#</cfou tput>"
I see you're using the DataTables plugin. It sounds like you're asking whether you can display the HTML search results dynamically - with ajax. The answer is yes. However, I'm not sure I correctly understand what you want that code to do...
Are you trying to control which columns the CFC returns? i.e. Only return the columns named "Loc", "NNS" and "EFG"
[ {Loc=123, NNS="222", Model="444"}
, {Loc=456, NNS="333", Model="555"} ]
... or control the content of the columns? i.e. Only return data where the Loc value = 456
[ {Loc=456, NNS="333", Model="555"} ]
(... I know you ultimately want to Export to Excel too, but one piece at a time :)
ASKER
Thank you. Trying to control the content of the columns.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
BTW, the example uses some CF2016+ specific syntax. If you're using an older EOL'd version like CF11, I'll need to make a few small changes to the CFC. To check your version
<cfdump var="#server.coldfusion#">
Also, if you're new to CF, be aware that it also supports CFScript syntax, not just tag based code (i.e. CFML). If you're coming from a .net background or other scripting languages, CFScript is a lot more similar than CFML.
<cfdump var="#server.coldfusion#">
Also, if you're new to CF, be aware that it also supports CFScript syntax, not just tag based code (i.e. CFML). If you're coming from a .net background or other scripting languages, CFScript is a lot more similar than CFML.
ASKER
Thanks for response. I'll check my parameter issue. I've been trying to make sure that's not one of the issues.
For question B, I have a search form that passes the parameters to the test.cfm. I took the code you provided to test and it passes the input value correctly. odd thing is if I click the display button, it displays the data. But when I click the export to excel button, no data. But if I first click the excel button, data is exported.
Ideally, when test.cfm opens, it will automatically display the data without clicking a display button. Just have the export to excel button. I've been trying to work on that.
I'll give your example a try. Also I am using ColdFusion 2016
For question B, I have a search form that passes the parameters to the test.cfm. I took the code you provided to test and it passes the input value correctly. odd thing is if I click the display button, it displays the data. But when I click the export to excel button, no data. But if I first click the excel button, data is exported.
Ideally, when test.cfm opens, it will automatically display the data without clicking a display button. Just have the export to excel button. I've been trying to work on that.
I'll give your example a try. Also I am using ColdFusion 2016
Edit
>> odd thing is if I click the display button, it displays the data.
That sounds like the old example. I wasn't sure how your cfm scripts were structured, so I deliberately set it up to wait until you clicked "display" before showing the results.
Try the new new example - "as is" first. It should automatically display the results when you submit the search form.
>> odd thing is if I click the display button, it displays the data.
That sounds like the old example. I wasn't sure how your cfm scripts were structured, so I deliberately set it up to wait until you clicked "display" before showing the results.
Try the new new example - "as is" first. It should automatically display the results when you submit the search form.
ASKER
I've copied over the code but there seems to be an issue at this line on test.cfm:
, "data": #serializeJSON( dataFilters)#
Says it's missing } after the property list. they seem to line up.
, "data": #serializeJSON( dataFilters)#
Says it's missing } after the property list. they seem to line up.
Edit: Are you sure you copied the whole thing "as is" - and that you're not mixing the example with other code? I just recopied the test.cfm code directly from the EE post above, and didn't get any errors.
Where's are you seeing an error - in CF or javascript/web console?
Where's are you seeing an error - in CF or javascript/web console?
ASKER
I'm using ColdFusion Builder.
ASKER
I'll redo just incase.
Okay. As sanity check, I just recopied all 3 scripts from above - and the example still works without error in FF and Chrome. So it sounds like there something different on your end :)
ASKER
So I still see a "x" on that line but the issue was when I created the Search page, I save to wrong location and or different name. works nicely now and export to Excel. I appreciate the help. Been struggling with this. Needed a template to do the same thing with other reports.
See an X in CF Builder, you mean? I don't use that IDE, but it's possible it's a false positive by it's built-in syntax checker? Since the code does evaluate to valid JS, I'm not sure what it's complaining about ;-)
Keep in mind you don't have to structure the search form exactly this way. I just separated things because I thought the example would be easier to follow. Anyway, glad it helped!
Keep in mind you don't have to structure the search form exactly this way. I just separated things because I thought the example would be easier to follow. Anyway, glad it helped!
ASKER
Thanks again for all your help and example. I'm going to add my query and see how that goes.
test.cfm
Open in new window
YourComponent.cfc
Open in new window