Skip to main content
T
TLC-IT
Inspiring
January 13, 2010
Question

Silly request, needed yesterday: "queries stored in table, to Excel"

  • January 13, 2010
  • 2 replies
  • 585 views

I have a fast-and-dirty requirement (CF8) which needs a fast-and-dirty solution ...  "hacks welcome."  I'd like to stumble-upon existing code that does as much of this work for me as possible.

I've got a table of queries.  (That is to say, one of the columns is a text column containing a known-good SQL statement.)  Those queries might contain a few parameters, and I've arranged for those to be identified in #some_name# syntax, within the text, just for simplicity  All of these parameters are, and always will be, "dates."

The application is internal-only and the user is trustworthy.  "Hijacking," injection and so forth is not a concern.

The user needs to be able to choose a query, enter the parameters needed, and poof, out pops a page in Microsoft Excel containing the query results.

I can see from a quick Google search that "this requirement has been done to death."  So, can anyone quickly point me to the closest thing they've stumbled-upon that matches these requirements?  Yes, I want to be lazy.

This topic has been closed for replies.

2 replies

T
TLC-ITAuthor
Inspiring
January 25, 2010

I found the following very satisfactory answer, and in fairly short order:  (Assumes Excel 2003 or better.)

  1. You can run "cfquery" no-problem getting the query from a string variable, if you use the function PreserveSingleQuotes() on the string.
  2. Next, by whatever means, construct an XML document as your output.  Excel defines a full-featured XML representation for workbooks, sheets, cell data, and formulas.  This is discussed in the Excel documentation.  The schemas are, e.g. "urn:schemas-microsoft-com:office:spreadsheet"
  3. Include this: <cfheader name="content-disposition" value="inline; filename=report_output.xls" />
  4. Now, use the <cfcontent> tag to output the XML data as type="application/msexcel"

Even though the file is an XML file, Excel understands it perfectly.

Now... how'd I find all this stuff out, so fast?  Yep!  "Google it!"    There are plenty of code-samples that illustrate the technique.  (Disregard any that don't use XML.  Ones that require special APIs are also unnecessary.)

D
Dan_Bracuk
Inspiring
January 14, 2010

On your form, have some text boxes or something that enables the user to enter dates in yyyy-mm-dd format.

When processing the form, replace, #some_name# with {d '#form.variable#'}

Run query

do a google search on coldfusion excel poi to find the cfc that makes it easy to export query results to excel and actually plays nicely with excel 2007.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices