Publications

Export 61 results:
Sort by: Author Title [ Type  (Asc)] Year
Book
Model-based Spreadsheet Engineering: Using Relational Models to Improve Spreadsheets, Cunha, Jácome , (2012) Abstractbook.pdf

Spreadsheets can be viewed as programming languages for non-professional programmers. These so-called ``end-user'' programmers vastly outnumber professional programmers creating millions of new spreadsheets every year. As a programming language, spreadsheets lack support for abstraction, testing, encapsulation, or structured programming. As a result, and as numerous studies have shown, the high rate of production is accompanied by an alarming high rate of errors. Some studies report that up to 90% of real-world spreadsheets contain errors. After their initial creation, many spreadsheets turn out to be used for storing and processing increasing amounts of data and supporting increasing numbers of users over long periods of time, making them complicated systems. An emerging solution to handle the complex and evolving software systems is Model-driven Engineering (MDE). To consider models as first class entities and any software artifact as a model or a model element is one of the basic principles of MDE. We adopted some techniques from MDE to solve spreadsheet problems. Most spreadsheets (if not all) lack a proper specification or a model. Using reverse engineering techniques we are able to derive various models from legacy spreadsheets. We use functional dependencies (a formalism that allow us to define how some column values depend on other column values) as building blocks for these models. Models can be used for several spreadsheet improvements, namely refactoring, safe evolution, migration or even generation of edit assistance. The techniques presented in this work are available under the framework HAEXCEL that we developed. It is composed of online and batch tools, reusable HASKELL libraries and OpenOffice.org extensions. A study with several end-users was organized to survey the impact of the techniques we designed. The results of this study indicate that the models can bring great benefits to spreadsheet engineering helping users to commit fewer errors and to work faster.

Book Chapter
Combining Smells and Fault Localization in Spreadsheets (in preparation), Abreu, Rui, Cunha Jácome, Fernandes João P., Martins Pedro, Perez Alexandre, and Saraiva João , (Submitted) paper.pdf
Graphical Querying of Model-Driven Spreadsheets, Cunha, Jácome, Fernandes João Paulo, Pereira Rui, and Saraiva João , Human Interface and the Management of Information. Information and Knowledge Design and Evaluation, Volume 8521, p.419–430, (2014) Abstracthci14.pdf

This paper presents a graphical interface to query model-driven spreadsheets, based on experience with previous work and empirical studies in querying systems, to simplify query construction for typical end-users with little to no knowledge of SQL. We briefly show our previous text based model-driven querying system. Afterwards, we detail our graphical model-driven querying interface, explaining each part of the interface and showing an example. To validate our work, we executed an empirical study, comparing our graphical querying approach to an alternative querying tool, which produced positive results.

Model-Based Programming Environments for Spreadsheets, Cunha, Jácome, Saraiva João, and Visser Joost , Programming Languages, Volume 7554, p.117–133, (2012) Abstractsblp12.pdf

Although spreadsheets can be seen as a flexible programming environment, they lack some of the concepts of regular programming languages, such as structured data types. This can lead the user to edit the spreadsheet in a wrong way and perhaps cause corrupt or redundant data. We devised a method for extraction of a relational model from a spreadsheet and the subsequent embedding of the model back into the spreadsheet to create a model-based spreadsheet programming environment. The extraction algorithm is specific for spreadsheets since it considers particularities such as layout and column arrangement. The extracted model is used to generate formulas and visual elements that are then embedded in the spreadsheet helping the user to edit data in a correct way. We present preliminary experimental results from applying our approach to a sample of spreadsheets from the EUSES Spreadsheet Corpus.

Watch out for that tree! A Tutorial on Shortcut Deforestation, Fernandes, João P., Cunha Jácome, Pardo Alberto, and Saraiva João , 2015 Central European Functional Programming School, Revised Selected Papers, (2016) cefp15.pdf
Conference Paper
Automatically Inferring ClassSheet Models from Spreadsheets, Cunha, Jácome, Erwig Martin, and Saraiva João , Proceedings of the 2010 IEEE Symposium on Visual Languages and Human-Centric Computing, Washington, DC, USA, p.93–100, (2010) Abstractvlhcc10.pdf

