[Solved] SAVE a SOLVER model in Calc

Discuss the spreadsheet application
Post Reply
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

[Solved] SAVE a SOLVER model in Calc

Post 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.
Attachments
examples.xls
sheets with examples
(113.5 KiB) Downloaded 223 times
documentation.xls
documentation of cells names definitions
(77.5 KiB) Downloaded 196 times
Last edited by gpgrego on Tue Feb 04, 2020 2:48 pm, edited 3 times in total.
openoffice 4.1.6 - Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SAVE a SOLVER model in Calc

Post 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).
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.
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: SAVE a SOLVER model in Calc

Post by gpgrego »

Thank you for your suggestion.
I update Attachments into the native file format Calc (.ods)
Attachments
example.ods
(22.69 KiB) Downloaded 218 times
documentation.ods
(78.74 KiB) Downloaded 242 times
openoffice 4.1.6 - Windows 10
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: SAVE a SOLVER model in Calc

Post 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
Attachments
solver_form.jpg
openoffice 4.1.6 - Windows 10
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SAVE a SOLVER model in Calc

Post by Zizi64 »

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
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.
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: SAVE a SOLVER model in Calc

Post 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.
openoffice 4.1.6 - Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] SAVE a SOLVER model in Calc

Post by Villeroy »

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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Issue] SAVE a SOLVER model in Calc

Post 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.
Attachments
names.jpg
openoffice 4.1.6 - Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Post 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. ...
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] SAVE a SOLVER model in Calc

Post 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.
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
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Issue] SAVE a SOLVER model in Calc

Post by gpgrego »

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
openoffice 4.1.6 - Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] SAVE a SOLVER model in Calc

Post 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 .)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Solved] SAVE a SOLVER model in Calc

Post 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
Attachments
example_in_OpenOffice.ods
(13.85 KiB) Downloaded 190 times
example_in_LibreOffice.ods
(13.84 KiB) Downloaded 261 times
openoffice 4.1.6 - Windows 10
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] SAVE a SOLVER model in Calc

Post 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.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] SAVE a SOLVER model in Calc

Post by RoryOF »

A quick search gives this, Lupp
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
gpgrego
Posts: 39
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Solved] SAVE a SOLVER model in Calc

Post by gpgrego »

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
openoffice 4.1.6 - Windows 10
Post Reply