Package org.apache.poi.ss.usermodel

Examples of org.apache.poi.ss.usermodel.FormulaEvaluator

611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
    }

    public void test49156() throws Exception {
        Workbook wb = XSSFTestDataSamples.openSampleWorkbook("49156.xlsx");
        FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();

        Sheet sheet = wb.getSheetAt(0);
        for(Row row : sheet){
            for(Cell cell : row){
                if(cell.getCellType() == Cell.CELL_TYPE_FORMULA){
                    formulaEvaluator.evaluateInCell(cell); // caused NPE on some cells
                }
            }
        }
    }
View Full Code Here
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
       Cell c = s.getRow(0).getCell(0);
      
       assertEquals("SUM(\n1,2\n)", c.getCellFormula());
       assertEquals(3.0, c.getNumericCellValue());
      
       FormulaEvaluator formulaEvaluator = wb.getCreationHelper().createFormulaEvaluator();
       formulaEvaluator.evaluateFormulaCell(c);
      
       assertEquals("SUM(\n1,2\n)", c.getCellFormula());
       assertEquals(3.0, c.getNumericCellValue());

       // For 51875
       Cell b3 = s.getRow(2).getCell(1);
       formulaEvaluator.evaluateFormulaCell(b3);
       assertEquals("B1+B2", b3.getCellFormula()); // The newline is lost for shared formulas
       assertEquals(3.0, b3.getNumericCellValue());
    }
View Full Code Here
1324
1325
1326
1327
1328
1329
1330
1331
1332
1333
1334
1335
1336
1337
1338
1339
1340
1341
1342
1343
1344
    /**
     * Bug 53101:
     */
    public void test5301(){
        Workbook workbook = XSSFTestDataSamples.openSampleWorkbook("53101.xlsx");
        FormulaEvaluator evaluator =
                workbook.getCreationHelper().createFormulaEvaluator();
        // A1: SUM(B1: IZ1)
        double a1Value =
                evaluator.evaluate(workbook.getSheetAt(0).getRow(0).getCell(0)).getNumberValue();

        // Assert
        assertEquals(259.0, a1Value, 0.0);

        // KY: SUM(B1: IZ1)
        double ky1Value =
                evaluator.evaluate(workbook.getSheetAt(0).getRow(0).getCell(310)).getNumberValue();

        // Assert
        assertEquals(259.0, a1Value, 0.0);
    }
View Full Code Here
223
224
225
226
227
228
229
230
231
232
233
234
     * @param workbook the workbook that contains the cell
     * @param cell the cell that contains the formula
     * @return the object representation of formula cell value.
     */
    private Object evaluateCellFormula(final HSSFWorkbook workbook, final Cell cell) {
        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
        CellValue cellValue = evaluator.evaluate(cell);
        Object result = null;

        if (cellValue.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            result = cellValue.getBooleanValue();
        } else if (cellValue.getCellType() == Cell.CELL_TYPE_NUMERIC) {
View Full Code Here
204
205
206
207
208
209
210
211
212
213
214
   * @param testFocusFunctionName name of a single function/operator to test alone.
   * Typically pass <code>null</code> to test all functions
   */
  private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
    FormulaEvaluator evaluator = new FormulaEvaluator(sheet, workbook);

    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);
View Full Code Here
47
48
49
50
51
52
53
54
55
56
57
58
59
60
     
      Cell c2 = r.createCell(1);
      c2.setCellFormula("10/2");
      assertTrue( Double.isNaN(c2.getNumericCellValue()) );
     
      FormulaEvaluator fe = new FormulaEvaluator(s, wb);
      fe.setCurrentRow(r);
     
      fe.evaluateFormulaCell(c1);
      fe.evaluateFormulaCell(c2);
     
      assertEquals(6.0, c1.getNumericCellValue(), 0.0001);
      assertEquals(5.0, c2.getNumericCellValue(), 0.0001);
    }
