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.
448 @section Clearing cells
449 @cindex clearing commands
450 @findex ses-clear-cell-backward
451 @findex ses-clear-cell-forward
453 These commands set both formula and printer to @code{nil}:
457 Clear cell and move left (@code{ses-clear-cell-backward}).
460 Clear cell and move right (@code{ses-clear-cell-forward}).
465 @section Copy, cut, and paste
469 @findex kill-ring-save
470 @findex mouse-set-region
471 @findex mouse-set-secondary
472 @findex ses-kill-override
474 @findex clipboard-yank
475 @findex mouse-yank-at-click
476 @findex mouse-yank-at-secondary
479 The copy functions work on rectangular regions of cells. You can paste the
480 copies into non-@acronym{SES} buffers to export the print text.
486 Copy the highlighted cells to kill ring and primary clipboard
487 (@code{kill-ring-save}).
490 Mark a region and copy it to kill ring and primary clipboard
491 (@code{mouse-set-region}).
493 @item [M-drag-mouse-1]
494 Mark a region and copy it to kill ring and secondary clipboard
495 (@code{mouse-set-secondary}).
500 The cut functions do not actually delete rows or columns---they copy
501 and then clear (@code{ses-kill-override}).
505 Paste from kill ring (@code{yank}). The paste functions behave
506 differently depending on the format of the text being inserted:
509 When pasting cells that were cut from a @acronym{SES} buffer, the print text is
510 ignored and only the attached formula and printer are inserted; cell
511 references in the formula are relocated unless you use @kbd{C-u}.
513 The pasted text overwrites a rectangle of cells whose top left corner
514 is the current cell. If part of the rectangle is beyond the edges of
515 the spreadsheet, you must confirm the increase in spreadsheet size.
517 Non-@acronym{SES} text is usually inserted as a replacement formula for the
518 current cell. If the formula would be a symbol, it's treated as a
519 string unless you use @kbd{C-u}. Pasted formulas with syntax errors
520 are always treated as strings.
524 Paste from primary clipboard or kill ring (@code{clipboard-yank}).
527 Set point and paste from primary clipboard (@code{mouse-yank-at-click}).
530 Set point and paste from secondary clipboard (@code{mouse-yank-secondary}).
533 Immediately after a paste, you can replace the text with a preceding
534 element from the kill ring (@code{ses-yank-pop}). Unlike the standard
535 Emacs yank-pop, the @acronym{SES} version uses @code{undo} to delete the old
536 yank. This doesn't make any difference?
539 @node Customizing @acronym{SES}
540 @section Customizing @acronym{SES}
542 @vindex enable-local-eval
543 @vindex ses-mode-hook
544 @vindex safe-functions
545 @vindex enable-local-eval
548 By default, a newly-created spreadsheet has 1 row and 1 column. The
549 column width is 7 and the default printer is @samp{"%.7g"}. Each of these
550 can be customized. Look in group ``ses''.
552 After entering a cell value, point normally moves right to the next
553 cell. You can customize @code{ses-after-entry-functions} to move left or
554 up or down. For diagonal movement, select two functions from the
557 @code{ses-mode-hook} is a normal mode hook (list of functions to
558 execute when starting @acronym{SES} mode for a buffer).
560 The variable @code{safe-functions} is a list of possibly-unsafe
561 functions to be treated as safe when analyzing formulas and printers.
562 @xref{Virus protection}. Before customizing @code{safe-functions},
563 think about how much you trust the person who's suggesting this
564 change. The value @code{t} turns off all anti-virus protection. A
565 list-of-functions value might enable a ``gee whiz'' spreadsheet, but it
566 also creates trapdoors in your anti-virus armor. In order for virus
567 protection to work, you must always press @kbd{n} when presented with
568 a virus warning, unless you understand what the questionable code is
569 trying to do. Do not listen to those who tell you to customize
570 @code{enable-local-eval}---this variable is for people who don't wear
574 @c ===================================================================
576 @node Advanced Features
577 @chapter Advanced Features
578 @cindex advanced features
579 @findex ses-read-header-row
584 (@code{ses-set-header-row}).
585 @findex ses-set-header-row
587 The header line at the top of the @acronym{SES}
588 window normally shows the column letter for each column. You can set
589 it to show a copy of some row, such as a row of column titles, so that
590 row will always be visible. Default is to set the current row as the
591 header; use C-u to prompt for header row. Set the header to row 0 to
592 show column letters again.
593 @item [header-line mouse-3]
594 Pops up a menu to set the current row as the header, or revert to
596 @item M-x ses-rename-cell
597 @findex ses-rename-cell
598 Rename a cell from a standard A1-like name to any
600 @item M-x ses-repair-cell-reference-all
601 @findex ses-repair-cell-reference-all
602 When you interrupt a cell formula update by clicking @kbd{C-g}, then
603 the cell reference link may be broken, which will jeopardize automatic
604 cell update when any other cell on which it depends is changed. To
605 repair that use function @code{ses-repair-cell-reference-all}
610 * Ranges in formulas::
611 * Sorting by column::
612 * Standard formula functions::
613 * More on cell printing::
614 * Import and export::
616 * Spreadsheets with details and summary::
620 @section The print area
623 @findex ses-renarrow-buffer
624 @findex ses-reprint-all
626 A @acronym{SES} file consists of a print area and a data area. Normally the
627 buffer is narrowed to show only the print area. The print area is
628 read-only except for special @acronym{SES} commands; it contains cell values
629 formatted by printer functions. The data area records the formula and
630 printer functions, etc.
634 Show print and data areas (@code{widen}).
637 Show only print area (@code{ses-renarrow-buffer}).
641 Recreate print area by reevaluating printer functions for all cells
642 (@code{ses-reprint-all}).
645 @node Ranges in formulas
646 @section Ranges in formulas
648 @findex ses-insert-range-click
649 @findex ses-insert-range
650 @findex ses-insert-ses-range-click
651 @findex ses-insert-ses-range
659 is the sum of three specific cells. If you insert a new second row,
664 and the new row is not included in the sum.
666 The macro @code{(ses-range @var{from} @var{to})} evaluates to a list of
667 the values in a rectangle of cells. If your formula is
669 (apply '+ (ses-range A1 A3))
671 and you insert a new second row, it becomes
673 (apply '+ (ses-range A1 A4))
675 and the new row is included in the sum.
677 While entering or editing a formula in the minibuffer, you can select
678 a range in the spreadsheet (using mouse or keyboard), then paste a
679 representation of that range into your formula. Suppose you select
684 Inserts "A1 B1 C1" @code{(ses-insert-range-click})
687 Keyboard version (@code{ses-insert-range}).
690 Inserts "(ses-range A1 C1)" (@code{ses-insert-ses-range-click}).
693 Keyboard version (@code{ses-insert-ses-range}).
696 If you delete the @var{from} or @var{to} cell for a range, the nearest
697 still-existing cell is used instead. If you delete the entire range,
698 the formula relocator will delete the ses-range from the formula.
700 If you insert a new row just beyond the end of a one-column range, or
701 a new column just beyond a one-row range, the new cell is included in
702 the range. New cells inserted just before a range are not included.
704 Flags can be added to @code{ses-range} immediately after the @var{to}
708 Empty cells in range can be removed by adding the @code{!} flag. An
709 empty cell is a cell the value of which is one of symbols @code{nil}
710 or @code{*skip*}. For instance @code{(ses-range A1 A4 !)} will do the
711 same as @code{(list A1 A3)} when cells @code{A2} and @code{A4} are
714 Empty cell values are replaced by the argument following flag
715 @code{_}, or @code{0} when flag @code{_} is last in argument list. For
716 instance @code{(ses-range A1 A4 _ "empty")} will do the same as
717 @code{(list A1 "empty" A3 "empty")} when cells @code{A2} and @code{A4}
718 are empty. Similarly, @code{(ses-range A1 A4 _ )} will do the same as
719 @code{(list A1 0 A3 0)}.
721 When order matters, list cells by reading cells row-wise from top left
722 to bottom right. This flag is provided for completeness only as it is
723 the default reading order.
725 List cells by reading cells row-wise from top right to bottom left.
727 List cells by reading cells column-wise from top left to bottom right.
729 List cells by reading cells column-wise from top right to bottom left.
731 A short hand for @code{v>}.
733 A short hand for @code{^>}.
735 A short hand for @code{>v}.
737 A short hand for @code{>^}.
739 Instead of listing cells, it makes a Calc vector or matrix of it
740 (@pxref{Top,,,calc,GNU Emacs Calc Manual}). If the range contains only
741 one row or one column a vector is made, otherwise a matrix is made.
743 Same as @code{*} except that a matrix is always made even when there
744 is only one row or column in the range.
746 Same as @code{*} except that a vector is always made even when there
747 is only one row or column in the range, that is to say the
748 corresponding matrix is flattened.
751 @node Sorting by column
752 @section Sorting by column
754 @findex ses-sort-column
755 @findex ses-sort-column-click
759 Sort the cells of a range using one of the columns
760 (@code{ses-sort-column}). The rows (or partial rows if the range
761 doesn't include all columns) are rearranged so the chosen column will
764 @item [header-line mouse-2]
765 The easiest way to sort is to click mouse-2 on the chosen column's header row
766 (@code{ses-sort-column-click}).
769 The sort comparison uses @code{string<}, which works well for
770 right-justified numbers and left-justified strings.
772 With prefix arg, sort is in descending order.
774 Rows are moved one at a time, with relocation of formulas. This works
775 well if formulas refer to other cells in their row, not so well for
776 formulas that refer to other rows in the range or to cells outside the
780 @node Standard formula functions
781 @section Standard formula functions
782 @cindex standard formula functions
785 @findex ses-delete-blanks
789 Oftentimes you want a calculation to exclude the blank cells. Here
790 are some useful functions to call from your formulas:
793 @item (ses-delete-blanks &rest @var{args})
794 Returns a list from which all blank cells (value is either @code{nil} or
795 '*skip*) have been deleted.
797 @item (ses+ &rest @var{args})
798 Sum of non-blank arguments.
800 @item (ses-average @var{list})
801 Average of non-blank elements in @var{list}. Here the list is passed
802 as a single argument, since you'll probably use it with @code{ses-range}.
805 @node More on cell printing
806 @section More on cell printing
807 @cindex cell printing, more
808 @findex ses-truncate-cell
809 @findex ses-recalculate-cell
813 @item nil prints the same as "", but allows previous cell to spill over.
814 @item '*skip* replaces nil when the previous cell actually does spill over;
815 nothing is printed for it.
816 @item '*error* indicates that the formula signaled an error instead of
817 producing a value: the print cell is filled with hash marks (#).
820 If the result from the printer function is too wide for the cell and
821 the following cell is @code{nil}, the result will spill over into the
822 following cell. Very wide results can spill over several cells. If
823 the result is too wide for the available space (up to the end of the
824 row or the next non-@code{nil} cell), the result is truncated if the cell's
825 value is a string, or replaced with hash marks otherwise.
827 @acronym{SES} could get confused by printer results that contain newlines or
828 tabs, so these are replaced with question marks.
832 Confine a cell to its own column (@code{ses-truncate-cell}). This
833 allows you to move point to a rightward cell that would otherwise be
834 covered by a spill-over. If you don't change the rightward cell, the
835 confined cell will spill over again the next time it is reprinted.
838 When applied to a single cell, this command displays in the echo area
839 any formula error or printer error that occurred during
840 recalculation/reprinting (@code{ses-recalculate-cell}). You can use
841 this to undo the effect of @kbd{t}.
844 When a printer function signals an error, the fallback printer
845 @samp{"%s"} is substituted. This is useful when your column printer
846 is numeric-only and you use a string as a cell value. Note that the
847 standard default printer is ``%.7g'' which is numeric-only, so cells
848 that are empty of contain strings will use the fallback printer.
849 @kbd{c} on such cells will display ``Format specifier doesn't match
853 @node Import and export
854 @section Import and export
855 @cindex import and export
856 @cindex export, and import
857 @findex ses-export-tsv
858 @findex ses-export-tsf
862 Export a range of cells as tab-separated values (@code{ses-export-tsv}).
864 Export a range of cells as tab-separated formulas (@code{ses-export-tsf}).
867 The exported text goes to the kill ring; you can paste it into
868 another buffer. Columns are separated by tabs, rows by newlines.
870 To import text, use any of the yank commands where the text to paste
871 contains tabs and/or newlines. Imported formulas are not relocated.
873 @node Virus protection
874 @section Virus protection
875 @cindex virus protection
877 Whenever a formula or printer is read from a file or is pasted into
878 the spreadsheet, it receives a ``needs safety check'' marking. Later,
879 when the formula or printer is evaluated for the first time, it is
880 checked for safety using the @code{unsafep} predicate; if found to be
881 ``possibly unsafe'', the questionable formula or printer is displayed
882 and you must press Y to approve it or N to use a substitute. The
883 substitute always signals an error.
885 Formulas or printers that you type in are checked immediately for
886 safety. If found to be possibly unsafe and you press N to disapprove,
887 the action is canceled and the old formula or printer will remain.
889 Besides viruses (which try to copy themselves to other files),
890 @code{unsafep} can also detect all other kinds of Trojan horses, such as
891 spreadsheets that delete files, send email, flood Web sites, alter
892 your Emacs settings, etc.
894 Generally, spreadsheet formulas and printers are simple things that
895 don't need to do any fancy computing, so all potentially-dangerous
896 parts of the Emacs Lisp environment can be excluded without cramping
897 your style as a formula-writer. See the documentation in @file{unsafep.el}
898 for more info on how Lisp forms are classified as safe or unsafe.
900 @node Spreadsheets with details and summary
901 @section Spreadsheets with details and summary
902 @cindex details and summary
903 @cindex summary, and details
905 A common organization for spreadsheets is to have a bunch of ``detail''
906 rows, each perhaps describing a transaction, and then a set of
907 ``summary'' rows that each show reduced data for some subset of the
908 details. @acronym{SES} supports this organization via the @code{ses-select}
912 @item (ses-select @var{fromrange} @var{test} @var{torange})
913 Returns a subset of @var{torange}. For each member in @var{fromrange}
914 that is equal to @var{test}, the corresponding member of @var{torange}
915 is included in the result.
920 (ses-average (ses-select (ses-range A1 A5) 'Smith (ses-range B1 B5)))
922 This computes the average of the B column values for those rows whose
923 A column value is the symbol 'Smith.
925 Arguably one could specify only @var{fromrange} plus
926 @var{to-row-offset} and @var{to-column-offset}. The @var{torange} is
927 stated explicitly to ensure that the formula will be recalculated if
928 any cell in either range is changed.
930 File @file{etc/ses-example.el} in the Emacs distribution is an example of a
931 details-and-summary spreadsheet.
934 @c ===================================================================
938 @cindex advanced features
942 * Nonrelocatable references::
944 * Buffer-local variables in spreadsheets::
945 * Uses of defadvice in @acronym{SES}::
948 @node Deferred updates
949 @section Deferred updates
950 @cindex deferred updates
951 @cindex updates, deferred
952 @vindex run-with-idle-timer
954 To save time by avoiding redundant computations, cells that need
955 recalculation due to changes in other cells are added to a set. At
956 the end of the command, each cell in the set is recalculated once.
957 This can create a new set of cells that need recalculation. The
958 process is repeated until either the set is empty or it stops changing
959 (due to circular references among the cells). In extreme cases, you
960 might see progress messages of the form ``Recalculating... (@var{nnn}
961 cells left)''. If you interrupt the calculation using @kbd{C-g}, the
962 spreadsheet will be left in an inconsistent state, so use @kbd{C-_} or
963 @kbd{C-c C-l} to fix it.
965 To save even more time by avoiding redundant writes, cells that have
966 changes are added to a set instead of being written immediately to the
967 data area. Each cell in the set is written once, at the end of the
968 command. If you change vast quantities of cells, you might see a
969 progress message of the form ``Writing... (@var{nnn} cells left)''.
970 These deferred cell-writes cannot be interrupted by @kbd{C-g}, so
971 you'll just have to wait.
973 @acronym{SES} uses @code{run-with-idle-timer} to move the cell underline when
974 Emacs will be scrolling the buffer after the end of a command, and
975 also to narrow and underline after @kbd{C-x C-v}. This is visible as
976 a momentary glitch after C-x C-v and certain scrolling commands. You
977 can type ahead without worrying about the glitch.
980 @node Nonrelocatable references
981 @section Nonrelocatable references
982 @cindex nonrelocatable references
983 @cindex references, nonrelocatable
985 @kbd{C-y} relocates all cell-references in a pasted formula, while
986 @kbd{C-u C-y} relocates none of the cell-references. What about mixed
993 to make an @dfn{absolute reference}. The formula relocator skips over
994 quoted things, so this will not be relocated when pasted or when
995 rows/columns are inserted/deleted. However, B3 will not be recorded
996 as a dependency of this cell, so this cell will not be updated
997 automatically when B3 is changed.
999 The variables @code{row} and @code{col} are dynamically bound while a
1000 cell formula is being evaluated. You can use
1002 (ses-cell-value row 0)
1004 to get the value from the leftmost column in the current row. This
1005 kind of dependency is also not recorded.
1009 @section The data area
1011 @findex ses-reconstruct-all
1013 Begins with an 014 character, followed by sets of cell-definition
1014 macros for each row, followed by column-widths, column-printers,
1015 default-printer, and header-row. Then there's the global parameters
1016 (file-format ID, numrows, numcols) and the local variables (specifying
1017 @acronym{SES} mode for the buffer, etc.).
1019 When a @acronym{SES} file is loaded, first the numrows and numcols values are
1020 loaded, then the entire data area is @code{eval}ed, and finally the local
1021 variables are processed.
1023 You can edit the data area, but don't insert or delete any newlines
1024 except in the local-variables part, since @acronym{SES} locates things by
1025 counting newlines. Use @kbd{C-x C-e} at the end of a line to install
1026 your edits into the spreadsheet data structures (this does not update
1027 the print area, use, e.g., @kbd{C-c C-l} for that).
1029 The data area is maintained as an image of spreadsheet data
1030 structures that area stored in buffer-local variables. If the data
1031 area gets messed up, you can try reconstructing the data area from the
1036 (@code{ses-reconstruct-all}).
1040 @node Buffer-local variables in spreadsheets
1041 @section Buffer-local variables in spreadsheets
1042 @cindex buffer-local variables
1043 @cindex variables, buffer-local
1045 You can add additional local variables to the list at the bottom of
1046 the data area, such as hidden constants you want to refer to in your
1049 You can override the variable @code{ses--symbolic-formulas} to be a list of
1050 symbols (as parenthesized strings) to show as completions for the @kbd{'}
1051 command. This initial completions list is used instead of the actual
1052 set of symbols-as-formulas in the spreadsheet.
1054 For an example of this, see file @file{etc/ses-example.ses}.
1056 If (for some reason) you want your formulas or printers to save data
1057 into variables, you must declare these variables as buffer-locals in
1058 order to avoid a virus warning.
1060 You can define functions by making them values for the fake local
1061 variable @code{eval}. Such functions can then be used in your
1062 formulas and printers, but usually each @code{eval} is presented to
1063 the user during file loading as a potential virus. This can get
1066 You can define functions in your @file{.emacs} file. Other people can
1067 still read the print area of your spreadsheet, but they won't be able
1068 to recalculate or reprint anything that depends on your functions. To
1069 avoid virus warnings, each function used in a formula needs
1071 (put 'your-function-name 'safe-function t)
1074 @node Uses of defadvice in @acronym{SES}
1075 @section Uses of defadvice in @acronym{SES}
1078 @cindex copy-region-as-kill
1083 Defines a new undo element format (@var{fun} . @var{args}), which
1084 means ``undo by applying @var{fun} to @var{args}''. For spreadsheet
1085 buffers, it allows undos in the data area even though that's outside
1088 @item copy-region-as-kill
1089 When copying from the print area of a spreadsheet, treat the region as
1090 a rectangle and attach each cell's formula and printer as 'ses
1094 When yanking into the print area of a spreadsheet, first try to yank
1095 as cells (if the yank text has 'ses properties), then as tab-separated
1096 formulas, then (if all else fails) as a single formula for the current
1100 @c ===================================================================
1106 @c ===================================================================
1108 @node Acknowledgments
1109 @unnumbered Acknowledgments
1113 @c jyavner@@member.fsf.org
1117 @c shigeru.fukaya@@gmail.com
1124 @c jyavner@@member.fsf.org
1133 @c christoph.conrad@@gmx.de
1135 @c cyberbob@@redneck.gacracker.org
1137 @c syver-en@@online.no
1139 @c fischman@@zion.bpnetworks.com
1141 @c Thomas.Gehrlein@@t-online.de
1143 @c c.f.a.johnson@@rogers.com
1145 @c lyusong@@hotmail.com
1149 @c maierh@@myself.com
1151 @c anash@@san.rr.com
1153 @c pinard@@iro.umontreal.ca
1155 @c ppinto@@cs.cmu.edu
1159 @c epameinondas@@gmx.de
1162 Richard M. Stallman,
1163 @c teirllm@@dms.auburn.edu
1167 @c jphil@@acs.pagesjaunes.fr
1168 Jean-Philippe Theberge
1171 @c ===================================================================
1173 @node GNU Free Documentation License
1174 @appendix GNU Free Documentation License
1175 @include doclicense.texi