[iamjesus1342027]

hi i am working on a button that when clicked will export data to an excel spreadsheet. i have been using a coworkers code as an example but it doesnt seem to be working so i am looking for more input. i have posted the code. also i have tested the url.reportkey variable and it is correctly storing the report key. i am fairly new to coldfusion and javascript so let me know if i am way off track or not. i know the problem is in the second function createExcel() some where. thanks in advance!

<script src="_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<script src="../../../_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<cfoutput>
	<cfparam name="url.reportkey" default="None">
	<div id="selectreport">
		<table align="left">
			<tr> 
				  <td>
					<button name="exportreport" style="font-weight:bold;" onclick="Excel()">Export Report to Excel</button>
 				</td>
			</tr>
		</table>
	</div>	
	<script>
		function Excel()
		{
			var reportkey = '#url.reportkey#';
			if(reportkey == null)
			{
				alert('not working');
				return;
			}
			alert(reportkey);
			<cftry>
				createExcel();
				<cfcatch type="any">
					<cfset local.name = 'dictionary.cfm'>
					<cfset sCurrent = #CFCATCH.TAGCONTEXT[1]#>
					<cflog text=" Error in #local.name# : #cfcatch.message# : Caught an exception, type = #CFCATCH.TYPE# : #CFCATCH.detail# : The error occurred near statement/line - #sCurrent['ID']# (#sCurrent['LINE']#,#sCurrent['COLUMN']#) #sCurrent['TEMPLATE']# " type="Error" file="customReports" date=now time=now>	
				</cfcatch>
			</cftry>
		}
		function createExcel()
		{
			<cfquery name="getReportData" datasource="#session.sysdb#">
				select *
				from DictionaryReport 
				where ReportKey = #reportkey#
				order by ReportKey
			</cfquery>
			<cfobject name="xls" component="_components/excel">
			<cfset sheets = ArrayNew(1)>
			<cfif isDefined("session.OutputDirectory")>
				  <cfset output = session.OutputDirectory>
			<cfelse>
 	 			<cfset output = "c:\temp\">
			</cfif>
			<cfset columnListArray = getMetaData(getReportData())>
			<cfset columnList = "">
			<cfset columnNames = "">
			<cfset columnNames = ListAppend(columnNames, 'Report Key')>
			<cfset columnNames = ListAppend(columnNames, 'Report Name')>
			<cfset columnNames = ListAppend(columnNames, 'Report Description')>
			<cfset columnNames = ListAppend(columnNames, 'Module')>
			<cfset columnNames = ListAppend(columnNames, 'Report Type')>
			<cfset columnNames = ListAppend(columnNames, 'Entity')>
			<cfset columnNames = ListAppend(columnNames, 'Version Added')>
			<cfset columnNames = ListAppend(columnNames, 'Visible')>
			<cfset columnNames = ListAppend(columnNames, 'Main Table')>
			<cfset columnNames = ListAppend(columnNames, 'Display Order')>
			<cfloop index="i" from="1" to="#arrayLen(columnListArray)#" >
				<cfset columnList = ListAppend(columnList, columnListArray[i].name)>
			</cfloop>
			<cfset sheets[1] = StructNew()>
			<cfset sheets[1].Query = getReportData()>
			<cfset sheets[1].ColumnList = columnList>
			<cfset sheets[1].ColumnNames = columnNames>
			<cfset sheets[1].SheetName = "Report Data">
			<cfset tempFileName = "ReportData_#RandRange(100,10000)#.xls">
			<cfset test = xls.WriteExcel('#output##tempFileName#','#sheets#',',')>
			<cfheader name="Pragma" value="">
			<cfheader name="Cache-control" value="">
			<CF_PROMPT4DOWNLOAD FILENAME="#tempFileName#" DIRECTORY="#output#">
		}
	</script>
</cfoutput>

[sansclue]

iamjesus1342027, on 31 Mar, 2009 - 06:04 AM, said:

i am fairly new to coldfusion and javascript so let me know if i am way off track or not. i know the problem is in the second function createExcel() some where. thanks in advance!

