Gets or sets formula string.

Remarks

GemBox.Spreadsheet can read and write formulas, but cannot calculate formula results. When you open a XLS file in MS Excel, formula results will be calculated automatically.

Old XLS format requires all formulas to be parsed and saved to XLS files as special tokens in RPN (Reverse Polish notation). GemBox.Spreadsheet only knows how to parse limited set of formulas listed below.

New XLSX (Open XML) format stores formulas as strings and leaves formula parsing to applications that read XLSX documents. Therefore, ALL formulas are supported when writing/reading XLSX files.

Depending on ExcelFile.DelayFormulaParse property, formula string can be parsed when this property is set or when one of ExcelFile.SaveXls methods is called.

GemBox.Spreadsheet formula parser will use English culture to parse numbers.

Currently supported formula features by GemBox.Spreadsheet formula parser are:

• Named cell
• Named range
• Absolute cell/range
• Relative cell/range
• Functions( partly, see the list of supported functions below )
• Missed argument
• Unary operator
• Binary operator
• Parentheses
• 3d cell reference
• 3d cell range reference
• Boolean
• Integer
• Float
• String
• Error

Currently unsupported formula features are:

• Const array
• Array formula
• R1C1 reference

Currently supported functions are:

• NOW
• SECOND
• MINUTE
• HOUR
• WEEKDAY
• YEAR
• MONTH
• DAY
• TIME
• DATE
• RAND
• TEXT
• VAR
• MOD
• NOT
• OR
• AND
• FALSE
• TRUE
• VALUE
• LEN
• MID
• ROUND
• SIGN
• INT
• ABS
• LN
• EXP
• SQRT
• PI
• COS
• SIN
• COLUMN
• ROW
• MAX
• MIN
• AVERAGE
• SUM
• IF
• COUNT
• SUBTOTAL

For more information on formulas, consult Microsoft Excel documentation.

Exceptions

Example

Following code demonstrates how to use formulas and named ranges. It shows next features: cell references (both absolute and relative), unary and binary operators, constand operands (integer and floating point), functions and named cell ranges.

[Visual Basic]
    ws.Cells("A1").Value = 5
    ws.Cells("A2").Value = 6
    ws.Cells("A3").Value = 10
 
    ws.Cells("C1").Formula = "=A1+A2"
    ws.Cells("C2").Formula = "=$A$1-A3"
    ws.Cells("C3").Formula = "=COUNT(A1:A3)"
    ws.Cells("C4").Formula = "=AVERAGE($A$1:$A$3)"
    ws.Cells("C5").Formula = "=SUM(A1:A3,2,3)"
    ws.Cells("C7").Formula = "= 123 - (-(-(23.5)))"
 
    ws.NamedRanges.Add("DataRange", ws.Cells.GetSubrange("A1", "A3"))
    ws.Cells("C8").Formula = "=MAX(DataRange)"
 
    Dim cr As CellRange = ws.Cells.GetSubrange("B9","C10")
    cr.Merged = True
    cr.Formula = "=A1*25"

[C#]
    
    ws.Cells["A1"].Value = 5;
    ws.Cells["A2"].Value = 6;
    ws.Cells["A3"].Value = 10;

    ws.Cells["C1"].Formula = "=A1+A2";
    ws.Cells["C2"].Formula = "=$A$1-A3";
    ws.Cells["C3"].Formula = "=COUNT(A1:A3)";
    ws.Cells["C4"].Formula = "=AVERAGE($A$1:$A$3)";
    ws.Cells["C5"].Formula = "=SUM(A1:A3,2,3)";
    ws.Cells["C7"].Formula = "= 123 - (-(-(23.5)))";

    ws.NamedRanges.Add("DataRange", ws.Cells.GetSubrange("A1", "A3"));
    ws.Cells["C8"].Formula = "=MAX(DataRange)";

    CellRange cr = ws.Cells.GetSubrange("B9", "C10");
    cr.Merged = true;
    cr.Formula = "=A1*25";

See Also

CellRange Class | GemBox.Spreadsheet Namespace | NamedRangeCollection.Add

---