Package org.apache.poi.hssf.usermodel

Examples of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValue

178
179
180
181
182
183
184
185
186
187
188
   * @param startRowIndex row index in the spreadsheet where the first function/operator is found
   * @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) {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
        Collection<String> funcs = FunctionEval.getSupportedFunctionNames();

    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
View Full Code Here
86
87
88
89
90
91
92
93
94
    HSSFRow row = sheet.getRow(0);
    HSSFCell myFuncCell = row.getCell(1); // =myFunc("_")

    HSSFCell myFunc2Cell = row.getCell(2); // =myFunc2("_")

    HSSFFormulaEvaluator fe = HSSFFormulaEvaluator.create(wb, null, udff);
    assertEquals("_abc", fe.evaluate(myFuncCell).getStringValue());
    assertEquals("_abc2", fe.evaluate(myFunc2Cell).getStringValue());
  }
View Full Code Here
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
    cellA1.setCellFormula("SUM(C1:OFFSET(C1,0,B1))");

    cellB1.setCellValue(1.0); // range will be C1:D1

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cellA1);
    } catch (IllegalArgumentException e) {
      if (e.getMessage().equals("Unexpected ref arg class (org.apache.poi.ss.formula.LazyAreaEval)")) {
        throw new AssertionFailedError("Identified bug 46948");
      }
      throw e;
    }

    assertEquals(12.0, cv.getNumberValue(), 0.0);

    cellB1.setCellValue(2.0); // range will be C1:E1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(21.0, cv.getNumberValue(), 0.0);

    cellB1.setCellValue(0.0); // range will be C1:C1
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    assertEquals(5.0, cv.getNumberValue(), 0.0);
  }
View Full Code Here
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
*/
public final class TestMissingArgEval extends TestCase {
 
  public void testEvaluateMissingArgs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
   
    cell.setCellFormula("if(true,)");
    fe.clearAllCachedResultValues();
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (EmptyStackException e) {
      throw new AssertionFailedError("Missing args evaluation not implemented (bug 43354");
    }
    // MissingArg -> BlankEval -> zero (as formula result)
    assertEquals(0.0, cv.getNumberValue(), 0.0);
   
    // MissingArg -> BlankEval -> empty string (in concatenation)
    cell.setCellFormula("\"abc\"&if(true,)");
    fe.clearAllCachedResultValues();
    assertEquals("abc", fe.evaluate(cell).getStringValue());
  }
View Full Code Here
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
    assertEquals("abc", fe.evaluate(cell).getStringValue());
  }
 
  public void testCountFuncs() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFCell cell = sheet.createRow(0).createCell(0);
   
    cell.setCellFormula("COUNT(C5,,,,)"); // 4 missing args, C5 is blank
    assertEquals(4.0, fe.evaluate(cell).getNumberValue(), 0.0);

    cell.setCellFormula("COUNTA(C5,,)"); // 2 missing args, C5 is blank
    fe.clearAllCachedResultValues();
    assertEquals(2.0, fe.evaluate(cell).getNumberValue(), 0.0);
  }
View Full Code Here
36
37
38
39
40
41
42
43
44
45
46
47
  /**
   * Translates StackOverflowError into AssertionFailedError
   */
  private static CellValue evaluateWithCycles(HSSFWorkbook wb, HSSFCell testCell)
      throws AssertionFailedError {
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);
    try {
      return evaluator.evaluate(testCell);
    } catch (StackOverflowError e) {
      throw new AssertionFailedError( "circular reference caused stack overflow error");
    }
  }
View Full Code Here
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
    cellB1.setCellFormula("1+C1");
    cellC1.setCellFormula("1+D1");
    cellD1.setCellFormula("1+E1");
    cellE1.setCellFormula("1+A1");

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;

    // Happy day flow - evaluate A1 first
    cv = fe.evaluate(cellA1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(42.0, cv.getNumberValue(), 0.0);
    cv = fe.evaluate(cellB1); // no circ-ref-error because A1 result is cached
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(46.0, cv.getNumberValue(), 0.0);

    // Show the bug - evaluate another cell from the loop first
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellB1);
    if (cv.getCellType() == ErrorEval.CIRCULAR_REF_ERROR.getErrorCode()) {
      throw new AssertionFailedError("Identified bug 46898");
    }
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(46.0, cv.getNumberValue(), 0.0);

    // start evaluation on another cell
    fe.clearAllCachedResultValues();
    cv = fe.evaluate(cellE1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(43.0, cv.getNumberValue(), 0.0);


  }
View Full Code Here
64
65
66
67
68
69
70
71
72
73
74
75
76
77
    HSSFRow row = sheet.createRow(0);
    HSSFCell cell = row.createCell(0);
    cell.setCellFormula("B1%");
    row.createCell(1).setCellValue(50.0);

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    try {
      cv = fe.evaluate(cell);
    } catch (RuntimeException e) {
      if(e.getCause() instanceof NullPointerException) {
        throw new AssertionFailedError("Identified bug 44608");
      }
      // else some other unexpected error
View Full Code Here
64
65
66
67
68
69
70
71
72
73
74
    // this definitely would have failed due to 27349
    assertEquals("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
        .getCellFormula());

    // We might as well evaluate the formula
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = fe.evaluate(cell);

    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(3.0, cv.getNumberValue(), 0.0);
  }
View Full Code Here
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
        throw new RuntimeException(e);
      }
    }
   
    // use POI's evaluator as an extra sanity check
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv;
    cv = fe.evaluate(cell);
    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(1.0, cv.getNumberValue(), 0.0);
   
    cv = fe.evaluate(row.getCell(1));
    assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, cv.getCellType());
    assertEquals(true, cv.getBooleanValue());
  }
View Full Code Here
TOP

Related Classes of org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator$CellValue

  • com.adaptiweb.utils.poi.HSSFRowSource
  • com.aesthete.swingobjects.fw.common.util.ExcelHelper
  • com.canoo.ant.table.ExcelPropertyTable
  • org.apache.poi.hssf.model.FormulaParser
  • org.apache.poi.hssf.model.TestFormulaParserEval
  • org.apache.poi.hssf.model.Workbook
  • org.apache.poi.hssf.record.formula.Area3DPtg
  • org.apache.poi.hssf.record.formula.AreaPtg
  • org.apache.poi.hssf.record.formula.atp.TestYearFracCalculatorFromSpreadsheet
  • org.apache.poi.hssf.record.formula.eval.Area2DEval
    • 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.