Yes, you are a bit off track. Javascript is client side and cannot run CF code. ColdFusion runs on the server and Javascript runs in the browser. Neither one knows anything about the other.

To execute any server side code from javascript, you need to submit another request to the server. You could create another page, say "createExcel.cfm" and then request that page from javascript.

[iamjesus1342027]

ok well its starting to make some sense now. i guess i shouldn't have trusted my coworkers code. lol. my new question is could i just take the coldfusion code in the function createExcel() and have it run in a page load event on a new page???

sansclue, on 31 Mar, 2009 - 06:24 AM, said:

iamjesus1342027, on 31 Mar, 2009 - 06:04 AM, said:

i am fairly new to coldfusion and javascript so let me know if i am way off track or not. i know the problem is in the second function createExcel() some where. thanks in advance!

Yes, you are a bit off track. Javascript is client side and cannot run CF code. ColdFusion runs on the server and Javascript runs in the browser. Neither one knows anything about the other.

To execute any server side code from javascript, you need to submit another request to the server. You could create another page, say "createExcel.cfm" and then request that page from javascript.

[iamjesus1342027]

i also have this page as part of my exporting project. this page has a button that gets the report key from the database and stores it in a variable. the button event then opens a new window (that has the previous code on it) and passes the report key to it using url.report key.

<script src="_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<script src="../../../_scripts/dictionary.js" type="text/javascript" language="javascript"></script>
<cfoutput>
	
<script>
	function getExcel(){
		var reportkey = document.getElementById('reporttypeselect').value;
		if(reportkey == '')
		{ 
			alert('you must select a report');
			return;
		}
		alert(reportkey);
		window.open('excelAction.cfm?reportkey=' + reportkey);
	}
</script>
	
	<!---Determine Edit Permissions. --->
	<cfif isDefined("session.report.permission.Admin.Management.User.Dictionary")>
			<cfset onchangeaction = "selectReport(this);">
		<cfelse>
			<cfset onchangeaction = "">
	</cfif>

	<cfquery name="qryReports" datasource="#session.sysdb#">
		SELECT ReportKey, ReportName, ReportDescription, [Module], ReportType, Entity
		FROM DictionaryReport
		<cfif not isDefined("session.report.permission.Admin.Management.User.Dictionary")>
			WHERE Visible = <cfqueryparam value="1" cfsqltype="bit">
		</cfif>
		ORDER BY DisplayOrder, ReportName
	</cfquery>
	
	<div id="selectreport">
		<table align="center">
			<tr>
 				<td colspan="2" align="center" style="font-weight:bold;font-size:16px;">Data Dictionary</td>
			</tr>
		</table>
		<table align="left">
			<tr>
				  <td style="font-weight:bold;font-size:14px;" nowrap><label class='reportlabel'>Select a Report Type to Export to Excel:</label></td>
				  <td><select name="reporttypeselect" id="reporttypeselect" onchange="#onchangeaction#" style="width:300px;">
					  <option value="">Choose A Report</option>
					  <cfloop query="qryReports">
		   				<option value="#qryReports.ReportKey#">#qryReports.Module# #qryReports.ReportName#</option>
		   			</cfloop>
	   				</select>
					<button name="exportreport" style="font-weight:bold;" onclick="getExcel()">Export Report to Excel</button>
 				</td>
			</tr>
		</table>
	</div>
</cfoutput>

[sansclue]

sansclue, on 31 Mar, 2009 - 06:24 AM, said:

i guess i shouldn't have trusted my coworkers code. lol. my new question is could i just take the coldfusion code in the function createExcel() and have it run in a page load event on a new page???

Sure, assuming you generate the right date and headers. I assume that is part of what this custom tag is doing:
<CF_PROMPT4DOWNLOAD FILENAME="#tempFileName#" DIRECTORY="#output#">.

As far as your coworker's code, I couldn't say. Maybe they were using <cfscript> instead of <script> tags, or possibly using this code on page load, .. which would make more sense.

[iamjesus1342027]

thanks a lot for your help so far. im getting it sorted out slowly. one question though. an easy one too. how do you set up the page load even in coldfusion?

