<P>
<P>
<CENTER><IMG SRC="../ss-pics/spread.jpg" ALT="figure: spread"></CENTER><A NAME="excel"></A><P><CENTER>Spreadsheet display from Microsoft
Excel
</CENTER><P>
<HTML>
<HEAD>
<TITLE>Simply Scheme: Introducing Computer Science ch 24: Example: A Spreadsheet Program</TITLE>
</HEAD>
<BODY>
<HR>
<CITE>Simply Scheme:</CITE>
<CITE>Introducing Computer Science</CITE> 2/e Copyright (C) 1999 MIT
<H2>Chapter 24</H2>
<H1>Example: A Spreadsheet Program</H1>
<TABLE width="100%"><TR><TD>
<IMG SRC="../simply.jpg" ALT="cover photo">
<TD><TABLE>
<TR><TD align="right"><CITE><A HREF="http://www.cs.berkeley.edu/~bh/">Brian
Harvey</A><BR>University of California, Berkeley</CITE>
<TR><TD align="right"><CITE><A HREF="http://ccrma.stanford.edu/~matt">Matthew
Wright</A><BR>University of California, Santa Barbara</CITE>
<TR><TD align="right"><BR>
<TR><TD align="right"><A HREF="../pdf/ssch24.pdf">Download PDF version</A>
<TR><TD align="right"><A HREF="../ss-toc2.html">Back to Table of Contents</A>
<TR><TD align="right"><A HREF="../ssch23/vectors.html"><STRONG>BACK</STRONG></A>
chapter thread <A HREF="../ssch25/spread-implement.html"><STRONG>NEXT</STRONG></A>
<TR><TD align="right"><A HREF="http://mitpress.mit.edu/0262082810">MIT
Press web page for <CITE>Simply Scheme</CITE></A>
</TABLE></TABLE>
<HR>
<P>Until now, you may have felt that the programs you've been writing in Scheme
don't act like other computer programs you've used. In this chapter and the
next, we're going to tie together almost everything you've learned so far to
write a <EM>spreadsheet</EM> program, just like the ones accountants use.
<P>This chapter describes the operation of the spreadsheet program, as a user
manual would. The next chapter explains how the program is implemented in
Scheme.
<P>You can load our program into Scheme by typing
<P><PRE>(load "spread.scm")
</PRE>
<P>To start the program, invoke the procedure <CODE>spreadsheet</CODE> with no
arguments; to quit the spreadsheet program, type <CODE>exit</CODE>.
<P>
<P>A spreadsheet is a program that displays information in two dimensions on
the screen. It can also compute some of the information automatically. Below
is an example of a display from our spreadsheet program. The
display is a rectangle of information with six columns and 20 rows. The
intersection of a row with a column is called a <EM>cell;</EM> for
example, the cell <CODE>c4</CODE> contains the number 6500. The column letters
(<CODE>a</CODE> through <CODE>f</CODE>) and row numbers are provided by the spreadsheet
program, as is the information on the bottom few lines, which we'll talk
about later. (The <CODE>??</CODE> at the very bottom is the spreadsheet prompt;
you type commands on that line.) We typed most of the entries in the cells,
using commands such as
<P><PRE>(put 6500 c4)
</PRE>
<P>
<PRE> -----a---- -----b---- -----c---- -----d---- -----e---- -----f----
1 NAME NUMBER PRICE GROSS DISCOUNT NET
2 Widget 40.00 1.27 50.80 0.00 50.80
3 Thingo 203.00 14.95 > 3034.85< 15.00 2579.62
4 Computer 1.00 6500.00 6500.00 8.00 5980.00
5 Yacht 300.00 200000.00 60000000.+ 0.00 60000000.+
6
7
8
9 TOTALS 60009585.+ 60008610.+
10
11
12
13
14
15
16
17
18
19
20
d3: 3034.85
(* b3 c3)
??
</PRE>
<P>What's most useful about a spreadsheet is its ability to compute some of the
cell values itself. For example, every number in column <CODE>d</CODE> is the
product of the numbers in columns <CODE>b</CODE> and <CODE>c</CODE> of the same row.
Instead of putting a particular number in a particular cell, we put a <EM>formula</EM> in all the cells of the column at once.<A NAME="text1" HREF="spread.html#ft1">[1]</A> This implies that when we change the value of one cell, other
cells will be updated automatically. For example, if we put the number 5
into cell <CODE>b4</CODE>, the spreadsheet will look like this:
<P>
<PRE> -----a---- -----b---- -----c---- -----d---- -----e---- -----f----
1 NAME NUMBER PRICE GROSS DISCOUNT NET
2 Widget 40.00 1.27 50.80 0.00 50.80
3 Thingo 203.00 14.95 > 3034.85< 15.00 2579.62
4 Computer 5.00 6500.00 32500.00 8.00 29900.00
5 Yacht 300.00 200000.00 60000000.+ 0.00 60000000.+
6
7
8
9 TOTALS 60035585.+ 60032530.+
10
11
12
13
14
15
16
17
18
19
20
d3: 3034.85
(* b3 c3)
??
</PRE>
<P>In addition to cell <CODE>b4</CODE>, the spreadsheet program has changed
the values in <CODE>d4</CODE>, <CODE>f4</CODE>, <CODE>d9</CODE>, and <CODE>f9</CODE>.
<P>One detail we haven't mentioned so far is that at any moment there is one
<EM>selected</EM> cell. Right now cell <CODE>d3</CODE> is selected. You can tell
that because of the arrowheads surrounding it in the display, like this:
<P><PRE>> 3034.85<
</PRE>
<P>Also, the lines
<P><PRE>d3: 3034.85
(* b3 c3)
</PRE>
<P>at the bottom of the screen mean that cell <CODE>d3</CODE> is selected,
its value is <CODE>3034.85</CODE>, and its formula is <CODE>(* b3 c3)</CODE>.
<P><H2>Limitations of Our Spreadsheet</H2>
<P>In commercial spreadsheet programs, you generally select a cell with arrow
keys or by clicking on it with a mouse. The highlighted cell is typically
displayed in inverse video, and there might be thin lines drawn in a grid on
the screen to separate the cells.
<P>Our program leaves all this out for two reasons. First, details like this
don't add very much to what you learn from studying the program, but they
take a disproportionate effort to get exactly right. Second, the facilities
needed in Scheme to control screen graphics are specific to each model of
computer. We couldn't write a single program that would work in all versions
of Scheme. (A program that works in all versions is called <EM>portable.</EM>)
<P>Similarly, our program prints an entire new screenful of information after
every command. A better program would change only the parts of the screen
for which the corresponding values have changed. But there is no uniform
way to ask Scheme to print at a particular position on the screen; some
versions of Scheme can do that, but not using standard procedures.
<P>Also, of course, if you spend $500 on a spreadsheet program, it will have
hundreds of bells and whistles, such as graphing, printing your spreadsheet
on paper, changing the widths of columns, and undoing the last command you
typed. But each of those is a straightforward extension. We didn't write
them all because there are only two of us and we're not getting paid
enough! You'll add some features as exercises.
<P><H2>Spreadsheet Commands</H2>
<P>When you begin the spreadsheet program, you will see an empty grid and a
prompt at the bottom of the screen.
<P>Most spreadsheet programs are controlled using single-keystroke commands (or
equivalent mouse clicks). That's another thing we can't do entirely
portably in Scheme. We've tried to compromise by including one-letter
command names in our program, but you do have to type the <CODE>return</CODE> or
<CODE>enter</CODE> key after each command. The single-letter commands are for
simple operations, such as selecting a cell one position up, down, left, or
right from the previously selected cell.
<P>For more complicated commands, such as entering values, a longer notation
is obviously required. In our program we use a notation that looks very
much like that of a Scheme program: A command consists of a name and some
arguments, all enclosed in parentheses. However, the spreadsheet commands
are <EM>not</EM> Scheme expressions. In particular, the arguments are not
evaluated as they would be in Scheme. For example, earlier we said
<P><PRE>(put 6500 c4)
</PRE>
<P>If this were a Scheme expression, we would have had to quote the
second argument:
<P><PRE>(put 6500 'c4) ;; wrong!
</PRE>
<P><H2>Moving the Selection</H2>
<P>There are four one-letter commands to move to a new selected cell:
<P>
<P><TABLE><TR><TH>Command Name <TH>Meaning
<TR><TR><TD><CODE>f</CODE><TD>move Forward (right)
<TR><TD><CODE>b</CODE><TD>move Back (left)
<TR><TD><CODE>n</CODE><TD>move to Next line (down)
<TR><TD><CODE>p</CODE><TD>move to Previous line (up)
</TABLE>
(These command names are taken from EMACS, the industry
standard text editor.)
<P>If you want to move one step, you can just type the letter <CODE>f</CODE>, <CODE>b</CODE>,
<CODE>n</CODE>, or <CODE>p</CODE> on a line by itself. If you want to move farther, you
can invoke the same commands in Scheme notation, with the distance to move
as an argument:
<P><PRE>?? (f 4)
</PRE>
<P>Another way to move the selection is to choose a particular cell by name.
The command for this is called <CODE>select</CODE>:
<P><PRE>(select e12)
</PRE>
<P>The spreadsheet grid includes columns <CODE>a</CODE> through <CODE>z</CODE> and rows <CODE>1</CODE> through <CODE>30</CODE>. Not all of it can fit on the screen at once. If you
select a cell that's not shown on the screen, then the program will shift
the entire screen display so that the rows and columns shown will include
the newly selected cell.
<P><H2>Putting Values in Cells</H2>
<P>As we've already seen, the <CODE>put</CODE> command is used to put a value in a
particular cell. It can be used with either one or two arguments. The
first (or only) argument is a value. If there is a second argument, it is
the (unquoted) name of the desired cell. If not, the currently selected
cell will be used.
<P>A value can be a number or a quoted word. (As in Scheme programming, most
words can be quoted using the single-quote notation <CODE>'word</CODE>, but words
that include spaces, mixed-case letters, or some punctuation characters must
be quoted using the double-quote string notation <CODE>"Widget"</CODE>.)
However, non-numeric words are used only as labels; they can't provide
values for formulas that compute values in other cells.
<P>The program displays numbers differently from labels. If the value in a
cell is a number, it is displayed at the right edge of its cell, and it is
shown with two digits following the decimal point. (Look again at the
screen samples earlier in this chapter.) If the value is a non-numeric word,
it is displayed at the left edge of its cell.
<P>If the value is too wide to fit in the cell (that is, more than ten
characters wide), then the program prints the first nine characters followed
by a plus sign (<CODE>+</CODE>) to indicate that there is more information than is
visible. (If you want to see the full value in such a cell, select it and
look at the bottom of the screen.)
<P>To erase the value from a cell, you can put an empty list <CODE>()</CODE> in it.
With this one exception, lists are not allowed as cell values.
<P>It's possible to put a value in an entire row or column, instead of just one
cell. To do this, use the row number or the column letter as the second
argument to <CODE>put</CODE>. Here's an example:
<P><PRE>(put 'peter d)
</PRE>
<P>This command will put the word <CODE>peter</CODE> into all the cells in
column <CODE>d</CODE>. (Remember that not all the cells are visible at once, but
even the invisible ones are affected. Cells <CODE>d1</CODE> through <CODE>d30</CODE> are
given values by this command.)
<P>What happens if you ask to fill an entire row or column at once, but some of
the cells already have values? In this case, only the vacant cells will be
affected. The only exception is that if the <EM>value</EM> you are using is
the empty list, indicating that you want to erase old values, then the
entire row or column is affected. (So if you put a formula in an entire row
or column and then change your mind, you must erase the old one before you
can install a new one.)
<P><H2>Formulas</H2>
<P>We mentioned earlier that the value of one cell can be made to depend on the
values of other cells. This, too, is done using the <CODE>put</CODE> command. The
difference is that instead of putting a constant value into a cell, you can
put a formula in the cell. Here's an example:
<P><PRE>(put (+ b3 c5) d6)
</PRE>
<P>This command says that the value in cell <CODE>d6</CODE> should be the
sum of the values in <CODE>b3</CODE> and <CODE>c5</CODE>. The command may or may not have
any immediately visible effect; it depends on whether those two cells
already have values. If so, a value will immediately appear in <CODE>d6</CODE>;
if not, nothing happens until you put values into <CODE>b3</CODE> and <CODE>c5</CODE>.
<P>If you erase the value in a cell, then any cells that depend on it are
also erased. For example, if you erase the value in <CODE>b3</CODE>, then the
value in <CODE>d6</CODE> will disappear also.
<P>So far we've seen only one example of a formula; it asks for the sum of
two cells. Formulas, like Scheme expressions, can include invocations of
functions with sub-formulas as the arguments.
<P><PRE>(put (* d6 (+ b4 92)) a3)
</PRE>
<P>The "atomic" formulas are constants (numbers or quoted words)
and cell references (such as <CODE>b4</CODE> in our example).
<P>Not every Scheme function can be used in a formula. Most important, there
is no <CODE>lambda</CODE> in the spreadsheet language, so you can't invent your own
functions. Also, since formulas can be based only on numbers, only the
numeric functions can be used.
<P>Although we've presented the idea of putting a formula in a cell separately
from the idea of putting a value in a cell, a value is really just a
particularly simple formula. The program makes no distinction internally
between these two cases. (Since a constant formula doesn't depend on any
other cells, its value is always displayed right away.)
<P>We mentioned that a value can be <CODE>put</CODE> into an entire row or column at
once. The same is true of formulas in general. But this capability gives
rise to a slight complication. The typical situation is that each cell in
the row or column should be computed using the same algorithm, but based on
different values. For example, in the spreadsheet at the beginning of the
chapter, every cell in column <CODE>d</CODE> is the product of a cell in column
<CODE>b</CODE> and a cell in column <CODE>c</CODE>, but not the <EM>same</EM> cell for
every row. If we used a formula like
<P><PRE>(put (* b2 c2) d)
</PRE>
<P>then every cell in column <CODE>d</CODE> would have the value <CODE>50.80</CODE>.
Instead we want the equivalent of
<P><PRE>(put (* b2 c2) d2)
(put (* b3 c3) d3)
(put (* b4 c4) d4)
</PRE>
<P>and so on. The spreadsheet program meets this need by providing
a notation for cells that indicates position relative to the cell being
computed, rather than by name. In our case we could say
<P><PRE>(put (* (cell b) (cell c)) d)
</PRE>
<P><CODE>Cell</CODE> can take one or two arguments. In this example we've used the
one-argument version. The argument must be either a letter, to indicate a
column, or a number between 1 and 30, to indicate a row. Whichever
dimension (row or column) is <EM>not</EM> specified by the argument will be
the same as that of the cell being computed. So, for example, if we are
computing a value for cell <CODE>d5</CODE>, then <CODE>(cell b)</CODE> refers to cell <CODE>b5</CODE>, but <CODE>(cell 12)</CODE> would refer to cell <CODE>d12</CODE>.
<P>The one-argument form of <CODE>cell</CODE> is adequate for many situations, but not
if you want a cell to depend on one that's both in a different row and in a
different column. For example, suppose you wanted to compute the change
of a number across various columns, like this:
<P>
<P><PRE> -----a---- -----b---- -----c---- -----d---- -----e---- -----f----
1 MONTH January February March April May
2 PRICE 70.00 74.00 79.00 76.50 81.00
3 CHANGE 4.00 5.00 -2.50 4.50
</PRE>
<P>
<P>The value of cell <CODE>d3</CODE>, for example, is a function of the
values of cells <CODE>d2</CODE> and <CODE>c2</CODE>.
<P>To create this spreadsheet, we said
<P><PRE>(put (- (cell 2) (cell <1 2)) 3)
</PRE>
<P>The first appearance of <CODE>cell</CODE> asks for the value of the cell
immediately above the one being calculated. (That is, it asks for the cell
in row 2 of the same column.) But the one-argument notation doesn't allow
us to ask for the cell above and to the left.
<P>In the two-argument version, the first argument determines the column and
the second determines the row. Each argument can take any of several
forms. It can be a letter (for the column) or number (for the row), to
indicate a specific column or row. It can be an asterisk (<CODE>*</CODE>) to
indicate the same column or row as the cell being calculated. Finally,
either argument can take the form <CODE><3</CODE> to indicate a cell three before
the one being calculated (above or to the left, depending on whether this is
the row or column argument) or <CODE>>5</CODE> to indicate a cell five after this
one (below or to the right).
<P>So any of the following formulas would have let us calculate the change in
this example:
<P><PRE>(put (- (cell 2) (cell <1 2)) 3)
(put (- (cell 2) (cell <1 <1)) 3)
(put (- (cell * 2) (cell <1 2)) 3)
(put (- (cell * <1) (cell <1 2)) 3)
(put (- (cell <0 <1) (cell <1 <1)) 3)
</PRE>
<P>When a formula is put into every cell in a particular row or column, it may
not be immediately computable for all those cells. The value for each cell
will depend on the values of the cells to which the formula refers, and some
of those cells may not have values. (If a cell has a non-numeric value,
that's the same as not having a value at all for this purpose.) New values
are computed only for those cells for which the formula is computable. For
example, cell <CODE>b3</CODE> in the monthly change display has the formula <CODE>(- b2 a2)</CODE>, but the value of cell <CODE>a2</CODE> is the label <CODE>PRICE</CODE>, so no
value is computed for <CODE>b3</CODE>.
<P><H2>Displaying Formula Values</H2>
<P>Formulas can be used in two ways. We've seen that a formula can be
associated with a cell, so that changes to one cell can automatically
recompute the value of another. You can also type a formula directly
to the spreadsheet prompt, in which case the value of the formula will
be shown at the bottom of the screen. In a formula used in this way,
cell references relative to "the cell being computed" refer instead to the
selected cell.
<P><H2>Loading Spreadsheet Commands from a File</H2>
<P>Sometimes you use a series of several spreadsheet commands to set up some
computation. For example, we had to use several commands such as
<P><PRE>(put "Thingo" a3)
</PRE>
<P>to set up the sample spreadsheet displays at the beginning of this
chapter.
<P>If you want to avoid retyping such a series of commands, you can put the
commands in a file using a text editor. Then, in the spreadsheet program,
use the command
<P><PRE>(load "<EM>filename</EM>")
</PRE>
<P>This looks just like Scheme's <CODE>load</CODE> (on purpose), but it's
not the same thing; the file that it loads must contain spreadsheet
commands, not Scheme expressions. It will list the commands from the file
as it carries them out.
<P><H2>Application Programs and Abstraction</H2>
<P>We've talked throughout this book about the importance of abstraction,
the act of giving a name to some process or structure. Writing an
application program can be seen as the ultimate in abstraction. The user of
the program is encouraged to think in a vocabulary that reflects the tasks
for which the program is used, rather than the steps by which the program
does its work. Some of these names are explicitly used to control the
program, either by typing commands or by selecting named choices from a
menu. Our spreadsheet program, for example, uses the name <CODE>put</CODE> for the
task of putting a formula into a cell. The algorithm used by the <CODE>put</CODE>
command is quite complicated, but the user's picture of the command is
simple. Other names are not explicitly used to control the program;
instead, they give the user a <EM>metaphor</EM> with which to think
about the work of the program. For example, in describing the operation of
our spreadsheet program, we've talked about rows, columns, cells, and
formulas. Introducing this vocabulary in our program <EM>documentation</EM>
is just as much an abstraction as introducing new procedures in the program
itself.
<P>In the past we've used procedural abstraction to achieve <EM>generalization</EM> of an algorithm, moving from specific instances to a
more universal capability, especially when we implemented the higher-order
functions. If you've used application programs, though, you've probably
noticed that in a different sense the abstraction in the program <EM>loses</EM> generality. For example, a formula in our spreadsheet program can
operate only on data that are in cells. The same formula, expressed as a
Scheme procedure, can get its arguments from anywhere: from reading a file,
from the keyboard, from a global variable, or from the result of invoking
some other procedure.
<P>An application program doesn't have to be less general than a programming
language. The best application programs are <EM>extensible.</EM> Broadly
speaking, this means that the programmer has made it possible for the user
to add capabilities to the program, or modify existing capabilities. This
broad idea of extensibility can take many forms in practice. Some
kinds of extensibility are more flexible than others; some are easier to use
than others. Here are a few examples:
<P>Commercial spreadsheet programs have grown in extensibility. We mentioned
that our spreadsheet program allows the user to express a function only as a
formula attached to a cell. Modern commercial programs allow the user to
define a procedure, similar in spirit to a Scheme procedure, that can then
be used in formulas.
<P>Our program, on the other hand, is extensible in a different sense: We
provide the Scheme programs that implement the spreadsheet in a form that
users can read and modify. In the next chapter, in fact, you'll be asked to
extend our spreadsheet. Most commercial programs are provided in a form
that computers can read, but people can't. The provision of human-readable
programs is an extremely flexible form of extensibility, but not necessarily
an easy one, since you have to know how to program to take advantage of it.
<P>We have written much of this book using a home computer as a remote terminal
to a larger computer at work. The telecommunication program we're using,
called <EM>Zterm,</EM> has dozens of options. We can set frivolous things
like the screen color and the sound used to attract our attention; we can
set serious things like the file transfer protocol used to "download" a
chapter for printing at home. The program has a directory of telephone
numbers for different computers we use, and we can set the technical details
of the connection separately for each number. It's very easy to customize
the program in all of these ways, because it uses a mouse-driven graphical
interface. But the interface is inflexible. For example, although the
screen can display thousands of colors, only eight are available in Zterm.
More important, if we think of an entirely new feature that would require
a modification to the program, there's no way we can do it. This program's
extensibility is the opposite of that in our spreadsheet: It's very easy
to use, but limited in flexibility.
<P>We started by saying that the abstraction in an application program runs the
risk of limiting the program's generality, but that this risk can be
countered by paying attention to the goal of <EM>extensibility.</EM> The ultimate form of extensibility is to
provide the full capabilities of a programming language to the user of the
application program. This can be done by inventing a special-purpose
language for one particular application, such as the <EM>Hypertalk</EM>
language that's used only in the <EM>Hypercard</EM> application program. Alternatively, the application
programmer can take advantage of an existing general-purpose language by
making it available within the program. You'll see an example soon, in the
database project, which the user controls by typing expressions at a Scheme
prompt. The EMACS text editor is a better-known example that includes a
Lisp interpreter.
<P><H2>Exercises</H2>
<P>
<P><P>For each of the following exercises, the information to hand in is
the sequence of spreadsheet commands you used to carry out the assignment.
You will find the <CODE>load</CODE> command helpful in these exercises.
<P><B>24.1</B> Set up a spreadsheet to keep track of the grades in a course. Each column
should be an assignment; each row should be a student. The last column
should add the grade points from the individual assignments. You can make
predictions about your grades on future assignments
and see what overall numeric grade each prediction gives you.
<P>
<B>24.2</B> Make a table of tax and tip amounts for a range of possible costs at a
restaurant. Column <CODE>a</CODE> should contain the pre-tax amounts, starting at
50 cents and increasing by 50 cents per row. (Do this without entering each
row separately!) Column <CODE>b</CODE> should compute the tax, based on your
state's tax rate. Column <CODE>c</CODE> should compute the 15% tip. Column <CODE>d</CODE> should add columns <CODE>a</CODE> through <CODE>c</CODE> to get the total cost of the
meal. Column <CODE>e</CODE> should contain the same total cost, rounded up to the
next whole dollar amount.
<P>
<B>24.3</B> Make a spreadsheet containing the values from Pascal's triangle: Each
element should be the sum of the number immediately above it and the
number immediately to its left, except that all of column <CODE>a</CODE> should have
the value 1, and all of row 1 should have the value 1.
<P>
<HR>
<A NAME="ft1" HREF="spread.html#text1">[1]</A> We did it by
saying
<P><PRE>(put (* (cell b) (cell c)) d)
</PRE>
<P>but we aren't going to talk about the details of formulas for a
while longer.<P>
<P><A HREF="../ss-toc2.html">(back to Table of Contents)</A><P>
<A HREF="../ssch23/vectors.html"><STRONG>BACK</STRONG></A>
chapter thread <A HREF="../ssch25/spread-implement.html"><STRONG>NEXT</STRONG></A>
<P>
<ADDRESS>
<A HREF="../index.html">Brian Harvey</A>,
<CODE>bh@cs.berkeley.edu</CODE>
</ADDRESS>
</BODY>
</HTML>