OOoBasic/Calc/Solver

  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73

Sub solver_1
  ' find solver component implementation
  Dim oSolverComp As Object
  sSolverImplName = "com.sun.star.comp.Calc.Solver" ' default solver
  'implementation names for http://extensions.services.openoffice.org/project/NLPSolver
  'com.sun.star.comp.Calc.NLPSolver.SCOSolverImpl
  'com.sun.star.comp.Calc.NLPSolver.DEPSSolverImpl
  oEnume = GetProcessServiceManager().createContentEnumeration("com.sun.star.sheet.Solver")
  While oEnume.hasMoreElements()
    oObj = oEnume.nextElement()
    If oObj.getImplementationName() = sSolverImplName Then
      oSolverComp = oObj
    End If
  WEnd
  
  If IsNull(oSolverComp) Then
    msgbox "Could not find solver: " & sSolverImplName
    Exit Sub
  End If
  
  ' instantiate solver
  oSolver = CreateUnoService(sSolverImplName)
  ' unable to call createInstance method for solver's implemented by NLPSolver
  'oSolver = oSolverComp.createInstance() 
  mri oSolver
  oDoc = ThisComponent
  nOP = com.sun.star.sheet.SolverConstraintOperator
  With oSolver
    .Document = oDoc
    .Objective = makeCellAddress(0, 1, 6)
    .Variables = Array(makeCellAddress(0, 2, 3), makeCellAddress(0, 2, 4))
    .Constraints = Array( _
        makeSolverConstraint(makeCellAddress(0, 1, 6), nOP.LESS_EQUAL, 2500), _
        makeSolverConstraint(makeCellAddress(0, 1, 7), nOP.EQUAL, 8))
    .Maximize = True
  End With
  
  oSolver.solve()
  If oSolver.Success Then
    ' default solver does not set solutions to cells
    'nRes = oSolver.Solution
    'oSheet = oDoc.getSheets().getByIndex(0)
    'oSheet.getCellByPosition(2, 3).setValue(nRes(0))
    'oSheet.getCellByPosition(2, 4).setValue(nRes(1))
    
    'msgbox oSolver.ResultValue
  Else
    msgbox "no solution."
  End If
End Sub
 
Function makeCellAddress(nsheet As Integer, ncol As Long, nrow As Long) _
     As com.sun.star.table.CellAddress
  aCellAddr = CreateUnoStruct("com.sun.star.table.CellAddress")
  With aCellAddr
    .Sheet = nsheet
    .Column = ncol
    .Row = nrow
  End With
  makeCellAddress = aCellAddr
End Function
 
 
Function makeSolverConstraint( _
    aLeft As com.sun.star.table.CellAddress, nOp As Integer, vRight As Variant ) _
       As com.sun.star.sheet.SolverConstraint
  aSolverConstraint = CreateUnoStruct("com.sun.star.sheet.SolverConstraint")
  With aSolverConstraint
    .Left = aLeft
    .Operator = nOp
    .Right = vRight
  End With
  makeSolverConstraint = aSolverConstraint
End Function