The Programming Language Perspective of Spreadsheets

1
The Programming Language Perspective of
Spreadsheets
Martin Erwig Oregon State University
2
Spreadsheets Programs ?
Spreadsheet First-order, non-recursive
functional program
3
Aut Excel aut nullus ?
Does any improvement of spreadsheet programming
have to be an add-on to Excel ?
4
Type Systems for Spreadsheets
Opportunity Type Systems can be extremely
helpful in error detection/prevention, but
Problem An abstract concept of types is very
difficult to impart on end users
Type system
End user
5
End User Type System
Idea Use vocabulary from the spreadsheet to
communicate with the end user
Example Column headers ? Units
6
Unit Checking
http//web.engr.oregonstate.edu/erwig/papers/abst
racts.htmlVLHCC04 http//web.engr.oregonstate.edu
/erwig/papers/abstracts.htmlPADL02
Idea Labels serve as user-defined types
B3 MonthMay FruitApple C3 MonthMay
FruitOrange C4 MonthJune FruitOrange
MonthMay(FruitApple FruitOrange)
MonthMayFruit
?
7
UCheck System Architecture
Frontend
Backend
Excel
VBA
Haskell
Formula Parser
Message Dispatcher
Header Inference
Message Dispatcher
Spreadsheet
Unit Inference
8
Results
Errors found by UCheck reference errors (e.g.
wrong address in formula) range errors (too
small/large range in agg. formula) omission
errors (empty cells)

• 10 expert-level spreadsheets

1 unit error detected in 1 sheet
18 beginner-level spreadsheets
19 unit errors detected in 7 sheets
9
Domain-Specific Support
More robust unit and header inference Domain-spec
ific libraries (a) unit vocabularies (b) header
practices (c) formatting conventions
10
Gencel
Idea Generate spreadsheet customized update
operations from table specification
? No reference, range, omission errors!
Example Budget Sheet
2004
Total
Category
Qnty
Cost
Total
Qnty
Cost
0
0
(L2,L)
SUM(L3)
SUM(L2)
Total
SUM(u)
SUM(u)
11
(No Transcript)
12
Smart Copy Paste
Use references to formulas instead of textual
copies
How to represent changes to copied formulas?
(instantiate to text) Handling of copy chains
A?B?C What happens to C when B is changed?
13
Keep Ranges Correct
Insert row/column behaves inconsistently
Two insert-column commands? (insert left and
insert right) What would normal users think?
14
Bulk Data and Operations
A whole array in a single cell
How does nest/unnest work? What about
printing? (auto-resizing of array cells?) Why
not ranges of cells as arrays?
15
Adding Functions
An obvious extension, ... one might think
How useful? (how often used?, what
savings?) Source of new errors? How to
encourage use of functions?