Download it now!
Bug 38948 - FILESAVE - Calc does not save the solver's model to files
Summary: FILESAVE - Calc does not save the solver's model to files
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
6.0.7.3 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
: 32063 130332 (view as bug list)
Depends on:
Blocks: Solver
  Show dependency treegraph
 
Reported: 2011-07-04 06:22 UTC by Gerald Senarclens de Grancy
Modified: 2020-05-11 16:55 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Example xl/workbook.xml from .xlsx showing solver values (18.59 KB, application/xml)
2019-10-27 23:34 UTC, Sean Porterfield
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Gerald Senarclens de Grancy 2011-07-04 06:22:10 UTC
Saving a document doesn't save the conditions and changes made in the solver interface. This is an old bug known from OpenOffice: http://openoffice.org/bugzilla/show_bug.cgi?id=93613
The bug is critical because it prevents LibreOffice Calc from being used in (university) courses that rely on the solver. This is particularly sad because it prevents LibreOffice from being shown to students that will otherwise not know about it, which is certainly bad for its widespread use and acceptance.

- reproduce:
open a new file
add a simple solver model, eg.:
max 4x + 4y
s.t. 3x + 5y <= 12
x, y >= 0
save the file
close Libreoffice
open the file again with Libreoffice
click on tools->solver

- expected:
the model is still there

- actual:
the target cell is the current cell, the modifiable cells and constraints are all gone
Comment 1 Gerald Senarclens de Grancy 2011-07-14 01:55:16 UTC
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).
Comment 2 TJ Meneses 2011-10-07 08:02:52 UTC
(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.
Comment 3 Björn Michaelsen 2011-12-23 12:25:16 UTC Comment hidden (obsolete)
Comment 4 Gerald Senarclens de Grancy 2011-12-26 05:08:57 UTC
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.
Comment 5 Gerald Senarclens de Grancy 2012-06-15 01:42:54 UTC
*** Bug 32063 has been marked as a duplicate of this bug. ***
Comment 6 Gerald Senarclens de Grancy 2012-06-15 01:53:05 UTC
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?
Comment 7 roberto.tavares 2012-06-15 01:58:35 UTC
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.
Comment 8 Fabio Fagundez 2012-09-30 01:42:03 UTC
(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.
Comment 9 Gerald Senarclens de Grancy 2014-11-25 09:17:57 UTC
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.
Comment 10 Dwight Walker 2015-12-15 03:20:07 UTC
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.
Comment 11 Tim M 2017-03-26 13:20:43 UTC
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?
Comment 12 Andrea Ferrari 2018-05-28 08:57:22 UTC
This bug is still present in Libreoffice 6.0.4.2 (x64)
Regards
Andrea
Comment 13 Hector Perez 2019-03-23 03:02:31 UTC
This bug continues to be present in LO 6.2. This is the biggest stumbling block for switching over to LO in my case.
Comment 14 Marco 2019-05-07 10:07:38 UTC
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.
Comment 15 Carlos Villegas 2019-08-01 17:19:39 UTC
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 !
Comment 16 Sean Porterfield 2019-10-27 23:34:48 UTC
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.
Comment 17 raal 2020-02-04 07:46:51 UTC
*** Bug 130332 has been marked as a duplicate of this bug. ***
Comment 18 Kanika Rajput 2020-05-09 05:09:13 UTC Comment hidden (spam)
Comment 19 Kanika Rajput 2020-05-09 05:09:35 UTC Comment hidden (spam)
Comment 20 Kanika Rajput 2020-05-09 05:09:42 UTC Comment hidden (spam)
Comment 21 Kanika Rajput 2020-05-09 05:09:58 UTC Comment hidden (spam)
Comment 22 Kanika Rajput 2020-05-09 05:10:09 UTC Comment hidden (spam)
Comment 23 Kanika Rajput 2020-05-09 05:10:16 UTC Comment hidden (spam)
Comment 24 Kanika Rajput 2020-05-09 05:10:37 UTC Comment hidden (spam)
Comment 25 Kanika Rajput 2020-05-09 05:10:53 UTC Comment hidden (spam)
Comment 26 Kanika Rajput 2020-05-09 05:11:00 UTC Comment hidden (spam)