Separate your stuff!

So, here is another new thing in ooo-build trunk that I just checked in. This may be particularly of interest to users in English-speaking countries.

This new option page named Formula lets you configure separators in your formula expressions. This comes in handy when, for instance, you want to separate your function parameters by commas (,) instead of semicolons (;) so that, instead of typing


you could type


like you are used to if you are coming from the other major spreadsheet application from the certain big company on the west coast of the US.

Likewise, you can also change the column and row separators for in-line arrays. Previously, an in-line array used semicolons (;) as the column separators and the pipe symbols (|) as the row separators, so a typical in-line array expression looked like this


for a 5 x 2 matrix array. By changing the column separators to commas (,) and the row separators to semicolons (;), the same expression will look like this


Now, the formula syntax option was there before I checked this piece in, but it was in the Calculate option page. I moved it to the new Formula page because I felt that those two configuration options really should belong together.

Anyway, this is probably good news especially for users in the English locales, where people are most used to having commas as the function argument separators but are frustrated by Calc because Calc forced them to get used to using semicolons to separate the parameters. Well, frustrate no more. :-)

Of course, those who prefer Calc’s conventional separators can revert back to them since they are configurable. So, these is no loss for the old-time Calc users who just want the old separators back. ;-)

Hiding data from chart

Here is something new in ooo-build trunk. You can now hide arbitrary data points in chart if their source cells are hidden in a spreadsheet document. Here is the proof:

There is now a new check box in the Data Series Option page, to select whether or not to plot data from hidden cells. By default, chart plots all data points regardless of cell’s visibility (which means the check box is on by default). By un-selecting the check box, the chart now only plots data from visible cells, and every time you show or hide a part of the source data range, the chart gets updated.

The upstreaming effort is underway in the koheichart01 CWS. I’m trying to squeeze this into 3.1, but because of the limited availability of QA resource, it might likely slip into 3.2. I’m still trying to find someone who can QA my CWS, but let’s see what happens.

scsolver code documentation

Inspired by the on-line OOo code documentation that Thorsten put together, I have decided to do the same for my humble scsolver project. I’m pleasantly surprised how good of class diagram that doxygen generates with GraphViz, since the last time I tried doxygen the diagram was very minimalistic only because I didn’t have GraphViz available on my system. Still the digram looks a little too noisy for my taste, but it’s very impressive.

Encrypting an Excel document with password from Calc

The feature of exporting an Excel document encrypted with a password has landed! Now you can save a spreadsheet document as an Excel document with password protection and open it in Excel with that password. The proof is in the following screenshot (the colored emphasis is mine):

Saving a Calc document as Excel with password protection (with encryption)

This feature is now available in the go-oo version of OOo. This also means that, when you edit & save an existing Excel document that has already been encrypted, it will get saved encrypted. Previously when you did this, Calc would save it un-encrypted and you would lose your password protection, which was not good and was a deal breaker for a certain segment of users.

The upstream effort of this feature is underway in the scsheetprotection02 CWS. That CWS also contains another enhancement for sheet and document structure protection (sounds similar but totally unrelated to this document encryption feature), and as soon as I take care of upstreaming that enhancement, I can push the whole CWS for upstream integration. The bad news is that, because that requires a change in the ODF file format & UI change, it will probably take some time before it can be integrated upstream. But I’m making slow but steady progress there, so I’ll keep you guys updated.

Meanwhile, please test the go-oo version for this encryption feature to see if there is any document that Calc fails to encrypt properly (that means Excel can’t open it), and report any bugs to us. I’ve done my own testing, but it’s never as good as many other users testing it. So, thanks!

P.S. Actually this feature has been available for at least two months in the 3.0 branch, but I wanted to see this bug fixed before writing a blog about this so that I could take a pretty screenshot with all texts displayed.

Calc Solver release

I just released a new version of Calc Solver after more than a year since the last release. A lot of effort has gone into this release mostly to re-package it as a true UNO extension, and also to make it available for the Windows version of OO.o beginning with this release.

The UI has been localized for French and Japanese, thanks to Laurent Godard and Kazunari Hirano. Laurent also helped me on various UNO related issues, so I would really like to acknowledge his help. Thank you Laurent. :-) The system language should be automatically picked up and the appropriate translation texts should be displayed for English, French and Japanese. If this doesn’t work for you, please let me know.

The ride was quite bumpy, however, to get Solver to build on Windows. Since this was my first attempt to build anything non-trivial on Windows, I had to spend a few days (and nights) studying how the MSVC compiler works so that I could build a DLL. There was also an issue with multi-thread vs single-thread libraries, so I had to manually select the default libraries to be all single-threaded for the Solver as well as the lpsolve code. Not to mention I didn’t know how to set up a build environment since GNU make in cygwin didn’t work too reliably due to file path separator and the driver letter issues. In the end, I came up with a custom DOS batch script with everything hardcoded to semi-automate the build process, but that’s far from being elegant. I’m just wondering if there is any better way to set up a build environment on Windows… Question: what do Windows developers use these days to build C++ projects?

