• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

coldfusion, jQuery help

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
jfreeman2010
Asked:
jfreeman2010
  • 13
  • 7
  • 5
2 Solutions
 
jfreeman2010Author Commented:
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
 
jfreeman2010Author Commented:
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
 
jfreeman2010Author Commented:
My jQuery got the resp back like this:
Template_20161014.csv


I can't find the file in my local machine
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
dgrafxCommented:
it's not in C:\temp ?
try changing the path to being under your web root
0
 
jfreeman2010Author Commented:
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
 
dgrafxCommented:
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
 
jfreeman2010Author Commented:
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
 
dgrafxCommented:
Are you saying that you want the file to be downloaded to the user's computer on their C drive?
0
 
jfreeman2010Author Commented:
yes, when below code in cfm were able to create excel file and downloaded in user locate c: drive:
0
 
jfreeman2010Author Commented:
<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
 
dgrafxCommented:
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
 
jfreeman2010Author Commented:
I thing I get what you said.  Why the code works in cfm file, but not cfc ?
0
 
dgrafxCommented:
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
 
_agx_Commented:
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
 
dgrafxCommented:
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
 
jfreeman2010Author Commented:
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
 
_agx_Commented:
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
 
jfreeman2010Author Commented:
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
 
jfreeman2010Author Commented:
I did try copy all code from cfc - should be  I did try copy all code from cfm to cfc
0
 
_agx_Commented:
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
 
jfreeman2010Author Commented:
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
 
jfreeman2010Author Commented:
Thank you very much for helping
0
 
_agx_Commented:
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
 
dgrafxCommented:
jfreeman - sorry I couldn't figure out a lot about what you were talking about ...

agx - thanks for translating ...
0
 
_agx_Commented:
dgrafx - No problem. I was not completely sure either, but glad we were all able to figure things out..
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 13
  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now