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.common.Testspace; 31 import org.openoffice.test.uno.UnoApp; 32 import testlib.uno.SCUtil; 33 import testlib.uno.TestUtil; 34 import com.sun.star.beans.XPropertySet; 35 import com.sun.star.lang.XComponent; 36 import com.sun.star.sheet.FilterOperator; 37 import com.sun.star.sheet.TableFilterField; 38 import com.sun.star.sheet.XCellRangeData; 39 import com.sun.star.sheet.XSheetFilterDescriptor; 40 import com.sun.star.sheet.XSheetFilterable; 41 import com.sun.star.sheet.XSheetFilterableEx; 42 import com.sun.star.sheet.XSpreadsheet; 43 import com.sun.star.sheet.XSpreadsheetDocument; 44 import com.sun.star.table.XCellRange; 45 import com.sun.star.table.XColumnRowRange; 46 import com.sun.star.table.XTableRows; 47 import com.sun.star.uno.UnoRuntime; 48 49 public class AdvanceFilter { 50 private static final UnoApp app = new UnoApp(); 51 52 UnoApp unoApp = new UnoApp(); 53 XSpreadsheetDocument scDocument = null; 54 XComponent scComponent = null; 55 private String filename = "FilterTest.xls"; 56 57 @Before setUpDocument()58 public void setUpDocument() throws Exception { 59 unoApp.start(); 60 } 61 62 @After tearDownDocument()63 public void tearDownDocument() { 64 unoApp.close(); 65 unoApp.closeDocument(scComponent); 66 67 } 68 69 @BeforeClass setUpConnection()70 public static void setUpConnection() throws Exception { 71 72 } 73 74 @AfterClass tearDownConnection()75 public static void tearDownConnection() throws InterruptedException, Exception { 76 77 } 78 79 @Test testStandardFilterForString()80 public void testStandardFilterForString() throws Exception { 81 // Prepare test data 82 String sample = Testspace.prepareData(filename); 83 // Open document 84 scDocument = SCUtil.openFile(sample, unoApp); 85 // Get cell range 86 XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 87 XSpreadsheet currentsheet = SCUtil.getCurrentSheet(scDocument); 88 89 // Get the FilterCrit range and set the filter Critvalue 90 XCellRange FilterCritRange = currentsheet.getCellRangeByName("A15:F16"); 91 XCellRangeData FilterCritData = (XCellRangeData) UnoRuntime.queryInterface(XCellRangeData.class, FilterCritRange); 92 Object[][] aCritValues = { { "Name", "Age", "Weight", "Height", "Score", "Graduate" }, { "", "", ">= 44", "", "", "" } }; 93 FilterCritData.setDataArray(aCritValues); 94 95 // Filter the date 96 XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 97 98 XSheetFilterableEx xCriteria = (XSheetFilterableEx) UnoRuntime.queryInterface(XSheetFilterableEx.class, xdataRange.getCellRangeByName("A15:F16")); 99 XSheetFilterDescriptor xFilterDesc = xCriteria.createFilterDescriptorByObject(xFilter); 100 if (xFilterDesc != null) 101 xFilter.filter(xFilterDesc); 102 103 // Verify filter result 104 XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 105 XTableRows Rows = ColRowRange.getRows(); 106 for (int i = 0; i < Rows.getCount(); i++) { 107 Object aRowObj = Rows.getByIndex(i); 108 XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 109 if (i == 1 | i == 4) { 110 assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 111 } else 112 assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 113 } 114 115 // Save and reload the document 116 SCUtil.save(scDocument); 117 SCUtil.closeFile(scDocument); 118 scDocument = SCUtil.openFile(sample, unoApp); 119 120 // Verify the result agains 121 xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 122 ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 123 Rows = ColRowRange.getRows(); 124 for (int i = 0; i < Rows.getCount(); i++) { 125 Object aRowObj = Rows.getByIndex(i); 126 XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 127 if (i == 1 | i == 4) { 128 assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 129 } else 130 assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 131 } 132 133 } 134 } 135