Package org.apache.poi.hssf.usermodel

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

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
     *  from http://office.microsoft.com/en-001/excel-help/ipmt-HP005209145.aspx
     */
    public void testFromFile() {

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

        HSSFSheet example1 = wb.getSheet("IPMT");
        HSSFCell ex1cell1 = example1.getRow(6).getCell(0);
        fe.evaluate(ex1cell1);
        assertEquals(-22.41, ex1cell1.getNumericCellValue(), 0.1);

        HSSFCell ex1cell2 = example1.getRow(7).getCell(0);
        fe.evaluate(ex1cell2);
        assertEquals(-292.45, ex1cell2.getNumericCellValue(), 0.1);

    }
View Full Code Here
35
36
37
38
39
40
41
42
43
44
45
46
  /**
   * 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
117
118
119
120
121
122
123
124
125
126
127
128
129
        if (null == cell) {
            return EMPTY;
        }
        final int cellValueType;
        if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
             final HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet, workbook);
             evaluator.setCurrentRow(row);
             cellValueType = evaluator.evaluateFormulaCell(cell);
        }
        else {
          cellValueType = cell.getCellType();
        }
       
View Full Code Here
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
   
    public void genericTest() throws Exception {
        HSSFSheet s = workbook.getSheetAt( 0 );
        HSSFRow r = s.getRow(getBeginRow());
        short endcolnum = r.getLastCellNum();
        HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(s, workbook);
        evaluator.setCurrentRow(r);

        HSSFCell c = null;
        for (short colnum=getBeginCol(); colnum < endcolnum; colnum++) {
            try {
            c = r.getCell(colnum);
            if (c==null || c.getCellType() != HSSFCell.CELL_TYPE_FORMULA)
                continue;
           
            HSSFFormulaEvaluator.CellValue actualValue = evaluator.evaluate(c);
           
            HSSFCell expectedValueCell = getExpectedValueCell(s, r, c);
            assertEquals("Formula: " + c.getCellFormula()
                    + " @ " + getBeginRow() + ":" + colnum,
                    expectedValueCell, actualValue);
View Full Code Here
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
  public void testEvaluateMultipleWorkbooks() {
    HSSFWorkbook wbA = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaA.xls");
    HSSFWorkbook wbB = HSSFTestDataSamples.openSampleWorkbook("multibookFormulaB.xls");

    HSSFFormulaEvaluator evaluatorA = new HSSFFormulaEvaluator(wbA);
    HSSFFormulaEvaluator evaluatorB = new HSSFFormulaEvaluator(wbB);

    // Hook up the workbook evaluators to enable evaluation of formulas across books
    String[] bookNames = { "multibookFormulaA.xls", "multibookFormulaB.xls", };
    HSSFFormulaEvaluator[] evaluators = { evaluatorA, evaluatorB, };
    HSSFFormulaEvaluator.setupEnvironment(bookNames, evaluators);

    HSSFCell cell;

    HSSFSheet aSheet1 = wbA.getSheetAt(0);
    HSSFSheet bSheet1 = wbB.getSheetAt(0);

    // Simple case - single link from wbA to wbB
    confirmFormula(wbA, 0, 0, 0, "[multibookFormulaB.xls]BSheet1!B1");
    cell = aSheet1.getRow(0).getCell(0);
    confirmEvaluation(35, evaluatorA, cell);


    // more complex case - back link into wbA
    // [wbA]ASheet1!A2 references (among other things) [wbB]BSheet1!B2
    confirmFormula(wbA, 0, 1, 0, "[multibookFormulaB.xls]BSheet1!$B$2+2*A3");
    // [wbB]BSheet1!B2 references (among other things) [wbA]AnotherSheet!A1:B2
    confirmFormula(wbB, 0, 1, 1, "SUM([multibookFormulaA.xls]AnotherSheet!$A$1:$B$2)+B3");

    cell = aSheet1.getRow(1).getCell(0);
    confirmEvaluation(264, evaluatorA, cell);

    // change [wbB]BSheet1!B3 (from 50 to 60)
    HSSFCell cellB3 = bSheet1.getRow(2).getCell(1);
    cellB3.setCellValue(60);
    evaluatorB.notifyUpdateCell(cellB3);
    confirmEvaluation(274, evaluatorA, cell);

    // change [wbA]ASheet1!A3 (from 100 to 80)
    HSSFCell cellA3 = aSheet1.getRow(2).getCell(0);
    cellA3.setCellValue(80);
View Full Code Here
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
    HSSFWorkbook wb = new HSSFWorkbook();
    HSSFSheet sheet = wb.createSheet("Sheet1");
    HSSFRow row = sheet.createRow(0);
    HSSFCell cellA1 = row.createCell(0);
    HSSFCell cellB1 = row.createCell(1);
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

    cellA1.setCellFormula("B1+2.2");
    cellB1.setCellValue(1.5);

    fe.notifyUpdateCell(cellA1);
    fe.notifyUpdateCell(cellB1);

    CellValue cv;
    cv = fe.evaluate(cellA1);
    assertEquals(3.7, cv.getNumberValue(), 0.0);

    cellB1.setCellType(HSSFCell.CELL_TYPE_BLANK);
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1); // B1 was used to evaluate A1
    assertEquals(2.2, cv.getNumberValue(), 0.0);

    cellB1.setCellValue(0.4);  // changing B1, so A1 cached result should be cleared
    fe.notifyUpdateCell(cellB1);
    cv = fe.evaluate(cellA1);
    if (cv.getNumberValue() == 2.2) {
      // looks like left-over cached result from before change to B1
      throw new AssertionFailedError("Identified bug 46053");
    }
    assertEquals(2.6, cv.getNumberValue(), 0.0);
View Full Code Here
86
87
88
89
90
91
92
93
94
95
96
97
98
99
    // put some values in the cells to make the evaluation more interesting
    sheet.createRow(32768).createCell(0).setCellValue(31);
    sheet.createRow(32769).createCell(0).setCellValue(11);
   
    HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
    CellValue result;
    try {
      result = fe.evaluate(cell);
    } catch (RuntimeException e) {
      FormulaParserTestHelper.confirmParseException(e);
      if (!e.getMessage().equals("Found reference to named range \"A\", but that named range wasn't defined!")) {
        throw new AssertionFailedError("Identifed bug 44539");
      }
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
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
160
161
162
    }


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