sc: the Venerable Spreadsheet Calculator

Boy, there sure is a lot of software for Linux—a lot of software! Why, if you want a browser, you can choose between Firefox, Opera, Chrome, Galeon, Surf and many others. And, on the command line, wget, curl, Lynx, ELinks and more are available. For e-mail, options include Evolution, KMail, Balsa, and xmh; or on the command line, you can use mutt (my favorite), sup, pine, mh and countless more. Calendaring choices include the GNOME calendar, KDE calendar, xcal and Evolution; or, in a terminal, you can use the very powerful Remind or ccal, not to mention command-line interfaces to the Google calendar. And for spreadsheets, you've got OpenOffice.org's OOCalc, Gnumeric, KSpread and Xspread; or, in a terminal, you've got perhaps the best spreadsheet of all, sc, especially if you're a vi fan.

I've been using sc for years, mostly for budgeting and project planning. The earliest version I've found was posted to comp.sources.unix on August 18, 1987, by Robert Bond (see Resources), but that was already version 4.1. The post said it was previously known as vc, and that the original version was written by James Gosling (of Java language fame) in September 1982. Although documentation for sc can be hard to find, it does come with a decent man page and a neat tutorial, which you can load right into sc. It also uses the same file format as Xspread, so existing documentation on formulas in Xspread (which is more plentiful than for sc) also can be helpful.

If you use Debian or Ubuntu, just type sudo apt-get install sc. If your distribution doesn't have an sc package, see Resources for a link to the source. Start the program by typing sc in a terminal, and you'll see a screen that looks something like Listing 1. Because it's curses-based, you can run it over slow links, as well as inside screen, so that you can detach and re-attach from another terminal. There is a pretty detailed man page, which (in the Ubuntu package) points out that you can start up sc with a tutorial by doing this:

sc /usr/share/doc/sc/tutorial.sc

Actually, that isn't quite right. In Ubuntu, first you need to uncompress the tutorial:

sudo gunzip /usr/share/doc/sc/tutorial.sc.gz

Then start it.

sc 7.16:  Type '?' for help.

        A         B         C         D         E
  0
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20

If you prefer to get started immediately with some real data, here are some useful commands. Like vi, sc starts in a command mode. The vi movement keys, hjkl, move left, down, up and right among cells, as you would expect. To jump straight to cell D3, press gD3. You can begin entering a numeric value or formula using =. To interrupt a command gracefully, press Ctrl-G. See the Basic sc Usage in Command Mode sidebar for more simple commands.

To me, the three most important things about working with spreadsheets are the ease of adding new data, moving data and defining simple formulas that are re-calculated automatically. sc shines as far as the first two requirements with its vi-like command mode. It also does quite well with formulas. Check the on-line help for a sizable list of formulas, but the most common function in my experience is simple addition. This is no different in sc from any other spreadsheet.

To put the sum of the values in A3 through A10 into A12, go to cell A12, and type =@sum(A2:A10). To edit the formula, press e for edit, and you will be in command mode in the top line, editing the formula. Edit as you would in vi, and press Return to save the edited formula.

If you want to insert five more rows before row 5, go to cell A5, and type 5ir, which means “do 5 times: insert a row”. The formula (now in A17) will be updated automatically to read @sum(A2:A15). Now you can copy that formula into cell B17 by going to A17, typing ma, then going to B17 and typing ca. The formula will be updated automatically to read @sum(B2:B15).

If you want to highlight some specific data in the spreadsheet without actually having to delete rows, you can hide the uninteresting ones. This is called zapping in sc, and you do it by pressing Z followed by either r for row or c for column. (If you follow it with a Z instead, sc will assume you meant save and exit as ZZ does in vi.) You can un-hide by using S for show, followed again by r or c. Again, you can type 30Zr to zap 30 rows.

I've already mentioned the tutorial and detailed man page, but sc also has on-line help, which you can see by pressing ?. There, you will find settings you can toggle, ways to move the cursor, lists of financial functions and so on.

To save a file, press P followed by a filename, such as budget.sc. To save a plain-text representation, press W followed by a filename, such as budget.asc. I find these particularly useful, not only to paste into an e-mail quickly, but also to look through a set of spreadsheets easily.

