xref: /AOO41X/test/testuno/source/fvt/uno/sc/data/SubtotalsForGroup.java (revision eba4d44a33e5be0b2528d5a9a6f0dcbf65adaa0d) 
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