~% 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.)
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:
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 |
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 |
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 |
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