sc/source/ui/unoobj/solveruno.cxx

   1 /* -*- Mode: C++; tab-width: 4; indent-tabs-mode: nil; c-basic-offset: 4 -*- */
   2 /*
   3  * This file is part of the LibreOffice project.
   4  *
   5  * This Source Code Form is subject to the terms of the Mozilla Public
   6  * License, v. 2.0. If a copy of the MPL was not distributed with this
   7  * file, You can obtain one at http://mozilla.org/MPL/2.0/.
   8  *
   9  */
  10
  11 #include <rtl/math.hxx>
  12 #include <vcl/svapp.hxx>
  13 #include <solveruno.hxx>
  14 #include <docsh.hxx>
  15 #include <docfunc.hxx>
  16 #include <address.hxx>
  17 #include <table.hxx>
  18 #include <convuno.hxx>
  19 #include <compiler.hxx>
  20 #include <solverutil.hxx>
  21 #include <rangeutl.hxx>
  22 #include <scresid.hxx>
  23 #include <globstr.hrc>
  24 #include <optsolver.hxx>
  25 #include <unonames.hxx>
  26 #include <SolverSettings.hxx>
  27 #include <o3tl/string_view.hxx>
  28 #include <cppuhelper/supportsservice.hxx>
  29 #include <comphelper/sequence.hxx>
  30 #include <com/sun/star/sheet/XSolver.hpp>
  31 #include <com/sun/star/sheet/XSolverDescription.hpp>
  32
  33 using namespace css;
  34
  35 constexpr OUString SC_SOLVERSETTINGS_SERVICE = u"com.sun.star.sheet.SolverSettings"_ustr;
  36
  37 namespace
  38 {
  39 // Returns the sc::ConstraintOperator equivalent to the Uno operator
  40 sc::ConstraintOperator getScOperatorFromUno(sheet::SolverConstraintOperator aOperator)
  41 {
  42     sc::ConstraintOperator aRet(sc::ConstraintOperator::CO_LESS_EQUAL);
  43
  44     switch (aOperator)
  45     {
  46         case sheet::SolverConstraintOperator_EQUAL:
  47             aRet = sc::ConstraintOperator::CO_EQUAL;
  48             break;
  49         case sheet::SolverConstraintOperator_GREATER_EQUAL:
  50             aRet = sc::ConstraintOperator::CO_GREATER_EQUAL;
  51             break;
  52         case sheet::SolverConstraintOperator_BINARY:
  53             aRet = sc::ConstraintOperator::CO_BINARY;
  54             break;
  55         case sheet::SolverConstraintOperator_INTEGER:
  56             aRet = sc::ConstraintOperator::CO_INTEGER;
  57             break;
  58         default:
  59         {
  60             // This should never be reached
  61         }
  62     }
  63     return aRet;
  64 }
  65
  66 // Returns the sheet::SolverConstraintOperator equivalent to sc::ConstraintOperator
  67 sheet::SolverConstraintOperator getUnoOperatorFromSc(sc::ConstraintOperator nOperator)
  68 {
  69     sheet::SolverConstraintOperator aRet(sheet::SolverConstraintOperator_LESS_EQUAL);
  70
  71     switch (nOperator)
  72     {
  73         case sc::ConstraintOperator::CO_EQUAL:
  74             aRet = sheet::SolverConstraintOperator_EQUAL;
  75             break;
  76         case sc::ConstraintOperator::CO_GREATER_EQUAL:
  77             aRet = sheet::SolverConstraintOperator_GREATER_EQUAL;
  78             break;
  79         case sc::ConstraintOperator::CO_BINARY:
  80             aRet = sheet::SolverConstraintOperator_BINARY;
  81             break;
  82         case sc::ConstraintOperator::CO_INTEGER:
  83             aRet = sheet::SolverConstraintOperator_INTEGER;
  84             break;
  85         default:
  86         {
  87             // This should never be reached
  88         }
  89     }
  90     return aRet;
  91 }
  92
  93 // Returns the CellRangeAddress struct from a ScRange
  94 table::CellRangeAddress getRangeAddress(ScRange aRange)
  95 {
  96     table::CellRangeAddress aRet;
  97     aRet.Sheet = aRange.aStart.Tab();
  98     aRet.StartColumn = aRange.aStart.Col();
  99     aRet.StartRow = aRange.aStart.Row();
 100     aRet.EndColumn = aRange.aEnd.Col();
 101     aRet.EndRow = aRange.aEnd.Row();
 102     return aRet;
 103 }
 104
 105 // Tests if a string is a valid number
 106 bool isValidNumber(const OUString& sValue, double& fValue)
 107 {
 108     if (sValue.isEmpty())
 109         return false;
 110
 111     rtl_math_ConversionStatus eConvStatus;
 112     sal_Int32 nEnd;
 113     fValue = rtl::math::stringToDouble(sValue, ScGlobal::getLocaleData().getNumDecimalSep()[0],
 114                                        ScGlobal::getLocaleData().getNumThousandSep()[0],
 115                                        &eConvStatus, &nEnd);
 116     // A conversion is only valid if nEnd is equal to the string length (all chars processed)
 117     return nEnd == sValue.getLength();
 118 }
 119 }
 120
 121 ScSolverSettings::ScSolverSettings(ScDocShell* pDocSh, uno::Reference<container::XNamed> xSheet)
 122     : m_pDocShell(pDocSh)
 123     , m_rDoc(m_pDocShell->GetDocument())
 124     , m_xSheet(std::move(xSheet))
 125     , m_nStatus(sheet::SolverStatus::NONE)
 126     , m_bSuppressDialog(false)
 127     , m_pTable(nullptr)
 128 {
 129     // Initialize member variables with information about the current sheet
 130     OUString aName = m_xSheet->getName();
 131     SCTAB nTab;
 132     if (m_rDoc.GetTable(aName, nTab))
 133     {
 134         m_pTable = m_rDoc.FetchTable(nTab);
 135         m_pSettings = m_pTable->GetSolverSettings();
 136     }
 137 }
 138
 139 ScSolverSettings::~ScSolverSettings() {}
 140
 141 bool ScSolverSettings::ParseRef(ScRange& rRange, const OUString& rInput, bool bAllowRange)
 142 {
 143     ScAddress::Details aDetails(m_rDoc.GetAddressConvention(), 0, 0);
 144     ScRefFlags nFlags = rRange.ParseAny(rInput, m_rDoc, aDetails);
 145     SCTAB nCurTab(m_pTable->GetTab());
 146     if (nFlags & ScRefFlags::VALID)
 147     {
 148         if ((nFlags & ScRefFlags::TAB_3D) == ScRefFlags::ZERO)
 149             rRange.aStart.SetTab(nCurTab);
 150         if ((nFlags & ScRefFlags::TAB2_3D) == ScRefFlags::ZERO)
 151             rRange.aEnd.SetTab(rRange.aStart.Tab());
 152         return (bAllowRange || rRange.aStart == rRange.aEnd);
 153     }
 154     else if (ScRangeUtil::MakeRangeFromName(rInput, m_rDoc, nCurTab, rRange, RUTL_NAMES, aDetails))
 155         return (bAllowRange || rRange.aStart == rRange.aEnd);
 156
 157     return false;
 158 }
 159
 160 bool ScSolverSettings::ParseWithNames(ScRangeList& rRanges, std::u16string_view rInput)
 161 {
 162     if (rInput.empty())
 163         return true;
 164
 165     ScAddress::Details aDetails(m_rDoc.GetAddressConvention(), 0, 0);
 166     SCTAB nCurTab(m_pTable->GetTab());
 167     sal_Unicode cDelimiter = ScCompiler::GetNativeSymbolChar(OpCode::ocSep);
 168     bool bError = false;
 169     sal_Int32 nIdx(0);
 170     do
 171     {
 172         ScRange aRange;
 173         OUString aRangeStr(o3tl::getToken(rInput, 0, cDelimiter, nIdx));
 174         ScRefFlags nFlags = aRange.ParseAny(aRangeStr, m_rDoc, aDetails);
 175         if (nFlags & ScRefFlags::VALID)
 176         {
 177             if ((nFlags & ScRefFlags::TAB_3D) == ScRefFlags::ZERO)
 178                 aRange.aStart.SetTab(nCurTab);
 179             if ((nFlags & ScRefFlags::TAB2_3D) == ScRefFlags::ZERO)
 180                 aRange.aEnd.SetTab(aRange.aStart.Tab());
 181             rRanges.push_back(aRange);
 182         }
 183         else if (ScRangeUtil::MakeRangeFromName(aRangeStr, m_rDoc, nCurTab, aRange, RUTL_NAMES,
 184                                                 aDetails))
 185             rRanges.push_back(aRange);
 186         else
 187             bError = true;
 188     } while (nIdx > 0);
 189
 190     return !bError;
 191 }
 192
 193 void ScSolverSettings::ShowErrorMessage(const OUString& rMessage)
 194 {
 195     std::unique_ptr<weld::MessageDialog> xBox(Application::CreateMessageDialog(
 196         Application::GetDefDialogParent(), VclMessageType::Warning, VclButtonsType::Ok, rMessage));
 197     xBox->run();
 198 }
 199
 200 // XSolverSettings
 201 sal_Int8 SAL_CALL ScSolverSettings::getObjectiveType()
 202 {
 203     sal_Int8 aRet(sheet::SolverObjectiveType::MAXIMIZE);
 204     switch (m_pSettings->GetObjectiveType())
 205     {
 206         case sc::ObjectiveType::OT_MINIMIZE:
 207             aRet = sheet::SolverObjectiveType::MINIMIZE;
 208             break;
 209         case sc::ObjectiveType::OT_VALUE:
 210             aRet = sheet::SolverObjectiveType::VALUE;
 211             break;
 212         default:
 213         {
 214             // This should never be reached
 215         }
 216     }
 217     return aRet;
 218 }
 219
 220 void SAL_CALL ScSolverSettings::setObjectiveType(sal_Int8 aObjType)
 221 {
 222     sc::ObjectiveType eType(sc::ObjectiveType::OT_MAXIMIZE);
 223     switch (aObjType)
 224     {
 225         case sheet::SolverObjectiveType::MINIMIZE:
 226             eType = sc::ObjectiveType::OT_MINIMIZE;
 227             break;
 228         case sheet::SolverObjectiveType::VALUE:
 229             eType = sc::ObjectiveType::OT_VALUE;
 230             break;
 231         default:
 232         {
 233             // This should never be reached
 234         }
 235     }
 236     m_pSettings->SetObjectiveType(eType);
 237 }
 238
 239 uno::Any SAL_CALL ScSolverSettings::getObjectiveCell()
 240 {
 241     // The objective cell must be a valid cell address
 242     OUString sValue(m_pSettings->GetParameter(sc::SolverParameter::SP_OBJ_CELL));
 243
 244     // Test if it is a valid cell reference; if so, return its CellAddress
 245     ScRange aRange;
 246     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 247     bool bOk = (aRange.ParseAny(sValue, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 248     if (bOk)
 249     {
 250         SCTAB nTab1, nTab2;
 251         SCROW nRow1, nRow2;
 252         SCCOL nCol1, nCol2;
 253         aRange.GetVars(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
 254         table::CellAddress aAddress(nTab1, nCol1, nRow1);
 255         return uno::Any(aAddress);
 256     }
 257
 258     // If converting to a CellAddress fails, returns the raw string
 259     return uno::Any(sValue);
 260 }
 261
 262 // The value being set must be either a string referencing a single cell or
 263 // a CellAddress instance
 264 void SAL_CALL ScSolverSettings::setObjectiveCell(const uno::Any& aValue)
 265 {
 266     // Check if a string value is being used
 267     OUString sValue;
 268     bool bIsString(aValue >>= sValue);
 269     if (bIsString)
 270     {
 271         // The string must correspond to a valid range; if not, an empty string is set
 272         ScRange aRange;
 273         OUString sRet;
 274         ScDocument& rDoc = m_pDocShell->GetDocument();
 275         const formula::FormulaGrammar::AddressConvention eConv = rDoc.GetAddressConvention();
 276         bool bOk = (aRange.ParseAny(sValue, rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 277         if (bOk)
 278         {
 279             SCTAB nTab1, nTab2;
 280             SCROW nRow1, nRow2;
 281             SCCOL nCol1, nCol2;
 282             aRange.GetVars(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
 283             // The range must consist of a single cell
 284             if (nTab1 == nTab2 && nCol1 == nCol2 && nRow1 == nRow2)
 285                 sRet = sValue;
 286         }
 287         m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_CELL, sRet);
 288         return;
 289     }
 290
 291     // Check if a CellAddress is being used
 292     table::CellAddress aUnoAddress;
 293     bool bIsAddress(aValue >>= aUnoAddress);
 294     if (bIsAddress)
 295     {
 296         OUString sRet;
 297         ScAddress aAdress(aUnoAddress.Column, aUnoAddress.Row, aUnoAddress.Sheet);
 298         sRet = aAdress.Format(ScRefFlags::RANGE_ABS, &m_rDoc);
 299         m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_CELL, sRet);
 300         return;
 301     }
 302
 303     // If all fails, set an empty string
 304     m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_CELL, "");
 305 }
 306
 307 uno::Any SAL_CALL ScSolverSettings::getGoalValue()
 308 {
 309     OUString sValue(m_pSettings->GetParameter(sc::SolverParameter::SP_OBJ_VAL));
 310
 311     // Test if it is a valid cell reference; if so, return its CellAddress
 312     ScRange aRange;
 313     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 314     bool bOk = (aRange.ParseAny(sValue, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 315     if (bOk)
 316     {
 317         SCTAB nTab1, nTab2;
 318         SCROW nRow1, nRow2;
 319         SCCOL nCol1, nCol2;
 320         aRange.GetVars(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
 321         table::CellAddress aAddress(nTab1, nCol1, nRow1);
 322         return uno::Any(aAddress);
 323     }
 324
 325     double fValue;
 326     bool bValid = isValidNumber(sValue, fValue);
 327     if (bValid)
 328         return uno::Any(fValue);
 329
 330     // If the conversion was not successful, return "empty"
 331     return uno::Any();
 332 }
 333
 334 void SAL_CALL ScSolverSettings::setGoalValue(const uno::Any& aValue)
 335 {
 336     // Check if a numeric value is being used
 337     double fValue;
 338     bool bIsDouble(aValue >>= fValue);
 339     if (bIsDouble)
 340     {
 341         // The value must be set as a localized number
 342         OUString sLocalizedValue = rtl::math::doubleToUString(
 343             fValue, rtl_math_StringFormat_Automatic, rtl_math_DecimalPlaces_Max,
 344             ScGlobal::getLocaleData().getNumDecimalSep()[0], true);
 345         m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_VAL, sLocalizedValue);
 346         return;
 347     }
 348
 349     // Check if a string value is being used
 350     OUString sValue;
 351     bool bIsString(aValue >>= sValue);
 352     if (bIsString)
 353     {
 354         // The string must correspond to a valid range; if not, an empty string is set
 355         ScRange aRange;
 356         OUString sRet;
 357         ScDocument& rDoc = m_pDocShell->GetDocument();
 358         const formula::FormulaGrammar::AddressConvention eConv = rDoc.GetAddressConvention();
 359         bool bOk = (aRange.ParseAny(sValue, rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 360         if (bOk)
 361         {
 362             SCTAB nTab1, nTab2;
 363             SCROW nRow1, nRow2;
 364             SCCOL nCol1, nCol2;
 365             aRange.GetVars(nCol1, nRow1, nTab1, nCol2, nRow2, nTab2);
 366             // The range must consist of a single cell
 367             if (nTab1 == nTab2 && nCol1 == nCol2 && nRow1 == nRow2)
 368                 sRet = sValue;
 369         }
 370         m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_VAL, sRet);
 371         return;
 372     }
 373
 374     // Check if a CellAddress is being used
 375     table::CellAddress aUnoAddress;
 376     bool bIsAddress(aValue >>= aUnoAddress);
 377     if (bIsAddress)
 378     {
 379         OUString sRet;
 380         ScAddress aAdress(aUnoAddress.Column, aUnoAddress.Row, aUnoAddress.Sheet);
 381         sRet = aAdress.Format(ScRefFlags::RANGE_ABS, &m_rDoc);
 382         m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_VAL, sRet);
 383         return;
 384     }
 385
 386     // If all fails, set an empty string
 387     m_pSettings->SetParameter(sc::SolverParameter::SP_OBJ_VAL, "");
 388 }
 389
 390 OUString SAL_CALL ScSolverSettings::getEngine()
 391 {
 392     return m_pSettings->GetParameter(sc::SP_LO_ENGINE);
 393 }
 394
 395 void SAL_CALL ScSolverSettings::setEngine(const OUString& sEngine)
 396 {
 397     // Only change the engine if the new engine exists; otherwise leave it unchanged
 398     uno::Sequence<OUString> arrEngineNames;
 399     uno::Sequence<OUString> arrDescriptions;
 400     ScSolverUtil::GetImplementations(arrEngineNames, arrDescriptions);
 401     if (comphelper::findValue(arrEngineNames, sEngine) == -1)
 402         return;
 403
 404     m_pSettings->SetParameter(sc::SP_LO_ENGINE, sEngine);
 405 }
 406
 407 uno::Sequence<OUString> SAL_CALL ScSolverSettings::getAvailableEngines()
 408 {
 409     uno::Sequence<OUString> arrEngineNames;
 410     uno::Sequence<OUString> arrDescriptions;
 411     ScSolverUtil::GetImplementations(arrEngineNames, arrDescriptions);
 412     return arrEngineNames;
 413 }
 414
 415 uno::Sequence<uno::Any> SAL_CALL ScSolverSettings::getVariableCells()
 416 {
 417     // Variable cells parameter is stored as a single string composed of valid ranges
 418     // separated using the formula separator character
 419     OUString sVarCells(m_pSettings->GetParameter(sc::SP_VAR_CELLS));
 420     // Delimiter character to separate ranges
 421     sal_Unicode cDelimiter = ScCompiler::GetNativeSymbolChar(OpCode::ocSep);
 422     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 423     uno::Sequence<uno::Any> aRangeSeq;
 424     sal_Int32 nIdx(0);
 425     sal_Int32 nArrPos(0);
 426
 427     do
 428     {
 429         OUString aRangeStr(o3tl::getToken(sVarCells, 0, cDelimiter, nIdx));
 430         // Check if range is valid
 431         ScRange aRange;
 432         bool bOk
 433             = (aRange.ParseAny(aRangeStr, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 434         if (bOk)
 435         {
 436             table::CellRangeAddress aRangeAddress(getRangeAddress(aRange));
 437             aRangeSeq.realloc(nArrPos + 1);
 438             auto pArrRanges = aRangeSeq.getArray();
 439             pArrRanges[nArrPos] <<= aRangeAddress;
 440             nArrPos++;
 441         }
 442     } while (nIdx > 0);
 443
 444     return aRangeSeq;
 445 }
 446
 447 void SAL_CALL ScSolverSettings::setVariableCells(const uno::Sequence<uno::Any>& aRanges)
 448 {
 449     OUString sVarCells;
 450     bool bFirst(true);
 451     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 452     OUStringChar cDelimiter(ScCompiler::GetNativeSymbolChar(OpCode::ocSep));
 453
 454     for (const auto& rRange : aRanges)
 455     {
 456         OUString sRange;
 457         bool bIsString(rRange >>= sRange);
 458         bool bOk(false);
 459         if (bIsString)
 460         {
 461             ScRange aRange;
 462             bOk = (aRange.ParseAny(sRange, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 463         }
 464
 465         table::CellRangeAddress aRangeAddress;
 466         bool bIsRangeAddress(rRange >>= aRangeAddress);
 467         if (bIsRangeAddress)
 468         {
 469             bOk = true;
 470             ScRange aRange(aRangeAddress.StartColumn, aRangeAddress.StartRow, aRangeAddress.Sheet,
 471                            aRangeAddress.EndColumn, aRangeAddress.EndRow, aRangeAddress.Sheet);
 472             sRange = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
 473         }
 474
 475         if (bOk)
 476         {
 477             if (bFirst)
 478             {
 479                 sVarCells = sRange;
 480                 bFirst = false;
 481             }
 482             else
 483             {
 484                 sVarCells += cDelimiter + sRange;
 485             }
 486         }
 487     }
 488
 489     m_pSettings->SetParameter(sc::SP_VAR_CELLS, sVarCells);
 490 }
 491
 492 uno::Sequence<sheet::ModelConstraint> SAL_CALL ScSolverSettings::getConstraints()
 493 {
 494     uno::Sequence<sheet::ModelConstraint> aRet;
 495     std::vector<sc::ModelConstraint> vConstraints = m_pSettings->GetConstraints();
 496     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 497     sal_Int32 nCount(0);
 498
 499     for (const auto& rConst : vConstraints)
 500     {
 501         sheet::ModelConstraint aConstraint;
 502
 503         // Left side: must be valid string representing a cell range
 504         ScRange aLeftRange;
 505         bool bIsLeftRange
 506             = (aLeftRange.ParseAny(rConst.aLeftStr, m_rDoc, eConv) & ScRefFlags::VALID)
 507               == ScRefFlags::VALID;
 508         if (bIsLeftRange)
 509             aConstraint.Left <<= getRangeAddress(aLeftRange);
 510
 511         // Operator
 512         aConstraint.Operator = getUnoOperatorFromSc(rConst.nOperator);
 513
 514         // Right side: must be either
 515         // - valid string representing a cell range or
 516         // - a numeric value
 517         ScRange aRightRange;
 518         bool bIsRightRange
 519             = (aRightRange.ParseAny(rConst.aRightStr, m_rDoc, eConv) & ScRefFlags::VALID)
 520               == ScRefFlags::VALID;
 521         if (bIsRightRange)
 522         {
 523             aConstraint.Right <<= getRangeAddress(aRightRange);
 524         }
 525         else
 526         {
 527             double fValue;
 528             bool bValid = isValidNumber(rConst.aRightStr, fValue);
 529             if (bValid)
 530                 aConstraint.Right <<= fValue;
 531             else
 532                 aConstraint.Right = uno::Any();
 533         }
 534
 535         // Adds the constraint to the sequence
 536         aRet.realloc(nCount + 1);
 537         auto pArrConstraints = aRet.getArray();
 538         pArrConstraints[nCount] = std::move(aConstraint);
 539         nCount++;
 540     }
 541
 542     return aRet;
 543 }
 544
 545 void SAL_CALL
 546 ScSolverSettings::setConstraints(const uno::Sequence<sheet::ModelConstraint>& aConstraints)
 547 {
 548     const formula::FormulaGrammar::AddressConvention eConv = m_rDoc.GetAddressConvention();
 549     std::vector<sc::ModelConstraint> vRetConstraints;
 550
 551     for (const auto& rConst : aConstraints)
 552     {
 553         sc::ModelConstraint aNewConst;
 554
 555         // Left side
 556         OUString sLeft;
 557         bool bOkLeft(false);
 558         bool bIsString(rConst.Left >>= sLeft);
 559         if (bIsString)
 560         {
 561             ScRange aRange;
 562             bOkLeft
 563                 = (aRange.ParseAny(sLeft, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 564         }
 565
 566         table::CellRangeAddress aLeftRangeAddress;
 567         bool bIsRangeAddress(rConst.Left >>= aLeftRangeAddress);
 568         if (bIsRangeAddress)
 569         {
 570             bOkLeft = true;
 571             ScRange aRange(aLeftRangeAddress.StartColumn, aLeftRangeAddress.StartRow,
 572                            aLeftRangeAddress.Sheet, aLeftRangeAddress.EndColumn,
 573                            aLeftRangeAddress.EndRow, aLeftRangeAddress.Sheet);
 574             sLeft = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
 575         }
 576
 577         if (bOkLeft)
 578             aNewConst.aLeftStr = sLeft;
 579
 580         // Constraint operator
 581         aNewConst.nOperator = getScOperatorFromUno(rConst.Operator);
 582
 583         // Right side (may have numeric values)
 584         OUString sRight;
 585         bool bOkRight(false);
 586
 587         double fValue;
 588         bool bIsDouble(rConst.Right >>= fValue);
 589         if (bIsDouble)
 590         {
 591             bOkRight = true;
 592             // The value must be set as a localized number
 593             sRight = rtl::math::doubleToUString(
 594                 fValue, rtl_math_StringFormat_Automatic, rtl_math_DecimalPlaces_Max,
 595                 ScGlobal::getLocaleData().getNumDecimalSep()[0], true);
 596         }
 597
 598         bIsString = (rConst.Right >>= sRight);
 599         if (bIsString)
 600         {
 601             ScRange aRange;
 602             bOkRight
 603                 = (aRange.ParseAny(sRight, m_rDoc, eConv) & ScRefFlags::VALID) == ScRefFlags::VALID;
 604         }
 605
 606         table::CellRangeAddress aRightRangeAddress;
 607         bIsRangeAddress = (rConst.Right >>= aRightRangeAddress);
 608         if (bIsRangeAddress)
 609         {
 610             bOkRight = true;
 611             ScRange aRange(aRightRangeAddress.StartColumn, aRightRangeAddress.StartRow,
 612                            aRightRangeAddress.Sheet, aRightRangeAddress.EndColumn,
 613                            aRightRangeAddress.EndRow, aRightRangeAddress.Sheet);
 614             sRight = aRange.Format(m_rDoc, ScRefFlags::RANGE_ABS);
 615         }
 616
 617         if (bOkRight)
 618             aNewConst.aRightStr = sRight;
 619
 620         vRetConstraints.push_back(aNewConst);
 621     }
 622
 623     m_pSettings->SetConstraints(std::move(vRetConstraints));
 624 }
 625
 626 sal_Int32 SAL_CALL ScSolverSettings::getConstraintCount()
 627 {
 628     if (!m_pTable)
 629         return -1;
 630
 631     return static_cast<sal_Int32>(m_pSettings->GetConstraints().size());
 632 }
 633
 634 uno::Sequence<beans::PropertyValue> SAL_CALL ScSolverSettings::getEngineOptions()
 635 {
 636     uno::Sequence<beans::PropertyValue> aRet = ScSolverUtil::GetDefaults(getEngine());
 637     m_pSettings->GetEngineOptions(aRet);
 638     return aRet;
 639 }
 640
 641 void SAL_CALL ScSolverSettings::setEngineOptions(const uno::Sequence<beans::PropertyValue>& rProps)
 642 {
 643     m_pSettings->SetEngineOptions(rProps);
 644 }
 645
 646 sal_Int8 SAL_CALL ScSolverSettings::getStatus() { return m_nStatus; }
 647
 648 OUString SAL_CALL ScSolverSettings::getErrorMessage() { return m_sErrorMessage; }
 649
 650 sal_Bool SAL_CALL ScSolverSettings::getSuppressDialog() { return m_bSuppressDialog; }
 651
 652 void SAL_CALL ScSolverSettings::setSuppressDialog(sal_Bool bSuppress)
 653 {
 654     m_bSuppressDialog = bSuppress;
 655 }
 656
 657 void SAL_CALL ScSolverSettings::reset() { m_pSettings->ResetToDefaults(); }
 658
 659 void SAL_CALL ScSolverSettings::solve()
 660 {
 661     // Show the progress dialog
 662     auto xProgress = std::make_shared<ScSolverProgressDialog>(Application::GetDefDialogParent());
 663     if (!m_bSuppressDialog)
 664     {
 665         // Get the value of the timeout property of the solver engine
 666         uno::Sequence<beans::PropertyValue> aProps(getEngineOptions());
 667         sal_Int32 nTimeout(0);
 668         sal_Int32 nPropCount(aProps.getLength());
 669         bool bHasTimeout(false);
 670         for (sal_Int32 nProp = 0; nProp < nPropCount && !bHasTimeout; ++nProp)
 671         {
 672             const beans::PropertyValue& rValue = aProps[nProp];
 673             if (rValue.Name == SC_UNONAME_TIMEOUT)
 674                 bHasTimeout = (rValue.Value >>= nTimeout);
 675         }
 676
 677         if (bHasTimeout)
 678             xProgress->SetTimeLimit(nTimeout);
 679         else
 680             xProgress->HideTimeLimit();
 681
 682         weld::DialogController::runAsync(xProgress, [](sal_Int32 /*nResult*/) {});
 683         // try to make sure the progress dialog is painted before continuing
 684         Application::Reschedule(true);
 685     }
 686
 687     // Check the validity of the objective cell
 688     ScRange aObjRange;
 689     if (!ParseRef(aObjRange, m_pSettings->GetParameter(sc::SP_OBJ_CELL), false))
 690     {
 691         m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 692         m_sErrorMessage = ScResId(STR_SOLVER_OBJCELL_FAIL);
 693         if (!m_bSuppressDialog)
 694             ScSolverSettings::ShowErrorMessage(m_sErrorMessage);
 695         return;
 696     }
 697     table::CellAddress aObjCell(aObjRange.aStart.Tab(), aObjRange.aStart.Col(),
 698                                 aObjRange.aStart.Row());
 699
 700     // Check the validity of the variable cells
 701     ScRangeList aVarRanges;
 702     if (!ParseWithNames(aVarRanges, m_pSettings->GetParameter(sc::SP_VAR_CELLS)))
 703     {
 704         m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 705         m_sErrorMessage = ScResId(STR_SOLVER_VARCELL_FAIL);
 706         if (!m_bSuppressDialog)
 707             ScSolverSettings::ShowErrorMessage(m_sErrorMessage);
 708         return;
 709     }
 710
 711     // Resolve ranges into single cells
 712     uno::Sequence<table::CellAddress> aVariableCells;
 713     sal_Int32 nVarPos(0);
 714     for (size_t nRangePos = 0, nRange = aVarRanges.size(); nRangePos < nRange; ++nRangePos)
 715     {
 716         ScRange aRange(aVarRanges[nRangePos]);
 717         aRange.PutInOrder();
 718         SCTAB nTab = aRange.aStart.Tab();
 719
 720         sal_Int32 nAdd = (aRange.aEnd.Col() - aRange.aStart.Col() + 1)
 721                          * (aRange.aEnd.Row() - aRange.aStart.Row() + 1);
 722         aVariableCells.realloc(nVarPos + nAdd);
 723         auto pVariables = aVariableCells.getArray();
 724
 725         for (SCROW nRow = aRange.aStart.Row(); nRow <= aRange.aEnd.Row(); ++nRow)
 726             for (SCCOL nCol = aRange.aStart.Col(); nCol <= aRange.aEnd.Col(); ++nCol)
 727                 pVariables[nVarPos++] = table::CellAddress(nTab, nCol, nRow);
 728     }
 729
 730     // Prepare model constraints
 731     uno::Sequence<sheet::SolverConstraint> aConstraints;
 732     sal_Int32 nConstrPos = 0;
 733     for (const auto& rConstr : m_pSettings->GetConstraints())
 734     {
 735         if (!rConstr.aLeftStr.isEmpty())
 736         {
 737             sheet::SolverConstraint aConstraint;
 738             aConstraint.Operator = getUnoOperatorFromSc(rConstr.nOperator);
 739
 740             // The left side of the constraint must be a valid range or a single cell
 741             ScRange aLeftRange;
 742             if (!ParseRef(aLeftRange, rConstr.aLeftStr, true))
 743             {
 744                 m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 745                 m_sErrorMessage = ScResId(STR_INVALIDCONDITION);
 746                 if (!m_bSuppressDialog)
 747                     ScSolverSettings::ShowErrorMessage(m_sErrorMessage);
 748                 return;
 749             }
 750
 751             // The right side can be either a cell range, a single cell or a numeric value
 752             bool bIsRange(false);
 753             ScRange aRightRange;
 754             if (ParseRef(aRightRange, rConstr.aRightStr, true))
 755             {
 756                 if (aRightRange.aStart == aRightRange.aEnd)
 757                     aConstraint.Right
 758                         <<= table::CellAddress(aRightRange.aStart.Tab(), aRightRange.aStart.Col(),
 759                                                aRightRange.aStart.Row());
 760
 761                 else if (aRightRange.aEnd.Col() - aRightRange.aStart.Col()
 762                              == aLeftRange.aEnd.Col() - aLeftRange.aStart.Col()
 763                          && aRightRange.aEnd.Row() - aRightRange.aStart.Row()
 764                                 == aLeftRange.aEnd.Row() - aLeftRange.aStart.Row())
 765                     // If the right side of the constraint is a range, it must have the
 766                     // same shape as the left side
 767                     bIsRange = true;
 768                 else
 769                 {
 770                     m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 771                     m_sErrorMessage = ScResId(STR_INVALIDCONDITION);
 772                     if (!m_bSuppressDialog)
 773                         ScSolverSettings::ShowErrorMessage(m_sErrorMessage);
 774
 775                     return;
 776                 }
 777             }
 778             else
 779             {
 780                 // Test if the right side is a numeric value
 781                 sal_uInt32 nFormat = 0;
 782                 double fValue(0);
 783                 if (m_rDoc.GetFormatTable()->IsNumberFormat(rConstr.aRightStr, nFormat, fValue))
 784                     aConstraint.Right <<= fValue;
 785                 else if (aConstraint.Operator != sheet::SolverConstraintOperator_INTEGER
 786                          && aConstraint.Operator != sheet::SolverConstraintOperator_BINARY)
 787                 {
 788                     m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 789                     m_sErrorMessage = ScResId(STR_INVALIDCONDITION);
 790                     if (!m_bSuppressDialog)
 791                         ScSolverSettings::ShowErrorMessage(ScResId(STR_INVALIDCONDITION));
 792                     return;
 793                 }
 794             }
 795
 796             // Resolve constraint into single cells
 797             sal_Int32 nAdd = (aLeftRange.aEnd.Col() - aLeftRange.aStart.Col() + 1)
 798                              * (aLeftRange.aEnd.Row() - aLeftRange.aStart.Row() + 1);
 799             aConstraints.realloc(nConstrPos + nAdd);
 800             auto pConstraints = aConstraints.getArray();
 801
 802             for (SCROW nRow = aLeftRange.aStart.Row(); nRow <= aLeftRange.aEnd.Row(); ++nRow)
 803                 for (SCCOL nCol = aLeftRange.aStart.Col(); nCol <= aLeftRange.aEnd.Col(); ++nCol)
 804                 {
 805                     aConstraint.Left = table::CellAddress(aLeftRange.aStart.Tab(), nCol, nRow);
 806                     if (bIsRange)
 807                         aConstraint.Right <<= table::CellAddress(
 808                             aRightRange.aStart.Tab(),
 809                             aRightRange.aStart.Col() + (nCol - aLeftRange.aStart.Col()),
 810                             aRightRange.aStart.Row() + (nRow - aLeftRange.aStart.Row()));
 811                     pConstraints[nConstrPos++] = aConstraint;
 812                 }
 813         }
 814     }
 815
 816     // Type of the objective function
 817     // If the objective is of type VALUE then a minimization model is used
 818     sc::ObjectiveType aObjType(m_pSettings->GetObjectiveType());
 819     bool bMaximize = aObjType == sc::ObjectiveType::OT_MAXIMIZE;
 820
 821     if (aObjType == sc::ObjectiveType::OT_VALUE)
 822     {
 823         // An additional constraint is added to the model forcing
 824         // the objective cell to be equal to a given value
 825         sheet::SolverConstraint aConstraint;
 826         aConstraint.Left = aObjCell;
 827         aConstraint.Operator = sheet::SolverConstraintOperator_EQUAL;
 828
 829         OUString aValStr = m_pSettings->GetParameter(sc::SP_OBJ_VAL);
 830         ScRange aRightRange;
 831
 832         if (ParseRef(aRightRange, aValStr, false))
 833             aConstraint.Right <<= table::CellAddress(
 834                 aRightRange.aStart.Tab(), aRightRange.aStart.Col(), aRightRange.aStart.Row());
 835         else
 836         {
 837             // Test if the right side is a numeric value
 838             sal_uInt32 nFormat = 0;
 839             double fValue(0);
 840             if (m_rDoc.GetFormatTable()->IsNumberFormat(aValStr, nFormat, fValue))
 841                 aConstraint.Right <<= fValue;
 842             else
 843             {
 844                 m_nStatus = sheet::SolverStatus::PARSE_ERROR;
 845                 m_sErrorMessage = ScResId(STR_SOLVER_TARGETVALUE_FAIL);
 846                 if (!m_bSuppressDialog)
 847                     ScSolverSettings::ShowErrorMessage(m_sErrorMessage);
 848                 return;
 849             }
 850         }
 851
 852         aConstraints.realloc(nConstrPos + 1);
 853         aConstraints.getArray()[nConstrPos++] = std::move(aConstraint);
 854     }
 855
 856     // Create a copy of document values in case the user chooses to restore them
 857     sal_Int32 nVarCount = aVariableCells.getLength();
 858     uno::Sequence<double> aOldValues(nVarCount);
 859     std::transform(std::cbegin(aVariableCells), std::cend(aVariableCells), aOldValues.getArray(),
 860                    [this](const table::CellAddress& rVariable) -> double {
 861                        ScAddress aCellPos;
 862                        ScUnoConversion::FillScAddress(aCellPos, rVariable);
 863                        return m_rDoc.GetValue(aCellPos);
 864                    });
 865
 866     // Create and initialize solver
 867     uno::Reference<sheet::XSolver> xSolver = ScSolverUtil::GetSolver(getEngine());
 868     OSL_ENSURE(xSolver.is(), "Unable to get solver component");
 869     if (!xSolver.is())
 870     {
 871         if (!m_bSuppressDialog)
 872             ScSolverSettings::ShowErrorMessage(ScResId(STR_INVALIDINPUT));
 873         m_nStatus = sheet::SolverStatus::ENGINE_ERROR;
 874         m_sErrorMessage = ScResId(STR_SOLVER_LOAD_FAIL);
 875         return;
 876     }
 877
 878     rtl::Reference<ScModelObj> xDocument(m_pDocShell->GetModel());
 879     xSolver->setDocument(xDocument);
 880     xSolver->setObjective(aObjCell);
 881     xSolver->setVariables(aVariableCells);
 882     xSolver->setConstraints(aConstraints);
 883     xSolver->setMaximize(bMaximize);
 884
 885     // Set engine options
 886     uno::Reference<beans::XPropertySet> xOptProp(xSolver, uno::UNO_QUERY);
 887     if (xOptProp.is())
 888     {
 889         for (const beans::PropertyValue& rValue : getEngineOptions())
 890         {
 891             try
 892             {
 893                 xOptProp->setPropertyValue(rValue.Name, rValue.Value);
 894             }
 895             catch (uno::Exception&)
 896             {
 897                 OSL_FAIL("Unable to set solver option property");
 898             }
 899         }
 900     }
 901
 902     xSolver->solve();
 903     bool bSuccess = xSolver->getSuccess();
 904
 905     // Close progress dialog
 906     if (!m_bSuppressDialog && xProgress)
 907         xProgress->response(RET_CLOSE);
 908
 909     if (bSuccess)
 910     {
 911         m_nStatus = sheet::SolverStatus::SOLUTION_FOUND;
 912         // Write solution to the document
 913         uno::Sequence<double> aSolution = xSolver->getSolution();
 914         if (aSolution.getLength() == nVarCount)
 915         {
 916             m_pDocShell->LockPaint();
 917             ScDocFunc& rFunc = m_pDocShell->GetDocFunc();
 918             for (nVarPos = 0; nVarPos < nVarCount; ++nVarPos)
 919             {
 920                 ScAddress aCellPos;
 921                 ScUnoConversion::FillScAddress(aCellPos, aVariableCells[nVarPos]);
 922                 rFunc.SetValueCell(aCellPos, aSolution[nVarPos], false);
 923             }
 924             m_pDocShell->UnlockPaint();
 925         }
 926         else
 927         {
 928             OSL_FAIL("Wrong number of variables in the solver solution");
 929         }
 930
 931         // Show success dialog
 932         if (!m_bSuppressDialog)
 933         {
 934             // Get formatted result from document to show in the Success dialog
 935             OUString aResultStr = m_rDoc.GetString(static_cast<SCCOL>(aObjCell.Column),
 936                                                    static_cast<SCROW>(aObjCell.Row),
 937                                                    static_cast<SCTAB>(aObjCell.Sheet));
 938
 939             ScSolverSuccessDialog xSuccessDialog(Application::GetDefDialogParent(), aResultStr);
 940             bool bRestore(true);
 941             if (xSuccessDialog.run() == RET_OK)
 942                 // Keep results in the document
 943                 bRestore = false;
 944
 945             if (bRestore)
 946             {
 947                 // Restore values to the document
 948                 m_pDocShell->LockPaint();
 949                 ScDocFunc& rFunc = m_pDocShell->GetDocFunc();
 950                 for (nVarPos = 0; nVarPos < nVarCount; ++nVarPos)
 951                 {
 952                     ScAddress aCellPos;
 953                     ScUnoConversion::FillScAddress(aCellPos, aVariableCells[nVarPos]);
 954                     rFunc.SetValueCell(aCellPos, aOldValues[nVarPos], false);
 955                 }
 956                 m_pDocShell->UnlockPaint();
 957             }
 958         }
 959     }
 960     else
 961     {
 962         // The solver failed to find a solution
 963         m_nStatus = sheet::SolverStatus::SOLUTION_NOT_FOUND;
 964         uno::Reference<sheet::XSolverDescription> xDesc(xSolver, uno::UNO_QUERY);
 965         // Get error message reported by the solver
 966         if (xDesc.is())
 967             m_sErrorMessage = xDesc->getStatusDescription();
 968         if (!m_bSuppressDialog)
 969         {
 970             ScSolverNoSolutionDialog aDialog(Application::GetDefDialogParent(), m_sErrorMessage);
 971             aDialog.run();
 972         }
 973     }
 974 }
 975
 976 void SAL_CALL ScSolverSettings::saveToFile() { m_pSettings->SaveSolverSettings(); }
 977
 978 // XServiceInfo
 979 OUString SAL_CALL ScSolverSettings::getImplementationName() { return u"ScSolverSettings"_ustr; }
 980
 981 sal_Bool SAL_CALL ScSolverSettings::supportsService(const OUString& rServiceName)
 982 {
 983     return cppu::supportsService(this, rServiceName);
 984 }
 985
 986 uno::Sequence<OUString> SAL_CALL ScSolverSettings::getSupportedServiceNames()
 987 {
 988     return { SC_SOLVERSETTINGS_SERVICE };
 989 }