Automatic detection of dimension errors in spreadsheets

被引:26
作者
Chambers, Chris [1 ]
Erwig, Martin [1 ]
机构
[1] Oregon State Univ, Corvallis, OR 97331 USA
基金
美国国家科学基金会;
关键词
Spreadsheet; Dimension; Unit of measurement; Static analysis; Inference rule; Error detection;
D O I
10.1016/j.jvlc.2009.04.002
中图分类号
TP31 [计算机软件];
学科分类号
081202 ; 0835 ;
摘要
We present a reasoning system for inferring dimension information in spreadsheets. This system can be used to check the consistency of spreadsheet formulas and thus is able to detect errors in spreadsheets. Our approach is based on three static analysis components. First, the spatial structure of the spreadsheet is analyzed to infer a labeling relationship among cells. Second, cells that are used as labels are lexically analyzed and mapped to potential dimensions. Finally, dimension information is propagated through spreadsheet formulas. An important aspect of the rule system defining dimension inference is that it works bi-directionally, that is, not only "downstream" from referenced arguments to the current cell, but also "upstream" in the reverse direction. This flexibility makes the system robust and turns out to be particularly useful in cases when the initial dimension information that can be inferred from headers is incomplete or ambiguous. We have implemented a prototype system as an add-in to Excel. In an evaluation of this implementation we were able to detect dimension errors in almost 50% of the investigated spreadsheets, which shows (i) that the system works reliably in practice and (ii) that dimension information can be well exploited to uncover errors in spreadsheets. (C) 2009 Elsevier Ltd. All rights reserved.
引用
收藏
页码:269 / 283
页数:15
相关论文
共 37 条
[1]   Visual specifications of correct spreadsheets [J].
Abraham, R ;
Erwig, M ;
Kollmansberger, S ;
Seifert, E .
2005 IEEE SYMPOSIUM ON VISUAL LANGUAGE AND HUMAN-CENTRIC COMPUTING, PROCEEDINGS, 2005, :189-196
[2]   Header and unit inference for spreadsheets through spatial analyses [J].
Abraham, R ;
Erwig, M .
2004 IEEE SYMPOSIUM ON VISUAL LANGUAGES AND HUMAN CENTRIC COMPUTING: PROCEEDINGS, 2004, :165-172
[3]  
ABRAHAM R, 2007, IEEE INT S VIS LANG, P215
[4]   UCheck: A spreadsheet type checker for end users [J].
Abraham, Robin ;
Erwig, Martin .
JOURNAL OF VISUAL LANGUAGES AND COMPUTING, 2007, 18 (01) :71-95
[5]  
Abraham R, 2007, PROC INT CONF SOFTW, P251
[6]  
Abraham R, 2006, IEEE SYMPOSIUM ON VISUAL LANGUAGES AND HUMAN-CENTRIC COMPUTING, PROCEEDINGS, P43
[7]   Test-Driven Goal-Directed Debugging in Spreadsheets [J].
Abraham, Robin ;
Erwig, Martin .
2008 IEEE SYMPOSIUM ON VISUAL LANGUAGES AND HUMAN-CENTRIC COMPUTING, PROCEEDINGS, 2008, :131-+
[8]   Mutation Operators for Spreadsheets [J].
Abraham, Robin ;
Erwig, Martin .
IEEE TRANSACTIONS ON SOFTWARE ENGINEERING, 2009, 35 (01) :94-108
[9]  
Ahmad Y, 2003, 18TH IEEE INTERNATIONAL CONFERENCE ON AUTOMATED SOFTWARE ENGINEERING, PROCEEDINGS, P174
[10]  
[Anonymous], 4 S SOFTW TEST AN VE