This page links to demonstrations for my paper Module Expressions for Modularising Spreadsheets and Sharing Code between Them submitted to the Calco-Tools 2011 workshop on tools based on algebraic and coalgebraic principles. Reviewers and delegates, and anyone else interested, can download them from here. The paper is about modularising spreadsheets using a module system based on ideas from OBJ and other algebraic-specification languages, and there are two sets of demonstrations. The first set demonstrates my Excelsior spreadsheet-description language, and the use of modules. The second set demonstrates how to insert modules into already-written spreadsheets. The point of both is to show that spreadsheets can be modularised in a way that has good mathematical foundations, and that doing so makes code-reuse easier. As well as examples of modularisation, the demonstrations include the examples from my paper with which I introduce Excelsior. Because there are several of these, I've written a section for each, and linked to it from the contents just below. There is also a section on how to install Excelsior. If you have problems, please mail me at popx@j-paine.org .
Excelsior is a compiler that reads text files and generates Excel spreadsheets. A program can be in one piece or split between several files, and can import modules.
The current version of Excelsior runs only on Windows. If anyone is interested, I can make a Unix version, but because Unix doesn't have Excel, it won't be able to launch Excel with the generated spreadsheet in as the Windows version does. To demonstrate Excelsior, you need a Windows system, and a copy of Excel. I have only tried it with Windows XP and Excel 2003, but I expect it to work with other modern Windows systems, including 64-bit, and with Excel 2007.
To install Excelsior, please visit my download page. The page links to a zip file and some installation instructions. Read the notes at the end of this section, then download the zip file and follow the instructions. These should be straightforward if you know Windows and the DOS command line. You'll be asked to create a directory for Excelsior to live in, unzip into it, set some environment variables, run some tests, and then put Excelsior on your PATH if you want to run it from a different directory. There are also installation instructions in my feature Gliders, Hasslers, and the Toadsucker: Writing and Explaining a Structured Excel Life Game. They're in the section called Compiling Life, and explain in more detail how to set environment variables.
The installation instructions ask you to create a directory for Excelsior
to live in.
I suggest making this a top-level directory called excelsior,
e.g. c:\excelsior\ . In the rest of this page, I'll
assume that's what you've called it.
Excelsior tells you when it starts compiling a program, and again when it has finished. The messages from a successful compilation will look like this, in the DOS window:
Compiling micawber1.exc. Compiled micawber1.exc.If Excelsior detects errors, it will report them in the same window.
If Excelsior does manage to compile your program, it will generate a temporary file holding Excel formulae, then launch Excel and make it run a Visual Basic for Applications (VBA) initialiser program that copies these formulae into the spreadsheet being generated. Because Excel is running VBA, it may ask you whether you want macros enabled. You do, otherwise the VBA program can't run.
Once Excel is launched, it will briefly display the spreadsheet
that I holds the initialiser program. This has one sheet,
showing a table of the cell formats for Excelsior array-element
types. These will then disappear and be replaced by your
spreadsheet. The initialiser will give this a temporary name,
something like wb__2011_3_28_7_25_38_218 . If you
want to keep the spreadsheet, go to Excel's File menu
and use the Save As option to save it under a name of your choice.
Excelsior doesn't delete or overwrite the Excel windows holding generated
spreadsheets, so if you do lots of compilations and don't delete them
either,
you'll end up with lots of Excel windows.
Excel scans for viruses when launching the initialiser. While doing so, it displays "Running virus scan..." in the bottom-left of its window. Be prepared for some delay while this is happening.
You run Excelsior from the DOS command line, passing it the input file or files, and various control options. These are described in the installation instructions. I also give the command lines for each example in my paper, below. The installation comes with a few examples, plus a number of test programs. You can find more examples in my paper; in the Life-game feature cited above; in my Dr. Dobbs blog posting How to Document a Spreadsheet: an Exercise with Cash-Flow and Loans; in my EuSpRIG 2008 paper Spreadsheet Components For All; and in the presentation accompanying it, Less Excel, More Components.
Here are links to the examples in my paper. These link to
files with the extension .exc, which should
display as plain text in your browser. You can capture this
by selecting the text with the mouse, then pasting it into a text
editor, or by saving the page. The
entire collection of examples is available in this
zip file.
micawber1.exc
Simple one-row spreadsheet calculating Micawber's net income. Compile it with this command line:
excelsior -t c:\excelsior\template.xlt micawber1.exc
micawber2.exc
Multi-row spreadsheet calculating Micawber's net income for each of nine years. Compile it with this command line:
excelsior -t c:\excelsior\template.xlt micawber2.exc
micawber3.exc
Like micawber2, but with years running horizontally
rather than vertically. Demonstrates how easy it is to change
layout.
Compile it with this command line:
excelsior -t c:\excelsior\template.xlt micawber3.exc
test_split.excsplit.exc
Demonstration of modules. The first file imports the module in the second file twice, to split the strings in two different tables. The tables, their base type or size, and the delimiter to split at, are parameters to the module. Compile it with this command line:
excelsior -t c:\excelsior\template.xlt test_split.exc
test_split2.excsplit2.exc
Demonstrates a bigger version of the module, omitted from the paper because of lack of room. This splits the strings into "before" and "after" segments, rather than just "before". Compile it with this command line:
excelsior -t c:\excelsior\template.xlt test_split2.exc
micawber2_data.exc
Test data which can be compiled together with
micawber2
by using the nearest construct Excelsior has so far to a
module-sum operator. This demonstrates how you can write
test data separately from the code being tested, then combine
them without having to edit the latter.
Compile the two files with this command line:
excelsior -t c:\excelsior\template.xlt micawber2.exc micawber2_data.exc
Consider an existing spreadsheet with a "hole" we want to put a module
into.
As you can see from the examples above, the module-inclusion statement
can parameterise modules; and
a module's parameters affect the shape, size, and position of its arrays.
For example, test_split
creates two instances of module split. In one instance,
the tables have two cells each; in the other, three. In one
instance, they hang downwards from row 2; in the other,
from row 7.
So imagine writing a "stub" program that includes the
module but does nothing else, then compiling it to a spreadsheet.
This will contain only the module, and by choosing
suitable parameters, we can shape it to fit any hole in the
existing spreadsheet. The examples below demonstrate this:
split_spacer.exc is such a stub, and
computer_scientists.xls
is a spreadsheet with a hole. Other
examples can be found in the Try for yourself
section of my EuSpRIG 2008
presentation Less Excel,
More Components; and with screen shots, and automated
for Google's online spreadsheet as well as Excel, in my
feature Spreadsheet
Components, Google Spreadsheets, and Code Reuse.
split_spacer.exccomputer_scientists.xls
First, compile split_spacer with this command line:
excelsior -t c:\excelsior\template.xlt split_spacer.exc
This makes a spreadsheet that holds a copy of the module
shaped so that its inputs are in D3:D7, and its outputs
in F3:F7. Next, open computer_scientists.xls.
This is a spreadsheet with the names of famous computer
scientists in D3:D7. Keep a copy, or be prepared
to download it again, because you'll need it unchanged
for the next demo.
Now, go to the
spreadsheet you compiled
from split_spacer, and select and copy
cells E3:F7, i.e. the non-input-cell portion of the module.
(If you don't know how to select and copy: drag the mouse
over the cells, then press Control-C.) Go back to
computer_scientists and paste these cells
into it at the same location. (Drag the mouse
over the cells, then press Control-V.) This pastes the
formulae that do the splitting into
computer_scientists,
and you should see the computer scientists' Christian names appear
in
F3:F7. E3:E7 hold intermediate results, the positions of the
space.
This demonstrates inserting a compiled module into an existing
spreadsheet, though you had to insert it manually.
split.etf
This is an intermediate file of Excel formulae. It's the
file that Excelsior emits and that the VBA initialiser
program then reads to generate the spreadsheet. The file
here is one I compiled from split_spacer. I'll
now demonstrate how to insert it automatically. Go to
this
page,
called How
to copy your component into Excel. It's one of the
pages on my Spreadsheet Parts Repository site. Download
the VBA program that it refers to near the top of the page
(actually, this is a spreadsheet with VBA in it),
and save it in c:\excelsior\spreadlet_copier.xls .
Also make
sure that you've downloaded split.etf from the
link above. Open spreadlet_copier.xls. You should
see a sheet with a grey "Go" button on it. Press the button.
You'll get a file-selection dialogue box titled "Please
select a target spreadsheet". Select
computer_scientists.xls .
You'll then get another file-selection dialogue box titled "Please
select a component ETF file". Select split.etf.
The VBA will then copy the formulae from it into
computer_scientists. As with the previous demo,
Christian names should appear.
This shows how split.etf was generated.
First, compile split_spacer with this command line:
excelsior -t c:\excelsior\template.xlt -no_excel true split_spacer.exc
The no_excel option tells Excelsior not to launch
Excel. Normally, Excelsior would delete the ETF file
after doing so. But with no_excel, it leaves it. You
should find it in the temporary-files directory you set
the EXCELSIOR_TEMP environment variable to when
installing
Excelsior, under the name excelsior.etf. Open it and
split.etf in a text editor. The formulae should be
the same.
Jocelyn Ireson-Paine