SES bugs and suggestions

[Gareth Rees <gareth.rees@pobox.com>]

This is a report on the Simple Emacs Spreadsheet (SES).  It contains a
number of suggestions for improvements to the SES code, documentation,
and manual.

I'm using GNU Emacs 22.1.1 (powerpc-apple-darwin8.9.0, Carbon Version
1.6.0) of 2007-06-17 on g5.tokyo.stp.isas.jaxa.jp.

1. As a new user, the first thing I want to know is how to create a new
spreadsheet, but the manual does not say how to do this.  It would be
nice if there were a node on this subject in the manual; this should
come before the node "The Basics", which assumes that you have already
created a new spreadsheet.

The answer appears to be: visit an empty buffer and type "M-x ses-mode
RET".  However, this makes a spreadsheet with just one row and one
column.  I can add rows and columns at this point, or I can configure
`ses-initial-size', but both of these seem awkward to me: surely a
common use-case is to start out with a spreadsheet of a particular size.

Here's a command that does this:

(defun ses-create (rows cols)
   "Create a new spreadsheet with size ROWS by COLS."
   (interactive "nRows: \nnColumns: ")
   (when (< rows 1) (error "There must be at least one row."))
   (when (< cols 1) (error "There must be at least one column."))
   (switch-to-buffer (generate-new-buffer "untitled-spreadsheet"))
   (ses-mode)
   (ses-resize rows cols))

(eval-when-compile (require 'cl))

(defun ses-resize (rows cols)
   "Resize spreadsheet to ROWS by COLS.
If this would involve deleting any cells with non-empty formulas, then
ask for confirmation."
   (interactive "nRows: \nnColumns: ")
   (when (< rows 1) (error "There must be at least one row."))
   (when (< cols 1) (error "There must be at least one column."))
   (let ((deleted-cells
          (loop for i from 0 below ses--numrows
                for j from 0 below ses--numcols
                count (and (or (<= rows i) (<= cols j))
                           (ses-cell-formula i j)))))
     (when (or (eql 0 deleted-cells)
               (y-or-n-p
                (format "Resizing will delete %d non-empty cells.  
Continue? "
                        deleted-cells)))
       (ses-begin-change)
       (save-excursion
         (let (ses--curcell)
           (when (< ses--numrows rows)
             ;; `ses-insert-row' inserts at the bottom if `ses-- 
curcell' is nil.
             (ses-insert-row (- rows ses--numrows)))
           (when (< rows ses--numrows)
             (ses-goto-print rows 0)
             (ses-set-curcell)
             (ses-delete-row (- ses--numrows rows)))
           (when (< ses--numcols cols)
             (ses-goto-print 0 0)
             (ses-set-curcell)
             (ses-insert-column (- cols ses--numcols)
                                ses--numcols
                                (ses-col-width (1- ses--numcols))
                                (ses-col-printer (1- ses--numcols))))
           (when (< cols ses--numcols)
             (ses-goto-print 0 cols)
             (ses-set-curcell)
             (ses-delete-column (- ses--numcols cols))))))))

2. If I already have a buffer or file containing tabular data, can I
convert that to SES?  This really ought to be the second item in the
manual after how to create a new file.

The answer appears to be, "(a) format the tabular data as tab-separated
fields, (b) make sure all the lines have the same number of fields; (c)
copy the data to the kill ring, (d) create a new spreadsheet; (e) yank
the data."

It would be nice if `ses-mode' offered to create a spreadsheet based on
the current contents if started in a buffer that contained tabular data.

Steps (a) and (b) can be a bit tedious, so it would be nice to have
helper functions to do this for tab-separated data and other common
tabular formats, for example:

(i) Comma-separated values
(ii) Space-formatted tables (like the output of `ls -l', `df', etc)
(iii) XHTML using <table>, <tr>, <td> markup

It would also be nice to have exporters for these formats (copy and yank
already works for (ii)).

(I could contribute some code here if anyone thinks it would be useful.)

