Query2Excel

[hope.martin]

I am trying to utilize this UDF found on CFLIB.ORG. Have emailed the author,
but havn't heard yet and I am on a tight deadline (as usual. ; )) I hope it is
OK to post this here.

When I specify the query, like so #Query2Excel(getreport)#, "getreport" being
the name of my query. It works fine.

BUUUUT when I try to add the columns & headers so I can control the order,
like so #Query2Excel(getreport,colorder,colorder)#, I get an error saying IE
can't open the page. "Colorder" is the variable that contains a comma-delimited
list of column names in the "getreport" query.

What am I doing wrong?

Thank you!
Hope:confused;

[hope.martin]

Whoops! I see I forgot a bit of code in my first post. To save confusion,
here's the whole thing:

<cfinclude template="../Assets/query2excel.cfm">
<cfscript>
cols = "<cfoutput>#colorder#</cfoutput>";
heads = "<cfoutput>#colorder#</cfoutput>";
data = getreport;
</cfscript>
<CFHEADER NAME="Content-Disposition" VALUE="inline; filename=tracker.xls">

<cfcontent
type="application/msexcel"><cfoutput>#Query2Excel(data,heads,cols)#</cfoutput>

[hope.martin]

Woohoo! I almost have this solved. Thanks to much help from the UDF author!


The first part of the problem was that I was aliasing a column in my getreport
query. Removed the alias and it hung on a column that doesn't exist
(FISCAL_YEAR). But the getreport query contained an added query column called
"FY". That is the same name as a column in another table that is used to build
this report. When I changed the added QUERY column to "FISCAL_YEAR"... IT
WORKED!!! Not sure why but at this point as long as it works I don't care.

Now the only problem remaining is this: on one of the columns (WON), once it
gets to excel, an apostrophe appears before the value. That is to say if in the
database I have "200500050009" then once it gets to excel it becomes
"'200500050009". That's not good!

Help!

[hope.martin]

Yay! I finally have this working as I need it.

To Review:
1. aliased columns caused problems. when I removed the aliases, the UDF worked
fine
2. In MSExcel, numerical values greater than 10 digits will show as scientific
notation. To get around this the UDF added an apostrophe infront of the numbers
like so:

if (isNumeric(colValue) and Len(colValue) gt 10) {
colValue="'#colValue#";

I didn't want the apostrophe, so I added and equal sign and surrounded the
number with quotes like so:

if (isNumeric(colValue) and Len(colValue) gt 10) {
colValue="=""#colValue#""";

And excel treats it as a formula displaying it correctly without the added
apostrophe

I hope someone will find this helpful!

:D