Detecting and refactoring code smells in spreadsheet formulas

详细信息    查看全文

• 作者：Felienne Hermans ; Martin Pinzger ; Arie van Deursen
• 关键词：Spreadsheets ; Code smells ; Refactoring
• 刊名：Empirical Software Engineering
• 出版年：2015
• 出版时间：April 2015
• 年：2015
• 卷：20
• 期：2
• 页码：549-575
• 全文大小：1,128 KB
• 参考文献：Abraham R, Erwig M (2005) How to communicate unit error messages in spreadsheets. In: Proceedings of WEUSE 鈥?5, pp 1鈥?
  Abraham R, Erwig M (2006) Inferring templates from spreadsheets. In: Proceedings of ICSE 鈥?6, pp 182鈥?91
  Abraham R, Erwig M (2007) Ucheck: a spreadsheet type checker for end users. J Vis Lang Comput 18:71鈥?5. doi:10.鈥?016/鈥媕.鈥媕vlc.鈥?006.鈥?6.鈥?01 View Article
  Alves TL, Ypma C, Visser J (2010) Deriving metric thresholds from benchmark data. In: Proceedings of ICSM 鈥?0. IEEE Computer Society, pp 1鈥?0
  Ayalew Y, Clermont M, Mittermeir RT (2000) Detecting errors in spreadsheets. In: Proceedings of EuSpRIG 鈥?0, pp 51鈥?2
  Badame S, Dig D (2012) Refactoring meets spreadsheet formulas. In: Proceedings of ICSM 鈥?2, pp 399鈥?09
  Bregar A (2004) Complexity metrics for spreadsheet models. In: Proceedings of EuSpRIG 鈥?4, p 9
  Chambers C, Erwig M (2009) Automatic detection of dimension errors in spreadsheets. J Vis Lang Comput 20:269鈥?83View Article
  Cunha J, Fernandes JP, Mendes J, Hugo Pacheco JS (2012) Towards a catalog of spreadsheet smells. In: Proceeding of ICCSA鈥?2. LNCS
  Cunha J, Fernandes JP, Peixoto C, Saraiva J (2012) A quality model for spreadsheets. In: Proceedings of QUATIC 鈥?2, pp 231鈥?36
  Cunha J, Saraiva J, Visser J (2009) Discovery-based edit assistance for spreadsheets. In: Proceedings of VL/HCC 鈥?9, pp 233鈥?37. IEEE
  Erwig M (2009) Software engineering for spreadsheets. IEEE Softw 26:25鈥?0. doi:10.鈥?109/鈥婱S.鈥?009.鈥?40 View Article
  Fisher M, Rothermel G (2005) The EUSES spreadsheet corpus: a shared resource for supporting experimentation with spreadsheet dependability mechanisms. In: Proceedings of WEUSE 鈥?5, pp 47鈥?1
  Fowler M (1999) Refactoring: improving the design of existing code. Addison-Wesley Longman Publishing Co., Inc., Boston
  Hendry DG, Green TRG (1994) Creating, comprehending and explaining spreadsheets: a cognitive interpretation of what discretionary users think of the spreadsheet model. Int J Human-Computer Stud 40(6):1033鈥?065View Article
  Hermans F, Pinzger M, van Deursen A (2010) Automatically extracting class diagrams from spreadsheets. In: Proceedings of ECOOP 鈥?0, pp 52鈥?5
  Hermans F, Pinzger M, van Deursen A (2011) Breviz: spreadsheet visualization and quality analysis. In: Proceedings of EuSpRIG 鈥?1, pp 63鈥?2
  Hermans F, Pinzger M, van Deursen A (2012a) Detecting and visualizing inter-worksheet smells in spreadsheets. In: Proceedings of ICSE 鈥?2, pp 441鈥?51
  Hermans F, Pinzger M, van Deursen A (2012b) Detecting code smells in spreadsheet formulas. In: Proceedings of ICSM 鈥?2, pp 409鈥?18
  Hermans F, Pinzger M, van Deursen A (2012c) Measuring spreadsheet formula understandability. In: Proceedings of EuSpRIG 鈥?2
  Hodnigg K, Mittermeir R (2008) Metrics-based spreadsheet visualization: support for focused maintenance. In: Proceedings of EuSpRIG 鈥?8, p 16
  Hole S, McPhee D, Lohfink A (2009) Mining spreadsheet complexity data to classify end user developers. In: Proceedings of ICDM 鈥?9. CSREA Press, pp 573鈥?79
  Kamiya T, Kusumoto S, Inoue K (2002) Ccfinder: a multilinguistic token-based code clone detection system for large scale source code. TSE 28(7):654鈥?70
  Lanza M, Marinescu R, Ducasse S (2005) Object-oriented metrics in practice. Springer-Verlag, New York, Inc., Secaucus
  Marinescu R (2001) Detecting design flaws via metrics in object-oriented systems. In: Proceedings of TOOLS 鈥?1. IEEE Computer Society, pp 173鈥?82
  Moha N, Gueheneuc YG, Duchien L, Le Meur AF (2010) Decor: a method for the specification and detection of code and design smells. TSE 36(1):20鈥?6View Article
  Nardi B, Miller J (1990) The spreadsheet interface: a basis for end user programming. In: Proceeding of the IFIP conference on human-computer interaction (INTERACT). North-Holland, pp 977鈥?83
  Olbrich S, Cruzes DS, Basili V, Zazworka N (2009) The evolution and impact of code smells: a case study of two open source systems. In: Proceedings of ESEM 鈥?9, pp 390鈥?00
  Panko R (2006) Facing the problem of spreadsheet errors. Decis Line 37(5):8鈥?0
  Panko RR (1998) What we know about spreadsheet errors. J End User Comput 10(2):15鈥?1View Article
  Powell S, Baker K, Lawson B (2009) Errors in operational spreadsheets: a review of the state of the art. In: Proceedings of HICCS 鈥?9. IEEE Computer Society, pp 1鈥?
  Raffensperger J (2009) New guidelines for spreadsheets. Int J Bus Econ 2:141鈥?54
  Rajalingham K, Chadwick D, Knight B, Edwards D (2000) Quality control in spreadsheets: a software engineering-based approach to spreadsheet development. In: Proceedings HICSS 鈥?0, pp 133鈥?43
  Wettel R, Lanza M (2008) Visually localizing design problems with disharmony maps. In: Proceedings of SoftVis 鈥?8, pp 155鈥?64
  Wettel R, Lanza M, Robbes R (2011) Software systems as cities: a controlled experiment. In: Proceedings of ICSE 鈥?1, pp 551鈥?60
  Winston W (2001) Executive education opportunities. OR/MS Today 28(4)
  
