incubator-ooo-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From liu...@apache.org
Subject svn commit: r1388775 - in /incubator/ooo/trunk/test/testuno/source/testcase/uno/sc/data: SubTotalsFunction.java SubtotalsForGroup.java
Date Sat, 22 Sep 2012 11:32:19 GMT
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 <polo8495@hotmail.com>
Review By: Liu Zhe <aliuzhe@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



Mime
View raw message