Coldfusion - Export query data to excel sheet

Page 1 of 1

4 Replies - 16730 Views - Last Post: 03 November 2008 - 03:15 PM Rate Topic: -----

#1 manjusha.pate  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 15-October 08

Coldfusion - Export query data to excel sheet

Posted 15 October 2008 - 06:24 AM

Hi,

I am new to ColdFusion. Can anyone suggest me the correct way to implement export functionality in ColdFusion application.

Below is the code -

<cfsetting enablecfoutputonly="Yes">
<cfquery name="queryData" datasource="#request.dsn#">
SELECT * from EMPLOYEE
</cfquery>
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=test.xls">

<cfoutput>
<table border="2">
<tr>
<td> ID </td><td> Name </td>
</tr>
<cfloop query="queryData">
<tr>
<td>#id#</td><td>#name#</td> </tr>
</cfloop>
</table>
</cfoutput>

Problem is -
1) it doesn't appear as normal excel file. It looks like plain file.(doesn't appear lines of rows and column)
2) HTML contents of my index.cfm file gets uploaded in this excel file. How to avoid this HTML contents?

I want to export query result in column-row format in excel sheet.

Thanks in advance.

Regards,

Manjusha

Is This A Good Question/Topic? 0
  • +

Replies To: Coldfusion - Export query data to excel sheet

#2 nbrooks427  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 16-October 08

Re: Coldfusion - Export query data to excel sheet

Posted 16 October 2008 - 10:19 AM

View Postmanjusha.pate, on 15 Oct, 2008 - 06:24 AM, said:

Hi,

I am new to ColdFusion. Can anyone suggest me the correct way to implement export functionality in ColdFusion application.

Below is the code -

<cfsetting enablecfoutputonly="Yes">
<cfquery name="queryData" datasource="#request.dsn#">
SELECT * from EMPLOYEE
</cfquery>
<cfcontent type="application/msexcel">
<cfheader name="Content-Disposition" value="filename=test.xls">

<cfoutput>
<table border="2">
<tr>
<td> ID </td><td> Name </td>
</tr>
<cfloop query="queryData">
<tr>
<td>#id#</td><td>#name#</td> </tr>
</cfloop>
</table>
</cfoutput>

Problem is -
1) it doesn't appear as normal excel file. It looks like plain file.(doesn't appear lines of rows and column)
2) HTML contents of my index.cfm file gets uploaded in this excel file. How to avoid this HTML contents?

I want to export query result in column-row format in excel sheet.

Thanks in advance.

Regards,

Manjusha


Manjusha,

The only thing I see different in your approach from the one I've done is the cfloop

Here is the base of what I have:

<cfheader name="content-disposition" value="attachment; filename=AddressBook.xls">
<cfif NotIE>
	<cfcontent type="application/vnd.ms-excel">
<cfelse>
	<cfcontent type="text/html">
</cfif>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head></head>
<body>
	<table border="1">
		<tr><th align="left" colspan="7">Address Book</th></tr>
		<tr valign="bottom">
			<th align="left" class="report">Contact name</th>
			<th class="report">Initials</th>
			<th align="left" class="report">Project title</th>
			<th align="left" class="report">Address</th>
			<th align="left" class="report">Phone</th>
			<th align="left" class="report">Fax</th>
			<th align="left" class="report">Email</th>
		</tr>
		<cfoutput query="AddrInfo" group="corpName">
			<tr bgcolor="##cccccc"><th align="left" colspan="7" class="report">#corpName#</th></tr>
			<cfoutput>
				<tr valign="top">
					<td class="report">#Trim(fullName)#</td>
					<td align="center" class="report"><cfif Len(initials)>#Trim(initials)#<cfelse><em>N/A</em></cfif></td>
					<td class="report"><cfif Len(projTitle)>#Trim(projTitle)#<cfelse>&nbsp;</cfif></td>
					<td class="report"><cfif Len(address1) + Len(address2) + Len(city) + Len(state) + Len(zip) + Len(country)>
							<cfif Len(address1)>#Trim(address1)#</cfif>
							<cfif Len(address2)><br>#Trim(address2)#</cfif>
							<cfif Len(city)><br>#Trim(city)#</cfif><cfif Len(state)>,&nbsp;#Trim(state)#</cfif><cfif Len(zip)>&nbsp;&nbsp;#Trim(zip)#</cfif>
						<cfelse>&nbsp;</cfif>
					</td>
					<td class="report"><cfif Len(phone)>#Trim(phone)#<cfelse>&nbsp;</cfif></td>
					<td class="report"><cfif Len(fax)>#Trim(fax)#<cfelse>&nbsp;</cfif></td>
					<td class="report"><cfif Len(email)>#Trim(email)#<cfelse>&nbsp;</cfif></td>
				</tr>
			</cfoutput>
			<tr><td colspan="7" class="report">&nbsp;</td></tr>
		</cfoutput>
	</table>


