Skip navigation
HugoSchmidt
HugoSchmidt Calculating status...
Currently Being Moderated

Can you use the R1C1 reference style for SpreadsheetSetCellFormula

Apr 26, 2012 10:10 AM

Tags: #column #spreadsheetsetcellformula #r1c1

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#curR ow#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?

650 Views   3 Replies   Latest reply: -==cfSearching==-, Apr 26, 2012 10:59 AM
 
Replies
  • -==cfSearching==-
    Currently Being Moderated
    1. -==cfSearching==-,
    Apr 26, 2012 10:44 AM   in reply to HugoSchmidt
    Report

    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==-

     
    |
    Mark as:
  • HugoSchmidt
    Currently Being Moderated
    2. HugoSchmidt,
    Apr 26, 2012 10:49 AM   in reply to -==cfSearching==-
    Report

    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!

     
    |
    Mark as:
  • -==cfSearching==-
    Currently Being Moderated
    3. -==cfSearching==-,
    Apr 26, 2012 10:59 AM   in reply to HugoSchmidt
    Report

    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.

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points