Package org.apache.poi.hssf.usermodel

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

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
53
54
55
56
57
58
59
    style.setDataFormat(fmt.getFormat("hh:mm:ss"));

    cell11 = sheet.createRow(0).createCell(0);
    form = new HSSFDataFormatter();

    evaluator = new HSSFFormulaEvaluator(wb);
  }
View Full Code Here
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
        HSSFCell cell = row.createCell(0);

        
        cell.setCellFormula("isblank(Sheet2!A1:A1)");
       
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
        CellValue result = fe.evaluate(cell);
        assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType());
        assertEquals(true, result.getBooleanValue());
       
        cell.setCellFormula("isblank(D7:D7)");
       
        result = fe.evaluate(cell);
        assertEquals(HSSFCell.CELL_TYPE_BOOLEAN, result.getCellType());
        assertEquals(true, result.getBooleanValue());
   }
View Full Code Here
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
    }
  }

  private int processTestSheet(HSSFWorkbook workbook, int sheetIndex, String sheetName) {
    HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
    int maxRows = sheet.getLastRowNum()+1;
    int result = Result.NO_EVALUATIONS_FOUND; // so far

    String currentGroupComment = null;
    for(int rowIndex=SS.START_TEST_CASES_ROW_INDEX; rowIndex<maxRows; rowIndex++) {
      HSSFRow r = sheet.getRow(rowIndex);
      String newMarkerValue = getMarkerColumnValue(r);
      if(r == null) {
        continue;
      }
      if(SS.TEST_CASES_END_MARKER.equalsIgnoreCase(newMarkerValue)) {
        // normal exit point
        return result;
      }
      if(SS.SKIP_CURRENT_TEST_CASE_MARKER.equalsIgnoreCase(newMarkerValue)) {
        // currently disabled test case row
        continue;
      }
      if(newMarkerValue != null) {
        currentGroupComment = newMarkerValue;
      }
      HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
      if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
        continue;
      }
      CellValue actualValue = evaluator.evaluate(c);
      HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);
      String rowComment = getRowCommentColumnValue(r);

      String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c, currentGroupComment, rowComment);
      try {
View Full Code Here
55
56
57
58
59
60
61
62
63
64
    cell.setCellValue(15.0);
    assertEquals("NPER(12,4500,100000,100000)", cell.getCellFormula());
    assertEquals(HSSFCell.CELL_TYPE_NUMERIC, cell.getCachedFormulaResultType());
    assertEquals(15.0, cell.getNumericCellValue(), 0.0);

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    fe.evaluateFormulaCell(cell);
    assertEquals(HSSFCell.CELL_TYPE_ERROR, cell.getCachedFormulaResultType());
    assertEquals(HSSFErrorConstants.ERROR_NUM, cell.getErrorCellValue());
  }
View Full Code Here
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
    }
  }

  private void processTestSheet(HSSFWorkbook workbook, String sheetName) {
    HSSFSheet sheet = workbook.getSheetAt(0);
    HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(workbook);
    int maxRows = sheet.getLastRowNum()+1;
    int result = Result.NO_EVALUATIONS_FOUND; // so far

    for(int rowIndex=0; rowIndex<maxRows; rowIndex++) {
      HSSFRow r = sheet.getRow(rowIndex);
      if(r == null) {
        continue;
      }
      HSSFCell c = r.getCell(SS.COLUMN_INDEX_EVALUATION);
      if (c == null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA) {
        continue;
      }
      CellValue actualValue = evaluator.evaluate(c);
      HSSFCell expectedValueCell = r.getCell(SS.COLUMN_INDEX_EXPECTED_RESULT);

      String msgPrefix = formatTestCaseDetails(sheetName, r.getRowNum(), c);
      try {
        confirmExpectedResult(msgPrefix, expectedValueCell, actualValue);
View Full Code Here
34
35
36
37
38
39
40
41
42
43
44
  public void testFind() {
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFCell cell = wb.createSheet().createRow(0).createCell(0);

    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

    confirmResult(fe, cell, "find(\"h\", \"haystack\")", 1);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",2)", 2);
    confirmResult(fe, cell, "find(\"a\", \"haystack\",3)", 6);
View Full Code Here
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
    final int COL_IX_EXPECTED = 3;

    int failureCount = 0;
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook(FILE_NAME);
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    int maxRow = sheet.getLastRowNum();
    for (int rowIx=START_ROW_IX; rowIx<maxRow; rowIx++) {
      HSSFRow row = sheet.getRow(rowIx);
      if(row == null) {
        continue;
      }
      HSSFCell cell = row.getCell(COL_IX_ACTUAL);
      CellValue cv = fe.evaluate(cell);
      double actualValue = cv.getNumberValue();
      double expectedValue = row.getCell(COL_IX_EXPECTED).getNumericCellValue();
      if (actualValue != expectedValue) {
        System.err.println("Problem with test case on row " + (rowIx+1) + " "
            + "Expected = (" + expectedValue + ") Actual=(" + actualValue + ") ");
View Full Code Here
72
73
74
75
76
77
78
79
80
81
82
  }
 
  public void testEvaluate() {
    HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("externalFunctionExample.xls");
    HSSFSheet sheet = wb.getSheetAt(0);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    confirmCellEval(sheet, 0, 0, fe, "YEARFRAC(B1,C1)", 29.0/90.0);
    confirmCellEval(sheet, 1, 0, fe, "YEARFRAC(B2,C2)", 0.0);
    confirmCellEval(sheet, 2, 0, fe, "YEARFRAC(B3,C3,D3)", 0.0);
    confirmCellEval(sheet, 3, 0, fe, "IF(ISEVEN(3),1.2,1.6)", 1.6);
    confirmCellEval(sheet, 4, 0, fe, "IF(ISODD(3),1.2,1.6)", 1.2);
View Full Code Here
145
146
147
148
149
150
151
152
153
154
155
156
157
158
     *  http://office.microsoft.com/en-us/excel-help/intercept-function-HP010062512.aspx?CTT=5&origin=HA010277524
     */
    public void testFromFile() {

        HSSFWorkbook wb = HSSFTestDataSamples.openSampleWorkbook("intercept.xls");
        HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

        HSSFSheet example1 = wb.getSheet("Example 1");
        HSSFCell a8 = example1.getRow(7).getCell(0);
        assertEquals("INTERCEPT(A2:A6,B2:B6)", a8.getCellFormula());
        fe.evaluate(a8);
        assertEquals(0.048387097, a8.getNumericCellValue(), 0.000000001);

    }
View Full Code Here
TOP

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

  • 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.