1 \input texinfo @c -*- mode: texinfo; coding: utf-8; -*-
3 @setfilename ../../info/ses.info
4 @settitle @acronym{SES}: Simple Emacs Spreadsheet
13 This file documents @acronym{SES}: the Simple Emacs Spreadsheet.
15 Copyright @copyright{} 2002--2016 Free Software Foundation, Inc.
18 Permission is granted to copy, distribute and/or modify this document
19 under the terms of the GNU Free Documentation License, Version 1.3 or
20 any later version published by the Free Software Foundation; with no
21 Invariant Sections, with the Front-Cover Texts being ``A GNU Manual,''
22 and with the Back-Cover Texts as in (a) below. A copy of the license
23 is included in the section entitled ``GNU Free Documentation License.''
25 (a) The FSF's Back-Cover Text is: ``You have the freedom to copy and
26 modify this GNU manual.''
30 @dircategory Emacs misc features
32 * @acronym{SES}: (ses). Simple Emacs Spreadsheet.
39 @subtitle Simple Emacs Spreadsheet
40 @author Jonathan A. Yavner
41 @author @email{jyavner@@member.fsf.org}
44 @vskip 0pt plus 1filll
50 @c ===================================================================
54 @comment node-name, next, previous, up
55 @top @acronym{SES}: Simple Emacs Spreadsheet
58 @acronym{SES} is a major mode for GNU Emacs to edit spreadsheet files, which
59 contain a rectangular grid of cells. The cells' values are specified
60 by formulas that can refer to the values of other cells.
64 To report bugs, use @kbd{M-x report-emacs-bug}.
69 * Sales Pitch:: Why use @acronym{SES}?
70 * Quick Tutorial:: A quick introduction
71 * The Basics:: Basic spreadsheet commands
72 * Advanced Features:: Want to know more?
73 * For Gurus:: Want to know @emph{even more}?
74 * Index:: Concept, Function and Variable Index
75 * Acknowledgments:: Acknowledgments
76 * GNU Free Documentation License:: The license for this documentation.
79 @c ===================================================================
82 @comment node-name, next, previous, up
87 @item Create and edit simple spreadsheets with a minimum of fuss.
88 @item Full undo/redo/autosave.
89 @item Immune to viruses in spreadsheet files.
90 @item Cell formulas are straight Emacs Lisp.
91 @item Printer functions for control of cell appearance.
92 @item Intuitive keystroke commands: C-o = insert row, M-o = insert column, etc.
93 @item ``Spillover'' of lengthy cell values into following blank cells.
94 @item Header line shows column letters or a selected row.
95 @item Completing-read for entering symbols as cell values.
96 @item Cut, copy, and paste can transfer formulas and printer functions.
97 @item Import and export of tab-separated values or tab-separated formulas.
98 @item Plaintext, easily-hacked file format.
101 @c ===================================================================
104 @chapter Quick Tutorial
108 If you want to get started quickly and think that you know what to
109 expect from a simple spreadsheet, this chapter may be all that you
112 First, visit a new file with the @file{.ses} extension.
113 Emacs presents you with an empty spreadsheet containing a single cell.
115 Begin by inserting a headline: @kbd{"Income"@key{RET}}. The double
116 quotes indicate that this is a text cell. (Notice that Emacs
117 automatically inserts the closing quotation mark.)
119 To insert your first income value, you must first resize the
120 spreadsheet. Press @key{TAB} to add a new cell and navigate back up
121 to it. Enter a number, such as @samp{2.23}. Then proceed to add a
122 few more income entries, e.g.:
135 To add up the values, enter a Lisp expression:
141 Perhaps you want to add a cell to the right of cell A4 to explain
142 why you have a negative entry. Pressing @kbd{TAB} in that cell
143 adds an entire new column @samp{B}, where you can add such a note.
145 The column is fairly narrow by default, but pressing @kbd{w} allows
146 you to resize it as needed. Make it 20 characters wide. You can
147 now add descriptive legends for all the entries, e.g.:
154 0.02 Informed opinion
161 By default, the labels in column B are right-justified. To change
162 that, you can enter a printer function for the whole column, using
163 e.g., @kbd{M-p ("%s")}. You can override a column's printer function
164 in any individual cell using @kbd{p}.
166 If Joe pays back his loan, you might blank that entry; e.g., by
167 positioning the cursor in cell A5 and pressing @kbd{C-d} twice.
168 If you do that, the total cell will display @samp{######}. That is
169 because the regular @code{+} operator does not handle a range that
170 contains some empty cells. Instead of emptying the cell, you could
171 enter a literal @samp{0}, or delete the entire row using @kbd{C-k}.
172 An alternative is to use the special function @code{ses+} instead of
173 the regular @code{+}:
179 To make a formula robust against changes in the spreadsheet geometry,
180 you can use the @code{ses-range} macro to refer to a range of cells by
181 the end-points, e.g.:
184 (apply 'ses+ (ses-range A2 A5))
187 (The @code{apply} is necessary because @code{ses-range} produces a
188 @emph{list} of values. This allows for more complex possibilities.)
190 @c ===================================================================
193 @comment node-name, next, previous, up
195 @cindex basic commands
198 @findex ses-mark-column
199 @findex ses-mark-whole-buffer
200 @findex set-mark-command
201 @findex keyboard-quit
203 To create a new spreadsheet, visit a nonexistent file whose name ends
204 with ".ses". For example, @kbd{C-x C-f test.ses RET}.
207 A @dfn{cell identifier} is a symbol with a column letter and a row
208 number. Cell B7 is the 2nd column of the 7th row. For very wide
209 spreadsheets, there are two column letters: cell AB7 is the 28th
210 column of the 7th row. Super wide spreadsheets get AAA1, etc.
211 You move around with the regular Emacs movement commands.
215 Moves point to cell, specified by identifier (@code{ses-jump}).
218 Point is always at the left edge of a cell, or at the empty endline.
219 When mark is inactive, the current cell is underlined. When mark is
220 active, the range is the highlighted rectangle of cells (@acronym{SES} always
221 uses transient mark mode). Drag the mouse from A1 to A3 to create the
222 range A1-A2. Many @acronym{SES} commands operate only on single cells, not
228 Set mark at point (@code{set-mark-command}).
231 Turn off the mark (@code{keyboard-quit}).
234 Highlight current row (@code{ses-mark-row}).
237 Highlight current column (@code{ses-mark-column}).
240 Highlight all cells (@code{mark-whole-buffer}).
246 * Printer functions::
249 * Customizing @acronym{SES}::
253 @section Cell formulas
255 @cindex formulas, entering
258 @cindex editing cells
259 @findex ses-read-cell
260 @findex ses-read-symbol
261 @findex ses-edit-cell
262 @findex ses-recalculate-cell
263 @findex ses-recalculate-all
265 To insert a value into a cell, simply type a numeric expression,
266 @samp{"double-quoted text"}, or a Lisp expression.
270 Self-insert a digit (@code{ses-read-cell}).
273 Self-insert a negative number (@code{ses-read-cell}).
276 Self-insert a fractional number (@code{ses-read-cell}).
279 Self-insert a quoted string. The ending double-quote
280 is inserted for you (@code{ses-read-cell}).
283 Self-insert an expression. The right-parenthesis is inserted for you
284 (@code{ses-read-cell}). To access another cell's value, just use its
285 identifier in your expression. Whenever the other cell is changed,
286 this cell's formula will be reevaluated. While typing in the
287 expression, you can use @kbd{M-@key{TAB}} to complete symbol names.
289 @item ' @r{(apostrophe)}
290 Enter a symbol (ses-read-symbol). @acronym{SES} remembers all symbols that have
291 been used as formulas, so you can type just the beginning of a symbol
292 and use @kbd{@key{SPC}}, @kbd{@key{TAB}}, and @kbd{?} to complete it.
295 To enter something else (e.g., a vector), begin with a digit, then
296 erase the digit and type whatever you want.
300 Edit the existing formula in the current cell (@code{ses-edit-cell}).
303 Force recalculation of the current cell or range (@code{ses-recalculate-cell}).
306 Recalculate the entire spreadsheet (@code{ses-recalculate-all}).
310 @section Resizing the spreadsheet
311 @cindex resizing spreadsheets
313 @cindex row, adding or removing
314 @cindex column, adding or removing
315 @cindex adding rows or columns
316 @cindex inserting rows or columns
317 @cindex removing rows or columns
318 @cindex deleting rows or columns
319 @findex ses-insert-row
320 @findex ses-insert-column
321 @findex ses-delete-row
322 @findex ses-delete-column
323 @findex ses-set-column-width
324 @findex ses-forward-or-insert
325 @findex ses-append-row-jump-first-column
332 (@code{ses-insert-row})
335 (@code{ses-insert-column})
338 (@code{ses-delete-row})
341 (@code{ses-delete-column})
344 (@code{ses-set-column-width})
347 Moves point to the next rightward cell, or inserts a new column if
348 already at last cell on line, or inserts a new row if at endline
349 (@code{ses-forward-or-insert}).
352 Linefeed inserts below the current row and moves to column A
353 (@code{ses-append-row-jump-first-column}).
356 Resizing the spreadsheet (unless you're just changing a column width)
357 relocates all the cell-references in formulas so they still refer to
358 the same cells. If a formula mentioned B1 and you insert a new first
359 row, the formula will now mention B2.
361 If you delete a cell that a formula refers to, the cell-symbol is
362 deleted from the formula, so @code{(+ A1 B1 C1)} after deleting the third
363 column becomes @code{(+ A1 B1)}. In case this is not what you wanted:
368 Undo previous action (@code{(undo)}).
372 @node Printer functions
373 @section Printer functions
374 @cindex printer functions
375 @cindex cell formatting
376 @cindex formatting cells
377 @findex ses-read-cell-printer
378 @findex ses-read-column-printer
379 @findex ses-read-default-printer
380 @findex ses-define-local-printer
382 @findex ses-center-span
384 @findex ses-dashfill-span
385 @findex ses-tildefill-span
388 Printer functions convert binary cell values into the print forms that
389 Emacs will display on the screen.
391 A printer can be a format string, like @samp{"$%.2f"}. The result
392 string is right-aligned within the print cell. To get left-alignment,
393 use parentheses: @samp{("$%.2f")}. A printer can also be a
394 one-argument function (a symbol or a lambda), whose result is a string
395 (right-aligned) or list of one string (left-aligned). While typing in
396 a lambda, you can use @kbd{M-@key{TAB}} to complete the names of symbols.
398 Each cell has a printer. If @code{nil}, the column-printer for the cell's
399 column is used. If that is also @code{nil}, the default-printer for the
404 Enter a printer for current cell or range (@code{ses-read-cell-printer}).
407 Enter a printer for the current column (@code{ses-read-column-printer}).
410 Enter the default printer for the spreadsheet
411 (@code{ses-read-default-printer}).
414 The @code{ses-read-@r{XXX}-printer} commands have their own minibuffer
415 history, which is preloaded with the set of all printers used in this
416 spreadsheet, plus the standard printers.
418 The standard printers are suitable only for cells, not columns or
419 default, because they format the value using the column-printer (or
420 default-printer if @code{nil}) and then center the result:
426 @item ses-center-span
427 Centering with spill-over to following blank cells.
430 Centering using dashes (-) instead of spaces.
432 @item ses-dashfill-span
433 Centering with dashes and spill-over.
435 @item ses-tildefill-span
436 Centering with tildes (~) and spill-over.
439 You can define printer function local to a sheet with the command
440 @code{ses-define-local-printer}. For instance, define a printer
441 @samp{foo} to @code{"%.2f"}, and then use symbol @samp{foo} as a
442 printer function. Then, if you call again
443 @code{ses-define-local-printer} on @samp{foo} to redefine it as
444 @code{"%.3f"}, all the cells using printer @samp{foo} will be
445 reprinted accordingly.
447 When you define a printer function with a lambda expression taking one
448 argument, please take care that the returned value is a string, or a
449 list containing a string, even when the input argument has an
450 unexpected value. Here is an example:
456 ((and (numberp val) (>= val 0)) (format "%.1f" val))
457 (t (ses-center-span (format "%S" val) ?#))))
463 When the cell is empty (ie.@: when @code{val} is @code{nil}), print an
464 empty string @code{""}
466 When the cell value is a non negative number, format the the value in
467 fixed-point notation with one decimal after point
469 Otherwise, handle the value as erroneous by printing it as an
470 s-expression (using @code{prin1}), centered and surrounded by @code{#}
478 @section Clearing cells
479 @cindex clearing commands
480 @findex ses-clear-cell-backward
481 @findex ses-clear-cell-forward
483 These commands set both formula and printer to @code{nil}:
487 Clear cell and move left (@code{ses-clear-cell-backward}).
490 Clear cell and move right (@code{ses-clear-cell-forward}).
495 @section Copy, cut, and paste
499 @findex kill-ring-save
500 @findex mouse-set-region
501 @findex mouse-set-secondary
502 @findex ses-kill-override
504 @findex clipboard-yank
505 @findex mouse-yank-at-click
506 @findex mouse-yank-at-secondary
509 The copy functions work on rectangular regions of cells. You can paste the
510 copies into non-@acronym{SES} buffers to export the print text.
516 Copy the highlighted cells to kill ring and primary clipboard
517 (@code{kill-ring-save}).
520 Mark a region and copy it to kill ring and primary clipboard
521 (@code{mouse-set-region}).
523 @item [M-drag-mouse-1]
524 Mark a region and copy it to kill ring and secondary clipboard
525 (@code{mouse-set-secondary}).
530 The cut functions do not actually delete rows or columns---they copy
531 and then clear (@code{ses-kill-override}).
535 Paste from kill ring (@code{yank}). The paste functions behave
536 differently depending on the format of the text being inserted:
539 When pasting cells that were cut from a @acronym{SES} buffer, the print text is
540 ignored and only the attached formula and printer are inserted; cell
541 references in the formula are relocated unless you use @kbd{C-u}.
543 The pasted text overwrites a rectangle of cells whose top left corner
544 is the current cell. If part of the rectangle is beyond the edges of
545 the spreadsheet, you must confirm the increase in spreadsheet size.
547 Non-@acronym{SES} text is usually inserted as a replacement formula for the
548 current cell. If the formula would be a symbol, it's treated as a
549 string unless you use @kbd{C-u}. Pasted formulas with syntax errors
550 are always treated as strings.
554 Paste from primary clipboard or kill ring (@code{clipboard-yank}).
557 Set point and paste from primary clipboard (@code{mouse-yank-at-click}).
560 Set point and paste from secondary clipboard (@code{mouse-yank-secondary}).
563 Immediately after a paste, you can replace the text with a preceding
564 element from the kill ring (@code{ses-yank-pop}). Unlike the standard
565 Emacs yank-pop, the @acronym{SES} version uses @code{undo} to delete the old
566 yank. This doesn't make any difference?
569 @node Customizing @acronym{SES}
570 @section Customizing @acronym{SES}
572 @vindex enable-local-eval
573 @vindex ses-mode-hook
574 @vindex safe-functions
575 @vindex enable-local-eval
578 By default, a newly-created spreadsheet has 1 row and 1 column. The
579 column width is 7 and the default printer is @samp{"%.7g"}. Each of these
580 can be customized. Look in group ``ses''.
582 After entering a cell value, point normally moves right to the next
583 cell. You can customize @code{ses-after-entry-functions} to move left or
584 up or down. For diagonal movement, select two functions from the
587 @code{ses-mode-hook} is a normal mode hook (list of functions to
588 execute when starting @acronym{SES} mode for a buffer).
590 The variable @code{safe-functions} is a list of possibly-unsafe
591 functions to be treated as safe when analyzing formulas and printers.
592 @xref{Virus protection}. Before customizing @code{safe-functions},
593 think about how much you trust the person who's suggesting this
594 change. The value @code{t} turns off all anti-virus protection. A
595 list-of-functions value might enable a ``gee whiz'' spreadsheet, but it
596 also creates trapdoors in your anti-virus armor. In order for virus
597 protection to work, you must always press @kbd{n} when presented with
598 a virus warning, unless you understand what the questionable code is
599 trying to do. Do not listen to those who tell you to customize
600 @code{enable-local-eval}---this variable is for people who don't wear
604 @c ===================================================================
606 @node Advanced Features
607 @chapter Advanced Features
608 @cindex advanced features
609 @findex ses-read-header-row
614 (@code{ses-set-header-row}).
615 @findex ses-set-header-row
617 The header line at the top of the @acronym{SES}
618 window normally shows the column letter for each column. You can set
619 it to show a copy of some row, such as a row of column titles, so that
620 row will always be visible. Default is to set the current row as the
621 header; use C-u to prompt for header row. Set the header to row 0 to
622 show column letters again.
623 @item [header-line mouse-3]
624 Pops up a menu to set the current row as the header, or revert to
626 @item M-x ses-rename-cell
627 @findex ses-rename-cell
628 Rename a cell from a standard A1-like name to any
630 @item M-x ses-repair-cell-reference-all
631 @findex ses-repair-cell-reference-all
632 When you interrupt a cell formula update by clicking @kbd{C-g}, then
633 the cell reference link may be broken, which will jeopardize automatic
634 cell update when any other cell on which it depends is changed. To
635 repair that use function @code{ses-repair-cell-reference-all}
640 * Ranges in formulas::
641 * Sorting by column::
642 * Standard formula functions::
643 * More on cell printing::
644 * Import and export::
646 * Spreadsheets with details and summary::
650 @section The print area
653 @findex ses-renarrow-buffer
654 @findex ses-reprint-all
656 A @acronym{SES} file consists of a print area and a data area. Normally the
657 buffer is narrowed to show only the print area. The print area is
658 read-only except for special @acronym{SES} commands; it contains cell values
659 formatted by printer functions. The data area records the formula and
660 printer functions, etc.
664 Show print and data areas (@code{widen}).
667 Show only print area (@code{ses-renarrow-buffer}).
671 Recreate print area by reevaluating printer functions for all cells
672 (@code{ses-reprint-all}).
675 @node Ranges in formulas
676 @section Ranges in formulas
678 @findex ses-insert-range-click
679 @findex ses-insert-range
680 @findex ses-insert-ses-range-click
681 @findex ses-insert-ses-range
689 is the sum of three specific cells. If you insert a new second row,
694 and the new row is not included in the sum.
696 The macro @code{(ses-range @var{from} @var{to})} evaluates to a list of
697 the values in a rectangle of cells. If your formula is
699 (apply '+ (ses-range A1 A3))
701 and you insert a new second row, it becomes
703 (apply '+ (ses-range A1 A4))
705 and the new row is included in the sum.
707 While entering or editing a formula in the minibuffer, you can select
708 a range in the spreadsheet (using mouse or keyboard), then paste a
709 representation of that range into your formula. Suppose you select
714 Inserts "A1 B1 C1" @code{(ses-insert-range-click})
717 Keyboard version (@code{ses-insert-range}).
720 Inserts "(ses-range A1 C1)" (@code{ses-insert-ses-range-click}).
723 Keyboard version (@code{ses-insert-ses-range}).
726 If you delete the @var{from} or @var{to} cell for a range, the nearest
727 still-existing cell is used instead. If you delete the entire range,
728 the formula relocator will delete the ses-range from the formula.
730 If you insert a new row just beyond the end of a one-column range, or
731 a new column just beyond a one-row range, the new cell is included in
732 the range. New cells inserted just before a range are not included.
734 Flags can be added to @code{ses-range} immediately after the @var{to}
738 Empty cells in range can be removed by adding the @code{!} flag. An
739 empty cell is a cell the value of which is one of symbols @code{nil}
740 or @code{*skip*}. For instance @code{(ses-range A1 A4 !)} will do the
741 same as @code{(list A1 A3)} when cells @code{A2} and @code{A4} are
744 Empty cell values are replaced by the argument following flag
745 @code{_}, or @code{0} when flag @code{_} is last in argument list. For
746 instance @code{(ses-range A1 A4 _ "empty")} will do the same as
747 @code{(list A1 "empty" A3 "empty")} when cells @code{A2} and @code{A4}
748 are empty. Similarly, @code{(ses-range A1 A4 _ )} will do the same as
749 @code{(list A1 0 A3 0)}.
751 When order matters, list cells by reading cells row-wise from top left
752 to bottom right. This flag is provided for completeness only as it is
753 the default reading order.
755 List cells by reading cells row-wise from top right to bottom left.
757 List cells by reading cells column-wise from top left to bottom right.
759 List cells by reading cells column-wise from top right to bottom left.
761 A short hand for @code{v>}.
763 A short hand for @code{^>}.
765 A short hand for @code{>v}.
767 A short hand for @code{>^}.
769 Instead of listing cells, it makes a Calc vector or matrix of it
770 (@pxref{Top,,,calc,GNU Emacs Calc Manual}). If the range contains only
771 one row or one column a vector is made, otherwise a matrix is made.
773 Same as @code{*} except that a matrix is always made even when there
774 is only one row or column in the range.
776 Same as @code{*} except that a vector is always made even when there
777 is only one row or column in the range, that is to say the
778 corresponding matrix is flattened.
781 @node Sorting by column
782 @section Sorting by column
784 @findex ses-sort-column
785 @findex ses-sort-column-click
789 Sort the cells of a range using one of the columns
790 (@code{ses-sort-column}). The rows (or partial rows if the range
791 doesn't include all columns) are rearranged so the chosen column will
794 @item [header-line mouse-2]
795 The easiest way to sort is to click mouse-2 on the chosen column's header row
796 (@code{ses-sort-column-click}).
799 The sort comparison uses @code{string<}, which works well for
800 right-justified numbers and left-justified strings.
802 With prefix arg, sort is in descending order.
804 Rows are moved one at a time, with relocation of formulas. This works
805 well if formulas refer to other cells in their row, not so well for
806 formulas that refer to other rows in the range or to cells outside the
810 @node Standard formula functions
811 @section Standard formula functions
812 @cindex standard formula functions
815 @findex ses-delete-blanks
819 Oftentimes you want a calculation to exclude the blank cells. Here
820 are some useful functions to call from your formulas:
823 @item (ses-delete-blanks &rest @var{args})
824 Returns a list from which all blank cells (value is either @code{nil} or
825 '*skip*) have been deleted.
827 @item (ses+ &rest @var{args})
828 Sum of non-blank arguments.
830 @item (ses-average @var{list})
831 Average of non-blank elements in @var{list}. Here the list is passed
832 as a single argument, since you'll probably use it with @code{ses-range}.
835 @node More on cell printing
836 @section More on cell printing
837 @cindex cell printing, more
838 @findex ses-truncate-cell
839 @findex ses-recalculate-cell
843 @item nil prints the same as "", but allows previous cell to spill over.
844 @item '*skip* replaces nil when the previous cell actually does spill over;
845 nothing is printed for it.
846 @item '*error* indicates that the formula signaled an error instead of
847 producing a value: the print cell is filled with hash marks (#).
850 If the result from the printer function is too wide for the cell and
851 the following cell is @code{nil}, the result will spill over into the
852 following cell. Very wide results can spill over several cells. If
853 the result is too wide for the available space (up to the end of the
854 row or the next non-@code{nil} cell), the result is truncated if the cell's
855 value is a string, or replaced with hash marks otherwise.
857 @acronym{SES} could get confused by printer results that contain newlines or
858 tabs, so these are replaced with question marks.
862 Confine a cell to its own column (@code{ses-truncate-cell}). This
863 allows you to move point to a rightward cell that would otherwise be
864 covered by a spill-over. If you don't change the rightward cell, the
865 confined cell will spill over again the next time it is reprinted.
868 When applied to a single cell, this command displays in the echo area
869 any formula error or printer error that occurred during
870 recalculation/reprinting (@code{ses-recalculate-cell}). You can use
871 this to undo the effect of @kbd{t}.
874 When a printer function signals an error, the fallback printer
875 @samp{"%s"} is substituted. This is useful when your column printer
876 is numeric-only and you use a string as a cell value. Note that the
877 standard default printer is ``%.7g'' which is numeric-only, so cells
878 that are empty of contain strings will use the fallback printer.
879 @kbd{c} on such cells will display ``Format specifier doesn't match
883 @node Import and export
884 @section Import and export
885 @cindex import and export
886 @cindex export, and import
887 @findex ses-export-tsv
888 @findex ses-export-tsf
892 Export a range of cells as tab-separated values (@code{ses-export-tsv}).
894 Export a range of cells as tab-separated formulas (@code{ses-export-tsf}).
897 The exported text goes to the kill ring; you can paste it into
898 another buffer. Columns are separated by tabs, rows by newlines.
900 To import text, use any of the yank commands where the text to paste
901 contains tabs and/or newlines. Imported formulas are not relocated.
903 @node Virus protection
904 @section Virus protection
905 @cindex virus protection
907 Whenever a formula or printer is read from a file or is pasted into
908 the spreadsheet, it receives a ``needs safety check'' marking. Later,
909 when the formula or printer is evaluated for the first time, it is
910 checked for safety using the @code{unsafep} predicate; if found to be
911 ``possibly unsafe'', the questionable formula or printer is displayed
912 and you must press Y to approve it or N to use a substitute. The
913 substitute always signals an error.
915 Formulas or printers that you type in are checked immediately for
916 safety. If found to be possibly unsafe and you press N to disapprove,
917 the action is canceled and the old formula or printer will remain.
919 Besides viruses (which try to copy themselves to other files),
920 @code{unsafep} can also detect all other kinds of Trojan horses, such as
921 spreadsheets that delete files, send email, flood Web sites, alter
922 your Emacs settings, etc.
924 Generally, spreadsheet formulas and printers are simple things that
925 don't need to do any fancy computing, so all potentially-dangerous
926 parts of the Emacs Lisp environment can be excluded without cramping
927 your style as a formula-writer. See the documentation in @file{unsafep.el}
928 for more info on how Lisp forms are classified as safe or unsafe.
930 @node Spreadsheets with details and summary
931 @section Spreadsheets with details and summary
932 @cindex details and summary
933 @cindex summary, and details
935 A common organization for spreadsheets is to have a bunch of ``detail''
936 rows, each perhaps describing a transaction, and then a set of
937 ``summary'' rows that each show reduced data for some subset of the
938 details. @acronym{SES} supports this organization via the @code{ses-select}
942 @item (ses-select @var{fromrange} @var{test} @var{torange})
943 Returns a subset of @var{torange}. For each member in @var{fromrange}
944 that is equal to @var{test}, the corresponding member of @var{torange}
945 is included in the result.
950 (ses-average (ses-select (ses-range A1 A5) 'Smith (ses-range B1 B5)))
952 This computes the average of the B column values for those rows whose
953 A column value is the symbol 'Smith.
955 Arguably one could specify only @var{fromrange} plus
956 @var{to-row-offset} and @var{to-column-offset}. The @var{torange} is
957 stated explicitly to ensure that the formula will be recalculated if
958 any cell in either range is changed.
960 File @file{etc/ses-example.el} in the Emacs distribution is an example of a
961 details-and-summary spreadsheet.
964 @c ===================================================================
968 @cindex advanced features
972 * Nonrelocatable references::
974 * Buffer-local variables in spreadsheets::
975 * Uses of defadvice in @acronym{SES}::
978 @node Deferred updates
979 @section Deferred updates
980 @cindex deferred updates
981 @cindex updates, deferred
982 @vindex run-with-idle-timer
984 To save time by avoiding redundant computations, cells that need
985 recalculation due to changes in other cells are added to a set. At
986 the end of the command, each cell in the set is recalculated once.
987 This can create a new set of cells that need recalculation. The
988 process is repeated until either the set is empty or it stops changing
989 (due to circular references among the cells). In extreme cases, you
990 might see progress messages of the form ``Recalculating... (@var{nnn}
991 cells left)''. If you interrupt the calculation using @kbd{C-g}, the
992 spreadsheet will be left in an inconsistent state, so use @kbd{C-_} or
993 @kbd{C-c C-l} to fix it.
995 To save even more time by avoiding redundant writes, cells that have
996 changes are added to a set instead of being written immediately to the
997 data area. Each cell in the set is written once, at the end of the
998 command. If you change vast quantities of cells, you might see a
999 progress message of the form ``Writing... (@var{nnn} cells left)''.
1000 These deferred cell-writes cannot be interrupted by @kbd{C-g}, so
1001 you'll just have to wait.
1003 @acronym{SES} uses @code{run-with-idle-timer} to move the cell underline when
1004 Emacs will be scrolling the buffer after the end of a command, and
1005 also to narrow and underline after @kbd{C-x C-v}. This is visible as
1006 a momentary glitch after C-x C-v and certain scrolling commands. You
1007 can type ahead without worrying about the glitch.
1010 @node Nonrelocatable references
1011 @section Nonrelocatable references
1012 @cindex nonrelocatable references
1013 @cindex references, nonrelocatable
1015 @kbd{C-y} relocates all cell-references in a pasted formula, while
1016 @kbd{C-u C-y} relocates none of the cell-references. What about mixed
1023 to make an @dfn{absolute reference}. The formula relocator skips over
1024 quoted things, so this will not be relocated when pasted or when
1025 rows/columns are inserted/deleted. However, B3 will not be recorded
1026 as a dependency of this cell, so this cell will not be updated
1027 automatically when B3 is changed.
1029 The variables @code{row} and @code{col} are dynamically bound while a
1030 cell formula is being evaluated. You can use
1032 (ses-cell-value row 0)
1034 to get the value from the leftmost column in the current row. This
1035 kind of dependency is also not recorded.
1039 @section The data area
1041 @findex ses-reconstruct-all
1043 Begins with an 014 character, followed by sets of cell-definition
1044 macros for each row, followed by column-widths, column-printers,
1045 default-printer, and header-row. Then there's the global parameters
1046 (file-format ID, numrows, numcols) and the local variables (specifying
1047 @acronym{SES} mode for the buffer, etc.).
1049 When a @acronym{SES} file is loaded, first the numrows and numcols values are
1050 loaded, then the entire data area is @code{eval}ed, and finally the local
1051 variables are processed.
1053 You can edit the data area, but don't insert or delete any newlines
1054 except in the local-variables part, since @acronym{SES} locates things by
1055 counting newlines. Use @kbd{C-x C-e} at the end of a line to install
1056 your edits into the spreadsheet data structures (this does not update
1057 the print area, use, e.g., @kbd{C-c C-l} for that).
1059 The data area is maintained as an image of spreadsheet data
1060 structures that area stored in buffer-local variables. If the data
1061 area gets messed up, you can try reconstructing the data area from the
1066 (@code{ses-reconstruct-all}).
1070 @node Buffer-local variables in spreadsheets
1071 @section Buffer-local variables in spreadsheets
1072 @cindex buffer-local variables
1073 @cindex variables, buffer-local
1075 You can add additional local variables to the list at the bottom of
1076 the data area, such as hidden constants you want to refer to in your
1079 You can override the variable @code{ses--symbolic-formulas} to be a list of
1080 symbols (as parenthesized strings) to show as completions for the @kbd{'}
1081 command. This initial completions list is used instead of the actual
1082 set of symbols-as-formulas in the spreadsheet.
1084 For an example of this, see file @file{etc/ses-example.ses}.
1086 If (for some reason) you want your formulas or printers to save data
1087 into variables, you must declare these variables as buffer-locals in
1088 order to avoid a virus warning.
1090 You can define functions by making them values for the fake local
1091 variable @code{eval}. Such functions can then be used in your
1092 formulas and printers, but usually each @code{eval} is presented to
1093 the user during file loading as a potential virus. This can get
1096 You can define functions in your @file{.emacs} file. Other people can
1097 still read the print area of your spreadsheet, but they won't be able
1098 to recalculate or reprint anything that depends on your functions. To
1099 avoid virus warnings, each function used in a formula needs
1101 (put 'your-function-name 'safe-function t)
1104 @node Uses of defadvice in @acronym{SES}
1105 @section Uses of defadvice in @acronym{SES}
1108 @cindex copy-region-as-kill
1113 Defines a new undo element format (@var{fun} . @var{args}), which
1114 means ``undo by applying @var{fun} to @var{args}''. For spreadsheet
1115 buffers, it allows undos in the data area even though that's outside
1118 @item copy-region-as-kill
1119 When copying from the print area of a spreadsheet, treat the region as
1120 a rectangle and attach each cell's formula and printer as 'ses
1124 When yanking into the print area of a spreadsheet, first try to yank
1125 as cells (if the yank text has 'ses properties), then as tab-separated
1126 formulas, then (if all else fails) as a single formula for the current
1130 @c ===================================================================
1136 @c ===================================================================
1138 @node Acknowledgments
1139 @unnumbered Acknowledgments
1143 @c jyavner@@member.fsf.org
1147 @c shigeru.fukaya@@gmail.com
1154 @c jyavner@@member.fsf.org
1163 @c christoph.conrad@@gmx.de
1165 @c cyberbob@@redneck.gacracker.org
1167 @c syver-en@@online.no
1169 @c fischman@@zion.bpnetworks.com
1171 @c Thomas.Gehrlein@@t-online.de
1173 @c c.f.a.johnson@@rogers.com
1175 @c lyusong@@hotmail.com
1179 @c maierh@@myself.com
1181 @c anash@@san.rr.com
1183 @c pinard@@iro.umontreal.ca
1185 @c ppinto@@cs.cmu.edu
1189 @c epameinondas@@gmx.de
1192 Richard M. Stallman,
1193 @c teirllm@@dms.auburn.edu
1197 @c jphil@@acs.pagesjaunes.fr
1198 Jean-Philippe Theberge
1201 @c ===================================================================
1203 @node GNU Free Documentation License
1204 @appendix GNU Free Documentation License
1205 @include doclicense.texi