spreadsheetSetForceFormulaRecalculation

Description

Recalculates a formula or formulas in an excel sheet and applies the formula or formulas when the sheet opens. Formula recalculation in spreadsheets ensures that all formulas are updated whenever a change is made to the data on which the formulas depend. This maintains data accuracy and integrity across complex spreadsheets with interdependent formulas.

Returns

None

History

ColdFusion (2025 release): Added the function.

Syntax

spreadsheetSetForceFormulaRecalculation( Object spreadsheet, Boolean forceRecalculation)

spreadsheetSetForceFormulaRecalculation( Object spreadsheet, Boolean forceRecalculation)

Parameters

Name

Required

Type

Description

spreadSheetObject

Yes

ExcelInfo

The Excel spreadsheet object which contains a formula or formulas to recalculate on launch.

forceRecalculation

Yes

Boolean

True to force all formulas to be recalculated, false otherwise.

Example

theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) &"BookFormula.xlsx";

theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"SpreadsheetSetPrintOrientationReadTrue.xlsx";

// read the first xlsx file

obj=SpreadsheetRead(#theFile#)

// set the value 21 to row 3, col 1

SpreadsheetSetCellValue(obj,21, 3, 1)

// add formula on cell row 11, col 1

SpreadsheetSetCellFormula(obj, "SUM(A1:A9)", 11, 1)

// force formula recalculation

SpreadsheetSetForceFormulaRecalculation(obj,true)

value=SpreadsheetGetForceFormulaRecalculation(obj)

writeoutput(value&"<br>")

spreadsheetWrite(obj,#theFile1#,"", "yes", "no")

obj=SpreadsheetRead(#theFile1#)

theValue=SpreadsheetGetCellValue(obj,11,1);

writeoutput(#theValue# &"<br>")

theValue2=SpreadsheetGetCellValue(obj,14,1);

writeoutput(#theValue2#)

</cfscript>

<cfscript> theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) &"BookFormula.xlsx"; theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"SpreadsheetSetPrintOrientationReadTrue.xlsx"; // read the first xlsx file obj=SpreadsheetRead(#theFile#) // set the value 21 to row 3, col 1 SpreadsheetSetCellValue(obj,21, 3, 1) // add formula on cell row 11, col 1 SpreadsheetSetCellFormula(obj, "SUM(A1:A9)", 11, 1) // force formula recalculation SpreadsheetSetForceFormulaRecalculation(obj,true) value=SpreadsheetGetForceFormulaRecalculation(obj) writeoutput(value&"<br>") spreadsheetWrite(obj,#theFile1#,"", "yes", "no") obj=SpreadsheetRead(#theFile1#) theValue=SpreadsheetGetCellValue(obj,11,1); writeoutput(#theValue# &"<br>") theValue2=SpreadsheetGetCellValue(obj,14,1); writeoutput(#theValue2#) </cfscript>

<cfscript>
    theFile=GetDirectoryFromPath(GetCurrentTemplatePath()) &"BookFormula.xlsx";
    theFile1=GetDirectoryFromPath(GetCurrentTemplatePath()) &"SpreadsheetSetPrintOrientationReadTrue.xlsx";
    // read the first xlsx file
    obj=SpreadsheetRead(#theFile#)
    // set the value 21 to row 3, col 1
    SpreadsheetSetCellValue(obj,21, 3, 1)
    // add formula on cell row 11, col 1
    SpreadsheetSetCellFormula(obj, "SUM(A1:A9)", 11, 1)
    // force formula recalculation
    SpreadsheetSetForceFormulaRecalculation(obj,true)
    value=SpreadsheetGetForceFormulaRecalculation(obj)
    writeoutput(value&"<br>")
    spreadsheetWrite(obj,#theFile1#,"", "yes", "no")
    obj=SpreadsheetRead(#theFile1#)
    theValue=SpreadsheetGetCellValue(obj,11,1);
    writeoutput(#theValue# &"<br>")
    theValue2=SpreadsheetGetCellValue(obj,14,1);
    writeoutput(#theValue2#)
</cfscript>

Get help faster and easier

New user?