[Solved] Solver Macro
[Solved] Solver Macro
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.
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.
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
Reason: Moved from Calc forum to Macros and UNO API
OOo 3.0.X on Ubuntu 8.x + Windows XP
Re: Solver Macro
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").
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")
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Solver Macro
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.
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
Re: Solver Macro
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
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Solver Macro
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.
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.
Re: [Solved] Solver Macro
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?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: [Solved] Solver Macro
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.
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
- Hagar Delest
- Moderator
- Posts: 32650
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Solver Macro
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.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Solver Macro
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...
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...
OOo 3.0.X on Ubuntu 8.x + Windows XP
- Hagar Delest
- Moderator
- Posts: 32650
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Solver Macro
But from the repositories or from the OOo web site?Olle E wrote:I'm running OpenOffice 3.0.1. on Ubuntu Intrepid.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Solver Macro
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
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
- Hagar Delest
- Moderator
- Posts: 32650
- Joined: Sun Oct 07, 2007 9:07 pm
- Location: France
Re: Solver Macro
Then try the official version (from Sun). See the link I've given in previous page.
LibreOffice 7.6.2.1 on Xubuntu 23.10 and 7.6.4.1 portable on Windows 10
Re: Solver Macro
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...
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...
OOo 3.0.X on Ubuntu 8.x + Windows XP
Re: Solver Macro
Hello FJCCFJCC 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
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
OpenOffice 3.2 - Windows 7
-
- Posts: 1
- Joined: Mon Jan 11, 2016 12:34 pm
Re: [Solved] Solver Macro
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?
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