Zeige Artikel getaggt mit spreadsheets
From Excel to Javascript - Implementing Spreadsheets in the Browser

Why we moved our spreadsheet to the web

At CS we use spreadsheets to break down paper questionnaires into logical tabular entities, such as intro texts, items, skip conditions, pages and many more. The resulting relational data can then be imported into a relational database and used in SurveyLab projects. SurveyLab is our RAD framework for building complex online survey systems. For a number of years we used Excel to accomplish this task, however for reasons that will be detailed below, we found this not to be the optimal choice.

Collaboration

If you have ever had the need to collaborate on an Excel file, you know how problematic this can become. There is virtually no way of concurrently editing an Excel file, meaning time lost, as the various collaborators add their changes in series. At times you end up with several versions of the same file, only to realize that integrating these is near impossible or at least very error-prone. But wait - isn’t that why we have version control systems? Supposedly yes, however Excel uses binary file formats and therefore to diff and merge conflicting changes becomes infinitely more difficult.

Data portability and availability

If you want to move your spreadsheet data to a database or need to serve other applications with it, you must export it into appropriate formats and have these apps import the data again. If you still want to be able to change this data easily via spreadsheet editor, you have to repeat this process every time changes are made. Clearly, there are better, more centralized ways of handling this.

Limited functionality

Excel obviously has many built-in functions. Yet, what to do when you need to implement your very own specialised validator? How about if you have to check for occurrences of foreign keys on another sheet? You can attempt this in Excel, but as you’ll find, it’s messy and hard to maintain.

Getting rid of technical debt

Realizing the technical debt we were accumulating with our Excel solution, we started looking into a way of dealing with the above problems. This initiative was also given wings by the fact that we started working on an advanced GUI for editing surveys. Since this advanced GUI will employ the exact same data that is manipulated via spreadsheet, we needed an accessible, extensible and portable format. Such problems can easily be solved by using a standardized data format like JSON and be automatized via an API. We therefore, decided on building our own browser-based spreadsheet editor. As we did not want to succumb to the “not invented here” syndrome, we had a look at available open source and commercial spreadsheet solutions based on Javascript.

spreadJS by Wijmo

The Excel-like demo by Wijmo seemed very promising, so we committed some time to evaluating it. It has almost all the functions of Excel and provides an API for customization. However, in addition to being too expensive, there were other problems we discovered.

Pros

  • It can basically replicate full Excel functionality 
  • It is in ongoing development
  • Good support and fast forum responses by the community and Wijmo

Cons

  • Expensive ( ~800€ )
  • Requires Excel I/O service or client side software for some functionality like creating a basetemplate from an Excel file
  • Doubtful code quality, e.g. CSS styles inside the HTML and Javascript code, which makes customizing difficult. Large overhead and storage needs because of the way meta information is handled. Our base template with few default data converted to a spreadJS JSON file had about 4-5 million characters. Our plan was to use localStorage for storing data while editing, which is limited to only a few MB (depending on the browser) and would not be sufficient to store several projects.

Google Docs Spreadsheets

We also looked into using Google Spreadsheets, but that would have meant building a spreadsheet editor from scratch, due to the fact that we cannot implement our own functionality. So we could “just” have used the Google Spreadsheet API to store and read data, however, we wanted to host the editor ourselves, as our projects tend to contain sensitive data. Moreover, we like to stay independent.

jQuery plugins

We looked at some of the many smaller jQuery spreadsheet plugins that are out there, like flexigrid, jQuery.sheet and some others. Yet, most of them didn’t fulfil our prerequisites, which included not being in active development or having bad documentation, or being too limited in scope. 

Our choice: handsontable

We decided to go with handsontable (hot), as it is cleanly and elegantly written, readily extensible, well-documented and updates are frequently released. The one troublesome thing is the large number of open issues on github, which may indicate a lack of contributors. So far we have been able to build clean workarounds for every problem we have encountered.

Reasons why we chose handsontable:

  • Easily extensible
  • Two way data binding: If the text inside a cell is changed, the data-object gets updated right away. It works the other way too - if new data is pushed to the editor the change is reflected in the UI after a re-render.
  • Good documentation and active development 
  • Good API: hot supports a ton of functions you can call, and in addition defines a lot of hooks (events) which makes customizing the editor much easier. E.g. it has events, which are fired before and after data is changed inside a cell and also for failed validations.
  • It is fast: except for the scrolling performance, which can be slow at times, editing, validation, importing and exporting work rapidly.

  • Small overhead for project files: the JSON data hot exports is pretty straightforward, with one array per table, which contains the rows represented by objects. This made it very easy to adjust our Surveylab importer to allow us to read those files.
  • Custom cell types: hot comes with a lot of predefined cell types, such as dropdowns, numeric, text only etc.
  • Cell validators: hot allows us to write custom cell validators. That was an important factor in the decision to use this program, as we needed to implement a foreign key check that would look for the existence of a key in other table instances.
  • Context menu: hot has an build in context menu on right mouse click, which can be extended and customized with your own functionality

So far we are fairly happy with the decision to move to an online spreadsheet editor. By far and away, the biggest improvement is in the ability to use git for our project templates, as we have now switched from Excel files to JSON and can merge changes without problems. Working with the editor is also more user-friendly, because we can highlight tables with failed validations or use hyperlinks to navigate directly from a foreign key to the corresponding table. In addition, the editor is future proof, because it is written in Javascript. If we need a new function or require a part of the UI to behave differently, we can now just build it.

Below you see an image of the current version of the editor. Red buttons indicate sheets with failed validations.

image00.png

 

 

 

Show page in

Was Kunden
über uns sagen

Dank der Expertise und dem Know-how der Mitarbeiter von cloud solutions wurden wir auch in schwierigen Situationen stets optimal beraten. Wir können cloud solutions ohne Bedenken weiter empfehlen.

Prof. Dr. Norbert K. Semmer
Psychologisches Institut, Universität Bern, Schweiz

 
 
 
The future of the PHP PaaS is here: Our journey to Platform.sh
CS Tech
In our team we’re very confident in our ability to produce high quality software. For the past decad...