|
47667
|
1 \input texinfo @c -*-texinfo-*-
|
|
|
2 @c %**start of header
|
|
|
3 @setfilename ../info/ses
|
|
|
4 @settitle SES: Simple Emacs Spreadsheet
|
|
|
5 @setchapternewpage off
|
|
|
6 @c %**end of header
|
|
|
7
|
|
47737
|
8 @copying
|
|
47667
|
9 This file documents SES: the Simple Emacs Spreadsheet.
|
|
|
10
|
|
|
11 Copyright @copyright{} 2002 Free Software Foundation, Inc.
|
|
|
12
|
|
47737
|
13 @quotation
|
|
47667
|
14 Permission is granted to copy, distribute and/or modify this document
|
|
|
15 under the terms of the GNU Free Documentation License, Version 1.1 or
|
|
|
16 any later version published by the Free Software Foundation; with no
|
|
|
17 Invariant Sections, with the Front-Cover texts being ``A GNU
|
|
|
18 Manual,'' and with the Back-Cover Texts as in (a) below. A copy of the
|
|
|
19 license is included in the section entitled ``GNU Free Documentation
|
|
|
20 License'' in the Emacs manual.
|
|
|
21
|
|
|
22 (a) The FSF's Back-Cover Text is: ``You have freedom to copy and modify
|
|
|
23 this GNU Manual, like GNU software. Copies published by the Free
|
|
|
24 Software Foundation raise funds for GNU development.''
|
|
|
25
|
|
|
26 This document is part of a collection distributed under the GNU Free
|
|
|
27 Documentation License. If you want to distribute this document
|
|
|
28 separately from the collection, you can do so by adding a copy of the
|
|
|
29 license to the document, as described in section 6 of the license.
|
|
47737
|
30 @end quotation
|
|
|
31 @end copying
|
|
|
32
|
|
|
33 @dircategory Emacs
|
|
|
34 @direntry
|
|
|
35 * SES: (ses). Simple Emacs Spreadsheet
|
|
|
36 @end direntry
|
|
47667
|
37
|
|
|
38 @finalout
|
|
|
39
|
|
|
40 @titlepage
|
|
|
41 @title SES
|
|
|
42 @subtitle Simple Emacs Spreadsheet
|
|
|
43 @author Jonathan A. Yavner
|
|
55365
|
44 @author @email{jyavner@@member.fsf.org}
|
|
47667
|
45
|
|
|
46 @page
|
|
|
47 @vskip 0pt plus 1filll
|
|
47737
|
48 @insertcopying
|
|
47667
|
49 @end titlepage
|
|
|
50
|
|
|
51 @contents
|
|
|
52
|
|
|
53 @c ===================================================================
|
|
|
54
|
|
|
55 @ifnottex
|
47668
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
56 @node Top, Sales Pitch, (dir), (dir)
|
|
47667
|
57 @comment node-name, next, previous, up
|
|
|
58 @top SES: Simple Emacs Spreadsheet
|
|
|
59
|
|
|
60 @display
|
|
|
61 SES is a major mode for GNU Emacs to edit spreadsheet files, which
|
|
|
62 contain a rectangular grid of cells. The cells' values are specified
|
|
|
63 by formulas that can refer to the values of other cells.
|
|
|
64 @end display
|
|
|
65 @end ifnottex
|
|
|
66
|
|
52042
|
67 To report bugs, send email to @email{jyavner@@member.fsf.org}.
|
|
47667
|
68
|
|
|
69 @menu
|
47668
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
70 * Sales Pitch:: Why use SES?
|
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
71 * The Basics:: Basic spreadsheet commands
|
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
72 * Advanced Features:: Want to know more?
|
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
73 * For Gurus:: Want to know @emph{even more}?
|
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
74 * Acknowledgements:: Acknowledgements
|
|
47667
|
75 @end menu
|
|
|
76
|
|
|
77 @c ===================================================================
|
|
|
78
|
|
|
79 @node Sales Pitch, The Basics, Top, Top
|
|
|
80 @comment node-name, next, previous, up
|
|
|
81 @chapter Sales Pitch
|
|
|
82
|
|
|
83 @itemize @bullet
|
|
|
84 @item Create and edit simple spreadsheets with a minimum of fuss.
|
|
|
85 @item Full undo/redo/autosave.
|
|
|
86 @item Immune to viruses in spreadsheet files.
|
|
|
87 @item Cell formulas are straight Emacs Lisp.
|
|
|
88 @item Printer functions for control of cell appearance.
|
|
|
89 @item Intuitive keystroke commands: C-o = insert row, M-o = insert column, etc.
|
|
|
90 @item ``Spillover'' of lengthy cell values into following blank cells.
|
|
|
91 @item Header line shows column letters or a selected row.
|
|
|
92 @item Completing-read for entering symbols as cell values.
|
|
|
93 @item Cut, copy, and paste can transfer formulas and printer functions.
|
|
|
94 @item Import and export of tab-separated values or tab-separated formulas.
|
|
|
95 @item Plaintext, easily-hacked file format.
|
|
|
96 @end itemize
|
|
|
97
|
|
|
98 @c ===================================================================
|
|
|
99
|
|
|
100 @node The Basics, Advanced Features, Sales Pitch, Top
|
|
|
101 @comment node-name, next, previous, up
|
|
|
102 @chapter The Basics
|
|
|
103
|
|
|
104 A @dfn{cell identifier} is a symbol with a column letter and a row
|
|
|
105 number. Cell B7 is the 2nd column of the 7th row. For very wide
|
|
|
106 spreadsheets, there are two column letters: cell AB7 is the 28th
|
|
|
107 column of the 7th row.
|
|
|
108
|
|
|
109 @table @kbd
|
|
|
110 @item j
|
|
|
111 Moves point to cell, specified by identifier (@code{ses-jump}).
|
|
|
112 @end table
|
|
|
113
|
|
|
114 Point is always at the left edge of a cell, or at the empty endline.
|
|
|
115 When mark is inactive, the current cell is underlined. When mark is
|
|
|
116 active, the range is the highlighted rectangle of cells (SES always
|
|
|
117 uses transient mark mode). Drag the mouse from A1 to A3 to create the
|
|
|
118 range A1-A2. Many SES commands operate only on single cells, not
|
|
|
119 ranges.
|
|
|
120
|
|
|
121 @table @kbd
|
|
|
122 @item C-SPC
|
|
|
123 @itemx C-@@
|
|
|
124 Set mark at point (@code{set-mark-command}).
|
|
|
125
|
|
|
126 @item C-g
|
|
|
127 Turn off the mark (@code{keyboard-quit}).
|
|
|
128
|
|
|
129 @item M-h
|
|
|
130 Highlight current row (@code{ses-mark-row}).
|
|
|
131
|
|
|
132 @item S-M-h
|
|
|
133 Highlight current column (@code{ses-mark-column}).
|
|
|
134
|
|
|
135 @item C-x h
|
|
|
136 Highlight all cells (@code{mark-whole-buffer}).
|
|
|
137 @end table
|
|
|
138
|
|
|
139 @menu
|
|
|
140 * Formulas::
|
|
|
141 * Resizing::
|
|
|
142 * Printer functions::
|
|
|
143 * Clearing cells::
|
|
|
144 * Copy/cut/paste::
|
|
|
145 * Customizing SES::
|
|
|
146 @end menu
|
|
|
147
|
|
|
148 @node Formulas, Resizing, The Basics, The Basics
|
|
|
149 @section Cell formulas
|
|
|
150
|
|
|
151 To enter a number into the current cell, just start typing:
|
|
|
152
|
|
|
153 @table @kbd
|
|
|
154 @item 0..9
|
|
|
155 Self-insert a digit (@code{ses-read-cell}).
|
|
|
156
|
|
|
157 @item -
|
|
|
158 Self-insert a negative number (@code{ses-read-cell}).
|
|
|
159
|
|
|
160 @item .
|
|
|
161 Self-insert a fractional number (@code{ses-read-cell}).
|
|
|
162
|
|
|
163 @item "
|
|
|
164 Self-insert a quoted string. The ending double-quote
|
|
|
165 is inserted for you (@code{ses-read-cell}).
|
|
|
166
|
|
|
167 @item (
|
|
|
168 Self-insert an expression. The right-parenthesis is inserted for you
|
|
|
169 (@code{ses-read-cell}). To access another cell's value, just use its
|
|
|
170 identifier in your expression. Whenever the other cell is changed,
|
|
|
171 this cell's formula will be reevaluated. While typing in the
|
|
|
172 expression, you can use @kbd{M-TAB} to complete symbol names.
|
|
|
173
|
|
|
174 @item ' @r{(apostrophe)}
|
|
|
175 Enter a symbol (ses-read-symbol). SES remembers all symbols that have
|
|
|
176 been used as formulas, so you can type just the beginning of a symbol
|
|
|
177 and use @kbd{SPC}, @kbd{TAB}, and @kbd{?} to complete it.
|
|
|
178 @end table
|
|
|
179
|
|
|
180 To enter something else (e.g., a vector), begin with a digit, then
|
|
|
181 erase the digit and type whatever you want.
|
|
|
182
|
|
|
183 @table @kbd
|
|
|
184 @item RET
|
|
|
185 Edit the existing formula in the current cell (@code{ses-edit-cell}).
|
|
|
186
|
|
|
187 @item C-c C-c
|
|
|
188 Force recalculation of the current cell or range (@code{ses-recalculate-cell}).
|
|
|
189
|
|
|
190 @item C-c C-l
|
|
|
191 Recalculate the entire spreadsheet (@code{ses-recalculate-all}).
|
|
|
192 @end table
|
|
|
193
|
|
|
194 @node Resizing, Printer functions, Formulas, The Basics
|
|
|
195 @section Resizing the spreadsheet
|
|
|
196
|
|
|
197 Basic commands:
|
|
|
198
|
|
|
199 @table @kbd
|
|
|
200 @item C-o
|
|
|
201 (@code{ses-insert-row})
|
|
|
202
|
|
|
203 @item M-o
|
|
|
204 (@code{ses-insert-column})
|
|
|
205
|
|
|
206 @item C-k
|
|
|
207 (@code{ses-delete-row})
|
|
|
208
|
|
|
209 @item M-k
|
|
|
210 (@code{ses-delete-column})
|
|
|
211
|
|
|
212 @item w
|
|
|
213 (@code{ses-set-column-width})
|
|
|
214
|
|
|
215 @item TAB
|
|
|
216 Moves point to the next rightward cell, or inserts a new column if
|
|
|
217 already at last cell on line, or inserts a new row if at endline
|
|
|
218 (@code{ses-forward-or-insert}).
|
|
|
219
|
|
|
220 @item C-j
|
|
|
221 Linefeed inserts below the current row and moves to column A
|
|
|
222 (@code{ses-append-row-jump-first-column}).
|
|
|
223 @end table
|
|
|
224
|
|
|
225 Resizing the spreadsheet (unless you're just changing a column width)
|
|
|
226 relocates all the cell-references in formulas so they still refer to
|
|
|
227 the same cells. If a formula mentioned B1 and you insert a new first
|
|
|
228 row, the formula will now mention B2.
|
|
|
229
|
|
|
230 If you delete a cell that a formula refers to, the cell-symbol is
|
|
|
231 deleted from the formula, so @code{(+ A1 B1 C1)} after deleting the third
|
|
|
232 column becomes @code{(+ A1 B1)}. In case this is not what you wanted:
|
|
|
233
|
|
|
234 @table @kbd
|
|
|
235 @item C-_
|
|
|
236 @itemx C-x u
|
|
|
237 Undo previous action (@code{(undo)}).
|
|
|
238 @end table
|
|
|
239
|
|
|
240
|
|
|
241 @node Printer functions, Clearing cells, Resizing, The Basics
|
|
|
242 @section Printer functions
|
|
|
243
|
|
|
244 Printer functions convert binary cell values into the print forms that
|
|
|
245 Emacs will display on the screen.
|
|
|
246
|
|
|
247 A printer can be a format string, like @samp{"$%.2f"}. The result
|
|
|
248 string is right-aligned within the print cell. To get left-alignment,
|
|
|
249 use parentheses: @samp{("$%.2f")}. A printer can also be a
|
|
|
250 one-argument function (a symbol or a lambda), whose result is a string
|
|
|
251 (right-aligned) or list of one string (left-aligned). While typing in
|
|
|
252 a lambda, you can use @kbd{M-TAB} to complete the names of symbols.
|
|
|
253
|
|
56279
|
254 Each cell has a printer. If @code{nil}, the column-printer for the cell's
|
|
|
255 column is used. If that is also @code{nil}, the default-printer for the
|
|
47667
|
256 spreadsheet is used.
|
|
|
257
|
|
|
258 @table @kbd
|
|
|
259 @item p
|
|
|
260 Enter a printer for current cell or range (@code{ses-read-cell-printer}).
|
|
|
261
|
|
|
262 @item M-p
|
|
|
263 Enter a printer for the current column (@code{ses-read-column-printer}).
|
|
|
264
|
|
|
265 @item C-c C-p
|
|
|
266 Enter the default printer for the spreadsheet
|
|
|
267 (@code{ses-read-default-printer}).
|
|
|
268 @end table
|
|
|
269
|
|
|
270 The @code{ses-read-@r{XXX}-printer} commands have their own minibuffer
|
|
|
271 history, which is preloaded with the set of all printers used in this
|
|
|
272 spreadsheet, plus the standard printers.
|
|
|
273
|
|
|
274 The standard printers are suitable only for cells, not columns or
|
|
|
275 default, because they format the value using the column-printer (or
|
|
56279
|
276 default-printer if @code{nil}) and then center the result:
|
|
47667
|
277
|
|
|
278 @table @code
|
|
|
279 @item ses-center
|
|
|
280 Just centering.
|
|
|
281
|
|
|
282 @item ses-center-span
|
|
|
283 Centering with spill-over to following blank cells.
|
|
|
284
|
|
|
285 @item ses-dashfill
|
|
|
286 Centering using dashes (-) instead of spaces.
|
|
|
287
|
|
|
288 @item ses-dashfill-span
|
|
|
289 Centering with dashes and spill-over.
|
|
|
290
|
|
|
291 @item ses-tildefill-span
|
|
|
292 Centering with tildes (~) and spill-over.
|
|
|
293 @end table
|
|
|
294
|
|
|
295
|
|
|
296 @node Clearing cells, Copy/cut/paste, Printer functions, The Basics
|
|
|
297 @section Clearing cells
|
|
|
298
|
|
56279
|
299 These commands set both formula and printer to @code{nil}:
|
|
47667
|
300
|
|
|
301 @table @kbd
|
|
|
302 @item DEL
|
|
|
303 Clear cell and move left (@code{ses-clear-cell-backward}).
|
|
|
304
|
|
|
305 @item C-d
|
|
|
306 Clear cell and move right (@code{ses-clear-cell-forward}).
|
|
|
307 @end table
|
|
|
308
|
|
|
309
|
|
|
310 @node Copy/cut/paste, Customizing SES, Clearing cells, The Basics
|
|
|
311 @section Copy, cut, and paste
|
|
|
312
|
|
|
313 The copy functions work on rectangular regions of cells. You can paste the
|
|
|
314 copies into non-SES buffers to export the print text.
|
|
|
315
|
|
|
316 @table @kbd
|
|
|
317 @item M-w
|
|
|
318 @itemx [copy]
|
|
|
319 @itemx [C-insert]
|
|
|
320 Copy the highlighted cells to kill ring and primary clipboard
|
|
|
321 (@code{kill-ring-save}).
|
|
|
322
|
|
|
323 @item [drag-mouse-1]
|
|
|
324 Mark a region and copy it to kill ring and primary clipboard
|
|
|
325 (@code{mouse-set-region}).
|
|
|
326
|
|
|
327 @item [M-drag-mouse-1]
|
|
|
328 Mark a region and copy it to kill ring and secondary clipboard
|
|
|
329 (@code{mouse-set-secondary}).
|
|
|
330
|
|
|
331 @item C-w
|
|
|
332 @itemx [cut]
|
|
|
333 @itemx [S-delete]
|
|
56279
|
334 The cut functions do not actually delete rows or columns---they copy
|
|
47667
|
335 and then clear (@code{ses-kill-override}).
|
|
|
336
|
|
|
337 @item C-y
|
|
|
338 @itemx [S-insert]
|
|
|
339 Paste from kill ring (@code{yank}). The paste functions behave
|
|
|
340 differently depending on the format of the text being inserted:
|
|
|
341 @itemize @bullet
|
|
|
342 @item
|
|
|
343 When pasting cells that were cut from a SES buffer, the print text is
|
|
|
344 ignored and only the attached formula and printer are inserted; cell
|
|
|
345 references in the formula are relocated unless you use @kbd{C-u}.
|
|
|
346 @item
|
|
|
347 The pasted text overwrites a rectangle of cells whose top left corner
|
|
|
348 is the current cell. If part of the rectangle is beyond the edges of
|
|
|
349 the spreadsheet, you must confirm the increase in spreadsheet size.
|
|
|
350 @item
|
|
|
351 Non-SES text is usually inserted as a replacement formula for the
|
|
|
352 current cell. If the formula would be a symbol, it's treated as a
|
|
|
353 string unless you use @kbd{C-u}. Pasted formulas with syntax errors
|
|
|
354 are always treated as strings.
|
|
|
355 @end itemize
|
|
|
356
|
|
|
357 @item [paste]
|
|
|
358 Paste from primary clipboard or kill ring (@code{clipboard-yank}).
|
|
|
359
|
|
|
360 @item [mouse-2]
|
|
|
361 Set point and paste from primary clipboard (@code{mouse-yank-at-click}).
|
|
|
362
|
|
|
363 @item [M-mouse-2]
|
|
|
364 Set point and paste from secondary clipboard (@code{mouse-yank-secondary}).
|
|
|
365
|
|
|
366 @item M-y
|
|
|
367 Immediately after a paste, you can replace the text with a preceding
|
|
|
368 element from the kill ring (@code{ses-yank-pop}). Unlike the standard
|
|
|
369 Emacs yank-pop, the SES version uses @code{undo} to delete the old
|
|
|
370 yank. This doesn't make any difference?
|
|
|
371 @end table
|
|
|
372
|
|
|
373 @node Customizing SES, , Copy/cut/paste, The Basics
|
|
|
374 @section Customizing SES
|
|
|
375
|
|
|
376 By default, a newly-created spreadsheet has 1 row and 1 column. The
|
|
|
377 column width is 7 and the default printer is @samp{"%.7g"}. Each of these
|
|
|
378 can be customized. Look in group ``ses''.
|
|
|
379
|
|
|
380 After entering a cell value, point normally moves right to the next
|
|
|
381 cell. You can customize @code{ses-after-entry-functions} to move left or
|
|
|
382 up or down. For diagonal movement, select two functions from the
|
|
|
383 list.
|
|
|
384
|
|
|
385 @code{ses-mode-hook} is a normal mode hook (list of functions to
|
|
|
386 execute when starting SES mode for a buffer).
|
|
|
387
|
|
54771
|
388 The variable @code{safe-functions} is a list of possibly-unsafe
|
|
47667
|
389 functions to be treated as safe when analysing formulas and printers.
|
|
|
390 @xref{Virus protection}. Before customizing @code{safe-functions},
|
|
|
391 think about how much you trust the person who's suggesting this
|
|
54771
|
392 change. The value @code{t} turns off all anti-virus protection. A
|
|
47667
|
393 list-of-functions value might enable a ``gee whiz'' spreadsheet, but it
|
|
|
394 also creates trapdoors in your anti-virus armor. In order for virus
|
|
|
395 protection to work, you must always press @kbd{n} when presented with
|
|
|
396 a virus warning, unless you understand what the questionable code is
|
|
|
397 trying to do. Do not listen to those who tell you to customize
|
|
|
398 @code{enable-local-eval}---this variable is for people who don't wear
|
|
|
399 safety belts!
|
|
|
400
|
|
|
401
|
|
|
402 @c ===================================================================
|
|
|
403
|
|
|
404 @node Advanced Features, For Gurus, The Basics, Top
|
|
|
405 @chapter Advanced Features
|
|
|
406
|
|
|
407 @table @kbd
|
|
|
408 @item C-c M-C-h
|
|
53945
|
409 (@code{ses-set-header-row}). The header line at the top of the SES
|
|
47667
|
410 window normally shows the column letter for each column. You can set
|
|
|
411 it to show a copy of some row, such as a row of column titles, so that
|
|
53945
|
412 row will always be visible. Default is to set the current row as the
|
|
|
413 header; use C-u to prompt for header row. Set the header to row 0 to
|
|
|
414 show column letters again.
|
|
|
415 @item [header-line mouse-3]
|
|
|
416 Pops up a menu to set the current row as the header, or revert to
|
|
|
417 column letters.
|
|
47667
|
418 @end table
|
|
|
419
|
|
|
420 @menu
|
|
|
421 * The print area::
|
|
|
422 * Ranges in formulas::
|
|
|
423 * Sorting by column::
|
|
|
424 * Standard formula functions::
|
|
|
425 * More on cell printing::
|
|
|
426 * Import and export::
|
|
|
427 * Virus protection::
|
|
|
428 * Spreadsheets with details and summary::
|
|
|
429 @end menu
|
|
|
430
|
|
|
431 @node The print area, Ranges in formulas, Advanced Features, Advanced Features
|
|
|
432 @section The print area
|
|
|
433
|
|
|
434 A SES file consists of a print area and a data area. Normally the
|
|
|
435 buffer is narrowed to show only the print area. The print area is
|
|
|
436 read-only except for special SES commands; it contains cell values
|
|
|
437 formatted by printer functions. The data area records the formula and
|
|
|
438 printer functions, etc.
|
|
|
439
|
|
|
440 @table @kbd
|
|
|
441 @item C-x n w
|
|
|
442 Show print and data areas (@code{widen}).
|
|
|
443
|
47668
bcc91f26d220
Fix Texinfo build failure by running `texinfo-all-menus-update'.
Robert J. Chassell <bob@rattlesnake.com>
diff
changeset
|
444 @item C-c C-n
|
|
47667
|
445 Show only print area (@code{ses-renarrow-buffer}).
|
|
|
446
|
|
|
447 @item S-C-l
|
|
|
448 @itemx M-C-l
|
|
|
449 Recreate print area by reevaluating printer functions for all cells
|
|
|
450 (@code{ses-reprint-all}).
|
|
|
451 @end table
|
|
|
452
|
|
|
453 @node Ranges in formulas, Sorting by column, The print area, Advanced Features
|
|
|
454 @section Ranges in formulas
|
|
|
455
|
|
|
456 A formula like
|
|
|
457 @lisp
|
|
|
458 (+ A1 A2 A3)
|
|
|
459 @end lisp
|
|
|
460 is the sum of three specific cells. If you insert a new second row,
|
|
|
461 the formula becomes
|
|
|
462 @lisp
|
|
|
463 (+ A1 A3 A4)
|
|
|
464 @end lisp
|
|
|
465 and the new row is not included in the sum.
|
|
|
466
|
|
|
467 The macro @code{(ses-range @var{from} @var{to})} evalutes to a list of
|
|
|
468 the values in a rectangle of cells. If your formula is
|
|
|
469 @lisp
|
|
|
470 (apply '+ (ses-range A1 A3))
|
|
|
471 @end lisp
|
|
|
472 and you insert a new second row, it becomes
|
|
|
473 @lisp
|
|
|
474 (apply '+ (ses-range A1 A4))
|
|
|
475 @end lisp
|
|
|
476 and the new row is included in the sum.
|
|
|
477
|
|
|
478 While entering or editing a formula in the minibuffer, you can select
|
|
|
479 a range in the spreadsheet (using mouse or keyboard), then paste a
|
|
|
480 representation of that range into your formula. Suppose you select
|
|
|
481 A1-C1:
|
|
|
482
|
|
|
483 @table @kbd
|
|
|
484 @item [S-mouse-3]
|
|
|
485 Inserts "A1 B1 C1" @code{(ses-insert-range-click})
|
|
|
486
|
|
|
487 @item C-c C-r
|
|
|
488 Keyboard version (@code{ses-insert-range}).
|
|
|
489
|
|
|
490 @item [C-S-mouse-3]
|
|
|
491 Inserts "(ses-range A1 C1)" (@code{ses-insert-ses-range-click}).
|
|
|
492
|
|
|
493 @item C-c C-s
|
|
|
494 Keyboard version (@code{ses-insert-ses-range}).
|
|
|
495 @end table
|
|
|
496
|
|
|
497 If you delete the @var{from} or @var{to} cell for a range, the nearest
|
|
|
498 still-existing cell is used instead. If you delete the entire range,
|
|
|
499 the formula relocator will delete the ses-range from the formula.
|
|
|
500
|
|
|
501 If you insert a new row just beyond the end of a one-column range, or
|
|
|
502 a new column just beyond a one-row range, the new cell is included in
|
|
|
503 the range. New cells inserted just before a range are not included.
|
|
|
504
|
|
|
505
|
|
|
506 @node Sorting by column, Standard formula functions, Ranges in formulas, Advanced Features
|
|
|
507 @section Sorting by column
|
|
|
508
|
|
|
509 @table @kbd
|
|
|
510 @item C-c M-C-s
|
|
|
511 Sort the cells of a range using one of the columns
|
|
|
512 (@code{ses-sort-column}). The rows (or partial rows if the range
|
|
|
513 doesn't include all columns) are rearranged so the chosen column will
|
|
|
514 be in order.
|
|
|
515
|
|
|
516 @item [header-line mouse-2]
|
|
|
517 The easiest way to sort is to click mouse-2 on the chosen column's header row
|
|
|
518 (@code{ses-sort-column-click}).
|
|
|
519 @end table
|
|
|
520
|
|
|
521 The sort comparison uses @code{string<}, which works well for
|
|
|
522 right-justified numbers and left-justified strings.
|
|
|
523
|
|
|
524 With prefix arg, sort is in descending order.
|
|
|
525
|
|
|
526 Rows are moved one at a time, with relocation of formulas. This works
|
|
|
527 well if formulas refer to other cells in their row, not so well for
|
|
|
528 formulas that refer to other rows in the range or to cells outside the
|
|
|
529 range.
|
|
|
530
|
|
|
531
|
|
|
532 @node Standard formula functions, More on cell printing, Sorting by column, Advanced Features
|
|
|
533 @section Standard formula functions
|
|
|
534
|
|
|
535 Oftentimes you want a calculation to exclude the blank cells. Here
|
|
|
536 are some useful functions to call from your formulas:
|
|
|
537
|
|
|
538 @table @code
|
|
|
539 @item (ses-delete-blanks &rest @var{args})
|
|
56279
|
540 Returns a list from which all blank cells (value is either @code{nil} or
|
|
47667
|
541 '*skip*) have been deleted.
|
|
|
542
|
|
|
543 @item (ses+ &rest @var{args})
|
|
|
544 Sum of non-blank arguments.
|
|
|
545
|
|
|
546 @item (ses-average @var{list})
|
|
|
547 Average of non-blank elements in @var{list}. Here the list is passed
|
|
|
548 as a single argument, since you'll probably use it with @code{ses-range}.
|
|
|
549 @end table
|
|
|
550
|
|
|
551 @node More on cell printing, Import and export, Standard formula functions, Advanced Features
|
|
|
552 @section More on cell printing
|
|
|
553
|
|
|
554 Special cell values:
|
|
|
555 @itemize
|
|
|
556 @item nil prints the same as "", but allows previous cell to spill over.
|
|
|
557 @item '*skip* replaces nil when the previous cell actually does spill over;
|
|
|
558 nothing is printed for it.
|
|
|
559 @item '*error* indicates that the formula signalled an error instead of
|
|
|
560 producing a value: the print cell is filled with hash marks (#).
|
|
|
561 @end itemize
|
|
|
562
|
|
|
563 If the result from the printer function is too wide for the cell and
|
|
56279
|
564 the following cell is @code{nil}, the result will spill over into the
|
|
47667
|
565 following cell. Very wide results can spill over several cells. If
|
|
|
566 the result is too wide for the available space (up to the end of the
|
|
56279
|
567 row or the next non-@code{nil} cell), the result is truncated if the cell's
|
|
47667
|
568 value is a string, or replaced with hash marks otherwise.
|
|
|
569
|
|
|
570 SES could get confused by printer results that contain newlines or
|
|
|
571 tabs, so these are replaced with question marks.
|
|
|
572
|
|
|
573 @table @kbd
|
|
|
574 @item C-c C-t
|
|
|
575 Confine a cell to its own column (@code{ses-truncate-cell}). This
|
|
|
576 alows you to move point to a rightward cell that would otherwise be
|
|
|
577 covered by a spill-over. If you don't change the rightward cell, the
|
|
|
578 confined cell will spill over again the next time it is reprinted.
|
|
|
579
|
|
|
580 @item C-c C-c
|
|
|
581 When applied to a single cell, this command displays in the echo area any
|
|
|
582 formula error or printer error that occurred during
|
|
|
583 recalculation/reprinting (@code{ses-recalculate-cell}).
|
|
|
584 @end table
|
|
|
585
|
|
|
586 When a printer function signals an error, the default printer
|
|
|
587 @samp{"%s"} is substituted. This is useful when your column printer
|
|
|
588 is numeric-only and you use a string as a cell value.
|
|
|
589
|
|
|
590
|
|
|
591 @node Import and export, Virus protection, More on cell printing, Advanced Features
|
|
|
592 @section Import and export
|
|
|
593
|
|
|
594 @table @kbd
|
|
|
595 @item x t
|
|
|
596 Export a range of cells as tab-separated values (@code{ses-export-tsv}).
|
|
|
597 @item x T
|
|
|
598 Export a range of cells as tab-separated formulas (@code{ses-export-tsf}).
|
|
|
599 @end table
|
|
|
600
|
|
|
601 The exported text goes to the kill ring --- you can paste it into
|
|
|
602 another buffer. Columns are separated by tabs, rows by newlines.
|
|
|
603
|
|
|
604 To import text, use any of the yank commands where the text to paste
|
|
|
605 contains tabs and/or newlines. Imported formulas are not relocated.
|
|
|
606
|
|
|
607 @node Virus protection, Spreadsheets with details and summary, Import and export, Advanced Features
|
|
|
608 @section Virus protection
|
|
|
609
|
|
|
610 Whenever a formula or printer is read from a file or is pasted into
|
|
|
611 the spreadsheet, it receives a ``needs safety check'' marking. Later,
|
|
|
612 when the formula or printer is evaluated for the first time, it is
|
|
|
613 checked for safety using the @code{unsafep} predicate; if found to be
|
|
|
614 ``possibly unsafe'', the questionable formula or printer is displayed
|
|
|
615 and you must press Y to approve it or N to use a substitute. The
|
|
|
616 substitute always signals an error.
|
|
|
617
|
|
|
618 Formulas or printers that you type in are checked immediately for
|
|
|
619 safety. If found to be possibly unsafe and you press N to disapprove,
|
|
|
620 the action is cancelled and the old formula or printer will remain.
|
|
|
621
|
|
|
622 Besides viruses (which try to copy themselves to other files),
|
|
|
623 @code{unsafep} can also detect all other kinds of Trojan horses, such as
|
|
|
624 spreadsheets that delete files, send email, flood Web sites, alter
|
|
|
625 your Emacs settings, etc.
|
|
|
626
|
|
|
627 Generally, spreadsheet formulas and printers are simple things that
|
|
|
628 don't need to do any fancy computing, so all potentially-dangerous
|
|
|
629 parts of the Emacs Lisp environment can be excluded without cramping
|
|
|
630 your style as a formula-writer. See the documentation in @file{unsafep.el}
|
|
|
631 for more info on how Lisp forms are classified as safe or unsafe.
|
|
|
632
|
|
|
633 @node Spreadsheets with details and summary, , Virus protection, Advanced Features
|
|
|
634 @section Spreadsheets with details and summary
|
|
|
635
|
|
|
636 A common organization for spreadsheets is to have a bunch of ``detail''
|
|
|
637 rows, each perhaps describing a transaction, and then a set of
|
|
|
638 ``summary'' rows that each show reduced data for some subset of the
|
|
|
639 details. SES supports this organization via the @code{ses-select}
|
|
|
640 function.
|
|
|
641
|
|
|
642 @table @code
|
|
|
643 @item (ses-select @var{fromrange} @var{test} @var{torange})
|
|
|
644 Returns a subset of @var{torange}. For each member in @var{fromrange}
|
|
|
645 that is equal to @var{test}, the corresponding member of @var{torange}
|
|
|
646 is included in the result.
|
|
|
647 @end table
|
|
|
648
|
|
|
649 Example of use:
|
|
|
650 @lisp
|
|
|
651 (ses-average (ses-select (ses-range A1 A5) 'Smith (ses-range B1 B5)))
|
|
|
652 @end lisp
|
|
|
653 This computes the average of the B column values for those rows whose
|
|
|
654 A column value is the symbol 'Smith.
|
|
|
655
|
|
|
656 Arguably one could specify only @var{fromrange} plus
|
|
|
657 @var{to-row-offset} and @var{to-column-offset}. The @var{torange} is
|
|
|
658 stated explicitly to ensure that the formula will be recalculated if
|
|
|
659 any cell in either range is changed.
|
|
|
660
|
|
|
661 File @file{etc/ses-example.el} in the Emacs distribution is an example of a
|
|
|
662 details-and-summary spreadsheet.
|
|
|
663
|
|
|
664
|
|
|
665 @c ===================================================================
|
|
|
666
|
|
|
667 @node For Gurus, Acknowledgements, Advanced Features, Top
|
|
|
668 @chapter For Gurus
|
|
|
669
|
|
|
670 @menu
|
|
|
671 * Deferred updates::
|
|
|
672 * Nonrelocatable references::
|
|
|
673 * The data area::
|
|
|
674 * Buffer-local variables in spreadsheets::
|
|
|
675 * Uses of defadvice in SES::
|
|
|
676 @end menu
|
|
|
677
|
|
|
678 @node Deferred updates, Nonrelocatable references, For Gurus, For Gurus
|
|
|
679 @section Deferred updates
|
|
|
680
|
|
|
681 To save time by avoiding redundant computations, cells that need
|
|
|
682 recalculation due to changes in other cells are added to a set. At
|
|
|
683 the end of the command, each cell in the set is recalculated once.
|
|
|
684 This can create a new set of cells that need recalculation. The
|
|
|
685 process is repeated until either the set is empty or it stops changing
|
|
|
686 (due to circular references among the cells). In extreme cases, you
|
|
|
687 might see progress messages of the form ``Recalculating... (@var{nnn}
|
|
|
688 cells left)''. If you interrupt the calculation using @kbd{C-g}, the
|
|
|
689 spreadsheet will be left in an inconsistent state, so use @kbd{C-_} or
|
|
|
690 @kbd{C-c C-l} to fix it.
|
|
|
691
|
|
|
692 To save even more time by avoiding redundant writes, cells that have
|
|
|
693 changes are added to a set instead of being written immediately to the
|
|
|
694 data area. Each cell in the set is written once, at the end of the
|
|
|
695 command. If you change vast quantities of cells, you might see a
|
|
|
696 progress message of the form ``Writing... (@var{nnn} cells left)''.
|
|
|
697 These deferred cell-writes cannot be interrupted by @kbd{C-g}, so
|
|
|
698 you'll just have to wait.
|
|
|
699
|
|
|
700 SES uses @code{run-with-idle-timer} to move the cell underline when
|
|
|
701 Emacs will be scrolling the buffer after the end of a command, and
|
|
|
702 also to narrow and underline after @kbd{C-x C-v}. This is visible as
|
|
|
703 a momentary glitch after C-x C-v and certain scrolling commands. You
|
|
|
704 can type ahead without worrying about the glitch.
|
|
|
705
|
|
|
706
|
|
|
707 @node Nonrelocatable references, The data area, Deferred updates, For Gurus
|
|
|
708 @section Nonrelocatable references
|
|
|
709
|
|
|
710 @kbd{C-y} relocates all cell-references in a pasted formula, while
|
|
|
711 @kbd{C-u C-y} relocates none of the cell-references. What about mixed
|
|
|
712 cases?
|
|
|
713
|
|
|
714 You can use
|
|
|
715 @lisp
|
|
|
716 (symbol-value 'B3)
|
|
|
717 @end lisp
|
|
|
718 to make an @dfn{absolute reference}. The formula relocator skips over
|
|
|
719 quoted things, so this will not be relocated when pasted or when
|
|
|
720 rows/columns are inserted/deleted. However, B3 will not be recorded
|
|
|
721 as a dependency of this cell, so this cell will not be updated
|
|
|
722 automatically when B3 is changed.
|
|
|
723
|
|
|
724 The variables @code{row} and @code{col} are dynamically bound while a
|
|
|
725 cell formula is being evaluated. You can use
|
|
|
726 @lisp
|
|
|
727 (ses-cell-value row 0)
|
|
|
728 @end lisp
|
|
|
729 to get the value from the leftmost column in the current row. This
|
|
|
730 kind of dependency is also not recorded.
|
|
|
731
|
|
|
732
|
|
|
733 @node The data area, Buffer-local variables in spreadsheets, Nonrelocatable references, For Gurus
|
|
|
734 @section The data area
|
|
|
735
|
|
|
736 Begins with an 014 character, followed by sets of cell-definition
|
|
|
737 macros for each row, followed by column-widths, column-printers,
|
|
|
738 default-printer, and header-row. Then there's the global parameters
|
|
|
739 (file-format ID, numrows, numcols) and the local variables (specifying
|
|
|
740 SES mode for the buffer, etc.)
|
|
|
741
|
|
|
742 When a SES file is loaded, first the numrows and numcols values are
|
|
|
743 loaded, then the entire data area is @code{eval}ed, and finally the local
|
|
|
744 variables are processed.
|
|
|
745
|
|
|
746 You can edit the data area, but don't insert or delete any newlines
|
|
|
747 except in the local-variables part, since SES locates things by
|
|
|
748 counting newlines. Use @kbd{C-x C-e} at the end of a line to install
|
|
|
749 your edits into the spreadsheet data structures (this does not update
|
|
|
750 the print area, use e.g. @kbd{C-c C-l} for that).
|
|
|
751
|
|
|
752 The data area is maintained as an image of spreadsheet data
|
|
|
753 structures that area stored in buffer-local variables. If the data
|
|
|
754 area gets messed up, you can try reconstructing the data area from the
|
|
|
755 data structures:
|
|
|
756
|
|
|
757 @table @kbd
|
|
|
758 @item C-c M-C-l
|
|
|
759 (@code{ses-reconstruct-all}).
|
|
|
760 @end table
|
|
|
761
|
|
|
762
|
|
|
763 @node Buffer-local variables in spreadsheets, Uses of defadvice in SES, The data area, For Gurus
|
|
|
764 @section Buffer-local variables in spreadsheets
|
|
|
765
|
|
|
766 You can add additional local variables to the list at the bottom of
|
|
|
767 the data area, such as hidden constants you want to refer to in your
|
|
|
768 formulas.
|
|
|
769
|
|
|
770 You can override the variable @code{symbolic-formulas} to be a list of
|
|
|
771 symbols (as parenthesized strings) to show as completions for the '
|
|
|
772 command. This initial completions list is used instead of the actual
|
|
|
773 set of symbols-as-formulas in the spreadsheet.
|
|
|
774
|
|
|
775 For examples of these, see file @file{etc/ses-example.ses}.
|
|
|
776
|
|
|
777 If (for some reason) you want your formulas or printers to save data
|
|
|
778 into variables, you must declare these variables as buffer-locals in
|
|
|
779 order to avoid a virus warning.
|
|
|
780
|
|
|
781 You can define functions by making them values for the fake local
|
|
|
782 variable @code{eval}. Such functions can then be used in your
|
|
|
783 formulas and printers, but usually each @code{eval} is presented to
|
|
|
784 the user during file loading as a potential virus --- this can get
|
|
|
785 annoying.
|
|
|
786
|
|
|
787 You can define functions in your @file{.emacs} file. Other people can
|
|
|
788 still read the print area of your spreadsheet, but they won't be able
|
|
|
789 to recalculate or reprint anything that depends on your functions. To
|
|
|
790 avoid virus warnings, each function used in a formula needs
|
|
|
791 @lisp
|
|
|
792 (put 'your-function-name 'safe-function t)
|
|
|
793 @end lisp
|
|
|
794
|
|
|
795 @node Uses of defadvice in SES, , Buffer-local variables in spreadsheets, For Gurus
|
|
|
796 @section Uses of defadvice in SES
|
|
|
797
|
|
|
798 @table @code
|
|
|
799 @item undo-more
|
|
|
800 Defines a new undo element format (@var{fun} . @var{args}), which
|
|
|
801 means ``undo by applying @var{fun} to @var{args}''. For spreadsheet
|
|
|
802 buffers, it allows undos in the data area even though that's outside
|
|
|
803 the narrowing.
|
|
|
804
|
|
|
805 @item copy-region-as-kill
|
|
|
806 When copying from the print area of a spreadsheet, treat the region as
|
|
|
807 a rectangle and attach each cell's formula and printer as 'ses
|
|
|
808 properties.
|
|
|
809
|
|
|
810 @item yank
|
|
|
811 When yanking into the print area of a spreadsheet, first try to yank
|
|
|
812 as cells (if the yank text has 'ses properties), then as tab-separated
|
|
|
813 formulas, then (if all else fails) as a single formula for the current
|
|
|
814 cell.
|
|
|
815 @end table
|
|
|
816
|
|
|
817
|
|
|
818 @c ===================================================================
|
|
|
819
|
|
|
820 @node Acknowledgements, , For Gurus, Top
|
|
|
821 @chapter Acknowledgements
|
|
|
822
|
|
53945
|
823 Coding by:
|
|
|
824 @quotation
|
|
|
825 Jonathan Yavner @email{jyavner@@member.fsf.org}@*
|
|
|
826 Stefan Monnier @email{monnier@@gnu.org}
|
|
|
827 @end quotation
|
|
|
828
|
|
|
829 Ideas from:
|
|
47667
|
830 @quotation
|
|
|
831 Christoph Conrad @email{christoph.conrad@@gmx.de}@*
|
|
|
832 CyberBob @email{cyberbob@@redneck.gacracker.org}@*
|
|
|
833 Syver Enstad @email{syver-en@@online.no}@*
|
|
|
834 Ami Fischman @email{fischman@@zion.bpnetworks.com}@*
|
|
|
835 Thomas Gehrlein @email{Thomas.Gehrlein@@t-online.de}@*
|
|
|
836 Chris F.A. Johnson @email{c.f.a.johnson@@rogers.com}@*
|
|
|
837 Yusong Li @email{lyusong@@hotmail.com}@*
|
|
54477
|
838 Juri Linkov @email{juri@@jurta.org}@*
|
|
47667
|
839 Harald Maier @email{maierh@@myself.com}@*
|
|
|
840 Alan Nash @email{anash@@san.rr.com}@*
|
|
|
841 Fran�ois Pinard @email{pinard@@iro.umontreal.ca}@*
|
|
|
842 Pedro Pinto @email{ppinto@@cs.cmu.edu}@*
|
|
|
843 Stefan Reich�r @email{xsteve@@riic.at}@*
|
|
|
844 Oliver Scholz @email{epameinondas@@gmx.de}@*
|
|
|
845 Richard M. Stallman @email{rms@@gnu.org}@*
|
|
53945
|
846 Luc Teirlinck @email{teirllm@@dms.auburn.edu}@*
|
|
47667
|
847 J. Otto Tennant @email{jotto@@pobox.com}@*
|
|
|
848 Jean-Philippe Theberge @email{jphil@@acs.pagesjaunes.fr}
|
|
|
849 @end quotation
|
|
|
850
|
|
|
851 @c ===================================================================
|
|
|
852
|
|
|
853 @bye
|
|
52401
|
854
|
|
|
855 @ignore
|
|
|
856 arch-tag: 10a4ee1c-7ef4-4c06-8b7a-f975e39f0dec
|
|
|
857 @end ignore
|