You also can output other formats. For instance, to output a LaTeX table to paste into a paper, type S (for set) tblstyle=latex, followed by T (for table output) output.tex. The resulting LaTeX table, of course, also lends itself to unending options for pretty-fication by adding images, fonts, colors and whatnot. For me, plain text almost always is the most useful.

To exchange data with other spreadsheet programs, sc exports in a colon-delimited format. Unfortunately, this exports the results of formulas and not the formulas themselves, but it still can be useful.

You can output colon-separated files in sc by typing S (for set) tblstyle=0, followed by T (for table output) output.cln. Actually, 0 is the default tblstyle, so you need to do only the first step (S), if you selected another format previously (like LaTeX).

To import this in OpenOffice.org's OOCalc, open OOCalc, go to the Insert menu, and choose Select from file. Browse to your output.cln file and select it. You'll get an import screen with a Separator options section. For Separated by, choose Other, and type in a colon. Make sure all other Separated by options are deselected, or it won't work right.

Unfortunately, although sc can export other formats, it does not import them. However, you can work around that. One way is to start by getting CSV output. This should be an option for your on-line bank statement, for instance. From OOCalc, choose Save as→Text CSV format, and click Edit filter setting. If the next pop-up warns you about losing information in this format, click keep current format. In the field options pop-up, unselect Save cell contents as shown; otherwise, numeric values will be placed in quotes. For field delimiter, let's use :, as that's what sc outputs. Let's assume that import.csv is the name of the resulting file.

There probably are several ways to import this data into sc. For instance, sc offers advanced macros you might be able to use. However, I think the simplest way is to convert the CSV file into a valid sc format file. This is easy, because the sc format itself is simple, plain-text—another reason for my fondness of sc.

#!/usr/bin/python

import sys
import string

if len(sys.argv) < 2:
    print "Usage: %s infile [outfile] [delimiter_char]" % sys.argv[0]
    sys.exit(1)

filename_in = sys.argv[1]

if len(sys.argv) > 2:
    filename_out = sys.argv[2]
    outfile = open(filename_out, 'w')
else:
    outfile = sys.stdout

delimiter = ':'
if len(sys.argv) == 4:
    delimiter = sys.argv[3][0]
    print 'using delimiter %c' % delimiter

infile = open(filename_in, 'r')

letters = string.ascii_uppercase
text = ["# Produced by convert_csv_to_sc.py" ]
row=0
for line in infile.readlines():
    allp = line.rstrip().split(delimiter)
    if len(allp) > 25:
        print "i'm too simple to handle more than 26 many columns"
        sys.exit(2)
    column = 0
    for p in allp:
        col = letters[column]
        if len(p) == 0:
                continue
        try:
            n = string.atol(p)
            text.append('let %c%d = %d' % (col, row, n))
        except:
            if p[0] == '"':
                text.append('label %c%d = %s' % (col, row, p))
            else:
                text.append('label %c%d = "%s"' % (col, row, p))
        column += 1
    row += 1

infile.close()
outfile.write("\n".join(text))
outfile.write("\n")
if outfile != sys.stdout:
    outfile.close()

The Python script in Listing 2 simply walks over the CSV values one by one, writing out sc commands to insert text and numeric values. Note how easy it also would be to insert formulas, if CSV supported them. Run this script by typing:

python c.py import.csv import.sc

If your CSV file was separated by a character other than a colon, for instance, a comma, add the delimiter as the last option:

python c.py import.csv import.sc ','

Now, open the spreadsheet with:

sc import.sc

Voilà, your on-line bank statement or simple OpenOffice.org spreadsheet is now open in sc.

You can take this one step further and turn c.py into an automatic plugin. Be warned, however, that this support isn't perfect. To do so, place a copy into .sc/plugins/. Then, add a line to $HOME/.scrc that reads:

plugin "cln" = "c.py"

