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.
The research leading to these results has received funding from the European Research Council under the European Union’s seventh framework programme (FP7/2007-2013), grant agreement 278673, Project MemCAD.
Chapter PDF
Similar content being viewed by others
Keywords
These keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.
References
Report of JPMorgan Chase & Co. management task force regarding 2012 CIO losses (January 2013)
MS-VBAL: VBA language specification. Tech. rep., Microsoft Corporation (April 2014)
Abraham, R., Erwig, M.: Header and unit inference for spreadsheets through spatial analyses. In: Visual Languages and Human-Centric Computing. IEEE Computer Society (2004)
Abraham, R., Erwig, M.: UCheck: A spreadsheet type checker for end users. J. Vis. Lang. Comput. (2007)
Ahmad, Y., Antoniu, T., Goldwater, S., Krishnamurthi, S.: A type system for statically detecting spreadsheet errors. In: ASE (2003)
Antoniu, T., Steckler, P.A., Krishnamurthi, S., Neuwirth, E., Felleisen, M.: Validating the unit correctness of spreadsheet programs. In: International Conference on Software Engineering (2004)
Burnett, M., Atwood, J., Walpole Djang, R., Reichwein, J., Gottfried, H., Yang, S.: Forms/3: A first-order visual language to explore the boundaries of the spreadsheet paradigm (2001)
Cheng, T., Rival, X.: An abstract domain to infer types over zones in spreadsheets. In: Miné, A., Schmidt, D. (eds.) SAS 2012. LNCS, vol. 7460, pp. 94–110. Springer, Heidelberg (2012)
Coblenz, M.J., Ko, A.J., Myers, B.A.: Using objects of measurement to detect spreadsheet errors. In: Visual Languages and Human-Centric Computing (2005)
Cousot, P., Cousot, R.: Abstract interpretation: A unified lattice model for static analysis of programs by construction or approximation of fixpoints. In: Principles of Programming Languages. ACM (1977)
Cousot, P., Cousot, R.: Systematic design of program analysis frameworks. In: Principles of Programming Languages. ACM (1979)
Cousot, P., Cousot, R., Logozzo, F.: A parametric segmentation functor for fully automatic and scalable array content analysis. In: Principles of Programming Languages. ACM (2011)
Cunha, J., Fernandes, J.P., Ribeiro, H., Saraiva, J.: Towards a catalog of spreadsheet smells. In: Murgante, B., Gervasi, O., Misra, S., Nedjah, N., Rocha, A.M.A.C., Taniar, D., Apduhan, B.O. (eds.) ICCSA 2012, Part IV. LNCS, vol. 7336, pp. 202–216. Springer, Heidelberg (2012)
Cunha, J., Saraiva, J., Visser, J.: Model-based programming environments for spreadsheets. Science of Computer Programming (2014)
Fisher II, M., Rothermel, G.: The EUSES Spreadsheet Corpus: A shared resource for supporting experimentation with spreadsheet dependability mechanisms. In: Workshop on End-User Software Engineering (2005)
Hammer, M.A., Phang, K.Y., Hicks, M., Foster, J.S.: Adapton: Composable, demand-driven incremental computation. In: Programming Language Design and Implementation. ACM (2014)
Hermans, F., Pinzger, M., Deursen, A.V.: Detecting and visualizing inter-worksheet smells in spreadsheets. In: International Conference on Software Engineering (2012)
Jensen, S.H., Jonsson, P.A., Møller, A.: Remedying the eval that men do. In: International Symposium on Software Testing and Analysis. ACM (2012)
Jones, S.P., Blackwell, A., Burnett, M.: A user-centred approach to functions in Excel. In: International Conference on Functional Programming. ACM (2003)
Miné, A.: The octagon abstract domain. Higher-Order and Symbolic Computation (2006)
Panko, R.R.: What we know about spreadsheet errors. Journal of End User Computing (1998)
Rajalingham, K., Chadwick, D.R., Knight, B.: Classification of spreadsheet errors. In: EuSpRIG Symposium (2001)
Reps, T., Gopan, D., Sagiv, M.: A framework for numeric analysis of array operations. In: Principles of Programming Languages. ACM (2005)
Sestoft, P.: Online partial evaluation of sheet-defined functions. EPTCS (2013)
Sestoft, P.: Spreadsheet Implementation Technology. Basics and Extensions. MIT Press (2014)
Thiemann, P.: Towards a type system for analyzing javascript programs. In: Sagiv, M. (ed.) ESOP 2005. LNCS, vol. 3444, pp. 408–422. Springer, Heidelberg (2005)
Wakeling, D.: Spreadsheet functional programming. Journal of Functional Programming (2007)
Xi, H., Pfenning, F.: Eliminating array bound checking hrough dependent types. In: Programming Language Design and Implementation. ACM (1998)
Author information
Authors and Affiliations
Corresponding author
Editor information
Editors and Affiliations
Rights and permissions
Copyright information
© 2015 Springer-Verlag Berlin Heidelberg
About this paper
Cite this paper
Cheng, T., Rival, X. (2015). Static Analysis of Spreadsheet Applications for Type-Unsafe Operations Detection. In: Vitek, J. (eds) Programming Languages and Systems. ESOP 2015. Lecture Notes in Computer Science(), vol 9032. Springer, Berlin, Heidelberg. https://doi.org/10.1007/978-3-662-46669-8_2
Download citation
DOI: https://doi.org/10.1007/978-3-662-46669-8_2
Publisher Name: Springer, Berlin, Heidelberg
Print ISBN: 978-3-662-46668-1
Online ISBN: 978-3-662-46669-8
eBook Packages: Computer ScienceComputer Science (R0)