Tag: calc

Custom sort in DataPilot

Kohei Yoshida

Just checked in this piece to the master branch of the Go-OO repository, to support sorting of DataPilot’s field members using custom sort lists. I’ve extended the popup window I wrote for the hide field members functionality to provide this additional sorting functionality. The result is the following popup window:

In the upper half of the window I’ve added a menu-like control, with the custom sort lists being provided in the submenu. The UI is fully functional, but still a bit rough around the edges. The custom sort list submenu, in particular, may need some additional work to handle a large set of custom sort lists, a sort list that is very long, or stuff like that. But as long as your sort list is in modest size, it should work just fine.

This feature didn’t make it in to 3.1 since we are in a stabilization phase for 3.1. But as soon as we branch master for the stable 3.1 branch, I will enable this feature in the default build in the master branch.

Traversing to precedents and dependents

Kohei Yoshida

Just checked this piece into the master branch of the go-oo repository. It allows traversing to precedents and dependents of a cell by Ctrl-[ and Ctrl-] key strokes, respectively. It is similar in concept to the existing Detective functionality, but while Detective graphically shows the precedents and dependents of a current cell, this new functionality physically moves the cursor to the precedent and dependent cells. Similar functionality already existed in Excel, so this is one of those interoperability features and, for some spreadsheet users, the ability to jump to precedents/dependents is apparently very important for their productivity.

No feature is complete without screenshots. So, here they are. :-)

In the first example, the cell currently selected contains references to three cells and one cell range in its formula expression. I’m showing the precedent traces here just to show their relationship visually.

When you hit Ctrl-[, it highlights all its reference cells (a.k.a precedents) and moves the cursor to the first precedent.

Once the cells are highlighted, you can easily navigate through the highlighted cells by hitting the Enter or Tab key.

There is one caveat. When the expression includes references to cells outside of the current sheet, the ones that are not on the current sheet are ignored. The only exception to this rule is when the first reference points to a cell in another sheet, in which case it jumps to that external-sheet cell while the rest of the references are ignored even if they are on the current sheet. If that reference is in another document (i.e. external reference), it opens that document then sets the cursor to the referenced cell position provided that the document is available at specified location in the file system.

Traversing to dependents also works in a similar fashion. Consider the following example

where current cell is referenced by multiple other cells. Again, I’m showing the dependency traces to display their relationship graphically. When you hit Ctrl-], it highlights all its dependent cells and moves the cursor to the first dependent cell.

Similar to the precedent jump, there is a caveat; when the cell is referenced by cells on multiple different sheets including the current sheet, only those cells on the current sheet are highlighted and the rest are ignored. But unlike the precedent jump, there is no exception to this rule.

That’s it folks! I hope you find this new functionality useful. And as always, please report back any problems you may encounter so that I can fix them. Thank you very much, ladies and gentlemen. :-)

Some minor enhancement with DataPilot

Kohei Yoshida2 Comments

Just checked into the master branch of go-oo repository (oh BTW we just switched our repository from svn on gnome.org to git on freedesktop.org) is a minor enhancement in DataPilot, to allow the users to filter results by field members directly from the field buttons in the table output. The following screenshot will tell you the change I just made:

The new field buttons also provide visual feedback on fields that are filtered; the same way the autofilter buttons already do. This way it’s visually obvious to the users which fields are currently filtered.

I call this a “minor” enhancement, simply because it doesn’t provide a new functionality per se; the same filtering functionality was already present but hidden deep beneath multiple layers of dialogs. You needed to go from the datapilot layout dialog (the main dialog), open up the field dialog for the field you want to filter results by, and then finally the field options dialog to get to the Hide items list in that dialog. This enhancement will push that functionality up-front and make it more accessible to the users, along with some visual feedback.

Anyway, this one made it into 3.1, so testing and feedback is greatly appreciated. :-)

Coloring tabs in Calc

Kohei Yoshida7 Comments

I just checked in to go-oo trunk the latest patch from Daniel Watson that enables coloring of sheet tabs in Calc. With his latest patch, the tab color dialog looks more compact and polished, which I definitely prefer over the previous version.

In case it’s not obvious how to use this feature, here are some screenshots to show how.

In the context menu on the sheet tab, there is now a new entry called Tab Color.

Select that menu to bring up the color palette dialog.

Select a color and click OK. The sheet tab now has a sliver of color at the bottom. When you switch to another sheet, you’ll notice that the tab you just colored is now filled entirely with that color. And when you color all of your tabs, you’ll get something like this:

Pretty cool huh?

BTW Daniel did this almost entirely on his own, with just a teeny bit of help from Eric Bachard and myself. He is very thorough and the quality of his code is pretty high. He maintains the spec for this feature right here. He is also anxious to get this feature upstreamed, and judging by the kind of reaction he is getting from the upstream Calc team, I’m pretty sure this feature is on a fast track to upstream acceptance. ;-) Great job Daniel!

Oh, one last thing. Please try out this feature and test it out.

It’s text when it’s quoted, stupid!

Kohei Yoshida4 Comments

Ok. This is another one I just checked into ooo-build trunk. It’s actually a very minor enhancement, but some people may find this useful.

What I did is to modify Calc’s csv import filter to provide the users an option to always import quoted fields (or cells) as text. Here is a screenshot of the dialog with this new option:

Hopefully what it does is self-evident. If not, here is an example.

Let’s say you have a csv file of the following content:

"0-0" "1-0" "2-0" "3-0" "4-0" "5-0"
"0-1" "1-1" "2-1" "3-1" "4-1" "5-1"

When you import this file into Calc, here is what you get with the current version:

As you can see, most of the fields got converted to dates. You get the same results even if those individual cells are not quoted, because Calc’s current csv import filter doesn’t make any distinction between quoted and unquoted fields in terms of data types.

