[Solved] Solver Macro

Creating a macro - Writing a Script - Using the API

[Solved] Solver Macro

Postby Olle E » Sat Mar 28, 2009 11:00 pm

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/ref/com/sun/star/sheet/XSolver.html

Many thanks in advance

/O.
Last edited by MrProgrammer on Wed Sep 18, 2019 5:49 pm, edited 6 times in total.
Reason: Moved from Calc forum to Macros and UNO API
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby Villeroy » Sat Mar 28, 2009 11:18 pm

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   Expand viewCollapse view
REM Basic Code
smgr = GetProcessServiceManager()
solv = smgr.createInstance("com.sun.star.sheet.Solver")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27888
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Solver Macro

Postby Olle E » Thu Apr 02, 2009 8:31 pm

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.
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby FJCC » Fri Apr 03, 2009 5:15 am

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   Expand viewCollapse view
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
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7539
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Solver Macro

Postby Olle E » Thu Apr 09, 2009 8:52 pm

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.
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: [Solved] Solver Macro

Postby FJCC » Fri Apr 10, 2009 4:02 am

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?
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7539
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: [Solved] Solver Macro

Postby Olle E » Fri Apr 10, 2009 10:10 pm

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 :cry:
Is there possibly another well-functioning non-linear add-in which runs in silent mode?

BR /O.
Last edited by Olle E on Fri Apr 10, 2009 10:15 pm, edited 1 time in total.
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby Hagar Delest » Fri Apr 10, 2009 11:01 pm

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.
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28719
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Solver Macro

Postby Olle E » Sat Apr 11, 2009 9:24 pm

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... :geek:
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby Hagar Delest » Sun Apr 12, 2009 10:12 am

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

But from the repositories or from the OOo web site?
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28719
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Solver Macro

Postby Olle E » Sun Apr 12, 2009 10:15 pm

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
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby Hagar Delest » Sun Apr 12, 2009 10:39 pm

Then try the official version (from Sun). See the link I've given in previous page.
AOO 4.1.7 on Xubuntu 19.10 and 4.1.5 on Windows 10 (with winPenPack port).
User avatar
Hagar Delest
Moderator
 
Posts: 28719
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Solver Macro

Postby Olle E » Mon Apr 13, 2009 9:55 pm

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... :lol:
OOo 3.0.X on Ubuntu 8.x + Windows XP
Olle E
 
Posts: 11
Joined: Sat Mar 28, 2009 10:46 pm

Re: Solver Macro

Postby Heki » Tue Sep 13, 2011 11:17 pm

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   Expand viewCollapse view
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 :roll:
OpenOffice 3.2 - Windows 7
Heki
 
Posts: 1
Joined: Tue Sep 13, 2011 11:07 pm

Re: [Solved] Solver Macro

Postby IvanMarkus » Mon Jan 11, 2016 12:46 pm

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?
LibreOffice 4.4 + Win7
IvanMarkus
 
Posts: 1
Joined: Mon Jan 11, 2016 12:34 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 2 guests