Line 0
Link Here
|
|
|
1 |
/* ==================================================================== |
2 |
Licensed to the Apache Software Foundation (ASF) under one or more |
3 |
contributor license agreements. See the NOTICE file distributed with |
4 |
this work for additional information regarding copyright ownership. |
5 |
The ASF licenses this file to You under the Apache License, Version 2.0 |
6 |
(the "License"); you may not use this file except in compliance with |
7 |
the License. You may obtain a copy of the License at |
8 |
|
9 |
http://www.apache.org/licenses/LICENSE-2.0 |
10 |
|
11 |
Unless required by applicable law or agreed to in writing, software |
12 |
distributed under the License is distributed on an "AS IS" BASIS, |
13 |
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
14 |
See the License for the specific language governing permissions and |
15 |
limitations under the License. |
16 |
==================================================================== */ |
17 |
package org.apache.poi.ss.formula.atp; |
18 |
|
19 |
import junit.framework.TestCase; |
20 |
import org.apache.poi.hssf.usermodel.HSSFWorkbook; |
21 |
import org.apache.poi.ss.usermodel.Cell; |
22 |
import org.apache.poi.ss.usermodel.CellType; |
23 |
import org.apache.poi.ss.usermodel.FormulaEvaluator; |
24 |
import org.apache.poi.ss.usermodel.Row; |
25 |
import org.apache.poi.ss.usermodel.Sheet; |
26 |
import org.apache.poi.ss.usermodel.Workbook; |
27 |
import org.apache.poi.ss.util.CellAddress; |
28 |
import org.apache.poi.ss.util.CellReference; |
29 |
import org.junit.Test; |
30 |
|
31 |
import static org.junit.Assert.assertEquals; |
32 |
|
33 |
/** |
34 |
* Testcase for 'Analysis Toolpak' function IFS() |
35 |
* |
36 |
* @author Pieter Degraeuwe |
37 |
*/ |
38 |
public class TestIfs { |
39 |
|
40 |
/** |
41 |
* =IFS(A1="A", "Value for A" , A1="B", "Value for B") |
42 |
*/ |
43 |
@Test |
44 |
public void testEvaluate() { |
45 |
Workbook wb = new HSSFWorkbook(); |
46 |
Sheet sh = wb.createSheet(); |
47 |
Row row1 = sh.createRow(0); |
48 |
|
49 |
// Create cells |
50 |
row1.createCell(0, CellType.STRING); |
51 |
|
52 |
// Create references |
53 |
CellReference a1Ref = new CellReference("A1"); |
54 |
|
55 |
// Set values |
56 |
final Cell cellA1 = sh.getRow(a1Ref.getRow()).getCell(a1Ref.getCol()); |
57 |
|
58 |
|
59 |
Cell cell1 = row1.createCell(1); |
60 |
cell1.setCellFormula("IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")"); |
61 |
|
62 |
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); |
63 |
|
64 |
cellA1.setCellValue("A"); |
65 |
assertEquals("Checks that the cell is numeric", |
66 |
CellType.STRING, evaluator.evaluate(cell1).getCellType()); |
67 |
assertEquals("IFS should return 'Value for B'", "Value for A", evaluator.evaluate(cell1).getStringValue()); |
68 |
|
69 |
cellA1.setCellValue("B"); |
70 |
evaluator.clearAllCachedResultValues(); |
71 |
assertEquals("Checks that the cell is numeric", |
72 |
CellType.STRING, evaluator.evaluate(cell1).getCellType()); |
73 |
assertEquals("IFS should return 'Value for B'", "Value for B", evaluator.evaluate(cell1).getStringValue()); |
74 |
|
75 |
|
76 |
} |
77 |
|
78 |
|
79 |
/** |
80 |
* where D1 contains a string "A" |
81 |
* =IFS(A1=D1, "Value for A" , A1="B", "Value for B") |
82 |
*/ |
83 |
@Test |
84 |
public void testEvaluateForReferenced() { |
85 |
Workbook wb = new HSSFWorkbook(); |
86 |
Sheet sh = wb.createSheet(); |
87 |
Row row1 = sh.createRow(0); |
88 |
|
89 |
// Create cells |
90 |
// row1.createCell(0, CellType.STRING); |
91 |
|
92 |
|
93 |
// Create references |
94 |
CellReference a1Ref = new CellReference("A1"); |
95 |
CellReference d1Ref = new CellReference("D1"); |
96 |
|
97 |
// Set values |
98 |
final Cell cellA1 = sh.getRow(a1Ref.getRow()).createCell(a1Ref.getCol()); |
99 |
cellA1.setCellFormula("D1"); |
100 |
|
101 |
final Cell cellD1 = sh.getRow(d1Ref.getRow()).createCell(d1Ref.getCol()); |
102 |
cellD1.setCellValue("A"); |
103 |
|
104 |
|
105 |
Cell cell1 = row1.createCell(1); |
106 |
cell1.setCellFormula("IFS(A1=\"A\", \"Value for A\", A1=\"B\",\"Value for B\")"); |
107 |
|
108 |
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator(); |
109 |
|
110 |
cellD1.setCellValue("A"); |
111 |
assertEquals("Checks that the cell is numeric", |
112 |
CellType.STRING, evaluator.evaluate(cell1).getCellType()); |
113 |
assertEquals("IFS should return 'Value for B'", "Value for A", evaluator.evaluate(cell1).getStringValue()); |
114 |
|
115 |
cellD1.setCellValue("B"); |
116 |
evaluator.clearAllCachedResultValues(); |
117 |
assertEquals("Checks that the cell is numeric", |
118 |
CellType.STRING, evaluator.evaluate(cell1).getCellType()); |
119 |
assertEquals("IFS should return 'Value for B'", "Value for B", evaluator.evaluate(cell1).getStringValue()); |
120 |
|
121 |
|
122 |
} |
123 |
|
124 |
|
125 |
} |