Many errors in spreadsheet formulas can be avoided if spreadsheets are built automatically from higher-level models that can encode and enforce consistency constraints. However, designing such models is time consuming and requires expertise beyond the knowledge to work with spreadsheets. Legacy spreadsheets pose a particular challenge to the approach of controlling spreadsheet evolution through higher-level models, because the need for a model might be overshadowed by two problems: (A) The benefit of creating a spreadsheet is lacking since the legacy spreadsheet already exists, and (B) existing data must be transferred into the new model-generated spreadsheet. To address these problems and to support the model-driven spreadsheet engineering approach, we have developed a tool that can automatically infer ClassSheet models from spreadsheets. To this end, we have adapted a method to infer entity/relationship models from relational database to the spreadsheets/ClassSheets realm. We have implemented our techniques in the HAEXCEL framework and integrated it with the ViTSL/Gencel spreadsheet generator, which allows the automatic generation of refactored spreadsheets from the inferred ClassSheet model. The resulting spreadsheet guides further changes and provably safeguards the spreadsheet against a large class of formula errors. The developed tool is a significant contribution to spreadsheet (reverse) engineering, because it fills an important gap and allows a promising design method (ClassSheets) to be applied to a huge collection of legacy spreadsheets with minimal effort.

A Bidirectional Model-driven Spreadsheet Environment (Poster/Abstract), Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, and Saraiva João , Proceedings of the 34rd International Conference on Software Engineering, p.1443–1444, (2012) Abstractabstract.pdfposter.pdf

n this extended abstract we present a bidirectional model-driven framework to develop spreadsheets. By being model driven, our approach allows to evolve a spreadsheet model and automatically have the data co-evolved. The bidirectional component achieves precisely the inverse, that is, to evolve the data and automatically obtain a new model to which the data conforms.

Bidirectional Transformation of Model-Driven Spreadsheets, Cunha, Jácome, Fernandes João P., Mendes Jorge, Pacheco Hugo, and Saraiva João , Theory and Practice of Model Transformations, Volume 7307, p.105–120, (2012) Abstracticmt12.pdf

Spreadsheets play an important role in software organizations. Indeed, in large software organizations, spreadsheets are not only used to define sheets containing data and formulas, but also to collect information from different systems, to adapt data coming from one system to the format required by another, to perform operations to enrich or simplify data, etc. In fact, over time many spreadsheets turn out to be used for storing and processing increasing amounts of data and supporting increasing numbers of users. Unfortunately, spreadsheet systems provide poor support for modularity, abstraction, and transformation, thus, making the maintenance, update and evolution of spreadsheets a very complex and error-prone task. We present techniques for model-driven spreadsheet engineering where we employ bidirectional transformations to maintain spreadsheet models and instances synchronized. In our setting, the business logic of spreadsheets is defined by ClassSheet models to which the spreadsheet data conforms, and spreadsheet users may evolve both the model and the data instances. Our techniques are implemented as part of the MDSheet framework: an extension for a traditional spreadsheet system.

Complexity Metrics for Spreadsheet Models, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, and Saraiva João , The 13th International Conference on Computational Science and Its Applications, Volume 7972, p.459–474, (2013) Abstracticcsa-sq13.pdf

This paper proposes a set of metrics for the assessment of the complexity of models defining the business logic of spreadsheets. This set can be considered the first step in the direction of building a quality standard for spreadsheet models, that is still to be defined. The computation of concrete metric values has further been integrated under a well-established model-driven spreadsheet development environment, providing a framework for the analysis of spreadsheet models under spreadsheets themselves.

Design and Implementation of Queries for Model-Driven Spreadsheets, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, Pereira Rui, and Saraiva João , Central European Functional Programming School - 5th Summer School, CEFP 2013, Revised Selected Papers, July, Heidelberg, (2014) Abstractdsl13_query.pdf

This paper presents a domain-specific querying language for model-driven spreadsheets. We briefly show the design of the language and present in detail its implementation, from the denormalization of data and translation of our user-friendly query language to a more efficient query, to the execution of the query using Google. To validate our work, we executed an empirical study, comparing QuerySheet with an alternative spreadsheet querying tool, which produced positive results.

Detecting Anomalous Energy Consumption in Android Applications, Carção, Tiago, Couto Marco, Cunha Jácome, Fernandes João Paulo, and Saraiva João , Proceedings of the 18th Brazilian Symposium on Programming Languages, p.77-91, (2014) Abstractsblp14.pdf

The use of powerful mobile devices, like smartphones, tablets and laptops, are changing the way programmers develop software. While in the past the primary goal to optimize software was the run time optimization, nowadays there is a growing awareness of the need to reduce energy consumption. This paper presents a technique and a tool to detect anomalous energy consumption in Android applications, and to relate it directly with the source code of the application. We propose a dynamically calibrated model for energy consumption for the Android ecosystem, and that supports different devices. The model is then used as an API to monitor the application execution: first, we instrument the application source code so that we can relate energy consumption to the application source code; second, we use a statistical approach, based on fault-localization techniques, to localize abnormal energy consumption in the source code.

