The Standard Spreadsheetmodule is an original implementation that
provides the necessary objects for designing a spreadsheet. A spreasheet
acts a great interface which structure data in the form of record and
sheets. Once structured, these data can be indexed, manipulated and exported
into various formats.
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 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 cellis 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 recordis 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 sheetis 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 recordand
footer recordas well as the tag vectorand the signature.
Folio
A foliois 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 caris the cell
name and the cadris 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.
Folio object
The Folioobject 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 Folioobject is built without argument. In this case, the folio is empty. A
predicate is available for testing the Folioobject.
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-namecommand and retrieved with the
get-namecommand.
const sps (afnix:sps:Folio)
sps:set-name "planets"
Sheet object
The Sheetobject is the primary object used to store data in a folio. Since a
Folioobject 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 Folioclass, the sheet name can
be passed at construction or set with the set-namemethod. 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 addmethod.
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-datamethod or the addmethod. With the add-datamethod, data are added as
literals. With the addmethod, 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 sortmethod. The sortmethod 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 Recordobject is an alternative to the sheet data filling. With the help of
the addmethod, 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-namemethod. 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 Cellobjects. In the first
case, the cell is automatically created for each literal added to the
record. The addmethod add a cell or literal to the record.
const rcd (Record)
rcd:add "Mercury" 4840 "1407:36"
For data extraction, the lengthmethod returns the record length.
Data can be extracted by index with either the getor mapmethod. The
getmethod returns the record cell while the mapmethod 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 Folioobject provides the primary mean to search for sheet. The findand
lookupmethods return a sheet by tag. The first sheet that matches the tag is
returned by these methods. The findmethod returns nil if the sheet cannot be
found while the lookupmethod throws an exception.
# get a sheet by tag
const sheet (folio:lookup "the tag")
If there are several sheets that matched the tag, the filtermethod
is more appropriate. The filtermethod 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 Folioobject also provides the method for building a cross-reference table.
The cross-reference table is represented by the Xrefobject. By default, a
complete Xrefobject table is built with the getxreffolio 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 getxrefsearch 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 filtermethod 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)
Cell
The Cellclass 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
Nameable
Constructors
Cell (none)
The Cellconstructor create a default cell with no name and no value. When
evaluated, the cell returns nil.
Cell (Literal)
The Cellconstructor create a default cell by value. The argument is a literal
object which can be viewed with its string representation.
Cell (String Literal)
The Cellconstructor 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 getmethod returns the cell literal value.
set -> none (Literal)
The setmethod sets the cell literal value.
get-name -> String (none)
The get-namemethod returns the cell name.
set-name -> none (String)
The set-namemethod sets the cell name.
Persist
The Persistclass is a base class for the AFNIX spreadsheet module. The class
defines the methods that are used to read of write a particular persistent
object. When saving, the object name is retreived with the get name method.
The saveas method does the same as save but takes a file name argument.
Predicate
persist-p
Inheritance
Nameable
Methods
save -> none (none)
The savemethod saves the current object.
saveas -> none (String)
The saveasmethod saves the current object into the file those name is the string
argument.
Record
The Recordclass 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
Persist
Constructors
Record (none)
The Recordconstructor create an empty record with no name and no cell.
Record (String)
The Recordconstructor create an empty record by name name. The argument is the
record name to use.
Methods
add -> none (Cell|Literal...)
The addmethod 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 getmethod returns a record cell by index. The index must be within the
record range or an exception is raised.
map -> Literal (Integer)
The mapmethod 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 setmethod 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 sortmethod 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 findmethod finds a cell by name. If the cell is not found, the nil object is
returned. This method is similar to the lookupmethod.
get-index -> Integer (String)
The get-indexmethod 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 lookupmethod finds a cell by name. If the cell is not found, an exception is
raised. This method is similar to the findmethod.
length -> Integer (none)
The lengthmethod returns the record length.
get-name -> String (none)
The get-namemethod returns the record name.
reset -> none (none)
The resetmethod rest the record. The record name is not changed but all record
cells are removed.
set-name -> none (String)
The set-namemethod sets the record name.
Sheet
The Sheetclass 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
Persist
Constructors
Sheet (none)
The Sheetconstructor create an empty sheet with no name and no cell.
Sheet (String)
The Sheetconstructor create an empty sheet by name. The argument is the sheet
name to use.
Sheet (String String)
The Sheetconstructor 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 addmethod adds a record to the sheet.
get -> Record (Integer)
The getmethod returns a sheet record by index. The index must be within the
sheet range or an exception is raised.
set -> none (Integer Record)
The setmethod sets the sheet record by index. The sheet index must be valid in
order to succeed.
sort -> none (none|Integer|Boolean|Integer
Boolean)
The sortmethod 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 lengthmethod returns the sheet length.
convert -> PrintTable ([Integer [Integer
[Boolean]]])
The convertmethod 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-datamethod 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-markermethod adds one or several literal objects to a sheet marker
record.
marker-length -> Integer (none)
The marker-lengthmethod returns the number of markers defined for this
sheet.
get-marker -> Cell (Integer)
The get-markermethod returns a marker cell by index from the sheet marker
record.
set-marker -> none (Integer Literal)
The set-markermethod set the sheet marker record by index and literal.
find-marker -> Integer (String)
The find-markermethod find the marker index. The argument is the marker string
value.
add-sign -> none ([Literal]+)
The add-signmethod adds one or several literal objects to a sheet sign
record.
signature-length -> Integer (none)
The signature-lengthmethod returns the number of signs defined for this
sheet.
get-sign -> Cell (Integer)
The get-signmethod returns a sign cell by index from the sheet sign
record.
set-sign -> none (Integer Literal)
The set-signmethod set the sheet sign record by index and literal.
find-sign -> Integer (String)
The find-signmethod find the sign index. The argument is the sign string
value.
add-header -> none (Cell|Literal...)
The add-headermethod 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-headermethod returns a header cell by index from the sheet header
record.
map-header -> Literal (Integer)
The map-headermethod maps to a literal object a header cell by index from the
sheet header record.
set-header -> none (Integer Literal)
The set-headermethod 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-footermethod 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-footermethod returns a footer cell by index from the sheet footer
record.
map-footer -> Literal (Integer)
The map-footermethod maps to a literal object an footer cell by index from the
sheet footer record.
set-footer -> none (Integer Literal)
The set-footermethod 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-namemethod returns the sheet name.
set-name -> none (String)
The set-namemethod sets the sheet name.
get-info -> String (none)
The get-infomethod returns the sheet info.
set-info -> none (String)
The set-infomethod sets the sheet info.
add-tag -> none ([String]+)
The add-tagmethod 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-pmethod returns true if the given tag is defined for this sheet.
tag-length -> Integer (none)
The tag-lengthmethod returns the number of tags defined for this sheet.
get-tag -> String (Integer)
The get-tagmethod returns a tag by index.
set-tag -> none (Integer Literal)
The set-tagmethod set the sheet tag record by index and literal.
find-tag -> Integer (String)
The find-tagmethod find the tag index. The argument is the tag string
value.
reset -> none (none)
The resetmethod resets the sheet. The name and information is unchanged during
this operation.
Folio
The Folioclass 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
Persist
Constructors
Folio (none)
The Folioconstructor create an empty folio with no name and no cell.
Folio (String)
The Folioconstructor create an empty folio by name. The argument is the folio
name to use.
Folio (String String)
The Folioconstructor 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 (InputStream)
The Folioconstructor 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 (OutputStream)
The writemethod write the folio contents to an output stream. The written form
is a serialized form.
add -> none (Sheet)
The addmethod adds a sheet to the folio.
get -> Sheet (Integer)
The getmethod returns a sheet by index. The index must be within the folio range
or an exception is raised.
set -> none (Integer Sheet)
The setmethod 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-pmethod returns true if a sheet with the tag argument exists in the
folio.
find -> Sheet (String)
The findmethod 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
filtershould be used. If no sheet is found the method return the nil
object.
lookup -> Sheet (String)
The lookupmethod 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
filtershould be used. If no sheet is found the method throws an exception.
This method is similar to the findmethod.
filter -> Folio (String)
The filtermethod return a new folio with sheets that match the argument tag. If
no sheet is found, the folio is empty.
reset -> none (none)
The resetmethod resets the folio. The name and information is unchanged during
this operation.
length -> Integer (none)
The lengthmethod returns the folio length.
get-name -> String (none)
The get-namemethod returns the folio name.
set-name -> none (String)
The set-namemethod sets the folio name.
get-info -> String (none)
The get-infomethod returns the folio info.
set-info -> none (String)
The set-infomethod sets the folio info.
get-xref -> Xref (none|Integer|String|Integer
Integer)
The get-xrefmethod 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 Indexclass 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 Indexconstructor creates an empty index.
Index (Integer)
The Indexconstructor creates an index with a cell index as its coordinate.
Index (Integer Integer)
The Integerconstructor 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 Indexconstructor 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 addmethod 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 resetmethod reset the index by removing all attached coordinates.
length -> Integer (none)
The lengthmethod 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-ppredicate 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-cellmethod 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-cellmethod 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-cellmethod returns the cell index for a particular entry. The
argument is the entry position.
set-index-record -> none (Integer Integer)
The set-index-recordmethod 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-recordmethod 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-recordmethod returns the record index for a particular entry. The
argument is the entry position.
set-index-sheet -> none (Integer Integer)
The set-index-sheetmethod 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-sheetmethod 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-sheetmethod returns the sheet index for a particular entry. The
argument is the entry position.
Xref
The Xrefclass 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 Xrefconstructor creates an empty cross-reference object.
Methods
add -> none (String [Integer|Integer
Integer|Integer Integer Integer])
The addmethod 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 getmethod returns an Indexobject 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 resetmethod resets the cross-reference table.
length -> Integer (none)
The lengthmethod returns the length of the cross-reference table.
lookup -> Index (String)
The lookupmethod 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-ppredicate returns true if an index those name is the matching
argument exists in the cross-reference table.
get-name -> String (Integer)
The get-namemethod returns the index name by position.