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