Opening Query Results in Excel

[scripter73]

Hi,

I have a query that returns my resultset in the Excel application. The only problem is every data element is put on another row instead of in another column of the same row for every currentrow.

<cfquery name="get_custs" datasource="customers" maxrows=20>

select 
	custid,
	contactid,
	custname_contact,
	firstname,
	lastname,
	title,
	workphone,
	address,
	email,
	city,
	state,
	zip
from contacts_cust

</cfquery>

<!--- Open Data in Excel --->

<cfif get_custs.recordcount gt 0>

	<cfheader name="content-disposition" value="attachment;filename=testexcel#DateFormat(Now(),"YYYYMMDD")#.xls">
	<cfcontent type="application/msexcel" reset="yes">
	
<cfset ColumnArray = ListToArray(get_custs.columnlist)>

<cfoutput query="get_custs">
[COLOR=red]All of this should return on same Excel row[/color]
 #get_custs.currentrow# -- 
<cfloop index="i" from="1" 
   to="#ArrayLen(ColumnArray)#">
#evaluate("get_custs."&ColumnArray[i])#
</cfloop><br />
</cfoutput>
	
</cfif>

Can anyone notice what I'm doing wrong? Thanks in advance for any suggestions.


Change Your Thinking, Change Your Life.

 

[r937]

does not compute

you have 20 rows of query results (see MAXROWS=20)

why you want all of them on one excel row?

remove the <br />

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[scripter73]

Actually I want the results of one row (all of the fields) in an Excel row. Each field for every query result row shows on a different row in Excel, like:

query result1.field1
field2....
fieldn....
query result2.field1
field2....
fieldn....

And so on.

Thanks for any advice.
scripter73




Change Your Thinking, Change Your Life.

 

[r937]

i don't see how that's possible, unless of course the line breaks -- not the <br /> tag, but the line breaks -- in the cfm source are translating into new lines in your output

so, write the <cfoutput>...<cfloop>...</cfloop>...</cfoutput> all on one line



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[scripter73]

Hi r937,

That worked. Strange. So it transfers the spaces and returns in the Excel?

Thanks for all of your help.

scripter73


Change Your Thinking, Change Your Life.

 

[philhege]

CFCONTENT is a literal output of what follows, carriage returns included. If the MIME app recognizes and deals with CRs and line feeds, what you see is indeed what you get.

HTH,

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'm not as think as you confused I am.
-----------
Flabbergasted (a.): Amazed at how much weight one has gained.
-----------
Oyster (n.): One who sprinkles their coversation with Yiddish expressions.