I vote for changing the importance of this bug to be at least "normal" for two reasons:
- it would be critical if after entering a formula in a cell, instead of the formula, only the outcome (number) is saved to file - which is basically what happens with the solver objective and constraints.
- this particular bug prevents (business) schools/ universities from teaching Libreoffice instead of Microsoft (and has been ignored by OpenOffice for about three years).

(In reply to comment #1)
> I vote for changing the importance of this bug to be at least "normal" for two
> reasons:
> - it would be critical if after entering a formula in a cell, instead of the
> formula, only the outcome (number) is saved to file - which is basically what
> happens with the solver objective and constraints.
> - this particular bug prevents (business) schools/ universities from teaching
> Libreoffice instead of Microsoft (and has been ignored by OpenOffice for about
> three years).

I second this vote. I'm a college student currently going through operations research subjects which require linear programming as part of its syllabus, and so far LO Calc has been a big help in solving cases on integer programming we're being given in class. However, the lack of a save function shocked me when I discovered that my previously lengthy model in one particular problem all but disappeared when I reopened the file at a later time.

This sounds like much more than a feature request. It's functionality that's missing, because no one in their right mind would create anything more than the most basic of LP models using Calc knowing that they would have to rebuild it EVERY SINGLE SESSION.

I read in a similar bug post (32063) that implementing it would require changing the ODF spec. I propose for the solver to simply not save the model in the same *.ods file as the spreadsheet from which it is built, but rather in a separate file. I find this to be an elegant solution, since the implementer of the language solver would use to save the data is unconstrained to pick from any of the plentiful (and open) LP languages out there, with my own preference of GAMS. It would furthermore be highly portable, meaning it could be loaded onto any other spreadsheet and run independent of the original basis of the model.

As suggested by the automated comment above I installed
LibO-Dev_3.5.0beta2_Linux_x86-64
and unfortunately, the bug still reproduces as described in the original report - I will thus set it to NEW again (I cannot set it to CONFIRMED).

Also, I'd like to stress the importance of the issue one more time - it basically prevents business schools from using LibreOffice Calc in their courses. Simply not saving a part of the entered data such as a formula or in this case a solver model is a bug and not a lacking enhancement. Unfortunately, afaik the reason for this is that ODF 1.1 does not specify an option to store such models in the file meta data (which vastly complicates things). If anyone could tell me where to file "feature requests" against the ODF specification I'll gladly do so. Thanks.

*** Bug 32063 has been marked as a duplicate of this bug. ***

I have a question regarding the feasibility of this request. It has been argued over and over that w/ the current version of the odf specification it is not possible to store the solver's model. However, as of January 2012 OASIS has released ODF 1.2 which includes the possibility of storing meta data:
http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part3.html#__RefHeading__752819_826425813
Shouldn't it be possible to save/ load the model(s) to/from metadata files?

I have the same problem! I want to use L.O. for teaching, but this bug really gets in the way, and I keep going back to MS Office...

(In reply to comment #2)
> (In reply to comment #1)
> > I vote for changing the importance of this bug to be at least "normal" for two
> > reasons:
> > - it would be critical if after entering a formula in a cell, instead of the
> > formula, only the outcome (number) is saved to file - which is basically what
> > happens with the solver objective and constraints.
> > - this particular bug prevents (business) schools/ universities from teaching
> > Libreoffice instead of Microsoft (and has been ignored by OpenOffice for about
> > three years).
> 
> I second this vote. I'm a college student currently going through operations
> research subjects which require linear programming as part of its syllabus, and
> so far LO Calc has been a big help in solving cases on integer programming
> we're being given in class. However, the lack of a save function shocked me
> when I discovered that my previously lengthy model in one particular problem
> all but disappeared when I reopened the file at a later time.
> 
> This sounds like much more than a feature request. It's functionality that's
> missing, because no one in their right mind would create anything more than the
> most basic of LP models using Calc knowing that they would have to rebuild it
> EVERY SINGLE SESSION.
> 
> I read in a similar bug post (32063) that implementing it would require
> changing the ODF spec. I propose for the solver to simply not save the model in
> the same *.ods file as the spreadsheet from which it is built, but rather in a
> separate file. I find this to be an elegant solution, since the implementer of
> the language solver would use to save the data is unconstrained to pick from
> any of the plentiful (and open) LP languages out there, with my own preference
> of GAMS. It would furthermore be highly portable, meaning it could be loaded
> onto any other spreadsheet and run independent of the original basis of the
> model.

(In reply to comment #1)
> I vote for changing the importance of this bug to be at least "normal" for
> two reasons:
> - it would be critical if after entering a formula in a cell, instead of the
> formula, only the outcome (number) is saved to file - which is basically
> what happens with the solver objective and constraints.
> - this particular bug prevents (business) schools/ universities from
> teaching Libreoffice instead of Microsoft (and has been ignored by
> OpenOffice for about three years).

I second that vote. In order to be a viable option, Calc's Solver must be able to save the model with its constraints, objective function and variables.

The issue of not being able to save solver models is still present in 4.2.7.2. Also, it is still not possible to load models saved by Microsoft Excel. A showstopper for using LibreOffice in operations research.

OpenOffice 2.0 has save and load for solver but LibreOffice 5.0.2.2 does not.

The solver settings are basic maths language and could be saved or exported as a text file.

I have written maths solutions using lp_solve in FreeBSD in a maths language - objective function, decision variables, constraints.

See this for the language:

http://lpsolve.sourceforge.net/5.5/

I discovered this when OpenOffice 2.0 crashed and a maths language of constraints etc was listed.

So the file does not have to be saved in ODF but plain text file. This is a script not a data file.

If you won't change the GUI, have a text format that anyone can edit and view or replace solver with a plugin where the plugin writer can export and import settings.

Or the script could be serialized and stored in a blob and reloaded when the file is loaded.

The apathy is staggering.

I tried to hack the binary file but could not find the solver data in there so it is only in memory till the application closes.

Outsource the data file to the plugin if you won't be bothered fixing this.

So back to writing down the objective function, decision variables and constraints on paper and re-entering whenever I run a LibreOffice solver solution. So daggy.

This is still an issue in Version: 5.1.6.2.0+.

Is this still an issue in later versions?

Are there any comments from someone more familiar with the code as to this bug's
status?

This bug is still present in Libreoffice 6.0.4.2 (x64)
Regards
Andrea

This bug continues to be present in LO 6.2. This is the biggest stumbling block for switching over to LO in my case.

Have lots of .xls sheets with solver settings. I think this is an important issue that lowers the value of Libreoffice Calc.
Hope it wil be fixed/implemented soon.

As of Libre Office version 6.07.3 this bug has not been solved nor Assigned !
Importance URGENT  as it was reported more than 8 years ago !

Created attachment 155355 [details]
Example xl/workbook.xml from .xlsx showing solver values

Microsoft Excel .xlsx files have this information saved, per worksheet.  Even without saving, we're stuck with the same solver info on every worksheet instead of having each one remembered.

It would be great, at the very least, if Calc supported the .xlsx feature in that file type, even if it can't be saved elsewhere.  (I'm not familiar with .odf, but I suspect the same feature can be used there as in .xlsx.)

When I unzip the .xlsx file, I see xl/workbook.xml which contains all of the solver information for the tabs.  Attaching example file.

*** Bug 130332 has been marked as a duplicate of this bug. ***

*** Bug 134529 has been marked as a duplicate of this bug. ***

*** Bug 144776 has been marked as a duplicate of this bug. ***

@Michael,
No clue about need/demand for this at enterprise level. Only a poke

So far no complaints. Of course - if this is useful for students it may be that we can find a student who would like to work on it, who can be supported by Hossain.

(In reply to Michael Meeks from comment #30)
> So far no complaints. Of course - if this is useful for students it may be
> that we can find a student who would like to work on it, who can be
> supported by Hossain.

This is mostly useful for universities and research. Of course there are some industry / organizational applications, but the number of companies using solvers is significantly lower than the number of companies using more common spreadsheet features.

I myself am a university professor teaching Operations Research (that relies on solvers), so it would be awesome to switch to LibreOffice Calc for all my lectures. However, the lack of (i) having separate solver configurations for each sheet and (ii) the inability to save solver configuration into files are issues that make it impossible to adopt LibreOffice Calc in my Operations Research lectures.

I have already delved into the code to try to figure out how it works... I could understand most of it, but I have no idea how settings could be saved into the ODS file. It seems ODF doesn't have anything specific to Solver data.

What I was considering is creating some mechanism that saves Solver settings into a JSON file and embed it in the ODS file. This is would be similar to the "Automatic Redaction" feature that saves entries into a JSON file. Could anyone tell me if this would be a valid approach?

Moreover, I'm not sure if I have enough C++ knowledge to carry this out. Maybe @Hossain can give some input on how to solve this issue.

Hi Rafael - great news that you've researched it =) Yes of course storing it in a un-standardized stream in the ODF file for now is a good approach. Probably you'll need some help connecting that through the UNO madness into somewhere you can easily stream things in and out - Hossein should be able to help with that I imagine. I would use XML not JSON just for reasons of consistency in ODF really. If there is an existing standard for this from the MS side it would be good to be interoperable with that I think too. HTH!

(In reply to Michael Meeks from comment #32)
> Probably you'll need some help connecting that through the UNO madness into
> somewhere you can easily stream things in and out - Hossein should be able
> to help with that I imagine. I would use XML not JSON just for reasons of
> consistency in ODF really. If there is an existing standard for this from
> the MS side it would be good to be interoperable with that I think too. HTH!
I would be happy to help implementing this feature. :-) Let me check and then write about it.

(In reply to Hossein from comment #33)
> I would be happy to help implementing this feature. :-) Let me check and
> then write about it.

Hi Hossein! I'll take another look at the code too, to recap everything.

In order to implement this new feature I believe we're gonna need many separate patches. One possible course of action would be:

1) Implement the ability to save solver configuration on a "per-sheet" basis at runtime (still not saving to the file). This part might not be so difficult.

2) Save solver settings to an embedded XML file into the ODS file.

3) Make Calc load solver settings when the ODS file is opened and the user selects a new sheet.

4) Implement XLSX export so that solver settings are compatible with MS Excel (it seems Excel saves solver info to 'xl/workbook.xml'.

5) Implement XLSX import to be able to load solver settings from Excel files.

