Model inference for spreadsheets

Abstract

Many errors in spreadsheet formulas can be avoided if spreadsheets are built automatically from higher-level models that can encode and enforce consistency constraints in the generated spreadsheets. Employing this strategy for legacy spreadsheets is difficult, because the model has to be reverse engineered from an existing spreadsheet and existing data must be transferred into the new model-generated spreadsheet. We have developed and implemented a technique that automatically infers relational schemas from spreadsheets. This technique uses particularities from the spreadsheet realm to create better schemas. We have evaluated this technique in two ways: first, we have demonstrated its applicability by using it on a set of real-world spreadsheets. Second, we have run an empirical study with users. The study has shown that the results produced by our technique are comparable to the ones developed by experts starting from the same (legacy) spreadsheet data. Although relational schemas are very useful to model data, they do not fit spreadsheets well, as they do not allow expressing layout. Thus, we have also introduced a mapping between relational schemas and ClassSheets. A ClassSheet controls further changes to the spreadsheet and safeguards it against a large class of formula errors. The developed tool is a contribution to spread-123 Autom Softw Eng sheet (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.

Table understanding approaches for extracting knowledge from heterogeneous tables

“…The final point that should be highlighted is that functional and structural analysis may also be applied for dynamic error checks in spreadsheet files. In this context, Erwig and Burnett (2002), Abraham and Erwig (2004, 2007), and Cunha, Erwig, Mendes, and Saraiva (2014) interestingly view each excel spreadsheet as composed by “units” (i.e., consecutive parts of rows and cells associated with a label). Each value in a spreadsheet (except blanks) potentially defines a unit, and the unit of any cell is intuitively determined by its headers (Erwig & Burnett, 2002).…”

“…TranSheet (Hung, Benatallah, & Saint‐Paul, 2011) and TabbyXL (Shigarov, Khristyuk, Mikhailov, & Paramonov, 2019) introduce languages for the specification of the transformations needed to simultaneously define the table functional and structural relationships and then convert them into a relational format. Moreover, Senbazuru (Chen et al, 2013) and HaExcel (Cunha et al, 2014) automatically extrapolate the table functional and structural relationships by applying some of the algorithms described in Section 3.2 and then exploit rule‐based algorithms to define and combine the relational tuples representing the structural relationships.…”

“…Differently from the aforementioned approaches, Senbazuru (Chen et al, 2013) automatically infers the table functional and structural relationships through CRFs (Section 2.4); next, based on the retrieved structural information, a tuple builder generates a set of annotated relational tuples, which associate each header to a data value and annotates such tuple with the header attribute; finally, the relation constructor assembles the tuples into a relational table. HaExcel (Cunha et al, 2014) exploits the Fun algorithm (Novelli & Cicchetti, 2001) to automatically extract the functional dependencies in a spreadsheet file and then determines the relational schema, with candidate foreign and primary keys, by using a standard inference procedure from the relational database theory. In the last step, a relational intermediate direct graph is generated.…”

Semi-Automated Formalization and Representation of the Engineering Knowledge Extracted From Spreadsheet Data

“…-partial or step-by-step transformation [19][20][21][22][23]: provide partial solutions using intermediate forms of data and knowledge representation, for example, in the form of conceptual models; they separately solve problems of transforming spreadsheets and conceptual models (mainly in the form of UML class diagrams).…”

EXACT: A tool for comprehending VBA-based Excel spreadsheet applications

“…In some successive works, , they propose a framework for the employment of a Model Driven Development approach for the spreadsheet context. In addition, they evaluated the impact of the proposed Model Driven approach on users' productivity through an empirical study , and in , they perform a further empirically study aimed at comparing the quality of automatically generated relational models against the ones provided by a group of database experts.…”