Refactoring meets spreadsheet formulas

refactoring;end-user;spreadsheets;Microsoft Excel;code smells

Badame, Sandro ; Dig ; Daniel

关键词: refactoring;    end-user;    spreadsheets;    Microsoft Excel;    code smells;   

Others  :  https://www.ideals.illinois.edu/bitstream/handle/2142/31155/Badame_Sandro.pdf?sequence=1&isAllowed=y

美国|英语

来源: The Illinois Digital Environment for Access to Learning and Scholarship

【 摘 要 】

The number of end-users who write spreadsheet programs is at least an orderof magnitude larger than the number of trained programmers who writeprofessional software. We studied a corpus of 3691 spreadsheets and we foundthat their formulas are riddled with the same smells that plague professionalsoftware: hardcoded constants, duplicated expressions, unnecessary complex-ity, and unsanitized input. These make spreadsheets difficult to read andexpensive to maintain. Like refactoring of object-oriented code, refactoring ofspreadsheet formulas can be transformative.In this paper we present seven refactorings for spreadsheet formulas imple-mented in RefBook, a plugin for Microsoft Excel. To evaluate the usefulnessof RefBook, we employed three kinds of empirical methods. First, we per-formed a Retrospective Case Study on the EUSES Spreadsheet Corpus with3691 spreadsheets to determine how often we could apply the refactoringssupported by RefBook. Second, we conducted a User Survey with 28 Excelusers to find out whether they preferred the refactored formulas. Third, weconducted a Controlled Experiment with the same 28 participants to measuretheir productivity when doing manual refactorings. The results show: (i) therefactorings are widely applicable, (ii) users prefer the refactored formulas,and (iii) RefBook is faster and safer than manual refactoring. On averageRefBook is able to apply the refactorings in less than half the time that usersperformed the refactorings manually. 92.54% of users introduced errors ornew smells into the spreadsheet or where unable to complete the task.