Package org.zkoss.poi.ss.usermodel

Examples of org.zkoss.poi.ss.usermodel.CellValue

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
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
169
170
171
172
173
174
175
176
177
178
    assertEquals(11, row11.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B11: 11
    assertEquals(12, row12.getCell(1).getNumericCellValue(), 0.0000000000000001); //B12: 12
   
    //C9: =B8
    Cell cellC9 = row9.getCell(2);
    CellValue valueC9 = _evaluator.evaluate(cellC9);
    assertEquals(8, valueC9.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC9.getCellType());
    testToFormulaString(cellC9, "B8");
   
    //C13: =B12
    Cell cellC13 = row13.getCell(2);
    CellValue valueC13 = _evaluator.evaluate(cellC13);
    assertEquals(12, valueC13.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC13.getCellType());
    testToFormulaString(cellC13, "B12");
   
    //Sort A1:C12
    ChangeInfo info = BookHelper.sort(sheet1, 0, 0, 11, 2, ((RangeImpl)Utils.getRange(sheet1, 0, 0)).getRefs().iterator().next(), false,
        null, 0, false, null, false, BookHelper.SORT_HEADER_NO, 0, false, false, 0,
        BookHelper.SORT_NORMAL_DEFAULT, BookHelper.SORT_NORMAL_DEFAULT, BookHelper.SORT_NORMAL_DEFAULT);
    Set<Ref> last = info.getToEval();
    Set<Ref> all = info.getAffected();
    _evaluator.notifySetFormula(cellC13);

    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row2.getCell(0).getNumericCellValue(), 0.0000000000000001);  //A2: 2
    assertEquals(3, row3.getCell(0).getNumericCellValue(), 0.0000000000000001); //A3: 3
    assertEquals("a", row4.getCell(0).getStringCellValue()); //A4: "a"
    assertEquals("b", row5.getCell(0).getStringCellValue()); //A5: "b"
    assertEquals("c", row6.getCell(0).getStringCellValue()); //A6: "c"
    assertEquals(false, row7.getCell(0).getBooleanCellValue()); //A7: FALSE
    assertEquals(true, row8.getCell(0).getBooleanCellValue()); //A8: TRUE
    assertEquals(ErrorConstants.ERROR_VALUE, row9.getCell(0).getErrorCellValue()); //A9: #VALUE!
    assertEquals(ErrorConstants.ERROR_REF, row10.getCell(0).getErrorCellValue()); //A10: #REF!
    assertEquals(ErrorConstants.ERROR_VALUE, row11.getCell(0).getErrorCellValue()); //A11: #VALUE!
    assertNull(row12.getCell(0)); //A9: null
   
    assertEquals(1, row1.getCell(1).getNumericCellValue(), 0.0000000000000001); //B1: 1
    assertEquals(2, row2.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B2: 2
    assertEquals(3, row3.getCell(1).getNumericCellValue(), 0.0000000000000001); //B3: 3
    assertEquals(4, row4.getCell(1).getNumericCellValue(), 0.0000000000000001); //B4: 4
    assertEquals(5, row5.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B5: 5
    assertEquals(6, row6.getCell(1).getNumericCellValue(), 0.0000000000000001); //B6: 6
    assertEquals(12, row7.getCell(1).getNumericCellValue(), 0.0000000000000001); //B7: 12
    assertEquals(11, row8.getCell(1).getNumericCellValue(), 0.0000000000000001); //B8: 11
    assertEquals(7, row9.getCell(1).getNumericCellValue(), 0.0000000000000001); //B9: 7
    assertEquals(8, row10.getCell(1).getNumericCellValue(), 0.0000000000000001); //B10: 8
    assertEquals(10, row11.getCell(1).getNumericCellValue(), 0.0000000000000001); //B11: 10
    assertEquals(9, row12.getCell(1).getNumericCellValue(), 0.0000000000000001); //B12: 9

    //C9 -> C12: =B8 -> B11
    Cell cellC12 = row12.getCell(2);
    CellValue valueC12 = _evaluator.evaluate(cellC12);
    assertEquals(10, valueC12.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC12.getCellType());
    testToFormulaString(cellC12, "B11");
   
    //C13: =B12
    cellC13 = row13.getCell(2);
    valueC13 = _evaluator.evaluate(cellC13);
View Full Code Here
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
    assertEquals("K", row16.getCell(10).getStringCellValue()); //K16: "K"
    assertEquals("L", row16.getCell(11).getStringCellValue()); //L16: "L"
   
    //I17: =H16
    Cell cellI17 = row17.getCell(8);
    CellValue valueI17 = _evaluator.evaluate(cellI17);
    assertEquals("H", valueI17.getStringValue());
    assertEquals(Cell.CELL_TYPE_STRING, valueI17.getCellType());
    testToFormulaString(cellI17, "H16");
   
    //M17: =L16
    Cell cellM17 = row17.getCell(12);
    CellValue valueM17 = _evaluator.evaluate(cellM17);
    assertEquals("L", valueM17.getStringValue());
    assertEquals(Cell.CELL_TYPE_STRING, valueM17.getCellType());
    testToFormulaString(cellM17, "L16");
   
    /*sort(Sheet sheet, int tRow, int lCol, int bRow, int rCol,
        Ref key1, boolean desc1, Ref key2, int type, boolean desc2, Ref key3, boolean desc3, int header, int orderCustom,
        boolean matchCase, boolean sortByRows, int sortMethod, int dataOption1, int dataOption2, int dataOption3) */

    //Sort A15:L17
    ChangeInfo info = BookHelper.sort(sheet1, 14, 0, 16, 11, ((RangeImpl)Utils.getRange(sheet1, 14, 0)).getRefs().iterator().next(), false,
        null, 0, false, null, false, BookHelper.SORT_HEADER_NO, 0, false, true, 0,
        BookHelper.SORT_NORMAL_DEFAULT, BookHelper.SORT_NORMAL_DEFAULT, BookHelper.SORT_NORMAL_DEFAULT);
    Set<Ref> last = info.getToEval();
    Set<Ref> all = info.getAffected();
    _evaluator.notifySetFormula(cellM17);

    assertEquals(1, row15.getCell(0).getNumericCellValue(), 0.0000000000000001); //A15: 1
    assertEquals(2, row15.getCell(1).getNumericCellValue(), 0.0000000000000001); //B15: 2
    assertEquals(3, row15.getCell(2).getNumericCellValue(), 0.0000000000000001); //C15: 3
    assertEquals("a", row15.getCell(3).getStringCellValue()); //D15: "a"
    assertEquals("b", row15.getCell(4).getStringCellValue()); //E15: "b"
    assertEquals("c", row15.getCell(5).getStringCellValue()); //F15: "c"
    assertEquals(false, row15.getCell(6).getBooleanCellValue()); //G15: FALSE
    assertEquals(true, row15.getCell(7).getBooleanCellValue()); //H15: TRUE
    assertEquals(ErrorConstants.ERROR_VALUE, row15.getCell(8).getErrorCellValue()); //I15: #VALUE!
    assertEquals(ErrorConstants.ERROR_REF, row15.getCell(9).getErrorCellValue()); //J15: #REF!
    assertEquals(ErrorConstants.ERROR_VALUE, row15.getCell(10).getErrorCellValue()); //K15: #VALUE!
    assertNull(row15.getCell(11)); //L15: null
   
    assertEquals("A", row16.getCell(0).getStringCellValue()); //A16: "A"
    assertEquals("B", row16.getCell(1).getStringCellValue()); //B16: "B"
    assertEquals("C", row16.getCell(2).getStringCellValue()); //C16: "C"
    assertEquals("D", row16.getCell(3).getStringCellValue()); //D16: "D"
    assertEquals("E", row16.getCell(4).getStringCellValue()); //E16: "E"
    assertEquals("F", row16.getCell(5).getStringCellValue()); //F16: "F"
    assertEquals("L", row16.getCell(6).getStringCellValue()); //G16: "L"
    assertEquals("K", row16.getCell(7).getStringCellValue()); //H16: "K"
    assertEquals("G", row16.getCell(8).getStringCellValue()); //I16: "G"
    assertEquals("H", row16.getCell(9).getStringCellValue()); //J16: "H"
    assertEquals("J", row16.getCell(10).getStringCellValue()); //K16: "J"
    assertEquals("I", row16.getCell(11).getStringCellValue()); //L16: "I"
   
    //I17 -> L17: =H16 -> K16
    Cell cellL17 = row17.getCell(11);
    CellValue valueL17 = _evaluator.evaluate(cellL17);
    assertEquals("J", valueL17.getStringValue());
    assertEquals(Cell.CELL_TYPE_STRING, valueL17.getCellType());
    testToFormulaString(cellL17, "K16");
   
    //M17: =L16
    cellM17 = row17.getCell(12);
    valueM17 = _evaluator.evaluate(cellM17);
View Full Code Here
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3: =A1+7
    Cell cellC3 = row3.getCell(2);
    CellValue valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //Copy cell (C3 -> D4)
    BookHelper.copyCell(cellC3, sheet1, 3, 3, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellC3);

    //A1,A2,A2,B2 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3 stay as is
    valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //D4: =B2+7
    Row row4 = sheet1.getRow(3);
    Cell cellD4 = row4.getCell(3);
    CellValue valueD4 = _evaluator.evaluate(cellD4);
    assertEquals(11, valueD4.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD4.getCellType());
    testToFormulaString(cellD4, "B2+7");
  }
View Full Code Here
126
127
128
129
130
131
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
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3: =A1+7
    Cell cellC3 = row3.getCell(2);
    CellValue valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //Copy cell (C3 -> C2)
    BookHelper.copyCell(cellC3, sheet1, 1, 2, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellC3);

    //A1,A2,A2,B2 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
   
    //C3 stay as is
    valueC3 = _evaluator.evaluate(cellC3);
    assertEquals(8, valueC3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC3.getCellType());
    testToFormulaString(cellC3, "A1+7");
   
    //C2: #REF!
    Cell cellC2 = row2.getCell(2);
    CellValue valueC2 = _evaluator.evaluate(cellC2);
    assertEquals(ErrorConstants.ERROR_REF, valueC2.getErrorValue());
    assertEquals(Cell.CELL_TYPE_ERROR, valueC2.getCellType());
    testToFormulaString(cellC2, "#REF!+7");
  }