sansclue, on 31 Mar, 2009 - 09:45 AM, said:

sansclue, on 31 Mar, 2009 - 06:24 AM, said:

i guess i shouldn't have trusted my coworkers code. lol. my new question is could i just take the coldfusion code in the function createExcel() and have it run in a page load event on a new page???

Sure, assuming you generate the right date and headers. I assume that is part of what this custom tag is doing:
<CF_PROMPT4DOWNLOAD FILENAME="#tempFileName#" DIRECTORY="#output#">.

As far as your coworker's code, I couldn't say. Maybe they were using <cfscript> instead of <script> tags, or possibly using this code on page load, .. which would make more sense.

[sansclue]

Quote

thanks a lot for your help so far. im getting it sorted out slowly. one question though. an easy one too. how do you set up the page load even in coldfusion?

Assuming this is a cfm separate page, whose only job is to generate reports for download, you don't really need to do anything special. Just run your db queries, then generate the headers and output the data. The mime headers will take care of the rest. They basically tell the browser "Here some Excel-like data". If the browser is configured to recognize Excel files (most are), it will either open the file or prompt to save it to disk.

Excel example, at the bottom of this page
http://livedocs.adob...cs/00000232.htm

[iamjesus1342027]

alright i have been out on the web reading all kinds of things and have some new code. i got the code from http://www.cftopper....Id/1/m/4/y/2007 and would like to give him credit. i have made some changes to the code to try to get my specific data but no major changes. unfortunately the code is not working. when i load the page nothing happens at all. any and all suggestions are welcome. Thanks!!!

<cffunction name="generateExcel" output="Yes">
<cfargument name="theQuery" type="query">
<cfargument name="columnList" type="string" default="">
<cfargument name="mode" type="string" default="save">
<cfargument name="theFilename" type="string" default="report.xls">

<cfset var TAB = chr(9)>
<cfset var i = 1>
<cfset var headerArray = ArrayNew(1)>

<cfparam name="url.reportkey" default="None">
<cfsetting enablecfoutputonly="Yes">

<!-- Dump all content before this fn call -->
<cfcontent reset="yes">

<!-- Set up the columnList -->
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Key')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Name')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Description')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Module')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Report Type')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Entity')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Version Added')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Visible')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Main Table')>
<cfset ARGUMENTS.columnList = ListAppend(ARGUMENTS.columnList, 'Display Order')>

<!-- Gets the data -->
<cfquery name="theQuery" datasource="#session.sysdb#">
	select *
	from DictionaryReport 
	where ReportKey = '#url.reportkey#'
	order by ReportKey
</cfquery>

<!-- Set up the file and location for spreadsheet to be saved in -->
<cfset guid = CreateUUID()>
<cfset location = "c:\temp\">
<cfset filename = location & "ReportData_" & guid & ".xls">
<cfset REQUEST.headerDoneFlag = true>
<cfheader name="Content-type" value="application/octet-stream"> 
<cfheader name="Content-Disposition" value="attachment;filename=""#ARGUMENTS.theFilename#""">
<cfsavecontent variable="output">
	<cfset generateExcel(ARGUMENTS.theQuery, ARGUMENTS.columnList, "save")>
</cfsavecontent><cfoutput>#output#</cfoutput><cfabort>
<cffile action="WRITE" file="#filename#" output="#output#">
<cfcontent type="application/msexcel" file="#filename#" deleteFile="Yes">
	
<!-- Write the header row -->
<cfloop index="i" from="1" to="#ListLen(ARGUEMENTS.columnList)#">
	<cfset item = Trim(ListGetAt(ARGUEMENTS.columnList, i))>
	<cfset headerArray[ArrayLen(headerArray )+1] = item>
	<cfoutput>#item##TAB#</cfoutput>
</cfloop>
<cfoutput>#chr(13)#</cfoutput>

