qadevOOo/tests/java/ifc/sheet/_XGoalSeek.java

   1 /*
   2  * This file is part of the LibreOffice project.
   3  *
   4  * This Source Code Form is subject to the terms of the Mozilla Public
   5  * License, v. 2.0. If a copy of the MPL was not distributed with this
   6  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   7  *
   8  * This file incorporates work covered by the following license notice:
   9  *
  10  *   Licensed to the Apache Software Foundation (ASF) under one or more
  11  *   contributor license agreements. See the NOTICE file distributed
  12  *   with this work for additional information regarding copyright
  13  *   ownership. The ASF licenses this file to you under the Apache
  14  *   License, Version 2.0 (the "License"); you may not use this file
  15  *   except in compliance with the License. You may obtain a copy of
  16  *   the License at http://www.apache.org/licenses/LICENSE-2.0 .
  17  */
  18 package ifc.sheet;
  19
  20 import com.sun.star.container.XIndexAccess;
  21 import com.sun.star.sheet.GoalResult;
  22 import com.sun.star.sheet.XGoalSeek;
  23 import com.sun.star.sheet.XSpreadsheet;
  24 import com.sun.star.sheet.XSpreadsheetDocument;
  25 import com.sun.star.sheet.XSpreadsheets;
  26 import com.sun.star.table.CellAddress;
  27 import com.sun.star.uno.UnoRuntime;
  28 import lib.MultiMethodTest;
  29 import lib.StatusException;
  30
  31 /**
  32  *
  33  */
  34 public class _XGoalSeek extends MultiMethodTest {
  35     public XGoalSeek oObj = null;
  36     XSpreadsheet xSheet = null;
  37     CellAddress aFormula = null;
  38     CellAddress aValue = null;
  39
  40     @Override
  41     public void before() {
  42         Exception ex = null;
  43         // get two sheets
  44         try {
  45             XSpreadsheetDocument xSpreadsheetDocument = UnoRuntime.queryInterface(XSpreadsheetDocument.class, oObj);
  46             XSpreadsheets oSheets = xSpreadsheetDocument.getSheets();
  47             XIndexAccess oIndexSheets = UnoRuntime.queryInterface(
  48                                                 XIndexAccess.class, oSheets);
  49             xSheet = UnoRuntime.queryInterface(
  50                                       XSpreadsheet.class, oIndexSheets.getByIndex(1));
  51         }
  52         catch(com.sun.star.lang.IndexOutOfBoundsException e) {
  53             ex = e;
  54         }
  55         catch(com.sun.star.lang.WrappedTargetException e) {
  56             ex = e;
  57         }
  58         catch(NullPointerException e) {
  59             ex = e;
  60         }
  61         if (ex != null) {
  62             throw new StatusException("Could not get a sheet.", ex);
  63         }
  64
  65         // set value and formula
  66         try {
  67             xSheet.getCellByPosition(3, 4).setValue(9);
  68             xSheet.getCellByPosition(3, 5).setFormula("= SQRT(D5)");
  69             aValue = new CellAddress((short)1, 3, 4);
  70             aFormula = new CellAddress((short)1, 3, 5);
  71         }
  72         catch(Exception e) {
  73             throw new StatusException("Could not get set formulas on the sheet.", e);
  74         }
  75     }
  76
  77     public void _seekGoal() {
  78         boolean result = true;
  79         double divergence = 0.01;
  80         GoalResult goal = oObj.seekGoal(aFormula, aValue, "4");
  81         log.println("Goal Result: " + goal.Result + "   Divergence: " + goal.Divergence);
  82         result &= goal.Divergence < divergence;
  83         result &= goal.Result > 16 - divergence || goal.Result < 16 + divergence;
  84
  85         goal = oObj.seekGoal(aFormula, aValue, "-4");
  86         log.println("Goal Result: " + goal.Result + "   Divergence: " + goal.Divergence);
  87         result &= goal.Divergence > 1/divergence;
  88         result &= goal.Result < divergence || goal.Result > -divergence;
  89
  90         // just curious: let goal seek find a limiting value
  91         try {
  92             xSheet.getCellByPosition(3, 4).setValue(0.8);
  93             xSheet.getCellByPosition(3, 5).setFormula("= (D5 ^ 2 - 1) / (D5 - 1)");
  94         }
  95         catch(Exception e) {}
  96         goal = oObj.seekGoal(aFormula, aValue, "2");
  97         log.println("Goal Result: " + goal.Result + "   Divergence: " + goal.Divergence);
  98         result &= goal.Divergence < divergence;
  99         result &= goal.Result > 16 - divergence || goal.Result < 16 + divergence;
 100
 101         tRes.tested("seekGoal()", result);
 102     }
 103 }