Created attachment 192989 [details] Sample macro that uses the existing 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
WIP patch here https://gerrit.libreoffice.org/c/core/+/171769
Created attachment 196153 [details] Sample ODS file with macros that use the new solver API This ODS file has 3 solver sheets, as well as the macros that create and run the solver models for each sheet. This file should be used as example for testing the patch. Sheet "Knapsack" - Use the macro "CreateKnapsackModel" to create the solver model (notice that it becomes visible in the Solver dialog) - Use the macro "RunKnapsackModel" to run the model Sheet "Transportation" - Macro "CreateTransportationModel" creates the model - Macro "RunTransportationModel" runs the model Sheet "BinPacking" (WARNING: this model may take a long time to finish running) - Macro "CreatePackingModel" creates the model - Macro "RunPackingModel" runs the model
Rafael Lima committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/911a57c4d6fc2cbe9d4350189e06a8b733e6f7d3 tdf#160062 Uno API to set solver settings at the sheet level It will be available in 25.2.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.