/************************************************************** | |
* | |
* 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 fvt.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)); | |
} | |
} |