[Solved] SAVE a SOLVER model in Calc
[Solved] SAVE a SOLVER model in Calc
I know that it is not possible to save a solver model in Calc.
https://ask.libreoffice.org/en/question ... l-in-calc/
https://bugs.documentfoundation.org/sho ... i?id=38948
So I try to propose a possible solution: I used WPS Office 2016 free, and I noticed that it stores the Solver data in the Definition of cells names.
This technique seems simple enough to implement, so can satisfy the requests of many users.
I attach 2 files that I have produced, with examples of two sheets to optimize, and documentation. I think it is not difficult for developers to understand.
Hope this proposal can help make this program better.
Regards.
https://ask.libreoffice.org/en/question ... l-in-calc/
https://bugs.documentfoundation.org/sho ... i?id=38948
So I try to propose a possible solution: I used WPS Office 2016 free, and I noticed that it stores the Solver data in the Definition of cells names.
This technique seems simple enough to implement, so can satisfy the requests of many users.
I attach 2 files that I have produced, with examples of two sheets to optimize, and documentation. I think it is not difficult for developers to understand.
Hope this proposal can help make this program better.
Regards.
- Attachments
-
- examples.xls
- sheets with examples
- (113.5 KiB) Downloaded 226 times
-
- documentation.xls
- documentation of cells names definitions
- (77.5 KiB) Downloaded 202 times
Last edited by gpgrego on Tue Feb 04, 2020 2:48 pm, edited 3 times in total.
openoffice 4.1.6 - Windows 10
Re: SAVE a SOLVER model in Calc
Do not use foreign, never standardized, obsolete file formats.examples.xls
documentation.xls
Note: There is not (never was and never will be) 100% compatibility between the different file formats.
The dafault (native) fileformat of the AOO and the LO are the International Standard ODF file formats.
The AOO and LO can save every properties (what are present and what you use in the Calc application) into the native file format (.ods), but you will lost some properties when you save the document into a foreign file format (.xls).
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: SAVE a SOLVER model in Calc
Thank you for your suggestion.
I update Attachments into the native file format Calc (.ods)
I update Attachments into the native file format Calc (.ods)
- Attachments
-
- example.ods
- (22.69 KiB) Downloaded 219 times
-
- documentation.ods
- (78.74 KiB) Downloaded 251 times
openoffice 4.1.6 - Windows 10
Re: SAVE a SOLVER model in Calc
I want to add: it would not be necessary that all the Solver parameters are saved, only the parameters of the main Dialog would be sufficient, so there is no need to set them again each time the file is reopened.
solver_opt
solver_typ
solver_val
solver_adj
solver_lhs1
solver_rel1
solver_rhs1
solver_num
solver_opt
solver_typ
solver_val
solver_adj
solver_lhs1
solver_rel1
solver_rhs1
solver_num
openoffice 4.1.6 - Windows 10
Re: SAVE a SOLVER model in Calc
Sorry, I never used the solver. Here are some informations about a bug.
https://ask.libreoffice.org/en/question ... l-in-calc/
https://bugs.documentfoundation.org/sho ... i?id=38948
https://ask.libreoffice.org/en/question ... l-in-calc/
https://bugs.documentfoundation.org/sho ... i?id=38948
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: SAVE a SOLVER model in Calc
I see now that bugs have been reported since 2011.
I think maybe OpenOffice or LibreOffice developers are not interested to implementing this functionality.
No problem for this, I will continue to use 'WPS Office 2016 free' or 'MS Excel' when i need Solver.
Thank you.
I think maybe OpenOffice or LibreOffice developers are not interested to implementing this functionality.
No problem for this, I will continue to use 'WPS Office 2016 free' or 'MS Excel' when i need Solver.
Thank you.
openoffice 4.1.6 - Windows 10
Re: [Issue] SAVE a SOLVER model in Calc
Search this forum for "solver +macro" (and skip the endless thread about the Sudoku 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: [Issue] SAVE a SOLVER model in Calc
I'm afraid this won't lead anywhere. Trying to save a model ourselves in a sheet we would need next time to create an instance of the service "com.sun.star.sheet.Solver" and to charge it with the appropriate settings from that sheet. The servic is easily created, but it then refuses to accept any assignment by whatever means. That's what I can tell from experience. This is common heritage of AOO and LibO though they use different (third-party?) solvers meanwhile.Villeroy wrote:Search this forum for "solver +macro"...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Issue] SAVE a SOLVER model in Calc
After all, I believe to making a macro is not the right way because you must to
create a different macro for each different problem, and this is long.
Suppose we set the data in the Solver dialog,
I want to explain with a image what the program Calc should do:
1. When we do click on button -> Close
program Calc save 'Names' in the file.
2. Wen we close file, reopen file, and choose Solver from menu
program Calc check, if there are 'Names in the file then restore in the Solver dialog.
It doesn't seem very complicated to do.
However, I don't want to bore you further.
Thank you so much to everyone for taking the time.
Best Regards.
create a different macro for each different problem, and this is long.
Suppose we set the data in the Solver dialog,
I want to explain with a image what the program Calc should do:
1. When we do click on button -> Close
program Calc save 'Names' in the file.
2. Wen we close file, reopen file, and choose Solver from menu
program Calc check, if there are 'Names in the file then restore in the Solver dialog.
It doesn't seem very complicated to do.
However, I don't want to bore you further.
Thank you so much to everyone for taking the time.
Best Regards.
openoffice 4.1.6 - Windows 10
Re: [Issue] SAVE a SOLVER model in Calc
That's right for the reason I told above: The needed service cannot be initialized due to a bug.gpgrego wrote:After all, I believe to making a macro is not the right way ...
That's wrong as I also pointed out already: You might put the needed info into a range of cells in a spreadsheet, and get the values from there (by a user function) for the initialization of the solver IF THIS WERE POSSIBLE at all.gpgrego wrote:...because you must {to} create a different macro for each different problem, and this is long.
There's no point in telling this the contributors of this forum. They aren't the developers.gpgrego wrote:Suppose we set the data in the Solver dialog,
I want to explain with a image what the program Calc should do:
1. When we do click on button -> Close
program Calc save 'Names' in the file. ...
Filing an enhancement request for AOO is also supposed to be pointless because there is next to no creative development in this branch for a long time now.
Concerning LibreOffice (the "other" branch) it isn't exactly pointless probably, but there is the old bug report already linked into this thread above by @Zizi64. It never was assigned and seems to not get much attention among developers. You may, however, "bump" and upvote it or comment on it in a different way.
Just do it if you feel the power.gpgrego wrote:It doesn't seem very complicated to do.
I didn't feel bored and even spent half an hour testing the possibility of implementing the feature essentially by user code. The result I already reported above: There is a bug. The needed API service doesn't accept initialization. ...gpgrego wrote:However, I don't want to bore you further.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Issue] SAVE a SOLVER model in Calc
Lupp, thank you for clarification. I was about to combine the named ranges approach with some macro code. God knows how much time I would have wasted.
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: [Issue] SAVE a SOLVER model in Calc
Well, the topic is of interest, and I feel a bit unsafe concerning the question if my statement probably was worded too apodictic. If YOU find a workaround concerning how to get a com.sun.star.sheet.Solver service to work by macro, I am very interested.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Issue] SAVE a SOLVER model in Calc
I understand that the bug is a job for developers.
Instead, for try to create a macro (but it will be hard) I think we can find the parameters to use here:
OpenOffice
http://www.openoffice.org/api/docs/comm ... olver.html
http://www.openoffice.org/api/docs/comm ... anges.html
Solver source code:
https://github.com/apache/openoffice/bl ... solver.cxx
LibreOffice
https://api.libreoffice.org/docs/idl/re ... olver.html
https://api.libreoffice.org/docs/idl/re ... anges.html
Solver source code:
https://github.com/LibreOffice/core/blo ... solver.cxx
Instead, for try to create a macro (but it will be hard) I think we can find the parameters to use here:
OpenOffice
http://www.openoffice.org/api/docs/comm ... olver.html
http://www.openoffice.org/api/docs/comm ... anges.html
Solver source code:
https://github.com/apache/openoffice/bl ... solver.cxx
LibreOffice
https://api.libreoffice.org/docs/idl/re ... olver.html
https://api.libreoffice.org/docs/idl/re ... anges.html
Solver source code:
https://github.com/LibreOffice/core/blo ... solver.cxx
openoffice 4.1.6 - Windows 10
Re: [Solved] SAVE a SOLVER model in Calc
I don't and can't.Lupp wrote:Just do it if you feel the power.
But: Whether named ranges are used (what I don't prefer and rarely do) or simply cell (or range) addresses or standardiszed "names" is of very little relevance. The respective properties of the com.sun.star.sheet.Solver object are CellAddress structures. (.Sheet, .Column, .Row). The only substantial problem is the missing acceptance of any assignments by that object. (And its only method is .Solve .)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] SAVE a SOLVER model in Calc
If can be of interest for someone how to solve problem with a macro, this is macro for my model, it seems to work.
Obviously, if we create a macro it is no longer necessary to save the Names. But I think, if in the future they will fix the bug (at least for LibreOffice), no macros will be needed.
Obviously, if we create a macro it is no longer necessary to save the Names. But I think, if in the future they will fix the bug (at least for LibreOffice), no macros will be needed.
Code: Select all
Option Explicit
Sub Solve_Chees_set
Dim oSolver As Object
Dim oDoc As Object
Dim oSheet As Object
Dim i As Integer
Dim oObjectiveCell As Object
' Array dimension for 2 Variables
Dim oVariableCells(1) as new com.sun.star.table.CellAddress
' Array dimension for 4 Constraints
Dim oConstraints(3) as new com.sun.star.sheet.SolverConstraint
' *** INITIALIZE SOLVER ENGINE ***
' IMPORTANT NOTE:
' must choose a different "Server Engine"
' if use OpenOffice or LibreOffice,
' otherwise macro give mistake.
' *** in OpenOffice, I used for my problem -> com.sun.star.comp.Calc.Solver
Set oSolver = CreateUnoService("com.sun.star.comp.Calc.Solver")
' *** in LibreOffice I used -> com.sun.star.comp.Calc.LpsolveSolver
' these are its solver engine that must used:
' Set oSolver = CreateUnoService("com.sun.star.comp.Calc.LpsolveSolver")
' Set oSolver = CreateUnoService("com.sun.star.comp.Calc.CoinMPSolver")
' Set oSolver = CreateUnoService("com.sun.star.comp.Calc.NLPSolver.SCOSolverImpl")
' Set oSolver = CreateUnoService("com.sun.star.comp.Calc.NLPSolver.DEPSSolverImpl")
' *** Document
oDoc = ThisComponent
oSolver.Document = oDoc
oSheet = oDoc.Sheets.getByName("Chess_set")
' *** Objective (Target cell)
oObjectiveCell = oSheet.getCellByPosition(2,18).CellAddress ' $C$19
oSolver.Objective = oObjectiveCell
' *** Optimize result to
oSolver.Maximize = true
' *** Variable cells (By changing cells)
oVariableCells(0) = oSheet.getCellByPosition(2,13).CellAddress ' $C$14
oVariableCells(1) = oSheet.getCellByPosition(3,13).CellAddress ' $D$14
oSolver.Variables = oVariableCells
' Operators
' com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
' com.sun.star.sheet.SolverConstraintOperator.EQUAL
' com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL
' com.sun.star.sheet.SolverConstraintOperator.INTEGER
' com.sun.star.sheet.SolverConstraintOperator.BINARY
' *** Constraints (Limiting Condions)
oConstraints(0).Left = oSheet.getCellByPosition(2,21).CellAddress ' $C$22
oConstraints(0).Operator = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
oConstraints(0).Right = oSheet.getCellByPosition(4,21).CellAddress ' $E$22"
oConstraints(1).Left = oSheet.getCellByPosition(2,22).CellAddress ' $C$23
oConstraints(1).Operator = com.sun.star.sheet.SolverConstraintOperator.LESS_EQUAL
oConstraints(1).Right = oSheet.getCellByPosition(4,22).CellAddress ' $E$23
oConstraints(2).Left = oSheet.getCellByPosition(2,13).CellAddress ' $C$14
oConstraints(2).Operator = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL
oConstraints(2).Right = 0
oConstraints(3).Left = oSheet.getCellByPosition(3,13).CellAddress ' $D$14
oConstraints(3).Operator = com.sun.star.sheet.SolverConstraintOperator.GREATER_EQUAL
oConstraints(3).Right = 0
oSolver.Constraints = oConstraints()
' *** Options ...
oSolver.Integer = 1 ' true
oSolver.NonNegative = 1 ' true
oSolver.Solve()
If oSolver.Success then
for i = 0 to UBound(oSolver.Variables)
oSheet.getCellByPosition(oSolver.Variables(i).Column,oSolver.Variables(i).Row).Value = oSolver.Solution(i)
next
MsgBox "Solving successsfully finished." & Chr$(10) & Chr$(10) & "Result: " & oSolver.ResultValue
Else
MsgBox "No solution was found."
End If
End Sub
- Attachments
-
- example_in_OpenOffice.ods
- (13.85 KiB) Downloaded 197 times
-
- example_in_LibreOffice.ods
- (13.84 KiB) Downloaded 265 times
openoffice 4.1.6 - Windows 10
Re: [Solved] SAVE a SOLVER model in Calc
Thanks!
That's funny. While your AOO example crashes for me at the point where the .Solve method is going to be called, the other one in LibO is working. But how did you find the needed information concerning the "linear_programming_solve_Solver"?
I couldn't and still cannot even find a namespace starting with "com.sun.star.comp", not to speak of the service "com.sun.star.comp.Calc.LpsolveSolver". Nonetheless the LibO Basic knows how to find it - and you knew about the existence at least.
Again: How did you find information about that? I need to learn.
That's funny. While your AOO example crashes for me at the point where the .Solve method is going to be called, the other one in LibO is working. But how did you find the needed information concerning the "linear_programming_solve_Solver"?
I couldn't and still cannot even find a namespace starting with "com.sun.star.comp", not to speak of the service "com.sun.star.comp.Calc.LpsolveSolver". Nonetheless the LibO Basic knows how to find it - and you knew about the existence at least.
Again: How did you find information about that? I need to learn.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] SAVE a SOLVER model in Calc
A quick search gives this, Lupp
https://www.openoffice.org/api/docs/jav ... mmary.html
But I know nothing about it.
Rory
https://www.openoffice.org/api/docs/jav ... mmary.html
But I know nothing about it.
Rory
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
Re: [Solved] SAVE a SOLVER model in Calc
First, by looking at this document from page 21 onwards:
https://fivedots.coe.psu.ac.th/~ad/jlop ... alysis.pdf
Second, I peeked around here:
https://github.com/KDAB/libreoffice-cor ... rce/solver
https://fivedots.coe.psu.ac.th/~ad/jlop ... alysis.pdf
Second, I peeked around here:
https://github.com/KDAB/libreoffice-cor ... rce/solver
openoffice 4.1.6 - Windows 10