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 data()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 setUpDocument()91 public void setUpDocument() throws Exception { 92 unoApp.start(); 93 } 94 95 @After tearDownDocument()96 public void tearDownDocument() { 97 unoApp.close(); 98 unoApp.closeDocument(scComponent); 99 100 } 101 102 @BeforeClass setUpConnection()103 public static void setUpConnection() throws Exception { 104 105 } 106 107 @AfterClass tearDownConnection()108 public static void tearDownConnection() throws InterruptedException, 109 Exception { 110 111 } 112 SubTotalsFunction(Enum operator, String operatorString, int operatorvalue, double bssubtotalresult, double cssubtotalresult, double mssubtotalresult, double grandtotal)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 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