[ Jocelyn Ireson-Paine's Home Page | Publications | More about Excelsior | The paper accompanying this talk, submitted to EuSpRIG 2008 ]
Some things are hard to program in Excel, but can be done by experts. Like this science-fiction generating spreadsheet. I'm not sure whether this is useful, but I mention it because it demonstrates the power of the programming techniques I'm showing here.
Some things are hard to program in Excel, but can be done by experts, and and are useful. An example is searching a range for all the cells that start with a given string, and copying them to another range, without gaps. Like searching column A in this spreadsheet for strings beginning with "Flat", "House" and "Bungalow", and copying the results to columns C, D and E, giving this spreadsheet.
The files you need to try this yourself are at the end of this demo.
I had to do this last year in a spreadsheet that modelled urban regeneration projects. The user could input lists of buildings, and we needed to get all buildings of a given kind for use as options in dropdown menus. It was important not to have gaps in the resulting range, because then there'd be blank options in the menu.
So wouldn't it be nice if an expert could build such a component to do the search for you? All you need to do is say where in your spreadsheet you want to put it.
That's the point of this talk: a service that lets you glue pre-built components into your spreadsheets in the same way you would charts.
I have to give the demo in pieces, because I'm giving the talk at Greenwich University, but the first stage runs remotely on my Web server, and I don't know whether they'll allow me Internet access. So let me explain. My idea is to have, on the Web, a "repository of spreadsheet parts", that is, a kind of software library. These parts or components will do calculations that are possible in Excel, but hard for the average practitioner to program.
When you want to use one of the components, you'll go to a form like this.
This names a particular component. In this case, one to search an input range and copy matching cells to an output range. You fill in the input range as you would when inserting a chart, and also the output range. I've done that already. The component may need some workspace; if so, you tell it where that can safely go. Then you press Submit, and the Web server sends the component as a text file containing formulae. (That's not shown on this form, because it works with Google's online Spreadsheets, which is why the bottom row asks for some Google Docs info.)
Once the server has given you the link to the downloadable file, you save it, and then run a Visual Basic macro which copies its formulae into your spreadsheet. Which is what I did a few paragraphs earlier when I went from this spreadsheet to this one. And that's all.
I want to emphasise that the same component can be adapted to different sizes, shapes and places in a sheet. That actually happened above, because I had downloaded three versions of the search component from my site: one to search for "Flat", one to search for "House", and one to search for "Bungalow". I've downloaded three more, that search cells A3:A9, and arrange results horizontally, not vertically. After I inserted these, this is the contents of my spreadsheet.
You can try this yourself here
And of course, we can have diverse components. Even funny ones, which I have just now added to the spreadsheet.
You can try this yourself here.
What are the benefits? The same as for prefabrication in any branch of engineering. It saves time, reduces errors, and enables parts to be built by the experts who best know how to do so.
EuSpRIG devotees will know that I've developed a program called Excelsior, which generates spreadsheets from descriptions written in a programming language that I also call Excelsior. I've got this running on my Web server as well as my laptop, and it's what the components are written in. Here's an example, the "joke" component.
This defines a table called "joke" with four elements. You can that the name on the "table" line is "joke", and that the name "table_size" referred to there is defined above as being one to four. Each element of the table contains one string: for example, the first contains "Did you hear about the". The table is to be placed in the second row: this is indicated by the "layout" statement, which has two arguments to the "rows" construct. The first of these lines is a heading, the name of the table. Both rows go in Sheet1.
If I tell Excelsior to compile that file, like this, it generates this spreadsheet. A table with four elements in Sheet1 with a heading in the top row.
It's easy to change layout. Here's the same Excelsior program, but with a different layout. Now, the table's contents is still the same, but the cells must be laid out in the x direction, as you can see from the "x" in the layout statement, rather than the y direction as in the first file's layout statement. If I tell Excelsior to compile this new file, like this, it generates this new spreadsheet. Notice the difference.
Now you can see how my Web server reshapes the components to fit the ranges you specify in the input form. In essence, it turns them into a layout description. It glues this onto the rest of the Excelsior program for the component, and then runs Excelsior. Which generates the formulae to be loaded into your spreadsheet.
Where do components come from? They can be written directly in Excelsior, as with the "joke" one.
Or they can be reverse engineered from spreadsheets. I want to show the reverse engineering off, because I'm pleased with it. I'll start with the joke spreadsheet, but now I'm going to reverse engineer it.
Excelsior is written in a language called Prolog, and here I have started a Prolog session. Now I load my reverse-engineering tools into it. I've programmed these with the names of some spreadsheets, so that I can demo reverse-engineering without a lot of typing. First, I ask Excelsior to load the "joke" spreadsheet and list it. The listing shows the cells used and their contents.
I've written some "substitutions". These tell Excelsior to map certain ranges onto tables, like this. Now I tell Excelsior to list the spreadsheet again, but substituting these names for the cells.
This comes into its own when the spreadsheets contain runs of cells created by filling down or across. I'll demonstrate with another spreadsheet. This has three similar rows. The output cell in each is the square of the working cell, which is double the input cell. First, I'll tell the reverse-engineering tool to list it as a spreadsheet.
Again, I've written some substitutions, which tell Excelsior to treat the ranges as tables. These are they. So, I'll list it again with these.
Here's the really good bit. I've written a "run detector", which finds and summarises runs of similar cells. So let's run that. And this is the result.
Please tell me, Jocelyn Ireson-Paine, at popx@j-paine.org.
This section gives you all you need to try components yourself. The list below links to the components I've demonstrated. I've put these into text files having the extension ".etf": I'll call these "ETF files" hereafter. These contain the formulae to be inserted. They may also list new sheets that must be added to hold intermediate results.
You can also get ETF files generated dynamically, showing how components would be downloaded from my library in real life. To do this, go to this Spreadsheet Parts Repository component form and follow the instructions.
You also need the program that copies the ETF files into your spreadsheet. You can download this from the Spreadsheet Parts Repository page How to copy your component into Excel. To run it, follow the page's instructions.
These are the links:
July 2008, updated 21st January 2009