Discovery-Based Edit Assistance for Spreadsheets, Cunha, Jácome, Saraiva João, and Visser Joost , Proceedings of the 2009 IEEE Symposium on Visual Languages and Human-Centric Computing (VL/HCC), Washington, DC, USA, p.233–237, (2009) Abstractvl-hcc09.pdf

Spreadsheets can be viewed as a highly flexible end-users programming environment which enjoys wide-spread adoption. But spreadsheets lack many of the structured programming concepts of regular programming paradigms. In particular, the lack of data structures in spreadsheets may lead spreadsheet users to cause redundancy, loss, or corruption of data during edit actions. In this paper, we demonstrate how implicit structural properties of spreadsheet data can be exploited to offer edit assistance to spreadsheet users. Our approach is based on the discovery of functional dependencies among data items which allow automatic reconstruction of a relational database schema. From this schema, new formulas and visual objects are embedded into the spreadsheet to offer features for auto-completion, guarded deletion, and controlled insertion. Schema discovery and spreadsheet enhancement are carried out automatically in the background and do not disturb normal user experience.

Embedding and Evolution of Spreadsheet Models in Spreadsheet Systems, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, and Saraiva João , Proceedings of the 2011 IEEE Symposium on Visual Languages and Human-Centric Computing, Washington, DC, USA, p.186–201, (2011) Abstractvlhcc11.pdf

This paper describes the embedding of ClassSheet models in spreadsheet systems. ClassSheet models are well-known and describe the business logic of spreadsheet data. We embed this domain specific model representation on the (general purpose) spreadsheet system it models. By defining such an embedding, we provide end users a model-driven engineering spreadsheet developing environment. End users can interact with both the model and the spreadsheet data in the same environment. Moreover, we use advanced techniques to evolve spreadsheets and models and to have them synchronized. In this paper we present our work on extending a widely used spreadsheet system with such a model-driven spreadsheet engineering environment.

Embedding Model-Driven Spreadsheet Queries in Spreadsheet Systems, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, Pereira Rui, and Saraiva João , Proceedings of the 2014 IEEE Symposium on Visual Languages and Human-Centric Computing, Washington, DC, USA, p.151-154, (2014) Abstractvlhcc14.pdf

Spreadsheets are widely used not only to define mathematical expressions, but also to store large and complex data. To query such data is usually a difficult task to perform, usually for end user. In this work we embed the textual query language in the model-driven spreadsheet environment as a spreadsheet itself. The result is an expressive and powerful query environment that has knowledge of the business logic defined by the spreadsheet data (the spreadsheet model) to guide end users constructing correct queries.

An Empirical Study on End-users Productivity Using Model-based Spreadsheets, Beckwith, Laura, Cunha Jácome, Fernandes João Paulo, and Saraiva João , Proceedings of the European Spreadsheet Risks Interest Group, July, p.87–100, (2011) Abstracteusprig11.pdf

Spreadsheets are widely used, and studies have shown that most end-user spreadsheets contain non-trivial errors. To improve end-users productivity, recent research proposes the use of a model-driven engineering approach to spreadsheets. In this paper we conduct the first systematic empirical study to assess the effectiveness and efficiency of this approach. A set of spreadsheet end users worked with two different model-based spreadsheets, and we present and analyze here the results achieved.

End-users Productivity in Model-based Spreadsheets: An Empirical Study, Beckwith, Laura, Cunha Jácome, Fernandes João Paulo, and Saraiva João , Proceedings of the Third International Symposium on End-User Development, June, Heidelberg, p.282–288, (2011) Abstractiseud11.pdf

Spreadsheetsarewidelyusedandstudiesshowthatmostoftheexisting ones contain non-trivial errors. To improve end-users productivity, recent research proposes the use of a model-driven engineering approach to spreadsheets. In this paper we conduct the first empirical study to assess the effectiveness and efficiency of this approach. A set of spreadsheet end users worked with two different model-based spreadsheets. We present and analyze here the results achieved.

