Using Excelsior

Contents

The Excelsior language

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.

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

Layout descriptions specify how tables are laid out on the sheets. You need one layout description to make each sheet. The rows and row 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 more.) 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 and shape specifiers

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 specifier as y.

Two-dimensional tables are arranged, by default, with their first dimension horizontal and their second. This is equivalent to the shape specifier as xy. You can make the first dimension run vertically and the second horizontally by using the shape specifier as yx.

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

Labels must be written in single or double quotes, in the same way as string constants. They always occupy one cell.

Blank cells

The word 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, skip(2,3) reserves a blank space two cells across by three down.

Copying tables to use as headings

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) or 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 heading, unless 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 )
            )
      ).

How Excelsior calculates table positions

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.

Generating headings from table names

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 row(…). 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 )
            )
      ).

Dropdown menus

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 as 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[1] = "e".
dropdown_2_options[2] = "f".
dropdown_2_options[3] = "g".
dropdown_2_options[4] = "h".

table dropdown_3_options : one_to_four -> text.
dropdown_3_options[1] = "i".
dropdown_3_options[2] = "j".
dropdown_3_options[3] = "k".
dropdown_3_options[4] = "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 ]
            )
      ).

The autolayout option

An even briefer shorthand is given by the autolayout compiler option. If you specify this, Excelsior generates its own layout, ignoring any layout descriptions in the program.

Equations

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 true and false.

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 all, which 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 example:

type range = 1:5.

table t1 : range -> general.
t1[ x ] = x.

table t2 : range -> general.
t2[1] = sum( t1[all] ).
t2[2] = sum( t1[1:5] ).
t2[3] = sum( t1[1:4] ).
t2[4] = sum( t1[2:5] ).
t2[5] = sum( t1[3:5] ).

table t3 : range -> text.
t3[1] = 'Sum of all elements'.
t3[2] = 'Sum of all elements'.
t3[3] = 'Sum of first four elements'.
t3[4] = 'Sum of last four elements'.
t3[5] = '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

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).

Table result types and the template file

The result type of a table can be one of boolean, currency, date, dateheading, general, heading, percent, and text. Excelsior will format the cells appropriately, using a style of the same name as the type, defined in the file named by the template option.

I supply a template file, template.xlt with Excelsior. In it, all styles except heading and 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.

The heading style is in white, and formats its cells bold and wrapped. Thus it is suitable for headings. The heading item in layout, and labels in layout, get given this style.

The 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

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

Constant declarations allow you to name a constant. You can then use this as one bound of a type declaration.

Literate Excelsior

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.

Installing Excelsior

I have given you a zip file containing:

Install as follows.

Running Excelsior

Once Excelsior is installed as above, you can run it from the command line by typing excelsior 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:

Examples

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

A note about how Excelsior generates the spreadsheets

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.