Now, any time you open a file in scn with a .cln extension using G (for get), it will be filtered through c.py, and sc will take its input from the plugin's standard output. Unfortunately, this support apparently was rarely used and is not well implemented. Specifying a .cln file on the command line (sc r.cln) will not invoke the plugin, so you must start sc with no files, and use the G command to load the file. Also, if you save the file later, it will use r.cln as the default filename but save an sc format file. So, if you use a plugin format, you'll need to specify a corresponding plugout script (let's call it cout.py) as well, and add a line to $HOME/.scrc that reads:

plugout "cln" = "cout.py"

sc has a few other neat features. For instance, it can support automatic encryption of spreadsheet files. However, the Ubuntu package is not compiled with that support, and when compiling a version with it, it's clear that no one has tried it in some time, as it required some patching. To support encryption, sc simply passes the output files through /usr/bin/crypt, which asks for a passphrase when you (P)ut the file. Therefore, I prefer using sc in a directory in an eCryptfs filesystem (and with encrypted swap), so that all files I produce are encrypted.

sc also supports color cells. You can get pretty fancy and have foreground and background colors calculated with any function sc supports—meaning that cell value, row and column, time of day or even external functions (see below) can determine the cell color. Tell sc to begin using color by typing ^T-C (Ctrl-T, for toggle, followed by C, for color). If you save the sheet after this, the command “set color” will be saved, and the sheet will be loaded with colors. There are eight color pairs, whose foreground and background values you can define using C. For instance, type C followed by color 1 = @red;@black, which defines color 1 to be foreground red with background black. The default color combinations are shown in the sc man page.

You can use these colors in a few simple ways. If you type ^T-N, cells with negative values will have their color value incremented by 1—for instance, if the cell would have been color 3, it will be shown in color 4. If you type ^T-E, cells with error values will be shown in color 3. To assign color 4 to the range A0:D5, type rC (range color) followed by A0:D5 4. Finally, to see what colors you have assigned to cells, type rS.

A great number of functions are available in sc, but if you find you need something more exotic, you can implement them in C, Python or whatever your poison, and use them as external functions. Type ^Te to enable external functions. Then, write your function so as to take input from standard input and send output to standard out. For instance, put the following in a file called bci.sh:

#!/bin/sh
echo $* | bc -ql

And, make it executable:

chmod ugo+x bci.sh

Now in sc, enter values in A0 and B0, then set C0 to @ston(@ext("./bci.sh",A0+B0)). The @ston function will convert the string returned by bci.sh to a number.

I've not run into this myself, but it is conceivable that with enough external functions in a large enough spreadsheet, re-calculation could start noticeably slowing things down. In that case, you can stop automatic re-calculation by typing ^T-a. After that, the sheet will be re-calculated only when you press @.

Similarly to external functions, sc also supports simple and advanced macros. A simple macro is a text file containing regular sc commands. You can run it by typing R, or ask for it to be run automatically whenever you load a file by using A. Advanced macros are executable files that communicate with sc over a pipe. In this way, they actually can request information from sc. You call an advanced macro by typing R and then preceding the filename with |. The only decent documentation I've seen for this is the SC.MACROS file included with the source code. The following macro is a simple (and useless) example of an advanced macro. Put the following in the file $HOME/.sc/macros/down.sh, and make it executable:

#!/bin/bash
echo down

Start up sc, and type R (run) |~/.sc/macros/down.sh. Note that the | preceding the filename indicates that this is an advanced macro. When you run this macro, the cursor will move down a cell. In other words, sc reads the “down” output by the echo command and executes it as a command (see SC.MACROS for a list of commands you can use).

If you will be using a lot of macros, you might want to use the D command to define a path under which sc should search for them. You also could define a function key to run a frequently used macro. For instance, add the following to your .scrc to cause F2 to call the down.sh macro:

fkey 2 = "merge \"|~/.sc/macros/down.sh\""

Now, you never need to type j again!

There still are more features that, like the ones listed in this section, I don't much use myself, but I could see them being useful. You can toggle ^T-$ to make all numeric values be interpreted as cents. And, you can configure newline actions, so that as you enter values, when you've entered the last column, you automatically are moved to the first cell of the next row. For me, these fall under the category of needing more thought to figure out whether and how to use them than just using the default, so I don't use them, although I keep meaning to try the last one. The man page and help pages can point you to more, and they're probably worth looking at to see which ones you would find useful.

Linux users have many options for spreadsheets, not to mention Web-based ones, including Google Docs spreadsheets. But, most people probably would be stumped if asked for a spreadsheet that can be used in a terminal. sc is one of the oldest FOSS spreadsheets. It's been available for more than 20 years, and it's terminal-based, with keybindings that should be familiar to any vi user. It supports advanced macros, plugins and external functions, and it can export to its own format, plain text, LaTeX or CSV for easy input to other spreadsheets.