View Full Code Here
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
    assertEquals(5, row1.getCell(4).getNumericCellValue(), 0.0000000000000001); //E1: 5
    assertEquals(6, row1.getCell(5).getNumericCellValue(), 0.0000000000000001); //F1: 6
   
    //D3: =SUM(E1:F1)
    Cell cellD3 = row3.getCell(3);
    CellValue valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(11, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "SUM(E1:F1)");
   
    //Copy cell (D3 -> XFD3)
    BookHelper.copyCell(cellD3, sheet1, 2, 16383, Range.PASTE_ALL, Range.PASTEOP_NONE, false);
    _evaluator.notifySetFormula(cellD3);

    //A1,A2,A2,B2,E1,F1 stay as is
    assertEquals(1, row1.getCell(0).getNumericCellValue(), 0.0000000000000001); //A1: 1
    assertEquals(2, row1.getCell(1).getNumericCellValue(), 0.0000000000000001);  //B1: 2
    assertEquals(3, row2.getCell(0).getNumericCellValue(), 0.0000000000000001); //A2: 3
    assertEquals(4, row2.getCell(1).getNumericCellValue(), 0.0000000000000001); //B2: 4
    assertEquals(5, row1.getCell(4).getNumericCellValue(), 0.0000000000000001); //E1: 5
    assertEquals(6, row1.getCell(5).getNumericCellValue(), 0.0000000000000001); //F1: 6
   
    //D3 stay as is
    valueD3 = _evaluator.evaluate(cellD3);
    assertEquals(11, valueD3.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD3.getCellType());
    testToFormulaString(cellD3, "SUM(E1:F1)");
   
    //XFD3: #REF!
    Cell cellXFD3 = row3.getCell(16383);
    CellValue valueIV3 = _evaluator.evaluate(cellXFD3);
    assertEquals(ErrorConstants.ERROR_REF, valueIV3.getErrorValue());
    assertEquals(Cell.CELL_TYPE_ERROR, valueIV3.getCellType());
    testToFormulaString(cellXFD3, "SUM(#REF!)");
  }
