Can you use the R1C1 reference style for SpreadsheetSetCellFormula

[HugoSchmidt]

I am creating a spreadsheet where I want the sum of some columns to appear at the end of the row.  The problem is that the number of columns can vary depending on when the report is run.

A SpreadsheetSetCellFormula function is written like this for summing Columns B through D and placing the function in column 5 (E).

<CFSET curRow = 2>

<CFSET curCol = 5>

<CFSET SpreadsheetSetCellFormula(sObjRollUp,"SUM(B2:D2)",curRow,curCol)>

But what if there are 10 columns, or 15 columns?

I tried this and it did not work using the R1C1 reference style:

<CFSET SpreadsheetSetCellFormula(sObjRollUp,"SUM(R#curRow#C#curCol-4#:R#curRow#C#curCol-1#)",curRow,curCol)>

But that generates an Excel Error.

I know that (in Excel), if I turn on R1C1 reference I can reference a Sum range like this: =SUM(R6C2:R6C3), but if the R1C1 reference is turned off, this function won't work.

So a couple of questions.

1) Is there CF or Excel function to get the "LETTER" value from a numeric column value (don't forget about columns AA, AB, etc).

OR

2) Is there a CF way to turn on R1C1 referencing in the spreadsheet object?

[-__cfSearching__- · Accepted solution]

I could be wrong, but I do not think CF supports R1C1 style.  However you can use the underlying POI classes to convert a column number to a letter. Just be aware POI expects the column index to be in base zero, not one.

<cfscript>

    // example generate all column letters

    util = createObject("java", "org.apache.poi.ss.util.CellReference");

    for (col = 1; col <= 256; col++) {

        // note - the column numbers are in base zero (0)!

        colLetter = util.convertNumToColString( col - 1 );

        WriteOutput("R1C1=[#col#] Alpha=[#colLetter#]<br>" );

    }

</cfscript>

Message was edited by: -==cfSearching==-

[HugoSchmidt]

I'll give you credit for a correct answer, because it does answer my #1 and it works.  I do wish I could just use the R1C1 format, though.  Especially since ColdFusion is done by numeric reference.

Thanks!

[-__cfSearching__-]

Well ultimately I think it comes down to whether POI supports it. Last I checked it did not, or at least not fully. Though that might have changed.