exporting from sql database to excel spreadsheet

  • (2 Pages)
  • +
  • 1
  • 2

24 Replies - 12918 Views - Last Post: 09 April 2009 - 10:17 AM Rate Topic: -----

#1 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

exporting from sql database to excel spreadsheet

Post icon  Posted 31 March 2009 - 07:04 AM

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>



Is This A Good Question/Topic? 0
  • +

Replies To: exporting from sql database to excel spreadsheet

#2 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 07:24 AM

View Postiamjesus1342027, 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.
Was This Post Helpful? 0
  • +
  • -

#3 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 08:46 AM

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???



View Postsansclue, on 31 Mar, 2009 - 06:24 AM, said:

View Postiamjesus1342027, 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.

Was This Post Helpful? 0
  • +
  • -

#4 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 10:28 AM

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>



Was This Post Helpful? 0
  • +
  • -

#5 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 10:45 AM

View Postsansclue, 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.
Was This Post Helpful? 0
  • +
  • -

#6 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 10:54 AM

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?



View Postsansclue, on 31 Mar, 2009 - 09:45 AM, said:

View Postsansclue, 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.

Was This Post Helpful? 0
  • +
  • -

#7 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 31 March 2009 - 11:14 AM

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
Was This Post Helpful? 1
  • +
  • -

#8 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 01 April 2009 - 12:23 PM

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>



Was This Post Helpful? 0
  • +
  • -

#9 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 01 April 2009 - 12:52 PM

Can I ask, are you just trying to create a CSV or tab delimited file for download?
Was This Post Helpful? 0
  • +
  • -

#10 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 01 April 2009 - 12:59 PM

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.




View Postsansclue, 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?

Was This Post Helpful? 0
  • +
  • -

#11 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 01 April 2009 - 01:18 PM

View Postiamjesus1342027, 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.
Was This Post Helpful? 0
  • +
  • -

#12 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 01 April 2009 - 01:20 PM

alright im gonna try that. sounds like a good idea and then maybe i can tackle the bigger fish. thanks for all your input.
Was This Post Helpful? 0
  • +
  • -

#13 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 06 April 2009 - 06:23 AM

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>



Was This Post Helpful? 0
  • +
  • -

#14 sansclue  Icon User is offline

  • D.I.C Regular

Reputation: 29
  • View blog
  • Posts: 316
  • Joined: 21-November 07

Re: exporting from sql database to excel spreadsheet

Posted 06 April 2009 - 09:19 AM

View Postiamjesus1342027, 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.
Was This Post Helpful? 0
  • +
  • -

#15 iamjesus1342027  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 41
  • Joined: 31-March 09

Re: exporting from sql database to excel spreadsheet

Posted 06 April 2009 - 09:22 AM

actually i just tested the code in firefox and it worked fine so its obviously something with the ie7 configuration. thanks though.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2