• 作者单位：Felienne Hermans (1)
  Martin Pinzger (1)
  Arie van Deursen (1)
  
  1. Delft University of Technology, Mekelweg 4, 2628 CD, Delft, The Netherlands
  
• 刊物类别：Computer Science
• 刊物主题：Software Engineering, Programming and Operating Systems
  Programming Languages, Compilers and Interpreters
  
• 出版者：Springer Netherlands
• ISSN：1573-7616

文摘

Spreadsheets are used extensively in business processes around the world and just like software, spreadsheets are changed throughout their lifetime causing understandability and maintainability issues. This paper adapts known code smells to spreadsheet formulas. To that end we present a list of metrics by which we can detect smelly formulas; a visualization technique to highlight these formulas in spreadsheets and a method to automatically suggest refactorings to resolve smells. We implemented the metrics, visualization and refactoring suggestions techniques in a prototype tool and evaluated our approach in three studies. Firstly, we analyze the EUSES spreadsheet corpus, to study the occurrence of the formula smells. Secondly, we analyze ten real life spreadsheets, and interview the spreadsheet owners about the identified smells. Finally, we generate refactoring suggestions for those ten spreadsheets and study the implications. The results of these evaluations indicate that formula smells are common, that they can reveal real errors and weaknesses in spreadsheet formulas and that in simple cases they can be refactored.