Authors
Ana Maria Fernandez-Soriano, Julio Marino, & Angel Herranz
Abstract
Spreadsheets have become widely used tools, but they are applied to increasingly complex problems, far beyond the kind of tasks for which they were originally conceived. This often results in large, hard to maintain sheets, with little guarantee about their correctness. Potential errors are due in part to unskilled users and also to the spreadsheet systems' own limitations.
This contribution presents a tool that aims at improving the usual working flow when filling in a spreadsheet. The tool integrates a constraint solver based on transformations of the cell content into equality and inequality systems over rationals. The transformed systems are then solved using constraint logic programs and the obtained solutions are presented to the user in an understandable way.
One of the practical benefits of this solution based on constraint logic programming is backwards execution: our tool is able to find out the required input values to reach the desired outputs depending on aggregation formulae written in the cells. Also, the constraint model offers a simple and sound solution to the problem of circular references in cell formulas.
Sample
Our tool has five main components:
- Graphic User Interface, a spreadsheet-like grid of rows and columns.
- Front-end component processes each cell to check the syntax is correct and constructs an abstract syntax tree that implements the mathematical objects.
- Back-end component generates a constraint logic programming (CLP) program.
- Prolog Constraint Solver loads the generated program and returns a solution.
- Presenter shows the solution in the GUI component.
Publication
2012, Electronic Notes in Theoretical Computer Science, Volume 282, May, pages 35–45
Full article
A tool for the integration of constraint solving in spreadsheets
