This paper showcases MDSheet, a framework aimed at improving the engineering of spreadsheets. This framework is model-driven, and has been fully integrated under a spreadsheet system. Also, its practical interest has been demonstrated by several empirical studies.
We can not run the modern world without spreadsheets. Spreadsheets are omnipresent, from individuals needing to cope with simple needs to large companies needing to implement complex forecasts or to produce advanced reports.
The realization of such importance has made concrete impact in the scienti c community as well. This is due to more research teams devoting their e orts to improving spreadsheets, and a growing number of scienti c events dedicated to them.
A successful approach to address spreadsheets under a scienti c perspective consists of incorporating well-established software engineering techniques in the spreadsheet development process.
Our approach is essentially based on precisely one such technique: we adopt model-driven spreadsheet engineering. In the setting we propose a spreadsheet is abstracted through a concise model, which is then used to improve e ectiveness and e ciency of spreadsheet users. The framework we describe in this paper has been realized in a traditional spreadsheet development system, thus not forcing spreadsheet users to move to a di erent paradigm.
The spreadsheet development framework that we envision has been fully incorporated in a tool, MDSheet1, whose features include:2 1) Model inference: we extract the abstract representation from legacy spreadsheets; 2) Embedded models: this abstract representation is manipulated and evolved in spreadsheets themselves; 3) User guidance: relying on this business model, we are able of guiding users in avoiding traditional spreadsheet mistakes; 4) Model/instance synchronization: we support the evolution of model and instances, ensuring an automatic synchronization of the unevolved artifact; 5) Model quality assessment: a set of metrics on the complexity of a spreadsheet model can be computed; 6) Querying: spreadsheet data can be queried. 2.
MDSheet is a framework for the engineering of spreadsheets in a model-driven fashion. This framework is highly extensible: we have actually extended it with several new functionalities that we have developed in the last few years. 2.1
The realization of our approach to spreadsheet engineering
builds upon the embedding of ClassSheets in a spreadsheet
system. So, we start by introducing ClassSheets within
MDSheet with the example given in Figure 1: we present a model
for a Budget spreadsheet (Figure 1a), which we adapted
from [
This model holds three classes where data is to be inserted by end users: i) Year, with a default value of 2010, for the budget to accommodate multi-year information, ii) Category, for assigning a label to each expense and iii), a(n implicit) relationship class where quantity and costs are registered and totals are calculated based on them. The actual spreadsheet may hold several repetitions of any of these elements, as indicated by the ellipsis. For each expense we record its quantity and its cost (with 0 as the default value), and we calculate the total amount associated with it. Finally, (simple) summation formulas are used to calculate the global amount spent per year (cell D5), the amount spent per expense type in all years (cell F3) and the total amount spent in all years (cell F5) are also calculated. 1MDSheet is available through the SSaaPP project website: http://ssaapp.di.uminho.pt. 2In the next section, we describe each such feature in a different subsection. 3We assume colors are visible in the digital version of this paper.
(a) Model worksheet. (b) Data/instance worksheet.
Following is the description of the full set of features offered by MDSheet. 2.2
A model-driven approach to spreadsheet engineering
offers an improved development experience: an abstract
representation of a spreadsheet, i.e., its model, helps us, among
other things, in guiding users into preventing errors. This
approach, however, requires the de nition of a model in
parallel with the spreadsheet it abstracts. In order to handle
legacy spreadsheets, i.e., the ones that have not been
developed from scratch together with their model, we have
devised a model inference technique [
The worksheet structure of spreadsheets is a decisive
factor in their modularity. In fact, we exploited precisely this
structure to make the model of a spreadsheet available within
spreadsheet systems themselves: one worksheet holds the
model of a spreadsheet, while another holds its data. This
embedding of spreadsheets has also been implemented under
MDSheet [
The embedding of our extended version of the ClassSheet language allows us to guide the user in inserting correct data. When a model is designed, it serves as a guider in the creation of a data worksheet, which is initially empty. Only cells containing plain data can be edited as all other are inferred from the model. This prevents, e.g., users from making mistakes when de ning formulas as they are locked. Moreover, the restrictions created in the model guarantee that the data in the cells respects them. In the model it is possible to de ne an interval of integers for a cell, or a regular expression that the content must conform to. A column or row can be marked as having only unique values or being a foreign key to another column or row. All these restrictions are enforced by MDSheet. In the case of foreign keys, the user can use a combo box to select existing values from the referred column/row. 2.5
As any other software artifact, spreadsheets evolve over
time. MDSheet accommodates changes by allowing the
evolution of models and instances, while automatically
coevolving the unchanged artifact. For this, we introduced a
formal framework to allow evolutions of the model to be
automatically spanned to the instances [
In a rst attempt to measure the quality of a spreadsheet
model, we introduced a set of metrics to calculate the
complexity of ClassSheet models [
As many spreadsheets are used as data repositories, the
need to query their data is frequent. MDSheet also
integrates a query system, which allows the de nition of
modeloriented queries, in the style of traditional database queries.
This allows the writing of queries without having to
manually observe a possibly large number of columns and rows
of concrete data. Indeed, queries are written, by analyzing
models, as abstractions that are simpler to understand. Our
system was initially presented as a textual language [
One of the purposes of our tool is to help users commit
less errors; if possible, it also intends to help users work
faster with spreadsheets. To assess these two concerns we
have run an empirical study and we have found empirical
evidence that indeed our model-driven spreadsheet
environment can in fact help users become more e cient and more
e ective [
in an agile testing framework of a software house; ii), to adapt data produced by di erent database systems for a car multimedia production company; and iii), to provide spreadsheet models for a food bank.
This work is part funded by the ERDF - European Regional Development Fund through the COMPETE Programme (operational programme for competitiveness) and by National Funds through the FCT - Fundaca~o para a Ci^encia e a Tecnologia (Portuguese Foundation for Science and Technology) within project FCOMP-01-0124-FEDER-020532. The rst author was funded by the FCT grant SFRH/BPD/73358/2010. 5.