Formula Evaluation

This guide will explain the process of using an evaluator to evaluate a spreadsheet formula.

Before you begin #

You should get your API key from your UniCloud account.

If this is your first time using UniOffice SDK, follow this guide to set up a local development environment.

Clone the project repository #

In your terminal, clone the examples repository. It contains the Go code we will be using for this guide.

git clone https://github.com/unidoc/unioffice-examples

To get the example navigate to the path spreadsheet/formula-evaluation folder in the unioffice-examples directory.

cd unioffice-examples/spreadsheet/formula-evaluation/

How it works #

The necessary libraries are imported in lines 4-11. Then the package is instantiated by setting the metered license API keys in side the init function.

The main function is defined in lines 22-49. In line 23-24, the list of the supported functions is printed.

In lines 24-26, a new workbook is created, and a new spreadsheet is added to the workbook using:

ss := spreadsheet.New()
defer ss.Close()
sheet := ss.AddSheet()

Then three cells are set with numbers as follows:

sheet.Cell("A1").SetNumber(1.2)
sheet.Cell("A2").SetNumber(2.3)
sheet.Cell("A3").SetNumber(2.3)

In line 32, a new formula evaluated is created using formula.NewEvaluator(). The value of the cell A1 is pulled, and its value evaluated in lines 36-37. Then in line 41-42 a new formula is valuated from a live string formula. Lines 45-47 show how to store a formula in a cell and then evaluate it using the formula evaluator. This is done using:

sheet.Cell("A4").SetFormulaRaw("SUM(A1:A3)+SUM(A1:A3)")
a4Value := formEv.Eval(sheet.FormulaContext(), "A4")
fmt.Println("A4 is", a4Value.Value())

Run the code #

To run the code use the following command.

go run main.go

Sample Output #

Currently support 231 functions
[ABS ACCRINTM ACOS ACOSH AMORDEGRC AMORLINC AND ASIN ASINH ATAN ATAN2 ATANH AVERAGE AVERAGEA CEILING CELL CHAR CHOOSE CLEAN
CODE COLUMN COLUMNS COMBIN CONCAT CONCATENATE COS COSH COUNT COUNTA COUNTBLANK COUNTIF COUNTIFS COUPDAYBS COUPDAYS
COUPDAYSNC COUPNCD COUPNUM COUPPCD CUMIPMT CUMPRINC DATE DATEDIF DATEVALUE DAY DAYS DB DDB DEGREES DISC DOLLARDE DOLLARFR
DURATION EDATE EFFECT EOMONTH EVEN EXACT EXP FACT FACTDOUBLE FALSE FIND FINDB FLOOR FV FVSCHEDULE GCD HLOOKUP IF IFERROR
IFS INDEX INDIRECT INT INTRATE IPMT IRR ISBLANK ISERR ISERROR ISEVEN ISLOGICAL ISNA ISNONTEXT ISNUMBER ISO.CEILING ISODD
ISPMT ISREF ISTEXT LARGE LCM LEFT LEFTB LEN LENB LN LOG LOG10 LOOKUP LOWER MATCH MAX MAXA MAXIFS MDETERM MDURATION MEDIAN
MID MIN MINA MINIFS MINUTE MIRR MOD MONTH MROUND MULTINOMIAL NA NOMINAL NOT NOW NPER NPV ODD ODDLPRICE ODDLYIELD OFFSET OR
ORG.OPENOFFICE.ISLEAPYEAR PDURATION PI PMT POWER PPMT PRICE PRICEDISC PRICEMAT PRODUCT PROPER PV QUOTIENT RADIANS RAND
RANDBETWEEN RATE RECEIVED REPLACE REPT RIGHT RIGHTB ROMAN ROUND ROUNDDOWN ROUNDUP ROW ROWS RRI SEARCH SEARCHB SERIESSUM
SIGN SIN SINH SLN SMALL SQRT SQRTPI SUBSTITUTE SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SYD T TAN TANH TBILLEQ TBILLPRICE
TBILLYIELD TEXT TEXTJOIN TIME TIMEVALUE TODAY TRANSPOSE TRIM TRUE TRUNC UPPER VALUE VDB VLOOKUP XIRR XNPV YEAR YEARFRAC
YIELD YIELDDISC YIELDMAT _xlfn.ACOT _xlfn.ACOTH _xlfn.ARABIC _xlfn.BASE _xlfn.CEILING.MATH _xlfn.CEILING.PRECISE _xlfn
COMBINA _xlfn.CONCAT _xlfn.COT _xlfn.COTH _xlfn.CSC _xlfn.CSCH _xlfn.DAYS _xlfn.DECIMAL _xlfn.FLOOR.MATH _xlfn.FLOOR
PRECISE _xlfn.IFNA _xlfn.IFS _xlfn.ISFORMULA _xlfn.MAXIFS _xlfn.MINIFS _xlfn.MUNIT _xlfn.PDURATION _xlfn.RRI _xlfn.SEC
_xlfn.SECH _xlfn.TEXTJOIN _xlfn.UNICHAR _xlfn.UNICODE _xlfn.XOR]
A1 is 1.2
SUM(A1:A3) is 5.8
A4 is 11.6