The Spread Package
|
Calling Sequence
|
|
CopySelection( ssid1, ssid2 )
CreateSpreadsheet()
CreateSpreadsheet(ssid)
EvaluateCurrentSelection(ssid)
EvaluateSpreadsheet(ssid)
GetCellFormula(ssid, ref)
GetCellFormula(ssid, row, col)
GetCellValue(ssid, ref)
GetCellValue(ssid, row, col)
GetFormulaeMatrix(ssid)
GetMaxCols(ssid)
GetMaxRows(ssid)
GetSelection(ssid)
GetValuesMatrix(ssid)
InsertMatrixIntoSelection(ssid, M)
IsStale(ssid, ref)
IsStale(ssid, row, col)
SetCellFormula(ssid, ref, val)
SetCellFormula(ssid, row, col, val)
SetMatrix(ssid, M)
SetMatrix(ssid, M, startrow, startcol)
SetSelection(ssid, startrow, startcol, endrow, endcol)
|
|
Parameters
|
|
ssid
|
-
|
spreadsheet identifier
|
ref
|
-
|
cell reference (string or symbol)
|
row
|
-
|
numeric row index
|
col
|
-
|
numeric column index
|
M
|
-
|
Matrix
|
|
|
|
|
Introduction
|
|
•
|
The Spread package is a small package of commands that provides programmatic access to spreadsheet data. This package is available and effective only in versions of Maple that employ a graphical user interface (GUI), because spreadsheets exist only in these interface versions. In particular, this package is ineffective in command-line interfaces to Maple (maple under UNIX, cmaple under Windows, and Command-line Maple under Macintosh).
|
•
|
For an explanation of basic spreadsheet concepts, and an explanation of the interactive manipulation of spreadsheets, see the help topic Overview of Spreadsheets. The help topic Reference Cells contains an explanation of the syntax and use of cell references which is likely to be useful in connection with this package.
|
•
|
Each command in the Spread package can be accessed by using either the long form or the short form of the command name in the command calling sequence.
|
|
As the underlying implementation of the Spread package is a module, it is also possible to use the form Spread:-command to access a command from the package. For more information, see Module Members.
|
•
|
All commands in the package take a spreadsheet identifier (either a string or symbol) as the first argument. This is required for all commands except CreateSpreadsheet. The spreadsheet identifier is the name of the spreadsheet; you must use it to specify the spreadsheet to which an operation is to be applied. To determine the name of a spreadsheet that was created with GUI controls (rather than programmatically with this package), look at the Spreadsheet Name field at the top of the Spreadsheet Properties dialog box. To access this dialog box, right-click the spreadsheet and select the Properties menu item. (In the Standard worksheet interface, the names have the form Spreadsheet(<number>), for example, Spreadsheet(1). In the Classic worksheet interface, the names have the form SpreadSheet###, in which ### is replaced by a numeric string, for example, SpreadSheet001.)
|
|
|
Creating a Spreadsheet
|
|
•
|
The CreateSpreadsheet command creates a spreadsheet. It can be called with no arguments, in which case a system-generated name for the spreadsheet is supplied. Otherwise, the argument ssid must be of type string or name, and it will be used as the name of the spreadsheet. A spreadsheet is created in the worksheet as a side effect of calling this procedure. The spreadsheet can be edited interactively in the GUI, as with any other spreadsheet.
|
•
|
To apply an operation to a spreadsheet, it must actually exist in the GUI, having been created either interactively or by a call to CreateSpreadsheet. Attempting to apply any operation (other than creation) to a nonexistent spreadsheet is an error. Attempting to create a spreadsheet using an identifier that already belongs to a spreadsheet is also an error.
|
|
|
The Spreadsheet Selection
|
|
•
|
A spreadsheet always has a current selection. This is a rectangular block of cells that is deemed to be currently active, and to which some cell-oriented operations are applied by default. The current selection is usually highlighted in the GUI, and may be selected with the mouse.
|
•
|
The current spreadsheet selection for a particular spreadsheet can be retrieved by using the GetSelection command. It requires a single argument ssid, the spreadsheet identifier of the spreadsheet in question. The selection is returned as an expression sequence of four positive integers: nw_x, nw_y, se_x, and se_y. The pair [nw[x], nw[y]] specifies the numeric coordinates of the upper-left (north-west) cell in the selection, while [se[x], se[y]] specifies the numeric coordinates of the lower-right (south-east) cell in the selection. The current spreadsheet selection is always a rectangular box of cells. The expression sequence returned from this procedure can be supplied directly in a call to the SetSelection command.
|
•
|
You can also modify the spreadsheet selection for a specified spreadsheet by using the SetSelection command. Its first argument must be a valid spreadsheet identifier. It also requires four positive integer arguments describing the selection box in the form nw_x, nw_y, se_x, and se_y. This is the same form as is returned by the GetSelection command. The SetSelection command returns the current selection (before modifying it) so that you can restore it later if you want.
|
|
For example, to copy the selection from a spreadsheet foo to a spreadsheet bar, you can issue the call SetSelection(bar, GetSelection(foo)). However, you can also use the CopySelection(foo, bar) command, which is more convenient, as an alternative to the SetSelection(bar, GetSelection(foo) command.
|
|
|
Evaluating Cells
|
|
•
|
To cause all of the stale cells in the current spreadsheet selection to be evaluated, use the EvaluateCurrentSelection command. It takes a single name or string as an argument: a valid spreadsheet identifier. All cells in the current spreadsheet selection for the specified spreadsheet are parsed and evaluated as a side effect. The value NULL is returned.
|
•
|
You can cause the entire spreadsheet to be evaluated by calling the procedure EvaluateSpreadsheet. All stale cells in the spreadsheet associated with the identifier used as the sole argument to this procedure are evaluated as a side effect of the call. The value NULL is returned.
|
|
|
Manipulating Cell Data
|
|
•
|
Each cell has associated with it two Maple expressions that are called the formula and the value of the cell. The formula for a cell is the expression (often involving cell references) that is used to produce a value in the cell; the value is the result of parsing and evaluating the formula.
|
•
|
Note that the formulae in spreadsheet cells are represented as strings (that is, as text) and not as Maple expressions. To produce a Maple object from a cell's formula, it must first be parsed (this happens automatically in the spreadsheet, but Maple code that manipulates cell formulae must perform this step explicitly).
|
•
|
The formula and the value associated with a spreadsheet cell can be retrieved by using the GetCellFormula and GetCellValue commands, respectively. Each command requires a valid spreadsheet identifier as its first argument. The remaining arguments specify the particular cell to query. A cell location can be specified either by an alphanumeric cell reference, such as "B3", or by numeric coordinates, such as [2, 2]. The Maple string or expression for the formula or value is returned.
|
•
|
You can determine whether the current value of a particular cell is out of date with respect to its formula by using the IsStale command. It has the same calling sequence as the GetCellFormula and GetCellValue commands.
|
•
|
The value expression associated with a cell cannot be set externally, because it is derived from the formula associated with that cell. You can set or modify the formula for a cell by using the SetCellFormula command. The first argument to this command must be a valid spreadsheet identifier. The last argument is the formula that is to be stored for the specified cell. The cell is specified by using either the second, or the second and third arguments. To set a cell's formula to value NULL, you must use SetCellFormula(ssid, row, col, 'NULL') or SetCellFormula(ssid, ref, 'NULL').
|
|
|
Block Operations with Matrix Data
|
|
•
|
Sometimes it is convenient to extract spreadsheet data into a Matrix, or to insert data stored in a Matrix into a particular region of a spreadsheet. Several procedures for doing this are provided.
|
•
|
You can extract the formulae from the cells in the current selection by using the GetFormulaeMatrix command. A Matrix that contains the formulae expressions is returned. The values in the cells can be retrieved by using, instead, the GetValuesMatrix command. Neither of these commands ensures that the cells being queried are not stale. To ensure that no extracted cell values are stale, use EvaluateSpreadsheet before calling GetValuesMatrix.
|
•
|
A Matrix containing cell formulae can be inserted into a spreadsheet by using the InsertMatrixIntoSelection and SetMatrix commands. The command InsertMatrixIntoSelection inserts the entries of the Matrix into the current selection (which should be more than 1 cell), clipping the Matrix if necessary to fit into the selection.
|
|
|
Other Queries
|
|
•
|
The GetMaxRows and GetMaxCols commands return (as positive integers) the maximum number of rows and columns, respectively, that can be addressed in a spreadsheet. Each procedure takes a spreadsheet identifier as its sole argument.
|
|
|