198f42bb7SLiu Zhe /************************************************************** 298f42bb7SLiu Zhe * 398f42bb7SLiu Zhe * Licensed to the Apache Software Foundation (ASF) under one 498f42bb7SLiu Zhe * or more contributor license agreements. See the NOTICE file 598f42bb7SLiu Zhe * distributed with this work for additional information 698f42bb7SLiu Zhe * regarding copyright ownership. The ASF licenses this file 798f42bb7SLiu Zhe * to you under the Apache License, Version 2.0 (the 898f42bb7SLiu Zhe * "License"); you may not use this file except in compliance 998f42bb7SLiu Zhe * with the License. You may obtain a copy of the License at 1098f42bb7SLiu Zhe * 1198f42bb7SLiu Zhe * http://www.apache.org/licenses/LICENSE-2.0 1298f42bb7SLiu Zhe * 1398f42bb7SLiu Zhe * Unless required by applicable law or agreed to in writing, 1498f42bb7SLiu Zhe * software distributed under the License is distributed on an 1598f42bb7SLiu Zhe * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 1698f42bb7SLiu Zhe * KIND, either express or implied. See the License for the 1798f42bb7SLiu Zhe * specific language governing permissions and limitations 1898f42bb7SLiu Zhe * under the License. 1998f42bb7SLiu Zhe * 2098f42bb7SLiu Zhe *************************************************************/ 21eba4d44aSLiu Zhe package fvt.uno.sc.data; 2298f42bb7SLiu Zhe 2398f42bb7SLiu Zhe import static org.junit.Assert.*; 2498f42bb7SLiu Zhe 2598f42bb7SLiu Zhe import org.junit.After; 2698f42bb7SLiu Zhe import org.junit.AfterClass; 2798f42bb7SLiu Zhe import org.junit.Before; 2898f42bb7SLiu Zhe import org.junit.BeforeClass; 2998f42bb7SLiu Zhe import org.junit.Test; 3098f42bb7SLiu Zhe import org.openoffice.test.common.Testspace; 3198f42bb7SLiu Zhe import org.openoffice.test.uno.UnoApp; 3298f42bb7SLiu Zhe import testlib.uno.SCUtil; 3398f42bb7SLiu Zhe import com.sun.star.beans.XPropertySet; 3498f42bb7SLiu Zhe import com.sun.star.lang.XComponent; 3598f42bb7SLiu Zhe import com.sun.star.sheet.FilterOperator; 3698f42bb7SLiu Zhe import com.sun.star.sheet.TableFilterField; 3798f42bb7SLiu Zhe import com.sun.star.sheet.XCellAddressable; 3898f42bb7SLiu Zhe import com.sun.star.sheet.XSheetFilterDescriptor; 3998f42bb7SLiu Zhe import com.sun.star.sheet.XSheetFilterable; 4098f42bb7SLiu Zhe import com.sun.star.sheet.XSpreadsheet; 4198f42bb7SLiu Zhe import com.sun.star.sheet.XSpreadsheetDocument; 4298f42bb7SLiu Zhe import com.sun.star.table.CellAddress; 4398f42bb7SLiu Zhe import com.sun.star.table.XCell; 4498f42bb7SLiu Zhe import com.sun.star.table.XCellRange; 4598f42bb7SLiu Zhe import com.sun.star.table.XColumnRowRange; 4698f42bb7SLiu Zhe import com.sun.star.table.XTableRows; 4798f42bb7SLiu Zhe import com.sun.star.uno.UnoRuntime; 4898f42bb7SLiu Zhe 4998f42bb7SLiu Zhe 50cfe4bce3SLi Feng Wang public class StandardFilterOption { 5198f42bb7SLiu Zhe UnoApp unoApp = new UnoApp(); 5298f42bb7SLiu Zhe XSpreadsheetDocument scDocument = null; 5398f42bb7SLiu Zhe XComponent scComponent = null; 5498f42bb7SLiu Zhe private String filename = "FilterTest.xls"; 5598f42bb7SLiu Zhe 5698f42bb7SLiu Zhe @Before setUpDocument()5798f42bb7SLiu Zhe public void setUpDocument() throws Exception { 5898f42bb7SLiu Zhe unoApp.start(); 5998f42bb7SLiu Zhe } 6098f42bb7SLiu Zhe 6198f42bb7SLiu Zhe @After tearDownDocument()6298f42bb7SLiu Zhe public void tearDownDocument() { 6398f42bb7SLiu Zhe unoApp.close(); 6498f42bb7SLiu Zhe unoApp.closeDocument(scComponent); 6598f42bb7SLiu Zhe 6698f42bb7SLiu Zhe } 6798f42bb7SLiu Zhe 6898f42bb7SLiu Zhe @BeforeClass setUpConnection()6998f42bb7SLiu Zhe public static void setUpConnection() throws Exception { 7098f42bb7SLiu Zhe 7198f42bb7SLiu Zhe } 7298f42bb7SLiu Zhe 7398f42bb7SLiu Zhe @AfterClass tearDownConnection()74ba41a487SLiu Zhe public static void tearDownConnection() throws InterruptedException, Exception { 7598f42bb7SLiu Zhe 7698f42bb7SLiu Zhe } 7798f42bb7SLiu Zhe 78cfe4bce3SLi Feng Wang /** 79cfe4bce3SLi Feng Wang * test standard filter with string 80cfe4bce3SLi Feng Wang */ 8198f42bb7SLiu Zhe @Test testStandardFilterForString()8298f42bb7SLiu Zhe public void testStandardFilterForString() throws Exception { 8398f42bb7SLiu Zhe // Prepare test data 84ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 8598f42bb7SLiu Zhe // Open document 8698f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 8798f42bb7SLiu Zhe // Get cell range 88ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 8998f42bb7SLiu Zhe 9098f42bb7SLiu Zhe // Set filter property and filter the cell range 91ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 92ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 9398f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 9498f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 9598f42bb7SLiu Zhe aFilterFields[0].Field = 0; 9698f42bb7SLiu Zhe aFilterFields[0].IsNumeric = false; 9798f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.EQUAL; 9898f42bb7SLiu Zhe aFilterFields[0].StringValue = "Tom"; 9998f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 100ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 10198f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 10298f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 10398f42bb7SLiu Zhe 10498f42bb7SLiu Zhe // Verify filter result 105ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 10698f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 10798f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 10898f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 109ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 11098f42bb7SLiu Zhe if (i == 0 | i == 5) { 111ba41a487SLiu Zhe assertTrue("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 11298f42bb7SLiu Zhe } else 113ba41a487SLiu Zhe assertFalse("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 11498f42bb7SLiu Zhe } 11598f42bb7SLiu Zhe 11698f42bb7SLiu Zhe // Save and reload the document 11798f42bb7SLiu Zhe SCUtil.save(scDocument); 11898f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 11998f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 12098f42bb7SLiu Zhe 121cfe4bce3SLi Feng Wang // Verify the result again 122ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 123ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 12498f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 12598f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 12698f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 127ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 12898f42bb7SLiu Zhe if (i == 0 | i == 5) { 129ba41a487SLiu Zhe assertTrue("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 13098f42bb7SLiu Zhe } else 131ba41a487SLiu Zhe assertFalse("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 13298f42bb7SLiu Zhe } 13398f42bb7SLiu Zhe 13498f42bb7SLiu Zhe } 13598f42bb7SLiu Zhe 136cfe4bce3SLi Feng Wang /** 137cfe4bce3SLi Feng Wang * test standard filter with case sensitive options 138cfe4bce3SLi Feng Wang */ 13998f42bb7SLiu Zhe @Test testStandardFilterOptionCaseSensitive()14098f42bb7SLiu Zhe public void testStandardFilterOptionCaseSensitive() throws Exception { 14198f42bb7SLiu Zhe // Prepare test data 142ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 14398f42bb7SLiu Zhe // Open document 14498f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 14598f42bb7SLiu Zhe // Get cell range 146ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 14798f42bb7SLiu Zhe 14898f42bb7SLiu Zhe // Set filter property and filter the cell range 149ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 150ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 15198f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 15298f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 15398f42bb7SLiu Zhe aFilterFields[0].Field = 5; 15498f42bb7SLiu Zhe aFilterFields[0].IsNumeric = false; 15598f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.EQUAL; 15698f42bb7SLiu Zhe aFilterFields[0].StringValue = "No"; 15798f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 158ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 15998f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 16098f42bb7SLiu Zhe xFilterProp.setPropertyValue("IsCaseSensitive", false); 16198f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 16298f42bb7SLiu Zhe 16398f42bb7SLiu Zhe // Verify filter result 164ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 16598f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 16698f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 16798f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 168ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 169ba41a487SLiu Zhe if (i == 0 | i == 1 | i == 5) { 170ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 17198f42bb7SLiu Zhe } else 172ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 17398f42bb7SLiu Zhe } 174ba41a487SLiu Zhe 175*7f5c89d5SJohn Bampton // Change to CaseSensitive 17698f42bb7SLiu Zhe xFilterProp.setPropertyValue("IsCaseSensitive", true); 17798f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 178ba41a487SLiu Zhe 179ba41a487SLiu Zhe // Verify result 18098f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 18198f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 182ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 183ba41a487SLiu Zhe if (i == 0 | i == 5) { 184ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 18598f42bb7SLiu Zhe } else 186ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 18798f42bb7SLiu Zhe } 188ba41a487SLiu Zhe 18998f42bb7SLiu Zhe // Save and reload the document 19098f42bb7SLiu Zhe SCUtil.save(scDocument); 19198f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 19298f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 19398f42bb7SLiu Zhe 19498f42bb7SLiu Zhe // Verify the result again 195ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 196ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 19798f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 19898f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 19998f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 200ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 201ba41a487SLiu Zhe if (i == 0 | i == 5) { 202ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 20398f42bb7SLiu Zhe } else 204ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 20598f42bb7SLiu Zhe } 20698f42bb7SLiu Zhe } 20798f42bb7SLiu Zhe 208cfe4bce3SLi Feng Wang /** 209cfe4bce3SLi Feng Wang * test standard filter with contain header options 210cfe4bce3SLi Feng Wang */ 21198f42bb7SLiu Zhe @Test testStandardFilterOptionContainsHeader()21298f42bb7SLiu Zhe public void testStandardFilterOptionContainsHeader() throws Exception { 21398f42bb7SLiu Zhe // Prepare test data 214ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 21598f42bb7SLiu Zhe // Open document 21698f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 21798f42bb7SLiu Zhe // Get cell range 218ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 21998f42bb7SLiu Zhe 22098f42bb7SLiu Zhe // Set filter property and filter the cell range 221ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 222ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 22398f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 22498f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 22598f42bb7SLiu Zhe aFilterFields[0].Field = 2; 22698f42bb7SLiu Zhe aFilterFields[0].IsNumeric = true; 22798f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.LESS; 22898f42bb7SLiu Zhe aFilterFields[0].NumericValue = 44; 22998f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 230ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 23198f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 23298f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 23398f42bb7SLiu Zhe 23498f42bb7SLiu Zhe // Verify filter result 235ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 23698f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 23798f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 23898f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 239ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 24098f42bb7SLiu Zhe if (i == 0 | i == 1 | i == 4) { 241ba41a487SLiu Zhe assertTrue("Expect result should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 24298f42bb7SLiu Zhe } else 243ba41a487SLiu Zhe assertFalse("Expect result should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 24498f42bb7SLiu Zhe } 245ba41a487SLiu Zhe 246ba41a487SLiu Zhe // Change to not contain header 24798f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(false)); 24898f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 249ba41a487SLiu Zhe 250ba41a487SLiu Zhe // Verify result 25198f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 25298f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 253ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 25498f42bb7SLiu Zhe if (i == 1 | i == 4) { 255ba41a487SLiu Zhe assertTrue("Expect result should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 25698f42bb7SLiu Zhe } else 257ba41a487SLiu Zhe assertFalse("Expect result should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 25898f42bb7SLiu Zhe } 259ba41a487SLiu Zhe 26098f42bb7SLiu Zhe // Save the document 26198f42bb7SLiu Zhe SCUtil.save(scDocument); 26298f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 26398f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 26498f42bb7SLiu Zhe 26598f42bb7SLiu Zhe // Verify filter result again 266ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 267ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 26898f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 26998f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 27098f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 271ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 27298f42bb7SLiu Zhe if (i == 1 | i == 4) { 273ba41a487SLiu Zhe assertTrue("Expect result should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 27498f42bb7SLiu Zhe } else 275ba41a487SLiu Zhe assertFalse("Expect result should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 27698f42bb7SLiu Zhe } 27798f42bb7SLiu Zhe 27898f42bb7SLiu Zhe } 27998f42bb7SLiu Zhe 280cfe4bce3SLi Feng Wang /** 281cfe4bce3SLi Feng Wang * test standard filter with copy out put after filter in options 282cfe4bce3SLi Feng Wang */ 28398f42bb7SLiu Zhe @Test testStandardFilterOptionCopyOutput()28498f42bb7SLiu Zhe public void testStandardFilterOptionCopyOutput() throws Exception { 28598f42bb7SLiu Zhe // Prepare test data 286ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 28798f42bb7SLiu Zhe // Open document 28898f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 28998f42bb7SLiu Zhe // Get cell range 290ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 29198f42bb7SLiu Zhe XSpreadsheet currentSheet = SCUtil.getCurrentSheet(scDocument); 292ba41a487SLiu Zhe // Get the value before filter 293ba41a487SLiu Zhe String[][] souce = SCUtil.getTextFromCellRange(currentSheet, 0, 0, 5, 5); 294ba41a487SLiu Zhe 295cfe4bce3SLi Feng Wang // Copy to cell position 29698f42bb7SLiu Zhe XCell cell = currentSheet.getCellByPosition(7, 7); 297ba41a487SLiu Zhe XCellAddressable xCellAddr = (XCellAddressable) UnoRuntime.queryInterface(XCellAddressable.class, cell); 298ba41a487SLiu Zhe CellAddress copytoAddress = xCellAddr.getCellAddress(); 299ba41a487SLiu Zhe 30098f42bb7SLiu Zhe // Set filter property and filter the cell range 301ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 302ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 30398f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 30498f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 30598f42bb7SLiu Zhe aFilterFields[0].Field = 3; 30698f42bb7SLiu Zhe aFilterFields[0].IsNumeric = true; 30798f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.GREATER; 30898f42bb7SLiu Zhe aFilterFields[0].NumericValue = 155; 30998f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 310ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 31198f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 312ba41a487SLiu Zhe xFilterProp.setPropertyValue("CopyOutputData", new Boolean(true)); 313ba41a487SLiu Zhe xFilterProp.setPropertyValue("OutputPosition", copytoAddress); 31498f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 31598f42bb7SLiu Zhe 31698f42bb7SLiu Zhe // Verify source range not changed 317ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 31898f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 31998f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 32098f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 321ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 322ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 32398f42bb7SLiu Zhe } 324ba41a487SLiu Zhe 325ba41a487SLiu Zhe // Get the data after filter 32698f42bb7SLiu Zhe String[][] dataafterFilter = SCUtil.getTextFromCellRange(currentSheet, 0, 0, 5, 5); 327ba41a487SLiu Zhe assertArrayEquals(souce, dataafterFilter); 32898f42bb7SLiu Zhe 329ba41a487SLiu Zhe // Get the copyto filter result, verify it 330ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("H8:M10")); 33198f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 33298f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 333ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 334ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 33598f42bb7SLiu Zhe } 336ba41a487SLiu Zhe // Verify the first filter line data 337ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 0, 5, 0), SCUtil.getTextFromCellRange(currentSheet, 7, 7, 12, 7)); 338ba41a487SLiu Zhe 339ba41a487SLiu Zhe // Verify the Second filter line data 340ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 1, 5, 1), SCUtil.getTextFromCellRange(currentSheet, 7, 8, 12, 8)); 341ba41a487SLiu Zhe 342ba41a487SLiu Zhe // Verify the Last filter line data 343ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 4, 5, 4), SCUtil.getTextFromCellRange(currentSheet, 7, 9, 12, 9)); 344ba41a487SLiu Zhe 34598f42bb7SLiu Zhe // Save the document 34698f42bb7SLiu Zhe SCUtil.save(scDocument); 34798f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 34898f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 34998f42bb7SLiu Zhe 35098f42bb7SLiu Zhe // Verify filter result again 351ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 352ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 35398f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 35498f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 35598f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 356ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 357ba41a487SLiu Zhe assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 35898f42bb7SLiu Zhe } 359ba41a487SLiu Zhe 360ba41a487SLiu Zhe // Get the data after filter 36198f42bb7SLiu Zhe currentSheet = SCUtil.getCurrentSheet(scDocument); 36298f42bb7SLiu Zhe dataafterFilter = SCUtil.getTextFromCellRange(currentSheet, 0, 0, 5, 5); 363ba41a487SLiu Zhe assertArrayEquals(souce, dataafterFilter); 364ba41a487SLiu Zhe 365ba41a487SLiu Zhe // Get the copyto filter result, verify it 366ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("H8:M10")); 36798f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 36898f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 369ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 370ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 37198f42bb7SLiu Zhe } 372ba41a487SLiu Zhe // Verify the first filter line data 373ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 0, 5, 0), SCUtil.getTextFromCellRange(currentSheet, 7, 7, 12, 7)); 374ba41a487SLiu Zhe 375ba41a487SLiu Zhe // Verify the Second filter line data 376ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 1, 5, 1), SCUtil.getTextFromCellRange(currentSheet, 7, 8, 12, 8)); 377ba41a487SLiu Zhe 378ba41a487SLiu Zhe // Verify the Last filter line data 379ba41a487SLiu Zhe assertArrayEquals(SCUtil.getTextFromCellRange(currentSheet, 0, 4, 5, 4), SCUtil.getTextFromCellRange(currentSheet, 7, 9, 12, 9)); 38098f42bb7SLiu Zhe } 381ba41a487SLiu Zhe 382cfe4bce3SLi Feng Wang /** 383cfe4bce3SLi Feng Wang * test standard filter with skip duplicates in options 384cfe4bce3SLi Feng Wang */ 38598f42bb7SLiu Zhe @Test testStandardFilterOptionSkipDuplicates()38698f42bb7SLiu Zhe public void testStandardFilterOptionSkipDuplicates() throws Exception { 38798f42bb7SLiu Zhe // Prepare test data 388ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 38998f42bb7SLiu Zhe // Open document 39098f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 39198f42bb7SLiu Zhe // Get cell range 392ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 39398f42bb7SLiu Zhe 39498f42bb7SLiu Zhe // Set filter property and filter the cell range 395ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:E6")); 396ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 39798f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 39898f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 39998f42bb7SLiu Zhe aFilterFields[0].Field = 3; 40098f42bb7SLiu Zhe aFilterFields[0].IsNumeric = true; 40198f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.GREATER_EQUAL; 40298f42bb7SLiu Zhe aFilterFields[0].NumericValue = 155; 40398f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 404ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 40598f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 40698f42bb7SLiu Zhe xFilterProp.setPropertyValue("SkipDuplicates", new Boolean(true)); 40798f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 40898f42bb7SLiu Zhe 40998f42bb7SLiu Zhe // Verify filter result 410ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 41198f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 41298f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 41398f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 414ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 41598f42bb7SLiu Zhe if (i == 2) { 416ba41a487SLiu Zhe assertFalse("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 41798f42bb7SLiu Zhe } else 418ba41a487SLiu Zhe assertTrue("Verify row is invisible.", (Boolean) PropSet.getPropertyValue("IsVisible")); 41998f42bb7SLiu Zhe } 420ba41a487SLiu Zhe 421*7f5c89d5SJohn Bampton // Change to skip Duplicates 42298f42bb7SLiu Zhe xFilterProp.setPropertyValue("SkipDuplicates", new Boolean(false)); 42398f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 424ba41a487SLiu Zhe 42598f42bb7SLiu Zhe // Verify filter result 426ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 427ba41a487SLiu Zhe Rows = ColRowRange.getRows(); 428ba41a487SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 429ba41a487SLiu Zhe Object aRowObj = Rows.getByIndex(i); 430ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 431ba41a487SLiu Zhe if (i == 2 | i == 6) { 432ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 433ba41a487SLiu Zhe } else 434ba41a487SLiu Zhe assertTrue("Expect should be True", (Boolean) PropSet.getPropertyValue("IsVisible")); 435ba41a487SLiu Zhe } 436ba41a487SLiu Zhe 43798f42bb7SLiu Zhe // Save the document 43898f42bb7SLiu Zhe SCUtil.save(scDocument); 43998f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 44098f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 44198f42bb7SLiu Zhe 44298f42bb7SLiu Zhe // Verify filter result again 443ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 444ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:E6")); 44598f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 44698f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount() - 1; i++) { 44798f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 448ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 449ba41a487SLiu Zhe if (i == 2 | i == 6) { 450ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 45198f42bb7SLiu Zhe } else 452ba41a487SLiu Zhe assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 45398f42bb7SLiu Zhe } 45498f42bb7SLiu Zhe 45598f42bb7SLiu Zhe } 456ba41a487SLiu Zhe 457cfe4bce3SLi Feng Wang /** 458cfe4bce3SLi Feng Wang * test standard filter with regular expressions in options 459cfe4bce3SLi Feng Wang */ 46098f42bb7SLiu Zhe @Test testStandardFilterOptionUseRegularExpressions()46198f42bb7SLiu Zhe public void testStandardFilterOptionUseRegularExpressions() throws Exception { 46298f42bb7SLiu Zhe // Prepare test data 463ba41a487SLiu Zhe String sample = Testspace.prepareData(filename); 46498f42bb7SLiu Zhe // Open document 46598f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 46698f42bb7SLiu Zhe // Get cell range 467ba41a487SLiu Zhe XCellRange xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 46898f42bb7SLiu Zhe 46998f42bb7SLiu Zhe // Set filter property and filter the cell range 470ba41a487SLiu Zhe XSheetFilterable xFilter = (XSheetFilterable) UnoRuntime.queryInterface(XSheetFilterable.class, xdataRange.getCellRangeByName("A1:F6")); 471ba41a487SLiu Zhe XSheetFilterDescriptor xFilterDesc = xFilter.createFilterDescriptor(true); 47298f42bb7SLiu Zhe TableFilterField[] aFilterFields = new TableFilterField[1]; 47398f42bb7SLiu Zhe aFilterFields[0] = new TableFilterField(); 47498f42bb7SLiu Zhe aFilterFields[0].Field = 0; 47598f42bb7SLiu Zhe aFilterFields[0].IsNumeric = false; 47698f42bb7SLiu Zhe aFilterFields[0].Operator = FilterOperator.EQUAL; 47798f42bb7SLiu Zhe aFilterFields[0].StringValue = "^.{3}$"; 47898f42bb7SLiu Zhe xFilterDesc.setFilterFields(aFilterFields); 479ba41a487SLiu Zhe XPropertySet xFilterProp = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, xFilterDesc); 48098f42bb7SLiu Zhe xFilterProp.setPropertyValue("ContainsHeader", new Boolean(true)); 48198f42bb7SLiu Zhe xFilterProp.setPropertyValue("UseRegularExpressions", new Boolean(true)); 48298f42bb7SLiu Zhe xFilter.filter(xFilterDesc); 483ba41a487SLiu Zhe 48498f42bb7SLiu Zhe // Verify filter result 485ba41a487SLiu Zhe XColumnRowRange ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 48698f42bb7SLiu Zhe XTableRows Rows = ColRowRange.getRows(); 48798f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 48898f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 489ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 490ba41a487SLiu Zhe if (i == 2 | i == 4) { 491ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 49298f42bb7SLiu Zhe } else 493ba41a487SLiu Zhe assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 49498f42bb7SLiu Zhe } 49598f42bb7SLiu Zhe 49698f42bb7SLiu Zhe // Save the document 49798f42bb7SLiu Zhe SCUtil.save(scDocument); 49898f42bb7SLiu Zhe SCUtil.closeFile(scDocument); 49998f42bb7SLiu Zhe scDocument = SCUtil.openFile(sample, unoApp); 50098f42bb7SLiu Zhe 50198f42bb7SLiu Zhe // Verify filter result again 502ba41a487SLiu Zhe xdataRange = (XCellRange) UnoRuntime.queryInterface(XCellRange.class, SCUtil.getCurrentSheet(scDocument)); 503ba41a487SLiu Zhe ColRowRange = (XColumnRowRange) UnoRuntime.queryInterface(XColumnRowRange.class, xdataRange.getCellRangeByName("A1:F6")); 50498f42bb7SLiu Zhe Rows = ColRowRange.getRows(); 50598f42bb7SLiu Zhe for (int i = 0; i < Rows.getCount(); i++) { 50698f42bb7SLiu Zhe Object aRowObj = Rows.getByIndex(i); 507ba41a487SLiu Zhe XPropertySet PropSet = (XPropertySet) UnoRuntime.queryInterface(XPropertySet.class, aRowObj); 508ba41a487SLiu Zhe if (i == 2 | i == 4) { 509ba41a487SLiu Zhe assertFalse("Expect should be false", (Boolean) PropSet.getPropertyValue("IsVisible")); 51098f42bb7SLiu Zhe } else 511ba41a487SLiu Zhe assertTrue("Expect should be true", (Boolean) PropSet.getPropertyValue("IsVisible")); 51298f42bb7SLiu Zhe } 51398f42bb7SLiu Zhe 51498f42bb7SLiu Zhe } 51598f42bb7SLiu Zhe 51698f42bb7SLiu Zhe } 517