diff options
| -rw-r--r-- | calc.go | 104 | ||||
| -rw-r--r-- | calc_test.go | 23 |
2 files changed, 119 insertions, 8 deletions
@@ -537,6 +537,8 @@ type formulaFuncs struct { // T // TAN // TANH +// TBILLEQ +// TEXTJOIN // TIME // TODAY // TRANSPOSE @@ -7636,6 +7638,64 @@ func (fn *formulaFuncs) SUBSTITUTE(argsList *list.List) formulaArg { return newStringFormulaArg(pre + newText.Value() + post) } +// TEXTJOIN function joins together a series of supplied text strings into one +// combined text string. The user can specify a delimiter to add between the +// individual text items, if required. The syntax of the function is: +// +// TEXTJOIN([delimiter],[ignore_empty],text1,[text2],...) +// +func (fn *formulaFuncs) TEXTJOIN(argsList *list.List) formulaArg { + if argsList.Len() < 3 { + return newErrorFormulaArg(formulaErrorVALUE, "TEXTJOIN requires at least 3 arguments") + } + if argsList.Len() > 252 { + return newErrorFormulaArg(formulaErrorVALUE, "TEXTJOIN accepts at most 252 arguments") + } + delimiter := argsList.Front().Value.(formulaArg) + ignoreEmpty := argsList.Front().Next().Value.(formulaArg).ToBool() + if ignoreEmpty.Type != ArgNumber { + return ignoreEmpty + } + args, ok := textJoin(argsList.Front().Next().Next(), []string{}, ignoreEmpty.Number != 0) + if ok.Type != ArgNumber { + return ok + } + result := strings.Join(args, delimiter.Value()) + if len(result) > TotalCellChars { + return newErrorFormulaArg(formulaErrorVALUE, fmt.Sprintf("TEXTJOIN function exceeds %d characters", TotalCellChars)) + } + return newStringFormulaArg(result) +} + +// textJoin is an implementation of the formula function TEXTJOIN. +func textJoin(arg *list.Element, arr []string, ignoreEmpty bool) ([]string, formulaArg) { + for arg.Next(); arg != nil; arg = arg.Next() { + switch arg.Value.(formulaArg).Type { + case ArgError: + return arr, arg.Value.(formulaArg) + case ArgString: + val := arg.Value.(formulaArg).Value() + if val != "" || !ignoreEmpty { + arr = append(arr, val) + } + case ArgNumber: + arr = append(arr, arg.Value.(formulaArg).Value()) + case ArgMatrix: + for _, row := range arg.Value.(formulaArg).Matrix { + argList := list.New().Init() + for _, ele := range row { + argList.PushBack(ele) + } + if argList.Len() > 0 { + args, _ := textJoin(argList.Front(), []string{}, ignoreEmpty) + arr = append(arr, args...) + } + } + } + } + return arr, newBoolFormulaArg(true) +} + // TRIM removes extra spaces (i.e. all spaces except for single spaces between // words or characters) from a supplied text string. The syntax of the // function is: @@ -7818,14 +7878,7 @@ func (fn *formulaFuncs) CHOOSE(argsList *list.List) formulaArg { for i := 0; i < idx; i++ { arg = arg.Next() } - var result formulaArg - switch arg.Value.(formulaArg).Type { - case ArgString: - result = newStringFormulaArg(arg.Value.(formulaArg).String) - case ArgMatrix: - result = newMatrixFormulaArg(arg.Value.(formulaArg).Matrix) - } - return result + return arg.Value.(formulaArg) } // deepMatchRune finds whether the text deep matches/satisfies the pattern @@ -9708,6 +9761,41 @@ func (fn *formulaFuncs) SYD(argsList *list.List) formulaArg { return newNumberFormulaArg(((cost.Number - salvage.Number) * (life.Number - per.Number + 1) * 2) / (life.Number * (life.Number + 1))) } +// TBILLEQ function calculates the bond-equivalent yield for a Treasury Bill. +// The syntax of the function is: +// +// TBILLEQ(settlement,maturity,discount) +// +func (fn *formulaFuncs) TBILLEQ(argsList *list.List) formulaArg { + if argsList.Len() != 3 { + return newErrorFormulaArg(formulaErrorVALUE, "TBILLEQ requires 3 arguments") + } + args := list.New().Init() + args.PushBack(argsList.Front().Value.(formulaArg)) + settlement := fn.DATEVALUE(args) + if settlement.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + args.Init() + args.PushBack(argsList.Front().Next().Value.(formulaArg)) + maturity := fn.DATEVALUE(args) + if maturity.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + dsm := maturity.Number - settlement.Number + if dsm > 365 || maturity.Number <= settlement.Number { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + discount := argsList.Back().Value.(formulaArg).ToNumber() + if discount.Type != ArgNumber { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + if discount.Number <= 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + return newNumberFormulaArg((365 * discount.Number) / (360 - discount.Number*dsm)) +} + // YIELDDISC function calculates the annual yield of a discounted security. // The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index 1df622e..95b479c 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1163,6 +1163,12 @@ func TestCalcCellValue(t *testing.T) { "=SUBSTITUTE(\"abab\",\"x\",\"X\",2)": "abab", "=SUBSTITUTE(\"John is 5 years old\",\"John\",\"Jack\")": "Jack is 5 years old", "=SUBSTITUTE(\"John is 5 years old\",\"5\",\"6\")": "John is 6 years old", + // TEXTJOIN + "=TEXTJOIN(\"-\",TRUE,1,2,3,4)": "1-2-3-4", + "=TEXTJOIN(A4,TRUE,A1:B2)": "1040205", + "=TEXTJOIN(\",\",FALSE,A1:C2)": "1,4,,2,5,", + "=TEXTJOIN(\",\",TRUE,A1:C2)": "1,4,2,5", + "=TEXTJOIN(\",\",TRUE,MUNIT(2))": "1,0,0,1", // TRIM "=TRIM(\" trim text \")": "trim text", "=TRIM(0)": "0", @@ -1354,6 +1360,8 @@ func TestCalcCellValue(t *testing.T) { // SYD "=SYD(10000,1000,5,1)": "3000", "=SYD(10000,1000,5,2)": "2400", + // TBILLEQ + "=TBILLEQ(\"01/01/2017\",\"06/30/2017\",2.5%)": "0.0256680731364276", // YIELDDISC "=YIELDDISC(\"01/01/2017\",\"06/30/2017\",97,100)": "0.0622012325059031", "=YIELDDISC(\"01/01/2017\",\"06/30/2017\",97,100,0)": "0.0622012325059031", @@ -2293,6 +2301,12 @@ func TestCalcCellValue(t *testing.T) { "=SUBSTITUTE()": "SUBSTITUTE requires 3 or 4 arguments", "=SUBSTITUTE(\"\",\"\",\"\",\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", "=SUBSTITUTE(\"\",\"\",\"\",0)": "instance_num should be > 0", + // TEXTJOIN + "=TEXTJOIN()": "TEXTJOIN requires at least 3 arguments", + "=TEXTJOIN(\"\",\"\",1)": "strconv.ParseBool: parsing \"\": invalid syntax", + "=TEXTJOIN(\"\",TRUE,NA())": "#N/A", + "=TEXTJOIN(\"\",TRUE," + strings.Repeat("0,", 250) + ",0)": "TEXTJOIN accepts at most 252 arguments", + "=TEXTJOIN(\",\",FALSE,REPT(\"*\",32768))": "TEXTJOIN function exceeds 32767 characters", // TRIM "=TRIM()": "TRIM requires 1 argument", "=TRIM(1,2)": "TRIM requires 1 argument", @@ -2328,6 +2342,7 @@ func TestCalcCellValue(t *testing.T) { "=CHOOSE()": "CHOOSE requires 2 arguments", "=CHOOSE(\"index_num\",0)": "CHOOSE requires first argument of type number", "=CHOOSE(2,0)": "index_num should be <= to the number of values", + "=CHOOSE(1,NA())": "#N/A", // COLUMN "=COLUMN(1,2)": "COLUMN requires at most 1 argument", "=COLUMN(\"\")": "invalid reference", @@ -2621,6 +2636,14 @@ func TestCalcCellValue(t *testing.T) { "=SYD(10000,1000,0,1)": "SYD requires life argument to be > 0", "=SYD(10000,1000,5,0)": "SYD requires per argument to be > 0", "=SYD(10000,1000,1,5)": "#NUM!", + // TBILLEQ + "=TBILLEQ()": "TBILLEQ requires 3 arguments", + "=TBILLEQ(\"\",\"06/30/2017\",2.5%)": "#VALUE!", + "=TBILLEQ(\"01/01/2017\",\"\",2.5%)": "#VALUE!", + "=TBILLEQ(\"01/01/2017\",\"06/30/2017\",\"\")": "#VALUE!", + "=TBILLEQ(\"01/01/2017\",\"06/30/2017\",0)": "#NUM!", + "=TBILLEQ(\"01/01/2017\",\"06/30/2018\",2.5%)": "#NUM!", + "=TBILLEQ(\"06/30/2017\",\"01/01/2017\",2.5%)": "#NUM!", // YIELDDISC "=YIELDDISC()": "YIELDDISC requires 4 or 5 arguments", "=YIELDDISC(\"\",\"06/30/2017\",97,100,0)": "#VALUE!", |