Solved

coldfusion, jQuery help

Posted on 2016-10-14
25
72 Views
Last Modified: 2016-10-19
I am looking for example code, using coldfusion cfm, cfc, jQuery do the following:

1st buttons click (cfm file) : query data, create excel file (cfc file)
2nd buttons click (cfm file): open a new window and display excel file just created. (not sure cfm or cfc)
0
Comment
Question by:jfreeman2010
  • 13
  • 7
  • 5
25 Comments
 

Author Comment

by:jfreeman2010
ID: 41844126
I cfc looks like: <cfcomponent output="false">

    <cffunction name="generate_Excel" access="remote" output="false" returntype="string">
        <cfargument name="id_list" type="string" required="true" />
            <cfargument name="BExclude" type="string" required="true" />
       
        <cfstoredproc procedure="template" datasource="MHL_DB">
            <cfprocparam type="in" variable="JOB_ID" value="#arguments.id_list#"  cfsqltype="cf_sql_varchar" />
            <cfprocparam type="IN" variable="BExclude"  value="#arguments.BExclude#"  cfsqltype="cf_sql_varchar" />
            <cfprocresult name="TempList" resultset="1"/>
        </cfstoredproc>
       

            <cfif isdefined("TempList.recordcount") and #TempList.recordcount# gt 0>
     
      <CFSET OUTFILE = "C:\temp\Template_#dateformat(now(),'yyyymmdd')#.csv">
            <cfset metaData = getMetaData(TempList)>
           
            <CFSET TXTOUTPUT = "">
            <cfloop array="#metaData#" index="colData">
                 <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#colData.name#"') >
            </cfloop>
            <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
           
            <CFLOOP QUERY="TempList">
                <CFSET TXTOUTPUT = "">
               
                <cfloop array="#metaData#" index="colData">
                    <cfset TXTOUTPUT=listAppend(TXTOUTPUT,  '"#TempList[ colData.name ][currentRow]#"') >
                </cfloop>
               
               
                 <cfif LEN(TXTOUTPUT)>  
                    <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">  
                </cfif>    
            </CFLOOP>
           
           <CFSET filename = "Template_#dateformat(now(),'yyyymmdd')#.csv">
         
         
       
        </cfif>
       
        <cfset obj = '#filename#'>
        <cfreturn obj />
    </cffunction>

 
</cfcomponent>
0
 

Author Comment

by:jfreeman2010
ID: 41844136
my jquery code in cfm file looks like this:
function getTempList(){
                  
       var x='70320'
        var c = "0";
                    

      var d = ({
            "id_list" : x,
            "BExclude" : c
            });
                  
      $.ajax({
            url: 'cfc/template.cfc?method=generate_Excel',
                  cache : false,
            type:"get",
            dataType : "html",
            data : d,
            success: function(objResponse){
            var parsedjson = $.parseJSON(objResponse);
            console.log(objResponse);
           
            }
      });
            
}
0
 

Author Comment

by:jfreeman2010
ID: 41844138
My jQuery got the resp back like this:
Template_20161014.csv


I can't find the file in my local machine
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 41845322
it's not in C:\temp ?
try changing the path to being under your web root
0
 

Author Comment

by:jfreeman2010
ID: 41846549
I am writing the excel file out to locate c:\ drive.  Its this a issue?  If write the excel file to web root, user can't or should access those file.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 41846987
is this your machine and you are just testing?
or is this a live site with professional hosting?
if the latter you won't (probably not anyway) have permission to write to the C: drive
if you want users to download file after generating then writing it under the web root will be your best option to allow user to download the file
0
 

Author Comment

by:jfreeman2010
ID: 41847069
I am working code for company/live site.
when user using cfm code, I want them to write the excel in they're locate c:drive, not  web root drive.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 41847071
Are you saying that you want the file to be downloaded to the user's computer on their C drive?
0
 

Author Comment

by:jfreeman2010
ID: 41847093
yes, when below code in cfm were able to create excel file and downloaded in user locate c: drive:
0
 

Author Comment

by:jfreeman2010
ID: 41847096
<CFSET OUTFILE = "C:\temp\Template_#dateformat(now(),'yyyymmdd')#.csv">
    <cfset metaData = getMetaData(TemplateQuery)>
   
    <CFSET TXTOUTPUT = "">
    <cfloop array="#metaData#" index="colData">
              <cfset TXTOUTPUT = listAppend(TXTOUTPUT, '"#colData.name#"') >
    </cfloop>
    <CFFILE ACTION="WRITE" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">
   
    <CFLOOP QUERY="TemplateQuery">
            <CFSET TXTOUTPUT = "">
       
        <cfloop array="#metaData#" index="colData">
              <cfset TXTOUTPUT=listAppend(TXTOUTPUT,  '"#TemplateQuery[ colData.name ][currentRow]#"') >
        </cfloop>
       
       
         <cfif LEN(TXTOUTPUT)>  
                  <CFFILE ACTION="APPEND" FILE="#OUTFILE#" OUTPUT="#TXTOUTPUT#">  
        </cfif>    
      </CFLOOP>
   
      <CFSET filename = "Template_#dateformat(now(),'yyyymmdd')#.csv">
      <CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#filename#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE">
