Is there any way to save a solver model in Calc?

The single most irritating aspect of the LP feature in Calc is its inability to save the model that I built. Specifically, data on the cells used as the objective function, constraints and variables is lost when the spreadsheet is reloaded.

I’ve known this problem for a while now, and know that there won’t be any straightforward way of saving the model, either through the extension options or Calc’s save function. All I want to know is if there’s any sane way to store the model in any sort of persistent data such that, upon restart, opening the model will result in the cells previously input to still be there. My first guess is through macros, but I’m both clueless on the basics of macro building as well as dubious that its functionality can extend to extensions.

If anyone could give me not only a simple yes/no answer, but also a step-by-step procedure for saving and restoring model data in a spreadsheet, I’d be incredibly grateful. (And I’d label you as the answer and +1 you.)

As TJ Meneses knows, a bug for this has been filed years ago against The same bug for LibreOffice was also filed in mid 2011 but doesn’t get any love.

Unfortunately, I cannot provide a way of circumventing the bug inside LibreOffice. I still use Calc for my courses if they don’t require a linear solver. For the rest, the sole solution I found was to use other tools.

Gnumeric serves rather nicely and is free software. Using a dedicated linear solver like lpsolve, glpk or coin OR is another alternative. Finally, if closed source is an option, Excel does the job quite well and can nowadays also be installed comfortably in Linux environments using frontends for wine like playonlinux.

Hm, how’s the linear solver of Gnumeric compared to Calc? I might decide to work with it if ever I do need to work with LP models.

I think what you want to do is not possible because no Calc doesn’t know where to put the figures. You have for each scenario several parameters. Therefore you need to create a table to store results of various scenarios. By having a separate table you also can compare the different scenarios you created.

In the case of Goal Seak the simulated cell is identified and Calc knows where to put the figures.

Hi @TJMeneses,

It sounds like @ROSt53 understands this situation better than I, so I don’t have much to add to his advice about how to save the state of your solver model in Calc.

If you believe that there’s an alternate way to save this data in the document, please feel free to file an enhancement bug and provide as much information about how the data should be saved. Don’t forget to mark your bug as an ‘enhancement’. The QA team will be happy to help you triage your feature request in the bugtracker.

Please post a link to any bugs you file in a comment below using the format “fdo#123456”.


Actually, I have absolutely no idea how it might possibly be implemented. =P Although, the OP of the bug report in @santa’s link suggested a possible way of doing it using ODF 1.2:

@TJMeneses – Ah, yes, the comments on that bug definitely help me to understand the problem and how the possible solution might work.

I guess you’ll want to keep an eye on fdo#38948 and see if the feature is added soon :slight_smile:

I just tried to make a test using the “solver” and not “goal seek”. However I got the message that my Java JRE is not the right one…

A possible workaround could be a screen shot - also it is not a good one.