I have no idea how to implement steps 2 to 5.

Sorry, I won't b able to scan the cod, but...

Regarding the already mentioned complications, the problem where to store the parameters, and how to do it per sheet or in a different way, then a probable decision to implement an interim solution, I would suggest a simpler and probably even more useful solution: 

Create a bit of code saving the parameters of a solver job (including the algorithm designator) to a cell range the user may describe in a field of the dialog. A button then can trigger the action. In the same way a job could be read from a cell range and passed to the solver object. 
This would be rather simple. 
I will not rework the dialog, but surely I could write the needed code even in Basic in a reasonable time.

Dear all, I arrived here after all the model I had loaded into the solver had disappeared when I opened the file the next day.
In my humble opinion, if there are no developers interested in implementing what is necessary to allow saving the models, there should at least be a warning, when opening the solver, that the models are volatile.
It should be clarified that the LibreOffice help does not mention such a limitation either.
It is not nice to lose all the work done.
From my point of view this is not only a missing feature but it is a loss of information and work, and forces to look for alternatives.
I hope I don't sound inconsiderate, it's just constructive criticism.

(In reply to Matías Benzo from comment #36)
> It should be clarified that the LibreOffice help does not mention such a
> limitation either.

Hi Matías, the current help page [1] has the following statement: "The dialog settings are retained until you close the current document."

It's expressed as a "note". Maybe we could rephrase it to "Beware that solver settings are not saved to the file by LibreOffice Calc. Closing and reopening the file will reset the solver dialog to default settings." and use a warning block instead of a note.

[1] https://help.libreoffice.org/latest/en-US/text/scalc/01/solver.html?DbPAR=CALC#bm_id7654652

Hi Rafael, thank you very much for your reply.
Evidently I misunderstood that note or overlooked it. Anyway, it is surely better as a warning than as a note.
Too bad this remains unresolved, it takes a lot of potential away from Calc.
Do you know if anyone is working on this?

Anyway the "current settings" are only  kept foe the ONE dialog as long as the file is open. 
In fact I would think of cases where more than one set of parameters for a solver should be recallable in different sheets e.g. or for working on a set of data with different solver algorithms or changing constraints experimentally.  
See my comment here https://bugs.documentfoundation.org/show_bug.cgi?id=70399#c3. 
The shortcoming insofar is neither handled by a note nor by a prompt.

Rafael Lima committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/40ee3181de08d372b5a25bb691644a94d3ffe9bd

Related tdf#38948 Warn that Calc Solver does not save model to file

Hello all,
this is curently the only piece of software I miss to stop using MS Office and then windows.
I work as a Logistics Engineering Manager, and I use the libreoffice solver to optimise network models, it is also used on MItx Supply Chaine Micromasters, wich encourage the use of Libreoffice giving .odf file model to teach.

Please add the funcionatlity to save, or export the solver model, save on the .odf file is not necesary if we can export the model and prevent the loss of time modeling.

Proposed patch in Gerrit:
https://gerrit.libreoffice.org/c/core/+/148112

This patch implements the solver config save/load feature:
- Solver configurations are now saved per tab (as in MS Excel)
- Solver settings are saved into the ODS file
- The mechanism to save/load solver configurations is compatible to MS Excel, so XLSX files with solver configurations should be loaded in Calc; and ODS files exported as XLSX are also expected to work in MS Excel (except a few solver settings that do not exist in LO)

Here are a few relevant information about the patch:

OVERVIEW

MS Excel saves solver settings in its documents using "hidden" named ranges. To check that, simply open the "workbook.xml" of a XLSX file with solver settings and check the "definedName" tags containing "solver_*" entries. This can also be checked by inspecting the file "SOLVER.XLAM" that implements all the solver UI in Excel (yes, the solver UI is implemented in VBA, but the solver engine itself is a dynamic library).

This is a good thing, because I originally though that saving solver settings wouldn't be compatible with the ODF format, because I thought that MS Excel had it's own OOXML standards for saving solver settings. But instead, MS Excel simply uses hidden named ranges. Since we also support named ranges/expressions, we can simply save solver settings as named ranges and we "automatically" gain export/import abilities.

The only problem is that we do not support "hidden" named ranges in the ODF specs. This does not hurt the patch, but the drawback is that the named ranges will be visible to the users instead of hidden by default.

LIMITATIONS

1) When exporting to XLSX the named ranges won't be hidden in Excel, so the first time the user opens the Solver dialog in Excel, the names of the named ranges will be shown instead of showing their contents; closing and opening the dialog will hide the named ranges and "fix" the problem
1) Solver engines used in Calc and Excel are different and have different options, so I did my best to implement compatibility, but not everything can be imported/exported

