Page 1 of 1
[Solved] SAVE a SOLVER model in Calc
Posted: Fri Jan 31, 2020 4:54 pm
by gpgrego
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.
Re: SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 10:28 am
by Zizi64
examples.xls
documentation.xls
Do not use foreign, never standardized, obsolete file formats.
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).
Re: SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 11:12 am
by gpgrego
Thank you for your suggestion.
I update Attachments into the native file format Calc (.ods)
Re: SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 11:38 am
by gpgrego
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
Re: SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 12:10 pm
by Zizi64
Re: SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 1:57 pm
by gpgrego
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.
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 4:24 pm
by Villeroy
Search this forum for "solver +macro" (and skip the endless thread about the Sudoku solver).
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Sun Feb 02, 2020 5:27 pm
by Lupp
Villeroy wrote:Search this forum for "solver +macro"...
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.
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Mon Feb 03, 2020 12:31 pm
by gpgrego
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.
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Mon Feb 03, 2020 3:11 pm
by Lupp
gpgrego wrote:After all, I believe to making a macro is not the right way ...
That's right for the reason I told above: The needed service cannot be initialized due to a bug.
gpgrego wrote:...because you must {to} create a different macro for each different problem, and this is long.
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: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. ...
There's no point in telling this the contributors of this forum. They aren't the developers.
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.
gpgrego wrote:It doesn't seem very complicated to do.
Just do it if you feel the power.
gpgrego wrote:However, I don't want to bore you further.
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. ...
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Mon Feb 03, 2020 4:50 pm
by Villeroy
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.
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Mon Feb 03, 2020 9:02 pm
by Lupp
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.
Re: [Issue] SAVE a SOLVER model in Calc
Posted: Tue Feb 04, 2020 2:45 pm
by gpgrego
Re: [Solved] SAVE a SOLVER model in Calc
Posted: Tue Feb 04, 2020 6:02 pm
by Lupp
Lupp wrote:Just do it if you feel the power.
I don't and can't.
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 .)
Re: [Solved] SAVE a SOLVER model in Calc
Posted: Thu Feb 06, 2020 1:50 pm
by gpgrego
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.
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
Re: [Solved] SAVE a SOLVER model in Calc
Posted: Thu Feb 06, 2020 3:37 pm
by Lupp
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.
Re: [Solved] SAVE a SOLVER model in Calc
Posted: Thu Feb 06, 2020 3:41 pm
by RoryOF
A quick search gives this, Lupp
https://www.openoffice.org/api/docs/jav ... mmary.html
But I know nothing about it.
Rory
Re: [Solved] SAVE a SOLVER model in Calc
Posted: Thu Feb 06, 2020 4:31 pm
by gpgrego