summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--calc.go719
-rw-r--r--calc_test.go178
2 files changed, 777 insertions, 120 deletions
diff --git a/calc.go b/calc.go
index ed25a58..2ab3d61 100644
--- a/calc.go
+++ b/calc.go
@@ -37,19 +37,26 @@ const (
formulaErrorGETTINGDATA = "#GETTING_DATA"
)
-// cellRef defines the structure of a cell reference
+// cellRef defines the structure of a cell reference.
type cellRef struct {
Col int
Row int
Sheet string
}
-// cellRef defines the structure of a cell range
+// cellRef defines the structure of a cell range.
type cellRange struct {
From cellRef
To cellRef
}
+// formulaArg is the argument of a formula or function.
+type formulaArg struct {
+ Value string
+ Matrix []string
+}
+
+// formulaFuncs is the type of the formula functions.
type formulaFuncs struct{}
// CalcCellValue provides a function to get calculated cell value. This
@@ -140,7 +147,7 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
if token.TSubType == efp.TokenSubTypeRange {
if !opftStack.Empty() {
// parse reference: must reference at here
- result, err := f.parseReference(sheet, token.TValue)
+ result, _, err := f.parseReference(sheet, token.TValue)
if err != nil {
return efp.Token{TValue: formulaErrorNAME}, err
}
@@ -156,16 +163,16 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
}
if nextToken.TType == efp.TokenTypeArgument || nextToken.TType == efp.TokenTypeFunction {
// parse reference: reference or range at here
- result, err := f.parseReference(sheet, token.TValue)
+ result, matrix, err := f.parseReference(sheet, token.TValue)
if err != nil {
return efp.Token{TValue: formulaErrorNAME}, err
}
- for _, val := range result {
- argsList.PushBack(efp.Token{
- TType: efp.TokenTypeOperand,
- TSubType: efp.TokenSubTypeNumber,
- TValue: val,
- })
+ for idx, val := range result {
+ arg := formulaArg{Value: val}
+ if idx < len(matrix) {
+ arg.Matrix = matrix[idx]
+ }
+ argsList.PushBack(arg)
}
if len(result) == 0 {
return efp.Token{}, errors.New(formulaErrorVALUE)
@@ -190,7 +197,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
opftStack.Pop()
}
if !opfdStack.Empty() {
- argsList.PushBack(opfdStack.Pop())
+ argsList.PushBack(formulaArg{
+ Value: opfdStack.Pop().(efp.Token).TValue,
+ })
}
continue
}
@@ -201,7 +210,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
// current token is text
if token.TType == efp.TokenTypeOperand && token.TSubType == efp.TokenSubTypeText {
- argsList.PushBack(token)
+ argsList.PushBack(formulaArg{
+ Value: token.TValue,
+ })
}
// current token is function stop
@@ -217,7 +228,9 @@ func (f *File) evalInfixExp(sheet string, tokens []efp.Token) (efp.Token, error)
// push opfd to args
if opfdStack.Len() > 0 {
- argsList.PushBack(opfdStack.Pop())
+ argsList.PushBack(formulaArg{
+ Value: opfdStack.Pop().(efp.Token).TValue,
+ })
}
// call formula function to evaluate
result, err := callFuncByName(&formulaFuncs{}, strings.NewReplacer(
@@ -324,7 +337,7 @@ func calculate(opdStack *Stack, opt efp.Token) error {
func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Stack) error {
// parse reference: must reference at here
if token.TSubType == efp.TokenSubTypeRange {
- result, err := f.parseReference(sheet, token.TValue)
+ result, _, err := f.parseReference(sheet, token.TValue)
if err != nil {
return errors.New(formulaErrorNAME)
}
@@ -383,7 +396,7 @@ func (f *File) parseToken(sheet string, token efp.Token, opdStack, optStack *Sta
// parseReference parse reference and extract values by given reference
// characters and default sheet name.
-func (f *File) parseReference(sheet, reference string) (result []string, err error) {
+func (f *File) parseReference(sheet, reference string) (result []string, matrix [][]string, err error) {
reference = strings.Replace(reference, "$", "", -1)
refs, cellRanges, cellRefs := list.New(), list.New(), list.New()
for _, ref := range strings.Split(reference, ":") {
@@ -423,7 +436,7 @@ func (f *File) parseReference(sheet, reference string) (result []string, err err
refs.Remove(e)
}
- result, err = f.rangeResolver(cellRefs, cellRanges)
+ result, matrix, err = f.rangeResolver(cellRefs, cellRanges)
return
}
@@ -431,7 +444,7 @@ func (f *File) parseReference(sheet, reference string) (result []string, err err
// This function will not ignore the empty cell. Note that the result of 3D
// range references may be different from Excel in some cases, for example,
// A1:A2:A2:B3 in Excel will include B1, but we wont.
-func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, err error) {
+func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string, matrix [][]string, err error) {
filter := map[string]string{}
// extract value from ranges
for temp := cellRanges.Front(); temp != nil; temp = temp.Next() {
@@ -441,16 +454,21 @@ func (f *File) rangeResolver(cellRefs, cellRanges *list.List) (result []string,
}
rng := []int{cr.From.Col, cr.From.Row, cr.To.Col, cr.To.Row}
sortCoordinates(rng)
- for col := rng[0]; col <= rng[2]; col++ {
- for row := rng[1]; row <= rng[3]; row++ {
- var cell string
+ matrix = [][]string{}
+ for row := rng[1]; row <= rng[3]; row++ {
+ var matrixRow = []string{}
+ for col := rng[0]; col <= rng[2]; col++ {
+ var cell, value string
if cell, err = CoordinatesToCellName(col, row); err != nil {
return
}
- if filter[cell], err = f.GetCellValue(cr.From.Sheet, cell); err != nil {
+ if value, err = f.GetCellValue(cr.From.Sheet, cell); err != nil {
return
}
+ filter[cell] = value
+ matrixRow = append(matrixRow, value)
}
+ matrix = append(matrix, matrixRow)
}
}
// extract value from references
@@ -500,11 +518,11 @@ func callFuncByName(receiver interface{}, name string, params []reflect.Value) (
//
func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ABS requires 1 numeric arguments")
+ err = errors.New("ABS requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Abs(val))
@@ -519,11 +537,11 @@ func (fn *formulaFuncs) ABS(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ACOS requires 1 numeric arguments")
+ err = errors.New("ACOS requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Acos(val))
@@ -537,11 +555,11 @@ func (fn *formulaFuncs) ACOS(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ACOSH requires 1 numeric arguments")
+ err = errors.New("ACOSH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Acosh(val))
@@ -556,11 +574,11 @@ func (fn *formulaFuncs) ACOSH(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ACOT requires 1 numeric arguments")
+ err = errors.New("ACOT requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Pi/2-math.Atan(val))
@@ -574,11 +592,11 @@ func (fn *formulaFuncs) ACOT(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ACOTH requires 1 numeric arguments")
+ err = errors.New("ACOTH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Atanh(1/val))
@@ -592,11 +610,11 @@ func (fn *formulaFuncs) ACOTH(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ARABIC requires 1 numeric arguments")
+ err = errors.New("ARABIC requires 1 numeric argument")
return
}
val, last, prefix := 0.0, 0.0, 1.0
- for _, char := range argsList.Front().Value.(efp.Token).TValue {
+ for _, char := range argsList.Front().Value.(formulaArg).Value {
digit := 0.0
switch char {
case '-':
@@ -643,11 +661,11 @@ func (fn *formulaFuncs) ARABIC(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ASIN requires 1 numeric arguments")
+ err = errors.New("ASIN requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Asin(val))
@@ -661,11 +679,11 @@ func (fn *formulaFuncs) ASIN(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ASINH requires 1 numeric arguments")
+ err = errors.New("ASINH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Asinh(val))
@@ -680,11 +698,11 @@ func (fn *formulaFuncs) ASINH(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ATAN requires 1 numeric arguments")
+ err = errors.New("ATAN requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Atan(val))
@@ -698,11 +716,11 @@ func (fn *formulaFuncs) ATAN(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) ATANH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("ATANH requires 1 numeric arguments")
+ err = errors.New("ATANH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Atanh(val))
@@ -721,10 +739,10 @@ func (fn *formulaFuncs) ATAN2(argsList *list.List) (result string, err error) {
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
- if y, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Atan2(x, y))
@@ -766,10 +784,10 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) {
}
var number float64
var radix, minLength int
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
- if radix, err = strconv.Atoi(argsList.Front().Next().Value.(efp.Token).TValue); err != nil {
+ if radix, err = strconv.Atoi(argsList.Front().Next().Value.(formulaArg).Value); err != nil {
return
}
if radix < 2 || radix > 36 {
@@ -777,7 +795,7 @@ func (fn *formulaFuncs) BASE(argsList *list.List) (result string, err error) {
return
}
if argsList.Len() > 2 {
- if minLength, err = strconv.Atoi(argsList.Back().Value.(efp.Token).TValue); err != nil {
+ if minLength, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil {
return
}
}
@@ -804,14 +822,14 @@ func (fn *formulaFuncs) CEILING(argsList *list.List) (result string, err error)
return
}
var number, significance float64 = 0, 1
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if number < 0 {
significance = -1
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
}
@@ -846,14 +864,14 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err
return
}
var number, significance, mode float64 = 0, 1, 1
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if number < 0 {
significance = -1
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(efp.Token).TValue, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil {
return
}
}
@@ -862,7 +880,7 @@ func (fn *formulaFuncs) CEILINGMATH(argsList *list.List) (result string, err err
return
}
if argsList.Len() > 2 {
- if mode, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
}
@@ -894,7 +912,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err
return
}
var number, significance float64 = 0, 1
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if number < 0 {
@@ -905,7 +923,7 @@ func (fn *formulaFuncs) CEILINGPRECISE(argsList *list.List) (result string, err
return
}
if argsList.Len() > 1 {
- if significance, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
significance = math.Abs(significance)
@@ -935,10 +953,10 @@ func (fn *formulaFuncs) COMBIN(argsList *list.List) (result string, err error) {
return
}
var number, chosen, val float64 = 0, 0, 1
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
- if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
number, chosen = math.Trunc(number), math.Trunc(chosen)
@@ -968,10 +986,10 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
return
}
var number, chosen float64
- if number, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
- if chosen, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if chosen, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
number, chosen = math.Trunc(number), math.Trunc(chosen)
@@ -984,15 +1002,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
return
}
args := list.New()
- args.PushBack(efp.Token{
- TValue: fmt.Sprintf("%g", number+chosen-1),
- TType: efp.TokenTypeOperand,
- TSubType: efp.TokenSubTypeNumber,
+ args.PushBack(formulaArg{
+ Value: fmt.Sprintf("%g", number+chosen-1),
})
- args.PushBack(efp.Token{
- TValue: fmt.Sprintf("%g", number-1),
- TType: efp.TokenTypeOperand,
- TSubType: efp.TokenSubTypeNumber,
+ args.PushBack(formulaArg{
+ Value: fmt.Sprintf("%g", number-1),
})
return fn.COMBIN(args)
}
@@ -1004,11 +1018,11 @@ func (fn *formulaFuncs) COMBINA(argsList *list.List) (result string, err error)
//
func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("COS requires 1 numeric arguments")
+ err = errors.New("COS requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Cos(val))
@@ -1022,11 +1036,11 @@ func (fn *formulaFuncs) COS(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("COSH requires 1 numeric arguments")
+ err = errors.New("COSH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
result = fmt.Sprintf("%g", math.Cosh(val))
@@ -1040,11 +1054,11 @@ func (fn *formulaFuncs) COSH(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("COT requires 1 numeric arguments")
+ err = errors.New("COT requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if val == 0 {
@@ -1062,11 +1076,11 @@ func (fn *formulaFuncs) COT(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("COTH requires 1 numeric arguments")
+ err = errors.New("COTH requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if val == 0 {
@@ -1084,11 +1098,11 @@ func (fn *formulaFuncs) COTH(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("CSC requires 1 numeric arguments")
+ err = errors.New("CSC requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if val == 0 {
@@ -1099,6 +1113,297 @@ func (fn *formulaFuncs) CSC(argsList *list.List) (result string, err error) {
return
}
+// CSCH function calculates the hyperbolic cosecant (csch) of a supplied
+// angle. The syntax of the function is:
+//
+// CSCH(number)
+//
+func (fn *formulaFuncs) CSCH(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("CSCH requires 1 numeric argument")
+ return
+ }
+ var val float64
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if val == 0 {
+ err = errors.New(formulaErrorNAME)
+ return
+ }
+ result = fmt.Sprintf("%g", 1/math.Sinh(val))
+ return
+}
+
+// DECIMAL function converts a text representation of a number in a specified
+// base, into a decimal value. The syntax of the function is:
+//
+// DECIMAL(text,radix)
+//
+func (fn *formulaFuncs) DECIMAL(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 2 {
+ err = errors.New("DECIMAL requires 2 numeric arguments")
+ return
+ }
+ var text = argsList.Front().Value.(formulaArg).Value
+ var radix int
+ if radix, err = strconv.Atoi(argsList.Back().Value.(formulaArg).Value); err != nil {
+ return
+ }
+ if len(text) > 2 && (strings.HasPrefix(text, "0x") || strings.HasPrefix(text, "0X")) {
+ text = text[2:]
+ }
+ val, err := strconv.ParseInt(text, radix, 64)
+ if err != nil {
+ err = errors.New(formulaErrorNUM)
+ return
+ }
+ result = fmt.Sprintf("%g", float64(val))
+ return
+}
+
+// DEGREES function converts radians into degrees. The syntax of the function
+// is:
+//
+// DEGREES(angle)
+//
+func (fn *formulaFuncs) DEGREES(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("DEGREES requires 1 numeric argument")
+ return
+ }
+ var val float64
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if val == 0 {
+ err = errors.New(formulaErrorNAME)
+ return
+ }
+ result = fmt.Sprintf("%g", 180.0/math.Pi*val)
+ return
+}
+
+// EVEN function rounds a supplied number away from zero (i.e. rounds a
+// positive number up and a negative number down), to the next even number.
+// The syntax of the function is:
+//
+// EVEN(number)
+//
+func (fn *formulaFuncs) EVEN(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("EVEN requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ sign := math.Signbit(number)
+ m, frac := math.Modf(number / 2)
+ val := m * 2
+ if frac != 0 {
+ if !sign {
+ val += 2
+ } else {
+ val -= 2
+ }
+ }
+ result = fmt.Sprintf("%g", val)
+ return
+}
+
+// EXP function calculates the value of the mathematical constant e, raised to
+// the power of a given number. The syntax of the function is:
+//
+// EXP(number)
+//
+func (fn *formulaFuncs) EXP(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("EXP requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ result = strings.ToUpper(fmt.Sprintf("%g", math.Exp(number)))
+ return
+}
+
+// fact returns the factorial of a supplied number.
+func fact(number float64) float64 {
+ val := float64(1)
+ for i := float64(2); i <= number; i++ {
+ val *= i
+ }
+ return val
+}
+
+// FACT function returns the factorial of a supplied number. The syntax of the
+// function is:
+//
+// FACT(number)
+//
+func (fn *formulaFuncs) FACT(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("FACT requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if number < 0 {
+ err = errors.New(formulaErrorNUM)
+ }
+ result = strings.ToUpper(fmt.Sprintf("%g", fact(number)))
+ return
+}
+
+// FACTDOUBLE function returns the double factorial of a supplied number. The
+// syntax of the function is:
+//
+// FACTDOUBLE(number)
+//
+func (fn *formulaFuncs) FACTDOUBLE(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("FACTDOUBLE requires 1 numeric argument")
+ return
+ }
+ var number, val float64 = 0, 1
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if number < 0 {
+ err = errors.New(formulaErrorNUM)
+ }
+ for i := math.Trunc(number); i > 1; i -= 2 {
+ val *= i
+ }
+ result = strings.ToUpper(fmt.Sprintf("%g", val))
+ return
+}
+
+// FLOOR function rounds a supplied number towards zero to the nearest
+// multiple of a specified significance. The syntax of the function is:
+//
+// FLOOR(number,significance)
+//
+func (fn *formulaFuncs) FLOOR(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 2 {
+ err = errors.New("FLOOR requires 2 numeric arguments")
+ return
+ }
+ var number, significance float64 = 0, 1
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if significance < 0 && number >= 0 {
+ err = errors.New(formulaErrorNUM)
+ }
+ val := number
+ val, res := math.Modf(val / significance)
+ if res != 0 {
+ if number < 0 && res < 0 {
+ val--
+ }
+ }
+ result = strings.ToUpper(fmt.Sprintf("%g", val*significance))
+ return
+}
+
+// FLOORMATH function rounds a supplied number down to a supplied multiple of
+// significance. The syntax of the function is:
+//
+// FLOOR.MATH(number,[significance],[mode])
+//
+func (fn *formulaFuncs) FLOORMATH(argsList *list.List) (result string, err error) {
+ if argsList.Len() == 0 {
+ err = errors.New("FLOOR.MATH requires at least 1 argument")
+ return
+ }
+ if argsList.Len() > 3 {
+ err = errors.New("FLOOR.MATH allows at most 3 arguments")
+ return
+ }
+ var number, significance, mode float64 = 0, 1, 1
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if number < 0 {
+ significance = -1
+ }
+ if argsList.Len() > 1 {
+ if significance, err = strconv.ParseFloat(argsList.Front().Next().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ }
+ if argsList.Len() == 1 {
+ result = fmt.Sprintf("%g", math.Floor(number))
+ return
+ }
+ if argsList.Len() > 2 {
+ if mode, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ }
+ val, res := math.Modf(number / significance)
+ if res != 0 && number < 0 && mode > 0 {
+ val--
+ }
+ result = fmt.Sprintf("%g", val*significance)
+ return
+}
+
+// FLOORPRECISE function rounds a supplied number down to a supplied multiple
+// of significance. The syntax of the function is:
+//
+// FLOOR.PRECISE(number,[significance])
+//
+func (fn *formulaFuncs) FLOORPRECISE(argsList *list.List) (result string, err error) {
+ if argsList.Len() == 0 {
+ err = errors.New("FLOOR.PRECISE requires at least 1 argument")
+ return
+ }
+ if argsList.Len() > 2 {
+ err = errors.New("FLOOR.PRECISE allows at most 2 arguments")
+ return
+ }
+ var number, significance float64 = 0, 1
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if number < 0 {
+ significance = -1
+ }
+ if argsList.Len() == 1 {
+ result = fmt.Sprintf("%g", math.Floor(number))
+ return
+ }
+ if argsList.Len() > 1 {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ significance = math.Abs(significance)
+ if significance == 0 {
+ result = "0"
+ return
+ }
+ }
+ val, res := math.Modf(number / significance)
+ if res != 0 {
+ if number < 0 {
+ val--
+ }
+ }
+ result = fmt.Sprintf("%g", val*significance)
+ return
+}
+
// GCD function returns the greatest common divisor of two or more supplied
// integers. The syntax of the function is:
//
@@ -1114,11 +1419,11 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) {
nums = []float64{}
)
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
- token := arg.Value.(efp.Token)
- if token.TValue == "" {
+ token := arg.Value.(formulaArg).Value
+ if token == "" {
continue
}
- if val, err = strconv.ParseFloat(token.TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(token, 64); err != nil {
return
}
nums = append(nums, val)
@@ -1143,6 +1448,74 @@ func (fn *formulaFuncs) GCD(argsList *list.List) (result string, err error) {
return
}
+// INT function truncates a supplied number down to the closest integer. The
+// syntax of the function is:
+//
+// INT(number)
+//
+func (fn *formulaFuncs) INT(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("INT requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ val, frac := math.Modf(number)
+ if frac < 0 {
+ val--
+ }
+ result = fmt.Sprintf("%g", val)
+ return
+}
+
+// ISOCEILING function rounds a supplied number up (regardless of the number's
+// sign), to the nearest multiple of a supplied significance. The syntax of
+// the function is:
+//
+// ISO.CEILING(number,[significance])
+//
+func (fn *formulaFuncs) ISOCEILING(argsList *list.List) (result string, err error) {
+ if argsList.Len() == 0 {
+ err = errors.New("ISO.CEILING requires at least 1 argument")
+ return
+ }
+ if argsList.Len() > 2 {
+ err = errors.New("ISO.CEILING allows at most 2 arguments")
+ return
+ }
+ var number, significance float64 = 0, 1
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if number < 0 {
+ significance = -1
+ }
+ if argsList.Len() == 1 {
+ result = fmt.Sprintf("%g", math.Ceil(number))
+ return
+ }
+ if argsList.Len() > 1 {
+ if significance, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ significance = math.Abs(significance)
+ if significance == 0 {
+ result = "0"
+ return
+ }
+ }
+ val, res := math.Modf(number / significance)
+ if res != 0 {
+ if number > 0 {
+ val++
+ }
+ }
+ result = fmt.Sprintf("%g", val*significance)
+ return
+}
+
// lcm returns the least common multiple of two supplied integers.
func lcm(a, b float64) float64 {
a = math.Trunc(a)
@@ -1168,11 +1541,11 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) {
nums = []float64{}
)
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
- token := arg.Value.(efp.Token)
- if token.TValue == "" {
+ token := arg.Value.(formulaArg).Value
+ if token == "" {
continue
}
- if val, err = strconv.ParseFloat(token.TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(token, 64); err != nil {
return
}
nums = append(nums, val)
@@ -1197,6 +1570,151 @@ func (fn *formulaFuncs) LCM(argsList *list.List) (result string, err error) {
return
}
+// LN function calculates the natural logarithm of a given number. The syntax
+// of the function is:
+//
+// LN(number)
+//
+func (fn *formulaFuncs) LN(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("LN requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ result = fmt.Sprintf("%g", math.Log(number))
+ return
+}
+
+// LOG function calculates the logarithm of a given number, to a supplied
+// base. The syntax of the function is:
+//
+// LOG(number,[base])
+//
+func (fn *formulaFuncs) LOG(argsList *list.List) (result string, err error) {
+ if argsList.Len() == 0 {
+ err = errors.New("LOG requires at least 1 argument")
+ return
+ }
+ if argsList.Len() > 2 {
+ err = errors.New("LOG allows at most 2 arguments")
+ return
+ }
+ var number, base float64 = 0, 10
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ if argsList.Len() > 1 {
+ if base, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ }
+ if number == 0 {
+ err = errors.New(formulaErrorNUM)
+ return
+ }
+ if base == 0 {
+ err = errors.New(formulaErrorNUM)
+ return
+ }
+ if base == 1 {
+ err = errors.New(formulaErrorDIV)
+ return
+ }
+ result = fmt.Sprintf("%g", math.Log(number)/math.Log(base))
+ return
+}
+
+// LOG10 function calculates the base 10 logarithm of a given number. The
+// syntax of the function is:
+//
+// LOG10(number)
+//
+func (fn *formulaFuncs) LOG10(argsList *list.List) (result string, err error) {
+ if argsList.Len() != 1 {
+ err = errors.New("LOG10 requires 1 numeric argument")
+ return
+ }
+ var number float64
+ if number, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
+ return
+ }
+ result = fmt.Sprintf("%g", math.Log10(number))
+ return
+}
+
+func minor(sqMtx [][]float64, idx int) [][]float64 {
+ ret := [][]float64{}
+ for i := range sqMtx {
+ if i == 0 {
+ continue
+ }
+ row := []float64{}
+ for j := range sqMtx {
+ if j == idx {
+ continue
+ }
+ row = append(row, sqMtx[i][j])
+ }
+ ret = append(ret, row)
+ }
+ return ret
+}
+
+// det determinant of the 2x2 matrix.
+func det(sqMtx [][]float64) float64 {
+ if len(sqMtx) == 2 {
+ m00 := sqMtx[0][0]
+ m01 := sqMtx[0][1]
+ m10 := sqMtx[1][0]
+ m11 := sqMtx[1][1]
+ return m00*m11 - m10*m01
+ }
+ var res, sgn float64 = 0, 1
+ for j := range sqMtx {
+ res += sgn * sqMtx[0][j] * det(minor(sqMtx, j))
+ sgn *= -1
+ }
+ return res
+}
+
+// MDETERM calculates the determinant of a square matrix. The
+// syntax of the function is:
+//
+// MDETERM(array)
+//
+func (fn *formulaFuncs) MDETERM(argsList *list.List) (result string, err error) {
+ var num float64
+ var rows int
+ var numMtx = [][]float64{}
+ var strMtx = [][]string{}
+ for arg := argsList.Front(); arg != nil; arg = arg.Next() {
+ if len(arg.Value.(formulaArg).Matrix) == 0 {
+ break
+ }
+ strMtx = append(strMtx, arg.Value.(formulaArg).Matrix)
+ rows++
+ }
+ for _, row := range strMtx {
+ if len(row) != rows {
+ err = errors.New(formulaErrorVALUE)
+ return
+ }
+ numRow := []float64{}
+ for _, ele := range row {
+ if num, err = strconv.ParseFloat(ele, 64); err != nil {
+ return
+ }
+ numRow = append(numRow, num)
+ }
+ numMtx = append(numMtx, numRow)
+ }
+ result = fmt.Sprintf("%g", det(numMtx))
+ return
+}
+
// POWER function calculates a given number, raised to a supplied power.
// The syntax of the function is:
//
@@ -1208,10 +1726,10 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) {
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
- if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
if x == 0 && y == 0 {
@@ -1234,11 +1752,11 @@ func (fn *formulaFuncs) POWER(argsList *list.List) (result string, err error) {
func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error) {
var val, product float64 = 0, 1
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
- token := arg.Value.(efp.Token)
- if token.TValue == "" {
+ token := arg.Value.(formulaArg)
+ if token.Value == "" {
continue
}
- if val, err = strconv.ParseFloat(token.TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
return
}
product = product * val
@@ -1256,11 +1774,11 @@ func (fn *formulaFuncs) PRODUCT(argsList *list.List) (result string, err error)
//
func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("SIGN requires 1 numeric arguments")
+ err = errors.New("SIGN requires 1 numeric argument")
return
}
var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
if val < 0 {
@@ -1282,18 +1800,23 @@ func (fn *formulaFuncs) SIGN(argsList *list.List) (result string, err error) {
//
func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) {
if argsList.Len() != 1 {
- err = errors.New("SQRT requires 1 numeric arguments")
+ err = errors.New("SQRT requires 1 numeric argument")
return
}
- var val float64
- if val, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ var res float64
+ var value = argsList.Front().Value.(formulaArg).Value
+ if value == "" {
+ result = "0"
return
}
- if val < 0 {
+ if res, err = strconv.ParseFloat(value, 64); err != nil {
+ return
+ }
+ if res < 0 {
err = errors.New(formulaErrorNUM)
return
}
- result = fmt.Sprintf("%g", math.Sqrt(val))
+ result = fmt.Sprintf("%g", math.Sqrt(res))
return
}
@@ -1305,11 +1828,11 @@ func (fn *formulaFuncs) SQRT(argsList *list.List) (result string, err error) {
func (fn *formulaFuncs) SUM(argsList *list.List) (result string, err error) {
var val, sum float64
for arg := argsList.Front(); arg != nil; arg = arg.Next() {
- token := arg.Value.(efp.Token)
- if token.TValue == "" {
+ token := arg.Value.(formulaArg)
+ if token.Value == "" {
continue
}
- if val, err = strconv.ParseFloat(token.TValue, 64); err != nil {
+ if val, err = strconv.ParseFloat(token.Value, 64); err != nil {
return
}
sum += val
@@ -1329,10 +1852,10 @@ func (fn *formulaFuncs) QUOTIENT(argsList *list.List) (result string, err error)
return
}
var x, y float64
- if x, err = strconv.ParseFloat(argsList.Front().Value.(efp.Token).TValue, 64); err != nil {
+ if x, err = strconv.ParseFloat(argsList.Front().Value.(formulaArg).Value, 64); err != nil {
return
}
- if y, err = strconv.ParseFloat(argsList.Back().Value.(efp.Token).TValue, 64); err != nil {
+ if y, err = strconv.ParseFloat(argsList.Back().Value.(formulaArg).Value, 64); err != nil {
return
}
if y == 0 {
diff --git a/calc_test.go b/calc_test.go
index 5d441ff..c66de8c 100644
--- a/calc_test.go
+++ b/calc_test.go
@@ -14,6 +14,7 @@ func TestCalcCellValue(t *testing.T) {
f.SetCellValue("Sheet1", "A3", 3)
f.SetCellValue("Sheet1", "A4", 0)
f.SetCellValue("Sheet1", "B1", 4)
+ f.SetCellValue("Sheet1", "B2", 5)
return f
}
@@ -115,22 +116,108 @@ func TestCalcCellValue(t *testing.T) {
"=COSH(0.5)": "1.1276259652063807",
"=COSH(-2)": "3.7621956910836314",
// _xlfn.COT
- "_xlfn.COT(0.785398163397448)": "0.9999999999999992",
+ "=_xlfn.COT(0.785398163397448)": "0.9999999999999992",
// _xlfn.COTH
- "_xlfn.COTH(-3.14159265358979)": "-0.9962720762207499",
+ "=_xlfn.COTH(-3.14159265358979)": "-0.9962720762207499",
// _xlfn.CSC
- "_xlfn.CSC(-6)": "3.5788995472544056",
- "_xlfn.CSC(1.5707963267949)": "1",
+ "=_xlfn.CSC(-6)": "3.5788995472544056",
+ "=_xlfn.CSC(1.5707963267949)": "1",
+ // _xlfn.CSCH
+ "=_xlfn.CSCH(-3.14159265358979)": "-0.08658953753004724",
+ // _xlfn.DECIMAL
+ `=_xlfn.DECIMAL("1100",2)`: "12",
+ `=_xlfn.DECIMAL("186A0",16)`: "100000",
+ `=_xlfn.DECIMAL("31L0",32)`: "100000",
+ `=_xlfn.DECIMAL("70122",8)`: "28754",
+ // DEGREES
+ "=DEGREES(1)": "57.29577951308232",
+ "=DEGREES(2.5)": "143.2394487827058",
+ // EVEN
+ "=EVEN(23)": "24",
+ "=EVEN(2.22)": "4",
+ "=EVEN(0)": "0",
+ "=EVEN(-0.3)": "-2",
+ "=EVEN(-11)": "-12",
+ "=EVEN(-4)": "-4",
+ // EXP
+ "=EXP(100)": "2.6881171418161356E+43",
+ "=EXP(0.1)": "1.1051709180756477",
+ "=EXP(0)": "1",
+ "=EXP(-5)": "0.006737946999085467",
+ // FACT
+ "=FACT(3)": "6",
+ "=FACT(6)": "720",
+ "=FACT(10)": "3.6288E+06",
+ // FACTDOUBLE
+ "=FACTDOUBLE(5)": "15",
+ "=FACTDOUBLE(8)": "384",
+ "=FACTDOUBLE(13)": "135135",
+ // FLOOR
+ "=FLOOR(26.75,0.1)": "26.700000000000003",
+ "=FLOOR(26.75,0.5)": "26.5",
+ "=FLOOR(26.75,1)": "26",
+ "=FLOOR(26.75,10)": "20",
+ "=FLOOR(26.75,20)": "20",
+ "=FLOOR(-26.75,-0.1)": "-26.700000000000003",
+ "=FLOOR(-26.75,-1)": "-26",
+ "=FLOOR(-26.75,-5)": "-25",
+ // _xlfn.FLOOR.MATH
+ "=_xlfn.FLOOR.MATH(58.55)": "58",
+ "=_xlfn.FLOOR.MATH(58.55,0.1)": "58.5",
+ "=_xlfn.FLOOR.MATH(58.55,5)": "55",
+ "=_xlfn.FLOOR.MATH(58.55,1,1)": "58",
+ "=_xlfn.FLOOR.MATH(-58.55,1)": "-59",
+ "=_xlfn.FLOOR.MATH(-58.55,1,-1)": "-58",
+ "=_xlfn.FLOOR.MATH(-58.55,1,1)": "-59", // should be -58
+ "=_xlfn.FLOOR.MATH(-58.55,10)": "-60",
+ // _xlfn.FLOOR.PRECISE
+ "=_xlfn.FLOOR.PRECISE(26.75,0.1)": "26.700000000000003",
+ "=_xlfn.FLOOR.PRECISE(26.75,0.5)": "26.5",
+ "=_xlfn.FLOOR.PRECISE(26.75,1)": "26",
+ "=_xlfn.FLOOR.PRECISE(26.75)": "26",
+ "=_xlfn.FLOOR.PRECISE(26.75,10)": "20",
+ "=_xlfn.FLOOR.PRECISE(26.75,0)": "0",
+ "=_xlfn.FLOOR.PRECISE(-26.75,1)": "-27",
+ "=_xlfn.FLOOR.PRECISE(-26.75,-1)": "-27",
+ "=_xlfn.FLOOR.PRECISE(-26.75,-5)": "-30",
// GCD
"=GCD(1,5)": "1",
"=GCD(15,10,25)": "5",
"=GCD(0,8,12)": "4",
"=GCD(7,2)": "1",
+ // INT
+ "=INT(100.9)": "100",
+ "=INT(5.22)": "5",
+ "=INT(5.99)": "5",
+ "=INT(-6.1)": "-7",
+ "=INT(-100.9)": "-101",
+ // ISO.CEILING
+ "=ISO.CEILING(22.25)": "23",
+ "=ISO.CEILING(22.25,1)": "23",
+ "=ISO.CEILING(22.25,0.1)": "22.3",
+ "=ISO.CEILING(22.25,10)": "30",
+ "=ISO.CEILING(-22.25,1)": "-22",
+ "=ISO.CEILING(-22.25,0.1)": "-22.200000000000003",
+ "=ISO.CEILING(-22.25,5)": "-20",
// LCM
"=LCM(1,5)": "5",
"=LCM(15,10,25)": "150",
"=LCM(1,8,12)": "24",
"=LCM(7,2)": "14",
+ // LN
+ "=LN(1)": "0",
+ "=LN(100)": "4.605170185988092",
+ "=LN(0.5)": "-0.6931471805599453",
+ // LOG
+ "=LOG(64,2)": "6",
+ "=LOG(100)": "2",
+ "=LOG(4,0.5)": "-2",
+ "=LOG(500)": "2.6989700043360183",
+ // LOG10
+ "=LOG10(100)": "2",
+ "=LOG10(1000)": "3",
+ "=LOG10(0.001)": "-3",
+ "=LOG10(25)": "1.3979400086720375",
// POWER
"=POWER(4,2)": "16",
// PRODUCT
@@ -171,26 +258,26 @@ func TestCalcCellValue(t *testing.T) {
}
mathCalcError := map[string]string{
// ABS
- "=ABS()": "ABS requires 1 numeric arguments",
+ "=ABS()": "ABS requires 1 numeric argument",
"=ABS(~)": `cannot convert cell "~" to coordinates: invalid cell name "~"`,
// ACOS
- "=ACOS()": "ACOS requires 1 numeric arguments",
+ "=ACOS()": "ACOS requires 1 numeric argument",
// ACOSH
- "=ACOSH()": "ACOSH requires 1 numeric arguments",
+ "=ACOSH()": "ACOSH requires 1 numeric argument",
// _xlfn.ACOT
- "=_xlfn.ACOT()": "ACOT requires 1 numeric arguments",
+ "=_xlfn.ACOT()": "ACOT requires 1 numeric argument",
// _xlfn.ACOTH
- "=_xlfn.ACOTH()": "ACOTH requires 1 numeric arguments",
+ "=_xlfn.ACOTH()": "ACOTH requires 1 numeric argument",
// _xlfn.ARABIC
- "=_xlfn.ARABIC()": "ARABIC requires 1 numeric arguments",
+ "=_xlfn.ARABIC()": "ARABIC requires 1 numeric argument",
// ASIN
- "=ASIN()": "ASIN requires 1 numeric arguments",
+ "=ASIN()": "ASIN requires 1 numeric argument",
// ASINH
- "=ASINH()": "ASINH requires 1 numeric arguments",
+ "=ASINH()": "ASINH requires 1 numeric argument",
// ATAN
- "=ATAN()": "ATAN requires 1 numeric arguments",
+ "=ATAN()": "ATAN requires 1 numeric argument",
// ATANH
- "=ATANH()": "ATANH requires 1 numeric arguments",
+ "=ATANH()": "ATANH requires 1 numeric argument",
// ATAN2
"=ATAN2()": "ATAN2 requires 2 numeric arguments",
// BASE
@@ -215,33 +302,75 @@ func TestCalcCellValue(t *testing.T) {
"=_xlfn.COMBINA(-1,1)": "COMBINA requires number > number_chosen",
"=_xlfn.COMBINA(-1,-1)": "COMBIN requires number >= number_chosen",
// COS
- "=COS()": "COS requires 1 numeric arguments",
+ "=COS()": "COS requires 1 numeric argument",
// COSH
- "=COSH()": "COSH requires 1 numeric arguments",
+ "=COSH()": "COSH requires 1 numeric argument",
// _xlfn.COT
- "=COT()": "COT requires 1 numeric arguments",
+ "=COT()": "COT requires 1 numeric argument",
// _xlfn.COTH
- "=COTH()": "COTH requires 1 numeric arguments",
+ "=COTH()": "COTH requires 1 numeric argument",
// _xlfn.CSC
- "_xlfn.CSC()": "CSC requires 1 numeric arguments",
- "_xlfn.CSC(0)": "#NAME?",
+ "=_xlfn.CSC()": "CSC requires 1 numeric argument",
+ "=_xlfn.CSC(0)": "#NAME?",
+ // _xlfn.CSCH
+ "=_xlfn.CSCH()": "CSCH requires 1 numeric argument",
+ "=_xlfn.CSCH(0)": "#NAME?",
+ // _xlfn.DECIMAL
+ "=_xlfn.DECIMAL()": "DECIMAL requires 2 numeric arguments",
+ `=_xlfn.DECIMAL("2000", 2)`: "#NUM!",
+ // DEGREES
+ "=DEGREES()": "DEGREES requires 1 numeric argument",
+ // EVEN
+ "=EVEN()": "EVEN requires 1 numeric argument",
+ // EXP
+ "=EXP()": "EXP requires 1 numeric argument",
+ // FACT
+ "=FACT()": "FACT requires 1 numeric argument",
+ "=FACT(-1)": "#NUM!",
+ // FACTDOUBLE
+ "=FACTDOUBLE()": "FACTDOUBLE requires 1 numeric argument",
+ "=FACTDOUBLE(-1)": "#NUM!",
+ // FLOOR
+ "=FLOOR()": "FLOOR requires 2 numeric arguments",
+ "=FLOOR(1,-1)": "#NUM!",
+ // _xlfn.FLOOR.MATH
+ "=_xlfn.FLOOR.MATH()": "FLOOR.MATH requires at least 1 argument",
+ "=_xlfn.FLOOR.MATH(1,2,3,4)": "FLOOR.MATH allows at most 3 arguments",
+ // _xlfn.FLOOR.PRECISE
+ "=_xlfn.FLOOR.PRECISE()": "FLOOR.PRECISE requires at least 1 argument",
+ "=_xlfn.FLOOR.PRECISE(1,2,3)": "FLOOR.PRECISE allows at most 2 arguments",
// GCD
"=GCD()": "GCD requires at least 1 argument",
"=GCD(-1)": "GCD only accepts positive arguments",
"=GCD(1,-1)": "GCD only accepts positive arguments",
+ // INT
+ "=INT()": "INT requires 1 numeric argument",
+ // ISO.CEILING
+ "=ISO.CEILING()": "ISO.CEILING requires at least 1 argument",
+ "=ISO.CEILING(1,2,3)": "ISO.CEILING allows at most 2 arguments",
// LCM
"=LCM()": "LCM requires at least 1 argument",
"=LCM(-1)": "LCM only accepts positive arguments",
"=LCM(1,-1)": "LCM only accepts positive arguments",
+ // LN
+ "=LN()": "LN requires 1 numeric argument",
+ // LOG
+ "=LOG()": "LOG requires at least 1 argument",
+ "=LOG(1,2,3)": "LOG allows at most 2 arguments",
+ "=LOG(0,0)": "#NUM!",
+ "=LOG(1,0)": "#NUM!",
+ "=LOG(1,1)": "#DIV/0!",
+ // LOG10
+ "=LOG10()": "LOG10 requires 1 numeric argument",
// POWER
"=POWER(0,0)": "#NUM!",
"=POWER(0,-1)": "#DIV/0!",
"=POWER(1)": "POWER requires 2 numeric arguments",
// SIGN
- "=SIGN()": "SIGN requires 1 numeric arguments",
+ "=SIGN()": "SIGN requires 1 numeric argument",
// SQRT
"=SQRT(-1)": "#NUM!",
- "=SQRT(1,2)": "SQRT requires 1 numeric arguments",
+ "=SQRT(1,2)": "SQRT requires 1 numeric argument",
// QUOTIENT
"=QUOTIENT(1,0)": "#DIV/0!",
"=QUOTIENT(1)": "QUOTIENT requires 2 numeric arguments",
@@ -255,6 +384,8 @@ func TestCalcCellValue(t *testing.T) {
}
referenceCalc := map[string]string{
+ // MDETERM
+ "=MDETERM(A1:B2)": "-3",
// PRODUCT
"=PRODUCT(Sheet1!A1:Sheet1!A1:A2,A2)": "4",
// SUM
@@ -277,6 +408,9 @@ func TestCalcCellValue(t *testing.T) {
}
referenceCalcError := map[string]string{
+ // MDETERM
+ "=MDETERM(A1:B3)": "#VALUE!",
+ // SUM
"=1+SUM(SUM(A1+A2/A4)*(2-3),2)": "#DIV/0!",
}
for formula, expected := range referenceCalcError {
generated by cgit v1.2.1 (git 2.18.0) at 2025-04-19 18:27:44 +0000