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 org.junit.After; 26 import org.junit.AfterClass; 27 import org.junit.Before; 28 import org.junit.BeforeClass; 29 import org.junit.Test; 30 import org.openoffice.test.uno.UnoApp; 31 import testlib.uno.SCUtil; 32 import com.sun.star.beans.PropertyValue; 33 import com.sun.star.lang.XComponent; 34 import com.sun.star.sheet.XCellAddressable; 35 import com.sun.star.sheet.XCellRangeData; 36 import com.sun.star.sheet.XSpreadsheet; 37 import com.sun.star.sheet.XSpreadsheetDocument; 38 import com.sun.star.table.CellAddress; 39 import com.sun.star.table.TableSortField; 40 import com.sun.star.table.XCell; 41 import com.sun.star.table.XCellRange; 42 import com.sun.star.uno.UnoRuntime; 43 import com.sun.star.util.XSortable; 44 45 public class DataSort { 46 UnoApp unoApp = new UnoApp(); 47 XSpreadsheetDocument scDocument = null; 48 XComponent scComponent = null; 49 50 @Before setUpDocument()51 public void setUpDocument() throws Exception { 52 unoApp.start(); 53 // New a SC document 54 scComponent = unoApp.newDocument("scalc"); 55 } 56 57 @After tearDownDocument()58 public void tearDownDocument() { 59 unoApp.close(); 60 unoApp.closeDocument(scComponent); 61 } 62 63 @BeforeClass setUpConnection()64 public static void setUpConnection() throws Exception { 65 66 } 67 68 @AfterClass tearDownConnection()69 public static void tearDownConnection() throws InterruptedException, 70 Exception { 71 72 } 73 74 /** 75 * test single field sort 76 */ 77 @Test singleFieldSort()78 public void singleFieldSort() throws Exception { 79 scDocument = SCUtil.getSCDocument(scComponent); 80 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 81 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 82 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 83 XCellRangeData.class, sourceRange); 84 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 85 { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 86 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 87 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 88 { "CS", 30, 7, "C", "Tom" } }; 89 sourceData.setDataArray(Source); 90 91 // define the fields to sort 92 TableSortField[] aSortFields = new TableSortField[1]; 93 aSortFields[0] = new TableSortField(); 94 aSortFields[0].Field = 1; 95 aSortFields[0].IsAscending = true; 96 aSortFields[0].IsCaseSensitive = false; 97 98 // define the sort descriptor 99 PropertyValue[] aSortDesc = new PropertyValue[2]; 100 aSortDesc[0] = new PropertyValue(); 101 aSortDesc[0].Name = "SortFields"; 102 aSortDesc[0].Value = aSortFields; 103 aSortDesc[1] = new PropertyValue(); 104 aSortDesc[1].Name = "ContainsHeader"; 105 aSortDesc[1].Value = new Boolean(true); 106 107 // perform the sorting 108 XSortable xSort = (XSortable) UnoRuntime.queryInterface( 109 XSortable.class, sourceRange); 110 xSort.sort(aSortDesc); 111 112 // Verify the sorting result 113 String[][] expectResult = { { "Level", "Code", "No.", "Team", "Name" }, 114 { "MS", "10", "1", "A", "Joker" }, 115 { "MS", "10", "3", "B", "Kevin" }, 116 { "BS", "20", "4", "B", "Elle" }, 117 { "BS", "20", "6", "C", "Sweet" }, 118 { "BS", "20", "2", "A", "Chcomic" }, 119 { "CS", "30", "5", "A", "Ally" }, 120 { "CS", "30", "7", "C", "Tom" } }; 121 String[][] actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 122 0, 4, 7); 123 assertArrayEquals(expectResult, actureResult); 124 125 // Save and reload the document verify the sort result again 126 SCUtil.saveFileAs(scComponent, "SortSingleFiled", "ods"); 127 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 128 scDocument, "SortSingleFiled.ods"); 129 scDocument = scDocumentTemp; 130 currentsheet = SCUtil.getCurrentSheet(scDocument); 131 actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 0, 4, 7); 132 assertArrayEquals(expectResult, actureResult); 133 134 } 135 136 /** 137 * test sort with two fields 138 */ 139 @Test sortByTwoFields()140 public void sortByTwoFields() throws Exception { 141 scDocument = SCUtil.getSCDocument(scComponent); 142 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 143 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 144 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 145 XCellRangeData.class, sourceRange); 146 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 147 { "BS", 20, 4, "B", "Elle" }, { "BS", 20, 6, "C", "Sweet" }, 148 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 149 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 150 { "CS", 30, 7, "C", "Tom" } }; 151 sourceData.setDataArray(Source); 152 153 // define the fields to sort 154 TableSortField[] aSortFields = new TableSortField[2]; 155 aSortFields[0] = new TableSortField(); 156 aSortFields[0].Field = 1; 157 aSortFields[0].IsAscending = true; 158 aSortFields[0].IsCaseSensitive = false; 159 aSortFields[1] = new TableSortField(); 160 aSortFields[1].Field = 2; 161 aSortFields[1].IsAscending = false; 162 aSortFields[1].IsCaseSensitive = false; 163 164 // define the sort descriptor 165 PropertyValue[] aSortDesc = new PropertyValue[2]; 166 aSortDesc[0] = new PropertyValue(); 167 aSortDesc[0].Name = "SortFields"; 168 aSortDesc[0].Value = aSortFields; 169 aSortDesc[1] = new PropertyValue(); 170 aSortDesc[1].Name = "ContainsHeader"; 171 aSortDesc[1].Value = new Boolean(true); 172 173 // perform the sorting 174 XSortable xSort = (XSortable) UnoRuntime.queryInterface( 175 XSortable.class, sourceRange); 176 xSort.sort(aSortDesc); 177 178 // Verify the sorting result 179 String[][] expectResult = { { "Level", "Code", "No.", "Team", "Name" }, 180 { "MS", "10", "3", "B", "Kevin" }, 181 { "MS", "10", "1", "A", "Joker" }, 182 { "BS", "20", "6", "C", "Sweet" }, 183 { "BS", "20", "4", "B", "Elle" }, 184 { "BS", "20", "2", "A", "Chcomic" }, 185 { "CS", "30", "7", "C", "Tom" }, 186 { "CS", "30", "5", "A", "Ally" } }; 187 String[][] actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 188 0, 4, 7); 189 assertArrayEquals(expectResult, actureResult); 190 191 // Save and reload the document verify the sort result again 192 SCUtil.saveFileAs(scComponent, "SortTwoFileds", "xls"); 193 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 194 scDocument, "SortTwoFileds.xls"); 195 scDocument = scDocumentTemp; 196 currentsheet = SCUtil.getCurrentSheet(scDocument); 197 actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 0, 4, 7); 198 assertArrayEquals(expectResult, actureResult); 199 } 200 201 202 /** 203 * test sort with three fields 204 */ 205 @Test sortByThreeField()206 public void sortByThreeField() throws Exception { 207 scDocument = SCUtil.getSCDocument(scComponent); 208 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 209 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:E8"); 210 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 211 XCellRangeData.class, sourceRange); 212 Object[][] Source = { { "Level", "Code", "No.", "Team", "Name" }, 213 { "BS", 20, 4, "B", "Elle" }, { "MS", 20, 6, "C", "Sweet" }, 214 { "BS", 20, 2, "A", "Chcomic" }, { "CS", 30, 5, "A", "Ally" }, 215 { "MS", 10, 1, "A", "Joker" }, { "MS", 10, 3, "B", "Kevin" }, 216 { "CS", 30, 7, "C", "Tom" } }; 217 sourceData.setDataArray(Source); 218 219 // --- sort by second column, ascending --- 220 221 // define the fields to sort 222 TableSortField[] aSortFields = new TableSortField[3]; 223 aSortFields[0] = new TableSortField(); 224 aSortFields[0].Field = 0; 225 aSortFields[0].IsAscending = true; 226 aSortFields[0].IsCaseSensitive = false; 227 aSortFields[1] = new TableSortField(); 228 aSortFields[1].Field = 1; 229 aSortFields[1].IsAscending = false; 230 aSortFields[1].IsCaseSensitive = false; 231 aSortFields[2] = new TableSortField(); 232 aSortFields[2].Field = 2; 233 aSortFields[2].IsAscending = false; 234 aSortFields[2].IsCaseSensitive = false; 235 236 // define the sort descriptor 237 PropertyValue[] aSortDesc = new PropertyValue[2]; 238 aSortDesc[0] = new PropertyValue(); 239 aSortDesc[0].Name = "SortFields"; 240 aSortDesc[0].Value = aSortFields; 241 aSortDesc[1] = new PropertyValue(); 242 aSortDesc[1].Name = "ContainsHeader"; 243 aSortDesc[1].Value = new Boolean(true); 244 245 // perform the sorting 246 XSortable xSort = (XSortable) UnoRuntime.queryInterface( 247 XSortable.class, sourceRange); 248 xSort.sort(aSortDesc); 249 250 // Verify the sorting result 251 String[][] expectResult = { { "Level", "Code", "No.", "Team", "Name" }, 252 { "BS", "20", "4", "B", "Elle" }, 253 { "BS", "20", "2", "A", "Chcomic" }, 254 { "CS", "30", "7", "C", "Tom" }, 255 { "CS", "30", "5", "A", "Ally" }, 256 { "MS", "20", "6", "C", "Sweet" }, 257 { "MS", "10", "3", "B", "Kevin" }, 258 { "MS", "10", "1", "A", "Joker" } }; 259 String[][] actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 260 0, 4, 7); 261 assertArrayEquals(expectResult, actureResult); 262 263 // Save and reload the document verify the sort result again 264 SCUtil.saveFileAs(scComponent, "SortThreeFileds", "ods"); 265 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 266 scDocument, "SortThreeFileds.ods"); 267 scDocument = scDocumentTemp; 268 currentsheet = SCUtil.getCurrentSheet(scDocument); 269 actureResult = SCUtil.getTextFromCellRange(currentsheet, 0, 0, 4, 7); 270 assertArrayEquals(expectResult, actureResult); 271 } 272 273 /** 274 * test sort options 275 */ 276 @Test sortOption()277 public void sortOption() throws Exception { 278 scDocument = SCUtil.getSCDocument(scComponent); 279 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 280 XCellRange sourceRange = currentsheet.getCellRangeByName("A1:A8"); 281 XCellRangeData sourceData = (XCellRangeData) UnoRuntime.queryInterface( 282 XCellRangeData.class, sourceRange); 283 Object[][] source = { { "Fri" }, { "Mon" }, { "Sun" }, { "Wed" }, 284 { "Thu" }, { "Sat" }, { "Tue" }, { "SUN" } }; 285 sourceData.setDataArray(source); 286 287 XCell cell = currentsheet.getCellByPosition(1, 0); 288 XCellAddressable xCellAddr = (XCellAddressable) UnoRuntime 289 .queryInterface(XCellAddressable.class, cell); 290 CellAddress copytoAddress = xCellAddr.getCellAddress(); 291 292 // define the fields to sort Sort by column 1and sort Ascending and not 293 // case sensitive 294 TableSortField[] aSortFields = new TableSortField[1]; 295 aSortFields[0] = new TableSortField(); 296 aSortFields[0].Field = 0; 297 aSortFields[0].IsAscending = true; 298 aSortFields[0].IsCaseSensitive = false; 299 300 // define the sort descriptor 301 // Range not contain label,Including formats,copy result to B1, and sort 302 // with custom sort order 303 PropertyValue[] aSortDesc = new PropertyValue[7]; 304 aSortDesc[0] = new PropertyValue(); 305 aSortDesc[0].Name = "SortFields"; 306 aSortDesc[0].Value = aSortFields; 307 aSortDesc[1] = new PropertyValue(); 308 aSortDesc[1].Name = "ContainsHeader"; 309 aSortDesc[1].Value = new Boolean(false); 310 aSortDesc[2] = new PropertyValue(); 311 aSortDesc[2].Name = "BindFormatsToContent"; 312 aSortDesc[2].Value = new Boolean(true); 313 aSortDesc[3] = new PropertyValue(); 314 aSortDesc[3].Name = "IsUserListEnabled"; 315 aSortDesc[3].Value = new Boolean(true); 316 aSortDesc[4] = new PropertyValue(); 317 aSortDesc[4].Name = "UserListIndex"; 318 aSortDesc[4].Value = 0; 319 aSortDesc[5] = new PropertyValue(); 320 aSortDesc[5].Name = "CopyOutputData"; 321 aSortDesc[5].Value = new Boolean(true); 322 aSortDesc[6] = new PropertyValue(); 323 aSortDesc[6].Name = "OutputPosition"; 324 aSortDesc[6].Value = copytoAddress; 325 326 // perform the sorting 327 XSortable xSort = (XSortable) UnoRuntime.queryInterface( 328 XSortable.class, sourceRange); 329 xSort.sort(aSortDesc); 330 331 // Verify the sorting result 332 String[][] expectResult = { { "Sun" }, { "SUN" }, { "Mon" }, { "Tue" }, 333 { "Wed" }, { "Thu" }, { "Fri" }, { "Sat" } }; 334 String[][] actureSortResult = SCUtil.getTextFromCellRange(currentsheet, 335 1, 0, 1, 7); 336 String[][] sourceAfterSort = SCUtil.getTextFromCellRange(currentsheet, 337 0, 0, 0, 7); 338 assertArrayEquals(source, sourceAfterSort); 339 assertArrayEquals(expectResult, actureSortResult); 340 341 // Save and reload the document verify the sort result again 342 SCUtil.saveFileAs(scComponent, "SortOption", "xls"); 343 XSpreadsheetDocument scDocumentTemp = SCUtil.reloadFile(unoApp, 344 scDocument, "SortOption.xls"); 345 scDocument = scDocumentTemp; 346 currentsheet = SCUtil.getCurrentSheet(scDocument); 347 actureSortResult = SCUtil 348 .getTextFromCellRange(currentsheet, 1, 0, 1, 7); 349 sourceAfterSort = SCUtil.getTextFromCellRange(currentsheet, 0, 0, 0, 7); 350 assertArrayEquals(source, sourceAfterSort); 351 assertArrayEquals(expectResult, actureSortResult); 352 } 353 } 354