Free Query to Excel Function
I'm working on extending a system with excel reports so I knocked up a handy function that will turn any Query into an excel view.Download
Syntax
The function generateQuery takes 4 arguments: the query, the columns, the mode and the filename. The syntax is:
generateExcel( theQuery, columnList, mode, filename )
But you can call it with just the name of the query if you want, e.g.:
generateExcel( myQuery )
Options
- You can be specific about the columns:
generateExcel( products, "productname, productCost" )
The Column list argument allows you to specify the order you want the columns displayed in, or omit columns you don't want. If you want you can just leave it blank and it will just use the query column list - but the columns could be in any order. - There are 3 output modes: "display", "save" and "debug":
generateExcel( products, "productId, productname, productCost", "save" )
The default mode is "display".
Mode "display": This will try to display the excel chart within the browser. If the end-user's browser does not allow this, Excel will launch and show the output instead.
Mode: "save": This will just do a download and will not attempt to display within the browser.
Mode: "debug": Will display the columns as a normal HTML page with <pre> tags.
-
The filename attribute allows you to specify the filename that the excel should be saved as. The default is "report.xls".
Enjoy!
Update May 15 2007
I made some improvements yesterday - the code uses <cfcontent reset="yes"> so that it will reset the output before rendering the excel view and now supports exporting dates properly.
Tags:
Comments [3]
Peter Coppinger aka Topper is a neurotic web monster who spends most of his chaotic life developing ColdFusion web applications when not drinking himself into a stupor and scheming his plans for world dominance.
