src/testcases/org/apache/poi/hssf/usermodel/TestFormulaEvaluatorDocs.java

   1 package org.apache.poi.hssf.usermodel;
   2
   3 import java.util.Iterator;
   4
   5 import junit.framework.TestCase;
   6
   7 /**
   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
  11  */
  12 public class TestFormulaEvaluatorDocs extends TestCase {
  13         protected void setUp() throws Exception {
  14                 super.setUp();
  15         }
  16
  17         /**
  18          * http://poi.apache.org/hssf/eval.html#EvaluateAll
  19          */
  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");
  26
  27                 HSSFRow s1r1 = s1.createRow(0);
  28                 HSSFRow s1r2 = s1.createRow(1);
  29                 HSSFRow s2r1 = s2.createRow(0);
  30
  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)");
  37
  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)");
  44
  45                 HSSFCell s2r1c1 = s2r1.createCell((short)0);
  46                 s2r1c1.setCellFormula("S1!A1");
  47
  48                 // Not evaluated yet
  49                 assertEquals(0.0, s1r1c3.getNumericCellValue(), 0);
  50                 assertEquals(0.0, s1r2c3.getNumericCellValue(), 0);
  51                 assertEquals(0.0, s2r1c1.getNumericCellValue(), 0);
  52
  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);
  58
  59                         for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
  60                                 HSSFRow r = (HSSFRow)rit.next();
  61                                 evaluator.setCurrentRow(r);
  62
  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);
  67
  68                                                 // For testing - all should be numeric
  69                                                 assertEquals(HSSFCell.CELL_TYPE_NUMERIC, evaluator.evaluateFormulaCell(c));
  70                                         }
  71                                 }
  72                         }
  73                 }
  74
  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());
  79
  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());
  83
  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());
  87
  88
  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);
  94
  95                         for(Iterator rit = sheet.rowIterator(); rit.hasNext();) {
  96                                 HSSFRow r = (HSSFRow)rit.next();
  97                                 evaluator.setCurrentRow(r);
  98
  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);
 103                                         }
 104                                 }
 105                         }
 106                 }
 107
 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());
 110
 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());
 113
 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());
 116         }
 117 }