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 22 23 package ifc.sheet; 24 25 import com.sun.star.container.XIndexAccess; 26 import com.sun.star.sheet.GoalResult; 27 import com.sun.star.sheet.XGoalSeek; 28 import com.sun.star.sheet.XSpreadsheet; 29 import com.sun.star.sheet.XSpreadsheetDocument; 30 import com.sun.star.sheet.XSpreadsheets; 31 import com.sun.star.table.CellAddress; 32 import com.sun.star.uno.UnoRuntime; 33 import lib.MultiMethodTest; 34 import lib.StatusException; 35 36 /** 37 * 38 */ 39 public class _XGoalSeek extends MultiMethodTest { 40 public XGoalSeek oObj = null; 41 XSpreadsheet xSheet = null; 42 CellAddress aFormula = null; 43 CellAddress aValue = null; 44 before()45 public void before() { 46 Exception ex = null; 47 // get two sheets 48 try { 49 XSpreadsheetDocument xSpreadsheetDocument = (XSpreadsheetDocument) 50 UnoRuntime.queryInterface(XSpreadsheetDocument.class, oObj); 51 XSpreadsheets oSheets = xSpreadsheetDocument.getSheets(); 52 XIndexAccess oIndexSheets = (XIndexAccess) UnoRuntime.queryInterface( 53 XIndexAccess.class, oSheets); 54 xSheet = (XSpreadsheet) UnoRuntime.queryInterface( 55 XSpreadsheet.class, oIndexSheets.getByIndex(1)); 56 } 57 catch(com.sun.star.lang.IndexOutOfBoundsException e) { 58 ex = e; 59 } 60 catch(com.sun.star.lang.WrappedTargetException e) { 61 ex = e; 62 } 63 catch(java.lang.NullPointerException e) { 64 ex = e; 65 } 66 if (ex != null) { 67 throw new StatusException("Could not get a sheet.", ex); 68 } 69 70 // set value and formula 71 try { 72 xSheet.getCellByPosition(3, 4).setValue(9); 73 xSheet.getCellByPosition(3, 5).setFormula("= SQRT(D5)"); 74 aValue = new CellAddress((short)1, 3, 4); 75 aFormula = new CellAddress((short)1, 3, 5); 76 } 77 catch(Exception e) { 78 throw new StatusException("Could not get set formulas on the sheet.", e); 79 } 80 } 81 _seekGoal()82 public void _seekGoal() { 83 boolean result = true; 84 double divergence = 0.01; 85 GoalResult goal = oObj.seekGoal(aFormula, aValue, "4"); 86 log.println("Goal Result: " + goal.Result + " Divergence: " + goal.Divergence); 87 result &= goal.Divergence < divergence; 88 result &= goal.Result > 16 - divergence || goal.Result < 16 + divergence; 89 90 goal = oObj.seekGoal(aFormula, aValue, "-4"); 91 log.println("Goal Result: " + goal.Result + " Divergence: " + goal.Divergence); 92 result &= goal.Divergence > 1/divergence; 93 result &= goal.Result < divergence || goal.Result > -divergence; 94 95 // just curious: let goal seek find a limiting value 96 try { 97 xSheet.getCellByPosition(3, 4).setValue(0.8); 98 xSheet.getCellByPosition(3, 5).setFormula("= (D5 ^ 2 - 1) / (D5 - 1)"); 99 } 100 catch(Exception e) {} 101 goal = oObj.seekGoal(aFormula, aValue, "2"); 102 log.println("Goal Result: " + goal.Result + " Divergence: " + goal.Divergence); 103 result &= goal.Divergence < divergence; 104 result &= goal.Result > 16 - divergence || goal.Result < 16 + divergence; 105 106 tRes.tested("seekGoal()", result); 107 } 108 } 109