View Full Code Here
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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
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
169
170
171
    assertEquals(11, row10.getCell(1).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(B5:C5)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0); //A1: =SUM(B5:C5)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B5:C5)");

    //B1: =SUM(B5:B10)
    Cell cellB1 = row1.getCell(1); //B1: =SUM(B5:B10)
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(36, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B10)");
   
    //C1: =SUM(B8:B10)
    Cell cellC1 = row1.getCell(2); //C1: =SUM(B8:B10)
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B8:B10)");
   
    //6 merge area
    assertEquals(6, sheet1.getNumMergedRegions());
    for (int j = 0; j < 6; ++j) {
      CellRangeAddress rng = sheet1.getMergedRegion(j);
      switch(j) {
      case 0:
        assertEquals("E6:F8", rng.formatAsString());
        break;
      case 1:
        assertEquals("E10:F12", rng.formatAsString());
        break;
      case 2:
        assertEquals("G5:H5", rng.formatAsString());
        break;
      case 3:
        assertEquals("E3:F4", rng.formatAsString());
        break;
      case 4:
        assertEquals("I3:J7", rng.formatAsString());
        break;
      case 5:
        assertEquals("E14:F15", rng.formatAsString());
        break;
      }
    }
 
    //remove rows 4 ~ 6
    BookHelper.deleteRows(sheet1, 3, 3); //remove rows 4 ~ 6
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellB1);
    _evaluator.notifySetFormula(cellC1);
   
    //B4: 5, B5:7, C5: empty, B6: 9, B7: 11, row 8 ~ row 10 empty
    Row row4 = sheet1.getRow(3);
    row5 = sheet1.getRow(4);
    row6 = sheet1.getRow(5);
    row7 = sheet1.getRow(6);
   
    row8 = sheet1.getRow(7);
    row9 = sheet1.getRow(8);
    row10 = sheet1.getRow(9);
   
    assertNull(row5.getCell(2)); //C5 not exist
    assertNull(row8.getCell(1));
    assertNull(row9.getCell(1));
    assertNull(row10);
   
    assertEquals(5, row4.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row5.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row6.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row7.getCell(1).getNumericCellValue(), 0.0000000000000001);
   
    //C1: =SUM(B5:B7)
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B5:B7)");
   
    //B1: =SUM(B4:B7)
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(32, valueB1.getNumberValue(), 0.0000000000000001);
View Full Code Here
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
    assertEquals(11, row10.getCell(1).getNumericCellValue(), 0.0000000000000001);

    //A1: =SUM(B5:C5)
    Row row1 = sheet1.getRow(0);
    Cell cellA1 = row1.getCell(0); //A1: =SUM(B5:C5)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(3, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(B5:C5)");

    //B1: =SUM(B5:B10)
    Cell cellB1 = row1.getCell(1); //B1: =SUM(B5:B10)
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(36, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B10)");
   
    //C1: =SUM(B8:B10)
    Cell cellC1 = row1.getCell(2); //C1: =SUM(B8:B10)
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B8:B10)");
   
    //6 merge area
    assertEquals(6, sheet1.getNumMergedRegions());
    for (int j = 0; j < 6; ++j) {
      CellRangeAddress rng = sheet1.getMergedRegion(j);
      switch(j) {
      case 0:
        assertEquals("E6:F8", rng.formatAsString());
        break;
      case 1:
        assertEquals("E10:F12", rng.formatAsString());
        break;
      case 2:
        assertEquals("G5:H5", rng.formatAsString());
        break;
      case 3:
        assertEquals("E3:F4", rng.formatAsString());
        break;
      case 4:
        assertEquals("I3:J7", rng.formatAsString());
        break;
      case 5:
        assertEquals("E14:F15", rng.formatAsString());
        break;
      }
    }
 
    //remove A4:J6
    BookHelper.deleteRange(sheet1, 3, 0, 5, 9, false);
    _evaluator.notifySetFormula(cellA1);
    _evaluator.notifySetFormula(cellB1);
    _evaluator.notifySetFormula(cellC1);
   
    //B4: 5, B5:7, C5: empty, B6: 9, B7: 11, row 8 ~ row 10 empty
    Row row4 = sheet1.getRow(3);
    row5 = sheet1.getRow(4);
    row6 = sheet1.getRow(5);
    row7 = sheet1.getRow(6);
   
    row8 = sheet1.getRow(7);
    row9 = sheet1.getRow(8);
    row10 = sheet1.getRow(9);
   
    assertNull(row5.getCell(2)); //C5 not exist
    assertNull(row8.getCell(1));
    assertNull(row9.getCell(1));
    assertNull(row10.getCell(1));
   
    assertEquals(5, row4.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(7, row5.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(9, row6.getCell(1).getNumericCellValue(), 0.0000000000000001);
    assertEquals(11, row7.getCell(1).getNumericCellValue(), 0.0000000000000001);
   
    //C1: =SUM(B5:B7)
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(27, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B5:B7)");
   
    //B1: =SUM(B4:B7)
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(32, valueB1.getNumberValue(), 0.0000000000000001);
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
    assertEquals(2, row7.getCell(3).getNumericCellValue(), 0.0000000000000001);  //D7: 2
    assertNull(sheet1.getRow(4)); //B5
   
    //C6: =A9
    Cell cellC6 = row6.getCell(2);
    CellValue valueC6 = _evaluator.evaluate(cellC6);
    assertEquals(1, valueC6.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC6.getCellType());
    testToFormulaString(cellC6, "A9");

    //A10: =C6
    Cell cellA10 = row10.getCell(0);
    CellValue valueA10 = _evaluator.evaluate(cellA10);
    assertEquals(1, valueA10.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA10.getCellType());
    testToFormulaString(cellA10, "C6");
   
    //A11: =B5
    Cell cellA11 = row11.getCell(0);
    CellValue valueA11 = _evaluator.evaluate(cellA11);
    assertEquals(0, valueA11.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA11.getCellType());
    testToFormulaString(cellA11, "B5");

    //A12: =D7
    Cell cellA12 = row12.getCell(0);
    CellValue valueA12 = _evaluator.evaluate(cellA12);
    assertEquals(2, valueA12.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA12.getCellType());
    testToFormulaString(cellA12, "D7");
   
    //merge B5:D7
    BookHelper.merge(sheet1, 4, 1, 6, 3, true);
   
    Row row5 = sheet1.getRow(4);
    assertEquals(Cell.CELL_TYPE_FORMULA, row5.getCell(1).getCellType()); //B5: null -> =A9
    assertEquals(Cell.CELL_TYPE_BLANK, row5.getCell(2).getCellType()); //C5
    assertEquals(Cell.CELL_TYPE_BLANK, row5.getCell(3).getCellType()); //D5
    assertNull(row5.getCell(4)); //E5
    assertEquals(Cell.CELL_TYPE_BLANK, row6.getCell(1).getCellType()); //B6
    assertEquals(Cell.CELL_TYPE_BLANK, row6.getCell(2).getCellType()); //C6: =A9 -> blank
    assertEquals(Cell.CELL_TYPE_BLANK, row6.getCell(3).getCellType()); //D6
    assertNull(row6.getCell(4)); //E6
    assertEquals(Cell.CELL_TYPE_BLANK, row7.getCell(1).getCellType()); //B7
    assertEquals(Cell.CELL_TYPE_BLANK, row7.getCell(2).getCellType()); //C7
    assertEquals(Cell.CELL_TYPE_BLANK, row7.getCell(3).getCellType()); //D7: 2 -> blank
    assertNull(row7.getCell(4)); //E7
   
    //B5: =A9
    Cell cellB5 = row5.getCell(1);
    CellValue valueB5 = _evaluator.evaluate(cellB5);
    assertEquals(1, valueB5.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB5.getCellType());
    testToFormulaString(cellB5, "A9");

    //A10: =C6 -> =B5
    _evaluator.notifySetFormula(cellA10);
    valueA10 = _evaluator.evaluate(cellA10);
View Full Code Here
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
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
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
    assertEquals(3, row8.getCell(2).getNumericCellValue(), 0.0000000000000001); //C8: 3
    assertEquals(7, row1.getCell(3).getNumericCellValue(), 0.0000000000000001); //D1: 7
   
    //A1: =SUM(A8:C8)
    Cell cellA1 = row1.getCell(0); //A1: =SUM(A8:C8)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(A8:C8)");
   
    //B1: =SUM(B5:B1048576)
    Cell cellB1 = row1.getCell(1);
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B1048576)");
    assertEquals(2, valueB1.getNumberValue(), 0.0000000000000001);

    //C1: =SUM(B1048575:B1048576)
    Cell cellC1 = row1.getCell(2);
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(0, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B1048575:B1048576)");
   
    //A10: =SUM(A8:C8)
    Row row10 = sheet1.getRow(9);
    Cell cellA10 = row10.getCell(0);
    CellValue valueA10 = _evaluator.evaluate(cellA10);
    assertEquals(6, valueA10.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA10.getCellType());
    testToFormulaString(cellA10, "SUM(A8:C8)");

    //D10: =D1
    Cell cellD10 = row10.getCell(3);
    CellValue valueD10 = _evaluator.evaluate(cellD10);
    assertEquals(7, valueD10.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD10.getCellType());
    testToFormulaString(cellD10, "D1");
   
    //Insert A5:D5
    BookHelper.insertRange(sheet1, 4, 0, 4, 3, false, Range.FORMAT_LEFTABOVE);
    _evaluator.notifySetFormula(cellC1);
   
    Row row9 = sheet1.getRow(8);
    assertEquals(1, row9.getCell(0).getNumericCellValue(), 0.0000000000000001); //A9: 1
    assertEquals(2, row9.getCell(1).getNumericCellValue(), 0.0000000000000001); //B9: 2
    assertEquals(3, row9.getCell(2).getNumericCellValue(), 0.0000000000000001); //C9: 3
    assertEquals(7, row1.getCell(3).getNumericCellValue(), 0.0000000000000001); //D1: 7

    //A1: =SUM(A9:C9)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(A9:C9)");
   
    //B1: =SUM(B6:B1048576)
    cellB1 = row1.getCell(1);
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(2, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B6:B1048576)");

    //C1: =SUM(B1048576:B1048576)
    cellC1 = row1.getCell(2);
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(0, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B1048576:B1048576)");
   
    //A11: =SUM(A9:C9)
    Row row11 = sheet1.getRow(10);
    Cell cellA11 = row11.getCell(0);
    CellValue valueA11 = _evaluator.evaluate(cellA11);
    assertEquals(6, valueA11.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA11.getCellType());
    testToFormulaString(cellA11, "SUM(A9:C9)");

    //D11: =D1
    Cell cellD11 = row11.getCell(3); //D11: =D1
    CellValue valueD11 = _evaluator.evaluate(cellD11);
    assertEquals(7, valueD11.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD11.getCellType());
    testToFormulaString(cellD11, "D1");

  }
