Bug 160062

Created attachment 192989 [details]
Sample macro that uses the Solver service

We currently have the Solver service [1] that in turn requires the implementation of the XSolver interface [2] and they work fine to create a macro that models a problem and runs the solver (see f.i. the attached file with an example of such macro).

However, we do not have API to define a solver model that can be saved at the sheet level (as was made possible after bug 38948 was fixed). For instance, if you run the macro in the attached file, the solver model itself won't be visible in the Tools - Solver dialog. Also, it is not possible to create a macro that runs the solver using the settings defined in the Tools - Solver dialog... they're not accessible via macros.

In other words, we need API to be able to access/define solver objective, constraints, etc so that they are visible when Tools - Solver dialog is opened. We also need to be able to run the solver from the macro without having to open the dialog and pressing the Solve button.

It would be something like this:

oSheet = ThisComponent.Sheets(0)
oSolverModel = oSheet.getSolverModel()
oRange1 = oSheet.getCellRangeByName("A1:A10")
oRange2 = oSheet.getCellRangeByName("B1:B10")
operator = com.sun.star.sheet.SolverConstraintOperator
oSolverModel.addConstraint(oRange1, oRange2, operator.LESS_EQUAL)
...
oSolverModel.run()

The code above would create a solver constraint "A1:A10" <= "B1:B10" and it would be visible in the Tools - Solver dialog, as well as saved to the file similarly when the user creates the model manually. It would also run the model without having to manually open the Solver dialog.

This enhancement would require the Spreadsheet service to implement the getSolverModel method, which would return an object of type XSolverModel (this interface is to be defined yet) that contains the methods and properties of the solver model that belongs to the sheet.

I am planning to implement this for the 24.8 release, but I'm not sure about how to organize this new API (define services, interfaces, etc). So if anyone could please provide some insight on how to organize this new API, I would be grateful =)

[1] https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/sheet/Solver.idl?r=5687eba4

[2] https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/sheet/XSolver.idl?r=5687eba4