What is a Spreadsheet?

A spreadsheet is a rectangular table (or grid) of information. The first spreadsheets were bookkeeping ledgers -- with columns for categories of expenditures across the top, invoices listed down the left margin, and the amount of each payment in the cell where its row and column intersect -- "spread" across facing pages of a bound ledger (book for keeping accounting records).

Here is an applet that supports very simple spreadsheet operations.

Points to note:

• The applet has 10 rows (1 through 10) and 5 columns (a-e).
• Cells can contain either a textual message or a symbolic formula that evaluates to a numerical value. When you click on a cell, the formula for the cell evaluation will be displayed in the textual bar at the top of the applet.
• Note. The "clear" button clears the cells and their formula. This feature worked in Java 1.0 but no longer works Java 5. We need to fix this!!! Appl

The html code that downloads and sets up the applet in a web page is as follows:

<APPLET codebase="http://www.intrepid.com/~robertl/spreadsheet1"
           code="spreadsheet.class" width="600" height="300">
<PARAM NAME="rows" VALUE="10">
<PARAM NAME="cols" VALUE="5">
<PARAM NAME="a1" VALUE="Item">
<PARAM NAME="b1" VALUE="Unit Price">
<PARAM NAME="c1" VALUE="Quantity">
<PARAM NAME="d1" VALUE="Discount (%)">
<PARAM NAME="e1" VALUE="Price">
<PARAM NAME="a3" VALUE="Peaches">
<PARAM NAME="b3" VALUE="4.20">
<PARAM NAME="a4" VALUE="Bananas">
<PARAM NAME="b4" VALUE="3.50">
<PARAM NAME="d3" VALUE="=If(Le(c3,50),0,If(Le(c3,100),10,20))">
<PARAM NAME="e3" VALUE="=c3*b3-b3*c3*d3/100">
<PARAM NAME="d4" VALUE="=If(Le(c4,50),0,If(Le(c4,100),10,20))">
<PARAM NAME="e4" VALUE="=c4*b4-b4*c4*d4/100">
<PARAM NAME="d6" VALUE="Total">
<PARAM NAME="e6" VALUE="=e3+e4">
</APPLET>

Points to note:

• The attribute "codebase" specifies the location on the web from which the spreadsheet java bytecode can be downloaded.
• The attribute "code" specifies the class name at that location.
• Parameters for java applet operation can be initialized with the PARAM tag. For example:

     <PARAM NAME="a1" VALUE="Item">
  

  sets the value of cell "a1" to the character string "Item". Similarly, the value of cell "d3" is defined by specificatrion:

      <PARAM NAME="d3" VALUE="=If(Le(c3,50),0,If(Le(c3,100),10,20))">
  

  If the value of "c3" is less than or equal to 50, then "d3" evaluates to zero. If "c3" is greater than 50 but less than or equal to 100, then "d3" evaluates to 10. Otherwise, "c3" is greater than 100 and the discount rate is set to 20.

Spreadsheet Operation

• The cells of a spreadsheet are functionally equivalent to variables in a sequential programming model. Cells often have a formula (i.e., set of instructions) which can be used to compute the value of a cell. Formulas can use the contents of other cells or external variables such as the current date and time.
• Spreadsheets usually attempt to automatically update cells when the cells on which they depend have been changed. The earliest spreadsheets used simple tactics like evaluating cells in a particular order, but modern spreadsheets compute a minimal recomputation order from the dependency graph, where the nodes are spreadsheet cells, and the edges are references to other cells specified in formulas.
• References between cells can take advantage of spatial concepts such as relative position and absolute position, as well as named locations, to make the spreadsheet formulas easier to understand and manage.

Note. There are questions on this aspect of spreadsheet calculations in the EIT exams.

Cell Syntax

• Numbers must begin with a digit.
• Text cells begin with anything except "=".
• Function cells begin with "=".
• Cell names begin with a lowercase letter, a1 etc.
• Function names begin with a capital letter, Abs etc.

Mathematical Functions

• Abs(x) returns the absolute value of x.
• Acos(x) returns the arccosine of x.
• Asin(x) returns the arcsine of x.
• Atan(x) returns the arctangent of x.
• Ceil(x) returns the smallest integer greater than or equal to x.
• Cos(x) returns the cosine of x.
• Exp(x) returns the exponential of x.
• Floor(x) returns the largest integer less than of equal to x.
• Log(x) returns the natural logarithm of x.
• Max(x, y) returns the larger of x or y.
• Min(x, y) returns the smaller or x or y.
• Pow(x, y) returns x raised to the power y.
• Random() returns a random number.
• Round(x) rounds x to the nearest integer.
• Sin(x) returns the sine of x.
• Sqrt(x) returns the square root of x.
• Tan(x) returns the tangent of x.

Comparision functions

• Lt(x, y) returns 1 if x < y, else 0.
• Le(x, y) returns 1 if x <= y, else 0.
• Gt(x, y) returns 1 if x > y, else 0.
• Ge(x, y) returns 1 if x >= y, else 0.
• Eq(x, y) returns 1 if x = y, else 0.
• Ne(x, y) returns 1 if x != y, else 0.

Boolean functions

• And(x, y) returns 1 if x and y are nonzero, else 0.
• Or(x, y) returns 1 if x or y is nonzero, else 0.
• Not(x) returns 1 if x is zero, else 0.

Conditional functions

• If(x, y) returns y if x is non zero, else 0.
• If(x, y, z) returns y if x is non zero, else z.

• Wikipedia reference for spreadsheets .