Wednesday, 02 Apr 2008
Wednesday, 02 Apr 2008
1. Convert text to columns
With this feature CSV data inside cells can be transformed into columns directly. It uses the already existing Text import dialog and transforms CSV data inside cells into multiple columns. Example: A1 holds '1,2,3,4". After using this feature, 1, 2, 3 and 4 are split over cells.
Start this feature with the menu Data>Text to Columns.
2. Insertion Mode for Cells
Cells
in spreadsheet can be moved, copied, or linked Select one or more
columns or rows, or a range of cells, and hold down Alt while moving
the selection with the mouse. When the mouse button is released, other
cells/columns/rows are moved left or down, to insert the selection.
Before this feature, target cells always were overwritten.
3. Enhanced Data Input
Enter key returns to the column where the input started, one row below Entering data row wise is made easier. The Enter key works like a carriage return-line feed on a type writer and allows to return to the column where the input started, but just a row below the current row.
4. Enhanced Formula Input
Formula input: "+" and "-" can also be used to start Most professional spreadsheet users do use the number pad for inputting data to save time. So it makes sense to allow to enter a formula not just by entering a "=" or hitting the Function button in the Formula tool bar. Therefore "+" and "-" can also be used to start entering a formula. This improves the usability and the speed when creating formulas in a spreadsheet. Furthermore this can be used as a little calculator.
5. Individual zoom level per sheet
Each sheet in a spreadsheet document can now have its own zoom level.
6. Improved AutoFilter Behavior
Choices
are clearer grouped and based on result of filtering in other columns
The AutoFilter function allows to filter data in a spreadsheet by one
criteria per column. This feature allows to run a quick analysis of
data. Two things are new: there is a clearer grouping of static filter
choices. And if in one column a filter is set, the filters in the other
columns only show the options that are available in the visible rows.
7. Improved DataPilot
Manual Sorting It's now possible to rearrange items in a DataPilot field via Drag&Drop or Copy&Paste. The default sorting for new tables is "Ascending", it is changed to "Manual" when items are moved.
Double-click in Datapilot cell provides calculation data of that cell When a cell within the data field is double-clicked, it inserts a new sheet containing a subset of rows from the original data source that constitutes the result data displayed in that cell. For instance, when the data field function is selected to be SUM, then the number that is shown in the data field cell must be identical to the sum of all the data field values in the constituent rows displayed in the inserted sheet.
8. Improved Print Dialog
The Print dialog in Calc makes it easier to choose what part of the spreadsheet has to be printed. The option 'print only selected sheets' is now on that dialog.
Furthermore the 'Sort' option is defaulted to on. This makes it easier to print multiple copies of a document.
9. Improved Print Preview
PageUp and PageDown keys scroll continuously through the print preview. Ctrl + PgUp/PgDn scroll to top of next/previous page.
tags: calc openoffice.org spreadsheet usability user-experience
Tuesday, 04 Mar 2008
With this year's Google Summer of Code on the horizon, it's time to mention an extension that started as a Summer of Code project last year: R4Calc, the integration of R into Calc, done by Wojciech Gryc, now also linked from the extension repository. It can put results into cells, or return a graph image. It contains a set of pre-defined tools, and it can easily be extended using simple text files. For example, this definition
__GUI: |
is all you need to show this dialog

