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.