2.2 Understanding the Sample Applications

After completing the installation, ``cd'' into the ``samples'' subdirectory. In this directory, you will find a simple application which exercises several of the most commonly used features of SpreadScript. There are three versions of the application coded in Perl (simple.pl), Python (simple.py), and Tcl (simple.tcl).

2.2.1 A Look at ``simple.pl''

In this section we will examine the code for the Perl version of the simple SpreadScript example.

2.2.1.1 The header section

Figure 2.1: Header section listing of simple.pl

The first section of code to look at is the header section, which is shown in Figure 2.1. The first line tells the shell to invoke the perl interpreter. Depending on how your system is configured, this line may vary slightly.

The second important line to note is line 13, which instructs Perl to load the ``spreadscript'' module. This extends the Perl interpreter to include all of the commands and functions provided by SpreadScript.

Line 12 tells Perl to use the ``strict'' module, which tells the Perl compiler not allow certain unsafe referencing constructs which are technically legal but represent poor programming practice. This is not required for writing SpreadScript applications, but is highly recommended.

The third important line is line 17. This line defines the variable ``$SPREADSCRIPT_LICENSE,'' which must be set to a string containing the path to your SpreadScript license key. SpreadScript will not run without a valid key.

2.2.1.2 The ``main'' program

Figure 2.2: Main program section listing of simple.pl

The next section of code we will examine is the ``main'' program, lines 76-145, shown in Figure 2.2. Line 79 calls spreadscript::init(), which initializes the SpreadScript module. This function must be called once at the beginning of any SpreadScript program. Its single argument is a string containing the path to the SpreadScript license key.

Line 81 sets SpreadScript to operate in Excel compatibility mode. In this mode, SpreadScript uses Excel-compatible formula syntax and Excel-compatible functions by default. Excel compatibility mode is described in more detail in Section 3.2.1.

Line 87 creates an instance of a ``workbook'' and gives it the name ``$book.'' A workbook is a collection of worksheets which are stored in a single file. Line 88 makes $book the currently active workbook. This must be done before any other operations are performed on it.

Line 91 opens the spreadsheet contained in the file ``sample.xls'' and loads it into the active workbook. In this case, the file is in the native Excel ``.xls'' file format, but it could have just as easily been a native NExS ``.xs3'' file. SpreadScript does not need to be told what type of file is being opened, as it determines this from the actual contents of the file itself.

SpreadScript can read and write NExS .xs3 files even when operating in Excel compatibility mode. All of the appropriate formula and function translations will be automatically handled when reading or writing the .xs3 file. Similarly, SpreadScript does not have to be operating in Excel compatibility mode to read or write Excel .xls files. Excel compatibility mode is simply provided as a convenience for applications designed primarily for Excel users.

Line 94 invokes method getSheet() to create a new reference to the first worksheet in the workbook and assigns it to a variable called ``$sheet.''

Line 97 stores the formula ``=min(a1:a3)'' in the cell in the 3rd row, 2nd column, i.e., cell ``B3'' of the worksheet. This formula says that cell B3 should contain the computed minimum of the values stored in cells A1, A2, and A3. There are several things worth noting here;

• Cell formulas always begin with an equals sign. If you happen to leave it out when invoking setFormula(), SpreadScript will insert it for you.
• Formulas are not case sensitive. This formula could be entered as ``=MIN(A1:A3)'' or ``=Min(A1:a3)'' with the same result.
• SpreadScript (and NExS) natively uses the notation ``..'' as the ``range'' operator to construct a range reference from two cell references. Excel uses a colon for the range operator; e.g., ``=min(a1:a3).'' Because SpreadScript is operating in Excel compatibility mode (line 83) the native Excel syntax is used here. If Excel compatibility mode had not been enabled, this formula would have been entered as ``min(a1..a3).''

Line 98 calls recalc() to recalculate the contents of the worksheet. When entering data in a GUI spreadsheet such as Excel or NExS, by default the worksheet is recalculated each time data is entered or altered in a cell. This causes the computed cells on the worksheet to always appear up-to-date, but will be inefficient if you are entering lots of data. The basic rule to remember is that you can store as much data in the sheet as you like without recalculating, but cells containing formulas will not have their values updated until you call recalc() on the worksheet.

Lines 102 and 103 set the default display format for cells containing numeric data to fixed precision with one place to the right of the decimal point. For example, a cell which contains the value 3.14159265 would be displayed as ``3.1'' when viewed in Excel or NExS. Note that setting the display format does not affect the precision the data stored in the cell. SpreadScript, NExS, and Excel all use IEEE double precision floating point for the actual values stored in cells, and for all calculations. IEEE double precision floating point is accurate to about 17 significant digits.

Line 104 calls subroutine dumpSheet() (see Figure 2.3) which prints the contents of the worksheet. Its details will be examined subsequently.

Lines 108-110 set the default display format for numeric cells in the worksheet to scientific notation with three decimal plances, and then call dumpSheet().

Lines 113-117 set a border around cell B2. When viewed in Excel, cell B2 will have a thin solid line across the top, a medium thickness solid line across the bottom, a dashed line bordering the left side, and a dotted line bordering the right side.

Lines 120 and 121 save the workbook in NExS (.xs3) and Excel 95 (.xls) formats, respectively.

Line 124 creates a new ``Range'' object. A ``range'' is a rectangular region of the sheet, specified by its upper left and lower right cell addresses. In this case, the Range object ``$r'' refers to the range of cells whose upper left corner is row 1, column 1 (cell A1) and whose lower right corner is row 3, column 3 (cell C3). Therefore, $r represents the range A1:C3 (or, in NExS syntax, A1..C3).

