MySheet Spreadsheet Program
[Name, team members (with ID#), project director, due date, etc.]
1
Cover sheet
2
Table of contents Cover sheet.................................................................................................................................................................................2 Table of contents ........................................................................................................................................................................3 Description and user manual ......................................................................................................................................................5 Overview and environment ....................................................................................................................................................5 Features ..................................................................................................................................................................................5 User manual ...........................................................................................................................................................................5 Algorithm Breakout....................................................................................................................................................................7 Cell class ................................................................................................................................................................................7 MySheet class.........................................................................................................................................................................7 Application.............................................................................................................................................................................8 Chart illustrating the data flow of the program ..........................................................................................................................9 Program listing .........................................................................................................................................................................10 Input Data.................................................................................................................................................................................21 Executed Output Samples ........................................................................................................................................................22
3
4
Description and user manual Overview and environment Program MySheet implements a simple spreadsheet prototype. Program is written in C++. Part of the code development was carried out with the GNU c++ on SUN-Solaris. However, the major part of the project, including integration and test runs was done in a PC-Windows environment using the Microsoft Visual C++ development system.
Features MySheet has the following features: - reading of the input file, which represents the spreadsheet (data and formulas) - notion of the current cell and a means to position on any cell (i.e. to make any cell current) - editing of the current cell - supported cell data types include floating-point values, ASCII text strings and formulas - formulas allow to reference horizontal or vertical cell ranges and to perform summation, find minimum and maximum values in the range, and to find the average value of the range - sorting of cell rows' range is supported (for columns containing either all text or all numeric values) - sorting is intelligent in that it does not corrupt the formulas referring to the horizontal cell ranges - string objects from the standard template library are used extensively The program inevitably has a number of misfeatures and omissions due to the fact that not all of the planned code was written and tested by the deadline. The most notable ones are: - error processing and recovery is rudimentary and needs to be significantly strengthened and expanded - destructors are empty and as a result, memory leaks are inevitable. - program uses only standard C++ features, but was not yet tested for portability - user interface is primitive and can be significantly improved - saving of the spreadsheet file was not implemented - dynamic adding of rows and columns to the spreadsheet was not implemented
User manual Starting the program MySheet program is started by typing MySheet to the command prompt, provided that the PATH environment variable includes the corresponding directory. The test spreadsheet file, test.sht must be in the current directory when the program starts up. After start-up, MySheet begins by reading in the test.sht file with the employee sales data. The file data is interpreted by the program and displayed as spreadsheet. The input file is shown in Input Data. Input file format is very simple: tab characters separate columns, and rows are delimited by newline characters. Different operating systems use different newline characters (, , ) and care must be taken to comply with the OS requirements. This is done automatically if the test.sht file is transferred between different computers by the ftp with ascii mode enabled.
User interface design The MySheet program is designed to work with the standard alphanumeric terminal (or in the terminal emulator window). The useful display space of the terminal is limited by the window of 24 lines by 80 columns. Therefore Mysheet displays only first 4 characters of every cell to give the user a general overview of the spreadsheet. The current cell, however, is displayed in full, between the last row of the spreadsheet and the user command prompt (see screen dumps in Executed Output Samples ). The cell is displayed as text followed by its floating-point value in brackets (if there is no floating-point value, 0 is displayed). The Solaris version of the program (port from the Widows to be completed) will differ in that the spreadsheet output will nor scroll, but the screen will be redrawn in place using the terminal escape sequence, to make the user interaction more comfortable. All user commands automatically trigger the recalculation and the redrawing of the spreadsheet. 5
User interaction with the program User input can be one of the 3 following types: 1.
Change the location of the current cell: to make a different cell current (e.g. to display its full contents or to modify its value), type the spreadsheet coordinate of the desired cell). Coordinate syntax is: :=
2.
Columns are numbered with capital letters A, B…, rows are numbered 1,2,… There must be no spaces within the cell coordinate. Example: A3, E14, etc. Modify the contents of the current cell: to modify the contents of the current cell, type 1 followed by the carriage return. The prompt for the new contents will appear on the next line, followed by the current (default) value in brackets. Type the new value followed by the carriage return. Typing just carriage return will retain the current value. A cell can contain a floating-point value, a text string or a formula. If the cell contents can be converted from ascii representation into a numerical value, this conversion is done. If a cell text starts with an "=" character, cell is supposed to contain a formula. All other cases are considered a test string. Formula syntax is: := "="(SUM | MIN | MAX | AVG) :=
3.
Sort rows from current cell down. To do the sort, one must supply 3 parameters to the MySheet program: • • •
Starting row, number of rows to sort and Key for sorting.
To simplify the input of these parameters we introduced the notion of the anchor cell for the sort. First select the anchor cell, by making it current. The anchor cell is used by the program to establish two of the 3 parameters above required for sorting: The contents of the current column will be used as sorting key, and the current row will be the first in the sorting range. Having selected the anchor cell, type 2 to the general prompt. A question for the number of rows to sort will be displayed. When this question is answered, the sorting is performed and the spreadsheet is redrawn. Note that the cells containing formulas referencing horizontal ranges are not moved during the sort procedure to preserve the semantics of the spreadsheet.
6
Algorithm Breakout The program structure is base on two classes - Cell, and MySheet. These classes encapsulate the behaviour of a single spreadsheet cell and of the entire spreadsheet, respectively. Below we present the review of the definition and implementation of these classes together with the main application program. The complete source code is presented Program listing.
Cell class The cell class implements the behaviour of the single spreadsheet cell. The default constructor is never used, instead a constructor is defined which requires the pointer to the parent spreadsheet as a parameter, together with the cell's row and column numbers. The setContents method sets the string value of the cell and calls the cell's parse() method to parse the string value. The parse method determines the contents type, which must be one of VAL - numeric value STR - text string SUM - summation formula AVG - averaging formula MAX - maximum formula MIN - minimum formula ERR - error interprets the string and fills the fields of the cell object accordingly. Finally, the evaluate() method is called for each cell containing a formula during the spreadsheet recalculation. It assumes that cell text contents is already parsed and the cell's fields contain all the necessary data to calculate the formula result. Finally, print() method is called for the cell to print its contents at the current cursor position.
MySheet class An object of the MySheet class implements a spreadsheet as a two-dimensional array of pointers to the objects of the Cell class: Cell *cells[MAXROWS][MAXCOLS]; MySheet class has the following major methods: -
MySheet(int nrows, int ncols) - constructor. Creates and initialises the spreadsheet with given number of rows and columns. void read(string filename) - reads in the input file and fills the cells with the data from file void setCurrent(int r, int c) - sets the object's field "current" to point to the current cell object void setCurrentContents(string s) - sets the value of the current cell by sending a setContents() message to it void recalculate(void) - re-evaluates the spreadsheet by recalculating the values of the cells containing the formulas void print(bool raw) - ouputs the spreadsheet to the screen int parseCellCoord(string &str, int &r, int &c) - a utility function to parse the cell coordinate specified either by user of in the cell formula int parseRange(string &str, int &r_from, int &c_from, int &r_to, int &c_to, bool &hor) - a utility function to parse the cell range specified in the formula void sort(int c, int r1, int r2) - implementation of the bubble sort algorithm with provision to leave the formulas operating on the horizontal range untouched.
7
Application This is the main program started by the user. It creates the object of the MySheet class, sends it a message to read the input file and then controls its behaviour based on the user input. The user interaction was supposed to be encapsulated in a separate class but this was not implemented by the project deadline. Therefore, the application program handles the user input directly by processing the user commands in an infinite loop.
8
Chart illustrating the data flow of the program The diagram below depicts the data flow through the system and the interaction of the program parts.
User interaction
Application
Instance of MySheet class
Input file
Spreadsheet data
Cell object Cell object Cell object Cell objects
9
Program listing // // main program // // Developed with MS Visual C++ // using standard library and in particular string type #include #include #include "mysheet.h" // Cursor control can be added on the standard terminal to avoid scrolling // and to redisplay the spreadsheet "in place" //#define HOME "\x01B[H" //#define CLRSCR "\x01B[J" main () { string char int int
inp; buf[100]; r = 0, c = 0; i, r1, r2, rs, cs;
MySheet *sheet = new MySheet(25, 17); sheet->init(); sheet->read(string("test.sht"));
// read spreadsheet from tab-delimited file
// process user commands in an infinite loop while (true) { sheet->setCurrent(r, c); // cout print(false); cout sheet->rows) || (r2 < 0) )continue; cs = c; sheet->sort(cs, r1, r2); continue; } } return 0; }
11
// // MySheet class interface // #if !defined _MYSHEET_H #define _MYSHEET_H #include #include "cell.h" const int const int
MAXROWS = 256; MAXCOLS = 256;
class MySheet { public: MySheet(); MySheet(int nrows, int ncols); void read(string filename); virtual ~MySheet(); void init(void); void setCurrent(int r, int c); void setCurrentContents(string s); void recalculate(void); void print(bool raw); int parseCellCoord(string &str, int &r, int &c); int parseRange(string &str, int &r_from, int &c_from, int &r_to, int &c_to, bool &hor); void sort(int c, int r1, int r2); Cell Cell
*cells[MAXROWS][MAXCOLS]; *current;
int int string
rows; cols; filename;
}; #endif
12
// // MySheet class implementation // #include #include #include #include
"mysheet.h"
// default constructor MySheet::MySheet() { } MySheet::~MySheet() { // cleanup should go here (destroying cells, etc.) } // Sort rows based on column c. Range of rows to sort is r1..r2 // Value cells are sorted numerically, string cells - lexicographically // Formulae operating on horisontal cell ranges are exempt from swaps. // Simple bubble sort algorithm. void MySheet::sort(int c, int r1, int r2) { int i, j; bool numeric, swap = true; Cell *t; numeric = (cells[r1][c]->type == Cell::VAL); if (numeric) { while (swap) { swap = false; for (i = r1; i < r2; i++) { if (cells[i][c]->value > cells[i+1][c]->value) { swap = true; for (j = 0; j < cols; j++) { // we do not swap formulae acting on a row if (cells[i][j]->cellStr[0] != '=') { t = cells[i][j]; cells[i][j] = cells[i+1][j]; cells[i+1][j] = t; } } } } } } else { while (swap) { swap = false; for (i = r1; i < r2; i++) { if (cells[i][c]->cellStr > cells[i+1][c]->cellStr) { swap = true; for (j = 0; j < cols; j++) { // we do not swap formulae acting on a row if (cells[i][j]->cellStr[0] != '=') { t = cells[i][j]; cells[i][j] = cells[i+1][j]; cells[i+1][j] = t; } } } } } } } // Read the spreadsheet from the tab-delimited ASCII file void MySheet::read(string filename) { ifstream in(filename.c_str()); string str; int r = 0, c = 0, i, k; char buf[256]; while(!in.eof()) {
13
getline(in, str); for (i = k = 0; i < str.length(); i++) { buf[k] = str[i]; if (buf[k] == '\t') { buf[k] = '\0'; setCurrent(r, c); if (buf[0] == '\0') { buf[0] = ' '; buf[1] = '\0'; } setCurrentContents(buf); c++; k = 0; } else { k++; } } buf[k] = '\0'; setCurrent(r, c); if (buf[0] == '\0') { buf[0] = ' '; buf[1] = '\0'; } setCurrentContents(buf); r++; c = 0; }
}
// Initialise the spreadsheet to the maximim required size. // Currently insertion of rows and columns is not supported, so this is static. MySheet::MySheet(int nrows, int ncols) { rows = nrows; cols = ncols; if ((rows >= MAXROWS) || (cols >= MAXCOLS)) { cout setContents(s); } // Set the position of the current cell void MySheet::setCurrent(int r, int c) { if ((r >= rows) || (c >= cols)) { return; } current = cells[r][c]; } // Parce cell coordinates: int MySheet::parseCellCoord(string &str, int &r, int &c) { char ch = str.at(0); int i, rn, cn;
14
if (ch < 'A' || ch > 'Z') { return 1; // error } cn = ch - 'A'; if ((cn < 0) || (cn > cols)) return 1; str = str.substr(1); if ((str[0] < '0') || (str[0] > '9')) return 1; for (i=0; i < str.length(); i++) { ch = str.at(i); if (ch < '0' || ch > '9') { break; } } rn = atoi((str.substr(0, i)).c_str()) - 1; str = str.substr(i); if ((rn < 0) || (rn > rows)) return 1; r = rn; c = cn; return 0; } // Parse the cell range in the form int MySheet::parseRange(string &str, int &r_from, int &c_from, int &r_to, int &c_to, bool &hor) { char ch = str.at(0); if (parseCellCoord(str, r_from, c_from)) { return 1; } if (parseCellCoord(str, r_to, c_to)) { return 1; }
}
hor = (r_from == r_to); return 0;
// Recalculate the spreadsheet void MySheet::recalculate() { int i,j; for (i=0; i < rows; i++) { for (j=0; j < cols; j++) { cells[i][j]->evaluate(); } } } // Print the spreadsheet, truncating long contents to fit on the screen // To see the truncated cell contents, make this cell current void MySheet::print(bool raw) { int i, j; cout value; } } else { // sum a part of a column for (i=r_from; icells[i][c_from]->value; } } value = f; break; case AVG: if (hor) { // avg a part of a row for (i=c_from; icells[r_from][i]->value; } n = c_to - c_from + 1; } else { // avg a part of a column for (i=r_from; icells[i][c_from]->value; } n = r_to - r_from + 1; } value = f / n; break; case MAX: f = -numeric_limits::infinity(); if (hor) { // row for (i=c_from; i app->cells[r_from][i]->value) app->cells[r_from][i]->value; } } else { // column for (i=r_from; i app->cells[i][c_from]->value) app->cells[i][c_from]->value; } } value = f; break; case MIN: f = numeric_limits::infinity(); if (hor) { // row for (i=c_from; icells[r_from][i]->value) app->cells[r_from][i]->value; } } else { // column for (i=r_from; icells[i][c_from]->value) app->cells[i][c_from]->value; } } value = f; break; }
? f :
? f :
? f :
? f :
} // print cell
19
void Cell::print(bool raw) { if (raw) { cout I1 I1
After setting the current cell to I1 A B C D E F G H I J K L M N O P Q 1 Bet Var 19 433 3 7 8 11 4 15 17 5 6 15 23 9 123 2 Bil Scu 8 999 66 23 5 5 16 11 1 9 4 0 34 23 197 3 Cal Buf 12 676 65 4 3 1 8 7 8 33 17 1 3 9 159 4 Che Fun 6 765 76 48 8 3 9 18 7 19 7 5 2 2 204 5 Chu Tru 13 755 87 1 13 5 21 8 11 22 20 2 9 2 201 6 Dir Che 10 675 87 75 4 1 13 0 17 9 4 0 13 25 248 7 Fio Cle 11 866 32 8 5 1 11 6 21 9 8 0 7 15 123 8 Hug Das 7 654 34 0 4 77 4 14 8 15 8 0 6 33 203 9 Jan Bro 2 263 88 68 7 3 2 9 16 19 27 2 3 5 249 10 Jef Stu 15 321 23 72 0 7 2 1 16 21 5 4 9 2 162 11 Joh Smi 1 564 10 46 2 3 2 9 16 15 27 1 3 9 143 12 Joh Dum 16 755 7 0 4 31 32 3 11 21 7 5 4 11 136 13 Kat Swe 3 366 56 0 1 3 7 0 16 15 22 5 3 5 133 14 Luc Sou 4 587 57 0 8 8 6 17 17 19 27 15 2 11 187 15 Mic Fle 18 522 44 0 6 35 7 11 11 4 6 11 7 6 148 16 Pat Cut 5 904 36 67 2 9 5 15 15 15 7 25 2 22 220 17 Rob Rus 17 654 55 0 6 22 7 5 11 0 4 8 4 9 131 18 Rol Mom 9 886 43 23 9 8 15 10 4 9 4 0 11 27 163 19 Ter Hil 20 599 78 14 11 12 4 15 19 8 6 3 4 0 174 20 Wes Wis 14 543 23 0 14 31 15 9 15 11 0 3 9 2 132 21 Tot 970 456 120 276 190 183 257 278 216 105 158 2273436 22 Min 3 0 0 1 2 0 1 0 0 0 2 0 0 23 Max 88 75 14 77 32 18 21 33 27 25 34 33 88 24 Avg 48.522.8 613.8 9.59.1512.8513.910.85.25 7.911.3514.3167 25 I1:4[4] Cell(e.g. N5)-select cell, 1-modify current cell, 2-sort ->2 2 rows to sort (down from current cell) ->19 19
24
After sorting 20 rows by column I A B C D E F G H I J K L M N O P Q 1 Jan Bro 2 263 88 68 7 3 2 9 16 19 27 2 3 5 249 2 Jef Stu 15 321 23 72 0 7 2 1 16 21 5 4 9 2 162 3 Joh Smi 1 564 10 46 2 3 2 9 16 15 27 1 3 9 143 4 Bet Var 19 433 3 7 8 11 4 15 17 5 6 15 23 9 123 5 Hug Das 7 654 34 0 4 77 4 14 8 15 8 0 6 33 203 6 Ter Hil 20 599 78 14 11 12 4 15 19 8 6 3 4 0 174 7 Pat Cut 5 904 36 67 2 9 5 15 15 15 7 25 2 22 220 8 Luc Sou 4 587 57 0 8 8 6 17 17 19 27 15 2 11 187 9 Kat Swe 3 366 56 0 1 3 7 0 16 15 22 5 3 5 133 10 Mic Fle 18 522 44 0 6 35 7 11 11 4 6 11 7 6 148 11 Rob Rus 17 654 55 0 6 22 7 5 11 0 4 8 4 9 131 12 Cal Buf 12 676 65 4 3 1 8 7 8 33 17 1 3 9 159 13 Che Fun 6 765 76 48 8 3 9 18 7 19 7 5 2 2 204 14 Fio Cle 11 866 32 8 5 1 11 6 21 9 8 0 7 15 123 15 Dir Che 10 675 87 75 4 1 13 0 17 9 4 0 13 25 248 16 Rol Mom 9 886 43 23 9 8 15 10 4 9 4 0 11 27 163 17 Wes Wis 14 543 23 0 14 31 15 9 15 11 0 3 9 2 132 18 Bil Scu 8 999 66 23 5 5 16 11 1 9 4 0 34 23 197 19 Chu Tru 13 755 87 1 13 5 21 8 11 22 20 2 9 2 201 20 Joh Dum 16 755 7 0 4 31 32 3 11 21 7 5 4 11 136 21 Tot 970 456 120 276 190 183 257 278 216 105 158 2273436 22 Min 3 0 0 1 2 0 1 0 0 0 2 0 0 23 Max 88 75 14 77 32 18 21 33 27 25 34 33 88 24 Avg 48.522.8 613.8 9.59.1512.8513.910.85.25 7.911.3514.3167 25 I1:2[2] Cell(e.g. N5)-select cell, 1-modify current cell, 2-sort ->1 1 New contents[2]->12 12
After changing the contents of I1 from 2 to 12 A B C D E F G H I J K L M N O P Q 1 Jan Bro 2 263 88 68 7 3 12 9 16 19 27 2 3 5 259 2 Jef Stu 15 321 23 72 0 7 2 1 16 21 5 4 9 2 162 3 Joh Smi 1 564 10 46 2 3 2 9 16 15 27 1 3 9 143 4 Bet Var 19 433 3 7 8 11 4 15 17 5 6 15 23 9 123 5 Hug Das 7 654 34 0 4 77 4 14 8 15 8 0 6 33 203 6 Ter Hil 20 599 78 14 11 12 4 15 19 8 6 3 4 0 174 7 Pat Cut 5 904 36 67 2 9 5 15 15 15 7 25 2 22 220 8 Luc Sou 4 587 57 0 8 8 6 17 17 19 27 15 2 11 187 9 Kat Swe 3 366 56 0 1 3 7 0 16 15 22 5 3 5 133 10 Mic Fle 18 522 44 0 6 35 7 11 11 4 6 11 7 6 148 11 Rob Rus 17 654 55 0 6 22 7 5 11 0 4 8 4 9 131 12 Cal Buf 12 676 65 4 3 1 8 7 8 33 17 1 3 9 159 13 Che Fun 6 765 76 48 8 3 9 18 7 19 7 5 2 2 204 14 Fio Cle 11 866 32 8 5 1 11 6 21 9 8 0 7 15 123 15 Dir Che 10 675 87 75 4 1 13 0 17 9 4 0 13 25 248 16 Rol Mom 9 886 43 23 9 8 15 10 4 9 4 0 11 27 163 17 Wes Wis 14 543 23 0 14 31 15 9 15 11 0 3 9 2 132 18 Bil Scu 8 999 66 23 5 5 16 11 1 9 4 0 34 23 197 19 Chu Tru 13 755 87 1 13 5 21 8 11 22 20 2 9 2 201 20 Joh Dum 16 755 7 0 4 31 32 3 11 21 7 5 4 11 136 21 Tot 970 456 120 276 200 183 257 278 216 105 158 2273446 22 Min 3 0 0 1 2 0 1 0 0 0 2 0 0 23 Max 88 75 14 77 32 18 21 33 27 25 34 33 88 24 Avg 48.522.8 613.8 109.1512.8513.910.85.25 7.911.3514.3583 25 I1:12[12] Cell(e.g. N5)-select cell, 1-modify current cell, 2-sort ->
25