View Full Code Here
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
      c4.setCellFormula("COUNTA(A1:E1)");
      assertTrue( Double.isNaN(c4.getNumericCellValue()) );


      // Evaluate and test
      FormulaEvaluator fe = new FormulaEvaluator(s, wb);
      fe.setCurrentRow(r);
     
      fe.evaluateFormulaCell(c1);
      fe.evaluateFormulaCell(c2);
      fe.evaluateFormulaCell(c3);
      fe.evaluateFormulaCell(c4);
     
      assertEquals(3.6, c1.getNumericCellValue(), 0.0001);
      assertEquals(17.5, c2.getNumericCellValue(), 0.0001);
      assertEquals(1, c3.getNumericCellValue(), 0.0001);
      assertEquals(4, c4.getNumericCellValue(), 0.0001);
View Full Code Here
191
192
193
194
195
196
197
198
199
200
201
   * @param testFocusFunctionName name of a single function/operator to test alone.
   * Typically pass <code>null</code> to test all functions
   */
  private void processFunctionGroup(int startRowIndex, String testFocusFunctionName) {
    FormulaEvaluator evaluator = new FormulaEvaluator(sheet, workbook);

    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);
View Full Code Here
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
   */
  public void testResultOutsideRange() {
    Workbook wb = new HSSFWorkbook();
    Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
    cell.setCellFormula("D2:D5"); // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would work too
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (IllegalArgumentException e) {
      if ("Specified row index (0) is outside the allowed range (1..4)".equals(e.getMessage())) {
        throw new AssertionFailedError("Identified bug in result dereferencing");
      }
      throw new RuntimeException(e);
    }
    assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
    assertEquals(ErrorConstants.ERROR_VALUE, cv.getErrorValue());

    // verify circular refs are still detected properly
    fe.clearAllCachedResultValues();
    cell.setCellFormula("OFFSET(A1,0,0)");
    cv = fe.evaluate(cell);
    assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
    assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
  }
View Full Code Here
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
   */
  public void testResultOutsideRange() {
    Workbook wb = new HSSFWorkbook();
    Cell cell = wb.createSheet("Sheet1").createRow(0).createCell(0);
    cell.setCellFormula("D2:D5"); // IF(TRUE,D2:D5,D2) or  OFFSET(D2:D5,0,0) would work too
    FormulaEvaluator fe = wb.getCreationHelper().createFormulaEvaluator();
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (IllegalArgumentException e) {
      if ("Specified row index (0) is outside the allowed range (1..4)".equals(e.getMessage())) {
        throw new AssertionFailedError("Identified bug in result dereferencing");
      }
      throw new RuntimeException(e);
    }
    assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
    assertEquals(ErrorConstants.ERROR_VALUE, cv.getErrorValue());

    // verify circular refs are still detected properly
    fe.clearAllCachedResultValues();
    cell.setCellFormula("OFFSET(A1,0,0)");
    cv = fe.evaluate(cell);
    assertEquals(Cell.CELL_TYPE_ERROR, cv.getCellType());
    assertEquals(ErrorEval.CIRCULAR_REF_ERROR.getErrorCode(), cv.getErrorValue());
  }
View Full Code Here
TOP

Related Classes of org.apache.poi.ss.usermodel.FormulaEvaluator

  • com.asakusafw.testdriver.excel.ExcelSheetDataModelSource
  • loxia.support.excel.impl.DefaultExcelReader
  • loxia.support.excel.impl.DefaultExcelWriter
  • org.apache.metamodel.excel.ExcelUtils
  • org.apache.poi.hssf.record.formula.Area3DPtg
  • org.apache.poi.hssf.record.formula.AreaPtg
  • org.apache.poi.hssf.record.formula.eval.Area2DEval
  • org.apache.poi.hssf.record.formula.eval.Area3DEval
  • org.apache.poi.hssf.record.formula.eval.AreaEval
  • org.apache.poi.hssf.record.formula.eval.BoolEval
    • Copyright © 2018 www.massapicom. All rights reserved.
    All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.