Hi, everyone. I hope someone can help me here. I've
looked through the archives, but didn't find a
solution that worked. I'm trying to create and
download an Excel file from a query. I'm using CF 5.0
on NT (SQL Server database) and do not have control
over the server and its settings.
Details: Sales reps can do queries of merchants. On
the search results page, the query is being saved to a
session variable (for use with the "Next" and
"Previous" buttons). A download link is to be
provided so the sales reps can save the results in an
Excel file. Given how many queries can be run, I
don't want to save the downloaded files on the server.
I also would ideally like to not have to re-run the
search query.
Options Tried:
1. CF_HTML2Excel. I have used this tag before
successfully, but unfortunately I do not have access
to the server and cannot install either Excel or the
Office Server Extensions on the machine.
2. CF_EXCEL. I have never tried this tag before. It
looks promising, but it requires that an SQL query be
run within it:
<CF_EXCEL Datasource="Datasource" Tableheadings="Comma
Delimited table headings" DataFields="Comma Delimited
data fields to output" SQLCOMMAND="SQL Statement">
and, given my desire to not re-run the query and all
the CFIF's in my query, I haven't bothered with making
the query straight-up SQL and trying out this tag.
3. <CFSET headerline="Merchant,Address 1,Address
2,City,State,Zip">
<CFSET CSVFileName="#filePath#search_results.xls">
<CFFILE ACTION="write" FILE="#CSVFileName#"
OUTPUT="#headerline#">
<CFSET CSVContent="">
<CFLOOP QUERY="session.MerchantResults">
<CFSET CSVRow="">
<CFLOOP INDEX="ii"
LIST="#session.MerchantResults.columnlist#">
<CFSET
CSVRow=ListAppend(CSVRow,evaluate("session.MerchantResults.#ii#[#session.MerchantResults.currentrow#]"))>
</CFLOOP>
<CFSET CSVContent=CSVContent & CSVRow & chr(13)>
</CFLOOP>
<CFFILE ACTION="append" FILE="#CSVFileName#"
OUTPUT="#CSVContent#" ADDNEWLINE="yes">
<CFHEADER NAME="Content-Disposition"
VALUE="inline;filename=#CSVFileName#">
<CFCONTENT TYPE="application/unknown"
FILE="#CSVFileName#" DELETEFILE="yes">
has the necessary data with the proper line breaks
between records, but each record is crammed into one
column. The data is hard to read and I can't seem to
break it out into the various columns.
4. <CFHEADER NAME="Content-Disposition"
VALUE="Inline;filename=search_results.xls">
<CFCONTENT TYPE="application/vnd.ms-excel">
<table border="1" cellspacing="1" cellpadding="2">
<tr>
<th>Merchant</th>
<th>Address 1</th>
<th>Address 2</th>
<th>City</th>
<th>State</th>
<th>Zip</th>
</tr>
<CFOUTPUT QUERY="session.MerchantResults">
<tr>
<td>#session.MerchantResults.merchant#</td>
<td>#session.MerchantResults.ex_add1#</td>
<td>#session.MerchantResults.ex_add2#</td>
<td>#session.MerchantResults.city#</td>
<td>#session.MerchantResults.st#</td>
<td>#session.MerchantResults.zip#</td>
</tr>
</CFOUTPUT>
</table>
<CFHEADER NAME="Content-Disposition"
VALUE="Inline;filename=search_results
xls">
<CFCONTENT TYPE="application/vnd.ms-excel">
looks like it has the potential of giving me a
nicely-formatted document, but instead is giving me a
blank, Excel-looking thing in my browser.
I officially plead ignorance on this. Any comments
welcome. I'm sure I've got a basic concept wrong here
somewhere. Thanks.
Kamie
=====
Kamie Curfman, Web Developer, FGM Inc.
45245 Business Court, Suite 400
Dulles, VA 20166
http://www.fgm.com
__________________________________________________
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
