1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 package fvt.uno.sc.data; 22 23 import static org.junit.Assert.*; 24 25 import org.junit.After; 26 import org.junit.AfterClass; 27 import org.junit.Before; 28 import org.junit.BeforeClass; 29 import org.junit.Test; 30 import org.openoffice.test.uno.UnoApp; 31 32 import testlib.uno.SCUtil; 33 34 import com.sun.star.lang.XComponent; 35 import com.sun.star.sheet.GeneralFunction; 36 import com.sun.star.sheet.SubTotalColumn; 37 import com.sun.star.sheet.XCellRangeData; 38 import com.sun.star.sheet.XSpreadsheet; 39 import com.sun.star.sheet.XSpreadsheetDocument; 40 import com.sun.star.sheet.XSubTotalCalculatable; 41 import com.sun.star.sheet.XSubTotalDescriptor; 42 import com.sun.star.table.XCellRange; 43 import com.sun.star.text.XTextDocument; 44 import com.sun.star.uno.UnoRuntime; 45 46 public class SubtotalsForGroup { 47 private static final UnoApp app = new UnoApp(); 48 49 UnoApp unoApp = new UnoApp(); 50 XSpreadsheetDocument scDocument = null; 51 XComponent scComponent = null; 52 53 @Before setUpDocument()54 public void setUpDocument() throws Exception { 55 unoApp.start(); 56 scComponent = unoApp.newDocument("scalc"); 57 } 58 59 @After tearDownDocument()60 public void tearDownDocument() { 61 unoApp.close(); 62 unoApp.closeDocument(scComponent); 63 64 } 65 66 @BeforeClass setUpConnection()67 public static void setUpConnection() throws Exception { 68 69 } 70 71 @AfterClass tearDownConnection()72 public static void tearDownConnection() throws InterruptedException, 73 Exception { 74 75 } 76 77 @Test testForSecondGroup()78 public void testForSecondGroup() throws Exception { 79 scComponent = unoApp.newDocument("scalc"); 80 scDocument = SCUtil.getSCDocument(scComponent); 81 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 82 XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface( 83 XCellRange.class, currentsheet); 84 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 85 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 86 XCellRangeData.class, sourceRange); 87 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 88 { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 89 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 90 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 91 { "CS", 30, 7, "C", "Tom" } }; 92 sourceData.setDataArray(Source); 93 94 // Create SubTotals 95 XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime 96 .queryInterface(XSubTotalCalculatable.class, sourceRange); 97 XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true); 98 SubTotalColumn[] aColumns = new SubTotalColumn[1]; 99 SubTotalColumn[] bColumns = new SubTotalColumn[1]; 100 // calculate sum of Second column 101 aColumns[0] = new SubTotalColumn(); 102 aColumns[0].Column = 1; 103 aColumns[0].Function = GeneralFunction.SUM; 104 // group by 4th column 105 xSubDesc.addNew(aColumns, 3); 106 107 // calculate sum of third column 108 bColumns[0] = new SubTotalColumn(); 109 bColumns[0].Column = 2; 110 bColumns[0].Function = GeneralFunction.SUM; 111 // group by second column 112 xSubDesc.addNew(bColumns, 1); 113 xSub.applySubTotals(xSubDesc, true); 114 115 // Verify the result on line 3 116 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2)); 117 118 assertEquals("=SUBTOTAL(9;$C$2:$C$2)", 119 SCUtil.getFormulaFromCell(currentsheet, 2, 2)); 120 121 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2), 122 0.000000001); 123 124 // Verify the result on line 5 125 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4)); 126 127 assertEquals("=SUBTOTAL(9;$C$4:$C$4)", 128 SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 129 130 assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4), 131 0.000000001); 132 133 // Verify the result on line 7 134 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 135 136 assertEquals("=SUBTOTAL(9;$C$6:$C$6)", 137 SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 138 139 assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6), 140 0.000000001); 141 142 // Verify the result on line 8 143 assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7)); 144 145 assertEquals("=SUBTOTAL(9;$B$2:$B$7)", 146 SCUtil.getFormulaFromCell(currentsheet, 1, 7)); 147 148 assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7), 149 0.000000001); 150 151 // Verify the result on line 10 152 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 153 154 assertEquals("=SUBTOTAL(9;$C$9:$C$9)", 155 SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 156 157 assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9), 158 0.000000001); 159 160 // Verify the result on line 12 161 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11)); 162 163 assertEquals("=SUBTOTAL(9;$C$11:$C$11)", 164 SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 165 166 assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11), 167 0.000000001); 168 169 // Verify the result on line 13 170 assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12)); 171 172 assertEquals("=SUBTOTAL(9;$B$9:$B$12)", 173 SCUtil.getFormulaFromCell(currentsheet, 1, 12)); 174 175 assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12), 176 0.000000001); 177 178 // Verify the result on line 15 179 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14)); 180 181 assertEquals("=SUBTOTAL(9;$C$14:$C$14)", 182 SCUtil.getFormulaFromCell(currentsheet, 2, 14)); 183 184 assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14), 185 0.000000001); 186 187 // Verify the result on line 17 188 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 189 190 assertEquals("=SUBTOTAL(9;$C$16:$C$16)", 191 SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 192 193 assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16), 194 0.000000001); 195 196 // Verify the result on line 18 197 assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 198 199 assertEquals("=SUBTOTAL(9;$B$14:$B$17)", 200 SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 201 202 assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17), 203 0.000000001); 204 205 // Verify the result on line 19 206 assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18)); 207 208 assertEquals("=SUBTOTAL(9;$B$2:$B$18)", 209 SCUtil.getFormulaFromCell(currentsheet, 1, 18)); 210 211 assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18), 212 0.000000001); 213 214 // Save the file and reload it 215 SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods"); 216 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 217 scDocument, "SubTotalsForGroup.ods"); 218 scDocument = scDocumentTemp; 219 currentsheet = SCUtil.getCurrentSheet(scDocument); 220 221 // verify it again 222 // Verify the result on line 3 223 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2)); 224 225 assertEquals("=SUBTOTAL(9;$C$2:$C$2)", 226 SCUtil.getFormulaFromCell(currentsheet, 2, 2)); 227 228 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2), 229 0.000000001); 230 231 // Verify the result on line 5 232 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4)); 233 234 assertEquals("=SUBTOTAL(9;$C$4:$C$4)", 235 SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 236 237 assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4), 238 0.000000001); 239 240 // Verify the result on line 7 241 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 242 243 assertEquals("=SUBTOTAL(9;$C$6:$C$6)", 244 SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 245 246 assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6), 247 0.000000001); 248 249 // Verify the result on line 8 250 assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7)); 251 252 assertEquals("=SUBTOTAL(9;$B$2:$B$7)", 253 SCUtil.getFormulaFromCell(currentsheet, 1, 7)); 254 255 assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7), 256 0.000000001); 257 258 // Verify the result on line 10 259 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 260 261 assertEquals("=SUBTOTAL(9;$C$9:$C$9)", 262 SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 263 264 assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9), 265 0.000000001); 266 267 // Verify the result on line 12 268 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11)); 269 270 assertEquals("=SUBTOTAL(9;$C$11:$C$11)", 271 SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 272 273 assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11), 274 0.000000001); 275 276 // Verify the result on line 13 277 assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12)); 278 279 assertEquals("=SUBTOTAL(9;$B$9:$B$12)", 280 SCUtil.getFormulaFromCell(currentsheet, 1, 12)); 281 282 assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12), 283 0.000000001); 284 285 // Verify the result on line 15 286 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14)); 287 288 assertEquals("=SUBTOTAL(9;$C$14:$C$14)", 289 SCUtil.getFormulaFromCell(currentsheet, 2, 14)); 290 291 assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14), 292 0.000000001); 293 294 // Verify the result on line 17 295 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 296 297 assertEquals("=SUBTOTAL(9;$C$16:$C$16)", 298 SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 299 300 assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16), 301 0.000000001); 302 303 // Verify the result on line 18 304 assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 305 306 assertEquals("=SUBTOTAL(9;$B$14:$B$17)", 307 SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 308 309 assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17), 310 0.000000001); 311 312 // Verify the result on line 19 313 assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18)); 314 315 assertEquals("=SUBTOTAL(9;$B$2:$B$18)", 316 SCUtil.getFormulaFromCell(currentsheet, 1, 18)); 317 318 assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18), 319 0.000000001); 320 321 } 322 323 @Test testForThirdGroup()324 public void testForThirdGroup() throws Exception { 325 scComponent = unoApp.newDocument("scalc"); 326 scDocument = SCUtil.getSCDocument(scComponent); 327 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 328 XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface( 329 XCellRange.class, currentsheet); 330 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 331 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 332 XCellRangeData.class, sourceRange); 333 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 334 { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 335 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 336 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 337 { "CS", 30, 7, "C", "Tom" } }; 338 sourceData.setDataArray(Source); 339 340 // Create SubTotals 341 XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime 342 .queryInterface(XSubTotalCalculatable.class, sourceRange); 343 XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true); 344 SubTotalColumn[] aColumns = new SubTotalColumn[1]; 345 SubTotalColumn[] bColumns = new SubTotalColumn[1]; 346 SubTotalColumn[] cColumns = new SubTotalColumn[1]; 347 // calculate sum of Second column 348 aColumns[0] = new SubTotalColumn(); 349 aColumns[0].Column = 1; 350 aColumns[0].Function = GeneralFunction.SUM; 351 // group by 4th column 352 xSubDesc.addNew(aColumns, 3); 353 354 // calculate sum of third column 355 bColumns[0] = new SubTotalColumn(); 356 bColumns[0].Column = 2; 357 bColumns[0].Function = GeneralFunction.SUM; 358 // group by second column 359 xSubDesc.addNew(bColumns, 1); 360 361 // calculate sum of third column 362 cColumns[0] = new SubTotalColumn(); 363 cColumns[0].Column = 4; 364 cColumns[0].Function = GeneralFunction.COUNT; 365 // group by first column 366 xSubDesc.addNew(cColumns, 0); 367 xSub.applySubTotals(xSubDesc, true); 368 369 // Verify the result on line 3 370 assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2)); 371 372 assertEquals("=SUBTOTAL(3;$E$2:$E$2)", 373 SCUtil.getFormulaFromCell(currentsheet, 4, 2)); 374 375 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2), 376 0.000000001); 377 378 // Verify the result on line 4 379 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3)); 380 381 assertEquals("=SUBTOTAL(9;$C$2:$C$3)", 382 SCUtil.getFormulaFromCell(currentsheet, 2, 3)); 383 384 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3), 385 0.000000001); 386 387 // Verify the result on line 6 388 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5)); 389 assertEquals("=SUBTOTAL(3;$E$5:$E$5)", 390 SCUtil.getFormulaFromCell(currentsheet, 4, 5)); 391 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5), 392 0.000000001); 393 394 // Verify the result on line7 395 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 396 assertEquals("=SUBTOTAL(9;$C$5:$C$6)", 397 SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 398 assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6), 399 0.000000001); 400 401 // Verify the result on line 9 402 assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8)); 403 assertEquals("=SUBTOTAL(3;$E$8:$E$8)", 404 SCUtil.getFormulaFromCell(currentsheet, 4, 8)); 405 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8), 406 0.000000001); 407 408 // Verify the result on line 10 409 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 410 assertEquals("=SUBTOTAL(9;$C$8:$C$9)", 411 SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 412 assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9), 413 0.000000001); 414 415 // Verify the result on line 11 416 assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10)); 417 assertEquals("=SUBTOTAL(9;$B$2:$B$10)", 418 SCUtil.getFormulaFromCell(currentsheet, 1, 10)); 419 assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10), 420 0.000000001); 421 422 // Verify the result on line 13 423 assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12)); 424 assertEquals("=SUBTOTAL(3;$E$12:$E$12)", 425 SCUtil.getFormulaFromCell(currentsheet, 4, 12)); 426 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12), 427 0.000000001); 428 429 // Verify the result on line 14 430 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13)); 431 assertEquals("=SUBTOTAL(9;$C$12:$C$13)", 432 SCUtil.getFormulaFromCell(currentsheet, 2, 13)); 433 assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13), 434 0.000000001); 435 436 // Verify the result on line 16 437 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15)); 438 assertEquals("=SUBTOTAL(3;$E$15:$E$15)", 439 SCUtil.getFormulaFromCell(currentsheet, 4, 15)); 440 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15), 441 0.000000001); 442 443 // Verify the result on line 17 444 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 445 446 assertEquals("=SUBTOTAL(9;$C$15:$C$16)", 447 SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 448 449 assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16), 450 0.000000001); 451 452 // Verify the result on line 18 453 assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 454 455 assertEquals("=SUBTOTAL(9;$B$12:$B$17)", 456 SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 457 458 assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17), 459 0.000000001); 460 461 // Verify the result on line 20 462 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19)); 463 assertEquals("=SUBTOTAL(3;$E$19:$E$19)", 464 SCUtil.getFormulaFromCell(currentsheet, 4, 19)); 465 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19), 466 0.000000001); 467 468 // Verify the result on line 21 469 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20)); 470 assertEquals("=SUBTOTAL(9;$C$19:$C$20)", 471 SCUtil.getFormulaFromCell(currentsheet, 2, 20)); 472 assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20), 473 0.000000001); 474 475 // Verify the result on line 23 476 assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22)); 477 assertEquals("=SUBTOTAL(3;$E$22:$E$22)", 478 SCUtil.getFormulaFromCell(currentsheet, 4, 22)); 479 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22), 480 0.000000001); 481 482 // Verify the result on line 24 483 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23)); 484 485 assertEquals("=SUBTOTAL(9;$C$22:$C$23)", 486 SCUtil.getFormulaFromCell(currentsheet, 2, 23)); 487 assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23), 488 0.000000001); 489 490 // Verify the result on line 25 491 assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24)); 492 493 assertEquals("=SUBTOTAL(9;$B$19:$B$24)", 494 SCUtil.getFormulaFromCell(currentsheet, 1, 24)); 495 assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24), 496 0.000000001); 497 498 // Verify the result on line 26 499 assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25)); 500 assertEquals("=SUBTOTAL(9;$B$2:$B$25)", 501 SCUtil.getFormulaFromCell(currentsheet, 1, 25)); 502 assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25), 503 0.000000001); 504 505 // Save the file and reload it 506 SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods"); 507 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 508 scDocument, "SubTotalsForGroup.ods"); 509 scDocument = scDocumentTemp; 510 currentsheet = SCUtil.getCurrentSheet(scDocument); 511 512 // verify it again 513 // Verify the result on line 3 514 assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2)); 515 516 assertEquals("=SUBTOTAL(3;$E$2:$E$2)", 517 SCUtil.getFormulaFromCell(currentsheet, 4, 2)); 518 519 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2), 520 0.000000001); 521 522 // Verify the result on line 4 523 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3)); 524 525 assertEquals("=SUBTOTAL(9;$C$2:$C$3)", 526 SCUtil.getFormulaFromCell(currentsheet, 2, 3)); 527 528 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3), 529 0.000000001); 530 531 // Verify the result on line 6 532 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5)); 533 assertEquals("=SUBTOTAL(3;$E$5:$E$5)", 534 SCUtil.getFormulaFromCell(currentsheet, 4, 5)); 535 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5), 536 0.000000001); 537 538 // Verify the result on line7 539 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 540 assertEquals("=SUBTOTAL(9;$C$5:$C$6)", 541 SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 542 assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6), 543 0.000000001); 544 545 // Verify the result on line 9 546 assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8)); 547 assertEquals("=SUBTOTAL(3;$E$8:$E$8)", 548 SCUtil.getFormulaFromCell(currentsheet, 4, 8)); 549 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8), 550 0.000000001); 551 552 // Verify the result on line 10 553 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 554 assertEquals("=SUBTOTAL(9;$C$8:$C$9)", 555 SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 556 assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9), 557 0.000000001); 558 559 // Verify the result on line 11 560 assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10)); 561 assertEquals("=SUBTOTAL(9;$B$2:$B$10)", 562 SCUtil.getFormulaFromCell(currentsheet, 1, 10)); 563 assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10), 564 0.000000001); 565 566 // Verify the result on line 13 567 assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12)); 568 assertEquals("=SUBTOTAL(3;$E$12:$E$12)", 569 SCUtil.getFormulaFromCell(currentsheet, 4, 12)); 570 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12), 571 0.000000001); 572 573 // Verify the result on line 14 574 assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13)); 575 assertEquals("=SUBTOTAL(9;$C$12:$C$13)", 576 SCUtil.getFormulaFromCell(currentsheet, 2, 13)); 577 assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13), 578 0.000000001); 579 580 // Verify the result on line 16 581 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15)); 582 assertEquals("=SUBTOTAL(3;$E$15:$E$15)", 583 SCUtil.getFormulaFromCell(currentsheet, 4, 15)); 584 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15), 585 0.000000001); 586 587 // Verify the result on line 17 588 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 589 590 assertEquals("=SUBTOTAL(9;$C$15:$C$16)", 591 SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 592 593 assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16), 594 0.000000001); 595 596 // Verify the result on line 18 597 assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 598 599 assertEquals("=SUBTOTAL(9;$B$12:$B$17)", 600 SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 601 602 assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17), 603 0.000000001); 604 605 // Verify the result on line 20 606 assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19)); 607 assertEquals("=SUBTOTAL(3;$E$19:$E$19)", 608 SCUtil.getFormulaFromCell(currentsheet, 4, 19)); 609 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19), 610 0.000000001); 611 612 // Verify the result on line 21 613 assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20)); 614 assertEquals("=SUBTOTAL(9;$C$19:$C$20)", 615 SCUtil.getFormulaFromCell(currentsheet, 2, 20)); 616 assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20), 617 0.000000001); 618 619 // Verify the result on line 23 620 assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22)); 621 assertEquals("=SUBTOTAL(3;$E$22:$E$22)", 622 SCUtil.getFormulaFromCell(currentsheet, 4, 22)); 623 assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22), 624 0.000000001); 625 626 // Verify the result on line 24 627 assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23)); 628 629 assertEquals("=SUBTOTAL(9;$C$22:$C$23)", 630 SCUtil.getFormulaFromCell(currentsheet, 2, 23)); 631 assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23), 632 0.000000001); 633 634 // Verify the result on line 25 635 assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24)); 636 637 assertEquals("=SUBTOTAL(9;$B$19:$B$24)", 638 SCUtil.getFormulaFromCell(currentsheet, 1, 24)); 639 assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24), 640 0.000000001); 641 642 // Verify the result on line 26 643 assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25)); 644 assertEquals("=SUBTOTAL(9;$B$2:$B$25)", 645 SCUtil.getFormulaFromCell(currentsheet, 1, 25)); 646 assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25), 647 0.000000001); 648 649 } 650 } 651