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 scientific community as well. This is due to more research teams devoting their efforts to improving spreadsheets, and a growing number of scientific events dedicated to them.
A successful approach to address spreadsheets under a scientific 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 effectiveness and efficiency 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 different paradigm.
The spreadsheet development framework that we envision has been fully incorporated in a tool, MDSheet1 , whose features include:
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.
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 MD-Sheet with the example given in Figure 1: we present a model for a Budget spreadsheet (Figure 1a), which we adapted from [13] 3 , and an instance of such model (Figure 1b). 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. Following is the description of the full set of features offered by MDSheet.
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 definition 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 [2], that has been implemented in MDSheet. Concretely, we infer models in the ClassSheets language, an object-oriented high-level formalism to abstract spreadsheets [13].
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 [6], which was demonstrated in Section 2.1. Moreover, we extended the ClassSheets language with database constraints, such as unique columns/rows or foreign keys, which have also been incorporated in MDSheet [11]. In fact, we have further extended the available restrictions so that the user can specify the contents of a cell using regular expressions or intervals [8]. Finally, we extended ClassSheets with references between different models making them more flexible. Note that through this embedding we can guarantee that spreadsheet data always conforms to a model.
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 defining 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 define an interval of integers for a cell, or a regu-lar 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.
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 [6,7,12]. We have later proposed techniques and tools to the evolution of data by the user and corresponding automatic coevolution of the model [3]. We therefore ensure that model/instance consistency is never broken.
In a first attempt to measure the quality of a spreadsheet model, we introduced a set of metrics to calculate the complexity of ClassSheet models [9]. These metrics are implemented under MDSheet and can be calculated for any Class-Sheet defined using it. They are then compared to the same metrics computed for a repository of ClassSheet models so users can have a reference point for such values. The evolution mechanisms can then be used to evolve the spreadsheet improving it according to the metrics calculated.
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 definition 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 [1,4], very similar to SQL. Even being textual it already was of great help for users [14]. Still, we have further improved it by embedding the language in a worksheet, thus creating a visual language for spreadsheet querying [5].
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 efficient and more effective [10].
We briefly presented MDSheet and all the features it offers to its users. Given the fact that it has been built as a framework, new tools, even if not proposed by us, can easily be integrated in it.
We believe this tool is in a very mature state and can be used in real case scenarios. We have thus started its integration in industry: i), to support test case evolution in an agile testing framework of a software house; ii), to adapt data produced by different 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 -Fundação para a Ciência e a Tecnologia (Portuguese Foundation for Science and Technology) within project FCOMP-01-0124-FEDER-020532. The first author was funded by the FCT grant SFRH/BPD/73358/2010.