3. The documentation for the command `ses-mode' recommends looking at
`etc/ses-example.ses' "for more info".  But when I visit that file I  
get the
following warning:

     The local variables list in ses-example.ses
     contains values that may not be safe (*).

     Do you want to apply it?  You can type
     y  -- to apply the local variables list.
     n  -- to ignore the local variables list.
     !  -- to apply the local variables list, and permanently mark these
           values (*) as safe (in the future, they will be set  
automatically.)

         mode : ses
       * life-universe-everything : 42
       * symbolic-formulas : (("Eastern area") ("West-district")  
("North&South") ("Other"))

This is rather unfriendly.  Since it's a demonstration file, it ought to
be set up to use only safe local variables.  As far as I can tell,
neither the `life-universe-everything' nor the `symbolic-formulas'
variable are used for anything.

4. You can't copy a rectangle of tabular data to a spreadsheet:
`insert-register' just fails with the message "Text is read-only".

(You can insert the register into a temporary buffer, copy the whole of
the temporary buffer, and yank into the spreadsheet, but that seems like
the kind of thing that a program could do.)

5. If I visit an empty cell and type "C-c C-c" (`ses-recalculate-cell')
I get the error:

     Format specifier doesn't match argument type

This message is not very informative, and anyway, surely this should
just be a no-op?

6. When there are more than 702 columns the columns start getting
non-alphabetical names: the 703rd column is called "[A", the 704th "[B"
and so on.  This is unlikely to be a problem in practice, but it costs
little to be robust:

(defun ses-column-letter (col)
   "Return the alphabetic name of column number COL.
0-25 become A-Z; 26-701 become AA-ZZ, and so on."
   (let ((units (char-to-string (+ ?A (% col 26)))))
     (if (< col 26)
         units
       (concat (ses-column-letter (1- (/ col 26))) units))))

7. If I use the mouse to select a region of the spreadsheet including
the blank line below the last row of the spreadsheet, I get one of these
two errors from `ses-copy-region-helper' (depending on exactly which
cells I select):

     Empty range
     aref: Wrong type argument: integerp, nil

It's not appropriate to get an error when I'm just selecting a region
with the mouse.

If I attempt to copy the whole spreadsheet by typing "C-x h M-w", I get
the error "Empty Range".

8. SES uses transient-mark-mode to highlight the selected region.  But
this is misleading because SES commands actually operate on the selected
rectangle.  (I know this is difficult: consider this another vote for
adding rectangle highlighting to the display engine.)

9. Info node "(ses)Customizing SES" says:

     By default, a newly-created spreadsheet has 1 row and 1 column.   
The
     column width is 7 and the default printer is `"%.7g"'.  Each of  
these
     can be customized.  Look in group "ses".

It would be better to name the options here, perhaps like this:

     By default, a newly-created spreadsheet has 1 row and 1 column.   
The
     column width is 7 and the default printer is `"%.7g"'.  These  
can be
     customized by setting the options `ses-initial-size',
     `ses-initial-column-width' and `ses-initial-default-printer'
     respectively.

10. Some key bindings seem awkward to me: a typical example is "C-c
M-C-h" to set the header row.  This despite the fact that most letter
keys are available for assignment (only `jpwx' are used).  So what about
`h' as well?  Some judicious assignment of letters (and letter prefixes,
like `x' for exporting functions) could make editing spreadsheets much
easier on the wrists.  Some other suggestions:

     c   ses-recalculate-cell
     C   ses-recalculate-all
     t   ses-truncate-cell   (and how about T = ses-truncate-all?)
     s   ses-sort-column

11. Info node "(ses)Buffer-local variables in spreadsheets" says:

     You can override the variable `symbolic-formulas' to be a list of
     symbols (as parenthesized strings) to show as completions for the '
     command.  This initial completions list is used instead of the  
actual
     set of symbols-as-formulas in the spreadsheet.

This is not the case.  Setting `symbolic-formulas' has no effect.

--
Gareth Rees