You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@openoffice.apache.org by li...@apache.org on 2012/09/22 13:32:19 UTC
svn commit: r1388775 - in
/incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data:
SubTotalsFunction.java SubtotalsForGroup.java
Author: liuzhe
Date: Sat Sep 22 11:32:19 2012
New Revision: 1388775
URL: http://svn.apache.org/viewvc?rev=1388775&view=rev
Log:
#120966 - [testUNO patch]Subtotal function, First Group.Second Group and Third Group
Patch By: Yang Wei <po...@hotmail.com>
Review By: Liu Zhe <al...@gmail.com>
Added:
incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java (with props)
incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java (with props)
Added: incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java?rev=1388775&view=auto
==============================================================================
--- incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java (added)
+++ incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java Sat Sep 22 11:32:19 2012
@@ -0,0 +1,248 @@
+/**************************************************************
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ *************************************************************/
+package testcase.uno.sc.data;
+
+import static org.junit.Assert.*;
+
+import java.util.Arrays;
+import java.util.Collection;
+
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.junit.runner.RunWith;
+import org.junit.runners.Parameterized;
+import org.junit.runners.Parameterized.Parameters;
+import org.openoffice.test.uno.UnoApp;
+import testlib.uno.SCUtil;
+import com.sun.star.lang.XComponent;
+import com.sun.star.sheet.GeneralFunction;
+import com.sun.star.sheet.SubTotalColumn;
+import com.sun.star.sheet.XCellRangeData;
+import com.sun.star.sheet.XSpreadsheet;
+import com.sun.star.sheet.XSpreadsheetDocument;
+import com.sun.star.sheet.XSubTotalCalculatable;
+import com.sun.star.sheet.XSubTotalDescriptor;
+import com.sun.star.table.XCellRange;
+import com.sun.star.uno.Enum;
+import com.sun.star.uno.UnoRuntime;
+
+@RunWith(value = Parameterized.class)
+public class SubTotalsFunction {
+ private static final UnoApp app = new UnoApp();
+
+ UnoApp unoApp = new UnoApp();
+ XSpreadsheetDocument scDocument = null;
+ XComponent scComponent = null;
+
+ private GeneralFunction operator;
+
+ private String operatorString;
+
+ private int operatorvalue;
+
+ private double bssubtotalresult;
+
+ private double cssubtotalresult;
+
+ private double mssubtotalresult;
+
+ private double grandtotal;
+
+ @Parameters
+ public static Collection<Object[]> data() throws Exception {
+ // Remove GeneralFunction.Auto,GeneralFunction.NONE
+ return Arrays.asList(new Object[][] {
+ { GeneralFunction.SUM, "Sum", 9, 12, 12, 4, 28 },
+ { GeneralFunction.AVERAGE, "Average", 1, 4, 6, 2, 4 },
+ { GeneralFunction.COUNT, "Count", 3, 3, 2, 2, 7 },
+ { GeneralFunction.COUNTNUMS, "Count", 2, 3, 2, 2, 7 },
+ { GeneralFunction.MAX, "Max", 4, 6, 7, 3, 7 },
+ { GeneralFunction.MIN, "Min", 5, 2, 5, 1, 1 },
+ { GeneralFunction.VAR, "Var", 10, 4, 2, 2, 4.666666667 },
+ { GeneralFunction.PRODUCT, "Product", 6, 48, 35, 3, 5040 },
+ { GeneralFunction.STDEVP, "StDev", 8, 1.6329931619, 1, 1, 2 },
+ { GeneralFunction.STDEV, "StDev", 7, 2, 1.4142135624,
+ 1.4142135624, 2.1602468995 },
+ { GeneralFunction.VARP, "Var", 11, 2.6666666667, 1, 1, 4 }, });
+ }
+
+ @Before
+ public void setUpDocument() throws Exception {
+ unoApp.start();
+ }
+
+ @After
+ public void tearDownDocument() {
+ unoApp.close();
+ unoApp.closeDocument(scComponent);
+
+ }
+
+ @BeforeClass
+ public static void setUpConnection() throws Exception {
+
+ }
+
+ @AfterClass
+ public static void tearDownConnection() throws InterruptedException,
+ Exception {
+
+ }
+
+ public SubTotalsFunction(Enum operator, String operatorString,
+ int operatorvalue, double bssubtotalresult,
+ double cssubtotalresult, double mssubtotalresult, double grandtotal) {
+ this.operator = (GeneralFunction) operator;
+ this.operatorString = operatorString;
+ this.operatorvalue = operatorvalue;
+ this.bssubtotalresult = bssubtotalresult;
+ this.cssubtotalresult = cssubtotalresult;
+ this.mssubtotalresult = mssubtotalresult;
+ this.grandtotal = grandtotal;
+ }
+
+ @Test
+ public void test() throws Exception {
+ // New document and input data in document
+ scComponent = unoApp.newDocument("scalc");
+ scDocument = SCUtil.getSCDocument(scComponent);
+ XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument);
+ XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(
+ XCellRange.class, currentsheet);
+ XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8");
+ XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface(
+ XCellRangeData.class, sourceRange);
+ Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" },
+ { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" },
+ { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" },
+ { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" },
+ { "CS", 30, 7, "C", "Tom" } };
+ sourceData.setDataArray(Source);
+
+ // Create SubTotals
+ XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime
+ .queryInterface(XSubTotalCalculatable.class, sourceRange);
+ XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true);
+ SubTotalColumn[] aColumns = new SubTotalColumn[1];
+ // calculate sum of third column
+ aColumns[0] = new SubTotalColumn();
+ aColumns[0].Column = 2;
+ aColumns[0].Function = operator;
+ // group by first column
+ xSubDesc.addNew(aColumns, 0);
+ xSub.applySubTotals(xSubDesc, true);
+
+ // Verify BS SubTotals result
+ String BSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$4)";
+ String BSsubtotalsString = "BS " + operatorString;
+
+ assertEquals(bssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 4), 0.000000001);
+ assertEquals(BSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 4));
+ assertEquals(BSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 4));
+
+ // Verify CS SubTotals result
+ String CSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$6:$C$7)";
+ String CSsubtotalsString = "CS " + operatorString;
+
+ assertEquals(cssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 7), 0.000000001);
+ assertEquals(CSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 7));
+ assertEquals(CSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 7));
+
+ // Verify MS SubTotals result
+ String MSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$9:$C$10)";
+ String MSsubtotalsString = "MS " + operatorString;
+
+ assertEquals(mssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 10), 0.000000001);
+ assertEquals(MSsubtotals,
+ SCUtil.getFormulaFromCell(currentsheet, 2, 10));
+ assertEquals(MSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 10));
+
+ // Verify GrandTotal result
+ String GTsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$11)";
+ String GTsubtotalsString = "Grand Total";
+
+ assertEquals(grandtotal, SCUtil.getValueFromCell(currentsheet, 2, 11),
+ 0.000000001);
+ assertEquals(GTsubtotals,
+ SCUtil.getFormulaFromCell(currentsheet, 2, 11));
+ assertEquals(GTsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 11));
+
+ // Save the file and reload it
+ SCUtil.saveFileAs(scComponent, "Subtotals", "ods");
+ XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
+ scDocument, "Subtotals.ods");
+ scDocument = scDocumentTemp;
+ currentsheet = SCUtil.getCurrentSheet(scDocument);
+
+ // verify it again
+ // Verify BS SubTotals result
+ BSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$4)";
+ BSsubtotalsString = "BS " + operatorString;
+
+ assertEquals(bssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 4), 0.000000001);
+ assertEquals(BSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 4));
+ assertEquals(BSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 4));
+
+ // Verify CS SubTotals result
+ CSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$6:$C$7)";
+ CSsubtotalsString = "CS " + operatorString;
+
+ assertEquals(cssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 7), 0.000000001);
+ assertEquals(CSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 7));
+ assertEquals(CSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 7));
+
+ // Verify MS SubTotals result
+ MSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$9:$C$10)";
+ MSsubtotalsString = "MS " + operatorString;
+
+ assertEquals(mssubtotalresult,
+ SCUtil.getValueFromCell(currentsheet, 2, 10), 0.000000001);
+ assertEquals(MSsubtotals,
+ SCUtil.getFormulaFromCell(currentsheet, 2, 10));
+ assertEquals(MSsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 10));
+
+ // Verify GrandTotal result
+ GTsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$11)";
+ GTsubtotalsString = "Grand Total";
+
+ assertEquals(grandtotal, SCUtil.getValueFromCell(currentsheet, 2, 11),
+ 0.000000001);
+ assertEquals(GTsubtotals,
+ SCUtil.getFormulaFromCell(currentsheet, 2, 11));
+ assertEquals(GTsubtotalsString,
+ SCUtil.getTextFromCell(currentsheet, 0, 11));
+ }
+}
Propchange: incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubTotalsFunction.java
------------------------------------------------------------------------------
svn:mime-type = text/plain
Added: incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java
URL: http://svn.apache.org/viewvc/incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java?rev=1388775&view=auto
==============================================================================
--- incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java (added)
+++ incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java Sat Sep 22 11:32:19 2012
@@ -0,0 +1,650 @@
+/**************************************************************
+ *
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing,
+ * software distributed under the License is distributed on an
+ * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+ * KIND, either express or implied. See the License for the
+ * specific language governing permissions and limitations
+ * under the License.
+ *
+ *************************************************************/
+package testcase.uno.sc.data;
+
+import static org.junit.Assert.*;
+
+import org.junit.After;
+import org.junit.AfterClass;
+import org.junit.Before;
+import org.junit.BeforeClass;
+import org.junit.Test;
+import org.openoffice.test.uno.UnoApp;
+
+import testlib.uno.SCUtil;
+
+import com.sun.star.lang.XComponent;
+import com.sun.star.sheet.GeneralFunction;
+import com.sun.star.sheet.SubTotalColumn;
+import com.sun.star.sheet.XCellRangeData;
+import com.sun.star.sheet.XSpreadsheet;
+import com.sun.star.sheet.XSpreadsheetDocument;
+import com.sun.star.sheet.XSubTotalCalculatable;
+import com.sun.star.sheet.XSubTotalDescriptor;
+import com.sun.star.table.XCellRange;
+import com.sun.star.text.XTextDocument;
+import com.sun.star.uno.UnoRuntime;
+
+public class SubtotalsForGroup {
+ private static final UnoApp app = new UnoApp();
+
+ UnoApp unoApp = new UnoApp();
+ XSpreadsheetDocument scDocument = null;
+ XComponent scComponent = null;
+
+ @Before
+ public void setUpDocument() throws Exception {
+ unoApp.start();
+ scComponent = unoApp.newDocument("scalc");
+ }
+
+ @After
+ public void tearDownDocument() {
+ unoApp.close();
+ unoApp.closeDocument(scComponent);
+
+ }
+
+ @BeforeClass
+ public static void setUpConnection() throws Exception {
+
+ }
+
+ @AfterClass
+ public static void tearDownConnection() throws InterruptedException,
+ Exception {
+
+ }
+
+ @Test
+ public void testForSecondGroup() throws Exception {
+ scComponent = unoApp.newDocument("scalc");
+ scDocument = SCUtil.getSCDocument(scComponent);
+ XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument);
+ XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(
+ XCellRange.class, currentsheet);
+ XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8");
+ XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface(
+ XCellRangeData.class, sourceRange);
+ Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" },
+ { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" },
+ { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" },
+ { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" },
+ { "CS", 30, 7, "C", "Tom" } };
+ sourceData.setDataArray(Source);
+
+ // Create SubTotals
+ XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime
+ .queryInterface(XSubTotalCalculatable.class, sourceRange);
+ XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true);
+ SubTotalColumn[] aColumns = new SubTotalColumn[1];
+ SubTotalColumn[] bColumns = new SubTotalColumn[1];
+ // calculate sum of Second column
+ aColumns[0] = new SubTotalColumn();
+ aColumns[0].Column = 1;
+ aColumns[0].Function = GeneralFunction.SUM;
+ // group by 4th column
+ xSubDesc.addNew(aColumns, 3);
+
+ // calculate sum of third column
+ bColumns[0] = new SubTotalColumn();
+ bColumns[0].Column = 2;
+ bColumns[0].Function = GeneralFunction.SUM;
+ // group by second column
+ xSubDesc.addNew(bColumns, 1);
+ xSub.applySubTotals(xSubDesc, true);
+
+ // Verify the result on line 3
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2));
+
+ assertEquals("=SUBTOTAL(9;$C$2:$C$2)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 2));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2),
+ 0.000000001);
+
+ // Verify the result on line 5
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4));
+
+ assertEquals("=SUBTOTAL(9;$C$4:$C$4)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 4));
+
+ assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4),
+ 0.000000001);
+
+ // Verify the result on line 7
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6));
+
+ assertEquals("=SUBTOTAL(9;$C$6:$C$6)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 6));
+
+ assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6),
+ 0.000000001);
+
+ // Verify the result on line 8
+ assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7));
+
+ assertEquals("=SUBTOTAL(9;$B$2:$B$7)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 7));
+
+ assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7),
+ 0.000000001);
+
+ // Verify the result on line 10
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9));
+
+ assertEquals("=SUBTOTAL(9;$C$9:$C$9)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 9));
+
+ assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9),
+ 0.000000001);
+
+ // Verify the result on line 12
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11));
+
+ assertEquals("=SUBTOTAL(9;$C$11:$C$11)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 11));
+
+ assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11),
+ 0.000000001);
+
+ // Verify the result on line 13
+ assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12));
+
+ assertEquals("=SUBTOTAL(9;$B$9:$B$12)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 12));
+
+ assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12),
+ 0.000000001);
+
+ // Verify the result on line 15
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14));
+
+ assertEquals("=SUBTOTAL(9;$C$14:$C$14)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 14));
+
+ assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14),
+ 0.000000001);
+
+ // Verify the result on line 17
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16));
+
+ assertEquals("=SUBTOTAL(9;$C$16:$C$16)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 16));
+
+ assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16),
+ 0.000000001);
+
+ // Verify the result on line 18
+ assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17));
+
+ assertEquals("=SUBTOTAL(9;$B$14:$B$17)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 17));
+
+ assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17),
+ 0.000000001);
+
+ // Verify the result on line 19
+ assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18));
+
+ assertEquals("=SUBTOTAL(9;$B$2:$B$18)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 18));
+
+ assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18),
+ 0.000000001);
+
+ // Save the file and reload it
+ SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods");
+ XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
+ scDocument, "SubTotalsForGroup.ods");
+ scDocument = scDocumentTemp;
+ currentsheet = SCUtil.getCurrentSheet(scDocument);
+
+ // verify it again
+ // Verify the result on line 3
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 2));
+
+ assertEquals("=SUBTOTAL(9;$C$2:$C$2)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 2));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 2),
+ 0.000000001);
+
+ // Verify the result on line 5
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 4));
+
+ assertEquals("=SUBTOTAL(9;$C$4:$C$4)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 4));
+
+ assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 4),
+ 0.000000001);
+
+ // Verify the result on line 7
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6));
+
+ assertEquals("=SUBTOTAL(9;$C$6:$C$6)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 6));
+
+ assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 6),
+ 0.000000001);
+
+ // Verify the result on line 8
+ assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 7));
+
+ assertEquals("=SUBTOTAL(9;$B$2:$B$7)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 7));
+
+ assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 7),
+ 0.000000001);
+
+ // Verify the result on line 10
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9));
+
+ assertEquals("=SUBTOTAL(9;$C$9:$C$9)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 9));
+
+ assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 9),
+ 0.000000001);
+
+ // Verify the result on line 12
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 11));
+
+ assertEquals("=SUBTOTAL(9;$C$11:$C$11)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 11));
+
+ assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 11),
+ 0.000000001);
+
+ // Verify the result on line 13
+ assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 12));
+
+ assertEquals("=SUBTOTAL(9;$B$9:$B$12)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 12));
+
+ assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 12),
+ 0.000000001);
+
+ // Verify the result on line 15
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 14));
+
+ assertEquals("=SUBTOTAL(9;$C$14:$C$14)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 14));
+
+ assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 14),
+ 0.000000001);
+
+ // Verify the result on line 17
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16));
+
+ assertEquals("=SUBTOTAL(9;$C$16:$C$16)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 16));
+
+ assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 16),
+ 0.000000001);
+
+ // Verify the result on line 18
+ assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 17));
+
+ assertEquals("=SUBTOTAL(9;$B$14:$B$17)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 17));
+
+ assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 17),
+ 0.000000001);
+
+ // Verify the result on line 19
+ assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 18));
+
+ assertEquals("=SUBTOTAL(9;$B$2:$B$18)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 18));
+
+ assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 18),
+ 0.000000001);
+
+ }
+
+ @Test
+ public void testForThirdGroup() throws Exception {
+ scComponent = unoApp.newDocument("scalc");
+ scDocument = SCUtil.getSCDocument(scComponent);
+ XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument);
+ XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(
+ XCellRange.class, currentsheet);
+ XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8");
+ XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface(
+ XCellRangeData.class, sourceRange);
+ Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" },
+ { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" },
+ { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" },
+ { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" },
+ { "CS", 30, 7, "C", "Tom" } };
+ sourceData.setDataArray(Source);
+
+ // Create SubTotals
+ XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime
+ .queryInterface(XSubTotalCalculatable.class, sourceRange);
+ XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true);
+ SubTotalColumn[] aColumns = new SubTotalColumn[1];
+ SubTotalColumn[] bColumns = new SubTotalColumn[1];
+ SubTotalColumn[] cColumns = new SubTotalColumn[1];
+ // calculate sum of Second column
+ aColumns[0] = new SubTotalColumn();
+ aColumns[0].Column = 1;
+ aColumns[0].Function = GeneralFunction.SUM;
+ // group by 4th column
+ xSubDesc.addNew(aColumns, 3);
+
+ // calculate sum of third column
+ bColumns[0] = new SubTotalColumn();
+ bColumns[0].Column = 2;
+ bColumns[0].Function = GeneralFunction.SUM;
+ // group by second column
+ xSubDesc.addNew(bColumns, 1);
+
+ // calculate sum of third column
+ cColumns[0] = new SubTotalColumn();
+ cColumns[0].Column = 4;
+ cColumns[0].Function = GeneralFunction.COUNT;
+ // group by first column
+ xSubDesc.addNew(cColumns, 0);
+ xSub.applySubTotals(xSubDesc, true);
+
+ // Verify the result on line 3
+ assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2));
+
+ assertEquals("=SUBTOTAL(3;$E$2:$E$2)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 2));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2),
+ 0.000000001);
+
+ // Verify the result on line 4
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3));
+
+ assertEquals("=SUBTOTAL(9;$C$2:$C$3)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 3));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3),
+ 0.000000001);
+
+ // Verify the result on line 6
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5));
+ assertEquals("=SUBTOTAL(3;$E$5:$E$5)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 5));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5),
+ 0.000000001);
+
+ // Verify the result on line7
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6));
+ assertEquals("=SUBTOTAL(9;$C$5:$C$6)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 6));
+ assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6),
+ 0.000000001);
+
+ // Verify the result on line 9
+ assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8));
+ assertEquals("=SUBTOTAL(3;$E$8:$E$8)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 8));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8),
+ 0.000000001);
+
+ // Verify the result on line 10
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9));
+ assertEquals("=SUBTOTAL(9;$C$8:$C$9)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 9));
+ assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9),
+ 0.000000001);
+
+ // Verify the result on line 11
+ assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10));
+ assertEquals("=SUBTOTAL(9;$B$2:$B$10)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 10));
+ assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10),
+ 0.000000001);
+
+ // Verify the result on line 13
+ assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12));
+ assertEquals("=SUBTOTAL(3;$E$12:$E$12)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 12));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12),
+ 0.000000001);
+
+ // Verify the result on line 14
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13));
+ assertEquals("=SUBTOTAL(9;$C$12:$C$13)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 13));
+ assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13),
+ 0.000000001);
+
+ // Verify the result on line 16
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15));
+ assertEquals("=SUBTOTAL(3;$E$15:$E$15)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 15));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15),
+ 0.000000001);
+
+ // Verify the result on line 17
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16));
+
+ assertEquals("=SUBTOTAL(9;$C$15:$C$16)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 16));
+
+ assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16),
+ 0.000000001);
+
+ // Verify the result on line 18
+ assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17));
+
+ assertEquals("=SUBTOTAL(9;$B$12:$B$17)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 17));
+
+ assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17),
+ 0.000000001);
+
+ // Verify the result on line 20
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19));
+ assertEquals("=SUBTOTAL(3;$E$19:$E$19)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 19));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19),
+ 0.000000001);
+
+ // Verify the result on line 21
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20));
+ assertEquals("=SUBTOTAL(9;$C$19:$C$20)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 20));
+ assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20),
+ 0.000000001);
+
+ // Verify the result on line 23
+ assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22));
+ assertEquals("=SUBTOTAL(3;$E$22:$E$22)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 22));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22),
+ 0.000000001);
+
+ // Verify the result on line 24
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23));
+
+ assertEquals("=SUBTOTAL(9;$C$22:$C$23)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 23));
+ assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23),
+ 0.000000001);
+
+ // Verify the result on line 25
+ assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24));
+
+ assertEquals("=SUBTOTAL(9;$B$19:$B$24)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 24));
+ assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24),
+ 0.000000001);
+
+ // Verify the result on line 26
+ assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25));
+ assertEquals("=SUBTOTAL(9;$B$2:$B$25)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 25));
+ assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25),
+ 0.000000001);
+
+ // Save the file and reload it
+ SCUtil.saveFileAs(scComponent, "SubTotalsForGroup", "ods");
+ XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp,
+ scDocument, "SubTotalsForGroup.ods");
+ scDocument = scDocumentTemp;
+ currentsheet = SCUtil.getCurrentSheet(scDocument);
+
+ // verify it again
+ // Verify the result on line 3
+ assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 2));
+
+ assertEquals("=SUBTOTAL(3;$E$2:$E$2)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 2));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 2),
+ 0.000000001);
+
+ // Verify the result on line 4
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 3));
+
+ assertEquals("=SUBTOTAL(9;$C$2:$C$3)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 3));
+
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 2, 3),
+ 0.000000001);
+
+ // Verify the result on line 6
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 5));
+ assertEquals("=SUBTOTAL(3;$E$5:$E$5)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 5));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 5),
+ 0.000000001);
+
+ // Verify the result on line7
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 6));
+ assertEquals("=SUBTOTAL(9;$C$5:$C$6)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 6));
+ assertEquals(2, SCUtil.getValueFromCell(currentsheet, 2, 6),
+ 0.000000001);
+
+ // Verify the result on line 9
+ assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 8));
+ assertEquals("=SUBTOTAL(3;$E$8:$E$8)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 8));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 8),
+ 0.000000001);
+
+ // Verify the result on line 10
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 9));
+ assertEquals("=SUBTOTAL(9;$C$8:$C$9)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 9));
+ assertEquals(5, SCUtil.getValueFromCell(currentsheet, 2, 9),
+ 0.000000001);
+
+ // Verify the result on line 11
+ assertEquals("A Sum", SCUtil.getTextFromCell(currentsheet, 3, 10));
+ assertEquals("=SUBTOTAL(9;$B$2:$B$10)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 10));
+ assertEquals(60, SCUtil.getValueFromCell(currentsheet, 1, 10),
+ 0.000000001);
+
+ // Verify the result on line 13
+ assertEquals("MS Count", SCUtil.getTextFromCell(currentsheet, 0, 12));
+ assertEquals("=SUBTOTAL(3;$E$12:$E$12)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 12));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 12),
+ 0.000000001);
+
+ // Verify the result on line 14
+ assertEquals("10 Sum", SCUtil.getTextFromCell(currentsheet, 1, 13));
+ assertEquals("=SUBTOTAL(9;$C$12:$C$13)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 13));
+ assertEquals(3, SCUtil.getValueFromCell(currentsheet, 2, 13),
+ 0.000000001);
+
+ // Verify the result on line 16
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 15));
+ assertEquals("=SUBTOTAL(3;$E$15:$E$15)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 15));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 15),
+ 0.000000001);
+
+ // Verify the result on line 17
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 16));
+
+ assertEquals("=SUBTOTAL(9;$C$15:$C$16)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 16));
+
+ assertEquals(4, SCUtil.getValueFromCell(currentsheet, 2, 16),
+ 0.000000001);
+
+ // Verify the result on line 18
+ assertEquals("B Sum", SCUtil.getTextFromCell(currentsheet, 3, 17));
+
+ assertEquals("=SUBTOTAL(9;$B$12:$B$17)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 17));
+
+ assertEquals(30, SCUtil.getValueFromCell(currentsheet, 1, 17),
+ 0.000000001);
+
+ // Verify the result on line 20
+ assertEquals("BS Count", SCUtil.getTextFromCell(currentsheet, 0, 19));
+ assertEquals("=SUBTOTAL(3;$E$19:$E$19)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 19));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 19),
+ 0.000000001);
+
+ // Verify the result on line 21
+ assertEquals("20 Sum", SCUtil.getTextFromCell(currentsheet, 1, 20));
+ assertEquals("=SUBTOTAL(9;$C$19:$C$20)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 20));
+ assertEquals(6, SCUtil.getValueFromCell(currentsheet, 2, 20),
+ 0.000000001);
+
+ // Verify the result on line 23
+ assertEquals("CS Count", SCUtil.getTextFromCell(currentsheet, 0, 22));
+ assertEquals("=SUBTOTAL(3;$E$22:$E$22)",
+ SCUtil.getFormulaFromCell(currentsheet, 4, 22));
+ assertEquals(1, SCUtil.getValueFromCell(currentsheet, 4, 22),
+ 0.000000001);
+
+ // Verify the result on line 24
+ assertEquals("30 Sum", SCUtil.getTextFromCell(currentsheet, 1, 23));
+
+ assertEquals("=SUBTOTAL(9;$C$22:$C$23)",
+ SCUtil.getFormulaFromCell(currentsheet, 2, 23));
+ assertEquals(7, SCUtil.getValueFromCell(currentsheet, 2, 23),
+ 0.000000001);
+
+ // Verify the result on line 25
+ assertEquals("C Sum", SCUtil.getTextFromCell(currentsheet, 3, 24));
+
+ assertEquals("=SUBTOTAL(9;$B$19:$B$24)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 24));
+ assertEquals(50, SCUtil.getValueFromCell(currentsheet, 1, 24),
+ 0.000000001);
+
+ // Verify the result on line 26
+ assertEquals("Grand Total", SCUtil.getTextFromCell(currentsheet, 3, 25));
+ assertEquals("=SUBTOTAL(9;$B$2:$B$25)",
+ SCUtil.getFormulaFromCell(currentsheet, 1, 25));
+ assertEquals(140, SCUtil.getValueFromCell(currentsheet, 1, 25),
+ 0.000000001);
+
+ }
+}
Propchange: incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data/SubtotalsForGroup.java
------------------------------------------------------------------------------
svn:mime-type = text/plain