NAME
sps - standard spreadsheet module
FUNDAMENTALS
This chapter covers the spreadsheet fundamentals available in the
standard spreadsheet module. The spreadsheet module provides a great
interface to structure data in the form of record and sheets. Once
structured, these data can be indexed, manipulated and exported into
various formats. All AFNIX spreadsheet objects are located in the
afnix-sps module. This module must be loaded prior any operation.
Multiple calls to the module initialization routine are harmless. The
interpreter method module loads a specific module by name. When the
module has been loaded, the object are available in the afnix:sps
nameset.
interp:library "afnix-sps"
Spreadsheet concepts
The sole purpose of using a spreadsheet is to collect various data and
store them in such a way that they can be accessed later. Unlike
standard spreadsheet system, the AFNIX standard spreadsheet module
does not place restrictions on the data organization. The spreadsheet
module stores data in a hierarchical fashion. The basic data element is
called a cell. A set of cells is a record. A set of records is a sheet.
A set of sheets and records is a folio.
Cell and data
A cell is a data container. There is only one data element per cell.
Eventually a name can be associated with a cell. The cell data can be
any kind of literals. Such literals are integer, real, boolean,
character or strings.
Record
A record is a vector of cells. A record can be created by adding cell
or simply by adding data. If the record has a predefined size, the cell
or data can be set by indexing.
Sheet
A sheet is a vector of records. A sheet can be created by adding
record. Similarly, if the sheet has a predefined size, record cell or
data can be added by indexing. A sheet can also be seen as a 2
dimensional array of cells. For the purpose of managing extra
information, the sheet carry also several extra records, namely, the
marker record, the header record and footer record as well as the tag
vector and the signature.
Folio
A folio is a set of sheets and/or records. A folio of sheets permits to
structure data in the form of tables. Since cell, record and table can
have a name, it is possible to create link between various elements,
thus creating a collection of structured data.
Storage model
There are several ways to integrate data. In the simplest form, data
are integrated in a record list. A complex model can be built with a
sheet. More complex models can also be designed by using a folio.
Single record model
With a single record model, the data are accumulated in a single array.
This kind of data storing is particularly adapted for single list
recording. As a single record, the basic search and sorting algorithm
can be applied. For instance, a list name can be stored as a single
record. With this view, there is no difference between a list, a vector
and a record. The record can also be named.
Record importation
Data are imported into the record, either by construction, list or
stream. Since the record object is a serializeable object, the
importation process is also performed automatically in the collection.
The base record importation class implements a simple importation model
based on blank separated literals. Complex importation models can be
devised by derivation. A special case with a cons cell is also
supported where the car is the cell name and the cadr is the cell
object.
# an example of file importation
1 "a string" ’a’
The previous example shows the file structure that can be used to
import cell data. The fist line defines a record with 3 cells. The
second line defines also a record with 3 cells. The second cell is a
named cell.
Record exportation
A record is an object that can be serialized. It can therefore be
exported easily. However, in the serialized form, the record is in a
binary form. It is also possible to walk through the record and
exports, for each cell its literal form.
Folio indexation
There are various ways to access a folio by reference. Since a folio
can contain several sheets, it seems natural to access them by tag. The
other method is to index the cells in a cross-reference album in order
to access rapidly.
Sheet access model
The sheet access model uses a tag to access one or several sheets in a
folio. A tag is a string attached to a sheet. It is possible in a folio
to have several sheet with the same tag. It is also possible to attach
several tags to a sheet. When a folio is searched by tag, the first
sheet that matches the tag is said to be the valid one. If all sheets
that match the requested tag are needed, it is possible to create a
derived folio with all sheets that match the requested tag.
Cell access model
The cell access model operates with a cross-reference table built with
an index. An index is a multiple entry record that stores the cell
location. A cell coordinate comprises the cell index in the record, the
record index in the sheet and the sheet index in the folio. If an index
contains multiple entries, this indicates that several cells are
indexed. A cell cross-reference table is a collection of index.
Generally the index name is the cell name. When the cross-reference
table is built, all cell of interests are scanned and if a cell name
exists, the cell is indexed in the cross-reference table. If there are
several cells with the same name, the index length associated with the
name is the number of cells with that name.
Search and access
The methodology for searching is to decide whether a sheet or a cell
should be accessible. If a sheet access is desired, the search by tag
method is the preferred way. This method assumes that the requested
sheet is structured in a particular way, known to the user. If a cell
access seems more appropriate, a cross-reference table should be built
first, and the search done from it. In the case of search by tag, the
method is dynamic and operates well when sheets are added in a folio.
When a cross-reference table is used, proper care should be taken to
rebuild the cross-reference table when some sheets are added unless the
user knows that there is no need for it.
SERVICES
This chapter covers the spreadsheet objects available in the standard
spreadsheet module. All functions and objects are part of the afnix-sps
module and bounded in the afnix:sps nameset.
Folio object
The Folio object is the primary object used for storing data. Although,
a folio is a collection of sheets, it the primary object that should be
created when manipulating such collection.
Creating a folio
The Folio object is built without argument. In this case, the folio is
empty. A predicate is available for testing the Folio object.
const sps (afnix:sps:Folio)
afnix:sps:folio-p sps # true
The constructor can operate also by name or by input stream. With a
string, a new folio those name is the argument is created. By stream, a
new folio is created and loaded with the input stream data. Eventually,
the folio name can be set with the set-name command and retrieved with
the get-name command.
const sps (afnix:sps:Folio)
sps:set-name "planets"
Sheet object
The Sheet object is the primary object used to store data in a folio.
Since a Folio object is a collection of sheets, a sheet can be
manipulated either by getting getting it from the folio or by creating
it independently and adding it into the folio.
Creating a sheet
An empty sheet can be created simply with or without name. Without
argument, an unnamed sheet is created. Similar to the Folio class, the
sheet name can be passed at construction or set with the set-name
method. As usual a predicate is provided.
const sht (afnix:sps:Sheet)
afnix:sps:sheet-p sht # true
When the sheet is created, it can be added to the folio spreadsheet
with the add method.
const sht (afnix:sps:Sheet "data")
sps:add sht
Adding data to the sheet
The process of adding data to a sheet is a straightforward operation
with the add-data method or the add method. With the add-data method,
data are added as literals. With the add method, data are added with
the help of a record object.
sht:add-data "Mercury" 4840 "1407:36"
sht:add-data "Venus" 12400 "5819:51"
sht:add-data "Earth" 12756 "23:56"
sht:add-data "Mars" 6800 "24:37"
sht:add-data "Jupiter" 142800 "9:50"
sht:add-data "Saturn" 120800 "10:14"
sht:add-data "Uranus" 47600 "10:49"
sht:add-data "Neptune" 44600 "15:40"
sht:add-data "Pluto" 5850 "153:17"
sht:add-data "Sedna" 1800 "960:00"
Data can be imported in a sheet by importation with an input stream.
During the importation, the serialized data are decoded and placed
sequentially in the stream.
Sheet sorting
A sheet can be sorted with the sort method. The sort method uses the
first integer argument as the column number. The second optional
argument is a boolean argument that selects the sorting method which
can be ascending (by default) or descending if the flag is false.
sht:sort 0
sht:sort 1 false
Record object
The Record object is an alternative to the sheet data filling. With the
help of the add method, a record can be added to a sheet.
Creating a record
A record is a named object that acts as a vector of cells. The record
name can be set either by construction or with the set-name method. As
usual a predicate is provided.
const rcd (afnix:sps:Record)
afnix:sps:record-p # true
Filling a record
A record can be filled either with literal data or Cell objects. In the
first case, the cell is automatically created for each literal added to
the record. The add method add a cell or literal to the record.
const rcd (Record)
rcd:add "Mercury" 4840 "1407:36"
For data extraction, the length method returns the record length. Data
can be extracted by index with either the get or map method. The get
method returns the record cell while the map method returns the cell
literal.
Object search
The search methodology is divided either by sheet or cells. In a folio,
the search is done in terms of sheets while the construction of a
cross-reference table is required for searching cells.
Searching in a folio
The Folio object provides the primary mean to search for sheet. The
find and lookup methods return a sheet by tag. The first sheet that
matches the tag is returned by these methods. The find method returns
nil if the sheet cannot be found while the lookup method throws an
exception.
# get a sheet by tag
const sheet (folio:lookup "the tag")
If there are several sheets that matched the tag, the filter method is
more appropriate. The filter method returns a new folio that contains
the sheet that matches the requested tag.
# get a sub-folio by tag
const sf (folio:filter "the tag")
Searching for a cell
The Folio object also provides the method for building a cross-
reference table. The cross-reference table is represented by the Xref
object. By default, a complete Xref object table is built with the
getxref folio method. Such method, scan all cells in the folio and add
them in the cross-reference table if the cell has a name.
# get a default xref table
const xref (folio:getxref)
The cross-reference table can also be built by searching for sheet
tags. With a string argument, the getxref search for all sheets that
matches the tag and then build a cross-reference table. This method is
equivalent to the operation of building a new folio by tag with the
filter method and then building the cross-reference table.
# get a xref table by tag
const xref (folio:getxref "the tag")
# another method
const sf (folio:filter "the tag")
const xref (sf:getxref)
A cross-reference table can also be built by cell index and/or record
index. With a cell index, the cross-reference table is built by
indexing the sheet column. With both the cell and record indexes, the
cross-reference table is built by indexing all cells at the coordinate
for all sheets.
# get a xref table by cell index
const xref (folio:getxref 0)
# get a xref table by cell and record index
const xref (folio:getxref 0 1)
STANDARD SPREADSHEET REFERENCE
This appendix is a reference of the AFNIX standard spreadsheet
module.
Symbol Description
afnix-sps module
afnix:sps nameset
Cell
The Cell class is a data container. There is only one data element per
cell. Eventually a name can be associated with a cell. The cell data
can be any kind of literals. Such literals are integer, real, boolean,
character or strings. A cell is the basic block used to build a
spreadsheet.
Predicate
cell-p
Inheritance
Object
Constructors
Cell (none)
The Cell constructor create a default cell with no name and no
value. When evaluated, the cell returns nil.
Cell (Literal)
The Cell constructor create a default cell by value. The
argument is a literal object which can be viewed with its string
representation.
Cell (String Literal)
The Cell constructor create a default cell by name and value.
The first argument is the cell name. The second argument is a
literal object which can be viewed with its string
representation.
Methods
get -> Literal (none)
The get method returns the cell literal value.
set -> none (Literal)
The set method sets the cell literal value.
get-name -> String (none)
The get-name method returns the cell name.
set-name -> none (String)
The set-name method sets the cell name.
Record
The Record class is a cell container. A record can be created by adding
cell or simply by adding data. If the record has a predefined size, the
cell or data can be set by indexing. A name can be attached to the
record. Facilities are provided to access directly the cell associated
with the record. A record can also be created by name.
Predicate
record-p
Inheritance
Object
Constructors
Record (none)
The Record constructor create an empty record with no name and
no cell.
Record (String)
The Record constructor create an empty record by name name. The
argument is the record name to use.
Methods
add -> none (Cell|Literal...)
The add method adds one or several cell objects to the record.
In the first form, the argument is a cell that is added in the
record. In the second form, the argument is a literal that is
used to create a cell by value.
get -> Cell (Integer)
The get method returns a record cell by index. The index must be
within the record range or an exception is raised.
map -> Literal (Integer)
The map method map a record cell by index. The index must be
within the record range or an exception is raised. The cell is
mapped to a literal object.
set -> none (Integer Cell|Literal)
The set method sets the record cell by index. The record index
must be valid in order to succeed. A new cell is created prior
the record update. The previous cell data is lost including its
name.
sort -> none (none|Boolean)
The sort method sorts a record in place. Without argument, the
record is sorted in ascending order. The optional boolean
argument specifies the sorting mode. If true, the record is
sorting in ascending order and in descending order if false.
find -> Cell (String)
The find method finds a cell by name. If the cell is not found,
the nil object is returned. This method is similar to the lookup
method.
get-index -> Integer (String)
The get-index method finds a cell index by name. If the cell is
not found, an exception is raised. The index is the cell
position in the record.
lookup -> Cell (String)
The lookup method finds a cell by name. If the cell is not
found, an exception is raised. This method is similar to the
find method.
length -> Integer (none)
The length method returns the record length.
get-name -> String (none)
The get-name method returns the record name.
reset -> none (none)
The reset method rest the record. The record name is not changed
but all record cells are removed.
set-name -> none (String)
The set-name method sets the record name.
Sheet
The Sheet class is a record container. A sheet can be created by adding
records. Similarly, if the sheet has a predefined size, record cell or
data can be added by indexing. A sheet can be also seen as a 2
dimensional array of cells. Like a record, a sheet can be named.
Without argument, a default sheet is created. With a string argument,
the sheet is created with an initial name.
Predicate
sheet-p
Inheritance
Object
Constructors
Sheet (none)
The Sheet constructor create an empty sheet with no name and no
cell.
Sheet (String)
The Sheet constructor create an empty sheet by name. The
argument is the sheet name to use.
Sheet (String String)
The Sheet constructor create an empty sheet by name and info.
The first argument is the sheet name to use. The second argument
is the sheet information string.
Methods
add -> none (Record)
The add method adds a record to the sheet.
get -> Record (Integer)
The get method returns a sheet record by index. The index must
be within the sheet range or an exception is raised.
set -> none (Integer Record)
The set method sets the sheet record by index. The sheet index
must be valid in order to succeed.
sort -> none (none|Integer|Boolean|Integer Boolean)
The sort method sorts the sheet in place. By default, the sheet
is sorted in ascending order with column 0. With one argument,
the argument can be either the sorting mode or the column
number. If the mode is true, the sheet is sorted in ascending
mode. If the mode is false, the sheet is sorted in descending
mode. With two arguments, the first argument is the column
number and the second is the sorting mode.
length -> Integer (none)
The length method returns the sheet length.
convert -> PrintTable ([Integer [Integer [Boolean]]])
The convert method converts the sheet into a print-table
representation. Without argument, the whole sheet is converted.
With one argument, the sheet is converted unto a maximum of
rows. The second optional argument is the start index that
default to 0. The third optional argument is the literal format.
By default, the literal is formatted as a string. If the flag is
true, the literal is formatted as a literal string
representation.
add-data -> none ([Cell|Literal]+)
The add-data method adds one or several cell object to a sheet
record. The sheet record is initially created and the object
elements are added sequentially to the record. In the first
form, the argument is a cell that is added in the record. In the
second form, the argument is a literal that is used to create a
cell by value.
add-marker -> none ([Literal]+)
The add-marker method adds one or several literal objects to a
sheet marker record.
marker-length -> Integer (none)
The marker-length method returns the number of markers defined
for this sheet.
get-marker -> Cell (Integer)
The get-marker method returns a marker cell by index from the
sheet marker record.
set-marker -> none (Integer Literal)
The set-marker method set the sheet marker record by index and
literal.
find-marker -> Integer (String)
The find-marker method find the marker index. The argument is
the marker string value.
add-sign -> none ([Literal]+)
The add-sign method adds one or several literal objects to a
sheet sign record.
signature-length -> Integer (none)
The signature-length method returns the number of signs defined
for this sheet.
get-sign -> Cell (Integer)
The get-sign method returns a sign cell by index from the sheet
sign record.
set-sign -> none (Integer Literal)
The set-sign method set the sheet sign record by index and
literal.
find-sign -> Integer (String)
The find-sign method find the sign index. The argument is the
sign string value.
add-header -> none (Cell|Literal...)
The add-header method adds one or several cell object to a sheet
header record. In the first form, the argument is a cell that is
added in the record. In the second form, the argument is a
literal that is used to create a cell by value.
get-header -> Cell (Integer)
The get-header method returns a header cell by index from the
sheet header record.
map-header -> Literal (Integer)
The map-header method maps to a literal object a header cell by
index from the sheet header record.
set-header -> none (Integer Literal)
The set-header method set the header record by index and
literal. The cell associated with the header record is updated
with the literal value.
add-footer -> none ([Cell|Literal]+)
The add-footer method adds one or several cell object to a sheet
footer record. In the first form, the argument is a cell that is
added in the record. In the second form, the argument is a
literal that is used to create a cell by value.
get-footer -> Cell (Integer)
The get-footer method returns a footer cell by index from the
sheet footer record.
map-footer -> Literal (Integer)
The map-footer method maps to a literal object an footer cell by
index from the sheet footer record.
set-footer -> none (Integer Literal)
The set-footer method set the footer record by index and
literal. The cell associated with the footer record is updated
with the literal value.
get-name -> String (none)
The get-name method returns the sheet name.
set-name -> none (String)
The set-name method sets the sheet name.
get-info -> String (none)
The get-info method returns the sheet info.
set-info -> none (String)
The set-info method sets the sheet info.
add-tag -> none ([String]+)
The add-tag method adds a tag into the tags vector. The tag can
be used to mark a sheet in a folio.
tag-p -> Boolean (string)
The tag-p method returns true if the given tag is defined for
this sheet.
tag-length -> Integer (none)
The tag-length method returns the number of tags defined for
this sheet.
get-tag -> String (Integer)
The get-tag method returns a tag by index.
set-tag -> none (Integer Literal)
The set-tag method set the sheet tag record by index and
literal.
find-tag -> Integer (String)
The find-tag method find the tag index. The argument is the tag
string value.
reset -> none (none)
The reset method resets the sheet. The name and information is
unchanged during this operation.
Folio
The Folio class is a sheet container. A folio of sheets can be
structured to handle various data organization. Since all objects are
named, it is possible to index them for fast data search. An empty
folio can be created by name or filled with an input stream.
Predicate
folio-p
Inheritance
Object
Constructors
Folio (none)
The Folio constructor create an empty folio with no name and no
cell.
Folio (String)
The Folio constructor create an empty folio by name. The
argument is the folio name to use.
Folio (String String)
The Folio constructor create an empty folio by name and info.
The first argument is the folio name to use. The second argument
is the folio information string.
Folio (Input)
The Folio constructor create an empty folio and fill it by
reading the data from the input stream. The input stream must
have the data in a serialized form.
Methods
write -> none (Output)
The write method write the folio contents to an output stream.
The written form is a serialized form.
add -> none (Sheet)
The add method adds a sheet to the folio.
get -> Sheet (Integer)
The get method returns a sheet by index. The index must be
within the folio range or an exception is raised.
set -> none (Integer Sheet)
The set method sets the folio by index and sheet. The index must
be within the folio range or an exception is raised.
tag-p -> Boolean (String)
The tag-p method returns true if a sheet with the tag argument
exists in the folio.
find -> Sheet (String)
The find method finds a sheet by tag. The first found sheet
those tag is matched is returned. In the case that multiple
sheet share the same tag, the filter should be used. If no sheet
is found the method return the nil object.
lookup -> Sheet (String)
The lookup method finds a sheet by tag. The first found sheet
those tag is matched is returned. In the case that multiple
sheet share the same tag, the filter should be used. If no sheet
is found the method throws an exception. This method is similar
to the find method.
filter -> Folio (String)
The filter method return a new folio with sheets that match the
argument tag. If no sheet is found, the folio is empty.
reset -> none (none)
The reset method resets the folio. The name and information is
unchanged during this operation.
length -> Integer (none)
The length method returns the folio length.
get-name -> String (none)
The get-name method returns the folio name.
set-name -> none (String)
The set-name method sets the folio name.
get-info -> String (none)
The get-info method returns the folio info.
set-info -> none (String)
The set-info method sets the folio info.
get-xref -> Xref (none|Integer|String|Integer Integer)
The get-xref method returns a cross-reference table from the
folio. Without argument, the whole folio is scanned and all
named cells are added in the cross-reference table. With an
integer argument, all cells that matches the cell index argument
are added in the cross-reference table. With a string argument,
all cells that have the same name are added in the table.
Finally, with two arguments that represents the cell index and
the record index are used, all cells are added in the table with
these two coordinates.
Index
The Index class is a class that maintain a cell index at the folio
level. A cell index is composed of the sheet index, the record index
and the cell index. The index object can be used to access in a generic
way a particular cell. Additionally, the folio name can also be stored
in the index. It is possible to have multiple records that represents
the same cell.
Predicate
index-p
Inheritance
Object
Constructors
Index (none)
The Index constructor creates an empty index.
Index (Integer)
The Index constructor creates an index with a cell index as its
coordinate.
Index (Integer Integer)
The Integer constructor creates an index with a cell and record
indexes as its coordinate. The first argument is the cell index.
The second argument is the record index.
Index (Integer Integer Integer)
The Index constructor creates an index with a cell, record and
sheet indexes as its coordinate. The first argument is the cell
index. The second argument is the record index. The third
argument is the sheet index.
Methods
add -> none (Integer|Integer Integer|Integer Integer Integer)
The add method adds a new index coordinate in the index object.
In the first form, the cell index is used as the coordinate. In
the second form, the cell and record indexes are used as the
coordinate. In the third form, the cell, record and sheet
indexes are used as the coordinate.
reset -> none (none)
The reset method reset the index by removing all attached
coordinates.
length -> Integer (none)
The length method returns the index length. The index length is
the number of coordinate entries in the index.
exists-p -> Boolean (Integer|Integer Integer|Integer Integer
Integer)
The exists-p predicate returns true if a coordinate entry exists
in the index. In the first form, the cell index is used as the
coordinate. In the second form, the cell and record indexes are
used as the coordinate. In the third form, the cell, record and
sheet indexes are used as the coordinate.
set-index-cell -> none (Integer Integer)
The set-index-cell method sets the cell index by position. The
first argument is the coordinate position. The second argument
is the cell index to use.
update-index-cell -> none (Integer)
The update-index-cell method updates the cell index for all
entries in the index. The argument is the new cell index to use
for the update process.
get-index-cell -> Integer (Integer)
The get-index-cell method returns the cell index for a
particular entry. The argument is the entry position.
set-index-record -> none (Integer Integer)
The set-index-record method sets the record index by position.
The first argument is the coordinate position. The second
argument is the record index to use.
update-index-record -> none (Integer)
The update-index-record method updates the record index for all
entries in the index. The argument is the new record index to
use for the update process.
get-index-record -> Integer (Integer)
The get-index-record method returns the record index for a
particular entry. The argument is the entry position.
set-index-sheet -> none (Integer Integer)
The set-index-sheet method sets the sheet index by position. The
first argument is the coordinate position. The second argument
is the cell sheet to use.
update-index-sheet -> none (Integer)
The update-index-sheet method updates the sheet index for all
entries in the index. The argument is the new sheet index to use
for the update process.
get-index-sheet -> Integer (Integer)
The get-index-sheet method returns the sheet index for a
particular entry. The argument is the entry position.
Xref
The Xref class is a cross-reference class. The class maintains the
association between a name and an index. with a particular name, an
index entry is created if it does not exists. Such entry can be later
used to access the cell content by index.
Predicate
xref-p
Inheritance
Object
Constructors
Xref (none)
The Xref constructor creates an empty cross-reference object.
Methods
add -> none (String [Integer|Integer Integer|Integer Integer
Integer])
The add method adds a new reference in the table. The first
argument is always the index name. In the first form, the cell
index is used as the coordinate. In the second form, the cell
and record indexes are used as the coordinate. In the third
form, the cell, record and sheet indexes are used as the
coordinate.
get -> Index (Integer|String)
The get method returns an Index object either by name or
position. With an integer argument, the index is obtained by
position. With a string argument, the index with the matching
name is returned.
reset -> none (none)
The reset method resets the cross-reference table.
length -> Integer (none)
The length method returns the length of the cross-reference
table.
lookup -> Index (String)
The lookup method returns an index those name is the matching
argument. If the index cannot be found, an exception is raised.
exists-p -> Boolean (String)
The exists-p predicate returns true if an index those name is
the matching argument exists in the cross-reference table.
get-name -> String (Integer)
The get-name method returns the index name by position.