SES bugs and suggestions

SES bugs and suggestions

[Gareth Rees]

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

Re: SES bugs and suggestions

[Jonathan Yavner]

> 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.

I have changed `ses.texi' in the CVS version (Emacs 23.0.50) to say:
  To create a new spreadsheet, visit a nonexistent file whose name ends
  with ".ses".  For example, `C-x C-f test.ses 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.

Yes, but what size should the default spreadsheet be?  1x1 is 
the "know-nothing" case.  Then you can use TAB and LF to extend the 
sheet to the desired 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))

When would this command be called?  The problem case is when visiting a 
new file.  It is not usual in Emacs to have parameter prompts when 
visiting an empty file.


> (eval-when-compile (require 'cl))
> ...
>           (loop for i from 0 below ses--numrows

Use of Common Lisp seems unnecessary here.  Why not
    (dotimes (i ses--numrows)


> 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.)

By all means, hack away!  My impression is that you have not already 
signed copyright-assignment papers for work on Emacs.  That paperwork 
could take months (the FSF is a bit slow), so you might want to drop a 
note about it to copyright-clerk@gnu.org right away.

I think overloading `ses-mode' is asking for trouble.  Perhaps a 
different function `convert-to-ses' would be better?  Or separate 
functions for convert-xhtml-to-ses, convert-csv-to-ses, etc.?


> 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 (*).
>
> This is rather unfriendly.

Yes.  The security of local variables has been tightened since 
ses-example.ses was written.


> 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.

The variable `life-universe-everything' was referenced by cell C16 in 
ses-example.ses.

Variable `symbolic-formulas' got renamed to `ses--symbolic-formulas' 
years ago.  You are the first person to notice this bug!

I have removed `life-universe-everything' since it's a silly feature and
not worth making it work.


> 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.

I have changed the variable name to `ses--symbolic-formulas' in the 
documentation and added a `safe-local-variable' property for it to get 
rid of the warning on file-load.


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

SES has specific overrides for various "insert" functions.  It currently 
has no override for `insert-register'.  This is a missing feature.  It 
should be fixed at some point.


> 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?

It's hard to say what to do here.  The default format is "%.7g" which 
signals an error for non-numeric values.  This error is normally 
suppressed, but C-c C-c is supposed to show suppressed errors.  I added 
a note in the documentation.


> 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))))

This is an improvement to SES because it increases adherence to the GNU 
coding standards, which call for programs not to have arbitrary hard 
limits like 702 columns.  I have installed this change into the CVS 
archives.  Congratulations Gareth, you are now an Emacs contributor!  
But this counts as 4 of the 12 lines of code that you can contribute 
without signing papers.

However, slightly-bad things happen to column 704 when column 703's 
width is set to 1 because now the column header is too wide and is 
misaligned.

In my experience, SES is just too slow to use on spreadsheets of 703 
columns.  Part of the sloth is the ridiculous algorithm it uses for 
finding the data line for a cell, which basically goes to the top and 
counts newlines until it finds the right spot!  Needs work...  (Maybe a 
marker for every row would help?)


> 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".

You should not be able to select the blank line below the last row.  
Apparently there are been changes elsewhere in Emacs for v22 that 
affected how this works.

I'm not exactly sure what's happening here, but I got rid of the error 
by having ses-copy-region and ses-set-curcell correct for the extra 
line if seen.


> 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.)

Yes.


> 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

Would it?  I wanted an excuse to mention the entire customization group, 
rather than mentioning all its options individually everywhere.


> 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.

This would certainly be a good change, if it is actually better to 
always list individual options and never mention the customization 
group that contains them.


> 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).

I wanted to reserve the plain keys for future "important" functions 
(that never showed up).  C-c M-C-h is used only by people who don't 
right-click on the header row.


> So what about `h' as well?

Is this really a function used all that often?


> 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

I have added 't' for ses-truncate-cell.  I suppose ses-truncate-all 
could be written at some point.

Recaculate-cell should be a rarely-used function, but it is useful 
immediately after truncating, so I have added it as 'c'.

If you need to use recalculate-all more than once in a blue moon, 
something is going terribly wrong for you.  It's supposed to be for 
recovering after screw-ups.

Sort-column should probably be in the popup menu for the header row.


Thanks for writing!

Re: SES bugs and suggestions

[Miles Bader]

Jonathan Yavner <jyavner@member.fsf.org> writes:
>> (eval-when-compile (require 'cl))
>> ...
>>           (loop for i from 0 below ses--numrows
>
> Use of Common Lisp seems unnecessary here.  Why not
>     (dotimes (i ses--numrows)

Seriously.

Loop no doubt seemed very clever at the time, but in actual usage it
almost always makes code _less_ readable.

-Miles

-- 
We are all lying in the gutter, but some of us are looking at the stars.
-Oscar Wilde