Make your own free website on Tripod.com

Esheet 0.8.3

A Spreadsheet Module for Emacs
This text, and esheet itself, are distributed under the terms of the General Public License.
This text written by Daniel Speyer

Installation
Gunzip and untar the distribution into a directory where it will be run.  I recommend ~/.xemacs/lisp/ (you may have to create it) but there are other such directories.  Everything is already inside a directory called `esheet', and that goes inside wherever you put it.  To be concrete, type the following (assuming you downloaded esheet into your home directory.

~% mkdir .xemacs
~% mkdir .xemacs/lisp
~% mv esheet0.8.3.tar.gz .xemacs/lisp/
~% cd .xemacs/lisp
~/.xemacs/lisp% gunzip esheet0.8.3.tar.gz
~/.xemacs/lisp% tar xf esheet0.8.3.tar

Once you've done this, esheet will automatically load whenever you C-x C-f a file ending in .esh  Alternatively, you can invoke esheet manually with M-x esheet-mode

Installing in GNU Emacs
So far as I know, GNU Emacs doesn't have an equivalent to the ~/.xemacs/lisp directory (If it does please tell me). Therefore, I recommend (though there may be better ways) untaring the files in your home directory (they'll create a directory esheet) (alternatively, if you want to be able to use Esheet with both emacses, into ~/.xemacs/lisp (the adjustments to the rest of the instructions shoulkd be obvious)). Then add the following (or rough equivalent) to your .emacs file:

(if (equal (substring (version) 0 3) "GNU") ;;don't execute this in XEmacs!
    (progn
      (setq load-path (cons "~/esheet" load-path)) ;;adjust directory as needed
      (load "auto-autoloads")))

The Basics
The Esheet screen is divided into a main screen and an edit line. On the main screen, all formulas are shown as results and all strings are truncated to five characters.

Annotated Screenshot
Many of the parts of the esheet screen have names by which they are referred to throughout this document. If these names are unintuitive, severe confusion can result. To prevent this, I offer the following screenshot. (Obviously, the titlebar and border of the window will vary according to your window manager.)


(This is a png image, if you can't see it, the problem is in your browser)



Moving
The current cell is displayed on a blue background. To move from cell to cell, use the arrow keys. Do not use home/end/pgup etc. The full content of the current cell (if a string) or the formula (if a formula) To replace the contents of the cell, simply type in it. The cursor will automatically jump to the edit line. If you wish to edit non-destructively, hit enter or tab.
Do not use the mouse! Hopefully mouse routines will arrive eventually. The address of the cell you are in will be displayed in the mini-buffer.

The Edit Line
All editing occurs in this three-line tall buffer at the top of the frame. To leave this buffer, and set the cell's value, hit either enter or tab. Enter goes to the cell below; tab goes to the right.

The Delete Key
The delete and backspace keys do the same thing, but C-h is unbound (this matters over some network connections. Either key will clear the content of whatever cell it is on, or, if the esheet-region is active, clears the content of all cells in the region.

The Toolbar
Esheet moves the toolbar onto the left (for aesthetic reasons) and changes most of its content (because many normal XEmacs functions (e.g. spell-check) make no sense in the context of Esheet).  The buttons do what they say they do, so a list is not needed here.  The last button brings the regular XEmacs toolbar back, with one button inserted.  That button puts the esheet toolbar back in place.  You can also restore the esheet toolbar with C-e.

The Region
Quite often in spreadsheets it is useful to copy data (or usually formulas) from one place to another. For this, esheet provides the region.

Creating
To select the region, simply hold down the shift keys and use the arrows (note that the cell the cursor is on is part of the region. If you regularly move the cursor while the region is active, it will deactivate the region.

Cut/Copy/Paste
Cut, copy, and paste are the fundamental functions that act on the region. They do essentially the same thing they do in any program: both cut and copy put the region's data into the clipboard (cut deletes the old data, copy does not) and paste puts the clipboard data back into the document.  There is also a function esheet-copy-values which puts the values of the cells in the region into the clipboard (instead of copying formulas).
Esheet does a few things with cut/copy/paste that most programs don't. Cut and copy, if the region is inactive, act on the 1 cell by 1 cell block consisting of the current cell. Paste, if the region is active, will fill the region and no more, by cropping or repeating data as necessary.
To activate these functions, use either M-x esheet-cut/M-x esheet-copy/M-x esheet-copy-values/M-x esheet-paste or select these functions from the esheet toolbar

Formulas
Possibly the most important feature of a spreadsheet is performing mathematical computation. Here, Esheet provides two distinct options.  You can use both on the same sheet, but cannot mix them within a cell.

Esheet Syntax
Esheet provides its own parsing routines, which should allow you to write formulas as complex as you want.  It's more compact and much more like other spreadsheets' formula parsers that elisp is.  All esheet formulas are prefixed with a plus sign.

The basic operators +-/*^% may be put between things to act, for example, 2+2 evaluates to 4.  Parentheses change order of evaluation.  Function names go before what they act on, and input must be in parenthesis.  Commas build lists.  Functions and variables have the same bindings they do in lisp.  All this is the same as standard mathematical notation, with the following exceptions:

Also available are the relational operators == <= >= < and >.  These are mainly meant for use with ?: branching.  This works just like in c.  (For example +3>3?1:2 yields 2; +3>=3?1:2 yields 1)  Note: the relational operators use their own true and false, not lisp's t and nil.

Elisp Syntax
Elisp provides more power than almost anything else, but with a somewhat more confusing syntax. All elisp formulas must begin with an equals sign.

After the equals sign, Esheet uses the elisp parser for it's evaluation. If you already are familiar with this format, you can probably skip this section.
The basic concept of elisp syntax is prefix operators (i.e. put the operator first). For example, =(+ 2 2) returns 4. Build more complex formulas exactly as you would expect: =(+ (* 4 10) (- 3 5)) returns 38.

You should probably only use elisp syntax if you are already comfortable with it and want elisp functions like let and while that are unavailable for esheet syntax.

Cell and Range
The cell function takes 2 arguments, which are the co-ordinates of a cell. It then returns the value of the cell referred to. For example, (cell 0 0) returns the value of the top left cell of the sheet. (cell 5 0) is five to the right, but still on the top row.
Range takes 4 arguments, and returns the values of all cells in the rectangle they define.
32 52 54 24 16
32 52 54 24 16
32 52 54 24 16
32 52 54 24 16
For example, (range 1 0 2 2) returns the values of the red cells. Ranges are a special data structure that holds the values of various cells, you can manipulate them with functions designed to accept them.

x and y
The special variables x and y refer to the address of the cell whose formula they appear in. If you want the value of a cell to be the sum of it's x and y co-ordinates, you can give it a formula of =(+ x y) (or, equivalently +x+y).
The main use of x and y, however, is with the cell and range commands. Many spreadsheets use relative or 'smart' addressing, which often increases convenience of copying formulas, but occasionally becomes very annoying. Esheet uses absolute addressing, but you can do effective relative addressing manually using x and y. For example, if you want a cell to have twice the value immediately to its left, use =(* (cell (- x 1) y) 2) or +cell(x-1,y)*2.
The advantage of this is power. For example, suppose you have data on heavy metal contamination in columns 0-8 and data on organ sizes in columns. Now suppose you want to take correlation coefficients of all metals with all organs in a neat table. (Assume you have a complete statistics library, which Esheet 0.8.2 does not (and therefore the correl command is unavailable)) You could type =(correl (range x 0 x 20) (range y 0 y 20)) or +correl(range(x,0,x,20),range(y,0,y,20)), and copy-paste this into all blocks of the table.
Operators
Esheet has redefined the +-*/^% operators (^ does exponentiation; % does mod). The basic operators +-/*% all return the same values they normally do, when called with data the normal versions can handle. ^ is unbound in ordinary elisp, so this isn't an issue there. The value of the new versions is that they handle data-structures, including lists and ranges. (note to those with experience in lisp: they can handle anything build out of conses) Therefore, the average difference between two datasets could be =(mean (- (range 0 0 0 20) (range 1 0 1 20))) or +mean(range(0,0,0,20)-range(1,0,1,20)). This can be very convenient when building your own statistics functions.
Statistics Library
Esheet's statistics library is incomplete, only a very basic set of operations is available. Hopefully the next version will have more. The existing functions, all of which take a single range, are listed below.
sum sum the elements
product product of all elements
n number of elements
mean arithmetic mean
geomean geometric mean
stdev Standard deviation
If you wish, you can also use these on data you directly provide. The statistical functions can take many different data structures (acting only on actual data).
Save and Load
Loading is simple: any XEmacs with Esheet correctly installed will handle loading correctly with the normal load commands.

Saving is a little more complicated.  The normal XEmacs save feature will lose the critical data that Esheet keeps in text properties.  In esheet mode, C-x C-s and C-x C-w are correctly rebound.  Also, the toolbar functions are rebound.  However, other methods of saving will not work.

In particular, the 'buffer modified, save?' warning you get on exiting will not save properly.  This is important because esheet's save doesn't mark the buffer as unmodified.

Also, auto-saving doesn't work with esheet.

Sort
Sorting data is often useful in a spreadsheet. To sort data in esheet, first select it as the region, and then hit either M-x sort-region or the sort button on the esheet toolbar.

Navigating the Dialog
When you hit sort, provided that the region is active, the edit line will activate with a dialog requesting instructions. Use the arrows (left and right) to move around in it. Hitting enter when on any of the three buttons pushes them. Note that the dialog is still fairly poor, and many useful keys are unbound.

Concepts
The critical information you must enter are the key and the orientation.
The key is which pieces of data to sort by. This is entered as a list of integers. Each number represents one cell in the row that is one datapoint. The numbering scheme starts at one, not zero.
The orientation is either rows or columns. The orientation refers to the direction in which one type of data lies, that is, perpendicular to a given datapoint.
An example: (because this is confusing)
Foo1 19.5 5
Foo2 14.5 10
Foo3 19.5 0
Sorted by columns with key '(2 3) yields
Foo2 14.5 10
Foo1 19.5 0
Foo3 19.5 5

The Other Sort
There is another way to sort data. You can use the sortmtx command from within a formula. For example:
=(nth (- y 8) (nth (- x 4) (sortmtx (range 2 8 3 11) '(0))))
This, if put into the eight cells from 4,8 to 5,11, will sort the data in 2,8..3,11 by rows. Note that here, the numbering starts at zero.
If you wish to sort by columns, you must use this:
=(nth (- x 4) (nth (- y 8) (sortmtx (transpose (range 2 8 3 11)) '(1))))

This type of sorting allows the sorted data to exist simultaneously with the unsorted. This allows you to edit the unsorted more easily. However, whenever you edit the unsorted, the sorted data will resort itself. This is both a feature and a bug: it can be very useful, but slows down all your editing. Note also that this method of sorting calls sort many times whereas the other method only sorts once. This makes it slower even if you never edit the unsorted data.

Graph
Graph in esheet automatically takes data from the region.  It then displays the graph in its own buffer (using image-mode, specifically, it is an xpm).  The graph is always a bar graph, with rows clustered and all datapoints from one column the same color.  Because that description is confusing, I provide a graphical example:
 
 
data: yields graph:
1 4 7
2 5 8
3 6 9

 

The Mouse Mouse support is not yet fully implemented, but some is available. Clicking on a cell takes you to that cell, and dragging selects a region. Neither works if used over an area that is empty (not filled with spaces). Areas become filled with spaces only when you move to them with the arrows (though they dco so as entire rows and columns, so if your cursor's been in that row and column, the cell has spaces). If you click there, Esheet will do nothing, but if you drag it will lose cursor function and you will have to run M-x restore-cursor-function. Do not use the mouse when in the edit line.

Runtime Troubleshooting
Various things can go wrong while using esheet. A few of them are very easy to fix.

Toolbars
The edit line is implemented as an ordinary buffer in an ordinary window. The toolbar is modified by various other packages.  This means you can accidentally eliminate them from your screen. If you do this, you can fix it with C-e

Line Length Fixes
The proper function of the esheet cursor is dependent on the maintenance of correct line lengths. It is possible (though most such bugs have been eliminated) to mess this up. If this happens, you will note erratic and irrational cursor function (e.g. the cursor is located in between cells). M-x restore-cursor-function will restore the cursor's function. Warning: it is possible for this function to lose data on the right edge of the sheet.

Bugs
The ability to do the above two things.
Save doesn't mark the buffer as unmodified.
If you create a region with the mouse outside the area actually filled with spaces, the cursor will break unttil you run M-x restore-cursor-function
Lots more, I'm sure. 


This text is distributed under the terms of the General Public License.
This text written by Daniel Speyer