 Okay, hello everyone. My name is Rafael Lima, and in my presentation today, I will tell you how LibreOffsCalc can now save server settings to the file. I will also tell you about a few more improvements that I have been making to the server, and a few other features that I plan to implement in the server for possibly the next release cycle. So, first of all, for all of you, if you don't know me, I am a professor at the Federal University of Technology in Paraná, Brazil. I teach, I do not teach computer science. I work for the industrial engineering department where I am responsible for the subjects of operations research, so therefore my interest in the server. I also teach statistical process control and also other subjects, such as logistics and production planning. So, I also do research using about machine learning, so how we can use machine learning in areas of industrial engineering, and also that's why I have some interest in macros and Python and all this stuff. And I have been a LibreOffs contributor since 2020 and a TDF member since 2021. So, here's an outline of my presentation. First, I will start talking about why the server matters. And then I'll move on to explaining what the problem is about not saving the server models. Then I will address the bug 38948, which was the bug that we were, where we were discussing this issue. Then I will talk about some ideas that I had to fix the problem, and then I will describe what was the actual fix that got implemented. To finalize, I will present some limitations that we have and then I will list some other improvements that I plan to implement for the server. So, why the server matters? So, the server is largely used in universities, both for teaching and also by companies to optimize real problems. So, universities we use linear and non-linear models, both for teaching and also for research. And in organizations, many real-world problems are modeled using linear and non-linear optimization languages. So, some examples are. So, we have production planning models, production scheduling. We have lot size problems, transportation problems, facility location, and vehicle routing problems just to name a few. So, in companies it's usual to have, we have some spreadsheets where someone has already set up the model so that the users can only input the data and run the model to get the optimized solution. So, one important thing about the server in the LibreOffice is that we offer very good server engines, especially for linear programming models. So, we offer out of the box LP Solve and also CoIMP, which are very good solvers. And they are far superior to the options that Microsoft Excel offers. So, if we use Microsoft Excel as an example, their server is limited to 200 decision variables, which is a small amount if you compare to the complexity of real words problems that we need to optimize with solvers. So, 200 decision variables is too few. And in LibreOffice, the solvers that we offer have no limitation. The only limitation is the model complexity, which is not something that LibreOffice would be to blame in this case. It's just the model that might be too complicated. So, we have very good options. We also have non-linear solvers, non-linear engine solvers that are good enough. They're compatible or competitive with other options that are available in other offsuits. So, this is one important aspect that we need to emphasize when it comes to solver in LibreOffice. Our engines are very good. So, why not saving the models to the file is a problem? So, in the university, one common problem is we teach a lecture or maybe we give a test to the students so they have to prepare a model, save the file and hand it to the teacher so then we can give grades to these files. And not saving the solver model to the file defeats this purpose, so it's impossible to use the solver for this use case. So, this is my pain because I am a professor, so I have this exact problem. But I've seen companies have the same problem because in companies, when they want to use the solver, they have to save it to the file so that they do not have to prepare the file every time to have things more automated. So, I have here a few screenshots of many requests in Bugzilla and also questions in Ask LibreOffice where users complain about the lack of saving the solver settings in LibreOffice. I just want to share an experience that I had because in my university, originally, we only had LibreOffice as our office suit in the entire university. But we had some professors that were pushing the university into buying Microsoft Office licenses and the lack of saving solver settings to the file, this item was one of the excuses that were in the report to push the administration to buy Microsoft licenses. So, this was said because I was basically forced to use Microsoft Excel solver for my operations research lectures up to now. So, because now it's possible, finally possible to save solver settings. So, now let's discuss a little about the bug, bug 38948, which was the bug report that concentrated the discussions and proposed solutions for this issue. So, it was quite popular, but not so much. It was reported a long time ago in 2011. It had four duplicates and 16 people copied on this bug report. And basically, people complained about two main issues. First, the lack of saving support and two, the need for compatibility with Excel files because it is expected that if a user creates a file in Excel with a solver model, it should be imported in LibreOffice and vice versa. So, in the bug ticket, many proposed solutions emerged. So first, we discussed, there were some discussions about the need to change ODF specifications to accommodate for solver settings. But this sounded too complicated. And to be honest, at the time, I think I wouldn't be able to do that. What people did to go around this issue, what other users did was create solver models using macros because it is possible to create a macro that run a solver model. So you can use the solver service and then you can create a macro and save the macro into the file. So then you are basically saving the solver model to the file, but in a very complicated manner because the macro is a bit complicated. Even for simple models, the macro is very extensive. So I have a few examples. I'm not gonna show them here, but if anyone is interested, I can show them to you. So this is what most people did to save models to the file. Then my first idea, instead of extending the ODF format, my first idea was to propose an extension that would automate the creation of these macros. So this would make it easier to create the macros and then save them to the file. I also thought about creating a separate dialogue using the basic IDE to recreate the solver dialogue and then save solver settings as an XML file inside the ODS file, which sounds complicated enough. So all these ideas were running through my mind. I never actually implemented any of these, only the macro variant here I did implement, but one day I was working on an Excel file in LibreOffice and by chance I opened the manage names dialogue. And then I saw this. I don't know if it's possible for you to see, but if you take a look at the name ranges that are here, you'll notice that they are indeed the description of the solver model. But in Excel, I remember not creating these names ranges. So then I realized that in Excel, the solver model is saved using named ranges. And this was a realization that I had that showed me the way of how I should implement this in LibreOffice as well. I don't know if you know this, but in Microsoft Excel, the solver is an extension and this is how they implemented it. Because along the way, I started realizing that many other extensions in Microsoft Excel use named ranges to store information in files. So here's just an excerpt of the workbook.xml file from an Excel spreadsheet that has a solver model. So you see that here we have a lot of defined names which is what they call it in Excel for each of the settings of a solver model. So one thing to note here is that in Excel, each tab has its own solver model. So we can have separate multiple models in the same file. So what I did is I mapped what named ranges corresponded to each portion of the solver dialog. So it's quite simple actually. So we have in the solver dialog from Excel, we have the named range that start that information. So what I did was basically figuring out what was the meaning of all the values that they used for each of the settings in the solver. And then I basically created the mapping mechanism between what was done here to the named ranges that were imported in LibreOffice. So let me go back just a bit because so why did I do it like that? So first I figured that because we have already named ranges support in LibreOffice, if I simply used named ranges as well, then we would automatically gain import and export support. And also if I used the same standard as Microsoft Excel uses for their solver models, then we would gain automatically interoperability support between LibreOffice and Excel. So this is why I chose this path. So here's just an overview of the patch. So here's the link for the patch. It has been merged some time ago. I believe it was in March. So this is just an overview of what was done. So first here we have the doc shell files because originally the solver settings in LibreOffice were stored here because we only allowed for one solver settings per file. So I removed it from here and placed it in the table file because now we can have one set of solver settings per tab. And also the other files. So here are the solver dialogue files where we basically load and save and update the solver settings from the dialogue to the object that stores the solver settings. And here is the implementation of the solver settings class which is responsible for storing to actually store the solver settings. So just to describe how it works, it's very simple. So in the SC table class, now we have a get solver settings method that returns a solver settings object if it hasn't been still created. So with this method, we can get access to the solver settings that are defined for that tab. So how does it work? Okay, so here we have a more detailed explanation. So when the user opens the dialogue, first the get solver settings method checks if the solver settings have already been read from the file. So what does it mean to read the settings? It means read the named ranges and convert them to the solver settings object. So if it hasn't been read already, sorry, if it hasn't been read already, then it is read. If not, it simply does nothing. And after the dialogue is closed, so the user changes the settings and when the dialogue is closed, the solver settings object is updated. Yeah, so this is here. Okay, so when the user closes the dialogue, the solver setting object is updated. So the patch goes on like that. It's very simple actually because after I realized what portions of the solver dialogue correspond to each named range. So the patch is now an effort of mapping which portions of the solver dialogue correspond to each named range. So here we have in this upper part, we have the main solver settings which are settings that are available for all models. And here we have in the part below, we have those settings that are solver engine dependent because not all of these settings are available to all solver engines. So I added all of them here, but not necessarily all of them will be available for all solver models, okay? So an important thing is that this part on the left are the names that are used internally by the LibreOffice solver service. And these are the named ranges used when importing and exporting the file. So, well, the main thing about this patch is that it's led to many other improvements that are required to make the solver in LibreOffice calc very competitive. The first of them is the need for support for hidden named ranges. So as I said earlier, when I first imported an Excel file with a solver model and then I realized that all solver settings were stored using named ranges, I first realized that they were actually being stored in hidden named ranges because in Excel they are, it's not possible to see them in the user interface, but in LibreOffice calc, we were capable of seeing them which is actually an interoperability problem because if in Excel a user creates a hidden range, named range, we should hide them in LibreOffice calc as well. So I created this, actually this bug report and also I have already submitted a patch that adds support for hidden named ranges in LibreOffice calc as well. So with this patch, it's now possible to import hidden named ranges from Excel and also from LibreOffice calc as well and also export. So it's important for interoperability because if in Excel or in LibreOffice calc, we want to have a range named range that is hidden from the user, we should be able to do so and import and export should work as expected. So just as an example, with the patch that is proposed here, which is by the way, not only because of the solver, but actually for named ranges. So with this patch here, I'm gonna zoom in a bit so you can see better. It's possible now to run a script like this. With this script, it's possible to hide a named range and then show the named range back, which is similar to what Microsoft Excel does because in Excel, it's only possible to show or hide named range via scripting, which is, I think it's by design because it's only meant for extension developers to use this, not for regular users. That's why you won't find anywhere in Excel's user interface an option to show or hide hidden named ranges. And from this patch that I proposed, it's only possible to show or hide named ranges via scripting. So this patch is still under review. Okay, so if any of you are willing to review this patch, I would be very thankful because it's awaiting review for some time now. So it's in Jarrett, it's from my end, it's working. And I have a few other improvements that I would like to do to the solver. So first, I would like to work on the solver dialogue user interface because I don't think that our user interface is very good, especially for large models. For example, here on the left, we have our user interface. And if we have a model that has like 10, 12 constraints, it's really hard to navigate through these constraints using a portion of the dialogue that only supports four constraints. So it's not nice if you want to see the whole model. Whereas in Excel's UI, we have something a bit, which I consider a bit better, not ideal, because here you can see more constraints in a smaller space. What I propose to do here is to have a tree view with three columns, so we can have the column headers, then we can have the left-hand side, the operator, and then the right-hand side of the constraints. I would like to propose too, in this portion of the dialogue, to allow the users to add separate objective cells to the model because both LibreOffice and Excel expect the user to select a range, then press comma, and then select another range, then press comma, and so on, which is very annoying because if you make a mistake, you have to start over. So what I will propose is to create a way where you can add each range separately, one at once. So you can have multiple objective cells ranges, but each entered individually. So these are a few improvements that I would like to do to the UI. Also a few more improvements that are still needed is first, improve scripting support because the current scripting support does not allow for us to create a script that adds the server model to the file, which I believe can be done by creating a recordable Uno command. So this is something that I'm still planning on working but I haven't started yet. Also, this is one thing that is quite easy to do because both linear solvers that we support, LP solve and coin P, they support a lot more parameters than we provide. So these parameters that the solver engines already support are easy to integrate in our solver implementations. So this is something that is easy to do and this will increase compatibility with Excel solver. So it would be better for interoperability as well. For instance, our solver does not support a very important parameter, which is the MIP MIP gap, which is probably the most important parameter that defines how much of gap do you need to achieve to say that a solution is optimal. And this is something that we still do not support. Well, in the future, but now this is something that is more to the future, I would like to integrate other solvers to Calc because we have some very important solvers that are good hobby and seplex, that are the industry standard, they are proprietary, but they offer C++ headers. So it would be possible to create an implementation of the solver service for these engines and because they are proprietary, we would need to allow the user to define a solver license so that they can run. So if the user has, for example, Grubby installed, they can set up where the license file is and then run it from within LibreOffice, which would be very good if we compare to other alternatives that exist today. So I believe that with these improvements, the solver in LibreOffice would become the best alternative among all the spreadsheet-based solvers. There are also other non-linear solvers that can be introduced to LibreOffice, for example, IPOPT, Bonn, Min and Coen, which are all open-source solvers. So we could also implement them into LibreOffice. And to finalize, I think that we should improve documentation, better describe what our solver engines are, how they are configured, what the parameters are. So these should be available in the help pages. And finally, a few limitations. Okay, so for LibreOffice 7.6, the hidden named ranges are still visible, but the patch that I proposed will probably only be available for 24.2, so these will be fixed. Also, there is a problem that I discovered this week while preparing the presentation, is that when you have a named range from Excel and it's tied to a tab instead of the whole document, when you save the file again, it gets lost. So it's a compatibility problem. I reported this bug in Bugzilla already, it has been confirmed. So this is something that needs to be worked on. And as I mentioned, another limitation is that not all parameters that exist in Excel are already supported in LibreOffice Calc, but this is something that can be easily worked on in the future. So that's it. I'd like to thank TDF for the support, for the funding for me to attend the conference, and also for everything else that TDF does, which is amazing. I also would like to thank the code reviews. So we have Thomas, we have Arkady, either I can say his name, but I'd like to thank Arkady and also Michael Mix because all of them were involved with reviewing the patch. So that's it. Thank you.