Prologue
WTF!
Weaker natures might be taken aback by the statement in the prologue… Spreadsheet - in Emacs...? Well - what those people don't afford anymore, do they?The very idea of a spreadsheet in text mode seems strange. But it wasn't always so, and of course it all started long ago when text (and only text) mode was the domain of professional computer use. And vice versa - the small 8-bit only a graphics mode only, which the text mode could emulate, but due to the low resolution just poorly and unsuitable for long term work.
Just a quick mention that Microsoft Excel or Libre(Open)Office Calc weren't here from beginning. Lotus 1-2-3 was very common in the past, I worked for years with Borland's Quattro Pro, which did have a graphics mode, but the computers of the time were "breathing hard" when working with it, so this one was only suitable for working with graphs. Apple ][ users had Visicalc and several graphics modes, including monochrome, which did a good job of getting the characters to the desired sharpness. And so on. Later history, which is not the subject of this article, has seen mostly only graphical programs, such as the Czech Calc602,… and then the classics - MS Excel, Gnumeric, KSpread and of course Libre(Open)Office Calc or ONLYOFFICE Spreadsheet Editor.
Fig. 0: Lotus 1-2-3 running in console
Fig. 1: Less known Quattro Pro, in text and graphics mode
In the GNU/Linux world, there are, of course, also console-friendly spreadsheets. Probably the best known is SC, but also Teapot, which allows you to add a third dimension to spreadsheets and thus make them very uncluttered (but again, this can also be seen as an additional layer of data related to the current cell, which we would otherwise have to keep in a separate table or sheet).
Fig.2: SC for current world
So text-mode spreadseets are not that exotic and certainly not unusable. For example, I create a lot of spreadsheets in my working life - but again, charts are minimal, so I don't need a graphics mode.
And - how good would it be to stay in the warm embrace of GNU Emacs.
SES - Simple Emacs Spreadsheet
And we will remain in that embrace.Personally, I create a lot of tables in the well-known org mode. It allows you to draw tables automatically, whose lines, cells,… dynamically adapt to the content (so no laborious drawing of ASCII characters). It can certainly perform numerical operations on data, using built-in functions, custom formulas and also elisp functions derived from the powerful calc mode.
The advantage is that these tables are nicely integrated into the rest of the text, their export can be suppressed (so for example I have calculations directly in the business offers, but these are not exported and thus not seen by those who are not supposed to see them). They can work with gnuplot and also with legder accounting extensions. And there is more. Basically they have no ailment, no disadvantage.
Except that it doesn't look like a spreadsheet environment.
Fig.3: Table in org mode
But to make things even better, Emacs also offers us something extra. Finally, I'm getting to the Simple Emacs Spreadsheet. And it is indeed "simple", yet powerful. Let the kind reader judge for himself.
Data entry
We create a file with the extension .ses, which automatically invokes start of right mode.We enter data into the table - well, just classically, with one difference, that we enter text strings with the ".
Some useful keyboard shortcuts and commands:
Keybinding Command Description C-o ses-insert-row inserting the row M-o ses-insert-column inserting the column C-k ses-delete-row deleting the row M-k ses-delete-column deleting the column w ses-set-column-width set the width od the column TAB ses-forward-or-insert move the cursor to the cell to the right C-j ses-append-row-jump-first-column adding row and jump the the 1st cell of that row
Ehm - I guess it should be mentioned that columns are marked with letters and rows with numbers. Isn't that perfectly normal? Sure it is, although in org-mode you can also use @row$column addressing.
Mentioning the fact, that cell addresses and references to cells in formulas change automatically, when cells are inserted is probably unnecessary.
Fig.4: Filling the table
"Printer" functions
Is a somewhat unusual name for formatting. A prompt to specify appears when you press p.What are our options?
Command Function ses-center centering with visual cropping of data by column width ses-center-span centering, but this time with an overlap to the next cell ses-dashfill centering, but instead of the space charater "-" is used ses-dashfill-span detto, but again with long text overlap ses-tildefill centering, but instead of the space character "~" is used ses-tildefill-span detto, but again with long text overlap nil default right alignment -custom function- defining custom formatting
Defining custom formatting
Here we get to thinner ice, which might be too thin for someone and to avoid an ice bath, they'd rather quickly escape the SES environment. ☺
Defining custom formatting can be like this, for example:
(ses-define-local-printer
'euro
(lambda (udaj)
(cond
((null udaj) "")
((numberp udaj) (format "%.2f €" udaj))
(t (ses-center-span udaj ?# 'ses-prin1)))))
Quick description: if udaj is equal to the empty string, nothing happens; if a number is given, it formats udaj with the defined format; if there is a type other than number, it displays it centered with the # sign around it.
And where to enter it?
As so often in life, the truth is hidden beneath the surface. In Emacs, there is a system for narrowing and widening the view; the narrow-to* and widen functions are used to do this. Of course, suppressed (invisible) text and data is not lost, it's just not viewable.
If we call widen in our .ses document, e.g., using M-x or the C-x n w shortcut, the rest of the buffer, which holds the actual cell contents and formatting commands and properties, is revealed.
And it is under these that we can put our own functions.
Fig.5: Narrow and widen (point of…)
Fig.6: Custom function at the end of file.
Formulas
Without formulas, a spreadsheet would be useless. SES uses the Lisp infix syntax, which is apparently not entirely common. I don't find difficult to switch from postfix notation, since I exclusively use RPN calculators (where operators are entered after operands) to another one, even infix or "classical algebraic".We simply enter the formula for adding the 2nd column by typing
(+ B2 B3 B4)
The other option is to use the built-in function:
(ses+ B2 B3 B4)This one is also more "robust" and ignores illogical entries, for example if there is text or even nothing in a column, it simply skips it when summing.
And thirdly by specifying a range:
(apply 'ses+ (ses-range B2 B4))
Evaluate it backwards: (ses-range B2 B4) will produce a list of values. We apply the ses+ function (or some other function) to this. Finally, apply is a function that takes a function (ses+) as the first argument and a list of values as the second argument. The odd character ' says: "ses+ is a symbol and will not evaluate to a value, but instead will be used as a reference to the ses+ function" (to simplify, we'll tell it that ses+ is not the word "ses+" but the eponymous function).
This approach will certainly scare people away - compared to "classic" programmes. But it's just a matter of habit.
Of course, nothing prevents us from making a macro or wrapper function for this:
(defun sum (start end)
"Returns the sum of the values from START to END."
(apply 'ses+ (ses-range start end)))
We use this function like this:
(sum B2 B4)
And it's already more cheerful. And immediately we see that we are no longer and will no longer be limited by the functions offered by the program itself.
Cell selection can also be done with the mouse, but who would bother with such a stochastic device 🙄.
In addition, SES can still arrange the data in columns.
Export + import
In the 1990s, this phrase was often used in company names here in Slovakia 😉. Simply, after the miserable time of socialism, when it was impossible to trade normally with foreign countries, every owner of his company wanted to proudly show that he could leave the limited world of a small country. 😃In SES it is like this:
Keyboard Shortcut Alt. command Function x t (ses-export-tsv) Exports the values in a table (or range) as TAB-separated data. x T (ses-export-tsf) Exports formulas in a table (or range) as TAB-separated data.
The exported text is moved to the kill-ring.
Columns are separated by tabs, rows by newlines.
The import is done by pasting the data from the clipboard, if it contains tabs or new rows, it will be correctly inserted into the table.
The (inserted) table can also be easily converted to org mode using org-table-covert-region.
| Verzia jadra | Počet súborov | Formátovaný typ | Suma všetkých buniek | | 1.0 | 561 | 20.555 | 3 | | 2.0 | 2048 | 10.5 | 10 | | 2.6.0 | 17784 | 20.3 | 1 | | | | | | | Σ | 20393 | 51.355 | 20458.355 | | ---------Σ €-------- | | | 20444.355 | | Verzia jadra | Počet súborov | Formátovaný typ | Suma všetkých buniek | | 1.0 | 561 | 20.555 | 3 | | 2.0 | 2048 | 10.5 | 10 | | 2.6.0 | 17784 | 20.3 | 1 | | | | | | | Σ | (apply 'ses+ (ses-range B2 B4)) | (sum C2 C5) | (apply 'ses+ (ses-range B2 D4)) | | ---------Σ €-------- | | | (sum B2 D5) |
A few notes at the end
Cells can also be named using ses-rename-cell and then accessed (or moved to and also viewed and used when entering formulas) using this identifier.It is also possible to have the cell names placed in the first row (A B C D ...) instead of the first (header) row (A B C D ...). This is done using ses-set-header-row.
Data area (which are normally hidden)
This starts with the "form's scroll character" (whose ASCII code is 014 in octal notation), followed by a set of macros and cell definitions for each row, followed by a set of cell formatting definitions, followed by column widths, format, and row header. Then there are global parameters (file format ID, number of rows, number of columns, number of local printers) and local variables.
When an SES file is loaded, the global parameters are loaded first, then the entire data area is evaluated, and finally the local variables are processed.
The data area can be edited, but no newlines should be inserted or removed except for the local variables section, because SES localizes things by counting newlines. Using C-x C-e at the end of a line moves the edits to the table data structures, but does not yet update the print area; this must be done manually with C-c C-l.
But of course it's better not to interfere and leave it to Emacs.
And that's all. A few more details can be found at https://www.gnu.org/software/emacs/manual/html_mono/ses.html.
Epilogue
Simple Emacs Spreadsheet is certainly not a heavyweight tool, nor does it try to be. It doesn't handle bell and whistles like framing, font changes, hyperlinks, images.But that doesn't mean it's useless. It's just what it was designed to do: quickly organize data in a spreadsheet, along with the appropriate numerical operations. Linking with the built-in calc mode allows it to perform even more difficult calculations, from differential calculus to statistics.
And since we are still in one environment, we can easily link it with other structures and modes, like email or zettelkasten…