I'm a bit confused: are you writing to an Excel spreadsheet server side,
or sending it to the client?
To send to the client, it's really easy:
<cfquery name="qry">
some_sql_here
</cfquery>
<CFHEADER NAME="Content-Disposition" VALUE="inline;
filename=myspreadsheet.xls">
<cfcontent type="application/vnd.ms-excel">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Untitled</title>
</head>
<body>
<table>
<tr>
<td>Column heading</td>
<td>Column heading 2</td>
<td>Column heading 3</td>
</tr>
<cfoutput query="qry">
<tr>
<td>#col1#</td>
<td>#col2#</td>
<td>#col3#</td>
</tr>
</cfoutput>
</table>
</body>
</html>
Since you're sending Excel an HTML table, you can format the table as
you wish, and it will appear that way in Excel. I've written a couple
of different custom tags to handle this.
If you're just writing to an Excel spreadsheet server-side, you have a
couple of options. You can do things with the Excel COM object
(http://www.cfcomet.com/cfcomet/excel/) The easiest way would be to do
what you're trying... use the spreadsheet with ODBC. It's just like
other formats (SQL Server, Access, etc) with one minor change. Here's
what a query would look like:
select *
from "worksheetname$"
You either have to reference a "table" (worksheet in Excel) as above, or
set up a named range in Excel (which syntactically is referenced like a
regular table). (This syntax should work for updating, inserting, and
deleting as well)
--
Billy Cravens
HR Web Development, Sabre
[EMAIL PROTECTED]
Gothica Creative wrote:
>
> hey all,
>
> we need to write out some excel files based on different query
> results & for some reason (i think its cf 4.5) the custom tag we
> WERE using no longer works.
>
> all of the new tags I can find seem to require excel on the server
> which to me sounds like a bad idea.
>
> SO, for the time being, I'm trying to write directly to an xls file
> as a datasource. seems possible, I mean, cfadministrator will see &
> verify the thing right?
>
> yeah right.
>
> no matter what I do, I cant' get any tables to show up (which I assumed
> would be the "sheet" names)
>
> here's what I THOUGHT:
>
> sql = excel
> datasource = datasource
> table = sheet
> column = column
>
> any help here would be appreciated.
>
> thanks,
> kyle dubben
>
> --
> Kyle Dubben
> Gothica Creative, Inc.
> 1801 Laws Street
> Dallas, TX 75202
> 214.720.0884 ph
> 214.303.0698 fax
>
> -------------------------------------------------------------------------
> This email server is running an evaluation copy of the MailShield anti-
> spam software. Please contact your email administrator if you have any
> questions about this message. MailShield product info: www.mailshield.com
>
> -----------------------------------------------
> To post, send email to [EMAIL PROTECTED]
> To subscribe / unsubscribe: http://www.dfwcfug.org
-------------------------------------------------------------------------
This email server is running an evaluation copy of the MailShield anti-
spam software. Please contact your email administrator if you have any
questions about this message. MailShield product info: www.mailshield.com
-----------------------------------------------
To post, send email to [EMAIL PROTECTED]
To subscribe / unsubscribe: http://www.dfwcfug.org
