1 /************************************************************** 2 * 3 * Licensed to the Apache Software Foundation (ASF) under one 4 * or more contributor license agreements. See the NOTICE file 5 * distributed with this work for additional information 6 * regarding copyright ownership. The ASF licenses this file 7 * to you under the Apache License, Version 2.0 (the 8 * "License"); you may not use this file except in compliance 9 * with the License. You may obtain a copy of the License at 10 * 11 * http://www.apache.org/licenses/LICENSE-2.0 12 * 13 * Unless required by applicable law or agreed to in writing, 14 * software distributed under the License is distributed on an 15 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 16 * KIND, either express or implied. See the License for the 17 * specific language governing permissions and limitations 18 * under the License. 19 * 20 *************************************************************/ 21 package fvt.uno.sc.data; 22 23 import static org.junit.Assert.*; 24 25 import java.util.Arrays; 26 import java.util.Collection; 27 28 import org.junit.After; 29 import org.junit.AfterClass; 30 import org.junit.Before; 31 import org.junit.BeforeClass; 32 import org.junit.Test; 33 import org.junit.runner.RunWith; 34 import org.junit.runners.Parameterized; 35 import org.junit.runners.Parameterized.Parameters; 36 import org.openoffice.test.uno.UnoApp; 37 import testlib.uno.SCUtil; 38 import com.sun.star.lang.XComponent; 39 import com.sun.star.sheet.GeneralFunction; 40 import com.sun.star.sheet.SubTotalColumn; 41 import com.sun.star.sheet.XCellRangeData; 42 import com.sun.star.sheet.XSpreadsheet; 43 import com.sun.star.sheet.XSpreadsheetDocument; 44 import com.sun.star.sheet.XSubTotalCalculatable; 45 import com.sun.star.sheet.XSubTotalDescriptor; 46 import com.sun.star.table.XCellRange; 47 import com.sun.star.uno.Enum; 48 import com.sun.star.uno.UnoRuntime; 49 50 @RunWith(value = Parameterized.class) 51 public class SubTotalsFunction { 52 private static final UnoApp app = new UnoApp(); 53 54 UnoApp unoApp = new UnoApp(); 55 XSpreadsheetDocument scDocument = null; 56 XComponent scComponent = null; 57 58 private GeneralFunction operator; 59 60 private String operatorString; 61 62 private int operatorvalue; 63 64 private double bssubtotalresult; 65 66 private double cssubtotalresult; 67 68 private double mssubtotalresult; 69 70 private double grandtotal; 71 72 @Parameters 73 public static Collection<Object[]> data() throws Exception { 74 // Remove GeneralFunction.Auto,GeneralFunction.NONE 75 return Arrays.asList(new Object[][] { 76 { GeneralFunction.SUM, "Sum", 9, 12, 12, 4, 28 }, 77 { GeneralFunction.AVERAGE, "Average", 1, 4, 6, 2, 4 }, 78 { GeneralFunction.COUNT, "Count", 3, 3, 2, 2, 7 }, 79 { GeneralFunction.COUNTNUMS, "Count", 2, 3, 2, 2, 7 }, 80 { GeneralFunction.MAX, "Max", 4, 6, 7, 3, 7 }, 81 { GeneralFunction.MIN, "Min", 5, 2, 5, 1, 1 }, 82 { GeneralFunction.VAR, "Var", 10, 4, 2, 2, 4.666666667 }, 83 { GeneralFunction.PRODUCT, "Product", 6, 48, 35, 3, 5040 }, 84 { GeneralFunction.STDEVP, "StDev", 8, 1.6329931619, 1, 1, 2 }, 85 { GeneralFunction.STDEV, "StDev", 7, 2, 1.4142135624, 86 1.4142135624, 2.1602468995 }, 87 { GeneralFunction.VARP, "Var", 11, 2.6666666667, 1, 1, 4 }, }); 88 } 89 90 @Before 91 public void setUpDocument() throws Exception { 92 unoApp.start(); 93 } 94 95 @After 96 public void tearDownDocument() { 97 unoApp.close(); 98 unoApp.closeDocument(scComponent); 99 100 } 101 102 @BeforeClass 103 public static void setUpConnection() throws Exception { 104 105 } 106 107 @AfterClass 108 public static void tearDownConnection() throws InterruptedException, 109 Exception { 110 111 } 112 113 public SubTotalsFunction(Enum operator, String operatorString, 114 int operatorvalue, double bssubtotalresult, 115 double cssubtotalresult, double mssubtotalresult, double grandtotal) { 116 this.operator = (GeneralFunction) operator; 117 this.operatorString = operatorString; 118 this.operatorvalue = operatorvalue; 119 this.bssubtotalresult = bssubtotalresult; 120 this.cssubtotalresult = cssubtotalresult; 121 this.mssubtotalresult = mssubtotalresult; 122 this.grandtotal = grandtotal; 123 } 124 125 @Test 126 public void test() throws Exception { 127 // New document and input data in document 128 scComponent = unoApp.newDocument("scalc"); 129 scDocument = SCUtil.getSCDocument(scComponent); 130 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 131 XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface( 132 XCellRange.class, currentsheet); 133 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 134 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 135 XCellRangeData.class, sourceRange); 136 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 137 { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 138 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 139 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 140 { "CS", 30, 7, "C", "Tom" } }; 141 sourceData.setDataArray(Source); 142 143 // Create SubTotals 144 XSubTotalCalculatable xSub = (XSubTotalCalculatable) UnoRuntime 145 .queryInterface(XSubTotalCalculatable.class, sourceRange); 146 XSubTotalDescriptor xSubDesc = xSub.createSubTotalDescriptor(true); 147 SubTotalColumn[] aColumns = new SubTotalColumn[1]; 148 // calculate sum of third column 149 aColumns[0] = new SubTotalColumn(); 150 aColumns[0].Column = 2; 151 aColumns[0].Function = operator; 152 // group by first column 153 xSubDesc.addNew(aColumns, 0); 154 xSub.applySubTotals(xSubDesc, true); 155 156 // Verify BS SubTotals result 157 String BSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$4)"; 158 String BSsubtotalsString = "BS " + operatorString; 159 160 assertEquals(bssubtotalresult, 161 SCUtil.getValueFromCell(currentsheet, 2, 4), 0.000000001); 162 assertEquals(BSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 163 assertEquals(BSsubtotalsString, 164 SCUtil.getTextFromCell(currentsheet, 0, 4)); 165 166 // Verify CS SubTotals result 167 String CSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$6:$C$7)"; 168 String CSsubtotalsString = "CS " + operatorString; 169 170 assertEquals(cssubtotalresult, 171 SCUtil.getValueFromCell(currentsheet, 2, 7), 0.000000001); 172 assertEquals(CSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 7)); 173 assertEquals(CSsubtotalsString, 174 SCUtil.getTextFromCell(currentsheet, 0, 7)); 175 176 // Verify MS SubTotals result 177 String MSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$9:$C$10)"; 178 String MSsubtotalsString = "MS " + operatorString; 179 180 assertEquals(mssubtotalresult, 181 SCUtil.getValueFromCell(currentsheet, 2, 10), 0.000000001); 182 assertEquals(MSsubtotals, 183 SCUtil.getFormulaFromCell(currentsheet, 2, 10)); 184 assertEquals(MSsubtotalsString, 185 SCUtil.getTextFromCell(currentsheet, 0, 10)); 186 187 // Verify GrandTotal result 188 String GTsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$11)"; 189 String GTsubtotalsString = "Grand Total"; 190 191 assertEquals(grandtotal, SCUtil.getValueFromCell(currentsheet, 2, 11), 192 0.000000001); 193 assertEquals(GTsubtotals, 194 SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 195 assertEquals(GTsubtotalsString, 196 SCUtil.getTextFromCell(currentsheet, 0, 11)); 197 198 // Save the file and reload it 199 SCUtil.saveFileAs(scComponent, "Subtotals", "ods"); 200 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 201 scDocument, "Subtotals.ods"); 202 scDocument = scDocumentTemp; 203 currentsheet = SCUtil.getCurrentSheet(scDocument); 204 205 // verify it again 206 // Verify BS SubTotals result 207 BSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$4)"; 208 BSsubtotalsString = "BS " + operatorString; 209 210 assertEquals(bssubtotalresult, 211 SCUtil.getValueFromCell(currentsheet, 2, 4), 0.000000001); 212 assertEquals(BSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 4)); 213 assertEquals(BSsubtotalsString, 214 SCUtil.getTextFromCell(currentsheet, 0, 4)); 215 216 // Verify CS SubTotals result 217 CSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$6:$C$7)"; 218 CSsubtotalsString = "CS " + operatorString; 219 220 assertEquals(cssubtotalresult, 221 SCUtil.getValueFromCell(currentsheet, 2, 7), 0.000000001); 222 assertEquals(CSsubtotals, SCUtil.getFormulaFromCell(currentsheet, 2, 7)); 223 assertEquals(CSsubtotalsString, 224 SCUtil.getTextFromCell(currentsheet, 0, 7)); 225 226 // Verify MS SubTotals result 227 MSsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$9:$C$10)"; 228 MSsubtotalsString = "MS " + operatorString; 229 230 assertEquals(mssubtotalresult, 231 SCUtil.getValueFromCell(currentsheet, 2, 10), 0.000000001); 232 assertEquals(MSsubtotals, 233 SCUtil.getFormulaFromCell(currentsheet, 2, 10)); 234 assertEquals(MSsubtotalsString, 235 SCUtil.getTextFromCell(currentsheet, 0, 10)); 236 237 // Verify GrandTotal result 238 GTsubtotals = "=SUBTOTAL(" + operatorvalue + ";$C$2:$C$11)"; 239 GTsubtotalsString = "Grand Total"; 240 241 assertEquals(grandtotal, SCUtil.getValueFromCell(currentsheet, 2, 11), 242 0.000000001); 243 assertEquals(GTsubtotals, 244 SCUtil.getFormulaFromCell(currentsheet, 2, 11)); 245 assertEquals(GTsubtotalsString, 246 SCUtil.getTextFromCell(currentsheet, 0, 11)); 247 } 248 } 249