Need a query example for writing to Excel?

Posted on 2009-04-29
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

    there is a custom tag to do this
    LVL 1

    Author Comment

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

    Assisted Solution

    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):

    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...

    LVL 36

    Expert Comment

    "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

    also - Wrapper methods to access Apache's POI HSSF java classes to read, write and edit Excel files.

    and - 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
    LVL 1

    Accepted Solution


    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>

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Network it in WD Red

    There's an industry-leading WD Red drive for every compatible NAS system to help fulfill your data storage needs. With drives up to 8TB, WD Red offers a wide array of solutions for customers looking to build the biggest, best-performing NAS storage solution.  

    Suggested Solutions

    A web service ( is a software related technology that facilitates machine-to-machine interaction over a network. This article helps beginners in creating and consuming a web service using the ColdFusion Ma…
    If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now