Forums
This topic is locked
Using cfcontent to output to excel
Posted 13 Feb 2003 20:14:09
1
has voted
13 Feb 2003 20:14:09 Mae Dozier posted:
I've received no replied to an earlier post so I'm trying another solution. Does anyone know how I can use cfcontent with input variables to output results to excel. I have tested and can get output using cfcontent by querying everything in the DB. So, now how do I use variables the user will enter along with cfcontent? Thanks! Replies
Replied 14 Feb 2003 00:52:46
14 Feb 2003 00:52:46 Dennis van Galen replied:
hold on here, where is your data ???
is it pure dbase tables ? in that case, looking at your other post you could check the action page like:
<pre id=code><font face=courier size=2 id=code>
<cfif form.outputtype EQ "Excel">
<cflocation template="excelformat.cfm" addtoken="no">
<!--- optional cfelseif outputtype EQ Word here --->
</cfif>
<cfquery get your data>
<table settings here>
<cfoutput above recordset>
<tr class="mystyle">
<td>label or #aboverecordset.field</td>
<td> etc for more fields</td>
</tr>
</cfoutput>
</table>
</font id=code></pre id=code>
on the excelformat.cfm your would do the query and use cfcontent types to activate the browsers excel plugin
you can even add word format and others by adding a <cfelseif> and the other checks before the </if>:
www.cfcomet.com/
great articles on using CF with office applications.
didn't see your post earlier but i hope that helps alittle.
Oh and another thing, here's an example page from WACK MX about cfcontent, you can *cough* download this chapter and others for free and *cough* read more about wack mx on www.forta.com <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
great book, a bit thick though 1500 pages.
here's the example:
<pre id=code><font face=courier size=2 id=code>
<!---
Filename: FilmsToExcel.cfm
Author: Nate Weiss (NMW)
Purpose: Outputs film information for Microsoft Excel
--->
<!--- Don’t output anything *not* in CFOUTPUT tags --->
<!--- This makes it easier to deal with whitespace --->
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<!--- Retrieve information about films --->
<CFQUERY DATASOURCE="ows" NAME="GetFilms">
SELECT MovieTitle, AmountBudgeted
FROM Films
ORDER BY MovieTitle
</CFQUERY>
<!--- Set variables for special characters --->
<CFSET TabChar = Chr(9)>
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Suggest default filename for spreadsheet --->
<CFHEADER NAME="Content-Disposition" VALUE="filename=FilmBudgets.xls">
<!--- Set the content-type so Excel is invoked --->
<CFCONTENT TYPE="application/msexcel">
<!--- Output the header row, with column names --->
<!--- Put tab between columns, and newline at end --->
<CFOUTPUT>MOVIE TITLE#TabChar#BUDGET#NewLine#</CFOUTPUT>
<!--- Output actual data rows, each on own line --->
<!--- Put tab between columns, and newline at end --->
<CFLOOP QUERY="GetFilms">
<CFOUTPUT>#MovieTitle##TabChar##AmountBudgeted##NewLine#</CFOUTPUT>
</CFLOOP>
</font id=code></pre id=code>
HTH.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Edited: i can't type people.
Edited: i never use tokens with cflocation, if user bookmarks cftoken and cfid parameters than later when they return they get a session timed out error.
Edited by - djvgalen on 14 Feb 2003 09:41:30
is it pure dbase tables ? in that case, looking at your other post you could check the action page like:
<pre id=code><font face=courier size=2 id=code>
<cfif form.outputtype EQ "Excel">
<cflocation template="excelformat.cfm" addtoken="no">
<!--- optional cfelseif outputtype EQ Word here --->
</cfif>
<cfquery get your data>
<table settings here>
<cfoutput above recordset>
<tr class="mystyle">
<td>label or #aboverecordset.field</td>
<td> etc for more fields</td>
</tr>
</cfoutput>
</table>
</font id=code></pre id=code>
on the excelformat.cfm your would do the query and use cfcontent types to activate the browsers excel plugin
you can even add word format and others by adding a <cfelseif> and the other checks before the </if>:
www.cfcomet.com/
great articles on using CF with office applications.
didn't see your post earlier but i hope that helps alittle.
Oh and another thing, here's an example page from WACK MX about cfcontent, you can *cough* download this chapter and others for free and *cough* read more about wack mx on www.forta.com <img src=../images/dmxzone/forum/icon_smile_wink.gif border=0 align=middle>
great book, a bit thick though 1500 pages.
here's the example:
<pre id=code><font face=courier size=2 id=code>
<!---
Filename: FilmsToExcel.cfm
Author: Nate Weiss (NMW)
Purpose: Outputs film information for Microsoft Excel
--->
<!--- Don’t output anything *not* in CFOUTPUT tags --->
<!--- This makes it easier to deal with whitespace --->
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<!--- Retrieve information about films --->
<CFQUERY DATASOURCE="ows" NAME="GetFilms">
SELECT MovieTitle, AmountBudgeted
FROM Films
ORDER BY MovieTitle
</CFQUERY>
<!--- Set variables for special characters --->
<CFSET TabChar = Chr(9)>
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Suggest default filename for spreadsheet --->
<CFHEADER NAME="Content-Disposition" VALUE="filename=FilmBudgets.xls">
<!--- Set the content-type so Excel is invoked --->
<CFCONTENT TYPE="application/msexcel">
<!--- Output the header row, with column names --->
<!--- Put tab between columns, and newline at end --->
<CFOUTPUT>MOVIE TITLE#TabChar#BUDGET#NewLine#</CFOUTPUT>
<!--- Output actual data rows, each on own line --->
<!--- Put tab between columns, and newline at end --->
<CFLOOP QUERY="GetFilms">
<CFOUTPUT>#MovieTitle##TabChar##AmountBudgeted##NewLine#</CFOUTPUT>
</CFLOOP>
</font id=code></pre id=code>
HTH.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Edited: i can't type people.
Edited: i never use tokens with cflocation, if user bookmarks cftoken and cfid parameters than later when they return they get a session timed out error.
Edited by - djvgalen on 14 Feb 2003 09:41:30
Replied 18 Feb 2003 16:00:15
18 Feb 2003 16:00:15 Mae Dozier replied:
Thank you for you suggestion. I'm not sure I understand. The page has the field to enter the query parameter, then a list menu for the user to choose between HTML or Excel , and the submit / reset buttons. How do I incorporate your suggestion. (can you tell I'm new at this??!!)
Replied 18 Feb 2003 17:14:48
18 Feb 2003 17:14:48 Dennis van Galen replied:
Let's see...
Say we have a table called Invoices and your form has a field for the parameter called "Customer" and a listbox called outputtype. Then you set your form action to Post, like in the example below to provide the action page with form variables to check and act upon.
InvoiceForm.cfm page:
<pre id=code><font face=courier size=2 id=code>
<html>
<head><title>Form page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfform action="InvoiceAction.cfm" method="post">
Customer ID:<cfinput
name="Customer"
type="text"
required="yes"
message="You must enter a customer ID !!!"
validate="integer"><BR>
Output type:<select name="outputtype">
<option value="Excel">Excel
<option value="HTML" selected>HTML
</select>
<input name="submit" type="submit">
</cfform>
</body>
</html>
</font id=code></pre id=code>
InvoiceAction.cfm page:
<pre id=code><font face=courier size=2 id=code>
<!--- First check if user comes from form, if not then the form.submit will not exist --->
<cfif NOT IsDefined(form.submit)><H2>YOU HAVE NO BUSINESS HERE, USE THE FORM !</H2></cfif>
<!--- User comes from the form so continue processing --->
<!--- Check if user selected Excel format --->
<cfif form.outputtype EQ "Excel">
<!--- if user selected excel then redirect user to page that outputs in Excel format --->
<cflocation template="excelformat.cfm" addtoken="no">
</cfif>
<!--- User did not select Excel so we assume he wants HTML format --->
<!--- Start query to get data --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<html>
<head>
<title>Customer invoices</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<!--- Create table to nicely display data --->
<table>
<tr>
<!--- Display description for the columns --->
<td>Invoice</td>
<td>Amount</td>
</tr>
<!--- Start outputting the data in the HTML table --->
<!--- Note: Because the cfoutput has attribute query specified, and is located around the
tr tags, this will show all records that are returned. Call it a easy repeat region --->
<cfoutput query="getInvoices">
<tr>
<td>#InvoiceNR#</td>
<td>#InvoiceAmount#</td>
</tr>
<!--- End the data outputting proces --->
</cfoutput>
</table>
</body>
</html>
</font id=code></pre id=code>
All you need to do now is modify the example page I provided in my earlier message so that it does the proper query and outputs the proper data, this actionpage displays the html format. And because of the cflocation tag the user will never know he was redirected to a page called excelformat.cfm if he choose Excel.
Note that in the form page I preselected the HTML option.
If you're still stuck then send me your DB table and what you have so far, I will take a look. My email is
EDIT: I forgot the submit button <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>
So I added it and immediately use it to show how to check if a form variable exists and to prevent users from just browsing this page without using the form.
Also added some more comments for you.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Silly mistake, using CFINPUT for javascript validation only works in a cfform and i typed form... I am also still learning, scheduled to follow advanced class next week.
Edited by - djvgalen on 19 Feb 2003 00:34:09
Say we have a table called Invoices and your form has a field for the parameter called "Customer" and a listbox called outputtype. Then you set your form action to Post, like in the example below to provide the action page with form variables to check and act upon.
InvoiceForm.cfm page:
<pre id=code><font face=courier size=2 id=code>
<html>
<head><title>Form page</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<cfform action="InvoiceAction.cfm" method="post">
Customer ID:<cfinput
name="Customer"
type="text"
required="yes"
message="You must enter a customer ID !!!"
validate="integer"><BR>
Output type:<select name="outputtype">
<option value="Excel">Excel
<option value="HTML" selected>HTML
</select>
<input name="submit" type="submit">
</cfform>
</body>
</html>
</font id=code></pre id=code>
InvoiceAction.cfm page:
<pre id=code><font face=courier size=2 id=code>
<!--- First check if user comes from form, if not then the form.submit will not exist --->
<cfif NOT IsDefined(form.submit)><H2>YOU HAVE NO BUSINESS HERE, USE THE FORM !</H2></cfif>
<!--- User comes from the form so continue processing --->
<!--- Check if user selected Excel format --->
<cfif form.outputtype EQ "Excel">
<!--- if user selected excel then redirect user to page that outputs in Excel format --->
<cflocation template="excelformat.cfm" addtoken="no">
</cfif>
<!--- User did not select Excel so we assume he wants HTML format --->
<!--- Start query to get data --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<html>
<head>
<title>Customer invoices</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<!--- Create table to nicely display data --->
<table>
<tr>
<!--- Display description for the columns --->
<td>Invoice</td>
<td>Amount</td>
</tr>
<!--- Start outputting the data in the HTML table --->
<!--- Note: Because the cfoutput has attribute query specified, and is located around the
tr tags, this will show all records that are returned. Call it a easy repeat region --->
<cfoutput query="getInvoices">
<tr>
<td>#InvoiceNR#</td>
<td>#InvoiceAmount#</td>
</tr>
<!--- End the data outputting proces --->
</cfoutput>
</table>
</body>
</html>
</font id=code></pre id=code>
All you need to do now is modify the example page I provided in my earlier message so that it does the proper query and outputs the proper data, this actionpage displays the html format. And because of the cflocation tag the user will never know he was redirected to a page called excelformat.cfm if he choose Excel.
Note that in the form page I preselected the HTML option.
If you're still stuck then send me your DB table and what you have so far, I will take a look. My email is
EDIT: I forgot the submit button <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>
So I added it and immediately use it to show how to check if a form variable exists and to prevent users from just browsing this page without using the form.
Also added some more comments for you.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Silly mistake, using CFINPUT for javascript validation only works in a cfform and i typed form... I am also still learning, scheduled to follow advanced class next week.
Edited by - djvgalen on 19 Feb 2003 00:34:09
Replied 19 Feb 2003 00:09:32
19 Feb 2003 00:09:32 Dennis van Galen replied:
You could also use the cfcontent on the same page, sticking the whole routine in a condition, using above example:
<pre id=code><font face=courier size=2 id=code>
<!--- First check if user comes from form, if not then the form.submit will not exist --->
<cfif NOT IsDefined(form.submit)>
<H2>YOU HAVE NO BUSINESS HERE, USE THE FORM !</H2>
<!--- Stop processing NOW ! --->
<CFABORT>
</cfif>
<!--- User comes from the form so continue processing --->
<!--- Check if user selected Excel format --->
<cfif form.outputtype EQ "Excel">
<!--- if user selected excel then output the excel sheet --->
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<!--- Retrieve Customer invoices --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<!--- Set variables for special characters --->
<CFSET TabChar = Chr(9)>
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Suggest default filename for spreadsheet --->
<CFHEADER NAME="Content-Disposition" VALUE="filename=CustomerInvoices.xls">
<!--- Set the content-type so Excel is invoked --->
<CFCONTENT TYPE="application/msexcel">
<!--- Output the header row, with column names --->
<!--- Put tab between columns, and newline at end --->
<CFOUTPUT>Invoice Nr.#TabChar#InvoiceAmount#NewLine#</CFOUTPUT>
<!--- Output actual data rows, each on own line --->
<!--- Put tab between columns, and newline at end --->
<CFLOOP QUERY="getInvoices">
<CFOUTPUT>#InvoiceNR##TabChar##InvoiceAmount##NewLine#</CFOUTPUT>
</CFLOOP>
<CFELSEIF>
<!--- User did not select Excel so we assume he wants HTML format --->
<!--- Start query to get data --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<html>
<head>
<title>Customer invoices</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<!--- Create table to nicely display data --->
<table>
<tr>
<!--- Display description for the columns --->
<td>Invoice</td>
<td>Amount</td>
</tr>
<!--- Start outputting the data in the HTML table --->
<!--- Note: Because the cfoutput has attribute query specified, and is located
around the tr tags, this will show all records that are returned. Call it a easy
repeat region --->
<cfoutput query="getInvoices">
<tr>
<td>#InvoiceNR#</td>
<td>#InvoiceAmount#</td>
</tr>
<!--- End the data outputting proces --->
</cfoutput>
</table>
</body>
</html>
</cfif>
</font id=code></pre id=code>
Note that you can change this:
<CFHEADER NAME="Content-Disposition" VALUE="filename=CustomerInvoices.xls">
to whatever you like to be shown when a user saves the excelsheet, simply by inserting data from a database or other source, for example:
<CFHEADER NAME="Content-Disposition" VALUE="filename=Customer#FORM.Customer#on#NOW()#.xls">
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Edited by - djvgalen on 19 Feb 2003 00:24:09
<pre id=code><font face=courier size=2 id=code>
<!--- First check if user comes from form, if not then the form.submit will not exist --->
<cfif NOT IsDefined(form.submit)>
<H2>YOU HAVE NO BUSINESS HERE, USE THE FORM !</H2>
<!--- Stop processing NOW ! --->
<CFABORT>
</cfif>
<!--- User comes from the form so continue processing --->
<!--- Check if user selected Excel format --->
<cfif form.outputtype EQ "Excel">
<!--- if user selected excel then output the excel sheet --->
<CFSETTING ENABLECFOUTPUTONLY="Yes">
<!--- Retrieve Customer invoices --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<!--- Set variables for special characters --->
<CFSET TabChar = Chr(9)>
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Suggest default filename for spreadsheet --->
<CFHEADER NAME="Content-Disposition" VALUE="filename=CustomerInvoices.xls">
<!--- Set the content-type so Excel is invoked --->
<CFCONTENT TYPE="application/msexcel">
<!--- Output the header row, with column names --->
<!--- Put tab between columns, and newline at end --->
<CFOUTPUT>Invoice Nr.#TabChar#InvoiceAmount#NewLine#</CFOUTPUT>
<!--- Output actual data rows, each on own line --->
<!--- Put tab between columns, and newline at end --->
<CFLOOP QUERY="getInvoices">
<CFOUTPUT>#InvoiceNR##TabChar##InvoiceAmount##NewLine#</CFOUTPUT>
</CFLOOP>
<CFELSEIF>
<!--- User did not select Excel so we assume he wants HTML format --->
<!--- Start query to get data --->
<cfquery name="getInvoices" datasource="myDatasource">
SELECT InvoiceNR, InvoiceAmount
FROM Invoices
<!--- Filter on the value entered in the FORM --->
WHERE CustomerID = #FORM.Customer#
Order by InvoiceNR
</cfquery>
<html>
<head>
<title>Customer invoices</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<!--- Create table to nicely display data --->
<table>
<tr>
<!--- Display description for the columns --->
<td>Invoice</td>
<td>Amount</td>
</tr>
<!--- Start outputting the data in the HTML table --->
<!--- Note: Because the cfoutput has attribute query specified, and is located
around the tr tags, this will show all records that are returned. Call it a easy
repeat region --->
<cfoutput query="getInvoices">
<tr>
<td>#InvoiceNR#</td>
<td>#InvoiceAmount#</td>
</tr>
<!--- End the data outputting proces --->
</cfoutput>
</table>
</body>
</html>
</cfif>
</font id=code></pre id=code>
Note that you can change this:
<CFHEADER NAME="Content-Disposition" VALUE="filename=CustomerInvoices.xls">
to whatever you like to be shown when a user saves the excelsheet, simply by inserting data from a database or other source, for example:
<CFHEADER NAME="Content-Disposition" VALUE="filename=Customer#FORM.Customer#on#NOW()#.xls">
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
Edited by - djvgalen on 19 Feb 2003 00:24:09
Replied 19 Feb 2003 16:34:46
19 Feb 2003 16:34:46 Mae Dozier replied:
I have given up trying to combine the functions of the HTML and/or Excel output and have separated the options. Now on my Excel page I am just trying to get the output to Excel based on the parameter enter by the user. My code in the page for Excel is: <cfparam name="url.docid" default="">
<cfquery name="rsDocid" datasource="prod8i2" username="prodmgr" password="a12345">
SELECT PRODMGR.PES_MASTER_ERROR_V.PES_DATE, PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT,
PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE, PRODMGR.PES_MASTER_ERROR_V.CLOSE_DATE,
PRODMGR.PES_MASTER_ERROR_V.PFER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCB, PRODMGR.PES_MASTER_ERROR_V.TEXT,
PRODMGR.PES_MASTER_ERROR_V.PUER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCRIPTION,
PRODMGR.PES_MASTER_ERROR_V.LINE_DESCRIPTION, PRODMGR.PES_MASTER_ERROR_V.PES_BATCH_ID,
PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID FROM PRODMGR.PES_MASTER_ERROR_V WHERE PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID
like 'url.docid'
</cfquery>
<cfcontent type="application/vnd.ms-excel">
<cfoutput query="rsDocid">
#PRODMGR.PES_MASTER_ERROR_V.PES_DATE# #PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT# #PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE#
</cfoutput>
<cfabort>
I only asked for 3 fields just to see if it worked. My form parameter is named docid. Excel opens now, but with no data. I've run the query in SQL+ and it works. Any ideas why the data isnt there.
<cfquery name="rsDocid" datasource="prod8i2" username="prodmgr" password="a12345">
SELECT PRODMGR.PES_MASTER_ERROR_V.PES_DATE, PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT,
PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE, PRODMGR.PES_MASTER_ERROR_V.CLOSE_DATE,
PRODMGR.PES_MASTER_ERROR_V.PFER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCB, PRODMGR.PES_MASTER_ERROR_V.TEXT,
PRODMGR.PES_MASTER_ERROR_V.PUER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCRIPTION,
PRODMGR.PES_MASTER_ERROR_V.LINE_DESCRIPTION, PRODMGR.PES_MASTER_ERROR_V.PES_BATCH_ID,
PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID FROM PRODMGR.PES_MASTER_ERROR_V WHERE PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID
like 'url.docid'
</cfquery>
<cfcontent type="application/vnd.ms-excel">
<cfoutput query="rsDocid">
#PRODMGR.PES_MASTER_ERROR_V.PES_DATE# #PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT# #PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE#
</cfoutput>
<cfabort>
I only asked for 3 fields just to see if it worked. My form parameter is named docid. Excel opens now, but with no data. I've run the query in SQL+ and it works. Any ideas why the data isnt there.
Replied 19 Feb 2003 17:27:11
19 Feb 2003 17:27:11 Dennis van Galen replied:
New code:
<pre id=code><font face=courier size=2 id=code>
<!--- Check for URL parameter docid, if not exists set default to "" --->
<!--- This could be the problem, you may need to use a default value --->
<cfparam name="url.docid" default="">
<!--- do the query on the Oracle? database --->
<cfquery name="rsDocid" datasource="prod8i2" username="prodmgr" password="a12345">
SELECT PRODMGR.PES_MASTER_ERROR_V.PES_DATE, PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT,
PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE, PRODMGR.PES_MASTER_ERROR_V.CLOSE_DATE,
PRODMGR.PES_MASTER_ERROR_V.PFER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCB, PRODMGR.PES_MASTER_ERROR_V.TEXT,
PRODMGR.PES_MASTER_ERROR_V.PUER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCRIPTION,
PRODMGR.PES_MASTER_ERROR_V.LINE_DESCRIPTION, PRODMGR.PES_MASTER_ERROR_V.PES_BATCH_ID,
PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID
FROM PRODMGR.PES_MASTER_ERROR_V
<!--- Filter records using URL parameter --->
<!--- If you want only one docid returned then use = instead of like --->
WHERE PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID like #url.docid#
</cfquery>
<!--- Define special characters for use in Excel plugin --->
<!--- Set the TAB character for seperating columns --->
<CFSET TabChar = Chr(9)>
<!--- Set the new line character for the next row --->
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Start output in Excel browser plugin --->
<cfcontent type="application/vnd.ms-excel">
<!--- Loop through the records --->
<cfloop query="rsDocid">
<cfoutput>
#PRODMGR.PES_MASTER_ERROR_V.PES_DATE##TabChar##PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT##TabChar##PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE##NewLine#
</cfoutput>
</cfloop>
<!--- End of page, there is no need to abort the processing at the end of a page --->
</font id=code></pre id=code>
This should work, I tried it on one of our databases with a slightly modified query and this works, just make sure you set a default value using the cfparam tag, i do not think there is any record that has a empty id.
If you do not want the tabs and newlines then it will be one big mess, all data in a single cell. I added further comments in code, where they apply.
Also try not to use the cfabort tag too often, if you use cflogin and your logged in user hits a cfabort tag then the user will also be logged out because of the abort.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5
<pre id=code><font face=courier size=2 id=code>
<!--- Check for URL parameter docid, if not exists set default to "" --->
<!--- This could be the problem, you may need to use a default value --->
<cfparam name="url.docid" default="">
<!--- do the query on the Oracle? database --->
<cfquery name="rsDocid" datasource="prod8i2" username="prodmgr" password="a12345">
SELECT PRODMGR.PES_MASTER_ERROR_V.PES_DATE, PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT,
PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE, PRODMGR.PES_MASTER_ERROR_V.CLOSE_DATE,
PRODMGR.PES_MASTER_ERROR_V.PFER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCB, PRODMGR.PES_MASTER_ERROR_V.TEXT,
PRODMGR.PES_MASTER_ERROR_V.PUER_CODE, PRODMGR.PES_MASTER_ERROR_V.DESCRIPTION,
PRODMGR.PES_MASTER_ERROR_V.LINE_DESCRIPTION, PRODMGR.PES_MASTER_ERROR_V.PES_BATCH_ID,
PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID
FROM PRODMGR.PES_MASTER_ERROR_V
<!--- Filter records using URL parameter --->
<!--- If you want only one docid returned then use = instead of like --->
WHERE PRODMGR.PES_MASTER_ERROR_V.DOCUMENT_ID like #url.docid#
</cfquery>
<!--- Define special characters for use in Excel plugin --->
<!--- Set the TAB character for seperating columns --->
<CFSET TabChar = Chr(9)>
<!--- Set the new line character for the next row --->
<CFSET NewLine = Chr(13) & Chr(10)>
<!--- Start output in Excel browser plugin --->
<cfcontent type="application/vnd.ms-excel">
<!--- Loop through the records --->
<cfloop query="rsDocid">
<cfoutput>
#PRODMGR.PES_MASTER_ERROR_V.PES_DATE##TabChar##PRODMGR.PES_MASTER_ERROR_V.ERROR_AMOUNT##TabChar##PRODMGR.PES_MASTER_ERROR_V.FINCEN_RECEIPT_DATE##NewLine#
</cfoutput>
</cfloop>
<!--- End of page, there is no need to abort the processing at the end of a page --->
</font id=code></pre id=code>
This should work, I tried it on one of our databases with a slightly modified query and this works, just make sure you set a default value using the cfparam tag, i do not think there is any record that has a empty id.
If you do not want the tabs and newlines then it will be one big mess, all data in a single cell. I added further comments in code, where they apply.
Also try not to use the cfabort tag too often, if you use cflogin and your logged in user hits a cfabort tag then the user will also be logged out because of the abort.
with regards,
Dennis van Galen
Webmaster KPN Telecom Holland
Financial & Information Services
Studio MX / CFMX PRO / SQL 2000 / NT4 AND win2kPRO / IIS5