Static Analysis of Spreadsheet Applications for Type-Unsafe Operations Detection

  • Tie ChengEmail author
  • Xavier Rival
Part of the Lecture Notes in Computer Science book series (LNCS, volume 9032)


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.


False Alarm Abstract State Abstract Interpretation Abstract Domain Abstract Formula 
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.


  1. 1.
    Report of JPMorgan Chase & Co. management task force regarding 2012 CIO losses (January 2013)Google Scholar
  2. 2.
    MS-VBAL: VBA language specification. Tech. rep., Microsoft Corporation (April 2014)Google Scholar
  3. 3.
    Abraham, R., Erwig, M.: Header and unit inference for spreadsheets through spatial analyses. In: Visual Languages and Human-Centric Computing. IEEE Computer Society (2004)Google Scholar
  4. 4.
    Abraham, R., Erwig, M.: UCheck: A spreadsheet type checker for end users. J. Vis. Lang. Comput. (2007)Google Scholar
  5. 5.
    Ahmad, Y., Antoniu, T., Goldwater, S., Krishnamurthi, S.: A type system for statically detecting spreadsheet errors. In: ASE (2003)Google Scholar
  6. 6.
    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)Google Scholar
  7. 7.
    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)Google Scholar
  8. 8.
    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)Google Scholar
  9. 9.
    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)Google Scholar
  10. 10.
    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)Google Scholar
  11. 11.
    Cousot, P., Cousot, R.: Systematic design of program analysis frameworks. In: Principles of Programming Languages. ACM (1979)Google Scholar
  12. 12.
    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)Google Scholar
  13. 13.
    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)Google Scholar
  14. 14.
    Cunha, J., Saraiva, J., Visser, J.: Model-based programming environments for spreadsheets. Science of Computer Programming (2014)Google Scholar
  15. 15.
    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)Google Scholar
  16. 16.
    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)Google Scholar
  17. 17.
    Hermans, F., Pinzger, M., Deursen, A.V.: Detecting and visualizing inter-worksheet smells in spreadsheets. In: International Conference on Software Engineering (2012)Google Scholar
  18. 18.
    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)Google Scholar
  19. 19.
    Jones, S.P., Blackwell, A., Burnett, M.: A user-centred approach to functions in Excel. In: International Conference on Functional Programming. ACM (2003)Google Scholar
  20. 20.
    Miné, A.: The octagon abstract domain. Higher-Order and Symbolic Computation (2006)Google Scholar
  21. 21.
    Panko, R.R.: What we know about spreadsheet errors. Journal of End User Computing (1998)Google Scholar
  22. 22.
    Rajalingham, K., Chadwick, D.R., Knight, B.: Classification of spreadsheet errors. In: EuSpRIG Symposium (2001)Google Scholar
  23. 23.
    Reps, T., Gopan, D., Sagiv, M.: A framework for numeric analysis of array operations. In: Principles of Programming Languages. ACM (2005)Google Scholar
  24. 24.
    Sestoft, P.: Online partial evaluation of sheet-defined functions. EPTCS (2013)Google Scholar
  25. 25.
    Sestoft, P.: Spreadsheet Implementation Technology. Basics and Extensions. MIT Press (2014)Google Scholar
  26. 26.
    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)Google Scholar
  27. 27.
    Wakeling, D.: Spreadsheet functional programming. Journal of Functional Programming (2007)Google Scholar
  28. 28.
    Xi, H., Pfenning, F.: Eliminating array bound checking hrough dependent types. In: Programming Language Design and Implementation. ACM (1998)Google Scholar

Copyright information

© Springer-Verlag Berlin Heidelberg 2015

Authors and Affiliations

  1. 1.CNRSParisFrance
  2. 2.École Normale SupérieureParisFrance
  3. 3.INRIA Paris–RocquencourtParisFrance

Personalised recommendations