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--2017 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 Alternatively you can use the @code{!} modifier of @code{ses-range} to
191 remove blank cells from the returned list, which allows to use
192 @code{+} instead of @code{ses+}:
195 (apply '+ (ses-range A2 A5 !))
198 @c ===================================================================
201 @comment node-name, next, previous, up
203 @cindex basic commands
206 @findex ses-mark-column
207 @findex ses-mark-whole-buffer
208 @findex set-mark-command
209 @findex keyboard-quit
211 To create a new spreadsheet, visit a nonexistent file whose name ends
212 with ".ses". For example, @kbd{C-x C-f test.ses RET}.
215 A @dfn{cell identifier} is a symbol with a column letter and a row
216 number. Cell B7 is the 2nd column of the 7th row. For very wide
217 spreadsheets, there are two column letters: cell AB7 is the 28th
218 column of the 7th row. Super wide spreadsheets get AAA1, etc.
219 You move around with the regular Emacs movement commands.
223 Moves point to cell, specified by identifier (@code{ses-jump}).
226 Point is always at the left edge of a cell, or at the empty endline.
227 When mark is inactive, the current cell is underlined. When mark is
228 active, the range is the highlighted rectangle of cells (@acronym{SES} always
229 uses transient mark mode). Drag the mouse from A1 to A3 to create the
230 range A1-A2. Many @acronym{SES} commands operate only on single cells, not
236 Set mark at point (@code{set-mark-command}).
239 Turn off the mark (@code{keyboard-quit}).
242 Highlight current row (@code{ses-mark-row}).
245 Highlight current column (@code{ses-mark-column}).
248 Highlight all cells (@code{mark-whole-buffer}).
254 * Printer functions::
257 * Customizing @acronym{SES}::
261 @section Cell formulas
263 @cindex formulas, entering
266 @cindex editing cells
267 @findex ses-read-cell
268 @findex ses-read-symbol
269 @findex ses-edit-cell
270 @findex ses-recalculate-cell
271 @findex ses-recalculate-all
273 To insert a value into a cell, simply type a numeric expression,
274 @samp{"double-quoted text"}, or a Lisp expression.
278 Self-insert a digit (@code{ses-read-cell}).
281 Self-insert a negative number (@code{ses-read-cell}).
284 Self-insert a fractional number (@code{ses-read-cell}).
287 Self-insert a quoted string. The ending double-quote
288 is inserted for you (@code{ses-read-cell}).
291 Self-insert an expression. The right-parenthesis is inserted for you
292 (@code{ses-read-cell}). To access another cell's value, just use its
293 identifier in your expression. Whenever the other cell is changed,
294 this cell's formula will be reevaluated. While typing in the
295 expression, you can use @kbd{M-@key{TAB}} to complete symbol names.
297 @item ' @r{(apostrophe)}
298 Enter a symbol (ses-read-symbol). @acronym{SES} remembers all symbols that have
299 been used as formulas, so you can type just the beginning of a symbol
300 and use @kbd{@key{SPC}}, @kbd{@key{TAB}}, and @kbd{?} to complete it.
303 To enter something else (e.g., a vector), begin with a digit, then
304 erase the digit and type whatever you want.
308 Edit the existing formula in the current cell (@code{ses-edit-cell}).
311 Force recalculation of the current cell or range (@code{ses-recalculate-cell}).
314 Recalculate the entire spreadsheet (@code{ses-recalculate-all}).
318 @section Resizing the spreadsheet
319 @cindex resizing spreadsheets
321 @cindex row, adding or removing
322 @cindex column, adding or removing
323 @cindex adding rows or columns
324 @cindex inserting rows or columns
325 @cindex removing rows or columns
326 @cindex deleting rows or columns
327 @findex ses-insert-row
328 @findex ses-insert-column
329 @findex ses-delete-row
330 @findex ses-delete-column
331 @findex ses-set-column-width
332 @findex ses-forward-or-insert
333 @findex ses-append-row-jump-first-column
340 (@code{ses-insert-row})
343 (@code{ses-insert-column})
346 (@code{ses-delete-row})
349 (@code{ses-delete-column})
352 (@code{ses-set-column-width})
355 Moves point to the next rightward cell, or inserts a new column if
356 already at last cell on line, or inserts a new row if at endline
357 (@code{ses-forward-or-insert}).
360 Linefeed inserts below the current row and moves to column A
361 (@code{ses-append-row-jump-first-column}).
364 Resizing the spreadsheet (unless you're just changing a column width)
365 relocates all the cell-references in formulas so they still refer to
366 the same cells. If a formula mentioned B1 and you insert a new first
367 row, the formula will now mention B2.
369 If you delete a cell that a formula refers to, the cell-symbol is
370 deleted from the formula, so @code{(+ A1 B1 C1)} after deleting the third
371 column becomes @code{(+ A1 B1)}. In case this is not what you wanted:
376 Undo previous action (@code{(undo)}).
380 @node Printer functions
381 @section Printer functions
382 @cindex printer functions
383 @cindex cell formatting
384 @cindex formatting cells
386 Printer functions convert binary cell values into the print forms that
387 Emacs will display on the screen.
390 * Various kinds of printer functions::
391 * Configuring what printer function applies::
392 * Standard printer functions::
393 * Local printer functions::
394 * Writing a lambda printer function::
397 @node Various kinds of printer functions
398 @subsection Various kinds of printer functions
400 When configuring what printer function applies (@pxref{Configuring
401 what printer function applies}), you can enter a printer function as
402 one of the following:
406 A format string, like @samp{"$%.2f"}. The result
407 string is right-aligned within the print cell. To get left-alignment,
408 use parentheses: @samp{("$%.2f")}.
410 A printer can also be a one-argument function, the result of which is
411 a string (right-aligned) or list of one string (left-aligned). Such a
412 function can be in turn configured as:
415 A lambda expression, for instance:
421 ((numberp x) (format "%.2f" x))
422 (t (ses-center-span x ?# 'ses-prin1))))
425 While typing in a lambda, you can use @kbd{M-@key{TAB}} to complete
426 the names of symbols.
428 A symbol referring to a standard printer function (@pxref{Standard
431 A symbol referring to a local printer function (@pxref{Local printer
439 @node Configuring what printer function applies
440 @subsection Configuring what printer function applies
442 Each cell has a printer. If @code{nil}, the column-printer for the cell's
443 column is used. If that is also @code{nil}, the default-printer for the
448 @findex ses-read-cell-printer
449 Enter a printer for current cell or range (@code{ses-read-cell-printer}).
452 @findex ses-read-column-printer
453 Enter a printer for the current column (@code{ses-read-column-printer}).
456 @findex ses-read-default-printer
457 Enter the default printer for the spreadsheet
458 (@code{ses-read-default-printer}).
461 The @code{ses-read-@var{xxx}-printer} commands have their own
462 minibuffer history, which is preloaded with the set of all printers
463 used in this spreadsheet, plus the standard printers (@pxref{Standard
464 printer functions}) and the local printers (@pxref{Local printer
467 @node Standard printer functions
468 @subsection Standard printer functions
471 Except for @code{ses-prin1}, the other standard printers are suitable
472 only for cells, not columns or default, because they format the value
473 using the column-printer (or default-printer if @code{nil}) and then
480 @item ses-center-span
481 Centering with spill-over to following blank cells.
484 Centering using dashes (-) instead of spaces.
486 @item ses-dashfill-span
487 Centering with dashes and spill-over.
489 @item ses-tildefill-span
490 Centering with tildes (~) and spill-over.
493 This is the fallback printer, used when calling the configured printer
497 @node Local printer functions
498 @subsection Local printer functions
500 @findex ses-define-local-printer
501 You can define printer function local to a sheet with the command
502 @code{ses-define-local-printer}. For instance, define a printer
503 @samp{foo} to @code{"%.2f"}, and then use symbol @samp{foo} as a
504 printer function. Then, if you call again
505 @code{ses-define-local-printer} on @samp{foo} to redefine it as
506 @code{"%.3f"}, all the cells using printer @samp{foo} will be
507 reprinted accordingly.
509 Sometimes there are local printers that you want to define or
510 re-define automatically every time you open a sheet. For instance
511 imagine that you want to define/re-define automatically a local
512 printer @code{euro} to display a number like an amount of euros, that
513 is to say number @code{3.1} would be displayed as
514 @code{3.10@dmn{}@euro{}}. To do so in any non read-only SES buffer,
515 you can add some code like this to your @file{.emacs} init file:
518 (defun my-ses-mode-hook ()
519 (unless buffer-read-only
520 (ses-define-local-printer
525 ((numberp x) (format "%.2f€" x))
526 (t (ses-center-span x ?# 'ses-prin1)))))))
527 (add-hook 'ses-mode-hook 'my-ses-mode-hook)
530 If you replace command @code{ses-define-local-printer} by function
531 @code{ses-define-if-new-local-printer}
532 @findex ses-define-if-new-local-printer
533 the definition will occur only if a local printer with the same name
534 in not already defined.
537 @node Writing a lambda printer function
538 @subsection Writing a lambda printer function
540 You can write a printer function with a lambda expression taking one
541 argument in two cases:
545 when you configure the printer function applying to a cell or column, or
547 when you define a local printer function with command
548 @code{ses-define-local-printer}.
551 When doing so, please take care that the returned value is a string,
552 or a list containing a string, even when the input argument has an
553 unexpected value. Here is an example:
559 ((and (numberp val) (>= val 0)) (format "%.1f" val))
560 (t (ses-center-span val ?# 'ses-prin1))))
567 When the cell is empty (ie.@: when @code{val} is @code{nil}), print an
568 empty string @code{""}
570 When the cell value is a non negative number, format the the value in
571 fixed-point notation with one decimal after point
573 Otherwise, handle the value as erroneous by printing it as an
574 s-expression (using @code{ses-prin1}), centered and surrounded by
578 Another precaution to take is to avoid stack overflow due to a
579 printer function calling itself indefinitely. This mistake can
580 happen when you use a local printer as a column printer,
581 and this local printer implicitly calls the current column printer, so it
582 will call itself recursively. Imagine for instance that you want to
583 create some local printer @code{=fill} that would center the content
584 of a cell and surround it by equal signs @code{=}, and you do it this
591 (t (ses-center x 0 ?=))))
594 Because @code{=fill} uses the standard printer @code{ses-center} without
595 explicitly passing any printer to it, @code{ses-center} will call the
596 current column printer if any, or the spreadsheet default printer
597 otherwise. So using @code{=fill} as a column printer will result in a
598 stack overflow in this column. SES does not check for that;
599 you just have to be careful. For instance, re-write @code{=fill} like
606 ((stringp x) (ses-center x 0 ?= " %s "))
607 (t (ses-center-span x ?# 'ses-prin1))))
610 The code above applies the @code{=} filling only to strings; it also
611 surrounds the string by one space on each side before filling with
612 @code{=} signs. So the string @samp{Foo} will be displayed like @samp{@w{===
613 Foo ===}} in an 11 character wide column. Anything other than an empty cell
614 or a non-string is displayed as an error by using @code{#} filling.
617 @section Clearing cells
618 @cindex clearing commands
619 @findex ses-clear-cell-backward
620 @findex ses-clear-cell-forward
622 These commands set both formula and printer to @code{nil}:
626 Clear cell and move left (@code{ses-clear-cell-backward}).
629 Clear cell and move right (@code{ses-clear-cell-forward}).
634 @section Copy, cut, and paste
638 @findex kill-ring-save
639 @findex mouse-set-region
640 @findex mouse-set-secondary
641 @findex ses-kill-override
643 @findex clipboard-yank
644 @findex mouse-yank-at-click
645 @findex mouse-yank-at-secondary
648 The copy functions work on rectangular regions of cells. You can paste the
649 copies into non-@acronym{SES} buffers to export the print text.
655 Copy the highlighted cells to kill ring and primary clipboard
656 (@code{kill-ring-save}).
659 Mark a region and copy it to kill ring and primary clipboard
660 (@code{mouse-set-region}).
662 @item [M-drag-mouse-1]
663 Mark a region and copy it to kill ring and secondary clipboard
664 (@code{mouse-set-secondary}).
669 The cut functions do not actually delete rows or columns---they copy
670 and then clear (@code{ses-kill-override}).
674 Paste from kill ring (@code{yank}). The paste functions behave
675 differently depending on the format of the text being inserted:
678 When pasting cells that were cut from a @acronym{SES} buffer, the print text is
679 ignored and only the attached formula and printer are inserted; cell
680 references in the formula are relocated unless you use @kbd{C-u}.
682 The pasted text overwrites a rectangle of cells whose top left corner
683 is the current cell. If part of the rectangle is beyond the edges of
684 the spreadsheet, you must confirm the increase in spreadsheet size.
686 Non-@acronym{SES} text is usually inserted as a replacement formula for the
687 current cell. If the formula would be a symbol, it's treated as a
688 string unless you use @kbd{C-u}. Pasted formulas with syntax errors
689 are always treated as strings.
693 Paste from primary clipboard or kill ring (@code{clipboard-yank}).
696 Set point and paste from primary clipboard (@code{mouse-yank-at-click}).
699 Set point and paste from secondary clipboard (@code{mouse-yank-secondary}).
702 Immediately after a paste, you can replace the text with a preceding
703 element from the kill ring (@code{ses-yank-pop}). Unlike the standard
704 Emacs yank-pop, the @acronym{SES} version uses @code{undo} to delete the old
705 yank. This doesn't make any difference?
708 @node Customizing @acronym{SES}
709 @section Customizing @acronym{SES}
711 @vindex enable-local-eval
712 @vindex ses-mode-hook
713 @vindex safe-functions
714 @vindex enable-local-eval
717 By default, a newly-created spreadsheet has 1 row and 1 column. The
718 column width is 7 and the default printer is @samp{"%.7g"}. Each of these
719 can be customized. Look in group ``ses''.
721 After entering a cell value, point normally moves right to the next
722 cell. You can customize @code{ses-after-entry-functions} to move left or
723 up or down. For diagonal movement, select two functions from the
726 @code{ses-mode-hook} is a normal mode hook (list of functions to
727 execute when starting @acronym{SES} mode for a buffer).
729 The variable @code{safe-functions} is a list of possibly-unsafe
730 functions to be treated as safe when analyzing formulas and printers.
731 @xref{Virus protection}. Before customizing @code{safe-functions},
732 think about how much you trust the person who's suggesting this
733 change. The value @code{t} turns off all anti-virus protection. A
734 list-of-functions value might enable a ``gee whiz'' spreadsheet, but it
735 also creates trapdoors in your anti-virus armor. In order for virus
736 protection to work, you must always press @kbd{n} when presented with
737 a virus warning, unless you understand what the questionable code is
738 trying to do. Do not listen to those who tell you to customize
739 @code{enable-local-eval}---this variable is for people who don't wear
743 @c ===================================================================
745 @node Advanced Features
746 @chapter Advanced Features
747 @cindex advanced features
748 @findex ses-read-header-row
753 (@code{ses-set-header-row}).
754 @findex ses-set-header-row
756 The header line at the top of the @acronym{SES}
757 window normally shows the column letter for each column. You can set
758 it to show a copy of some row, such as a row of column titles, so that
759 row will always be visible. Default is to set the current row as the
760 header; use C-u to prompt for header row. Set the header to row 0 to
761 show column letters again.
762 @item [header-line mouse-3]
763 Pops up a menu to set the current row as the header, or revert to
765 @item M-x ses-rename-cell
766 @findex ses-rename-cell
767 Rename a cell from a standard A1-like name to any string that can be a
768 valid local variable name (See also @ref{Nonrelocatable references}).
769 @item M-x ses-repair-cell-reference-all
770 @findex ses-repair-cell-reference-all
771 When you interrupt a cell formula update by clicking @kbd{C-g}, then
772 the cell reference link may be broken, which will jeopardize automatic
773 cell update when any other cell on which it depends is changed. To
774 repair that use function @code{ses-repair-cell-reference-all}
779 * Ranges in formulas::
780 * Sorting by column::
781 * Standard formula functions::
782 * More on cell printing::
783 * Import and export::
785 * Spreadsheets with details and summary::
789 @section The print area
792 @findex ses-renarrow-buffer
793 @findex ses-reprint-all
795 A @acronym{SES} file consists of a print area and a data area. Normally the
796 buffer is narrowed to show only the print area. The print area is
797 read-only except for special @acronym{SES} commands; it contains cell values
798 formatted by printer functions. The data area records the formula and
799 printer functions, etc.
803 Show print and data areas (@code{widen}).
806 Show only print area (@code{ses-renarrow-buffer}).
810 Recreate print area by reevaluating printer functions for all cells
811 (@code{ses-reprint-all}).
814 @node Ranges in formulas
815 @section Ranges in formulas
817 @findex ses-insert-range-click
818 @findex ses-insert-range
819 @findex ses-insert-ses-range-click
820 @findex ses-insert-ses-range
828 is the sum of three specific cells. If you insert a new second row,
833 and the new row is not included in the sum.
835 The macro @code{(ses-range @var{from} @var{to})} evaluates to a list of
836 the values in a rectangle of cells. If your formula is
838 (apply '+ (ses-range A1 A3))
840 and you insert a new second row, it becomes
842 (apply '+ (ses-range A1 A4))
844 and the new row is included in the sum.
846 While entering or editing a formula in the minibuffer, you can select
847 a range in the spreadsheet (using mouse or keyboard), then paste a
848 representation of that range into your formula. Suppose you select
853 Inserts "A1 B1 C1" @code{(ses-insert-range-click})
856 Keyboard version (@code{ses-insert-range}).
859 Inserts "(ses-range A1 C1)" (@code{ses-insert-ses-range-click}).
862 Keyboard version (@code{ses-insert-ses-range}).
865 If you delete the @var{from} or @var{to} cell for a range, the nearest
866 still-existing cell is used instead. If you delete the entire range,
867 the formula relocator will delete the ses-range from the formula.
869 If you insert a new row just beyond the end of a one-column range, or
870 a new column just beyond a one-row range, the new cell is included in
871 the range. New cells inserted just before a range are not included.
873 Flags can be added to @code{ses-range} immediately after the @var{to}
877 Empty cells in range can be removed by adding the @code{!} flag. An
878 empty cell is a cell the value of which is one of symbols @code{nil}
879 or @code{*skip*}. For instance @code{(ses-range A1 A4 !)} will do the
880 same as @code{(list A1 A3)} when cells @code{A2} and @code{A4} are
883 Empty cell values are replaced by the argument following flag
884 @code{_}, or @code{0} when flag @code{_} is last in argument list. For
885 instance @code{(ses-range A1 A4 _ "empty")} will do the same as
886 @code{(list A1 "empty" A3 "empty")} when cells @code{A2} and @code{A4}
887 are empty. Similarly, @code{(ses-range A1 A4 _ )} will do the same as
888 @code{(list A1 0 A3 0)}.
890 When order matters, list cells by reading cells row-wise from top left
891 to bottom right. This flag is provided for completeness only as it is
892 the default reading order.
894 List cells by reading cells row-wise from top right to bottom left.
896 List cells by reading cells column-wise from top left to bottom right.
898 List cells by reading cells column-wise from top right to bottom left.
900 A short hand for @code{v>}.
902 A short hand for @code{^>}.
904 A short hand for @code{>v}.
906 A short hand for @code{>^}.
908 Instead of listing cells, it makes a Calc vector or matrix of it
909 (@pxref{Top,,,calc,GNU Emacs Calc Manual}). If the range contains only
910 one row or one column a vector is made, otherwise a matrix is made.
912 Same as @code{*} except that a matrix is always made even when there
913 is only one row or column in the range.
915 Same as @code{*} except that a vector is always made even when there
916 is only one row or column in the range, that is to say the
917 corresponding matrix is flattened.
920 @node Sorting by column
921 @section Sorting by column
923 @findex ses-sort-column
924 @findex ses-sort-column-click
928 Sort the cells of a range using one of the columns
929 (@code{ses-sort-column}). The rows (or partial rows if the range
930 doesn't include all columns) are rearranged so the chosen column will
933 @item [header-line mouse-2]
934 The easiest way to sort is to click mouse-2 on the chosen column's header row
935 (@code{ses-sort-column-click}).
938 The sort comparison uses @code{string<}, which works well for
939 right-justified numbers and left-justified strings.
941 With prefix arg, sort is in descending order.
943 Rows are moved one at a time, with relocation of formulas. This works
944 well if formulas refer to other cells in their row, not so well for
945 formulas that refer to other rows in the range or to cells outside the
949 @node Standard formula functions
950 @section Standard formula functions
951 @cindex standard formula functions
954 @findex ses-delete-blanks
958 Oftentimes you want a calculation to exclude the blank cells. Here
959 are some useful functions to call from your formulas:
962 @item (ses-delete-blanks &rest @var{args})
963 Returns a list from which all blank cells (value is either @code{nil}
964 or '*skip*) have been deleted. Order of args is reverted. Please note
965 that @code{ses-range} has a @code{!} modifier that allows to remove
966 blanks, so it is possible to write:
972 (apply 'ses-delete-blanks (ses-range A1 A5 <))
975 @item (ses+ &rest @var{args})
976 Sum of non-blank arguments.
978 @item (ses-average @var{list})
979 Average of non-blank elements in @var{list}. Here the list is passed
980 as a single argument, since you'll probably use it with @code{ses-range}.
983 @node More on cell printing
984 @section More on cell printing
985 @cindex cell printing, more
986 @findex ses-truncate-cell
987 @findex ses-recalculate-cell
991 @item nil prints the same as "", but allows previous cell to spill over.
992 @item '*skip* replaces nil when the previous cell actually does spill over;
993 nothing is printed for it.
994 @item '*error* indicates that the formula signaled an error instead of
995 producing a value: the print cell is filled with hash marks (#).
998 If the result from the printer function is too wide for the cell and
999 the following cell is @code{nil}, the result will spill over into the
1000 following cell. Very wide results can spill over several cells. If
1001 the result is too wide for the available space (up to the end of the
1002 row or the next non-@code{nil} cell), the result is truncated if the cell's
1003 value is a string, or replaced with hash marks otherwise.
1005 @acronym{SES} could get confused by printer results that contain newlines or
1006 tabs, so these are replaced with question marks.
1010 Confine a cell to its own column (@code{ses-truncate-cell}). This
1011 allows you to move point to a rightward cell that would otherwise be
1012 covered by a spill-over. If you don't change the rightward cell, the
1013 confined cell will spill over again the next time it is reprinted.
1016 When applied to a single cell, this command displays in the echo area
1017 any formula error or printer error that occurred during
1018 recalculation/reprinting (@code{ses-recalculate-cell}). You can use
1019 this to undo the effect of @kbd{t}.
1022 When a printer function signals an error, the fallback printer
1024 @code{ses-prin1} is substituted. This is useful when your column printer
1025 is numeric-only and you use a string as a cell value. Note that the
1026 standard default printer is @samp{"%.7g"} which is numeric-only, so cells
1027 that are empty of contain strings will use the fallback printer.
1028 @kbd{c} on such cells will display ``Format specifier doesn't match
1032 @node Import and export
1033 @section Import and export
1034 @cindex import and export
1035 @cindex export, and import
1036 @findex ses-export-tsv
1037 @findex ses-export-tsf
1041 Export a range of cells as tab-separated values (@code{ses-export-tsv}).
1043 Export a range of cells as tab-separated formulas (@code{ses-export-tsf}).
1046 The exported text goes to the kill ring; you can paste it into
1047 another buffer. Columns are separated by tabs, rows by newlines.
1049 To import text, use any of the yank commands where the text to paste
1050 contains tabs and/or newlines. Imported formulas are not relocated.
1052 @node Virus protection
1053 @section Virus protection
1054 @cindex virus protection
1056 Whenever a formula or printer is read from a file or is pasted into
1057 the spreadsheet, it receives a ``needs safety check'' marking. Later,
1058 when the formula or printer is evaluated for the first time, it is
1059 checked for safety using the @code{unsafep} predicate; if found to be
1060 ``possibly unsafe'', the questionable formula or printer is displayed
1061 and you must press Y to approve it or N to use a substitute. The
1062 substitute always signals an error.
1064 Formulas or printers that you type in are checked immediately for
1065 safety. If found to be possibly unsafe and you press N to disapprove,
1066 the action is canceled and the old formula or printer will remain.
1068 Besides viruses (which try to copy themselves to other files),
1069 @code{unsafep} can also detect all other kinds of Trojan horses, such as
1070 spreadsheets that delete files, send email, flood Web sites, alter
1071 your Emacs settings, etc.
1073 Generally, spreadsheet formulas and printers are simple things that
1074 don't need to do any fancy computing, so all potentially-dangerous
1075 parts of the Emacs Lisp environment can be excluded without cramping
1076 your style as a formula-writer. See the documentation in @file{unsafep.el}
1077 for more info on how Lisp forms are classified as safe or unsafe.
1079 @node Spreadsheets with details and summary
1080 @section Spreadsheets with details and summary
1081 @cindex details and summary
1082 @cindex summary, and details
1084 A common organization for spreadsheets is to have a bunch of ``detail''
1085 rows, each perhaps describing a transaction, and then a set of
1086 ``summary'' rows that each show reduced data for some subset of the
1087 details. @acronym{SES} supports this organization via the @code{ses-select}
1091 @item (ses-select @var{fromrange} @var{test} @var{torange})
1092 Returns a subset of @var{torange}. For each member in @var{fromrange}
1093 that is equal to @var{test}, the corresponding member of @var{torange}
1094 is included in the result.
1099 (ses-average (ses-select (ses-range A1 A5) 'Smith (ses-range B1 B5)))
1101 This computes the average of the B column values for those rows whose
1102 A column value is the symbol 'Smith.
1104 Arguably one could specify only @var{fromrange} plus
1105 @var{to-row-offset} and @var{to-column-offset}. The @var{torange} is
1106 stated explicitly to ensure that the formula will be recalculated if
1107 any cell in either range is changed.
1109 File @file{etc/ses-example.el} in the Emacs distribution is an example of a
1110 details-and-summary spreadsheet.
1113 @c ===================================================================
1117 @cindex advanced features
1120 * Deferred updates::
1121 * Nonrelocatable references::
1123 * Buffer-local variables in spreadsheets::
1124 * Uses of defadvice in @acronym{SES}::
1127 @node Deferred updates
1128 @section Deferred updates
1129 @cindex deferred updates
1130 @cindex updates, deferred
1131 @vindex run-with-idle-timer
1133 To save time by avoiding redundant computations, cells that need
1134 recalculation due to changes in other cells are added to a set. At
1135 the end of the command, each cell in the set is recalculated once.
1136 This can create a new set of cells that need recalculation. The
1137 process is repeated until either the set is empty or it stops changing
1138 (due to circular references among the cells). In extreme cases, you
1139 might see progress messages of the form ``Recalculating... (@var{nnn}
1140 cells left)''. If you interrupt the calculation using @kbd{C-g}, the
1141 spreadsheet will be left in an inconsistent state, so use @kbd{C-_} or
1142 @kbd{C-c C-l} to fix it.
1144 To save even more time by avoiding redundant writes, cells that have
1145 changes are added to a set instead of being written immediately to the
1146 data area. Each cell in the set is written once, at the end of the
1147 command. If you change vast quantities of cells, you might see a
1148 progress message of the form ``Writing... (@var{nnn} cells left)''.
1149 These deferred cell-writes cannot be interrupted by @kbd{C-g}, so
1150 you'll just have to wait.
1152 @acronym{SES} uses @code{run-with-idle-timer} to move the cell underline when
1153 Emacs will be scrolling the buffer after the end of a command, and
1154 also to narrow and underline after @kbd{C-x C-v}. This is visible as
1155 a momentary glitch after C-x C-v and certain scrolling commands. You
1156 can type ahead without worrying about the glitch.
1159 @node Nonrelocatable references
1160 @section Nonrelocatable references
1161 @cindex nonrelocatable references
1162 @cindex references, nonrelocatable
1164 @kbd{C-y} relocates all cell-references in a pasted formula, while
1165 @kbd{C-u C-y} relocates none of the cell-references. What about mixed
1168 The best way is to rename cells that you do not want to be relocatable
1169 by using @code{ses-rename-cell}.
1170 @findex ses-rename-cell
1171 Cells that do not have an A1-like name style are not relocated on
1172 yank. Using this method, the concerned cells won't be relocated
1173 whatever formula they appear in. Please note however that when a
1174 formula contains some range @code{(ses-range @var{cell1} @var{cell2})}
1175 then in the yanked formula each range bound @var{cell1} and
1176 @var{cell2} are relocated, or not, independently, depending on whether
1177 they are A1-like or renamed.
1179 An alternative method is to use
1183 to make an @dfn{absolute reference}. The formula relocator skips over
1184 quoted things, so this will not be relocated when pasted or when
1185 rows/columns are inserted/deleted. However, B3 will not be recorded
1186 as a dependency of this cell, so this cell will not be updated
1187 automatically when B3 is changed, this is why using
1188 @code{ses-rename-cell} is most of the time preferable.
1190 The variables @code{row} and @code{col} are dynamically bound while a
1191 cell formula is being evaluated. You can use
1193 (ses-cell-value row 0)
1195 to get the value from the leftmost column in the current row. This
1196 kind of dependency is also not recorded.
1200 @section The data area
1202 @findex ses-reconstruct-all
1204 Begins with an 014 character, followed by sets of cell-definition
1205 macros for each row, followed by the set of local printer
1206 definitions, followed by column-widths, column-printers,
1207 default-printer, and header-row. Then there's the global parameters
1208 (file-format ID, row count, column count, local printer count) and the
1209 local variables (specifying @acronym{SES} mode for the buffer, etc.).
1211 When a @acronym{SES} file is loaded, first the global parameters are
1212 loaded, then the entire data area is @code{eval}ed, and finally the local
1213 variables are processed.
1215 You can edit the data area, but don't insert or delete any newlines
1216 except in the local-variables part, since @acronym{SES} locates things by
1217 counting newlines. Use @kbd{C-x C-e} at the end of a line to install
1218 your edits into the spreadsheet data structures (this does not update
1219 the print area, use, e.g., @kbd{C-c C-l} for that).
1221 The data area is maintained as an image of spreadsheet data
1222 structures that area stored in buffer-local variables. If the data
1223 area gets messed up, you can try reconstructing the data area from the
1228 (@code{ses-reconstruct-all}).
1232 @node Buffer-local variables in spreadsheets
1233 @section Buffer-local variables in spreadsheets
1234 @cindex buffer-local variables
1235 @cindex variables, buffer-local
1237 You can add additional local variables to the list at the bottom of
1238 the data area, such as hidden constants you want to refer to in your
1241 You can override the variable @code{ses--symbolic-formulas} to be a list of
1242 symbols (as parenthesized strings) to show as completions for the @kbd{'}
1243 command. This initial completions list is used instead of the actual
1244 set of symbols-as-formulas in the spreadsheet.
1246 For an example of this, see file @file{etc/ses-example.ses}.
1248 If (for some reason) you want your formulas or printers to save data
1249 into variables, you must declare these variables as buffer-locals in
1250 order to avoid a virus warning.
1252 You can define functions by making them values for the fake local
1253 variable @code{eval}. Such functions can then be used in your
1254 formulas and printers, but usually each @code{eval} is presented to
1255 the user during file loading as a potential virus. This can get
1258 You can define functions in your @file{.emacs} file. Other people can
1259 still read the print area of your spreadsheet, but they won't be able
1260 to recalculate or reprint anything that depends on your functions. To
1261 avoid virus warnings, each function used in a formula needs
1263 (put 'your-function-name 'safe-function t)
1266 @node Uses of defadvice in @acronym{SES}
1267 @section Uses of defadvice in @acronym{SES}
1270 @cindex copy-region-as-kill
1275 Defines a new undo element format (@var{fun} . @var{args}), which
1276 means ``undo by applying @var{fun} to @var{args}''. For spreadsheet
1277 buffers, it allows undos in the data area even though that's outside
1280 @item copy-region-as-kill
1281 When copying from the print area of a spreadsheet, treat the region as
1282 a rectangle and attach each cell's formula and printer as 'ses
1286 When yanking into the print area of a spreadsheet, first try to yank
1287 as cells (if the yank text has 'ses properties), then as tab-separated
1288 formulas, then (if all else fails) as a single formula for the current
1292 @c ===================================================================
1298 @c ===================================================================
1300 @node Acknowledgments
1301 @unnumbered Acknowledgments
1305 @c jyavner@@member.fsf.org
1309 @c shigeru.fukaya@@gmail.com
1316 @c jyavner@@member.fsf.org
1325 @c christoph.conrad@@gmx.de
1327 @c cyberbob@@redneck.gacracker.org
1329 @c syver-en@@online.no
1331 @c fischman@@zion.bpnetworks.com
1333 @c Thomas.Gehrlein@@t-online.de
1335 @c c.f.a.johnson@@rogers.com
1337 @c lyusong@@hotmail.com
1341 @c maierh@@myself.com
1343 @c anash@@san.rr.com
1345 @c pinard@@iro.umontreal.ca
1347 @c ppinto@@cs.cmu.edu
1351 @c epameinondas@@gmx.de
1354 Richard M. Stallman,
1355 @c teirllm@@dms.auburn.edu
1359 @c jphil@@acs.pagesjaunes.fr
1360 Jean-Philippe Theberge
1363 @c ===================================================================
1365 @node GNU Free Documentation License
1366 @appendix GNU Free Documentation License
1367 @include doclicense.texi