How to dynamically set spreadsheet auto filter in ColdFusion 2016?

Question

I would like to add dynamically auto filter to all columns in the first row on the spreadsheet. Here is example of what I have so far:

SpreadSheetAddAutoFilter(theSheet,"A1:Z1");

This works but problem is if I have less or more columns in my files. Is there a way to set filter on every column in the first row of my file? That shouldn't be hard coded. Please let me know if you have any example for this problem. Thank you.

Answer 1

Assuming you already have an Excel sheet, and are trying to dynamically add the filter to the entire row, you need to

1. Get the column count
2. Translate the column count into the corresponding letter. For example column E -> 5
3. Create a range from that, such as A1:E1 and pass that string into the SpreadSheetAddAutoFilter() function.

Here is a working example that I wrote:

<cffunction name="columnToLetter" returntype="any">
    <cfargument name="index" type="numeric">
    <cfset letterArray = listToArray('A B C D E F G H I J K L M N O P Q R S T U V W X Y Z', ' ')>
    <cfreturn letterArray[index]>
</cffunction>


<cfspreadsheet action="read" src="fin.xlsx" name="spreadSheetObj" sheetname="Sheet1"> 
<cfset count = SpreadsheetGetColumnCount(spreadSheetObj, 'Sheet1')>

<cfoutput>
    count: #count# <br>
    <cfset column_label = columnToLetter(count)>
    <cfset column_label = 'A1:' & column_label & '1'>
    label: #column_label#
    <cfset SpreadSheetAddAutoFilter(spreadSheetObj, column_label)>
    <cfspreadsheet action="write" filename="updatedFile.xls" name="spreadSheetObj" sheetname="courses" overwrite=true> 
</cfoutput>

Answer 2

Posting this answer since it has better results than the already posted answer. This is just a simple loop with division by 26 and getting the character.

<cfscript>
    // Function to print Excel column name for a given column number
    function printString(n){
        str = '';
        while (n>0){
            // Find remainder
            rem = n%26;
            // If remainder is 0, then a 'Z' must be there in output
            if (rem==0){
                str = 'Z' & str;
                n = (n\26)-1;
            } else {
                str = chr(64 + rem) & str;
                n = n\26;
            }
        }
        return str;
    }
</cfscript>

Shorter form

function printString(n){
    str = '';
    while (n>0){
        rem = n%26;
        str = rem==0 ? 'Z' & str : chr(64 + rem) & str;
        n = rem==0 ? (n\26)-1 : n\26;
    }
    return str;
}

Function in action.