[Solved] Solver Macro

[Olle E]

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.

[Villeroy]

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

Code: Select all

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

[Olle E]

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.

[FJCC]

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

[Olle E]

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.

[FJCC]

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?

[Olle E]

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.

[Hagar Delest]

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.

[Olle E]

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...

[Hagar Delest]

I'm running OpenOffice 3.0.1. on Ubuntu Intrepid.

But from the repositories or from the OOo web site?

[Olle E]

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

[Hagar Delest]

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

[Olle E]

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...

[Heki]

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

[IvanMarkus]

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?