Package org.apache.poi.hssf.usermodel

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

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
176
177
178
179
180
181
182
183
184
185
186
   * @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);

    int rowIndex = startRowIndex;
    while (true) {
      Row r = sheet.getRow(rowIndex);
      String targetFunctionName = getTargetFunctionName(r);
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
160
161
162
163
164
165
166
167
168
169
170
    addCell(sheet1, 67, 1, 9.0); // B68
    addCell(sheet1, 68, 1, 10.0); // B69

    double expectedResult = (4.0 * 8.0 + 5.0 * 9.0) / 10.0;

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue cv = fe.evaluate(cell);

    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cv.getCellType());
    assertEquals(expectedResult, cv.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
84
85
86
87
88
89
90
91
92
93
94
  public void testBasic() {

    HSSFWorkbook wbA = createWBA();
    HSSFCell c = wbA.getSheetAt(0).createRow(5).createCell(2);
    HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);

    // non-error cases
    confirm(feA, c, "INDIRECT(\"C2\")", 23);
    confirm(feA, c, "INDIRECT(\"$C2\")", 23);
    confirm(feA, c, "INDIRECT(\"C$2\")", 23);
View Full Code Here
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
  }

  public void testMultipleWorkbooks() {
    HSSFWorkbook wbA = createWBA();
    HSSFCell cellA = wbA.getSheetAt(0).createRow(10).createCell(0);
    HSSFFormulaEvaluator feA = new HSSFFormulaEvaluator(wbA);

    HSSFWorkbook wbB = createWBB();
    HSSFCell cellB = wbB.getSheetAt(0).createRow(10).createCell(0);
    HSSFFormulaEvaluator feB = new HSSFFormulaEvaluator(wbB);

    String[] workbookNames = { "MyBook", "Figures for January", };
    HSSFFormulaEvaluator[] evaluators = { feA, feB, };
    HSSFFormulaEvaluator.setupEnvironment(workbookNames, evaluators);
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.