<!-- Write all the sub rows --->
<cfloop query="ARGUMENTS.theQuery">
	<cfloop index="i" from="1" to="#ArrayLen(headerArray)#">
		<cfset dbVal = Trim(ARGUMENTS.theQuery[headerArray[i]][CurrentRow])>
		<cfif IsDate(dbVal)>
			<cfset dbVal = DateFormat(dbVal, "mm/dd/yyyy")>
		<cfelse>
			<cfset dbVal = replaceList(dbVal,"#TAB#,#chr(10)#,#chr(13)#"," , , ")>
		</cfif>
		<cfoutput>#dbVal##TAB#</cfoutput>
	</cfloop>
	<cfoutput>#chr(13)#</cfoutput>
</cfloop>

<cfsetting enablecfoutputonly="No">
</cffunction>

[sansclue]

Can I ask, are you just trying to create a CSV or tab delimited file for download?

[iamjesus1342027]

well i need the data to look nice once it is downloaded so it can be printed out and used. but a tab delimited file would work great cause i dont really need it to be in excel. the data isnt gonna be much more than a list so there really isnt any need for it to be an excel file. i wont be running any kind of analysis on it or anything.

sansclue, on 1 Apr, 2009 - 11:52 AM, said:

Can I ask, are you just trying to create a CSV or tab delimited file for download?

[sansclue]

iamjesus1342027, on 1 Apr, 2009 - 11:59 AM, said:

well i need the data to look nice once it is downloaded so it can be printed out and used. but a tab delimited file would work great cause i dont really need it to be in excel. the data isnt gonna be much more than a list so there really isnt any need for it to be an excel file. i wont be running any kind of analysis on it or anything.

Well .. personally I would try generating a basic tab delimited file on my own first. So I first understood how to generate excel like files on the fly. Once I understood that, then I would try and make it look nice .. or use a pre-made component by someone else. It is much harder to work with someone else's code (as good as it might be) when you don't understand the underlying code.

[iamjesus1342027]

alright im gonna try that. sounds like a good idea and then maybe i can tackle the bigger fish. thanks for all your input.

[iamjesus1342027]

alright. i have figured out to make a csv file and have some new code for an excel file. this code almost works. when the page loads a window pops up asking if you want to save or open the excel file. the problem occurs when you click either of these options. when you do an error message pops up saying you cannot do that because the file does not exist, the file is being used by another program, or a file with the same name already exists. i am not sure what to do about this error. i have noticed that the name of the file that is trying to be opened is the same as the url for the page. i dont think this is right and i specify a file name in my code but apparently it is being recognized. any ideas or suggestions would be greatly appreciated. thanks!!!

<!-- use cfsetting to block output of HTML 
outside of cfoutput tags -->
<cfsetting enablecfoutputonly="Yes">
<cfparam name="url.reportkey" default="None">
<cfsetting showdebugoutput="no"> 

<!-- get employee info -->
<cfquery name="getReport" datasource="#session.sysdb#">
  SELECT * 
FROM DictionaryReport
</cfquery>

<!-- set vars for special chars -->
<cfset TabChar = Chr(9)>
<cfset NewLine = Chr(13) & Chr(10)>
<!-- set content type to invoke Excel -->
<cfcontent type="application/vnd.ms-excel">

<!-- suggest default name for XLS file -->
<!-- use "Content-Disposition" in cfheader for Internet Explorer  -->
<cfheader name="Content-Disposition" value="filename=Report_Data.xls">

 <!-- output data using cfloop & cfoutput -->
<cfloop query="getReport">
  <cfoutput>#ReportKey##TabChar##ReportName#
  #TabChar##ReportDescription##TabChar##Module##NewLine#</cfoutput>
</cfloop>

[sansclue]

iamjesus1342027, on 6 Apr, 2009 - 05:23 AM, said:

<cfoutput>
<cfheader name="Content-Disposition" value="attachment; filename=Report_Data.xls">
<cfcontent type="application/vnd.ms-excel">
<cfloop query="getReport">
  #ReportKey##TabChar##ReportName##TabChar##ReportDescription##TabChar##Module##NewLine#
</cfloop>
</cfoutput>

I do not receive any errors with the code. But you should add either "attachment" or "inline" to the Content-Disposition.

[iamjesus1342027]

actually i just tested the code in firefox and it worked fine so its obviously something with the ie7 configuration. thanks though.