[Solved] SAVE a SOLVER model in Calc

Discuss the spreadsheet application

[Solved] SAVE a SOLVER model in Calc

Postby gpgrego » Fri Jan 31, 2020 4:54 pm

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 37 times
documentation.xls
documentation of cells names definitions
(77.5 KiB) Downloaded 34 times
Last edited by gpgrego on Tue Feb 04, 2020 2:48 pm, edited 3 times in total.
openoffice 4.1.6 - Windows 10
gpgrego
 
Posts: 8
Joined: Fri Jan 31, 2020 4:04 pm

Re: SAVE a SOLVER model in Calc

Postby Zizi64 » Sun Feb 02, 2020 10:28 am

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; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9606
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SAVE a SOLVER model in Calc

Postby gpgrego » Sun Feb 02, 2020 11:12 am

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

Re: SAVE a SOLVER model in Calc

Postby gpgrego » Sun Feb 02, 2020 11:38 am

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

Re: SAVE a SOLVER model in Calc

Postby Zizi64 » Sun Feb 02, 2020 12:10 pm

Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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.
User avatar
Zizi64
Volunteer
 
Posts: 9606
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: SAVE a SOLVER model in Calc

Postby gpgrego » Sun Feb 02, 2020 1:57 pm

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

Re: [Issue] SAVE a SOLVER model in Calc

Postby Villeroy » Sun Feb 02, 2020 4:24 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28668
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] SAVE a SOLVER model in Calc

Postby Lupp » Sun Feb 02, 2020 5:27 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2930
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Postby gpgrego » Mon Feb 03, 2020 12:31 pm

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

Re: [Issue] SAVE a SOLVER model in Calc

Postby Lupp » Mon Feb 03, 2020 3:11 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2930
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Postby Villeroy » Mon Feb 03, 2020 4:50 pm

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, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28668
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Issue] SAVE a SOLVER model in Calc

Postby Lupp » Mon Feb 03, 2020 9:02 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2930
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Issue] SAVE a SOLVER model in Calc

Postby gpgrego » Tue Feb 04, 2020 2:45 pm

openoffice 4.1.6 - Windows 10
gpgrego
 
Posts: 8
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Solved] SAVE a SOLVER model in Calc

Postby Lupp » Tue Feb 04, 2020 6:02 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2930
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] SAVE a SOLVER model in Calc

Postby gpgrego » Thu Feb 06, 2020 1:50 pm

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   Expand viewCollapse view
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 34 times
example_in_LibreOffice.ods
(13.84 KiB) Downloaded 44 times
openoffice 4.1.6 - Windows 10
gpgrego
 
Posts: 8
Joined: Fri Jan 31, 2020 4:04 pm

Re: [Solved] SAVE a SOLVER model in Calc

Postby Lupp » Thu Feb 06, 2020 3:37 pm

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 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2930
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] SAVE a SOLVER model in Calc

Postby RoryOF » Thu Feb 06, 2020 3:41 pm

A quick search gives this, Lupp
https://www.openoffice.org/api/docs/java/ref/com/sun/star/comp/helper/package-summary.html

But I know nothing about it.

Rory
Apache OpenOffice 4.1.7 on Xubuntu 20.04.1 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 31560
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: [Solved] SAVE a SOLVER model in Calc

Postby gpgrego » Thu Feb 06, 2020 4:31 pm

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


Return to Calc

Who is online

Users browsing this forum: No registered users and 19 guests