1 package org
.apache
.poi
.hssf
.usermodel
;
3 import java
.util
.Iterator
;
5 import junit
.framework
.TestCase
;
8 * Tests to show that our documentation at
9 * http://poi.apache.org/hssf/eval.html
10 * all actually works as we'd expect them to
12 public class TestFormulaEvaluatorDocs
extends TestCase
{
13 protected void setUp() throws Exception
{
18 * http://poi.apache.org/hssf/eval.html#EvaluateAll
20 public void testEvaluateAll() throws Exception
{
21 HSSFWorkbook wb
= new HSSFWorkbook();
22 HSSFSheet s1
= wb
.createSheet();
23 HSSFSheet s2
= wb
.createSheet();
24 wb
.setSheetName(0, "S1");
25 wb
.setSheetName(1, "S2");
27 HSSFRow s1r1
= s1
.createRow(0);
28 HSSFRow s1r2
= s1
.createRow(1);
29 HSSFRow s2r1
= s2
.createRow(0);
31 HSSFCell s1r1c1
= s1r1
.createCell((short)0);
32 HSSFCell s1r1c2
= s1r1
.createCell((short)1);
33 HSSFCell s1r1c3
= s1r1
.createCell((short)2);
34 s1r1c1
.setCellValue(22.3);
35 s1r1c2
.setCellValue(33.4);
36 s1r1c3
.setCellFormula("SUM(A1:B1)");
38 HSSFCell s1r2c1
= s1r2
.createCell((short)0);
39 HSSFCell s1r2c2
= s1r2
.createCell((short)1);
40 HSSFCell s1r2c3
= s1r2
.createCell((short)2);
41 s1r2c1
.setCellValue(-1.2);
42 s1r2c2
.setCellValue(-3.4);
43 s1r2c3
.setCellFormula("SUM(A2:B2)");
45 HSSFCell s2r1c1
= s2r1
.createCell((short)0);
46 s2r1c1
.setCellFormula("S1!A1");
49 assertEquals(0.0, s1r1c3
.getNumericCellValue(), 0);
50 assertEquals(0.0, s1r2c3
.getNumericCellValue(), 0);
51 assertEquals(0.0, s2r1c1
.getNumericCellValue(), 0);
53 // Do a full evaluate, as per our docs
54 // uses evaluateFormulaCell()
55 for(int sheetNum
= 0; sheetNum
< wb
.getNumberOfSheets(); sheetNum
++) {
56 HSSFSheet sheet
= wb
.getSheetAt(sheetNum
);
57 HSSFFormulaEvaluator evaluator
= new HSSFFormulaEvaluator(sheet
, wb
);
59 for(Iterator rit
= sheet
.rowIterator(); rit
.hasNext();) {
60 HSSFRow r
= (HSSFRow
)rit
.next();
61 evaluator
.setCurrentRow(r
);
63 for(Iterator cit
= r
.cellIterator(); cit
.hasNext();) {
64 HSSFCell c
= (HSSFCell
)cit
.next();
65 if(c
.getCellType() == HSSFCell
.CELL_TYPE_FORMULA
) {
66 evaluator
.evaluateFormulaCell(c
);
68 // For testing - all should be numeric
69 assertEquals(HSSFCell
.CELL_TYPE_NUMERIC
, evaluator
.evaluateFormulaCell(c
));
75 // Check now as expected
76 assertEquals(55.7, wb
.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0);
77 assertEquals("SUM(A1:B1)", wb
.getSheetAt(0).getRow(0).getCell((short)2).getCellFormula());
78 assertEquals(HSSFCell
.CELL_TYPE_FORMULA
, wb
.getSheetAt(0).getRow(0).getCell((short)2).getCellType());
80 assertEquals(-4.6, wb
.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0);
81 assertEquals("SUM(A2:B2)", wb
.getSheetAt(0).getRow(1).getCell((short)2).getCellFormula());
82 assertEquals(HSSFCell
.CELL_TYPE_FORMULA
, wb
.getSheetAt(0).getRow(1).getCell((short)2).getCellType());
84 assertEquals(22.3, wb
.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0);
85 assertEquals("'S1'!A1", wb
.getSheetAt(1).getRow(0).getCell((short)0).getCellFormula());
86 assertEquals(HSSFCell
.CELL_TYPE_FORMULA
, wb
.getSheetAt(1).getRow(0).getCell((short)0).getCellType());
89 // Now do the alternate call, which zaps the formulas
90 // uses evaluateInCell()
91 for(int sheetNum
= 0; sheetNum
< wb
.getNumberOfSheets(); sheetNum
++) {
92 HSSFSheet sheet
= wb
.getSheetAt(sheetNum
);
93 HSSFFormulaEvaluator evaluator
= new HSSFFormulaEvaluator(sheet
, wb
);
95 for(Iterator rit
= sheet
.rowIterator(); rit
.hasNext();) {
96 HSSFRow r
= (HSSFRow
)rit
.next();
97 evaluator
.setCurrentRow(r
);
99 for(Iterator cit
= r
.cellIterator(); cit
.hasNext();) {
100 HSSFCell c
= (HSSFCell
)cit
.next();
101 if(c
.getCellType() == HSSFCell
.CELL_TYPE_FORMULA
) {
102 evaluator
.evaluateInCell(c
);
108 assertEquals(55.7, wb
.getSheetAt(0).getRow(0).getCell((short)2).getNumericCellValue(), 0);
109 assertEquals(HSSFCell
.CELL_TYPE_NUMERIC
, wb
.getSheetAt(0).getRow(0).getCell((short)2).getCellType());
111 assertEquals(-4.6, wb
.getSheetAt(0).getRow(1).getCell((short)2).getNumericCellValue(), 0);
112 assertEquals(HSSFCell
.CELL_TYPE_NUMERIC
, wb
.getSheetAt(0).getRow(1).getCell((short)2).getCellType());
114 assertEquals(22.3, wb
.getSheetAt(1).getRow(0).getCell((short)0).getNumericCellValue(), 0);
115 assertEquals(HSSFCell
.CELL_TYPE_NUMERIC
, wb
.getSheetAt(1).getRow(0).getCell((short)0).getCellType());