An Excelsior program consists of constant declarations, type declarations, table declarations, equations, and layout descriptions. Each of these kinds of statement ends with a full stop. You can write statements in any order at all; Excelsior will sort them into the order it needs when it compiles your program.
layout is free-format. Programs can include
// comments which comment out
everything up to the end of the line, and
comments, which comment out everything they
enclose. For a different set of commenting conventions,
less typical of programming languages but
which I believe are better suited to documenting
programs, I have
implemented Literate Excelsior.
The Excelsior compiler can read program text from any text file. However, I recommend that Excelsior files have the extension .exc, and that Literate Excelsior files have the extension .lexc. As I improve Excelsior, some tools may rely on this convention; Literate Excelsior already does, in deciding which files to preprocess.
Here is an example program:
constant low=1. // Low bound of type. constant high=10. // High bound. type range1 = low:high. /* This type uses the constant declarations. */ type range2 = 1:20. type range3 = 1:5. table t0 : -> general. table t1 : range1 -> currency. table t2 : range2 -> general. table t3 : range1 range2 -> percent. table t4 : range1 range2 -> text. macro table t5 : range3 range1 -> general. table t6 : range3 range1 -> text. t0 = 1. t1[ r1 ] = r1 * 10. t2[ 1 ] = 100. t2[ r2 > 1 ] = r2 * 100. t3[ 1, 1 ] = 1 / 100. t3[ r1>1, r2>1 ] = r1/100 + r2/1000. t4[ x, y ] = "|" & t3[ x, y ] & "|". t5[ a, b ] = len( t4[ a, b ] ). t6[ p, q ] = t5[ p, q ] & " : " & t4[ p, q ]. layout( 'Sheet 1' , rows( row( 'Sheet 1' ) , row( skip ) , row( 't0', 't1' , 't2' , 't3' , 't4' ) , row( t0 , t1 as x, t2 as y, t3 as xy, t4 as yx ) , row( skip(0,3) ) , heading , row( t6 as xy ) ) ).
Layout descriptions specify how tables are laid out
on the sheets. You need one layout description
to make each sheet. The
items in each layout description define a 2-d grid
of "boxes". (I call them
"boxes" to avoid confusion with spreadsheet cells:
a box may map on to only one cell, but often maps on to
Each box can contain one of: a table; a table
with a shape specifier; a table copy;
a table copy with a shape specifier; a label; a 'skip'.
Tables are specified by giving their name. By default,
Excelsior arranges one-dimensional tables to run
vertically downwards. You can make them run horizontally
by using the shape specifier
as x. The default
vertical arrangement is equivalent to using the shape
Two-dimensional tables are arranged, by default,
with their first dimension horizontal and their
second. This is equivalent to the shape
as xy. You can make the
first dimension run vertically and the second
horizontally by using the shape specifier
Zero-dimensional tables always occupy one cell,
and don't need a shape specifier. However, they
can be given one, namely
as null. This
can be useful when specifying dropdown menus.
Labels must be written in single or double quotes, in the same way as string constants. They always occupy one cell.
skip indicates a blank cell.
You can reserve more than one blank cell in a box
by using arguments to indicate the number of horizontal
and vertical blank cells. Thus,
reserves a blank space two cells across by three down.
When writing spreadsheets, we often want to accompany tables by headings along the top or left-hand side, to label the cells within. For example, around a table showing inflation rate by year and country, we might want to run years along the top, and country names down the left.
You can create such headings in Excelsior by writing them into extra tables, but that becomes tedious when the same headings need to be repeated in many places in the spreadsheet. I have therefore implemented an abbreviation for copying such headings.
You specify these by writing
copy(Table) as ShapeSpecifier.
This gives a shorthand way of copying
tables to be used as headings. The copy gets given
the result type and style
the result type of the table being copied is
date, in which case it gets the
result type and style
dateheading. (This is necessary
because in Excel, dates are just integers, and one
has to set the cell format specially to make them
appear as dates.)
Here's an example:
type year = 1:5. table years : year -> date. years[ t ] = date( 2000+t-1, 1, 1 ). table days_in_year : year -> general. days_in_year[ t ] = date( 2000+t, 1, 1 ) - date( 2000+t-1, 1, 1 ). table is_leap : year -> boolean. is_leap[ t ] = days_in_year[ t ] = 366. layout( 'Sheet 1' , rows( row( 'Sheet 1' ) , row( skip(1,2) ) , row( 'My years' ) , row( years as y ) , row( skip(1,2) ) , row( 'Year', 'Days in year' ) , row( copy(years) as y, days_in_year as y ) , row( skip(1,2) ) , row( 'Year', 'Is a leap year' ) , row( copy(years) as y, is_leap as y ) ) ).
To work out where each table, 'skip', or label goes on its sheet, Excelsior places it at the top left-hand corner of its box in the grid. It already knows how much space labels and 'skip's occupy; it works out the space needed for the tables from their dimensions, as mapped onto the sheet by default or by their shape specifier. It then pads each box below with spaces as needed to make all boxes in its column the same width, and to its right with spaces as needed to make all boxes in its row the same height.
When developing new spreadsheets, I don't always want to bother typing all the table names into a layout; and yet I do want to be able to see quickly where each table is in the spreadsheet.
To make this easier, I allow
the special symbol
heading to be
be written instead of a
It creates a row with a label naming
each table in the row beneath.
Each label is
the same as the name of its table, but with
underlines replaced by spaces. If
you make your table names meaningful phrases,
composed of words separated
by underlines, the
headings will therefore be easy for you to understand.
(Note however that you may want to replace these
automatically-generated headings by more user-friendly
once you have finished developing the spreadsheet and
are ready to release it.)
Here is an example:
type range = 1:20. table doubles: range -> general. table squares: range -> general. doubles[ n ] = 2 * n. squares[ n ] = n * n. layout( 'Sheet 1' , rows( heading , row( doubles, squares ) ) ).
Excelsior allows dropdowns to be placed in cells. To do so,
it uses an extra argument to
as, putting it after
the shape specifier. This argument must be either a list of
numbers or strings, or the name of a table from which the
dropdown options will be taken. So that the
can take three arguments rather than two, it has to be
written as a function, with the arguments in brackets.
Here is an example. This creates three sets of dropdowns. One set contains options specified as a list; one contains options taken from another table on the same sheet; one contains options taken from another table on a different sheet:
type one_to_three = 1:3. table has_dropdown_1 : one_to_three -> general. table has_dropdown_2 : one_to_three -> general. table has_dropdown_3 : one_to_three -> general. type one_to_four = 1:4. table dropdown_2_options : one_to_four -> text. dropdown_2_options = "e". dropdown_2_options = "f". dropdown_2_options = "g". dropdown_2_options = "h". table dropdown_3_options : one_to_four -> text. dropdown_3_options = "i". dropdown_3_options = "j". dropdown_3_options = "k". dropdown_3_options = "l". layout( 'Sheet 1' , rows( heading , [ as( has_dropdown_1, y, ['a','b','c','d'] ) , as( has_dropdown_2, y, dropdown_2_options ) , as( has_dropdown_3, y, dropdown_3_options ) ] , heading , [ dropdown_2_options ] ) ). layout( 'Sheet 2' , rows( heading , [ dropdown_3_options ] ) ).
An even briefer shorthand is given by
autolayout compiler option.
If you specify this, Excelsior generates its own
layout, ignoring any
layout descriptions in the program.
Equations for non-macro tables get translated into Excel formulae. The right-hand side of an equation consists of constants, Excel function calls, and subscripted tables: i.e. it is like an Excel formula, but with tables instead of cell references.
Constants can be numeric, string, or Boolean.
Numeric constants have the same syntax as in
Excel. String constants are written between single
or double quotes. The Booleans are
Function calls are written as in Excel.
Tables, when used on the right-hand side of an equation, denote either single cells or ranges of cells. Single-cell references are formed by subscripting: following the table name by a list of comma-separated subscripts in square brackets. The number of subscripts must be the same as the table's dimensionality.
Range references are also formed by following
the table name by a square-bracketed list
of items. However,
in this case, one or more of the items
can denote a range of cells. This is done by
using either the word
denotes that entire dimension, or a pair of
expressions separated by a colon, giving the
lower and upper bounds of the range. Here is an
type range = 1:5. table t1 : range -> general. t1[ x ] = x. table t2 : range -> general. t2 = sum( t1[all] ). t2 = sum( t1[1:5] ). t2 = sum( t1[1:4] ). t2 = sum( t1[2:5] ). t2 = sum( t1[3:5] ). table t3 : range -> text. t3 = 'Sum of all elements'. t3 = 'Sum of all elements'. t3 = 'Sum of first four elements'. t3 = 'Sum of last four elements'. t3 = 'Sum of three elements'. layout( 'Sheet 1' , rows( heading , row( t1, t2, t3 ) ) ).
Equations for macro tables have exactly the same kind of right-hand side as those for non-macro tables. However, Excelsior does not allocate cells to macro tables. Instead, when it encounters a reference to a macro table, it expands it in place, replacing it by its definition.
Table declarations specify the shape of a table: that is, its size and dimensionality. Each dimension's bounds are specified by a name declared in a type declaration.
It can be useful to think of tables as analogous to functions. For this reason, I have made the syntax of a table declaration resemble the notation uses in maths for specifying functions: a list of types for the arguments (the function's domain) followed by the type of its result (its codomain).
The result type
of a table can be one of
text. Excelsior will format
the cells appropriately, using a style
of the same name as the type, defined in the file
named by the
I supply a template file,
template.xlt with Excelsior. In it,
all styles except
dateheading have the
same non-white background colour. This makes it
easy, when checking a generated spreadsheet,
to see immediately where the tables are.
You can edit the template file to change the
styles' effect, but it's probably helpful to
keep all non-heading styles a different colour
from the background.
heading style is in white,
and formats its cells bold and wrapped. Thus it is
suitable for headings. The
item in layout, and
labels in layout, get
given this style.
dateheading style is similar,
but formats its cells as dates.
Sheet1 in template.xlt shows these styles. Column A gives the style names; column B shows cells styled accordingly.
Type declarations give a name to a pair consisting of a lower and upper bound, which as just explained, describe one dimension of a table.
When thinking of tables as functions, you can think of type declarations as describing what type of thing each argument is. This is useful when documenting programs.
Constant declarations allow you to name a constant. You can then use this as one bound of a type declaration.
The discipline of "literate programming" was invented in an attempt to make programs easier to document, and to read when documented. The idea is that instead of marking commentary by comment symbols, everything is taken to be commentary unless specially marked as code. Writing a program should then become like writing, say, a piece of mathematics, where one concentrates on explaining how the program works, inserting code as necessary to this explanation. A number of programming languages have been implemented to follow this idea, and are usually given a name starting with the word "Literate".
Literate Excelsior is a preprocessor that runs over the files with extension .lexc in a directory, assuming them to contain "Literate Excelsior" text and converting them to ordinary Excelsior .exc files and also to a set of HTML documentation files. You invoke it with the literate option.
In a Literate Excelsior file, all text is assumed to be commentary unless indented by at least two spaces. The documentation generator will convert commentary to HTML, inserting paragraph markers at line breaks.
Lines that start with minus signs are treated as headings and converted to an HTML heading: its level depends on the number of minus signs: one for a level-1 heading, two for a level-2, and so on.
Text that is indented by two or more spaces is treated as code. The documentation generator puts it inside a <pre> element, causing it to be rendered verbatim. I supply a stylesheet with Literate Excelsior: this causes the code to be displayed on a pale but not white background, so that it stands out from the surrounding commentary.
Text that is indented by two or more spaces also gets copied from its .lexc file to a corresponding .exc file in the same directory. This can then be compiled.
I have given you a zip file containing:
Install as follows.
excelsiorExcelsior should give you a summary of its command-line syntax.
Once Excelsior is installed as above, you can
run it from the command
line by typing
followed by any options and the
name of the
input file to compile.
An option is indicated by a minus sign immediately followed by the option name. If the option has an argument, this should come after the name, and can be separated from it by spaces. All options must come before the input file.
The options are:
-help. Prints a summary
of the command syntax.
the version number and date the software was made. Please
tell me this when reporting problems.
If this is true, Excelsior will ignore any
layout statements in the program, and generate its
-literate, followed by
a directory name. If this is specified, runs Literate
Excelsior over all .lexc files in the directory, generating
an HTML listing and a compilable .exc file from each. An index
to the HTML files will be created in index.html.
by the name of a .xlt file. As explained above, Excelsior
treats each table's result type as a style, and emits
code to format its cells with that style.
When Excelsior creates a spreadsheet, it has to
open it as a workbook with a template that defines those
styles, otherwise Excel will complain that they are
undefined. Therefore, every time you compile
an Excelsior program, you must use this option to tell Excelsior
where it can find such a template. I've provided
a file template.xlt with my style definitions; you can
edit it as you wish if you want to change how they look,
as long as you retain the same style names.
These commands will cause Excelsior to display a summary of the command syntax:
excelsior excelsior -? excelsior -help
These commands display the version number and date:
excelsior -v excelsior -version
These commands will compile source_tests\test23.exc. The resulting spreadsheet will use styles from c:\kb7\mm6\template.xlt:
excelsior -tc:\kb7\mm6\template.xlt source_tests\test23.exc excelsior -t c:\kb7\mm6\template.xlt source_tests\test23.exc excelsior -template c:\kb7\mm6\template.xlt source_tests\test23.exc
These commands will compile source_tests\test23.exc as above. However, because of the autolayout option, Excelsior will ignore the layout descriptions in the program, and generate its own layout:
excelsior -tc:\kb7\mm6\template.xlt -a true source_tests\test23.exc excelsior -autolayouttrue -t c:\kb7\mm6\template.xlt source_tests\test23.exc excelsior -template c:\kb7\mm6\template.xlt -autolayout true source_tests\test23.exc
These commands will compile source_tests\test23.exc and use c:\kb7\mm6\template.xlt as above. But first, because of the "literate" option, they will run Literate Excelsior over all .lexc files in the directory, generating HTML and .exc files from each:
excelsior -l source_tests\ -tc:\kb7\mm6\template.xlt source_tests\test23.exc excelsior -t c:\kb7\mm6\template.xlt -literate source_tests\ source_tests\test23.exc
Writing .xls files is tricky, because of their complicated binary format. I have side-stepped this for the moment by having Excelsior output the compiled spreadsheet in a simple text format which can be converted to a spreadsheet by a small VBA program, provided with Excelsior. This program is in interface.xls.
When Excelsior thinks it has compiled a program without detecting any errors, it writes the output to a text file in the EXCELSIOR_TEMP directory. It then runs Excel from the EXCEL_HOME directory, giving it the name interface.xls as argument. interface.xls contains a VBA routine which will automatically run when it starts, and which will read this text file. The file contains simple commands which interface.xls interprets in order to put formulae, styles, and other information into the new workbook it creates.
It will give this workbook a name guaranteed to be unique, and you will see the workbook appear in a new Excel instance. If you want to keep the workbook once created, you can use Excel's 'Save As' command to do so.
Copyright © Jocelyn Ireson-Paine and Spreadsheet Repository, 2007-2009.