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.

Article Issue: 4040 Spec

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.

Article Issue: 7180 Spec

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.

Video Issue: 15546 Spec

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.

Issue: 20496 Spec

5. Individual zoom level per sheet

Each sheet in a spreadsheet document can now have its own zoom level.

Issue: 24372 Spec

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.

Issue: 27745 Spec

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.

Issue: 32307 Spec

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.

Issue: 57030 Spec

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.

Issue: 82071 Spec

9. Improved Print Preview

PageUp and PageDown keys scroll continuously through the print preview. Ctrl + PgUp/PgDn scroll to top of next/previous page.

Issue: 7269

tags: calc openoffice.org spreadsheet usability user-experience

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: DIALOG <-Dialog(100,100,115,55,Symbol Bar Plot) LBL1 <-Label(5,5,50,10,Chart Data:) ARR1 <-ArrayField(60,5,50,10) LBL2 <-Label(5,20,50,10,Chart Title:) TXT1 <-TextField(60,20,50,10) RUN <-RunButton(80,35,30,15) __CALL: library(plotrix) PLOT: symbolbarplot({$ARR1}, main="{$TXT1}")

 is all you need to show this dialog

and create the output shown below.

tags: calc gsoc openoffice.org spreadsheet

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:

1. Undo all changes made by the user in the temporary copy.
2. Apply all changes since the last save from the shared file to the temporary copy.
3. Redo all changes made by the user in the temporary copy.
4. Copy the temporary file to the shared file.

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.

tags: calc openoffice.org spreadsheet usability

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?

tags: calc odf odff opendocument openoffice.org spreadsheet

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;

tags: api calc openoffice.org spreadsheet

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

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

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.

tags: calc openoffice.org spreadsheet usability

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,000
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!

tags: calc openoffice.org performance spreadsheet

tags: calc openoffice.org performance