diff options
| -rw-r--r-- | calc.go | 126 | ||||
| -rw-r--r-- | calc_test.go | 111 | ||||
| -rw-r--r-- | datavalidation_test.go | 2 | ||||
| -rw-r--r-- | rows.go | 14 |
4 files changed, 203 insertions, 50 deletions
@@ -582,6 +582,8 @@ type formulaFuncs struct { // NA // NEGBINOM.DIST // NEGBINOMDIST +// NETWORKDAYS +// NETWORKDAYS.INTL // NOMINAL // NORM.DIST // NORMDIST @@ -724,6 +726,7 @@ type formulaFuncs struct { // WEEKNUM // WEIBULL // WEIBULL.DIST +// WORKDAY // WORKDAY.INTL // XIRR // XLOOKUP @@ -899,12 +902,11 @@ func (f *File) evalInfixExp(sheet, cell string, tokens []efp.Token) (formulaArg, if result.Type == ArgUnknown { return newEmptyFormulaArg(), errors.New(formulaErrorVALUE) } - // when thisToken is Range and nextToken is Argument and opfdStack not Empty, should push value to opfdStack and continue. - if nextToken.TType == efp.TokenTypeArgument { - if !opfdStack.Empty() { - opfdStack.Push(result) - continue - } + // when current token is range, next token is argument and opfdStack not empty, + // should push value to opfdStack and continue + if nextToken.TType == efp.TokenTypeArgument && !opfdStack.Empty() { + opfdStack.Push(result) + continue } argsStack.Peek().(*list.List).PushBack(result) continue @@ -12563,16 +12565,17 @@ func (fn *formulaFuncs) MONTH(argsList *list.List) formulaArg { // genWeekendMask generate weekend mask of a series of seven 0's and 1's which // represent the seven weekdays, starting from Monday. func genWeekendMask(weekend int) []byte { - mask := make([]byte, 7) if masks, ok := map[int][]int{ 1: {5, 6}, 2: {6, 0}, 3: {0, 1}, 4: {1, 2}, 5: {2, 3}, 6: {3, 4}, 7: {4, 5}, 11: {6}, 12: {0}, 13: {1}, 14: {2}, 15: {3}, 16: {4}, 17: {5}, }[weekend]; ok { + mask := make([]byte, 7) for _, idx := range masks { mask[idx] = 1 } + return mask } - return mask + return nil } // isWorkday check if the date is workday. @@ -12687,6 +12690,113 @@ func workdayIntl(endDate, sign int, holidays []int, weekendMask []byte, startDat return endDate } +// NETWORKDAYS function calculates the number of work days between two supplied +// dates (including the start and end date). The calculation includes all +// weekdays (Mon - Fri), excluding a supplied list of holidays. The syntax of +// the function is: +// +// NETWORKDAYS(start_date,end_date,[holidays]) +// +func (fn *formulaFuncs) NETWORKDAYS(argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, "NETWORKDAYS requires at least 2 arguments") + } + if argsList.Len() > 3 { + return newErrorFormulaArg(formulaErrorVALUE, "NETWORKDAYS requires at most 3 arguments") + } + args := list.New() + args.PushBack(argsList.Front().Value.(formulaArg)) + args.PushBack(argsList.Front().Next().Value.(formulaArg)) + args.PushBack(newNumberFormulaArg(1)) + if argsList.Len() == 3 { + args.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.NETWORKDAYSdotINTL(args) +} + +// NETWORKDAYSdotINTL function calculates the number of whole work days between +// two supplied dates, excluding weekends and holidays. The function allows +// the user to specify which days are counted as weekends and holidays. The +// syntax of the function is: +// +// NETWORKDAYS.INTL(start_date,end_date,[weekend],[holidays]) +// +func (fn *formulaFuncs) NETWORKDAYSdotINTL(argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, "NETWORKDAYS.INTL requires at least 2 arguments") + } + if argsList.Len() > 4 { + return newErrorFormulaArg(formulaErrorVALUE, "NETWORKDAYS.INTL requires at most 4 arguments") + } + startDate := toExcelDateArg(argsList.Front().Value.(formulaArg)) + if startDate.Type != ArgNumber { + return startDate + } + endDate := toExcelDateArg(argsList.Front().Next().Value.(formulaArg)) + if endDate.Type != ArgNumber { + return endDate + } + weekend := newNumberFormulaArg(1) + if argsList.Len() > 2 { + weekend = argsList.Front().Next().Next().Value.(formulaArg) + } + var holidays []int + if argsList.Len() == 4 { + holidays = prepareHolidays(argsList.Back().Value.(formulaArg)) + sort.Ints(holidays) + } + weekendMask, workdaysPerWeek := prepareWorkday(weekend) + if workdaysPerWeek == 0 { + return newErrorFormulaArg(formulaErrorVALUE, formulaErrorVALUE) + } + sign := 1 + if startDate.Number > endDate.Number { + sign = -1 + temp := startDate.Number + startDate.Number = endDate.Number + endDate.Number = temp + } + offset := endDate.Number - startDate.Number + count := int(math.Floor(offset/7) * float64(workdaysPerWeek)) + daysMod := int(offset) % 7 + for daysMod >= 0 { + if isWorkday(weekendMask, endDate.Number-float64(daysMod)) { + count++ + } + daysMod-- + } + for i := 0; i < len(holidays); i++ { + holiday := float64(holidays[i]) + if isWorkday(weekendMask, holiday) && holiday >= startDate.Number && holiday <= endDate.Number { + count-- + } + } + return newNumberFormulaArg(float64(sign * count)) +} + +// WORKDAY function returns a date that is a supplied number of working days +// (excluding weekends and holidays) ahead of a given start date. The syntax +// of the function is: +// +// WORKDAY(start_date,days,[holidays]) +// +func (fn *formulaFuncs) WORKDAY(argsList *list.List) formulaArg { + if argsList.Len() < 2 { + return newErrorFormulaArg(formulaErrorVALUE, "WORKDAY requires at least 2 arguments") + } + if argsList.Len() > 3 { + return newErrorFormulaArg(formulaErrorVALUE, "WORKDAY requires at most 3 arguments") + } + args := list.New() + args.PushBack(argsList.Front().Value.(formulaArg)) + args.PushBack(argsList.Front().Next().Value.(formulaArg)) + args.PushBack(newNumberFormulaArg(1)) + if argsList.Len() == 3 { + args.PushBack(argsList.Back().Value.(formulaArg)) + } + return fn.WORKDAYdotINTL(args) +} + // WORKDAYdotINTL function returns a date that is a supplied number of working // days (excluding weekends and holidays) ahead of a given start date. The // function allows the user to specify which days of the week are counted as diff --git a/calc_test.go b/calc_test.go index d5c263e..c7333c5 100644 --- a/calc_test.go +++ b/calc_test.go @@ -5380,7 +5380,7 @@ func TestCalcTTEST(t *testing.T) { } } -func TestCalcWORKDAYdotINTL(t *testing.T) { +func TestCalcNETWORKDAYSandWORKDAY(t *testing.T) { cellData := [][]interface{}{ {"05/01/2019", 43586}, {"09/13/2019", 43721}, @@ -5395,31 +5395,53 @@ func TestCalcWORKDAYdotINTL(t *testing.T) { } f := prepareCalcData(cellData) formulaList := map[string]string{ - "=WORKDAY.INTL(\"12/01/2015\",0)": "42339", - "=WORKDAY.INTL(\"12/01/2015\",25)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",-25)": "42304", - "=WORKDAY.INTL(\"12/01/2015\",25,1)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",25,2)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",25,3)": "42372", - "=WORKDAY.INTL(\"12/01/2015\",25,4)": "42373", - "=WORKDAY.INTL(\"12/01/2015\",25,5)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",25,6)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",25,7)": "42374", - "=WORKDAY.INTL(\"12/01/2015\",25,11)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,12)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,13)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,14)": "42369", - "=WORKDAY.INTL(\"12/01/2015\",25,15)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,16)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,17)": "42368", - "=WORKDAY.INTL(\"12/01/2015\",25,\"0001100\")": "42374", - "=WORKDAY.INTL(\"01/01/2020\",-123,4)": "43659", - "=WORKDAY.INTL(\"01/01/2020\",123,4,44010)": "44002", - "=WORKDAY.INTL(\"01/01/2020\",-123,4,43640)": "43659", - "=WORKDAY.INTL(\"01/01/2020\",-123,4,43660)": "43658", - "=WORKDAY.INTL(\"01/01/2020\",-123,7,43660)": "43657", - "=WORKDAY.INTL(\"01/01/2020\",123,4,A1:A12)": "44008", - "=WORKDAY.INTL(\"01/01/2020\",123,4,B1:B12)": "44008", + "=NETWORKDAYS(\"01/01/2020\",\"09/12/2020\")": "183", + "=NETWORKDAYS(\"01/01/2020\",\"09/12/2020\",2)": "183", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\")": "183", + "=NETWORKDAYS.INTL(\"09/12/2020\",\"01/01/2020\")": "-183", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",1)": "183", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",2)": "184", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",3)": "184", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",4)": "183", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",5)": "182", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",6)": "182", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",7)": "182", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",11)": "220", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",12)": "220", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",13)": "220", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",14)": "219", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",15)": "219", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",16)": "219", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",17)": "219", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",1,A1:A12)": "178", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",1,B1:B12)": "178", + "=WORKDAY(\"12/01/2015\",25)": "42374", + "=WORKDAY(\"01/01/2020\",123,B1:B12)": "44006", + "=WORKDAY.INTL(\"12/01/2015\",0)": "42339", + "=WORKDAY.INTL(\"12/01/2015\",25)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",-25)": "42304", + "=WORKDAY.INTL(\"12/01/2015\",25,1)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",25,2)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",25,3)": "42372", + "=WORKDAY.INTL(\"12/01/2015\",25,4)": "42373", + "=WORKDAY.INTL(\"12/01/2015\",25,5)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",25,6)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",25,7)": "42374", + "=WORKDAY.INTL(\"12/01/2015\",25,11)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,12)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,13)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,14)": "42369", + "=WORKDAY.INTL(\"12/01/2015\",25,15)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,16)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,17)": "42368", + "=WORKDAY.INTL(\"12/01/2015\",25,\"0001100\")": "42374", + "=WORKDAY.INTL(\"01/01/2020\",-123,4)": "43659", + "=WORKDAY.INTL(\"01/01/2020\",123,4,44010)": "44002", + "=WORKDAY.INTL(\"01/01/2020\",-123,4,43640)": "43659", + "=WORKDAY.INTL(\"01/01/2020\",-123,4,43660)": "43658", + "=WORKDAY.INTL(\"01/01/2020\",-123,7,43660)": "43657", + "=WORKDAY.INTL(\"01/01/2020\",123,4,A1:A12)": "44008", + "=WORKDAY.INTL(\"01/01/2020\",123,4,B1:B12)": "44008", } for formula, expected := range formulaList { assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) @@ -5428,15 +5450,34 @@ func TestCalcWORKDAYdotINTL(t *testing.T) { assert.Equal(t, expected, result, formula) } calcError := map[string]string{ - "=WORKDAY.INTL()": "WORKDAY.INTL requires at least 2 arguments", - "=WORKDAY.INTL(\"01/01/2020\",123,4,A1:A12,\"\")": "WORKDAY.INTL requires at most 4 arguments", - "=WORKDAY.INTL(\"01/01/2020\",\"\",4,B1:B12)": "strconv.ParseFloat: parsing \"\": invalid syntax", - "=WORKDAY.INTL(\"\",123,4,B1:B12)": "#VALUE!", - "=WORKDAY.INTL(\"01/01/2020\",123,\"\",B1:B12)": "#VALUE!", - "=WORKDAY.INTL(\"01/01/2020\",123,\"000000x\")": "#VALUE!", - "=WORKDAY.INTL(\"01/01/2020\",123,\"0000002\")": "#VALUE!", - "=WORKDAY.INTL(\"January 25, 100\",123)": "#VALUE!", - "=WORKDAY.INTL(-1,123)": "#NUM!", + "=NETWORKDAYS()": "NETWORKDAYS requires at least 2 arguments", + "=NETWORKDAYS(\"01/01/2020\",\"09/12/2020\",2,\"\")": "NETWORKDAYS requires at most 3 arguments", + "=NETWORKDAYS(\"\",\"09/12/2020\",2)": "#VALUE!", + "=NETWORKDAYS(\"01/01/2020\",\"\",2)": "#VALUE!", + "=NETWORKDAYS.INTL()": "NETWORKDAYS.INTL requires at least 2 arguments", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",4,A1:A12,\"\")": "NETWORKDAYS.INTL requires at most 4 arguments", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"January 25, 100\",4)": "#VALUE!", + "=NETWORKDAYS.INTL(\"\",123,4,B1:B12)": "#VALUE!", + "=NETWORKDAYS.INTL(\"01/01/2020\",123,\"000000x\")": "#VALUE!", + "=NETWORKDAYS.INTL(\"01/01/2020\",123,\"0000002\")": "#VALUE!", + "=NETWORKDAYS.INTL(\"January 25, 100\",123)": "#VALUE!", + "=NETWORKDAYS.INTL(\"01/01/2020\",\"09/12/2020\",8)": "#VALUE!", + "=NETWORKDAYS.INTL(-1,123)": "#NUM!", + "=WORKDAY()": "WORKDAY requires at least 2 arguments", + "=WORKDAY(\"01/01/2020\",123,A1:A12,\"\")": "WORKDAY requires at most 3 arguments", + "=WORKDAY(\"01/01/2020\",\"\",B1:B12)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=WORKDAY(\"\",123,B1:B12)": "#VALUE!", + "=WORKDAY(\"January 25, 100\",123)": "#VALUE!", + "=WORKDAY(-1,123)": "#NUM!", + "=WORKDAY.INTL()": "WORKDAY.INTL requires at least 2 arguments", + "=WORKDAY.INTL(\"01/01/2020\",123,4,A1:A12,\"\")": "WORKDAY.INTL requires at most 4 arguments", + "=WORKDAY.INTL(\"01/01/2020\",\"\",4,B1:B12)": "strconv.ParseFloat: parsing \"\": invalid syntax", + "=WORKDAY.INTL(\"\",123,4,B1:B12)": "#VALUE!", + "=WORKDAY.INTL(\"01/01/2020\",123,\"\",B1:B12)": "#VALUE!", + "=WORKDAY.INTL(\"01/01/2020\",123,\"000000x\")": "#VALUE!", + "=WORKDAY.INTL(\"01/01/2020\",123,\"0000002\")": "#VALUE!", + "=WORKDAY.INTL(\"January 25, 100\",123)": "#VALUE!", + "=WORKDAY.INTL(-1,123)": "#NUM!", } for formula, expected := range calcError { assert.NoError(t, f.SetCellFormula("Sheet1", "C1", formula)) diff --git a/datavalidation_test.go b/datavalidation_test.go index 80cbf59..d9e060a 100644 --- a/datavalidation_test.go +++ b/datavalidation_test.go @@ -172,7 +172,7 @@ func TestDeleteDataValidation(t *testing.T) { // Test delete data validation on no exists worksheet. assert.EqualError(t, f.DeleteDataValidation("SheetN", "A1:B2"), "sheet SheetN is not exist") - // Test delete all data validations in the worksheet + // Test delete all data validations in the worksheet. assert.NoError(t, f.DeleteDataValidation("Sheet1")) assert.Nil(t, ws.(*xlsxWorksheet).DataValidations) } @@ -28,11 +28,11 @@ import ( // GetRows return all the rows in a sheet by given worksheet name // (case sensitive), returned as a two-dimensional array, where the value of -// the cell is converted to the string type. If the cell format can be -// applied to the value of the cell, the applied value will be used, -// otherwise the original value will be used. GetRows fetched the rows with -// value or formula cells, the tail continuously empty cell will be skipped. -// For example: +// the cell is converted to the string type. If the cell format can be applied +// to the value of the cell, the applied value will be used, otherwise the +// original value will be used. GetRows fetched the rows with value or formula +// cells, the continually blank cells in the tail of each row will be skipped, +// so the length of each row may be inconsistent. For example: // // rows, err := f.GetRows("Sheet1") // if err != nil { @@ -122,7 +122,9 @@ func (rows *Rows) Close() error { return nil } -// Columns return the current row's column values. +// Columns return the current row's column values. This fetches the worksheet +// data as a stream, returns each cell in a row as is, and will not skip empty +// rows in the tail of the worksheet. func (rows *Rows) Columns(opts ...Options) ([]string, error) { if rows.curRow > rows.seekRow { return nil, nil |