diff options
| -rw-r--r-- | calc.go | 98 | ||||
| -rw-r--r-- | calc_test.go | 32 |
2 files changed, 130 insertions, 0 deletions
@@ -17,6 +17,7 @@ import ( "errors" "fmt" "math" + "math/big" "math/cmplx" "math/rand" "net/url" @@ -552,9 +553,11 @@ type formulaFuncs struct { // UNICHAR // UNICODE // UPPER +// VALUE // VAR.P // VARP // VLOOKUP +// WEEKDAY // WEIBULL // WEIBULL.DIST // XOR @@ -7187,6 +7190,63 @@ func daysBetween(startDate, endDate int64) float64 { return float64(int(0.5 + float64((endDate-startDate)/86400))) } +// WEEKDAY function returns an integer representing the day of the week for a +// supplied date. The syntax of the function is: +// +// WEEKDAY(serial_number,[return_type]) +// +func (fn *formulaFuncs) WEEKDAY(argsList *list.List) formulaArg { + if argsList.Len() < 1 { + return newErrorFormulaArg(formulaErrorVALUE, "WEEKDAY requires at least 1 argument") + } + if argsList.Len() > 2 { + return newErrorFormulaArg(formulaErrorVALUE, "WEEKDAY allows at most 2 arguments") + } + + sn := argsList.Front().Value.(formulaArg) + num := sn.ToNumber() + weekday, returnType := 0, 1 + if num.Type != ArgNumber { + dateString := strings.ToLower(sn.Value()) + if !isDateOnlyFmt(dateString) { + if _, _, _, _, _, err := strToTime(dateString); err.Type == ArgError { + return err + } + } + y, m, d, _, err := strToDate(dateString) + if err.Type == ArgError { + return err + } + weekday = int(time.Date(y, time.Month(m), d, 0, 0, 0, 0, time.Now().Location()).Weekday()) + } else { + if num.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, formulaErrorNUM) + } + weekday = int(timeFromExcelTime(num.Number, false).Weekday()) + } + if argsList.Len() == 2 { + returnTypeArg := argsList.Back().Value.(formulaArg).ToNumber() + if returnTypeArg.Type != ArgNumber { + return returnTypeArg + } + returnType = int(returnTypeArg.Number) + } + if returnType == 2 { + returnType = 11 + } + weekday++ + if returnType == 1 { + return newNumberFormulaArg(float64(weekday)) + } + if returnType == 3 { + return newNumberFormulaArg(float64((weekday + 6 - 1) % 7)) + } + if returnType >= 11 && returnType <= 17 { + return newNumberFormulaArg(float64((weekday+6-(returnType-10))%7 + 1)) + } + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) +} + // Text Functions // CHAR function returns the character relating to a supplied character set @@ -7831,6 +7891,44 @@ func (fn *formulaFuncs) UPPER(argsList *list.List) formulaArg { return newStringFormulaArg(strings.ToUpper(argsList.Front().Value.(formulaArg).String)) } +// VALUE function converts a text string into a numeric value. The syntax of +// the function is: +// +// VALUE(text) +// +func (fn *formulaFuncs) VALUE(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "VALUE requires 1 argument") + } + text := strings.ReplaceAll(argsList.Front().Value.(formulaArg).Value(), ",", "") + percent := 1.0 + if strings.HasSuffix(text, "%") { + percent, text = 0.01, strings.TrimSuffix(text, "%") + } + decimal := big.Float{} + if _, ok := decimal.SetString(text); ok { + value, _ := decimal.Float64() + return newNumberFormulaArg(value * percent) + } + dateValue, timeValue, errTime, errDate := 0.0, 0.0, false, false + if !isDateOnlyFmt(text) { + h, m, s, _, _, err := strToTime(text) + errTime = err.Type == ArgError + if !errTime { + timeValue = (float64(h)*3600 + float64(m)*60 + s) / 86400 + } + } + y, m, d, _, err := strToDate(text) + errDate = err.Type == ArgError + if !errDate { + dateValue = daysBetween(excelMinTime1900.Unix(), makeDate(y, time.Month(m), d)) + 1 + } + if errTime && errDate { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + return newNumberFormulaArg(dateValue + timeValue) +} + // Conditional Functions // IF function tests a supplied condition and returns one result if the diff --git a/calc_test.go b/calc_test.go index 5661145..af27079 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1052,6 +1052,20 @@ func TestCalcCellValue(t *testing.T) { "=TIME(5,44,32)": "0.239259259259259", "=TIME(\"5\",\"44\",\"32\")": "0.239259259259259", "=TIME(0,0,73)": "0.000844907407407407", + // WEEKDAY + "=WEEKDAY(0)": "7", + "=WEEKDAY(47119)": "2", + "=WEEKDAY(\"12/25/2012\")": "3", + "=WEEKDAY(\"12/25/2012\",1)": "3", + "=WEEKDAY(\"12/25/2012\",2)": "2", + "=WEEKDAY(\"12/25/2012\",3)": "1", + "=WEEKDAY(\"12/25/2012\",11)": "2", + "=WEEKDAY(\"12/25/2012\",12)": "1", + "=WEEKDAY(\"12/25/2012\",13)": "7", + "=WEEKDAY(\"12/25/2012\",14)": "6", + "=WEEKDAY(\"12/25/2012\",15)": "5", + "=WEEKDAY(\"12/25/2012\",16)": "4", + "=WEEKDAY(\"12/25/2012\",17)": "3", // Text Functions // CHAR "=CHAR(65)": "A", @@ -1194,6 +1208,13 @@ func TestCalcCellValue(t *testing.T) { "=UPPER(\"TEST\")": "TEST", "=UPPER(\"Test\")": "TEST", "=UPPER(\"TEST 123\")": "TEST 123", + // VALUE + "=VALUE(\"50\")": "50", + "=VALUE(\"1.0E-07\")": "1e-07", + "=VALUE(\"5,000\")": "5000", + "=VALUE(\"20%\")": "0.2", + "=VALUE(\"12:00:00\")": "0.5", + "=VALUE(\"01/02/2006 15:04:05\")": "38719.62783564815", // Conditional Functions // IF "=IF(1=1)": "TRUE", @@ -2224,6 +2245,14 @@ func TestCalcCellValue(t *testing.T) { "=TIME(0,0,-1)": "#NUM!", // TODAY "=TODAY(A1)": "TODAY accepts no arguments", + // WEEKDAY + "=WEEKDAY()": "WEEKDAY requires at least 1 argument", + "=WEEKDAY(0,1,0)": "WEEKDAY allows at most 2 arguments", + "=WEEKDAY(0,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=WEEKDAY(\"\",1)": "#VALUE!", + "=WEEKDAY(0,0)": "#VALUE!", + "=WEEKDAY(\"January 25, 100\")": "#VALUE!", + "=WEEKDAY(-1,1)": "#NUM!", // Text Functions // CHAR "=CHAR()": "CHAR requires 1 argument", @@ -2337,6 +2366,9 @@ func TestCalcCellValue(t *testing.T) { // UNICODE "=UNICODE()": "UNICODE requires 1 argument", "=UNICODE(\"\")": "#VALUE!", + // VALUE + "=VALUE()": "VALUE requires 1 argument", + "=VALUE(\"\")": "#VALUE!", // UPPER "=UPPER()": "UPPER requires 1 argument", "=UPPER(1,2)": "UPPER requires 1 argument", |