This creates an excel file that has definate rows and columns
Was This Post Helpful? 0
  • +
  • -

#6 bruce779  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 18
  • Joined: 29-May 07

Re: Coldfusion - Export query data to excel sheet

Posted 30 October 2008 - 01:58 AM

Hi.
I use a really simple method for outputting to excel.

Hope it helps.

<cfset dsn = 'frontl_dba'>

<cfquery name="qReport" datasource="#dsn#">

	SELECT peo.id peo_id
	  , peo.forename fName
	  , peo.surname sName
	  , peo.dob dob
	  , SUBSTR(REPLACE(adr.address, CHR(13), ', '), 1, LENGTH(REPLACE(adr.address, CHR(13), ', ')) - 2) as address
	  , pid.actual_value actVal
	FROM cdh_people peo
	  , mad_addresses adr
	  , cdh_person_identifiers pid
	WHERE peo.id = pid.peo_id
	AND adr.adr_id = FUNC_RETURNADRID(peo.id)
	AND pid.sou_id = (SELECT sou.id FROM cdh_sources sou WHERE abbr = 'SCN')
	AND pid.end_date IS NULL
	AND peo.deceased_date IS NULL
	AND length(pid.actual_value) < 9
	ORDER BY sNAme, fName, dob

</cfquery>

<cfsavecontent variable="report">
<cfoutput>
	<table border="1">
		<thead align="center">
			<th>PEO_ID</th>
			<th>Surname</th>
			<th>Forename</th>
			<th>DOB</th>
			<th>Address</th>
			<th>SCN Number</th>
		</thead>
		<cfloop query="qReport">
		<tr align="left">
			<td>#peo_id#</td>
			<td>#sName#</td>
			<td>#fName#</td>
			<td>#dob#</td>
			<td>#address#</td>
			<td>#actVal#</td>
		</tr>
		</cfloop>
	</table>
</cfoutput>
</cfsavecontent>
<cffile action="write" file="C:\Reports\8_digit_SCN_#DateFormat(Now(),'ddmmyyyy')#_#LSTimeFormat(Now(),'HHMMSS')#.xls" output="#report#">


This outputs a bordered spreadsheet with the column headers emboldened.
Was This Post Helpful? 1

#7 AFProgMan  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 21-October 08

Re: Coldfusion - Export query data to excel sheet

Posted 03 November 2008 - 07:09 AM

CFLOOP will always slow down the run when there is more than just a few entries of data or records to export/import.
Was This Post Helpful? 0
  • +
  • -

#8 CR250  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 58
  • Joined: 14-June 08

Re: Coldfusion - Export query data to excel sheet

Posted 03 November 2008 - 03:15 PM

View Postbruce779, on 30 Oct, 2008 - 12:58 AM, said:

Hi.
I use a really simple method for outputting to excel.


We pretty much do what Bruce does 90% of the time.
But there is this that is better if you need more functionality but its take longer to code up.

http://www.bennadel....poi-utility.htm
Was This Post Helpful? 1
Page 1 of 1