Line 125 invokes the exportHTML() method on the range object $r. This writes a file containing an HTML table representation of the contents of range A1:C3.

Lines 128 and 129 create an activate a new workbook object. Line 132 creates a new worksheet within this workbook, and names it ``The Sheet.'' When viewed in Excel, this name will appear on the tab which is used to select the sheet at the bottom of the Excel worksheet window.

Lines 135-140 are a loop which stores the even numbers from 10 through 28 in cells A5 through A14, respectively. Note that if the worksheet contained any formulas which depended on the values stored in these cells, it would be necessary to invoke $sheet$\rightarrow$recalc() to bring these formulas up-to-date. Line 143 calls dumpSheet() to display the worksheet's current contents.

This completes the analysis of the main program. We will next examine the dumpSheet() and dumpCell() subroutines.

2.2.1.3 The dumpSheet() subroutine

Figure 2.3: dumpSheet() subroutine listing from simple.pl

Figure 2.3 displays the subroutine named dumpSheet() from lines 49-75 of simple.pl. This subroutine takes one argument, which is a worksheet object. Line 56 invokes the getName() method to print the name of the worksheet to the standard output. Line 57 invokes the getDimensions() method which returns a range object representing the smallest rectangular range which covers all non-empty cells of the worksheet. Lines 58-61 extract the first and last rows and columns of that range and store them in variables $firstRow, $lastRow, $firstCol, and $lastCol for convenience in iterating over the range.

Lines 63-74 contain a loop which iterates over the range of interest in column-major order, calling dumpCell() (line 69) for each cell within the range.

2.2.1.4 The dumpCell() subroutine

The dumpCell() subroutine is displayed in Figure 2.4. The dumpCell() subroutine prints the contents of a cell to the standard output. It takes three arguments. The first argument is a worksheet object. The second and third arguments are the row and column addresses, respectively, of the cell whose contents are to be printed.

Figure 2.4: dumpCell() subroutine listing from simple.pl

Line 27 invokes the getType() method on the worksheet object to determine what type of value the cell contains. In this case, we are simply interested in determining if the cell contains a formula so that we can print out the text of the formula along with its value. Line 33 calls spreadscript::getTypeString() to convert the cell type to a printable string.

Line 30 invokes the getText() method to get the formatted contents of the cell. This is the text that would be displayed in the cell if it were being viewed with NExS or Excel.

Line 36 prints the row and column address of the cell, the cell's formatted value, and the cell type to the standard output. The ``if'' statement beginning on line 39 checks to see if the cell being printed contains a formula. If so, the getFormula() method is invoked (line 41) to obtain the text of the formula, which is then printed.

2.2.2 Running simple.pl

Assuming that SpreadScript was installed correctly on your system, you should be able to change into the ``samples'' subdirectory and execute simple.pl by typing ./simple.pl at the UNIX command prompt. The output from the program is shown in Figure 2.5.

Figure 2.5: Output listing from simple.pl

In addition to the output of Figure 2.5, which is printed on the terminal, simple.pl writes three new files in the ``samples'' subdirectory: sample_out.xls, sample_out.xs3, and sample_out.html.

2.2.3 Python and Tcl Versions of the ``simple'' Script

The ``samples'' subdirectory contains simple.py, a Python translation of simple.pl, and simple.tcl, a Tcl translation of the script. All three versions of the script perform exactly the same work, and we will leave it to the reader to study the Python and Tcl versions.

CGI Programming Hint: When returning an Excel spreadsheet from a CGI script, be sure to specify ``Content-type: application/vnd.ms-excel'' in the HTTP header.

2.2.4 SpreadScript Error Handling

SpreadScript reports a large number of errors and warnings. These are handled through the respective exception mechanisms of the Perl, Python, and Tcl script language interpreters.

2.2.4.1 Python Example

Suppose you attempt to write an Excel file to a directory for which you do not have write permission from line 18 of a SpreadScript Python script:

    book.save("/bin/echo_out.xls", spreadscript.SS_EXCEL_95)  <<-- line 18

This will produce the following Python traceback:

Traceback (innermost last):
  File "foo.py", line 18, in ?
    book.save("/bin/echo_out.xls", spreadscript.SS_EXCEL_95)
  File "../python/spreadscript.py", line 129, in save
    val = apply(spreadscriptc.Workbook_save,(self,) + _args, _kwargs)
RuntimeError: ** SpreadScript Exception:  cannot write file -- XLS write /bin/echo_out.xls

2.2.4.2 Perl Example

Suppose we modify line 91 of ``simple.pl'' (see Figure 2.2) such that we attempt to get worksheet 2, which doesn't exist:

my $book = new Workbook();
$book->activate();
$book->open("sample.xls");

my $sheet = $book->getSheet(2);  <<--- there is actually only one sheet

The error message produced by the Perl interpreter is not as helpful as for Python, as it requires an examination of the SpreadScript Perl Module, spreadscript.pm, to learn which method caused the exception:

 The sheet index is too large. at ../perl/spreadscript.pm line 317.

Looking at spreadscript.pm:317 reveals that the error occurs in the getSheet() call.

2.2.4.3 Tcl Example

Making the same error as above (i.e., attempting to reference sheet 2) in simple.tcl generates the following exception:

The sheet index is too large.
    while executing
"book getSheet 2"
    (file "simple.tcl" line 82)