and create the output shown below.
tags: calc gsoc openoffice.org spreadsheet
Monday, 03 Dec 2007
One of the highest voted issues in Calc is #8811# (Allow multiple users to edit the same spreadsheet through workbook sharing), which has currently 143 votes. After some evaluation we decided to give this feature a try. Before we start with the implementation I'd like to present some conceptual ideas.
Concept
The basic idea is to allow multiple users to edit a shared spreadsheet document simultaneously. The concept is rather simple and a non-server based solution, that means no document versioning, no access control, no messages, tasks and discussions. The granularity for sharing is the whole spreadsheet document.
A shared spreadsheet document will be identified by its shared status. The shared status can be stored as shared property in the document settings, as top level entry in the document file or in a separate file next to the document file.
If a user opens a shared spreadsheet document a temporary copy of the document file is created. The user works on the temporary copy, that means no exclusive write lock is required while editing the document. When the user saves the document, all changes made in the temporary copy will be merged into the shared file. During this merge process an exclusive write lock on the shared file is required. If there are merge conflicts during the merge process, e.g. two users have edited the same cell, the merge conflicts must be resolved interactively by the user. Probably the existing 'Accept or Reject Changes' dialog can be reused.
Merge Process
The whole merge process is based on the existing change tracking functionality, that means while editing a shared spreadsheet document change tracking must be enabled. Each document, that means the shared file and all temporary copies have its internal change tracking list, which consists of all recorded change actions. When merging, the change tracking list of the temporary copy must be synchronized with the change tracking list of the shared file. In order to achieve this the following steps are necessary for merging:
Although this procedure may look complicated it guarantees that the change tracking lists stay synchronized.
File Locking
As the evaluation showed, file locking is not working reliable cross platform, e.g. it's possible to have a write lock on the same file via Samba from Windows and via NFS from Linux/Solaris. As this problem won't be fixed in the short term on the operating system level, we decided to implement file locking on document level ourselves. The idea is to write a lock file next to the document file, which contains the information which user has locked the file.
Restrictions
As the whole merge process is based on the change tracking, only those change actions can be merged, which are recorded. Unfortunately when change tracking is switched on some of Calc's functionality is not enabled (e.g. Sorting, DataPilot, etc.) and some of Calc's functionality is enabled but not recorded (e.g. cell formatting and drawing layer actions) and therefore lost during the merge process. The main problem seems to be, that the cell formatting information gets lost during merging. This can hopefully be improved in future
versions.
Wednesday, 28 Nov 2007
To plan implementation of necessary changes in the Calc formula compiler and interpreter for the upcoming OpenDocument Format Formula specification (ODFF aka OpenFormula) I took a look at several project planning tools producing Gantt charts and the like. I discarded one after the other as being not suitable for me until I came across TaskJuggler, which for sure is different.
TaskJuggler doesn't make you click thousand dialogs, instead it takes some sort of programming language as input and compiles it into HTML or XML reports, and does CSV and iCalendar output too. Gantt charts are also available in the UI, but more as a by-product. The important thing is it really calculates, taking all dependencies, time restrictions, milestones and resource leveling into account, has a projection mode where already existing bookings of resources go in and the plan is adjusted based on the information available, not just some "fire a plan and forget" tool. It pays to read the fine documentation though, otherwise you will get stuck at some point or wonder why it does things like it does. And it sure does need some time to get acquainted with. Important is to use the latest stable version available, which currently is 2.4.0; I previously tried 2.2.0 that came with the distribution and has some nasty bugs, so I rolled my own. Unfortunately the UI that also produces the Gantt charts needs KDE, so the tool is somewhat bound to certain platforms, but the task compiler itself is independent and may also be run standalone. The UI is handy though, also for managing the reports and editing task, especially if the compiler mocks about under or over specified tasks or impossible dependencies due to time restrictions or oversights, where it jumps to the source location.
Anyway, to shorten a long story: the outcome is an ODFF implementation schedule for OOo3.0 now uploaded to the wiki, together with some explanations.
It clearly points out that the Calc team could need the helping hand of one more experienced developer to work on some tasks. A great opportunity, as many tasks are independent of each other and can be addressed individually, with different degrees of difficulty, ranging from very simple to more sophisticated. Would you like to join in?
Friday, 16 Nov 2007
Currently, several solver components for Calc are available (with different capabilities and different licenses), and OOo 3.0 is scheduled to contain another one in the default installation. Some areas, like non-linear models, are still open, so more solver implementations may appear in the future.
Instead of having a variety of “Solver xyz” entries in the “Tools” menu, each with their own dialog, it seems better to select between the implementations in a single dialog. So that's what we are doing: There's going to be a UNO service “com.sun.star.sheet.Solver” for the core implementation, without UI, and a dialog within the “sc” module to specify model parameters, and select between different service implementations. Future components will be able to implement just that service, and be called from the common dialog. The dialog design isn't final yet, but it will look similar to this (the images in the specification will be updated if the design is changed):
The “Options” button will open an options dialog where you can select an implementation and the options needed for that implementation.
The Solver service will be something like this, plus an XPropertySet for the options (note: even more preliminary than the dialog design):
enum SolverConstraintOperator
{
LESS_EQUAL,
GREATER_EQUAL,
EQUAL,
INTEGER,
BINARY
};
struct SolverConstraint
{
com::sun::star::table::CellAddress Left;
SolverConstraintOperator Operator;
any Right;
};
interface XSolver: com::sun::star::uno::XInterface
{
[attribute] XSpreadsheetDocument Document;
[attribute] com::sun::star::table::CellAddress Objective;
[attribute] sequence< com::sun::star::table::CellAddress > Variables;
[attribute] sequence< SolverConstraint > Constraints;
[attribute] boolean Maximize;
void solve();
[attribute, readonly] boolean Success;
[attribute, readonly] double ResultValue;
[attribute, readonly] sequence< double > Solution;
};
service Solver: XSolver;
|
As an added benefit, since the service implementation is available from outside the solver dialog, you will be able to easily use solver functionality from macros or other components.
Implementation will be in the child workspace “calcsolver”, and there's also a wiki page. Feedback is welcome on the sc-dev mailing list.
tags: api calc openoffice.org spreadsheet
Thursday, 26 Jul 2007
In Calc, validity settings that are defined for a cell range are verified for cell input before actually writing to the cell. Because of this, a “custom” option, with a formula specifying if the input is valid, isn't available – the formula wouldn't see the new value yet. The good news: In many cases, it isn't needed. With the addition of dynamic ranges, “cell range” validation can now (in version 2.3, or recent developer builds) be used to prevent duplicate entries in lists. This is possible because the formula can return an array as well as a cell range.
For example, if the named cell range “possible” contains the list of allowed values (A2:A7 in the screenshot), and “input” is the range across which the entries should be unique (C2:C7), the formula IF(COUNTIF(input;possible);"";possible) entered as “Source” for “Allow: Cell range” will allow only those entries that aren't already there. As a benefit, you'll also get them listed in the drop-down window, which “custom” validation wouldn't do.
tags: calc openoffice.org spreadsheet
Monday, 23 Jul 2007
The OOo issue tracking system (IssueZilla) already contains many usability related issues around Calc and there are more input channels beside IssueZilla. So the problem is not to find usability issues to fix but rather to decide which are the most relevant ones with a good relation between effort on the development side and value for the user.
Therefore we have created a list in the wiki to list all
these sources (i.e. top voted issues in IssueZilla, customer feedback
and other community sides) and started working on these issues for
the 2.3 release of OOo.
Furthermore we have added a list of ux issues we want to address for OOo 2.4 to this wiki page last week.
I will post updates on the usability improvements made, if a new version of OOo has been released.
tags: calc openoffice.org spreadsheet usability user-experience
Friday, 06 Jul 2007
The Calc Ease of Use iTeam is currently attacking the most annoying usability issues in Calc. Our first issue was Calc's AutoSum feature.
AutoSum is used for summing up cell values automatically. If the user sets the cursor to the next empty cell of a row or column, which contains a series of values, and presses the Sum button in the formula bar, Calc automatically sums up the values and inserts the result as a sum formula at the cursor position.
A lot of users complained about Calc that it's not possible that the cells which should be summed up can be specified by the user, e.g. by marking the cell range with the mouse. This problem has been addressed in CWS calcautosum, which has been integrated into SRC680 m217 and will be available in OpenOffice.org 2.3. You can also download the recent developer snapshot build and give it a try.
For a first impression of this feature, please see the screenshot below.
More information can be found here.