View Full Code Here
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
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
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
    assertEquals(3, row8.getCell(2).getNumericCellValue(), 0.0000000000000001); //C8: 3
    assertEquals(7, row1.getCell(3).getNumericCellValue(), 0.0000000000000001); //D1: 7
   
    //A1: =SUM(A8:C8)
    Cell cellA1 = row1.getCell(0); //A1: =SUM(A8:C8)
    CellValue valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(A8:C8)");
   
    //B1: =SUM(B5:B1048576)
    Cell cellB1 = row1.getCell(1);
    CellValue valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B5:B1048576)");
    assertEquals(2, valueB1.getNumberValue(), 0.0000000000000001);

    //C1: =SUM(B1048575:B1048576)
    Cell cellC1 = row1.getCell(2);
    CellValue valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(0, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B1048575:B1048576)");
   
    //A10: =SUM(A8:C8)
    Row row10 = sheet1.getRow(9);
    Cell cellA10 = row10.getCell(0);
    CellValue valueA10 = _evaluator.evaluate(cellA10);
    assertEquals(6, valueA10.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA10.getCellType());
    testToFormulaString(cellA10, "SUM(A8:C8)");

    //D10: =D1
    Cell cellD10 = row10.getCell(3);
    CellValue valueD10 = _evaluator.evaluate(cellD10);
    assertEquals(7, valueD10.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD10.getCellType());
    testToFormulaString(cellD10, "D1");
   
    //Insert row 5
    BookHelper.insertRows(sheet1, 4, 1, Range.FORMAT_LEFTABOVE);
    _evaluator.notifySetFormula(cellC1);
   
    //height shall be the same
    Row row4 = sheet1.getRow(3);
    Row row5 = sheet1.getRow(4);
    assertEquals(row4.getHeight(), row5.getHeight());
   
    Row row9 = sheet1.getRow(8);
    assertEquals(1, row9.getCell(0).getNumericCellValue(), 0.0000000000000001); //A9: 1
    assertEquals(2, row9.getCell(1).getNumericCellValue(), 0.0000000000000001); //B9: 2
    assertEquals(3, row9.getCell(2).getNumericCellValue(), 0.0000000000000001); //C9: 3
    assertEquals(7, row1.getCell(3).getNumericCellValue(), 0.0000000000000001); //D1: 7

    //A1: =SUM(A9:C9)
    cellA1 = row1.getCell(0);
    valueA1 = _evaluator.evaluate(cellA1);
    assertEquals(6, valueA1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA1.getCellType());
    testToFormulaString(cellA1, "SUM(A9:C9)");
   
    //B1: =SUM(B6:B1048576)
    cellB1 = row1.getCell(1);
    valueB1 = _evaluator.evaluate(cellB1);
    assertEquals(2, valueB1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueB1.getCellType());
    testToFormulaString(cellB1, "SUM(B6:B1048576)");

    //C1: =SUM(B1048576:B1048576)
    cellC1 = row1.getCell(2);
    valueC1 = _evaluator.evaluate(cellC1);
    assertEquals(0, valueC1.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueC1.getCellType());
    testToFormulaString(cellC1, "SUM(B1048576:B1048576)");
   
    //A11: =SUM(A9:C9)
    Row row11 = sheet1.getRow(10);
    Cell cellA11 = row11.getCell(0);
    CellValue valueA11 = _evaluator.evaluate(cellA11);
    assertEquals(6, valueA11.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueA11.getCellType());
    testToFormulaString(cellA11, "SUM(A9:C9)");

    //D11: =D1
    Cell cellD11 = row11.getCell(3); //D11: =D1
    CellValue valueD11 = _evaluator.evaluate(cellD11);
    assertEquals(7, valueD11.getNumberValue(), 0.0000000000000001);
    assertEquals(Cell.CELL_TYPE_NUMERIC, valueD11.getCellType());
    testToFormulaString(cellD11, "D1");

  }
View Full Code Here
TOP

Related Classes of org.zkoss.poi.ss.usermodel.CellValue

  • org.zkoss.zss.model.impl.Book01XlsFormulaHitTest
  • org.zkoss.zss.model.impl.Book03XlsFormulaEvaluatorTest
  • org.zkoss.zss.model.impl.Book03XlsxFormulaEvaluatorTest
  • org.zkoss.zss.model.impl.Book04XlsInsertRowsTest
  • org.zkoss.zss.model.impl.Book04XlsxInsertRowsTest
  • org.zkoss.zss.model.impl.Book05XlsDeleteRowsTest
  • org.zkoss.zss.model.impl.Book05XlsxDeleteRowsTest
  • org.zkoss.zss.model.impl.Book06XlsInsertColumnsTest
  • org.zkoss.zss.model.impl.Book06XlsxInsertColumnsTest
  • org.zkoss.zss.model.impl.Book07XlsDeleteColumnsTest
    • 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.