Apache OpenOffice Community Forum

Now that the solver is starting to look good, is there anybody that would not mind sharing an oobasic code snippet to run it from code.
I have a set of minimisations that I need to iterate through, and setting up the solver manually for each is a pain really.
As I'm a newbie, I'm not really sure how to use the info in the (rather scetchy) documentation...
http://api.openoffice.org/docs/common/r ... olver.html

Many thanks in advance

/O.

The documentation looks wrong. Interfaces don't have attibutes. The attributes belong to service c.s.s.sheet.Solver, I guess.
According to the introspection interface of a spreadsheet document there is no way to access this service. It is not among the supported services nor is it availlable. There is no ThisComponent.Solver nor ThisComponent.createInstance("com.sun.star.sheet.Solver").

Edit: It's implemented like an add-on. Thus you get it from the global service manager

Thank you.
And then what?
I'm sorry if this sounds like my asking someone to do my job, but I'm not. I simply don't get it.
I can do all the I/O and iterative bits easily, but I still don't understand how to set up the solver from code and then get it to run.
Please help.

/O.

I built a trivial example to show how code might look. I avoided loops to show every step explicitly. The problem I used is to have the formula =A1+B1-C1 in cell D1. I want to maximize D1 with the constraints that A1 must be <= 6, B1 must be <= 8 and C1 must be >= 4. Take a look at this in conjunction with the documentation for Interface XSolver, Struct SolverConstraint, and Enum SolverConstraintOperator. Thanks to Villeroy for showing how to get the Solver service.

Brilliant.

Is it possible to run in silent mode, i.e. NOT have to clik OK in the solver progress dialog?

Thank you very much, and a happy easter to everyone!

/O.

Hmm, I surprised you get a dialog. If I run the solver by calling the macro from Tools -> Macros -> Run Macro, I don't get any dialog. I can set the macro to step through a series of variable cells and objective cells without any pause. Can you explain the steps you take the produce a dialog?

The minimisations that I'm doing are non-linear, so I downloaded the NLPSolver.oxt add-in (which works fine). However, if I deactivate that add-in, no progress dialog shows up, so I suppose the add-in is the culprit here... On the other hand, when I deactivate the add-in the solution will not converge given the problem I want to solve
Is there possibly another well-functioning non-linear add-in which runs in silent mode?

BR /O.

Not sure but it may be linked to the version you're using. If you're using the Ubuntu version (from repository), you should try the official (Sun) version: [Ubuntu] Installing OOo on Debian and Co. NB: if you had a 2.x version, you should reset your OOo user profile but don't transfer your personal data during the welcome process, configuration files from the former version might corrupt the new profile.

I'm running OpenOffice 3.0.1. on Ubuntu Intrepid.
Unfortunately, the included solver is linear only and thus not usable to solve the financial engineering problem I'm solving.
Hell, I want to be able to use OpenOffice for my calc needs, and not have to resort to Excel all the time...

Olle E wrote:I'm running OpenOffice 3.0.1. on Ubuntu Intrepid.

But from the repositories or from the OOo web site?

Eeer. I'm not really following the vocabulary here I'm afraid.
This is where I got my current version of OpenOffice: http://ppa.launchpad.net/openoffice-pkgs/ubuntu

Then try the official version (from Sun). See the link I've given in previous page.

I now found the solution to my problem. Of course it was right in front of my nose the whole time, I just didn't see it.
Here's a link with in-depth documentation and a simple code example.
http://wiki.services.openoffice.org/wik ... Parameters

Thank you all for your help, and Kudos to Sun and others for (finally) addressing this. The solver really is a key component to a spreadsheet.
Now, if only they would fix...

FJCC wrote:I built a trivial example to show how code might look. I avoided loops to show every step explicitly. The problem I used is to have the formula =A1+B1-C1 in cell D1. I want to maximize D1 with the constraints that A1 must be <= 6, B1 must be <= 8 and C1 must be >= 4. Take a look at this in conjunction with the documentation for Interface XSolver, Struct SolverConstraint, and Enum SolverConstraintOperator. Thanks to Villeroy for showing how to get the Solver service.

Code: Select all

Dim Variables(2) as Object
Dim Constraint_0 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_1 as New com.sun.star.sheet.SolverConstraint
Dim Constraint_2 as New com.sun.star.sheet.SolverConstraint
Dim Constraints(2) as Object

smgr = GetProcessServiceManager()
solv = smgr.createInstance("com.sun.star.sheet.Solver")

ConstrOperator1 = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
ConstrOperator2 = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL

oDoc = ThisComponent
solv.Document = oDoc

Sheet = oDoc.Sheets(0)

ObjectiveCell = Sheet.getCellByPosition(3,0) 'This cell has the formula =A1 +B1 - C1
solv.Objective = ObjectiveCell.CellAddress

VariCell0 = Sheet.getCellByPosition(0,0) 
Variables(0) = VariCell0.CellAddress

VariCell1 = Sheet.getCellByPosition(1,0)
Variables(1) = VariCell1.CellAddress

VariCell2 = Sheet.getCellByPosition(2,0)
Variables(2) = VariCell2.CellAddress

solv.Variables = Variables()
	
Constraint_0.Left = Sheet.getCellByPosition(0,0).CellAddress
Constraint_0.Operator = ConstrOperator1
Constraint_0.Right = 6
Constraints(0) = Constraint_0

Constraint_1.Left = Sheet.getCellByPosition(1,0).CellAddress
Constraint_1.Operator = ConstrOperator1
Constraint_1.Right = 8
Constraints(1) = Constraint_1

Constraint_2.Left = Sheet.getCellByPosition(2,0).CellAddress
Constraint_2.Operator = ConstrOperator2
Constraint_2.Right = 4
Constraints(2) = Constraint_2
	
solv.Constraints = Constraints()

solv.Maximize = True
solv.Solve()

Print solv.ResultValue

Hello FJCC

I never take a look into OOo Basic and I don't understand how to modify this code form my proposal.

Using your formula =A1+B1-C1 in cell D1, I want that the result are equal to E1 and the cell when the solver must write the result is G1.
Cells are in the second sheet called "Formula"

How I can modify the code you post before?

Thank you very much and sorry if I am too noob

I have problem with this example in line: -> solv.Objective = ObjectiveCell.CellAddress

Error de ejecución de BASIC.
Se ha producido una excepción
Type: com.sun.star.uno.RuntimeException
Message: [jni_uno bridge error] UNO calling Java method Objective: [map_to_uno():com.sun.star.table.CellAddress] null-ref given!
java stack trace:
.

It doesn't work in LibreOffice 4.4.7 but yes with v5 or OpenOffice. Does anyone know why?