0
 
LVL 25

Assisted Solution

by:dgrafx
dgrafx earned 50 total points
ID: 41847131
Can't be done!
As I said you need to write file under web root then you serve the file to user and they decide where to download it to and if they want to download it.
You can't decide where user downloads it to.
0
 

Author Comment

by:jfreeman2010
ID: 41847215
I thing I get what you said.  Why the code works in cfm file, but not cfc ?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 25

Expert Comment

by:dgrafx
ID: 41847241
If what you're wanting to do works in a .CFM file then I misunderstand what you're trying to do.
If the only difference is CFM to CFC then try
ReturnType=string
ReturnFormat=plain
In your function
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41847328
I could be wrong, but I don't think $ajax() does file downloads out of the box. Might need a plugin.

Though, to just trigger a download without leaving the page .. I'm not sure you even need ajax.  Have you tried a regular link?

1. Create a wrapper function to trigger the download.

      <cffunction name="downloadExcel" access="remote" output="false">
            <!--- generate_Excel() should be access=private and
                       MUST return a full file path, ie c:\temp\someUniqueName.csv --->
            <cfset var pathToTempFile = generate_Excel()>

            <!--- trigger download and DELETE the temp file --->
            <cfheader name="Content-Disposition" value="inline; filename=""FileNameToDisplayHere.csv""">
            <cfcontent type="text/csv" file="#pathToTempFile#" deletefile="yes">
      </cffunction>

2. Point to the CFC:

      <a href="http://yoursite/YourComponent.cfc?method=downloadExcel">Download File</a>
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 41847536
So again I may have misunderstood what you want to do.
So remove your jQuery function and provide a simple link like agx posted.
It's pretty much as simple as that!

Good luck ...
0
 

Author Comment

by:jfreeman2010
ID: 41848199
Hi All - thank you very much for the response and help.
My original cfm code had the following workflow and it works:
Get input from user, get data from storeproc and create a excel file, open/download for user. - all those code in a cfm file and it works.

 Now I need to alter as:
1) Get input from user, pass it to cfc file using jQuery/ajax call.  
2) cfc file will get data using storeproc and create a excel file, pass back the file name/location to called cfm file.  
3) cfm file when will use a button to open the downloaded excel file

When I try to do the above in 2), I did not see the file create and download, I was able to pass the file back to cfm file.
So my question is why the same code able to create excel file in cfm, but not cfc?

Does anyone have example doing thing like this?

Thank you very much for all of you helping!!!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41848311
Ignoring the question of whether you can download w/jQuery for a sec, there's no reason the same code can't work from a CFC. However, the initial code looks different than the CFM code you posted later . The CFC code contains an extra IsDefined check, and is missing the cfheader/cfcontent.  Those differences are probably why it's not working.

Since you say the original CFM file works, start by copying that code into a new page. Slowly add pieces one at a time to build it into a CFC. Test after each one, to help you spot what's breaking.  

First pass, hard coding the two argument variables at the top of the new .CFM script, and run it.  


 
        <!--- Hard coded arguments for TESTING only --->
        <cfset arguments.id_list = "some test value here">
        <cfset arguments.BExclude = "some test value here">

       <cfstoredproc procedure="template" datasource="MHL_DB">
            <cfprocparam type="in" variable="JOB_ID" value="#arguments.id_list#"  cfsqltype="cf_sql_varchar" />
            <cfprocparam type="IN" variable="BExclude"  value="#arguments.BExclude#"  cfsqltype="cf_sql_varchar" />
            <cfprocresult name="TempList" resultset="1"/>
        </cfstoredproc>
       ....  etc.....
      <CFSET filename = "Template_#dateformat(now(),'yyyymmdd')#.csv">
      <CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#filename#">
      <CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE"> 

Open in new window


If it works, a) wrap it in a cffuntion and add the cfarguments and b) VAR/Local scope all of the function local arguments.  Then try and invoke the function in the same script.

<!--- Hard coded arguments for TESTING only --->
<cfset arguments.id_list = "some test value here">
<cfset arguments.BExclude = "some test value here">

<!---- try and call the function --->
<cfset generate_Excel(argumentCollection=arguments)>

<cffunction name="generate_Excel" access="remote" output="false" returntype="string">
        <cfargument name="id_list" type="string" required="true" />
        <cfargument name="BExclude" type="string" required="true" />
        
         .... rest of the code here 
