(Showing Draft Content)

Built-In Formulas

You can use built-in formulas in cells. Use the formula method or setFormula method to add formulas. You can also specify the reference style with the referenceStyle property.

The following code sample sets the referenceStyle property in the Workbook method.

spread.options.referenceStyle = GC.Spread.Sheets.ReferenceStyle.R1C1;
//spread.options.referenceStyle = GC.Spread.Sheets.ReferenceStyle.A1;

The following code sample uses the formula method.

activeSheet.setValue(0,0,1);
activeSheet.setValue(0,1,2);
activeSheet.setValue(0,2,10);
activeSheet.getCell(1,1).formula("=SUM(A1:C1)");

To clear a formula from a cell or range, you have two primary options:

clear(): Access this method via GC.Spread.Sheets.Worksheet.clear() or GC.Spread.Sheets.CellRange.clear(). This action will clear both the formula and its associated calculated result, resulting in an empty cell.
setFormula(): Call the setFormula() method and pass null specifically for the formula argument. This will remove only the formula, retaining the cell's last computed value.

Note:
Setting a new value in a formula-containing cell requires clearing the formula first. Failure to do so will prevent the new value from taking effect.

sheet.setFormula(1, 1, '=SUM(A1,C3)');
sheet.setValue(1, 1, 7); // Failed
// Method1
sheet.getRange(1, 1, 1, 1).clear(GC.Spread.Sheets.StorageType.data);
// Method2
sheet.clear(1, 1, 1, 1, GC.Spread.Sheets.SheetArea.viewport, GC.Spread.Sheets.StorageType.data);
// Method3
sheet.setFormula(1, 1, null);
sheet.getFormula(1, 1, GC.Spread.Sheets.SheetArea.viewport);
// Output: null
sheet.setValue(1, 1, 7); // Success