Need a query example for writing to Excel?

Posted on 2009-04-29
Medium Priority
Last Modified: 2013-12-24

I need to query a db table and then write the recordset to an excel spreadsheet.
Can someone provide a query example of what the syntax is for writing to an excel spreadsheet.  I have set an ODBC connection up for the excel already.

Question by:g118481
LVL 19

Assisted Solution

erikTsomik earned 200 total points
ID: 24264697
there is a custom tag to do this

Author Comment

ID: 24264726
I am really just looking for a query model.
LVL 27

Assisted Solution

azadisaryev earned 200 total points
ID: 24266161
there is no specific query model to write to an excel file.
in fact, you can't 'write to an excel file' at all - all you can do is save the query output as a comma- or tab-delimited file (.csv), then open in in excel.

there is also a POIUtility, which will allow you to create more complicated xls files (i.e. with proper cell formatting, multiple worksheets, etc etc): http://www.bennadel.com/projects/poi-utility.htm

you could also output your query as plain html table, saving this output in a variable, then serve up this variable using a combination of cfheader and cfcontent tags with proper mime-type to 'pretend' the file is an .xls file - this way users will be prompted to download the file and it will be saved as .xls - but there are various pitfalls with this approach, depending on user's system, browser and which excel version they have installed...

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 36

Expert Comment

ID: 24266253
"can't 'write to an excel file' at all"

Azadi... I'm not sure that's entirely true...

can you not use an excel spreadsheet as a datasource for cfgrid and modify the live data ?? Pretty sure I've seen this done (tho not done it myself)

LVL 36

Assisted Solution

SidFishes earned 600 total points
ID: 24266260

http://cfexcelproxy.riaforge.org/ - Wrapper methods to access Apache's POI HSSF java classes to read, write and edit Excel files.


http://cfxl.riaforge.org/ - CFXL lets you quickly and easily create new spreadsheets and modify or read data from existing spreadsheets.

both leverage poi

you are right that there is no simple query model

Accepted Solution

g118481 earned 0 total points
ID: 24294863

I found this snippet of code on the web.  
It is working for me just fine.

Thanks for your time.

<cfset dsn="mytable">
<cfquery name="daily" datasource="#dsn#" dbtype="ODBC" >
    SELECT *
    FROM mytable
    <!--- make some variables to store the dirctory and file name. --->
    <cfset f_dir = "c:\reports\">
    <!--- I like to use the date and time for a file name, but you can name it anything you like by changing the value below. --->
    <cfset f_name = "report.csv">
    <!--- Lets make the file, and put the first row of Column headings in --->
    <cffile action="WRITE" file="#f_dir##f_name#"
    output="myfields" addnewline="Yes">
    <!--- Now lets loop over the RecordSet and fill in the data --->
    <cfloop query="daily">
    <cffile action="APPEND" file="#f_dir##f_name#"
    <!--- End the loop here --->
   <cfoutput> <a href="/reports/#f_name#">Here is the excel file</a></cfoutput>

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses
Course of the Month16 days, 3 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question