To loop through a query result in CFSCRIPT and build a spreadsheet, you can utilize the SpreadsheetAddColumn. This example does not take into consideration a zero-length query result, so you will have to provide your own conditional checks. The idea here is to just show how you can loop through a query and add each column in succession.

<cfscript>
   TheFile=GetDirectoryFromPath(GetCurrentTemplatePath()) & "TestDocument.xls";
   TheSheet=SpreadsheetNew("TestDocument");
   SpreadsheetAddRow(TheSheet, "Column A, Column B");

So far I've set the location for the XLS document to be created and stored, and set the name of my spreadsheet. For this example, there are two columns defined in the event I want to call them something other than the name of the records in the query result. I am going to use SpreadsheetAddColumn as an example where the query is being pulled for other processes on the page and may have more column names than I actually want in the XLS document.

For (i=1; i LTE GetQueryResult.RecordCount; i=i+1) {
      SpreadsheetAddColumn(TheSheet, GetQueryResult.FruitColor[i], i+1, 1, false);
      SpreadsheetAddColumn(TheSheet, GetQueryResult.FruitNames[i], i+1, 2, false);}
</cfscript>

We're looping through the query results (assuming there is at least one result for this example) and generating the column data. The result is grabbing the query record by position according to the looped value of "i". So we can keep the header in place we are adding one to the position start row and number our columns. The "false" is for the optional "insert".

Next we need to commit the spreadsheet to a file on the server and in this example, trigger a download of the file.

<cfspreadsheet action="write" filename="#TheFile#" name="TheSheet" overwrite="yes" sheetname="TestDocument">

<cfheader name="Content-Disposition" value="attachment; filename=TestDocument.xls">
<cfcontent type="application/vnd.ms-excel">
<cfcontent file="#TheFile#" reset="yes">

You may want to add a MIME type to the CFCONTENT page for properly generated code, but I've found that it will still deliver the XLS document without it, so its up to you and your situation. The CFHEADER file name location may also need to be tweaked if it is now pointing to the literal location of where the absolute path created our document with the TheFile variable.

If you find this post useful please leave a comment and let me know how you used the information.

Whenever a user attempted to post a comment to this blog, they would receive the error "It looks like you have already posted this comment".ﾠ A quick search in Google was no help.ﾠ So for anyone else who may receive this error while running a Mango Blog here is what I did to resolve the problem.

First, the error is actually triggered from the component file CommentManager.cfc which sets the variable isDuplicateComment.ﾠ I dug through the file and removed the conditional logic checks for this variable only to discover that the blog still produced the error.

Go to Cache in your Mango Blog admin and press the Reload Configuration button.ﾠ This will allow your changes to take affect.ﾠ Now, for this blog anyway, if you want to leave a comment you can once again.

While working with CFLDAP recently I needed to develop filters that would authenticate active directory members who belonged to certain characteristic groups and whose domain accounts were not disabled. There are a number of online resources that can help with filtering domain lookups but when you combine the filters together the conditional logic can be problematic. I ended up wasting a lot of time configuring and reconfiguring my filter.

If you're working within an active directory and you need to filter users based on certain criteria, then one of the best tools that you can use is called Active Directory Explorer from Sysinternals. You can use this tool to find the exact search parameters you require from your CFLDAP filter and then just copy and paste them into the attribute. This will prevent you from wasting time trying to design all the parameters needed in the conditional logic of the filter. The search feature in the Active Directory Explorer tool allows you to define any kind of search parameters you may need and takes care of things like "contains", "is equal to", and "does not contain".

The conditional logic of the Active Directory Explorer tool works a lot like CFSCRIPT and can be a little daunting at first. As soon as you start combining conditional elements together you could end up spending a lot of time adapting your filter. Using the GUI search interface in Active Directory Explorer will allow you to develop your filter requirements quickly and effectively.

Information on the Active Diretory Explorer tool can be found here.

If you find this post useful please leave a comment and let me know how you used the information.

I was recently working on a ColdFusion 9 project using CFTREE and CFLAYOUT together. The project called for user selection of the tree icons that would cause navigation panels in the layout to display different directory contents and provide the appropriate edit menus.

Everything was working as planned, after applying some new logic and adding the decorative icons and balanced color schemes. While alpha testing I noticed that the tree would occasionally fail to trigger the layout panel and you would be left with partial content and rouge JavaScript errors. This was battled for about a week. Line by line the code was checked for flaws, the data was checked, the database was checked, and then begrudgingly the ColdFusion built-in Ajax was examined. The error message was simply that the layout panel was undefined.

When all lines of code involved were completely examined and nothing obvious could be found, we turned our attention to reproducing the actual error. It seemed as though it could happen to any content from any position in the tree. The real kicker was that it could only be triggered if the user would rapidly click on the tree nodes. The first fix-candidate was a two second timeout applied to the tree whenever a node was clicked, but this slowed the application down and was generally irritating. Utilizing the windows "onerror" event was eventually settled on as a fix. When triggered, thanks to rapidly clicking on a tree node, the event listener simply reloaded the layout area and flushed the client error notice.

The bottom line though, is that when combined with the CFLAYOUT, the CFTREE tag in ColdFusion 9 (no other versions have been tested) will generate a client side JavaScript error when the user clicks on any tree node too quickly in rapid succession. If you have these tags combined, users will click, over-click, and rapid click; so you may need to develop your own mechanism to capture and deal with the client side error event.

If you find this post useful please leave a comment and let me know how you used the information.

I was working with CFMESSAGEBOX and kept getting the client side error that there was an exception thrown and not caught, the culprit was identified as the "CFAJAX.JS" file on line 90.

There really isn't anything wrong this the JS file, the problem is that when you call CFMESSAGEBOX from an ajax page, there is little you have to do, but when you are calling it from a page without any other ajax and you receive the error; just be sure to use the "AjaxOnload" command to execute at the bottom of your page.

If you find this post useful please leave a comment and let me know how you used the information.