On the Linux side, the Solver extension installed and worked fine, but I’ve experienced a major problem with installing it in the Extension Manager UI. But as long as it’s installed from the command line using unopkg, it works fine. I still haven’t figured out why installing with the Extension Manager caused a problem but installing with unopkg didn’t.

Oh I almost forgot. If you use Go-oo version of OO.o (aka ooo-build) or any variant of it with my Solver already included, you don’t need to install this extension. It’s already there in Tools – Solver.

Anyway, enough talk. Enjoy! :-) And please report me any problems you may experience.

Constraint parsing bug

One avid user of my Calc Solver from Belgium found a rather serious, and a little embarrassing bug and brought it to my attention (thank you, Ludovic!). It was the way I convert a constraint formula from the spreadsheet format to the internal model, which utterly fails to do it correctly when the left-hand-side cell contains a constant value. I need to fix this sooner rather than later since I believe this is rather a severe bug that should not be left in the code knowingly (now that I know it…).

So, I’ll spend my next available time trying to fix it, and put it into ooo-build first. Then bring the fix to scsolver02 cws afterwards. I’ll have to hold off the upstream integration work for now.

Solver new snapshot available

I’ve uploaded a new snapshot of Solver up on the usual place. The difference from the last snapshot is that the Solver now uses lp_solve as the backend LP optimizer. lp_solve is quite a robust optimizer currently developed and maintained by Kjell Eikland and Peter Notebaert. It has an excellent track record with users across many industries, so if you have a model that my Solver failed to solve previously, try out this snapshot to see if it will do a better job for you.

Now that the UNO package version of my Solver is finally out after a long 4-month hiatus , my next immediate task is to work on integrating this lp_solve-based Solver into ooo-build. For this, I will take a different integration strategy.

To recap, the strategy I took to implement the lp_solve-based Solver (this snapshot) was to add a UNO layer to lp_solve to turn it into a UNO library, and add a UNO hook to the main Solver component to glue the two separate components. This strategy was necessary despite its over-complexity and obvious overhead because, currently, it is not possible to include a shared library into a UNO package unless it provides a UNO service, or the package registration will simply fail. This is because OO.o expects every library included inside a package to export a certain set of callback functions for component registration, or it will balk at it.

The integration into the build, however, will be much simpler because there is no need for component registration. So I can simply have the Solver core library dynamically link to it and avoid the overhead of UNO. The downside of this is that I have to write additional code for it ;-), but I expect it to take only a minimal amount of effort.

First scsolver binary package after ooo-build integration

Since not everybody who wants to use my solver has a version of OO.o derived from ooo-build, I have made available a separate binary package that can be installed on a regular OO.o. There is no functionality change since the last snapshot I uploaded here on my website, so if you are having trouble solving your model with my solver, sorry, this snapshot is not gonna help you.

Because now I work directly off of ooo-build cvs, the new changelog is located here. The old changelog is no longer maintained.

Update on what I’ve been up to these days

I haven’t been active for this past month as I was a bit preoccupied with my personal life. First of all, I’m in the process of career transition, moving from working in natural science field into software engineering. As a result of this, writing code is no longer just my hobby activity but has become my professional career as well. Over the course of years of my involvement in, I have come to realize that software development is something more than just what I want to do in my spare time, and something I want to devote my entire professional life to. So, this transition is a huge step in the right direction for me.

Unfortunately, this has kept me busy for the past month or so and I couldn’t spend much time on developing solver. But I’m hopeful that I will start being active again real soon (and no, I won’t be getting paid for working on, so, that part will still remain my hobby activity even after this career change). Right now, my priority is to have my solver code integrated into ooo-build, and prepare for possible future upstreaming of this feature. Since my CVS account is not set up yet, Michael was kind enough to do the uploading for me, and to take a look at my code and make a few suggestions for improvement with a patch.

After the integration into ooo-build is complete, here is the list of things I am planning to work on (not in this particular order):

  • Integration of bounded revised simplex – I’m convinced that certain types of models are best solved using this algorithm, while the existing revised simplex is still needed for all the other types.
  • Support for solution with negative decision variables – the simplex algorithm does not allow a solution with negative decision variables by design. But there is a simple technique you can use to work around this limitation; split each variable into two variables and solve the model, and perform subtraction afterwards.
  • Consider perhaps using an existing optimization library, such as lp_solve – while I still believe that writing and improving my own algorithms without external dependencies will be ideal in the long run, sometimes using an existing library may be worth looking into, especially when the authors of lp_solve have already spent a lot of time and effort on its development and improvement. Several people have already suggested lp_solve, so I should definitely look into it.
  • Options dialog – for algorithms selection, precision, positive variables etc.
  • Thread issues – still on the drawing board.