Authors
Tie Cheng & Xavier Rival
Abstract
Spreadsheets are widely used, yet are error-prone. In particular, they use a weak type system, which allows certain operations that will silently return unexpected results, like comparisons of integer values with string values. However, discovering these issues is hard, since data and formulas can be dynamically set, read or modified.
We propose a static analysis that detects all run-time type-unsafe operations in spreadsheets. It is based on an abstract interpretation of spreadsheet applications, including spreadsheet tables, global re-evaluation and associated programs.
Our implementation supports the features commonly found in real-world spreadsheets. We ran our analyzer on the EUSES Spreadsheet Corpus. This evaluation shows that our tool is able to automatically verify a large number of real spreadsheets, runs in a reasonable time and discovers complex bugs that are difficult to detect by code review or by testing.
Sample
This example, taken from a realistic spreadsheet application, silently produces wrong results due to improper use of operators, in the presence of a very weak type system, as found in spreadsheet environments.
The wrong result is issued without raising any warning because of the semantics of the language with a very relaxed type discipline.
Such issues are quite common in large spreadsheet applications; they are also hard to diagnose, and usually overlooked by non-experienced users.
Publication
2015, European Symposium on Programming, Lecture Notes in Computer Science, Volume 9032, April, pages 26-52
Full article
Static analysis of spreadsheet applications for type-unsafe operations detection
