169efe8daSLiu Zhe /************************************************************** 269efe8daSLiu Zhe * 369efe8daSLiu Zhe * Licensed to the Apache Software Foundation (ASF) under one 469efe8daSLiu Zhe * or more contributor license agreements. See the NOTICE file 569efe8daSLiu Zhe * distributed with this work for additional information 669efe8daSLiu Zhe * regarding copyright ownership. The ASF licenses this file 769efe8daSLiu Zhe * to you under the Apache License, Version 2.0 (the 869efe8daSLiu Zhe * "License"); you may not use this file except in compliance 969efe8daSLiu Zhe * with the License. You may obtain a copy of the License at 1069efe8daSLiu Zhe * 1169efe8daSLiu Zhe * http://www.apache.org/licenses/LICENSE-2.0 1269efe8daSLiu Zhe * 1369efe8daSLiu Zhe * Unless required by applicable law or agreed to in writing, 1469efe8daSLiu Zhe * software distributed under the License is distributed on an 1569efe8daSLiu Zhe * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 1669efe8daSLiu Zhe * KIND, either express or implied. See the License for the 1769efe8daSLiu Zhe * specific language governing permissions and limitations 1869efe8daSLiu Zhe * under the License. 1969efe8daSLiu Zhe * 2069efe8daSLiu Zhe *************************************************************/ 21*eba4d44aSLiu Zhe package fvt.uno.sc.data; 2269efe8daSLiu Zhe 2369efe8daSLiu Zhe import static org.junit.Assert.*; 2469efe8daSLiu Zhe 2569efe8daSLiu Zhe import org.junit.After; 2669efe8daSLiu Zhe import org.junit.AfterClass; 2769efe8daSLiu Zhe import org.junit.Before; 2869efe8daSLiu Zhe import org.junit.BeforeClass; 2969efe8daSLiu Zhe import org.junit.Test; 3069efe8daSLiu Zhe import org.openoffice.test.uno.UnoApp; 3169efe8daSLiu Zhe 3269efe8daSLiu Zhe import testlib.uno.SCUtil; 3369efe8daSLiu Zhe 3469efe8daSLiu Zhe import com.sun.star.lang.XComponent; 3569efe8daSLiu Zhe import com.sun.star.sheet.GeneralFunction; 3669efe8daSLiu Zhe import com.sun.star.sheet.SubTotalColumn; 3769efe8daSLiu Zhe import com.sun.star.sheet.XCellRangeData; 3869efe8daSLiu Zhe import com.sun.star.sheet.XSpreadsheet; 3969efe8daSLiu Zhe import com.sun.star.sheet.XSpreadsheetDocument; 4069efe8daSLiu Zhe import com.sun.star.sheet.XSubTotalCalculatable; 4169efe8daSLiu Zhe import com.sun.star.sheet.XSubTotalDescriptor; 4269efe8daSLiu Zhe import com.sun.star.table.XCellRange; 4369efe8daSLiu Zhe import com.sun.star.text.XTextDocument; 4469efe8daSLiu Zhe import com.sun.star.uno.UnoRuntime; 4569efe8daSLiu Zhe 4669efe8daSLiu Zhe public class SubtotalsForGroup { 4769efe8daSLiu Zhe private static final UnoApp app = new UnoApp(); 4869efe8daSLiu Zhe 4969efe8daSLiu Zhe UnoApp unoApp = new UnoApp(); 5069efe8daSLiu Zhe XSpreadsheetDocument scDocument = null; 5169efe8daSLiu Zhe XComponent scComponent = null; 5269efe8daSLiu Zhe 5369efe8daSLiu Zhe @Before setUpDocument()5469efe8daSLiu Zhe public void setUpDocument() throws Exception { 5569efe8daSLiu Zhe unoApp.start(); 5669efe8daSLiu Zhe scComponent = unoApp.newDocument("scalc"); 5769efe8daSLiu Zhe } 5869efe8daSLiu Zhe 5969efe8daSLiu Zhe @After tearDownDocument()6069efe8daSLiu Zhe public void tearDownDocument() { 6169efe8daSLiu Zhe unoApp.close(); 6269efe8daSLiu Zhe unoApp.closeDocument(scComponent); 6369efe8daSLiu Zhe 6469efe8daSLiu Zhe } 6569efe8daSLiu Zhe 6669efe8daSLiu Zhe @BeforeClass setUpConnection()6769efe8daSLiu Zhe public static void setUpConnection() throws Exception { 6869efe8daSLiu Zhe 6969efe8daSLiu Zhe } 7069efe8daSLiu Zhe 7169efe8daSLiu Zhe @AfterClass tearDownConnection()7269efe8daSLiu Zhe public static void tearDownConnection() throws InterruptedException, 7369efe8daSLiu Zhe Exception { 7469efe8daSLiu Zhe 7569efe8daSLiu Zhe } 7669efe8daSLiu Zhe 7769efe8daSLiu Zhe @Test testForSecondGroup()7869efe8daSLiu Zhe public void testForSecondGroup() throws Exception { 7969efe8daSLiu Zhe scComponent = unoApp.newDocument("scalc"); 8069efe8daSLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 8169efe8daSLiu Zhe XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 8269efe8daSLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface( 8369efe8daSLiu Zhe XCellRange.class, currentsheet); 8469efe8daSLiu Zhe XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 8569efe8daSLiu Zhe XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 8669efe8daSLiu Zhe XCellRangeData.class, sourceRange); 8769efe8daSLiu Zhe Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 8869efe8daSLiu Zhe { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 8969efe8daSLiu Zhe { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 9069efe8daSLiu Zhe { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 9169efe8daSLiu Zhe { "CS", 30, 7, "C", "Tom" } }; 9269efe8daSLiu Zhe sourceData.setDataArray(Source); 9369efe8daSLiu Zhe 9469efe8daSLiu Zhe // Create SubTotals 9569efe8daSLiu Zhe XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime 9669efe8daSLiu Zhe .queryInterface(XSubTotalCalculatable.class, sourceRange); 9769efe8daSLiu Zhe XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true); 9869efe8daSLiu Zhe SubTotalColumn[] aColumns = new SubTotalColumn[1]; 9969efe8daSLiu Zhe SubTotalColumn[] bColumns = new SubTotalColumn[1]; 10069efe8daSLiu Zhe // calculate sum of Second column 10169efe8daSLiu Zhe aColumns[0] = new SubTotalColumn(); 10269efe8daSLiu Zhe aColumns[0].Column = 1; 10369efe8daSLiu Zhe aColumns[0].Function = GeneralFunction.SUM; 10469efe8daSLiu Zhe // group by 4th column 10569efe8daSLiu Zhe xSubDesc.addNew(aColumns, 3); 10669efe8daSLiu Zhe 10769efe8daSLiu Zhe // calculate sum of third column 10869efe8daSLiu Zhe bColumns[0] = new SubTotalColumn(); 10969efe8daSLiu Zhe bColumns[0].Column = 2; 11069efe8daSLiu Zhe bColumns[0].Function = GeneralFunction.SUM; 11169efe8daSLiu Zhe // group by second column 11269efe8daSLiu Zhe xSubDesc.addNew(bColumns, 1); 11369efe8daSLiu Zhe xSub.applySubTotals(xSubDesc, true); 11469efe8daSLiu Zhe 11569efe8daSLiu Zhe // Verify the result on line 3 11669efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2)); 11769efe8daSLiu Zhe 11869efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$2:$C$2)", 11969efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 2)); 12069efe8daSLiu Zhe 12169efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2), 12269efe8daSLiu Zhe 0.000000001); 12369efe8daSLiu Zhe 12469efe8daSLiu Zhe // Verify the result on line 5 12569efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4)); 12669efe8daSLiu Zhe 12769efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$4:$C$4)", 12869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 12969efe8daSLiu Zhe 13069efe8daSLiu Zhe assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4), 13169efe8daSLiu Zhe 0.000000001); 13269efe8daSLiu Zhe 13369efe8daSLiu Zhe // Verify the result on line 7 13469efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 13569efe8daSLiu Zhe 13669efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$6:$C$6)", 13769efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 13869efe8daSLiu Zhe 13969efe8daSLiu Zhe assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6), 14069efe8daSLiu Zhe 0.000000001); 14169efe8daSLiu Zhe 14269efe8daSLiu Zhe // Verify the result on line 8 14369efe8daSLiu Zhe assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7)); 14469efe8daSLiu Zhe 14569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$7)", 14669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 7)); 14769efe8daSLiu Zhe 14869efe8daSLiu Zhe assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7), 14969efe8daSLiu Zhe 0.000000001); 15069efe8daSLiu Zhe 15169efe8daSLiu Zhe // Verify the result on line 10 15269efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 15369efe8daSLiu Zhe 15469efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$9:$C$9)", 15569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 15669efe8daSLiu Zhe 15769efe8daSLiu Zhe assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9), 15869efe8daSLiu Zhe 0.000000001); 15969efe8daSLiu Zhe 16069efe8daSLiu Zhe // Verify the result on line 12 16169efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11)); 16269efe8daSLiu Zhe 16369efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$11:$C$11)", 16469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 16569efe8daSLiu Zhe 16669efe8daSLiu Zhe assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11), 16769efe8daSLiu Zhe 0.000000001); 16869efe8daSLiu Zhe 16969efe8daSLiu Zhe // Verify the result on line 13 17069efe8daSLiu Zhe assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12)); 17169efe8daSLiu Zhe 17269efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$9:$B$12)", 17369efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 12)); 17469efe8daSLiu Zhe 17569efe8daSLiu Zhe assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12), 17669efe8daSLiu Zhe 0.000000001); 17769efe8daSLiu Zhe 17869efe8daSLiu Zhe // Verify the result on line 15 17969efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14)); 18069efe8daSLiu Zhe 18169efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$14:$C$14)", 18269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 14)); 18369efe8daSLiu Zhe 18469efe8daSLiu Zhe assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14), 18569efe8daSLiu Zhe 0.000000001); 18669efe8daSLiu Zhe 18769efe8daSLiu Zhe // Verify the result on line 17 18869efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 18969efe8daSLiu Zhe 19069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$16:$C$16)", 19169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 19269efe8daSLiu Zhe 19369efe8daSLiu Zhe assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16), 19469efe8daSLiu Zhe 0.000000001); 19569efe8daSLiu Zhe 19669efe8daSLiu Zhe // Verify the result on line 18 19769efe8daSLiu Zhe assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 19869efe8daSLiu Zhe 19969efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$14:$B$17)", 20069efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 20169efe8daSLiu Zhe 20269efe8daSLiu Zhe assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17), 20369efe8daSLiu Zhe 0.000000001); 20469efe8daSLiu Zhe 20569efe8daSLiu Zhe // Verify the result on line 19 20669efe8daSLiu Zhe assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18)); 20769efe8daSLiu Zhe 20869efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$18)", 20969efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 18)); 21069efe8daSLiu Zhe 21169efe8daSLiu Zhe assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18), 21269efe8daSLiu Zhe 0.000000001); 21369efe8daSLiu Zhe 21469efe8daSLiu Zhe // Save the file and reload it 21569efe8daSLiu Zhe SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods"); 21669efe8daSLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 21769efe8daSLiu Zhe scDocument, "SubTotalsForGroup.ods"); 21869efe8daSLiu Zhe scDocument = scDocumentTemp; 21969efe8daSLiu Zhe currentsheet = SCUtil.getCurrentSheet(scDocument); 22069efe8daSLiu Zhe 22169efe8daSLiu Zhe // verify it again 22269efe8daSLiu Zhe // Verify the result on line 3 22369efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2)); 22469efe8daSLiu Zhe 22569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$2:$C$2)", 22669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 2)); 22769efe8daSLiu Zhe 22869efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2), 22969efe8daSLiu Zhe 0.000000001); 23069efe8daSLiu Zhe 23169efe8daSLiu Zhe // Verify the result on line 5 23269efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4)); 23369efe8daSLiu Zhe 23469efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$4:$C$4)", 23569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 23669efe8daSLiu Zhe 23769efe8daSLiu Zhe assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4), 23869efe8daSLiu Zhe 0.000000001); 23969efe8daSLiu Zhe 24069efe8daSLiu Zhe // Verify the result on line 7 24169efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 24269efe8daSLiu Zhe 24369efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$6:$C$6)", 24469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 24569efe8daSLiu Zhe 24669efe8daSLiu Zhe assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6), 24769efe8daSLiu Zhe 0.000000001); 24869efe8daSLiu Zhe 24969efe8daSLiu Zhe // Verify the result on line 8 25069efe8daSLiu Zhe assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7)); 25169efe8daSLiu Zhe 25269efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$7)", 25369efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 7)); 25469efe8daSLiu Zhe 25569efe8daSLiu Zhe assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7), 25669efe8daSLiu Zhe 0.000000001); 25769efe8daSLiu Zhe 25869efe8daSLiu Zhe // Verify the result on line 10 25969efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 26069efe8daSLiu Zhe 26169efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$9:$C$9)", 26269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 26369efe8daSLiu Zhe 26469efe8daSLiu Zhe assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9), 26569efe8daSLiu Zhe 0.000000001); 26669efe8daSLiu Zhe 26769efe8daSLiu Zhe // Verify the result on line 12 26869efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11)); 26969efe8daSLiu Zhe 27069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$11:$C$11)", 27169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 27269efe8daSLiu Zhe 27369efe8daSLiu Zhe assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11), 27469efe8daSLiu Zhe 0.000000001); 27569efe8daSLiu Zhe 27669efe8daSLiu Zhe // Verify the result on line 13 27769efe8daSLiu Zhe assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12)); 27869efe8daSLiu Zhe 27969efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$9:$B$12)", 28069efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 12)); 28169efe8daSLiu Zhe 28269efe8daSLiu Zhe assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12), 28369efe8daSLiu Zhe 0.000000001); 28469efe8daSLiu Zhe 28569efe8daSLiu Zhe // Verify the result on line 15 28669efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14)); 28769efe8daSLiu Zhe 28869efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$14:$C$14)", 28969efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 14)); 29069efe8daSLiu Zhe 29169efe8daSLiu Zhe assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14), 29269efe8daSLiu Zhe 0.000000001); 29369efe8daSLiu Zhe 29469efe8daSLiu Zhe // Verify the result on line 17 29569efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 29669efe8daSLiu Zhe 29769efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$16:$C$16)", 29869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 29969efe8daSLiu Zhe 30069efe8daSLiu Zhe assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16), 30169efe8daSLiu Zhe 0.000000001); 30269efe8daSLiu Zhe 30369efe8daSLiu Zhe // Verify the result on line 18 30469efe8daSLiu Zhe assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 30569efe8daSLiu Zhe 30669efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$14:$B$17)", 30769efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 30869efe8daSLiu Zhe 30969efe8daSLiu Zhe assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17), 31069efe8daSLiu Zhe 0.000000001); 31169efe8daSLiu Zhe 31269efe8daSLiu Zhe // Verify the result on line 19 31369efe8daSLiu Zhe assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18)); 31469efe8daSLiu Zhe 31569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$18)", 31669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 18)); 31769efe8daSLiu Zhe 31869efe8daSLiu Zhe assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18), 31969efe8daSLiu Zhe 0.000000001); 32069efe8daSLiu Zhe 32169efe8daSLiu Zhe } 32269efe8daSLiu Zhe 32369efe8daSLiu Zhe @Test testForThirdGroup()32469efe8daSLiu Zhe public void testForThirdGroup() throws Exception { 32569efe8daSLiu Zhe scComponent = unoApp.newDocument("scalc"); 32669efe8daSLiu Zhe scDocument = SCUtil.getSCDocument(scComponent); 32769efe8daSLiu Zhe XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 32869efe8daSLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface( 32969efe8daSLiu Zhe XCellRange.class, currentsheet); 33069efe8daSLiu Zhe XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 33169efe8daSLiu Zhe XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 33269efe8daSLiu Zhe XCellRangeData.class, sourceRange); 33369efe8daSLiu Zhe Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 33469efe8daSLiu Zhe { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 33569efe8daSLiu Zhe { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 33669efe8daSLiu Zhe { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 33769efe8daSLiu Zhe { "CS", 30, 7, "C", "Tom" } }; 33869efe8daSLiu Zhe sourceData.setDataArray(Source); 33969efe8daSLiu Zhe 34069efe8daSLiu Zhe // Create SubTotals 34169efe8daSLiu Zhe XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime 34269efe8daSLiu Zhe .queryInterface(XSubTotalCalculatable.class, sourceRange); 34369efe8daSLiu Zhe XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true); 34469efe8daSLiu Zhe SubTotalColumn[] aColumns = new SubTotalColumn[1]; 34569efe8daSLiu Zhe SubTotalColumn[] bColumns = new SubTotalColumn[1]; 34669efe8daSLiu Zhe SubTotalColumn[] cColumns = new SubTotalColumn[1]; 34769efe8daSLiu Zhe // calculate sum of Second column 34869efe8daSLiu Zhe aColumns[0] = new SubTotalColumn(); 34969efe8daSLiu Zhe aColumns[0].Column = 1; 35069efe8daSLiu Zhe aColumns[0].Function = GeneralFunction.SUM; 35169efe8daSLiu Zhe // group by 4th column 35269efe8daSLiu Zhe xSubDesc.addNew(aColumns, 3); 35369efe8daSLiu Zhe 35469efe8daSLiu Zhe // calculate sum of third column 35569efe8daSLiu Zhe bColumns[0] = new SubTotalColumn(); 35669efe8daSLiu Zhe bColumns[0].Column = 2; 35769efe8daSLiu Zhe bColumns[0].Function = GeneralFunction.SUM; 35869efe8daSLiu Zhe // group by second column 35969efe8daSLiu Zhe xSubDesc.addNew(bColumns, 1); 36069efe8daSLiu Zhe 36169efe8daSLiu Zhe // calculate sum of third column 36269efe8daSLiu Zhe cColumns[0] = new SubTotalColumn(); 36369efe8daSLiu Zhe cColumns[0].Column = 4; 36469efe8daSLiu Zhe cColumns[0].Function = GeneralFunction.COUNT; 36569efe8daSLiu Zhe // group by first column 36669efe8daSLiu Zhe xSubDesc.addNew(cColumns, 0); 36769efe8daSLiu Zhe xSub.applySubTotals(xSubDesc, true); 36869efe8daSLiu Zhe 36969efe8daSLiu Zhe // Verify the result on line 3 37069efe8daSLiu Zhe assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2)); 37169efe8daSLiu Zhe 37269efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$2:$E$2)", 37369efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 2)); 37469efe8daSLiu Zhe 37569efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2), 37669efe8daSLiu Zhe 0.000000001); 37769efe8daSLiu Zhe 37869efe8daSLiu Zhe // Verify the result on line 4 37969efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3)); 38069efe8daSLiu Zhe 38169efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$2:$C$3)", 38269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 3)); 38369efe8daSLiu Zhe 38469efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3), 38569efe8daSLiu Zhe 0.000000001); 38669efe8daSLiu Zhe 38769efe8daSLiu Zhe // Verify the result on line 6 38869efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5)); 38969efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$5:$E$5)", 39069efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 5)); 39169efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5), 39269efe8daSLiu Zhe 0.000000001); 39369efe8daSLiu Zhe 39469efe8daSLiu Zhe // Verify the result on line7 39569efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 39669efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$5:$C$6)", 39769efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 39869efe8daSLiu Zhe assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6), 39969efe8daSLiu Zhe 0.000000001); 40069efe8daSLiu Zhe 40169efe8daSLiu Zhe // Verify the result on line 9 40269efe8daSLiu Zhe assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8)); 40369efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$8:$E$8)", 40469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 8)); 40569efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8), 40669efe8daSLiu Zhe 0.000000001); 40769efe8daSLiu Zhe 40869efe8daSLiu Zhe // Verify the result on line 10 40969efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 41069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$8:$C$9)", 41169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 41269efe8daSLiu Zhe assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9), 41369efe8daSLiu Zhe 0.000000001); 41469efe8daSLiu Zhe 41569efe8daSLiu Zhe // Verify the result on line 11 41669efe8daSLiu Zhe assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10)); 41769efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$10)", 41869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 10)); 41969efe8daSLiu Zhe assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10), 42069efe8daSLiu Zhe 0.000000001); 42169efe8daSLiu Zhe 42269efe8daSLiu Zhe // Verify the result on line 13 42369efe8daSLiu Zhe assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12)); 42469efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$12:$E$12)", 42569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 12)); 42669efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12), 42769efe8daSLiu Zhe 0.000000001); 42869efe8daSLiu Zhe 42969efe8daSLiu Zhe // Verify the result on line 14 43069efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13)); 43169efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$12:$C$13)", 43269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 13)); 43369efe8daSLiu Zhe assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13), 43469efe8daSLiu Zhe 0.000000001); 43569efe8daSLiu Zhe 43669efe8daSLiu Zhe // Verify the result on line 16 43769efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15)); 43869efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$15:$E$15)", 43969efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 15)); 44069efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15), 44169efe8daSLiu Zhe 0.000000001); 44269efe8daSLiu Zhe 44369efe8daSLiu Zhe // Verify the result on line 17 44469efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 44569efe8daSLiu Zhe 44669efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$15:$C$16)", 44769efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 44869efe8daSLiu Zhe 44969efe8daSLiu Zhe assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16), 45069efe8daSLiu Zhe 0.000000001); 45169efe8daSLiu Zhe 45269efe8daSLiu Zhe // Verify the result on line 18 45369efe8daSLiu Zhe assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 45469efe8daSLiu Zhe 45569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$12:$B$17)", 45669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 45769efe8daSLiu Zhe 45869efe8daSLiu Zhe assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17), 45969efe8daSLiu Zhe 0.000000001); 46069efe8daSLiu Zhe 46169efe8daSLiu Zhe // Verify the result on line 20 46269efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19)); 46369efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$19:$E$19)", 46469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 19)); 46569efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19), 46669efe8daSLiu Zhe 0.000000001); 46769efe8daSLiu Zhe 46869efe8daSLiu Zhe // Verify the result on line 21 46969efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20)); 47069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$19:$C$20)", 47169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 20)); 47269efe8daSLiu Zhe assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20), 47369efe8daSLiu Zhe 0.000000001); 47469efe8daSLiu Zhe 47569efe8daSLiu Zhe // Verify the result on line 23 47669efe8daSLiu Zhe assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22)); 47769efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$22:$E$22)", 47869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 22)); 47969efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22), 48069efe8daSLiu Zhe 0.000000001); 48169efe8daSLiu Zhe 48269efe8daSLiu Zhe // Verify the result on line 24 48369efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23)); 48469efe8daSLiu Zhe 48569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$22:$C$23)", 48669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 23)); 48769efe8daSLiu Zhe assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23), 48869efe8daSLiu Zhe 0.000000001); 48969efe8daSLiu Zhe 49069efe8daSLiu Zhe // Verify the result on line 25 49169efe8daSLiu Zhe assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24)); 49269efe8daSLiu Zhe 49369efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$19:$B$24)", 49469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 24)); 49569efe8daSLiu Zhe assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24), 49669efe8daSLiu Zhe 0.000000001); 49769efe8daSLiu Zhe 49869efe8daSLiu Zhe // Verify the result on line 26 49969efe8daSLiu Zhe assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25)); 50069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$25)", 50169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 25)); 50269efe8daSLiu Zhe assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25), 50369efe8daSLiu Zhe 0.000000001); 50469efe8daSLiu Zhe 50569efe8daSLiu Zhe // Save the file and reload it 50669efe8daSLiu Zhe SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods"); 50769efe8daSLiu Zhe XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 50869efe8daSLiu Zhe scDocument, "SubTotalsForGroup.ods"); 50969efe8daSLiu Zhe scDocument = scDocumentTemp; 51069efe8daSLiu Zhe currentsheet = SCUtil.getCurrentSheet(scDocument); 51169efe8daSLiu Zhe 51269efe8daSLiu Zhe // verify it again 51369efe8daSLiu Zhe // Verify the result on line 3 51469efe8daSLiu Zhe assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2)); 51569efe8daSLiu Zhe 51669efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$2:$E$2)", 51769efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 2)); 51869efe8daSLiu Zhe 51969efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2), 52069efe8daSLiu Zhe 0.000000001); 52169efe8daSLiu Zhe 52269efe8daSLiu Zhe // Verify the result on line 4 52369efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3)); 52469efe8daSLiu Zhe 52569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$2:$C$3)", 52669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 3)); 52769efe8daSLiu Zhe 52869efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3), 52969efe8daSLiu Zhe 0.000000001); 53069efe8daSLiu Zhe 53169efe8daSLiu Zhe // Verify the result on line 6 53269efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5)); 53369efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$5:$E$5)", 53469efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 5)); 53569efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5), 53669efe8daSLiu Zhe 0.000000001); 53769efe8daSLiu Zhe 53869efe8daSLiu Zhe // Verify the result on line7 53969efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6)); 54069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$5:$C$6)", 54169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 6)); 54269efe8daSLiu Zhe assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6), 54369efe8daSLiu Zhe 0.000000001); 54469efe8daSLiu Zhe 54569efe8daSLiu Zhe // Verify the result on line 9 54669efe8daSLiu Zhe assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8)); 54769efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$8:$E$8)", 54869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 8)); 54969efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8), 55069efe8daSLiu Zhe 0.000000001); 55169efe8daSLiu Zhe 55269efe8daSLiu Zhe // Verify the result on line 10 55369efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9)); 55469efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$8:$C$9)", 55569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 9)); 55669efe8daSLiu Zhe assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9), 55769efe8daSLiu Zhe 0.000000001); 55869efe8daSLiu Zhe 55969efe8daSLiu Zhe // Verify the result on line 11 56069efe8daSLiu Zhe assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10)); 56169efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$10)", 56269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 10)); 56369efe8daSLiu Zhe assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10), 56469efe8daSLiu Zhe 0.000000001); 56569efe8daSLiu Zhe 56669efe8daSLiu Zhe // Verify the result on line 13 56769efe8daSLiu Zhe assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12)); 56869efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$12:$E$12)", 56969efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 12)); 57069efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12), 57169efe8daSLiu Zhe 0.000000001); 57269efe8daSLiu Zhe 57369efe8daSLiu Zhe // Verify the result on line 14 57469efe8daSLiu Zhe assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13)); 57569efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$12:$C$13)", 57669efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 13)); 57769efe8daSLiu Zhe assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13), 57869efe8daSLiu Zhe 0.000000001); 57969efe8daSLiu Zhe 58069efe8daSLiu Zhe // Verify the result on line 16 58169efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15)); 58269efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$15:$E$15)", 58369efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 15)); 58469efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15), 58569efe8daSLiu Zhe 0.000000001); 58669efe8daSLiu Zhe 58769efe8daSLiu Zhe // Verify the result on line 17 58869efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16)); 58969efe8daSLiu Zhe 59069efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$15:$C$16)", 59169efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 16)); 59269efe8daSLiu Zhe 59369efe8daSLiu Zhe assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16), 59469efe8daSLiu Zhe 0.000000001); 59569efe8daSLiu Zhe 59669efe8daSLiu Zhe // Verify the result on line 18 59769efe8daSLiu Zhe assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17)); 59869efe8daSLiu Zhe 59969efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$12:$B$17)", 60069efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 17)); 60169efe8daSLiu Zhe 60269efe8daSLiu Zhe assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17), 60369efe8daSLiu Zhe 0.000000001); 60469efe8daSLiu Zhe 60569efe8daSLiu Zhe // Verify the result on line 20 60669efe8daSLiu Zhe assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19)); 60769efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$19:$E$19)", 60869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 19)); 60969efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19), 61069efe8daSLiu Zhe 0.000000001); 61169efe8daSLiu Zhe 61269efe8daSLiu Zhe // Verify the result on line 21 61369efe8daSLiu Zhe assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20)); 61469efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$19:$C$20)", 61569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 20)); 61669efe8daSLiu Zhe assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20), 61769efe8daSLiu Zhe 0.000000001); 61869efe8daSLiu Zhe 61969efe8daSLiu Zhe // Verify the result on line 23 62069efe8daSLiu Zhe assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22)); 62169efe8daSLiu Zhe assertEquals("=SUBTOTAL(3;$E$22:$E$22)", 62269efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 4, 22)); 62369efe8daSLiu Zhe assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22), 62469efe8daSLiu Zhe 0.000000001); 62569efe8daSLiu Zhe 62669efe8daSLiu Zhe // Verify the result on line 24 62769efe8daSLiu Zhe assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23)); 62869efe8daSLiu Zhe 62969efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$C$22:$C$23)", 63069efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 2, 23)); 63169efe8daSLiu Zhe assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23), 63269efe8daSLiu Zhe 0.000000001); 63369efe8daSLiu Zhe 63469efe8daSLiu Zhe // Verify the result on line 25 63569efe8daSLiu Zhe assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24)); 63669efe8daSLiu Zhe 63769efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$19:$B$24)", 63869efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 24)); 63969efe8daSLiu Zhe assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24), 64069efe8daSLiu Zhe 0.000000001); 64169efe8daSLiu Zhe 64269efe8daSLiu Zhe // Verify the result on line 26 64369efe8daSLiu Zhe assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25)); 64469efe8daSLiu Zhe assertEquals("=SUBTOTAL(9;$B$2:$B$25)", 64569efe8daSLiu Zhe SCUtil.getFormulaFromCell(currentsheet, 1, 25)); 64669efe8daSLiu Zhe assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25), 64769efe8daSLiu Zhe 0.000000001); 64869efe8daSLiu Zhe 64969efe8daSLiu Zhe } 65069efe8daSLiu Zhe } 651