I tested this patch with various XLSX files with solver models I use for teaching and all of them worked. However, I would appreciate more testing for us to get this feature working as nice as possible.

Nice work Rafael ! =)

Rafael Lima committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/5d86cc81fef7d538440c630a503a5d94c6a3be4c

tdf#38948 Save solver settings to file

It will be available in 7.6.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

I am closing this bug as FIXED.

With this recent patch, LibreOffice is now able to save solver settings to the ODS file and each tab can have its own model.

Saving as XLSX and opening solver models in Excel should work, as well as opening XLSX files with models created in Excel, given the limitations pointed out in Comment #43.

I highly encourage users to download the daily builds and test this new functionality.

If anyone finds any bugs, please open a separate ticket and add me to the CC list.

Thank you very much for your work Rafael!

fix verified in:

Version: 7.6.0.0.alpha1+ (X86_64) / LibreOffice Community
Build ID: f4c24da1e7f11664e0d2f688d2531f068e4a3bc0
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

Issue was inherited as it wasn't available in OOo 3.3.

Added bug 154449 (about hidden named ranges) to see also.

Thanks Rafael!

Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/94bda149c7f824e4c31db3efe1df6bb8df8a98be

tdf#38948 - Help on solver saved settings

Olivier Hallot committed a patch related to this issue.
It has been pushed to "libreoffice-7-6":

https://git.libreoffice.org/help/commit/ff44d8360ed43e2f2b749205804e4645b801f55a

tdf#38948 - Help on solver saved settings

*** Bug 158735 has been marked as a duplicate of this bug. ***