OX Blog

OX Spreadsheet Reloaded

Written by Malte Timmermann | Jun 20, 2016

In OX App Suite 7.8.2, OX Documents Spreadsheet has undergone a major rewrite – available now!

In the past, a lot of spreadsheet functionality has been performed on the server by the C++ CalcEngine which was derived from Apache Open Office.

This approach has helped us to reduce time to market for OX Spreadsheet, because a number of things didn’t have to be implemented from scratch in JavaScript: Tons of functions, dependency management for cell formulas, clipboard, and sometimes strange (Excel) spreadsheet logic.

But this approach also had drawbacks: Latency for every new calculation on user input, latency for getting more data from the CalcEngine when scrolling, maintenance and enhancements of Open Office code which often is very different from what Excel and OX Spreadsheet do, and finally, no calculations at all while the connection is gone. On top of this, the CalcEngine had a huge influence on deployment and sizing/scaling.

So we finally decided to get rid of the CalcEngine.

Now, all the magic is rewritten in JavaScript:

 

  • 219 new functions (resulting in 455! implemented functions), ranging from really simple ones to complex functions that probably almost nobody knows about (fyi – here is a list of all functions that Excel knows about)
  • Dependency Manager for formula calculation, to make sure all formulas are calculated in the correct order, and that there are no circular dependencies (which would result in endless loops)
  • Adjustment of formulas, so that when you insert/delete rows or columns, the ranges in formulas are updated accordingly
  • Sort and Filter
  • Autofill
  • Table References and Shared Formulas (features we didn’t even support with the CalcEngine)
  • Number and Currency formats
  • Clipboard, also including formula adjustments on paste
  • Calculation of optimal row height and column width
  • Conditional formatting, including support for attributions the CalcEngine didn’t support, for example data bars
  • Chunked import and chunked loading – spreadsheet data can be huge

For administrators there is no longer the need to install (remote) calc engines and all questions about sizing are obsolete. The resource requirements (CPU/RAM) are much lower now.

The new OX Spreadsheet feels much more responsive – and we have been positively surprised about the function calculation speed in JavaScript. It seems JavaScript really isn’t that slow, as long as you don’t need to touch the DOM.