1 /* Licensed to the Apache Software Foundation (ASF) under one 2 * or more contributor license agreements. See the NOTICE file 3 * distributed with this work for additional information 4 * regarding copyright ownership. The ASF licenses this file 5 * to you under the Apache License, Version 2.0 (the 6 * "License"); you may not use this file except in compliance 7 * with the License. You may obtain a copy of the License at 8 * 9 * http://www.apache.org/licenses/LICENSE-2.0 10 * 11 * Unless required by applicable law or agreed to in writing, 12 * software distributed under the License is distributed on an 13 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 14 * KIND, either express or implied. See the License for the 15 * specific language governing permissions and limitations 16 * under the License. 17 * 18 *************************************************************/ 19 20 package testcase.uno.sc.sheet; 21 22 import static org.junit.Assert.*; 23 import org.junit.After; 24 import org.junit.AfterClass; 25 import org.junit.Before; 26 import org.junit.BeforeClass; 27 import org.junit.Test; 28 import org.openoffice.test.common.FileUtil; 29 import org.openoffice.test.common.Testspace; 30 import org.openoffice.test.uno.UnoApp; 31 32 import com.sun.star.beans.PropertyValue; 33 import com.sun.star.container.XIndexAccess; 34 import com.sun.star.container.XNamed; 35 import com.sun.star.frame.XModel; 36 import com.sun.star.frame.XStorable; 37 import com.sun.star.io.IOException; 38 import com.sun.star.lang.IndexOutOfBoundsException; 39 import com.sun.star.lang.WrappedTargetException; 40 import com.sun.star.lang.XComponent; 41 import com.sun.star.sheet.XSpreadsheet; 42 import com.sun.star.sheet.XSpreadsheetDocument; 43 import com.sun.star.sheet.XSpreadsheetView; 44 import com.sun.star.sheet.XSpreadsheets; 45 import com.sun.star.table.XCell; 46 import com.sun.star.uno.UnoRuntime; 47 48 public class SheetBasicTest { 49 UnoApp unoApp = new UnoApp(); 50 51 XSpreadsheetDocument scDocument = null; 52 XComponent scComponent = null; 53 54 @BeforeClass 55 public static void setUpBeforeClass() throws Exception { 56 57 } 58 59 @AfterClass 60 public static void tearDownAfterClass() throws Exception { 61 } 62 63 @Before 64 public void setUp() throws Exception { 65 unoApp.start(); 66 // New a SC document 67 scComponent = unoApp.newDocument("scalc"); 68 } 69 70 @After 71 public void tearDown() throws Exception { 72 unoApp.closeDocument(scComponent); 73 unoApp.close(); 74 } 75 76 @Test 77 public void insertRenameDeleteSheet() throws Exception { 78 // Insert a sheet named aa after first sheet 79 String sheetname = "aa"; 80 scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( 81 XSpreadsheetDocument.class, scComponent); 82 XSpreadsheets spreadsheets = scDocument.getSheets(); 83 spreadsheets.insertNewByName(sheetname, (short) 1); 84 85 // active the sheet second sheet aa 86 XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime 87 .queryInterface(XIndexAccess.class, spreadsheets); 88 XSpreadsheet newSpreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( 89 XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); 90 XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( 91 XModel.class, scDocument); 92 XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime 93 .queryInterface(XSpreadsheetView.class, 94 xSpreadsheetModel.getCurrentController()); 95 xSpeadsheetView.setActiveSheet(newSpreadSheet); 96 97 // get the new speadsheet name 98 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 99 newSpreadSheet); 100 assertEquals("actual should equals aa", sheetname, xsheetname.getName()); 101 102 // Change the Spreadsheet name 103 String changedname = "SpeadsheetAfterChange"; 104 xsheetname.setName(changedname); 105 106 // Save and reload document 107 reloadSpreadsheet("TestSpreadsheet.xls"); 108 109 // Verify the changed Spreadsheet name 110 assertEquals("actual should equals SpeadsheetAfterChange", changedname, 111 xsheetname.getName()); 112 113 spreadsheets.removeByName(changedname); 114 115 assertFalse("actual should equals false", 116 spreadsheets.hasByName(changedname)); 117 118 } 119 120 @Test 121 public void copypastesheet() throws Exception { 122 // Insert some value into cells 123 scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( 124 XSpreadsheetDocument.class, scComponent); 125 XSpreadsheets spreadsheets = scDocument.getSheets(); 126 XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime 127 .queryInterface(XIndexAccess.class, spreadsheets); 128 XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( 129 XSpreadsheet.class, xspreadsheetIndex.getByIndex(0)); 130 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 131 spreadSheet); 132 xsheetname.setName("sourcesheet"); 133 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 134 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 135 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 136 // input strings into sheet1 137 for (int intY = 0; intY < stringValues.length; intY++) { 138 for (int intX = 0; intX < stringValues[intY].length; intX++) { 139 // Insert the value to the cell, specified by intY and intX. 140 this.insertIntoCell(intY, intX, stringValues[intY][intX], 141 spreadSheet, ""); 142 } 143 } 144 145 // copy the sheet from sourcesheet to copysheet 146 String newcopysheet = "copysheet"; 147 spreadsheets.copyByName(xsheetname.getName(), newcopysheet, (short) 2); 148 149 // Save and reload document 150 reloadSpreadsheet("TestCopysheet.xls"); 151 XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( 152 XModel.class, scDocument); 153 XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime 154 .queryInterface(XSpreadsheetView.class, 155 xSpreadsheetModel.getCurrentController()); 156 XSpreadsheet copysheet = (XSpreadsheet) UnoRuntime.queryInterface( 157 XSpreadsheet.class, xspreadsheetIndex.getByIndex(2)); 158 for (int intY = 0; intY < stringValues.length; intY++) { 159 for (int intX = 0; intX < stringValues[intY].length; intX++) { 160 XCell xcell = null; 161 xcell = copysheet.getCellByPosition(intY, intX); 162 assertEquals(stringValues[intY][intX], xcell.getFormula()); 163 } 164 } 165 166 } 167 168 @Test 169 public void movesheet() throws Exception { 170 171 // new sc document 172 scDocument = (XSpreadsheetDocument) UnoRuntime.queryInterface( 173 XSpreadsheetDocument.class, scComponent); 174 175 // change the first sheet name and input same value into the sheet cell 176 XSpreadsheets spreadsheets = scDocument.getSheets(); 177 XIndexAccess xspreadsheetIndex = (XIndexAccess) UnoRuntime 178 .queryInterface(XIndexAccess.class, spreadsheets); 179 XSpreadsheet spreadSheet = (XSpreadsheet) UnoRuntime.queryInterface( 180 XSpreadsheet.class, xspreadsheetIndex.getByIndex(0)); 181 XNamed xsheetname = (XNamed) UnoRuntime.queryInterface(XNamed.class, 182 spreadSheet); 183 String sheetname = "sourcesheet"; 184 xsheetname.setName(sheetname); 185 String[][] stringValues = { { "", "Jan", "Feb", "Mar", "Apr", "Mai" }, 186 { "Profit", "12.3", "43.2", "5.1", "76", "56.8" }, 187 { "Rival in business", "12.2", "12.6", "17.7", "20.4", "100" }, }; 188 189 for (int intY = 0; intY < stringValues.length; intY++) { 190 for (int intX = 0; intX < stringValues[intY].length; intX++) { 191 // Insert the value to the cell, specified by intY and intX. 192 this.insertIntoCell(intY, intX, stringValues[intY][intX], 193 spreadSheet, ""); 194 } 195 } 196 197 // Before move, get the 2nd sheet name 198 XSpreadsheet secondSheetBeforeMove = (XSpreadsheet) UnoRuntime 199 .queryInterface(XSpreadsheet.class, 200 xspreadsheetIndex.getByIndex(1)); 201 XNamed secondSheetNameBeforeMove = (XNamed) UnoRuntime.queryInterface( 202 XNamed.class, secondSheetBeforeMove); 203 204 // move the first sheet 205 spreadsheets.moveByName(sheetname, (short) 2); 206 207 // Save and reload document 208 reloadSpreadsheet("Testmovesheet.xls"); 209 210 // After move, get the first sheet name, and verify it same as 2nd sheet 211 // name before move 212 XSpreadsheet firstSheetAfterMove = (XSpreadsheet) UnoRuntime 213 .queryInterface(XSpreadsheet.class, 214 xspreadsheetIndex.getByIndex(0)); 215 XNamed xfirstsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface( 216 XNamed.class, firstSheetAfterMove); 217 assertEquals("Expect result should be Sheet2", 218 secondSheetNameBeforeMove.getName(), 219 xfirstsheetnameAfterMove.getName()); 220 221 // Get the target sheet name after move 222 XSpreadsheet sheetAfterMove = (XSpreadsheet) UnoRuntime.queryInterface( 223 XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); 224 XNamed xsheetnameAfterMove = (XNamed) UnoRuntime.queryInterface( 225 XNamed.class, sheetAfterMove); 226 assertEquals("Expect result should be sourcesheet", sheetname, 227 xsheetnameAfterMove.getName()); 228 229 // Check the cell value after move 230 XModel xSpreadsheetModel = (XModel) UnoRuntime.queryInterface( 231 XModel.class, scDocument); 232 XSpreadsheetView xSpeadsheetView = (XSpreadsheetView) UnoRuntime 233 .queryInterface(XSpreadsheetView.class, 234 xSpreadsheetModel.getCurrentController()); 235 XSpreadsheet movesheet = (XSpreadsheet) UnoRuntime.queryInterface( 236 XSpreadsheet.class, xspreadsheetIndex.getByIndex(1)); 237 for (int intY = 0; intY < stringValues.length; intY++) { 238 for (int intX = 0; intX < stringValues[intY].length; intX++) { 239 XCell xcell = null; 240 xcell = movesheet.getCellByPosition(intY, intX); 241 assertEquals(stringValues[intY][intX], xcell.getFormula()); 242 } 243 } 244 245 } 246 247 // input value into sheet cell 248 public static void insertIntoCell(int intX, int intY, String stringValue, 249 XSpreadsheet xspreadsheet, String stringFlag) 250 throws IndexOutOfBoundsException { 251 XCell xcell = null; 252 xcell = xspreadsheet.getCellByPosition(intX, intY); 253 if (stringFlag.equals("V")) { 254 xcell.setValue((new Float(stringValue)).floatValue()); 255 } else { 256 xcell.setFormula(stringValue); 257 } 258 } 259 260 // Save and load the document 261 public XSpreadsheetDocument reloadSpreadsheet(String spreadSheetname) 262 throws Exception { 263 String filePath = Testspace.getPath("output/" + spreadSheetname); 264 XStorable xStorable = (XStorable) UnoRuntime.queryInterface( 265 XStorable.class, scDocument); 266 PropertyValue[] aStoreProperties = new PropertyValue[2]; 267 aStoreProperties[0] = new PropertyValue(); 268 aStoreProperties[1] = new PropertyValue(); 269 aStoreProperties[0].Name = "Override"; 270 aStoreProperties[0].Value = true; 271 aStoreProperties[1].Name = "FilterName"; 272 aStoreProperties[1].Value = "MS Excel 97"; 273 xStorable.storeAsURL(FileUtil.getUrl(filePath), aStoreProperties); 274 275 return UnoRuntime.queryInterface(XSpreadsheetDocument.class, 276 unoApp.loadDocument(filePath)); 277 } 278 } 279