Energy Efficiency across Programming Languages: How Do Energy, Time, and Memory Relate?, Pereira, Rui, Couto Marco, Ribeiro Francisco, Rua Rui, Cunha Jácome, Fernandes João P., and Saraiva João , 10th ACM SIGPLAN International Conference on Software Language Engineering (SLE’17), 23-24 October, Vancouver, Canada, (2017) paper.pdf
ES-SQL: Visually Querying Spreadsheets, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, Pereira Rui, and Saraiva João , Proceedings of the 2014 IEEE Symposium on Visual Languages and Human-Centric Computing, Washington, DC, USA, p.203–204, (2014) Abstractvlhcc14-td.pdf

This paper presents ES-SQL, an embedded tool for visually constructing queries over spreadsheets. This tool provides an expressive query environment which has knowledge on the business logic of spreadsheets, and by this knowledge it assists the user in defining the intended queries.

Extension and Implementation of ClassSheet Models, Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, and Saraiva João , Proceedings of the 2012 IEEE Symposium on Visual Languages and Human-Centric Computing, Washington, DC, USA, p.19–22, (2012) Abstractvlhcc12.pdf

n this paper we explore the use of models in the context of spreadsheet engineering. We review a successful spreadsheet modeling language, whose semantics we further extend. With this extension we bring spreadsheet models closer to the business models of spreadsheets themselves. An addon for a widely used spreadsheet system, providing bidirectional model-driven spreadsheet development, was also improved to include the proposed model extension.

FaultySheet Detective: When Smells Meet Fault Localization, Abreu, Rui, Cunha Jácome, Fernandes João Paulo, Martins Pedro, Perez Alexandre, and Saraiva João , Proceedings of the 30th IEEE International Conference on Software Maintenance and Evolution, Washington, DC, USA, p.625–628, (2014) Abstracticsme14-td.pdf

This paper presents a tool, dubbed FaultySheet Detective, for aiding in spreadsheet fault localization, which combines the detection of bad smells with a generic spectrum-based fault localization algorithm.

From Relational ClassSheets to UML+OCL, Cunha, Jácome, Fernandes João Paulo, and Saraiva João , Proceedings of the Software Engineering Track at the 27th Annual ACM Symposium On Applied Computing (SAC 2012), p.1151–1158, (2012) Abstractsac-se12.pdf

Spreadsheets are among the most popular programming languages in the world. Unfortunately, spreadsheet systems were not tailored from scratch with modern programming language features that guarantee, as much as possible, program correctness. As a consequence, spreadsheets are populated with unacceptable amounts of errors. In other programming language settings, model-based approaches have been proposed to increase productivity and program effectiveness. Within spreadsheets, this approach has also been followed, namely by ClassSheets. In this paper, we propose an extension to ClassSheets to allow the specification of spreadsheets that can be viewed as relational databases. Moreover, we present a transformation from ClassSheet models to UML class diagrams enriched with OCL constraints. This brings to the spreadsheet realm the entire paraphernalia of model validation techniques that are available for UML.

From Spreadsheets to Relational Databases and Back, Cunha, Jácome, Saraiva João, and Visser Joost , Proceedings of the 2009 ACM SIGPLAN Workshop on Partial Evaluation and Program Manipulation, New York, NY, USA, p.179–188, (2009) Abstractpepm09.pdf

This paper presents techniques and tools to transform spreadsheets into relational databases and back. A set of data refinement rules is introduced to map a tabular datatype into a relational database schema. Having expressed the transformation of the two data models as data refinements, we obtain for free the functions that migrate the data. We use well-known relational database techniques to optimize and query the data. Because data refinements define bidirectional transformations we can map such database back to an optimized spreadsheet. We have implemented the data refinement rules and we have constructed tools to manipulate, optimize and refactor Excel-like spreadsheets.

GreenDroid: A Tool for Analysing Power Consumption in the Android Ecosystem, Couto, Marco, Cunha Jácome, and Fernandes João Paulo , Proceedings of the 13th International Conference Informatics’2015, Propad, Slovakia, p.73-78, (2015) informatics2015.pdf
HaExcel: A Model-Based Spreadsheet Evolution System (Poster), Cunha, Jácome, Fernandes João Paulo, Mendes Jorge, and Saraiva João , 2011 IEEE Symposium on Visual Languages and Human-Centric Computing, September, (2011) Abstractposter.vlhcc11.png

n/a

Helping Programmers Improve the Energy Efficiency of Source Code (Abstract/Poster), Pereira, Rui, Carção Tiago, Couto Marco, Cunha Jácome, Fernandes João P., and Saraiva João , Proceedings of the 39th International Conference on Software Engineering (ICSE 2017), Buenos Aires, Argentina, (2017) paper.pdfpostera3.pdf