Detecting table clones and smells in spreadsheets

Dou, Wensheng; Cheung, Shing-Chi; Gao, Chushu; Xu, Chang; Xu, Liang; Wei, Jun

doi:10.1145/2950290.2950359

Cited by 19 publications

(12 citation statements)

References 32 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…Hermans et al [38] proposed data clone detection in spreadsheets. TableCheck [11] identifies table clones that share the same/similar computational semantics. These two approaches can only identify areas with the same data or computational semantics.…”

Section: Related Workmentioning

confidence: 99%

See 1 more Smart Citation

SpreadCluster: Recovering Versioned Spreadsheets through Similarity-Based Clustering

Dou

Gao

et al. 2017

2017 IEEE/ACM 14th International Conference on Mining Software Repositories (MSR)

Self Cite

View full text Add to dashboard Cite

Version information plays an important role in spreadsheet understanding, maintaining and quality improving. However, end users rarely use version control tools to document spreadsheets' version information. Thus, the spreadsheets' version information is missing, and different versions of a spreadsheet coexist as individual and similar spreadsheets. Existing approaches try to recover spreadsheet version information through clustering these similar spreadsheets based on spreadsheet filenames or related email conversation. However, the applicability and accuracy of existing clustering approaches are limited due to the necessary information (e.g., filenames and email conversation) is usually missing.We inspected the versioned spreadsheets in VEnron, which is extracted from the Enron Corporation. In VEnron, the different versions of a spreadsheet are clustered into an evolution group. We observed that the versioned spreadsheets in each evolution group exhibit certain common features (e.g., similar table headers and worksheet names). Based on this observation, we proposed an automatic clustering algorithm, SpreadCluster. SpreadCluster learns the criteria of features from the versioned spreadsheets in VEnron, and then automatically clusters spreadsheets with the similar features into the same evolution group. We applied SpreadCluster on all spreadsheets in the Enron corpus. The evaluation result shows that SpreadCluster could cluster spreadsheets with higher precision (78.5% vs. 59.8%) and recall rate (70.7% vs. 48.7%) than the filename-based approach used by VEnron. Based on the clustering result by SpreadCluster, we further created a new versioned spreadsheet corpus VEnron2, which is much bigger than VEnron (12,254 vs. 7,294 spreadsheets). We also applied SpreadCluster on the other two spreadsheet corpora FUSE and EUSES. The results show that SpreadCluster can cluster the versioned spreadsheets in these two corpora with high precision (91.0% and 79.8%).

show abstract

Section: Related Workmentioning

confidence: 99%

“…For each corpus, columns[3][4][5][6][7][8][9][10][11] show the numbers of evolution groups. After SpreadCluster (or the filename-based approach[8]) detected evolution groups (Detected), we manually validated all or parts of them (Validated).…”

mentioning

confidence: 99%

SpreadCluster: Recovering Versioned Spreadsheets through Similarity-Based Clustering

Dou

Gao

et al. 2017

2017 IEEE/ACM 14th International Conference on Mining Software Repositories (MSR)

Self Cite

View full text Add to dashboard Cite

show abstract

“…These papers also analyze spreadsheet formulas but do not detail which analysis method or grammar they use for formula parsing. The table clone detection mechanism recently presented by Dou et al in previous study relies on detecting the similarity between spreadsheet formulas but utilizes string comparison of their R1C1 representation, along with undefined techniques for recognizing constants and external references.…”

Section: Related Workmentioning

confidence: 99%

“…Recent research has often focused on analyzing and visualizing spreadsheets. () More recently, researchers have attempted to detect data and table clones in spreadsheets() and to define spreadsheet smells : applications of Fowler's code smells to spreadsheets,() followed by approaches to refactor spreadsheets() and to apply testing practices on spreadsheets . These research works analyze the formulas within spreadsheets and therefore often involve formula parsing.…”

Section: Introductionmentioning

confidence: 99%

“…These research works analyze the formulas within spreadsheets and therefore often involve formula parsing. This is done by using simple grammars, which have not been evaluated (like in Badame and Dig or the formula pattern recovery technique in Dou et al), through implied, undefined grammars (previous studies()) or through entirely bypassing formula parsing and using string comparison operations (Dou et al).…”

Section: Introductionmentioning

confidence: 99%

See 1 more Smart Citation

Parsing Excel formulas: A grammar and its application on 4 large datasets

Aivaloglou

Hoepelman

Hermans

2017

J Software Evolu Process

View full text Add to dashboard Cite

Spreadsheets are popular end user programming tools, especially in the industrial world. This makes them interesting research targets. However, there does not exist a reliable grammar that is concise enough to facilitate formula parsing and analysis and to support research on spreadsheet codebases. This paper presents a grammar for spreadsheet formulas that can successfully parse 99.99% of more than 8 million unique formulas extracted from 4 spreadsheet datasets. Our grammar is compatible with the spreadsheet formula language, recognizes the spreadsheet formula elements that are required for supporting spreadsheets research, and produces parse trees aimed at further manipulation and analysis. Additionally, we use the grammar to analyze the characteristics of the formulas of the 4 datasets in 3 different dimensions: complexity, functionality, and data utilization. Our results show that (1) most Excel formulas are simple, however formulas with more than 50 functions or operations exist, (2) almost all formulas use data from other cells, which is often not local, and (3) a surprising number of referring mechanisms are used by less than 1% of the formulas.

show abstract