Spreadsheet is one of the most wildly used end-user development tools. Spreadsheet is used for data storage and tracking, financial reporting, education, and so on. Spreadsheet cells that contain data or formulas often have certain computational semantics. When cells’ data or formulas cannot prescribe the end-users intended semantics, they would suffer from computational semantic errors. This kind of errors could cause data inconsistency, which could degrade the quality of spreadsheets.

    It is hard to identify which cells contain computational semantic errors in spreadsheets, because this involves knowledge of intended semantics, which often requires human judgments or specifications. We observe that spreadsheet cells whose computation is subject to the same semantics are often clustered in a row or column (we name them as a cell array). In spreadsheets, cell arrays may suffer from several kinds of computational semantic errors, such as missing formula errors, inconsistent formula errors, and inconsistent data errors. Based on this observation, we propose two novel approaches to automatically detect and repair cell array-based computational semantic errors in spreadsheets.

    In this dissertation, we have done three pieces of work related to cell array-based computational semantic errors. 

    (1) We do the first empirical study to understand the key properties of cell arrays in real-life spreadsheets on EUSES and Enron corpuses. Our study shows several interesting findings as following: a) cell arrays are widely used in real-life spreadsheet, and about 68.6% of spreadsheet with formulas have used cell arrays. b) About 83.1% of cell arrays obey the isomorphic data dependence, in which each cell reference the cells in the same row/column with the cell as inputs. c) Cell arrays in spreadsheets rarely overlap. Based on this empirical study, we propose two novel approaches to detect and repair cell-array based computational semantic errors.

    (2) We propose SameCheck that detects and repairs cell arrays with isomorphic data dependence. SameCheck assumes that each cell in a cell array references the other cells with the same row/column with this cell as inputs, and proposes a novel algorithm to detect this kind of cell arrays. For the smelly cell arrays, SameCheck propose a novel improved component-based program synthesis to recover the cell arrays’ intended computational semantics. 

    (3) We propose ShareCheck that detects and repairs cell arrays with non-isomorphic data dependence. ShareCheck identifies cell arrays through a novel data dependence similarity of cells, and then leverages the properties (such as, non-overlap) of cell arrays to filter out wrongly identified cell arrays.

    The evaluations on EUSES corpus and case studies on real-life spreadsheets from Institute of Software Chinese Academy of Sciences show that (1) cell array-based computational semantic errors are very common, and they have degraded the quality of existing spreadsheets, (2) SameCheck and ShareCheck can detect and repair cell array-based computational semantic errors precisely, and (3) ShareCheck could detect cell arrays with non-isomorphic data dependence, and besides, could filter out the wrongly identified cell arrays with isomorphic data dependence by SameCheck.