Programming in Java Lab 4

In this lab you will create a simple spreadsheet. A spreadsheet cell can contain either a label (text), a value (number) or a formula. The spreadsheet can be displayed in two modes: the evaluated mode shows the values calculated for formulas, and the raw mode shows the text of the formulas. Both modes show the values as numbers and the labels as text.

Your spreadsheet class will contain a two dimensional array of cells. It will also contain the width of each cell (int), the number of rows and columns in the spreadsheet (both int), and the number of decimal places to print when the spreadsheet is displayed (int).

You will need a class for each of the type of objects that can be stored in the spreadsheet – Values, Labels, and Formulas. A Value contains a double, a Label contains a String, and a Formula contains a String which contains the formula. We will allow only very restricted types of formulas (discussed below) to simplify the code needed for evaluation. Since Values, Labels, and Formulas will all be stored in the same two dimensional array (in your spreadsheet class) they must all implement an interface. Then the interface can be used as the type of the array.

Your interface should be called Cellable. Each Cellable class must provide the toString and toDouble methods. The toString method returns a String and has two parameters: the length of the String to be returned (the width of a cell in the spreadsheet) and the number of decimal places to format for numbers (the number of decimal places to print in the spreadsheet). The toDouble method returns a double and has no parameters.

1. For Labels, the toString method should truncate or pad the label with blanks (on the right) so that the String returned is the length that was passed to the method. The toDouble method should return NaN. Recall that NaN is a valid value for a double or a float that indicates an error.



2. The toString method for Values will return a String of the specified length, with the specified number of decimal places. To get the correct number of decimal places, use the round method from the FloatFormatter class (which I will give you). After rounding to the correct number of decimal places, if the resulting String is too big to fit in the length given, return a String "####" of the length given. This will indicate that the value overflowed the allowable area. After rounding to the correct number of decimal places, if the resulting String is smaller than the length given, pad it with blanks on the right so that it is the desired length. The toDouble method should return the number contained in the Value.



3. For formulas, the toString method should truncate or pad the formula (on the right) so that the String returned is the length that was passed to the method. The toDouble method should return NaN.

Note that the value NaN can be accessed through the public static field Double.NaN.

The Cellable classes also need constructors that will allow them to be initialized.

The spreadsheet class needs a constructor that will allow it to be initialized. It also needs methods for changing the cell width, changing the number of decimal places, storing into a particular cell, displaying a particular cell in evaluated mode, displaying a particular cell in raw mode, displaying itself (the whole spreadsheet) in evaluated mode, displaying itself (the whole spreadsheet) in raw mode, and evaluating formulas (used when displaying in evaluated mode). The spreadsheet should display several blank spaces between each cell so that the columns are separated.

Formulas will be restricted to a VERY simple format. If you want to expand this, have fun! Formulas will be restricted to the form <cellref><operator><cellref>. A cellref is a letter (for the column) followed by a digit (for the row). The valid operators are +, -, *, and /. No spaces are allowed in formulas, and the row number is restricted to one digit. (This is the first restriction to lift if you want to allow more flexibility in formulas.) For example, some valid formulas are:

A5*D8
C3+D3
A2-A3

The Formula class should contain a method to return the first or second column reference in the formula (use a parm to decide whether to return the first or second reference), a method to return the first or second row reference (again use a parm to decide whether to return the first or second reference) and a method to return the operator. These methods can be called by the evaluate method in the Spreadsheet class. A formula may not be valid -- it may refer to a cell that doesn't contain a value, in which case the result of the formula should be displayed as "ERR" (padded with blanks on the right so that it is the correct length to fill a cell). A formula may refer to a cell that itself contains a formula. This is valid. The referenced formula must be evaluated to get a number to use in evaluating the referring formula.