diff options
| -rw-r--r-- | calc.go | 57 | ||||
| -rw-r--r-- | calc_test.go | 47 |
2 files changed, 97 insertions, 7 deletions
@@ -556,6 +556,7 @@ type formulaFuncs struct { // SEC // SECH // SHEET +// SHEETS // SIGN // SIN // SINH @@ -6818,13 +6819,61 @@ func (fn *formulaFuncs) NA(argsList *list.List) formulaArg { // SHEET function returns the Sheet number for a specified reference. The // syntax of the function is: // -// SHEET() +// SHEET([value]) // func (fn *formulaFuncs) SHEET(argsList *list.List) formulaArg { - if argsList.Len() != 0 { - return newErrorFormulaArg(formulaErrorVALUE, "SHEET accepts no arguments") + if argsList.Len() > 1 { + return newErrorFormulaArg(formulaErrorVALUE, "SHEET accepts at most 1 argument") + } + if argsList.Len() == 0 { + return newNumberFormulaArg(float64(fn.f.GetSheetIndex(fn.sheet) + 1)) + } + arg := argsList.Front().Value.(formulaArg) + if sheetIdx := fn.f.GetSheetIndex(arg.Value()); sheetIdx != -1 { + return newNumberFormulaArg(float64(sheetIdx + 1)) + } + if arg.cellRanges != nil && arg.cellRanges.Len() > 0 { + if sheetIdx := fn.f.GetSheetIndex(arg.cellRanges.Front().Value.(cellRange).From.Sheet); sheetIdx != -1 { + return newNumberFormulaArg(float64(sheetIdx + 1)) + } + } + if arg.cellRefs != nil && arg.cellRefs.Len() > 0 { + if sheetIdx := fn.f.GetSheetIndex(arg.cellRefs.Front().Value.(cellRef).Sheet); sheetIdx != -1 { + return newNumberFormulaArg(float64(sheetIdx + 1)) + } } - return newNumberFormulaArg(float64(fn.f.GetSheetIndex(fn.sheet) + 1)) + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) +} + +// SHEETS function returns the number of sheets in a supplied reference. The +// result includes sheets that are Visible, Hidden or Very Hidden. The syntax +// of the function is: +// +// SHEETS([reference]) +// +func (fn *formulaFuncs) SHEETS(argsList *list.List) formulaArg { + if argsList.Len() > 1 { + return newErrorFormulaArg(formulaErrorVALUE, "SHEETS accepts at most 1 argument") + } + if argsList.Len() == 0 { + return newNumberFormulaArg(float64(len(fn.f.GetSheetList()))) + } + arg := argsList.Front().Value.(formulaArg) + sheetMap := map[string]interface{}{} + if arg.cellRanges != nil && arg.cellRanges.Len() > 0 { + for rng := arg.cellRanges.Front(); rng != nil; rng = rng.Next() { + sheetMap[rng.Value.(cellRange).From.Sheet] = nil + } + } + if arg.cellRefs != nil && arg.cellRefs.Len() > 0 { + for ref := arg.cellRefs.Front(); ref != nil; ref = ref.Next() { + sheetMap[ref.Value.(cellRef).Sheet] = nil + } + } + if len(sheetMap) > 0 { + return newNumberFormulaArg(float64(len(sheetMap))) + } + return newErrorFormulaArg(formulaErrorNA, formulaErrorNA) } // T function tests if a supplied value is text and if so, returns the diff --git a/calc_test.go b/calc_test.go index a2b1294..0aeff70 100644 --- a/calc_test.go +++ b/calc_test.go @@ -1028,7 +1028,11 @@ func TestCalcCellValue(t *testing.T) { "=N(TRUE)": "1", "=N(FALSE)": "0", // SHEET - "=SHEET()": "1", + "=SHEET()": "1", + "=SHEET(\"Sheet1\")": "1", + // SHEETS + "=SHEETS()": "1", + "=SHEETS(A1)": "1", // T "=T(\"text\")": "text", "=T(N(10))": "", @@ -2442,7 +2446,11 @@ func TestCalcCellValue(t *testing.T) { "=NA()": "#N/A", "=NA(1)": "NA accepts no arguments", // SHEET - "=SHEET(1)": "SHEET accepts no arguments", + "=SHEET(\"\",\"\")": "SHEET accepts at most 1 argument", + "=SHEET(\"Sheet2\")": "#N/A", + // SHEETS + "=SHEETS(\"\",\"\")": "SHEETS accepts at most 1 argument", + "=SHEETS(\"Sheet1\")": "#N/A", // T "=T()": "T requires 1 argument", "=T(NA())": "#N/A", @@ -2459,7 +2467,8 @@ func TestCalcCellValue(t *testing.T) { // IFNA "=IFNA()": "IFNA requires 2 arguments", // IFS - "=IFS()": "IFS requires at least 2 arguments", + "=IFS()": "IFS requires at least 2 arguments", + "=IFS(FALSE,FALSE)": "#N/A", // NOT "=NOT()": "NOT requires 1 argument", "=NOT(NOT())": "NOT requires 1 argument", @@ -3758,6 +3767,38 @@ func TestCalcISFORMULA(t *testing.T) { } } +func TestCalcSHEET(t *testing.T) { + f := NewFile() + f.NewSheet("Sheet2") + formulaList := map[string]string{ + "=SHEET(\"Sheet2\")": "2", + "=SHEET(Sheet2!A1)": "2", + "=SHEET(Sheet2!A1:A2)": "2", + } + for formula, expected := range formulaList { + assert.NoError(t, f.SetCellFormula("Sheet1", "A1", formula)) + result, err := f.CalcCellValue("Sheet1", "A1") + assert.NoError(t, err, formula) + assert.Equal(t, expected, result, formula) + } +} + +func TestCalcSHEETS(t *testing.T) { + f := NewFile() + f.NewSheet("Sheet2") + formulaList := map[string]string{ + "=SHEETS(Sheet1!A1:B1)": "1", + "=SHEETS(Sheet1!A1:Sheet1!A1)": "1", + "=SHEETS(Sheet1!A1:Sheet2!A1)": "2", + } + for formula, expected := range formulaList { + assert.NoError(t, f.SetCellFormula("Sheet1", "A1", formula)) + result, err := f.CalcCellValue("Sheet1", "A1") + assert.NoError(t, err, formula) + assert.Equal(t, expected, result, formula) + } +} + func TestCalcZTEST(t *testing.T) { f := NewFile() assert.NoError(t, f.SetSheetRow("Sheet1", "A1", &[]int{4, 5, 2, 5, 8, 9, 3, 2, 3, 8, 9, 5})) |