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)

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

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