From 471c8f22d0ac6cc17915eea25d171e578c06ac7d Mon Sep 17 00:00:00 2001 From: xuri Date: Sat, 26 Feb 2022 21:32:57 +0800 Subject: This closes #1160, and added 4 new formula functions * Fix show sheet tabs issue * Ref #65, new formula functions: ERROR.TYPE, HOUR, SECOND TIMEVALUE --- calc.go | 147 +++++++++++++++++++++++++++++++++++++++++++++++++++++++-- calc_test.go | 63 ++++++++++++++++++++++--- xmlWorkbook.go | 20 ++++---- 3 files changed, 208 insertions(+), 22 deletions(-) diff --git a/calc.go b/calc.go index a4c45fb..d4828be 100644 --- a/calc.go +++ b/calc.go @@ -401,6 +401,7 @@ type formulaFuncs struct { // ERF.PRECISE // ERFC // ERFC.PRECISE +// ERROR.TYPE // EVEN // EXACT // EXP @@ -427,6 +428,7 @@ type formulaFuncs struct { // HEX2DEC // HEX2OCT // HLOOKUP +// HOUR // IF // IFERROR // IFNA @@ -574,6 +576,7 @@ type formulaFuncs struct { // RRI // SEC // SECH +// SECOND // SHEET // SHEETS // SIGN @@ -604,6 +607,7 @@ type formulaFuncs struct { // TBILLYIELD // TEXTJOIN // TIME +// TIMEVALUE // TODAY // TRANSPOSE // TRIM @@ -852,7 +856,9 @@ func (f *File) evalInfixExpFunc(sheet, cell string, token, nextToken efp.Token, // calculate trigger topOpt := opftStack.Peek().(efp.Token) if err := calculate(opfdStack, topOpt); err != nil { - return err + argsStack.Peek().(*list.List).PushBack(newErrorFormulaArg(err.Error(), err.Error())) + opftStack.Pop() + continue } opftStack.Pop() } @@ -874,7 +880,11 @@ func (f *File) evalInfixExpFunc(sheet, cell string, token, nextToken efp.Token, if opfStack.Len() > 0 { // still in function stack if nextToken.TType == efp.TokenTypeOperatorInfix || (opftStack.Len() > 1 && opfdStack.Len() > 0) { // mathematics calculate in formula function - opfdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + if arg.Type == ArgError { + opfdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeError}) + } else { + opfdStack.Push(efp.Token{TValue: arg.Value(), TType: efp.TokenTypeOperand, TSubType: efp.TokenSubTypeNumber}) + } } else { argsStack.Peek().(*list.List).PushBack(arg) } @@ -1096,11 +1106,19 @@ func calculate(opdStack *Stack, opt efp.Token) error { fn, ok := tokenCalcFunc[opt.TValue] if ok { if opdStack.Len() < 2 { + if opdStack.Len() == 1 { + rOpd := opdStack.Pop().(efp.Token) + if rOpd.TSubType == efp.TokenSubTypeError { + return errors.New(rOpd.TValue) + } + } return ErrInvalidFormula } rOpd := opdStack.Pop().(efp.Token) lOpd := opdStack.Pop().(efp.Token) - + if lOpd.TSubType == efp.TokenSubTypeError { + return errors.New(lOpd.TValue) + } if err := fn(rOpd, lOpd, opdStack); err != nil { return err } @@ -4797,8 +4815,8 @@ func (fn *formulaFuncs) SUM(argsList *list.List) formulaArg { for arg := argsList.Front(); arg != nil; arg = arg.Next() { token := arg.Value.(formulaArg) switch token.Type { - case ArgUnknown: - continue + case ArgError: + return token case ArgString: if num := token.ToNumber(); num.Type == ArgNumber { sum += num.Number @@ -6787,6 +6805,29 @@ func (fn *formulaFuncs) ZTEST(argsList *list.List) formulaArg { // Information Functions +// ERRORdotTYPE function receives an error value and returns an integer, that +// tells you the type of the supplied error. The syntax of the function is: +// +// ERROR.TYPE(error_val) +// +func (fn *formulaFuncs) ERRORdotTYPE(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "ERROR.TYPE requires 1 argument") + } + token := argsList.Front().Value.(formulaArg) + if token.Type == ArgError { + for i, errType := range []string{ + formulaErrorNULL, formulaErrorDIV, formulaErrorVALUE, formulaErrorREF, + formulaErrorNAME, formulaErrorNUM, formulaErrorNA, + } { + if errType == token.String { + return newNumberFormulaArg(float64(i) + 1) + } + } + } + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) +} + // ISBLANK function tests if a specified cell is blank (empty) and if so, // returns TRUE; Otherwise the function returns FALSE. The syntax of the // function is: @@ -7884,6 +7925,40 @@ func (fn *formulaFuncs) ISOWEEKNUM(argsList *list.List) formulaArg { return newNumberFormulaArg(float64(weeknum)) } +// HOUR function returns an integer representing the hour component of a +// supplied Excel time. The syntax of the function is: +// +// HOUR(serial_number) +// +func (fn *formulaFuncs) HOUR(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "HOUR requires exactly 1 argument") + } + date := argsList.Front().Value.(formulaArg) + num := date.ToNumber() + if num.Type != ArgNumber { + timeString := strings.ToLower(date.Value()) + if !isTimeOnlyFmt(timeString) { + _, _, _, _, err := strToDate(timeString) + if err.Type == ArgError { + return err + } + } + h, _, _, pm, _, err := strToTime(timeString) + if err.Type == ArgError { + return err + } + if pm { + h += 12 + } + return newNumberFormulaArg(float64(h)) + } + if num.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, "HOUR only accepts positive argument") + } + return newNumberFormulaArg(float64(timeFromExcelTime(num.Number, false).Hour())) +} + // MINUTE function returns an integer representing the minute component of a // supplied Excel time. The syntax of the function is: // @@ -8131,6 +8206,37 @@ func (fn *formulaFuncs) NOW(argsList *list.List) formulaArg { return newNumberFormulaArg(25569.0 + float64(now.Unix()+int64(offset))/86400) } +// SECOND function returns an integer representing the second component of a +// supplied Excel time. The syntax of the function is: +// +// SECOND(serial_number) +// +func (fn *formulaFuncs) SECOND(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "SECOND requires exactly 1 argument") + } + date := argsList.Front().Value.(formulaArg) + num := date.ToNumber() + if num.Type != ArgNumber { + timeString := strings.ToLower(date.Value()) + if !isTimeOnlyFmt(timeString) { + _, _, _, _, err := strToDate(timeString) + if err.Type == ArgError { + return err + } + } + _, _, s, _, _, err := strToTime(timeString) + if err.Type == ArgError { + return err + } + return newNumberFormulaArg(float64(int(s) % 60)) + } + if num.Number < 0 { + return newErrorFormulaArg(formulaErrorNUM, "SECOND only accepts positive argument") + } + return newNumberFormulaArg(float64(timeFromExcelTime(num.Number, false).Second())) +} + // TIME function accepts three integer arguments representing hours, minutes // and seconds, and returns an Excel time. I.e. the function returns the // decimal value that represents the time in Excel. The syntax of the Time @@ -8155,6 +8261,37 @@ func (fn *formulaFuncs) TIME(argsList *list.List) formulaArg { return newNumberFormulaArg(t) } +// TIMEVALUE function converts a text representation of a time, into an Excel +// time. The syntax of the Timevalue function is: +// +// TIMEVALUE(time_text) +// +func (fn *formulaFuncs) TIMEVALUE(argsList *list.List) formulaArg { + if argsList.Len() != 1 { + return newErrorFormulaArg(formulaErrorVALUE, "TIMEVALUE requires exactly 1 argument") + } + date := argsList.Front().Value.(formulaArg) + timeString := strings.ToLower(date.Value()) + if !isTimeOnlyFmt(timeString) { + _, _, _, _, err := strToDate(timeString) + if err.Type == ArgError { + return err + } + } + h, m, s, pm, _, err := strToTime(timeString) + if err.Type == ArgError { + return err + } + if pm { + h += 12 + } + args := list.New() + args.PushBack(newNumberFormulaArg(float64(h))) + args.PushBack(newNumberFormulaArg(float64(m))) + args.PushBack(newNumberFormulaArg(s)) + return fn.TIME(args) +} + // TODAY function returns the current date. The function has no arguments and // therefore. The syntax of the function is: // diff --git a/calc_test.go b/calc_test.go index c3be308..e49b29e 100644 --- a/calc_test.go +++ b/calc_test.go @@ -990,6 +990,12 @@ func TestCalcCellValue(t *testing.T) { "=WEIBULL.DIST(1,3,1,FALSE)": "1.103638323514327", "=WEIBULL.DIST(2,5,1.5,TRUE)": "0.985212776817482", // Information Functions + // ERROR.TYPE + "=ERROR.TYPE(1/0)": "2", + "=ERROR.TYPE(COT(0))": "2", + "=ERROR.TYPE(XOR(\"text\"))": "3", + "=ERROR.TYPE(HEX2BIN(2,1))": "6", + "=ERROR.TYPE(NA())": "7", // ISBLANK "=ISBLANK(A1)": "FALSE", "=ISBLANK(A5)": "TRUE", @@ -1139,6 +1145,13 @@ func TestCalcCellValue(t *testing.T) { "=DAYS(2,1)": "1", "=DAYS(INT(2),INT(1))": "1", "=DAYS(\"02/02/2015\",\"01/01/2015\")": "32", + // HOUR + "=HOUR(1)": "0", + "=HOUR(43543.5032060185)": "12", + "=HOUR(\"43543.5032060185\")": "12", + "=HOUR(\"13:00:55\")": "13", + "=HOUR(\"1:00 PM\")": "13", + "=HOUR(\"12/09/2015 08:55\")": "8", // ISOWEEKNUM "=ISOWEEKNUM(42370)": "53", "=ISOWEEKNUM(\"42370\")": "53", @@ -1183,10 +1196,26 @@ func TestCalcCellValue(t *testing.T) { "=YEARFRAC(\"02/29/2000\", \"01/29/2001\",1)": "0.915300546448087", "=YEARFRAC(\"02/29/2000\", \"03/29/2000\",1)": "0.0792349726775956", "=YEARFRAC(\"01/31/2000\", \"03/29/2000\",4)": "0.163888888888889", + // SECOND + "=SECOND(\"13:35:55\")": "55", + "=SECOND(\"13:10:60\")": "0", + "=SECOND(\"13:10:61\")": "1", + "=SECOND(\"08:17:00\")": "0", + "=SECOND(\"12/09/2015 08:55\")": "0", + "=SECOND(\"12/09/2011 08:17:23\")": "23", + "=SECOND(\"43543.5032060185\")": "37", + "=SECOND(43543.5032060185)": "37", // TIME "=TIME(5,44,32)": "0.239259259259259", "=TIME(\"5\",\"44\",\"32\")": "0.239259259259259", "=TIME(0,0,73)": "0.000844907407407407", + // TIMEVALUE + "=TIMEVALUE(\"2:23\")": "0.0993055555555556", + "=TIMEVALUE(\"2:23 am\")": "0.0993055555555556", + "=TIMEVALUE(\"2:23 PM\")": "0.599305555555555", + "=TIMEVALUE(\"14:23:00\")": "0.599305555555555", + "=TIMEVALUE(\"00:02:23\")": "0.00165509259259259", + "=TIMEVALUE(\"01/01/2011 02:23\")": "0.0993055555555556", // WEEKDAY "=WEEKDAY(0)": "7", "=WEEKDAY(47119)": "2", @@ -2167,12 +2196,14 @@ func TestCalcCellValue(t *testing.T) { "=POISSON(0,0,\"\")": "strconv.ParseBool: parsing \"\": invalid syntax", "=POISSON(0,-1,TRUE)": "#N/A", // SUM - "=SUM((": ErrInvalidFormula.Error(), - "=SUM(-)": ErrInvalidFormula.Error(), - "=SUM(1+)": ErrInvalidFormula.Error(), - "=SUM(1-)": ErrInvalidFormula.Error(), - "=SUM(1*)": ErrInvalidFormula.Error(), - "=SUM(1/)": ErrInvalidFormula.Error(), + "=SUM((": ErrInvalidFormula.Error(), + "=SUM(-)": ErrInvalidFormula.Error(), + "=SUM(1+)": ErrInvalidFormula.Error(), + "=SUM(1-)": ErrInvalidFormula.Error(), + "=SUM(1*)": ErrInvalidFormula.Error(), + "=SUM(1/)": ErrInvalidFormula.Error(), + "=SUM(1*SUM(1/0))": "#DIV/0!", + "=SUM(1*SUM(1/0)*1)": "#DIV/0!", // SUMIF "=SUMIF()": "SUMIF requires at least 2 arguments", // SUMSQ @@ -2453,6 +2484,9 @@ func TestCalcCellValue(t *testing.T) { "=ZTEST(A1,1)": "#DIV/0!", "=ZTEST(A1,1,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // Information Functions + // ERROR.TYPE + "=ERROR.TYPE()": "ERROR.TYPE requires 1 argument", + "=ERROR.TYPE(1)": "#N/A", // ISBLANK "=ISBLANK(A1,A2)": "ISBLANK requires 1 argument", // ISERR @@ -2582,6 +2616,11 @@ func TestCalcCellValue(t *testing.T) { "=DAYS(0,\"\")": "#VALUE!", "=DAYS(NA(),0)": "#VALUE!", "=DAYS(0,NA())": "#VALUE!", + // HOUR + "=HOUR()": "HOUR requires exactly 1 argument", + "=HOUR(-1)": "HOUR only accepts positive argument", + "=HOUR(\"\")": "#VALUE!", + "=HOUR(\"25:10:55\")": "#VALUE!", // ISOWEEKNUM "=ISOWEEKNUM()": "ISOWEEKNUM requires 1 argument", "=ISOWEEKNUM(\"\")": "#VALUE!", @@ -2612,10 +2651,20 @@ func TestCalcCellValue(t *testing.T) { "=YEARFRAC(42005,42094,\"\")": "strconv.ParseFloat: parsing \"\": invalid syntax", // NOW "=NOW(A1)": "NOW accepts no arguments", + // SECOND + "=SECOND()": "SECOND requires exactly 1 argument", + "=SECOND(-1)": "SECOND only accepts positive argument", + "=SECOND(\"\")": "#VALUE!", + "=SECOND(\"25:55\")": "#VALUE!", // TIME "=TIME()": "TIME requires 3 number arguments", "=TIME(\"\",0,0)": "TIME requires 3 number arguments", "=TIME(0,0,-1)": "#NUM!", + // TIMEVALUE + "=TIMEVALUE()": "TIMEVALUE requires exactly 1 argument", + "=TIMEVALUE(1)": "#VALUE!", + "=TIMEVALUE(-1)": "#VALUE!", + "=TIMEVALUE(\"25:55\")": "#VALUE!", // TODAY "=TODAY(A1)": "TODAY accepts no arguments", // WEEKDAY @@ -3354,7 +3403,7 @@ func TestCalcCellValue(t *testing.T) { f := prepareCalcData(cellData) assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) result, err := f.CalcCellValue("Sheet1", "C1") - assert.EqualError(t, err, expected) + assert.EqualError(t, err, expected, formula) assert.Equal(t, "", result, formula) } diff --git a/xmlWorkbook.go b/xmlWorkbook.go index f014bee..2bb417c 100644 --- a/xmlWorkbook.go +++ b/xmlWorkbook.go @@ -139,19 +139,19 @@ type xlsxBookViews struct { // http://schemas.openxmlformats.org/spreadsheetml/2006/main This element // specifies a single Workbook view. type xlsxWorkBookView struct { - ActiveTab int `xml:"activeTab,attr,omitempty"` - AutoFilterDateGrouping bool `xml:"autoFilterDateGrouping,attr,omitempty"` - FirstSheet int `xml:"firstSheet,attr,omitempty"` - Minimized bool `xml:"minimized,attr,omitempty"` - ShowHorizontalScroll bool `xml:"showHorizontalScroll,attr,omitempty"` - ShowSheetTabs bool `xml:"showSheetTabs,attr,omitempty"` - ShowVerticalScroll bool `xml:"showVerticalScroll,attr,omitempty"` - TabRatio int `xml:"tabRatio,attr,omitempty"` Visibility string `xml:"visibility,attr,omitempty"` - WindowHeight int `xml:"windowHeight,attr,omitempty"` - WindowWidth int `xml:"windowWidth,attr,omitempty"` + Minimized bool `xml:"minimized,attr,omitempty"` + ShowHorizontalScroll *bool `xml:"showHorizontalScroll,attr"` + ShowVerticalScroll *bool `xml:"showVerticalScroll,attr"` + ShowSheetTabs *bool `xml:"showSheetTabs,attr"` XWindow string `xml:"xWindow,attr,omitempty"` YWindow string `xml:"yWindow,attr,omitempty"` + WindowWidth int `xml:"windowWidth,attr,omitempty"` + WindowHeight int `xml:"windowHeight,attr,omitempty"` + TabRatio int `xml:"tabRatio,attr,omitempty"` + FirstSheet int `xml:"firstSheet,attr,omitempty"` + ActiveTab int `xml:"activeTab,attr,omitempty"` + AutoFilterDateGrouping *bool `xml:"autoFilterDateGrouping,attr"` } // xlsxSheets directly maps the sheets element from the namespace -- cgit v1.2.1