</cffunction>

Open in new window


If that works, a) remove the hard coded arguments and b) add in the cfcomponent tags. Then try and invoke the CFC remotely from a browser with sample values:

        http://yourserver/yourComponent.cfc?method=generate_Excel&id_list=someValue&BExclude=otherValue

If that succeeds, try it from jQuery (though I do not think this part will work).
0
 

Author Comment

by:jfreeman2010
ID: 41848348
HI _agx_ , thanks for the reply.  I did try copy all code from cfc and wrap it with cffunction, all what works - I also try it with cfdump to see the storeproc, and see the return data from DB.  what I am not see is the file download.
0
 

Author Comment

by:jfreeman2010
ID: 41848371
I did try copy all code from cfc - should be  I did try copy all code from cfm to cfc
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 450 total points
ID: 41848437
EDIT:   Did not see the last comment about CFM vs CFC before posting.

copy all code from cfc

Hi jfreeman2010.  No, not the .cfc.  You said you have a working cfm page.  Start with that code. Easier to start with something you know works, and test each step to see what breaks.

Honestly, I'm a little confused about exactly what code you're using now and how you're invoking the function (ie via browser url or jquery) :)  Just so we're not going round in circles, save the code below as a CFC. Then invoke it in your browser (NOT via jQuery).  If it works, then the problem is your current code.

        http://yourserver/path/yourComponent.cfc?method=generate_Excel&id_list=someValue&BExclude=otherValue

<cfcomponent>

    <cffunction name="generate_Excel" access="remote" returntype="void" hint="Generates and saves an excel and returns file path">
		<cfargument name="id_list" type="string" required="true" />
        <cfargument name="BExclude" type="string" required="true" />
		
		<!--- Temp file name MUST be globally unique name --->
		<cfset Local.outFile = "C:\temp\"& createUUID() &".csv">

		<!--- DEMO, simulate stored procedure call --->
		<cfset Local.yourQuery = queryNew("")>
		<cfset queryAddColumn(Local.yourQuery, "ID", [1,15,82,63])>
		<cfset queryAddColumn(Local.yourQuery, "Name", ["Smith","Alberts","Michaels","Davis"])>
		<cfset queryAddColumn(Local.yourQuery, "City", ["Hollywood","Chicago","Memphis","New York"])>

		<cfset Local.metaData = getMetaData(Local.yourQuery)>
		<cfset Local.csvRows = []>
		
		<!--- add headers --->
		<cfset Local.row = []>
        <cfloop array="#Local.metaData#" index="Local.col">
           <cfset arrayAppend(Local.row, '"#Local.col.name#"')>
        </cfloop>

		<cfset Local.line = arrayToList(Local.row, ',')>
		<cffile action="append" file="#local.outFile#" output="#Local.line#">

		<!--- add data --->
        <cfloop query="Local.yourQuery">
			<cfset Local.row = []>
			<cfloop array="#Local.metaData#" index="Local.col">
				<cfset arrayAppend(Local.row, '"#local.yourQuery[Local.col.name][Local.yourQuery.currentRow]#"')>
			</cfloop>

			<cfset Local.line = arrayToList(Local.row, ',')>
			<cfif len(local.line)>
				<cffile action="append" file="#local.outFile#" output="#local.line#">
			</cfif>

		</cfloop>
		<cffile action="append" file="#local.outFile#" output="#arrayToList(Local.row, ',')#">
		
		<CFSET Local.filename = "Template_#dateformat(now(),'yyyymmdd')#.csv">
		<CFHEADER  NAME="content-disposition" VALUE="attachment; filename=#Local.filename#">
		<CFCONTENT  TYPE="APPLICATION/MSEXCEL" FILE="#OUTFILE#" DELETEFILE="TRUE" RESET="TRUE"> 
	</cffunction>
	
</cfcomponent>

Open in new window

0
 

Author Comment

by:jfreeman2010
ID: 41848632
Hi _agx_,

your cfc works and did create a download file.  I can see is my cfm code have issue.  I will work on it.

Thank you very much for your help.
0
 

Author Closing Comment

by:jfreeman2010
ID: 41848639
Thank you very much for helping
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41848640
Okay, just for grins try calling the test CFC from $.ajax(...)?  I don't think it'll work, but that would at least confirm file downloads don't work out of the box.
0
 
LVL 25

Expert Comment

by:dgrafx
ID: 41848644
jfreeman - sorry I couldn't figure out a lot about what you were talking about ...

agx - thanks for translating ...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41850131
dgrafx - No problem. I was not completely sure either, but glad we were all able to figure things out..
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
This article discusses how to create an extensible mechanism for linked drop downs.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now