i-nth logo

Authors

Markus Luckey, Martin Erwig, & Gregor Engels

Abstract

Using spreadsheets is the preferred method to calculate, display or store anything that fits into a table-like structure. They are often used by end users to create applications, although they have one critical drawback—spreadsheets are very error-prone.

Recent research has developed methods to reduce this error-proneness by introducing a new way of object-oriented modeling of spreadsheets before using them. These spreadsheet models, termed ClassSheets, are used to generate concrete spreadsheets on the instance level. By this approach sources of errors are reduced and spreadsheet applications become easier to understand.

As usual for almost every other application, requirements on spreadsheets change due to the changing environment. Thus, the problem of evolution of spreadsheets arises. The update and evolution of spreadsheets is the uttermost source of errors that may have severe impact.

In this paper, we will introduce a model-based approach to spreadsheet evolution by propagating updates on spreadsheet models (i.e. ClassSheets) to spreadsheets. To this end, update commands for the ClassSheet layer are automatically transformed to those for the spreadsheet layer.

We describe spreadsheet model update propagation using a formal framework and present an integrated tool suite that allows the easy creation and safe update of spreadsheet models. The presented approach greatly contributes to the problem of software evolution and maintenance for spreadsheets and thus avoids many errors that might have severe impacts.

Sample

Current vs intended approach
Current vs intended approach

This is the current (top) vs intended (bottom) ClassSheet approach to spreadsheet development.

In contrast to the current approach, the user does not develop low-level spreadsheets, but creates a ClassSheet (i.e. business model) that reflects all necessary business entities.

This information is provided to the spreadsheet application to generate spreadsheets and avoid errors, such as missed formula updates.

ClassSheets present an effective way of designing spreadsheet models using several popular paradigms from actual computer science research. ClassSheets define object-oriented models that are used as templates and allow the use of an MDE style development process for spreadsheets. To deal with the problem of maintainability, we propose a formally defined graph data structure for ClassSheets to enable the easy definition of propagation rules that allow the semantics-preserving propagation of updates on ClassSheets to ClassSheet instances, i.e. spreadsheets.

To express updates on ClassSheets we introduced an update language and described its semantics using denotational style. We have developed a prototype system that uses the ClassSheet editor Claos to propagate model updates to spreadsheets.

Publication

2012, Journal of Visual Languages & Computing, Volume 23, Number 5, October, pages 267-286

Full article

Systematic evolution of model-based spreadsheet applications