-A spreadsheet usually starts as a simple and single- to evolve into a single software artifact where all business user software artifact, but, as frequent as in other software logic from all different users is defined! In such a collaborative systems, quickly evolves into a complex system developed by setting if a new user needs to express a new business rule on tmhaensyamacetosrps.reOadftsehne,edt:iffwerheilnet ausseercsrwetoarrky omnadyifbfeereonntlyasipnevcotlsveodf the spreadsheet data, he/she has to do it by intrusively adding in adding plain data to the spreadsheet, an accountant may formulas to the existing spreadsheet. define new business rules, while an engineer may need to adapt The programming language community developed advanced the spreadsheet content so it can be used by other software modularity mechanisms to avoid this problem in regular smyesctehmans.isUmnsf,oratnudnaatselay, csopnreseaqdusheneecte,syssotmeme sodfothneotporfefveriomusodtauslkars programming languages. In that sense, aspect-oriented promay be defined by adding intrusive “code” to the spreadsheet. gramming (AOP) is a popular and advanced technique that In this paper we go through the design and implementation enables the modular implementation of the so-called crossof an aspect-oriented language for spreadsheets so that users cutting concerns. The crosscutting structure tends to appear can work on different aspects of a spreadsheet in a modular tangled and scatted across several artifacts of a software nweawy. bFuosrineexsasmrupllees, atospaenctsexciasntinbge sdperfienadedshieneto,rodrertotomianntirpoudluactee system. While implementing such crosscutting concerns, the the spreadsheet data to be ported to another system. Aspects crosscutting structure can appear in common software developare defined as aspect-oriented program specifications that are ment concerns, such as distribution and persistence [2], error dynamically woven into the underlying spreadsheet by an aspect handling [3], [4], certain design patterns [5], tracing [6], or weaver. In this aspect-oriented style of spreadsheet development, design by contract [6]-[8]. cdoifdfeerteontthueseorrsigdienvaellosppr,eoardrsehueseet,. aSsupcehctcsowdiethisouatdaddedd/ienxgeicnuttreudsibvye In this paper we introduce the concept of AOP to spreadthe spreadsheet weaving mechanism proposed in this paper. sheets. We start by introducing a running example in Section II. Our first contribution is presented in Section III where I. INTRODUCTION we adapt the AOP concept to spreadsheets. For instance, a key feature of a common aspect-oriented language is the possibility to add advice before, after or around a join point. Since spreadsheets are two-dimensional, AOP features like advising need to be adapted in order to be applied to spreadsheets. The second contribution we do in this work is the design of a new language to implement AOP for spreadsheets. This language is presented in Section IV. An overview of the architecture of the system is presented in Section V. Finally, Section VI discusses related work, and Section VII presents our conclusions.
Spreadsheet systems are the software system of choice
for many non-professional programmers, often called
enduser programmers [
Very much like in the development of other software systems, different developers (end users in this case) are concerned with different aspects of the functionality of the system. However, while modern programming languages offer modularity mechanisms providing powerful abstractions to develop software collaboratively, spreadsheet systems offer no such support to their users. As a result, a spreadsheet tends
In this section we present an example of a popular setting to use spreadsheets: manage the students marks in a course. Such a spreadsheet is shown in Fig. 1.
In this simple spreadsheet, the final mark of a student is the average of three evaluation items: two exams and one essay. Thus, this mark is obtained by the formula =AVERAGE(B2:D2) (for the student in line 2).
Let us consider now a real scenario where three different users share, access, and update this spreadsheet: a teaching assistant, that mainly structures the spreadsheet and compiles the different marks, the teaching coordinator who has to validate the spreadsheet, decide on borderline cases, and send the final marks to the academic services, and finally, the Erasmus coordinator who has to adapt the marks of Erasmus students to the ECTS system1.
Because spreadsheets offer no modularity mechanisms, a typical spreadsheet development environment allows a spreadsheet to quickly evolve into a more complex one. That is, each of the users adds/updates data and formulas in order to express the (crosscutting) logic they need. In the following, we show the spreadsheet after the teaching and Erasmus coordinator update it.
The data edited by one user can be seen as intrusive code for the others. For example, the teaching coordinator and its assistant are not concerned with the ECTS system, although this concern is now part of their spreadsheet.
Let us analyze the teaching coordinator role in more detail. Because he decides on borderline cases, he decided to approve only one of such cases (student in line 3). Instead of changing that particular final mark formula, and as often occurs in reality, he just replaces this formula by the constant 5.0. Although the result of such an operation is a correct spreadsheet, this action has several problems. First, there is no documentation on how borderline cases have been decided (the threshold is not known). Second, the original spreadsheet data is lost, and it may be difficult to recover it (for example, if other borderline students ask for explanations).
This is the natural setting to apply AOP. In this style of programming users do not intrusively modify the original program (a spreadsheet in our case), but instead, a new software fragment is defined in order to specify the program transformations needed to express such new concern. These software fragments are called aspects. Then, a specific AOP 1For more details on the ECTS system please refer to http://ec.europa.eu/ education/tools/docs/ects-guide en.pdf.
} end mechanism, called weaver, given the original program and the aspect(s), weaves them into a coherent executable software program.
We present in Listing 1 our first spreadsheet aspect, the one that specifies the coordinator concerns on borderline cases.
Listing 1. An aspect to handle borderline marks. aspect BorderlineCase finalmark : select sheet{*}.column{*}.cell{*} around finalmark { #{cell.result >= 4.8 && cell.result < 5 ? 5 : cell.value} } when {
cell.column[0].value = "Final Mark" } end
An aspect-oriented language has three main parts. First it is necessary to select the join points of interest by means of pointcut declarations. In the example, the pointcut finalmark selects any cell within any worksheet. This is done by the select command in our language. Then, the around advice declaration defines the actions (transformations in our case) to be applied. Hence, it specifies when the result of evaluating a formula is greater than of equal to 4.8, and if it is, the cell is replaced by the constant 5.0. To access the result of the computation of a cell, that is, the result of the formula in the cell, we use cell.result. This accesses a cell, and after that, its computed result. The when statement specifies when the action is applied (in this case to all columns labeled Final Mark).
This aspect clearly and non-intrusively defines the crosscutting rule used to decide on borderline cases: all students with marks greater than or equal to 4.8 are approved in the course.
Similarly, the Erasmus coordinator can define an aspect where the rules to define the ECTS marks are expressed. This aspect is presented in Listing 2.
Listing 2. Aspect to add ECTS marks. aspect AddECTSMark finalmarks : select sheet{*}.column{*}.cell{*} right finalmarks { =IF(#{cell.name}<=10 && #{cell.name}>=9.5 , "A" , IF(#{cell.name}<9.5 && #{cell.name}>=8.5 , "B" , IF(#{cell.name}<8.5 && #{cell.name}>=6.5 , "C" , IF(#{cell.name}<6.5 && #{cell.name}>=5.5 , "D" , IF(#{cell.name}<5.5 && #{cell.name}>=5 , "E" , "F"))))) } when { column[0].value == "Final Mark"
&& cell.row <> 0 } right finalmarks {
ECTS Mark } when { column[0].value = "Final Mark" && cell.row = 0
In this aspect, we use cell.name to access the cell address (for instance “E4”). Thus, when the IF formula is placed in the corresponding cells, the correct references will be calculated and inserted in the formula replacing the use of cell.name.
An aspect weaver can then weave this aspects into the original spreadsheet individually, or by composing them. Actually, Fig. 2 shows the result of weaving the aspect AddECTSMark after the aspect BorderlineCase. Thus, aspects can be combined. It should also be noticed that such a spreadsheet based weaver has to dynamically weave aspects since for some aspects (BorderlineCase) only after computing formulas, aspects can be weaved. Indeed, it is necessary to execute the spreadsheet to get the results of formulas, for instance, when the cell.result operator is used.
We have just presented two aspects of our AOP spreadsheet language and briefly explained how aspects are weaved into a spreadsheet system. Next sections present in detail the design and implementation of this language and weaver.
III. APPLYING ASPECT-ORIENTED PROGRAMMING
TO SPREADSHEETS
In this section, we discuss how AOP concepts can be applied to spreadsheets. Specific examples are described in Section IV.
In order to apply AOP to a language, it is necessary to identify the join point model that the new aspect language supports. A join point is a well-defined point in the program that is specified by a pointcut, that is, an expression to match specific elements within the language. In our case, these elements are set to be the main elements of the spreadsheet, where users will want to perform operations on, for instance, use an alternate worksheet for testing or add more cells for debugging. For spreadsheets, we define the following join points of interest: worksheets; ranges; and, cells.
With these join points the spreadsheet is accessible from aspects, hence users can separate concerns at different levels:
When a join point is instantiated, it is possible to perform an action using advice. Advice are additional behavior that one wants for the underlying program, that can be new worksheets, ranges of cells, or single cells. This allows to separate the business logic of the spreadsheet into several concerns when developing the spreadsheet and then join everything in order to achieve the wanted application.
A spreadsheet file is composed of a set of worksheets, each of which containing the cells with the data and formulas. Worksheets are the top-level artifacts in a spreadsheet.
As a join point, a worksheet (see Fig. 3) can be modified by the standard AOP advice in the following manner: before – insert a worksheet before the current join point; after – insert a worksheet after the current join point; around – insert a worksheet before and/or after the current joint point and/or define an alternative worksheet for the current join point.
As an example, consider Fig. 3 with Sheet2 as the join point. A before advice for this join point results in a new worksheet between the worksheets Sheet1 and Sheet2. On the other hand, if we consider an after advice, the resulting worksheet is between worksheets Sheet2 and Sheet3.
To specify a worksheet, two options are available: either defining the cells for the new worksheet, or referencing a worksheet to be duplicated.
Cells are the finest grained join points possible in the spreadsheets world. Unlike common programming languages, they are inserted in a two-dimensional plan. Thus, the usual before and after advice declarations are not completely adequate since ambiguities may arise about which of the two dimensions (vertical or horizontal) should be used. To overcome this issue, each kind of advice declaration is separated in two for each dimension in the plane, resulting in the following: XXXXXXXadXviXceX direction vertical horizontal before above left after below right The advice that can be defined for cell join points is: left – add a cell, or range of cells, to the left of the current join point; above – add a cell, or range of cells, above the current join point; right – add a cell, or range of cells, to the right of the current join point; below – add a cell, or range of cells, below the current join point; around – define an alternative cell for the current join point.
A cell is specified just by stating its contents. A range of cells is specified by defining the cells that it contains.
In this section we present our language for aspects for spreadsheets. This language is based on existing ones for common purposes programming languages, and implements the vision we described in Section III. The proposed language allows to specify aspects defining its pointcuts and advice. For each component of the aspects to be written by the user we present next the corresponding grammar. This grammar is the artifact used to validate, though a compiler, the correctness of the aspects written by the users. We start by introducing the grammar for pointcuts.
Pointcuts are defined by an expression pattern, which is specified by the following grammar: hjoin pointi ::= hjp namei ‘:’ ‘select’ hpExpri where hpExpri is a pattern expression. The expression is used to define the kind of join point to be selected: sheet, range, or cell.
The allowed expressions in join points are instances of the following productions: hpExpri ::= hpSheeti j hpSheeti ‘.’ hpRangei j hpSheeti (‘.’ hpRangei)? ‘.’ hpCelli hpSheeti ::= ‘sheet’ ‘{’ hpSheetExpri ‘}’ hpSheetExpri ::= ‘name’ hbCompi hstringi j ‘number’ hbCompi hintegeri j ‘*’ hpRangei ::= ‘range’ ‘{’ hpRangeExpri ‘}’ j ‘column’ ‘{’ hpRangeExpri ‘}’ j ‘row’ ‘{’ hpRangeExpri ‘}’ hpRangeExpri ::= ‘name’ hbCompi hstringi
j ‘*’ hpCelli ::= ‘cell’ ‘{’ hpCellExpri ‘}’ hpCellExpri ::= ‘name’ hbCompi hstringi j ‘match’ hbCompi hstringi j ‘*’ hbCompi ::= ‘==’ j ‘<’ j ‘<=’ j ‘>’ j ‘>=’ j ‘<>’
The pattern expression hpExpri allows to select worksheets hsheeti, ranges hrangei, or cells hcelli.
When specifying a worksheet as a join point, it is possible to select a worksheet relative to a given name, to a worksheet index, or to select all worksheets.
For ranges, there are three kinds that can be selected: a column range (column) with a width of one cell, a row range (row) with a height of one cell, or a range with any rectangular shape (range).
For cells, it is possible to specify its address (name), or a pattern-match expression (match). When no specific worksheet, range or cell is necessary, the wildcard symbol ‘*’ can be used.
With this, we can select, for instance, the second worksheet worksheet_jp: select worksheet{number=2} or a rectangular range of 3 columns by 2 rows starting at cell A2 in any worksheet:
range_jp: select worksheet{*}.range{name="A2:C3"} or the cells in the first row of any worksheet: cell_jp: select worksheet{*}.range{row=1}.cell{*} Depending on the kind of join point selected, different artifacts are made available to work with within the advice. For worksheets, we can use the variable worksheet, and then one of the attributes: name or number. For ranges, depending on its kind, the available variable can be: range, column, or row; they have the attribute name. Moreover, since ranges are sets of cells, indexation can be used, for instance, to select the first row of a column, one can write column[0]. For cells, we have the variable cell which has the attribute name (its cell reference).
Advice are the actions to apply to the join points. They are defined by the following grammar: hadvicei ::= (hadvice namei‘:’)? hadvice positioni
hjp namei ‘{’ hcodei ‘}’ hadvice conditioni hadvice positioni ::= ‘left’
j ‘below’ j ‘around’ hadvice conditioni ::= (‘when {’ hbExpri ‘}’)? j ‘above’ j ‘right’ where hcodei is a cell or a list of cells and respective contents: hcodei ::= hstringi j hcellListi hcellListi ::= hcellRef i ‘=’ hstringi (‘;’hcellListi)?
The contents of the cell hstringi can be defined using interpolation of values made available in the context of the advice (for instance, join point contents) using interpolation.
For example, to add a row which evaluates the total of a column (for instance, the join point is a column range) where the join point is named myColumn: below myColumn { =SUM(#{range.name}) }
In the above example, interpolation is used to introduce a value that is available in the join point, but is not accessible from common spreadsheet formulas. If the column range is C1:C20, then the formula would be =SUM(C1:C20).
Note that the order by which the advice are applied is important. They are applied according to their precedence which is defined by the order they are defined. In the case of before the ones defined first are the ones with more precedence. Thus, the ones defined earlier are applied first.
For the after it is the other way around, that is, although the ones declared first are the ones with more priority, they are executed last. This is the common behavior of aspects for other programming languages.
Advice can be applied conditionally, that is, when a specified criterion is met. This is specified with a boolean expression as defined by the following grammar: hbExpri ::= ‘!’ hbExpri j hbExpri (‘&&’ j ‘||’) hbExpri j hexpri hbCompi hexpri hexpressioni ::= hbExpri j hvari j hstringi j hnumberi j hcellRef i j hrangeRef i
However, some aspects require the execution of the program, in our case, of the spreadsheet (formulas). This is the case illustrate in Listing 1 where it is necessary to compute the student grade to decide if the final grade is changed or not. In that case we used the operator cell.result, which requires to evaluate the formula of the underlying cell to obtain its result. Thus, the weaver must be integrated with a spreadsheet execution engine. Indeed we intend to build the weaver inside Excel itself, so it can reuse its recalculation mechanism.
In these cases, the spreadsheet is kept untouched, but since now the spreadsheet is only complete when considered with the aspects defined, the values it shows may change.
If the user wants to see the original spreadsheet then, it is only necessary to deactivate the aspects.
Fig. 5 illustrates the integration of our weaver with a spreadsheet system to create an aspect-oriented spreadsheet system.
Spreadsheet
Aspects Weaver
Excel Execution
Engine
C. Aspect Aspect-oriented programming has been applied to several
programming languages, for instance, Java [
An aspect is composed of the definition of the pointcuts MATLAB [
hjoin pointi+ hadvicei+ Java was one of the first languages to be exposed to aspects
‘end’ through a language dubbed AspectJ [
V. A WEAVER FOR SPREADSHEETS compile time, but others are applied only during run time when In this section we present our architectural model for aspect the complete information about the execution is available. The spreadsheets. The aspects are defined using the language pre- nature of spreadsheets, where both data and computation are sented in Section IV, and together with the spreadsheet, they at the same level, imposes a dynamic join point model in order are interpreted by the weaver. In this context, the spreadsheet to have access to run-time values. is only complete when considered together with the aspects. AOP was also used to support development of embedded
Some aspects can be handled statically, that is, without systems. The LARA language [
In the context of spreasheets, there are several works
presenting techniques to transform spreadsheets by using
spreadsheet specific transformation languages [
BumbleBee [
RefBook [
Our approach can also be used to perform these refactorings.
More generic transformations for spreadsheets, introduced
by end-users’ needs, can be performed using program
synthesis. This technique added the ability to transform strings of
text [
Another kind of transformation, targeting spreadsheet
testing, is mutation [
In [
This paper proposes the use of aspect-oriented programming for spreadsheets. We have designed an aspect language that considers spreadsheet peculiarities, and a dynamic weaver that is embedded in the evaluation mechanism of a spreadsheet system.
Although AOP provides a powerful modular approach that is particularly suitable to be used in software that is shared and being collaboratively developed, our work opens some important questions that we intend to answer in future work by conducting empirical studies, namely:
Are end users able to understand the abstractions provided by AOP and to use it in practice? This is not only related to our proposed language as it is a more general question. Nevertheless, we need to answer it so we can understand how to better make our AOP language available for user. This is specially important when dealing with end users.
Does AOP improves end-users’ productivity? We have shown that some of the model-driven approaches we introduced in the past can do that. We will conduct similar studies to evaluate this new proposal.
Is the textual definition of the AOP language adequate or
should we use a more spreadsheet-like one?
When dealing with more advanced users, it is not always
the case they prefer visual languages [
This work is financed by the FCT – Fundac¸a˜o para a Cieˆncia e a Tecnologia (Portuguese Foundation for Science and Technology) within project UID/EEA/50014/2013. This work has also been partially funded by FLAD/NSF through a project grant (ref. 233/2014). The last author is supported by
(PVE) grant (ref. 15075133).