Watch out, there's more to come.
Thursday, 21 Jun 2007
With integration of CWS dr54 a quite small but very effective change regarding updates of references to external documents when loading a spreadsheet will become available. In a customer's test case document this brought down loading time from over 15 minutes to 7 seconds, which is by factor 128 or more.
Some details: imagine a spreadsheet with cells listening to large ranges of external references such that there are 35000 formula fragments listening to an identical range (A) of 50000 cells, another 9000 formula fragments listening to an identical range (B) of 50000 cells, and yet another 9000 formula fragments listening to yet another identical range (C) of 50000 cells. After having loaded the document the user is asked whether to update data of the external references. If answering yes the external data is loaded and the relevant parts replace the already existing cached data.
The replacement has the consequence that for each changed cell content the change is broadcasted to the listening cells, and although a resulting action is taken only if the target cell wasn't already notified, the overhead of the broadcasting mechanism was responsible for the massive slowdown. To stick with the numbers from above for simplicity:
Broadcaster Actions
A: 50000 * 35000 = 1,750,000,000
B: 50000 * 9000 = 450,000,000
C: 50000 * 9000 = 450,000,000
-------------
2,650,000,000
So, even if for range A after the first cell change was broadcasted all listening formula fragments were dirty, quite significant portions of the algorithm were still ran through for the remaining 1,749,999,999 notifications. Same for ranges B and C. This is where I placed the scalpel to cut things off and introduced a caching mechanism that remembers ranges formulas are listening to when broadcasted during a certain action. This gives:
Broadcaster Actions
A: 1 * 35000 = 35,000Cache Lookups
B: 1 * 9000 = 9,000
C: 1 * 9000 = 9,000
-------------
53,000
A: 49999 * 1 = 49,999
B: 49999 * 1 = 49,999
C: 49999 * 1 = 49,999
-------------
149,997
Wow! Great!
Friday, 01 Jun 2007
With Michael Meeks' excellent work on string "intern"ing, it basically takes only a one-line patch to share the string representations of Calc text cells that have the same content. See issue 63500. The change is in the CWS "dr54", it will take a while before it's available in a developer build. Until then, here's a screenshot to enjoy. It shows the memory usage of two m210 Linux builds, each with "01_text_large.ods" loaded (from the performance test documents, a file with much duplication). Guess which one has the patch.
tags: calc openoffice.org performance