sc: the Venerable Spreadsheet Calculator

 in
If you like vi, and you like the command line, you will love sc—a spreadsheet that runs in a terminal.
Advanced Usage

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.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Code patch with indentation corrected

Ronald Greenberg's picture

Oops, I didn't realize the code would be reformatted. Should have used the right HTML tags to keep the indentation:

        col = letters[column]
        if len(p) != 0:
            try:
                n = string.atof(p)
                text.append('let %c%d = %g' % (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

Improvement to script for empty cells

Ronald Greenberg's picture

The script skips over empty cells as if they didn't exist. It's fine to not put anything in the .sc file for an empty cell, but one should move over to the next column so that empty cells are preserved. A fix is to replace the body of the "for p in allp" with this:
col = letters[column]
if len(p) != 0:
try:
n = string.atof(p)
text.append('let %c%d = %g' % (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
(I've aso applied the fix suggested in the previous comment.)

R.

Great article. Problem in Python script:(

dfranz's picture

Just on the day I started exploring sc on my Nanonote,
which was highligted in the same issue, I discovered this
really helpful article via web search. When my October
issue came in, I'm sure I saw the article, but it did
not catch my attention then:)

My quick review of sc revealed that there was no feature
to import from other formats. The Python script looked
like it solved the problem, but on my first test I found
that it treated all numbers containing a decimal point
as labels--not what I wanted. Changing the call
to string.atol() to string.atof() and changing
the format "let %c%d = %d' to 'let %c%d = %g'
solved the problem.

Now on to exploring sc on the Ben Nanonote. Thanks
for a timely article.

Delbert

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix