Set formula
Set formula by editing a cell
Enter a value with an equal-sign prefix (”=”) to set the formula in a cell.
Set formula programmatically
To set a cell formula in a .NET program, use one of the following methods:
// method 1: access cell data directly
worksheet["E3"] = "=(A3+B3)\*C3";
// method 2: via cell instance
var cell = worksheet.Cells["E3"];
cell.Formula = "(A3+B3)\*C3"; // do not need equal-sign (=)Get cell formula
To get the formula from a cell:
// method 1: via worksheet method
var formula = worksheet.GetCellFormula("C1");
// method 2: via cell instance
var cell = worksheet.Cells["C1"];
var formula = cell.Formula;Check whether a cell has a formula
To check whether a cell has a formula whose value is calculated from that formula, use the following property of the cell instance:
bool hasFormula = cell.HasFormula;Get formula result
After cell editing is finished, the formula is parsed and the cell value is calculated immediately.
Get formula result programmatically
Use one of the following methods to get the calculation result of a formula:
// method 1: access cell data directly
object value = worksheet["E3"];
// method 2: via cell instance
var cell = worksheet.Cells["E3"];
object value = cell.Data;Types of formula values
ReoGrid always uses .NET native types as formula value types. If the cell value is a number, the .NET double type is used; if the value is a string, the .NET string type is used. For example:
worksheet["E3"] = "=(A3+B3)\*C3";// object value = worksheet[“E3”]; // OK, type is double System.Diagnostics.Debug.Assert(worksheet[“E3”] is double); // true
The following .NET types are used in formula calculations:
- double
- string
- bool
- DateTime
Integer, long, short, and float are converted to double; string and StringBuilder are converted to string. For example:
worksheet["A1"] = "=10"; // 10 will be treated as double type
object val = worksheet["A1"];
MessageBox.Show(val.GetType().Name); // result is 'Double'Prevent cell text from being treated as a formula
Cell text that starts with a single quote (’) is not parsed as a formula:
worksheet["A1"] = "'=10"; // cell data is '=10'Cell Reference
As in Excel, ReoGrid supports two types of addresses:
- A1 format: Relative address
- $A$1 format: Absolute address
Cell or range addresses are used to reference values from other cells:
Example formula using a relative reference to another cell: 
Result: 
Range Reference
The format StartCell:EndCell is used to describe an address that references a range: 
Result: 
Get formula reference list
ReoGrid creates and manages the reference relationships between cells in memory. To get the reference list of a cell formula, use the GetCellFormulaReferenceRanges method:
worksheet["H8"] = "=A1+B1-SUM(A1:C3)+AVERAGE(D1:H5)";
// Get reference list from cell
var rangeList = worksheet.GetCellFormulaReferenceRanges("H8");
// Assert information from reference list
AssertTrue(rangeList != null);
AssertEquals(rangeList[0].Range, new RangePosition("A1"));
AssertEquals(rangeList[1].Range, new RangePosition("B1"));
AssertEquals(rangeList[2].Range, new RangePosition("A1:C3"));
AssertEquals(rangeList[3].Range, new RangePosition("D1:H5"));Remove formula from range
The ClearRangeContent method of a worksheet can be used to delete formulas from a specified range.
var sheet = grid.CurrentWorksheet;
sheet.ClearRangeContent("A1:B5", CellElementFlags.Formula);Trace Precedents and Dependents
Trace formula precedents and dependents for cells.
To enable tracing of precedents, set the TraceFormulaPrecedent property of a cell to true:
var sheet = workbook.CurrentWorksheet;
sheet["C2"] = 10;
sheet["C3"] = 5;
ReoGridCell cell = sheet.Cells["C5"];
cell.Formula = "C2+C3";
cell.TraceFormulaPrecedents = true;Or enable tracing by calling the TraceCellPrecedents method of the worksheet:
sheet.TraceCellPrecedents("C5");Once the formula changes, the trace arrows will disappear. To always enable formula tracing, use the following code:
sheet.CellDataChanged += (s, e) => e.Cell.TraceFormulaPrecedents = true;Named Range Reference
For information on how to define and remove a named range, see Named Range.
To reference a named range in a formula, use its name directly. For example:
sheet.DefineNamedRange("myrange", "A1:B5");
sheet.Cells["E10"] = "=SUM(myrange)";Built-in Excel-compatible functions
ReoGrid supports built-in functions that can be used in formula calculations. See Formula Functions.
Automatic reference cell updating
When a cell’s value changes, all cells that reference that cell are also recalculated. ReoGrid performs this automatically. To disable this behavior, see the section “Pause and resume automatic reference cell updating” below.
Pause and resume automatic reference cell updating
When a cell references another cell, it is updated whenever the referenced cell changes. By default, this behavior is performed automatically. To disable it, use the following method:
sheet.SuspendFormulaReferenceUpdates();To resume it:
sheet.ResumeFormulaReferenceUpdates();Or change the worksheet settings to achieve the same effect:
// disable reference updating
sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, false);
// enable reference updating
sheet.SetSettings(WorksheetSettings.Formula_AutoUpdateReferenceCell, true);Force recalculation of the entire worksheet
Sometimes it is necessary to recalculate the entire worksheet, especially when automatic formula reference updating is disabled.
sheet.Recalculate();Custom Function
To add a custom function, see the next topic: Custom Function.