When you import this file using the new option, you get this instead:

Now, all quoted fields are imported as text, with no surprise conversion. Plain and simple. :-)

Separate your stuff!

Kohei Yoshida2 Comments

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

=SUM(A1;B1;C1)

you could type

=SUM(A1,B1,C1)

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

={1;2;3;4;5|6;7;8;9;10}

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

={1,2,3,4,5;6,7,8,9,10}

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

Kohei Yoshida2 Comments

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.

This is valid C++ code?

Kohei Yoshida1 Comment

My compiler reported a build error in the following code block today.

long ScDPOutput::GetHeaderDim( const ScAddress& rPos, USHORT& rOrient )
{
    SCCOL nCol = rPos.Col();
    SCROW nRow = rPos.Row();
    SCTAB nTab = rPos.Tab();
    if ( nTab != aStartPos.Tab() )
        return -1;                                      // wrong sheet
 
    //  calculate output positions and sizes
 
    CalcSizes();
 
    //  test for column header
 
    if ( nRow == nTabStartRow && nCol >= nDataStartCol && nCol < nDataStartCol + nColFieldCount )
    {
        rOrient = sheet::DataPilotFieldOrientation_COLUMN;
        long nField = nCol - nDataStartCol;
        return pColFields[nField].nDim;
    }
 
    //  test for row header
 
    if ( nRow+1 == nDataStartRow && nCol >= nTabStartCol == nCol < nTabStartCol + nRowFieldCount )
    {
        rOrient = sheet::DataPilotFieldOrientation_ROW;
        long nField = nCol - nTabStartCol;
        return pRowFields[nField].nDim;
    }
 
    //  test for page field
 
    SCROW nPageStartRow = aStartPos.Row() + ( bDoFilter ? 1 : 0 );
    if ( nCol == aStartPos.Col() && nRow >= nPageStartRow && nRow < nPageStartRow + nPageFieldCount )
    {
        rOrient = sheet::DataPilotFieldOrientation_PAGE;
        long nField = nRow - nPageStartRow;
        return pPageFields[nField].nDim;
    }
 
    //! single data field (?)
 
    rOrient = sheet::DataPilotFieldOrientation_HIDDEN;
    return -1;      // invalid
}

In particular, my compiler didn’t like the == (equality operator) in nTabStartCol == nCol in the 3rd if statement block from the top. Looking at the if statement before and after that, you’ll probably say “yeah, looks like that ‘==’ was supposed to be &&, so what’s the surprise?” Well, the thing is, this piece of code has not changed for at least a few years, which means it was compiling just fine up until today (though it may have caused a bug somewhere…). And even today, it compiled fine before I made a few changes that were not related to this method, and I didn’t modify this method itself at all.

I have to wonder, why this code block compiled fine up till today, and what change of mine triggered the compiler to complain all of a sudden if the method itself is unchanged…. :-/

Update on scsolver

Kohei Yoshida2 Comments

I’ve made several updates on scsolver that I think is worth mentioning.

First, I’ve finally put an option to solve to a specific value in addition to the minimize and maximize options. But this only works for linear programming models. For non-linear models, if you select to solve to a specific value, you’ll get “Goal not set” error message.

Also, I’ve noticed that the integer constraint option was not working at all; even when this option was selected, the integer constraint was not enforced. This is also fixed. It was just a simple silly mistake I had overlooked which caused this bug.

As far as the non-linear programming backend algorithm goes, I’ve put quite some effort into improving the algorithm a bit. Unfortunately not all of that improvement is available from the UI yet, but the line search algorithm used by the default quasi-Newton algorithm has been switched to a new one that uses quadratic fit line search. This one is a lot more robust than the old algorithm, which was basically a hand-crafted version of golden section search and was quite limiting. The quadratic fit also scales better with search distance. So, hopefully this will improve the overall stability of the algorithm a little. Having said that, writing a robust non-linear algorithm is a huge task, so I have no doubt in my mind that I still have a long way to go.

I will also drop the binary releases of scsolver on Linux, since it’s not easy to build a binary that works universally across different flavors and versions of Linux distros. Also, building from the sources is quite easy on Linux these days. If you have any doubt, see this page for the instructions on how to build it to see if you agree with me on it being “easy”.

Another way to get scsolver, if you are comfortable building the OOo itself from the sources, is to build ooo-build with the --enable-scsolver option. With that, you’ll get the scsolver package already integrated in Calc in place of Calc’s default Solver. So, there is now quite a few ways to get scsolver working on your system. By the way this build option also works on Windows.

Ok, I think that’s all the updates I need to mention. Now, what’s coming next? Well, I have a few things in mind. First, I’d like to re-organize the UI a little, to make it easier and more intuitive to switch between different algorithms. I’m well aware that no single algorithm can solve all models of different shapes and sizes, so I believe it’s important to provide different algorithms for different problem types, especially for non-linear problems. This also includes adding more specialized per-algorithm options to control its run-time behavior.

Another thing I have in mind is some sort of output window to show the running status of the iteration. This is useful for the backend algorithm to give real-time feedback of what’s going on.

I’d also like to do something about the terrible focus problem that the UI is suffering from right now. But quite frankly I don’t have a good idea on how to fix it yet. That may be a limitation of the current UNO AWT API. The worst case, I could reduce the number of child dialogs or avoid having multiple dialogs altogether. Anyway, this is in the back of my mind.

Of course, I will continue working on improving the non-linear algorithms. Currently the non-linear algorithms don’t deal with constraint problems yet, but I’ll get to that eventually.

Encrypting an Excel document with password from Calc

Kohei Yoshida1 Comment

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.