One of the coolest features new to sc 7.3 is the ability to write advanced macros in your favorite language. I haven't had time to document this feature properly (I'm hoping to do a separate man page), but until I can, I decided to write a basic overview of how this feature works, and let people experiment with it. Any comments, questions, or suggestions are welcome. Basically, the way the new macros appear to sc is no different than the old style macros, apart from the additional commands. On the other side, however, they are as different as night and day. The old style macros are basically just text files containing sc commands in the same form that they would be stored in a plain spreadsheet file. There is no difference between reading a macro file and reading a spreadsheet file, and they can both contain the same commands. This is very limiting because there is no way to create a loop or branch to another part of the macro or execute certain commands only under specific conditions. Advanced macros allow you to do all this and more. An advanced macro is nothing more than an executable program that outputs sc commands on stdout and reads information back in on stdin. All decision-making, looping, etc. is done within the macro program, rather than within sc. This means that you have all the power of whatever language you prefer when programming macros. A few additional commands have been added to request specific data from sc. I call these "pipe commands", since they cause sc to pipe data back to the macro program. The pipe symbol (|) that used to precede these commands is no longer necessary, starting with version 7.13. The command is echoed or printed to stdout, and the result is read from stdin in the form of a newline-terminated string. If the result contains multiple values, they will be separated by spaces. Most of these commands take an optional argument specifying a cell or column you are requesting information from. If this argument is missing, the current cell or column will be used. A few more commands have been added to do things that are easily done from the keyboard, but which previously had no specific commands that could be used to do them from a macro file. These include such things as moving around the spreadsheet using h, j, k, l, or the cursor control keys, or forcing recalculation of the spreadsheet using @. Here is a list of the new commands, along with a description of each one: Pipe Commands: -------------- whereami This command tells sc that you would like to know where you currently are in the spreadsheet. The response will be the address of the cur- rent cell followed by the address of the cell in the upper left-hand corner of the screen, separated by a space. If this line is appended to the string "goto ", it can be used to restore the cell cursor to its original position. It can also be parsed to find out which row and column you're in, which can be used for whatever purpose you want. getnum This command tells sc that you would like the number contained in a given cell. Each cell in sc can contain both a numeric portion and a string portion, and one of those, but not both, can be in the form of an expression. This command requests the numeric portion. If the numeric portion is an expression, this will return the calculated value, rather than the expression. The result will be returned in the form of a newline terminated string formated with "%.15g", which is the same format that is used to store a numeric value in a spreadsheet file, unless it's in the form of an expression (remember, sc files are text files). When followed by a cell address (e.g., `getnum b23'), it will return the number from that cell. Specifying a range instead will return the number from each cell in the range, one row per line, with the values separated by tabs (an empty cell will be represented by two consecutive tabs with nothing between). With no arguments, the number from the current cell will be returned. If a cell contains an error, the string "ERROR" or "INVALID" will be returned. fgetnum This command works exactly like getnum, except that the format used to return the value is the same as that used to format it for display on the screen. In other words, if the cell uses a date format, a date will be returned. If the cell is formatted for scientific notation, that's what you'll get back. The only exception is that if the for- matted result happens to be wider than the cell, you won't get a string of *'s back. Instead, you'll get a result that is wider than the cell it's contained in. Like getnum, you can use this by itself to get the number from the current cell, or with a cell address or range as an argument. If a cell contains an error, the string "ERROR" or "INVALID" will be returned. getstring Like getnum and fgetnum above, this command requests data from sc. However, what is requested (and returned) is the string portion of the cell. If the string portion of the cell is an expression, it will be evaluated, and the result of that evaluation will be returned. getstring may be used to get the data from the current cell or from a specified cell or range, just like getnum and fgetnum above. getexp If either the numeric portion or the string portion of a cell is in the form of an expression, this will return that expression. getformat This command will return a string containing the three numeric values that specify the format of the given column, or the current column if none is given. This is the format specified by the f (format) command. getfmt This command will return the format string for the specified cell (or cells, if a range is specified), or for the current cell if no arguments are given. This is the format specified by the F (fmt) command. It may be either a standard numeric format or a date format. getframe This command will return the outer and inner ranges, respectively, of the framed range containing either the specified cell, if given, or the current cell, otherwise, separated by a space. If the cell is not inside a framed range, an empty string will be returned (in other words, just a lone newline). getrange This command takes a string argument and checks to see if a named range exists with that name. If it does, the actual range is returned. Otherwise, an empty string is returned. status This command will return a set of flags to show information about the current state of the program or the file. Currently, there are three flags implemented: m If the string returned contains an `m', the file currently in memory has been modified. If no `m' is present, the file has not been modified. i If the string returned contains an `i', stdin is currently connected to a terminal. Otherwise, stdin has been redirected to a file or pipe. o If the string returned contains an `o', stdout is currently connected to a terminal. Otherwise, stdout has been re- directed to a file or pipe. query This command can be used to obtain information from the user. An optional string argument will be displayed on the second line of the display to ask the user a question or present an informational message. For example, 'query "Please enter today's sales."' will display the message on the second line, and wait for the user to enter the appropriate information on the top line. If a second string argument is present, it will be used as a default response which the user can accept as is, or edit. The user may switch from insert mode to edit mode, navigate mode, etc., and may use any of the vi-style editing commands or operations that are available during input of regular sc commands, including the use of the command line history. getkey This command can be used to get a single key from the user. For special keys, such as Insert, Delete, function keys, cursor keys, etc., a NULL character will be returned, followed by a newline- terminated string naming the key that was pressed. This name is the same as that found in curses.h with the "KEY_" prefix and any embedded parentheses removed. For example, the cursor right key is "RIGHT", the Insert key is "IC", and the F4 function key is "F4". error Displays a specified string on the second line of the display. The string argument is required, but may be empty (""). This command is intended for displaying error messages from a macro, but may be used to display other informative messages as well. eval This can be used to send an expression directly to sc for evaluation without entering it into a cell. An optional second parameter can be used to specify formatting information. For example, the following line could be used in a shell script: echo eval a49-c53 \"0.00\" You can then read the result in from standard input. If you want to know the number of the current column without having to convert the column name yourself, you can let sc do it for you by sending the command "eval @mycol" and reading the answer back from sc. You can also use this in non-macro shell scripts. For example, the following line could be used to calculate the area of a circle to four decimal places: AREA=`echo eval @pi*$RADIUS^2 \"0.0000\" | sc -q` seval This works like eval, except that it evaluates string expressions instead of numeric expressions. For example, the following line could be used in a shell script to convert a column number to its name (e.g., 5 would be converted to F): echo "seval @coltoa($COLNUM)" The quotes are necessary in this case to prevent the shell from using the parentheses for its own purposes. Other Commands: --------------- up down left right These do just what you would expect. They move the cell cursor in the specified direction. You can also use an optional numeric argu- ment to move the specified number of cells in the given direction. For example, `down 7' will take you to the cell seven rows below the current cell. endup enddown endleft endright These also do what you would expect. For example, if you're in the middle of a long column of data, and you would like to jump to the bottom of the column, but you don't know where the column ends, using the enddown command will take you there. This works exactly like the END key (or ^E) followed by a cursor movement key. insertrow insertcol openrow opencol deleterow deletecol yankrow yankcol These commands insert, delete, or yank rows or columns, just as if the user had pressed `i', `o', `d', or `y'. If you want to insert, delete, or yank more than one row or column, follow the command with `*' and a number; e.g. `insertrow * 5' will insert five rows before the current row. pull pullmerge pullrows pullcols pullxchg pulltp pullfmt pullcopy These commands pull cells (or parts of cells in the case of the pullfmt command) from the delete buffer into the current location in the spreadsheet. They perform the same actions as the `pp' (pull), `pm' (pullmerge), `pr' (pullrows), `pc' (pullcols), `px' (pullxchg), `pt' (pulltp), `pf' (pullfmt), and `pC' (pullcopy) user commands. leftjustify rightjustify center These commands are used to justify or center the string(s) in a cell or range of cells. With no argument, all strings in the currently highlighted range, if one is highlighted, or the current cell, if not, will be justified/centered. Otherwise, a cell or range may be given as a single argument, which will define which strings are to be justified or centered. select This command takes a single string argument, whose first character is used to select a named buffer to be used for the next insertion, deletion, yank, or pull. It is the same as the interactive `"' command. recalc This works like the @ command. It forces recalculation of the spreadsheet. Note that automatic recalculation is turned off tem- porarily while executing a macro (for speed), so you will need to use this command if you want to present current data to the user before the macro is complete. You will also need to use the redraw command to write the recalculated data to the screen. Since recal- culation is turned back on after the macro is complete, this command will not be necessary at the end of a macro. redraw This command works like ^L, and redraws the screen. You may have to use the recalc command first if you want the data to be current. Note that screen updates are not performed during macros (for speed), so you'll have to use this command if the data have changed in any way, and you want the user to see those changes. If your macro writes directly to the screen at any time to display messages or otherwise interact with the user, you will need to use this command to restore the spreadsheet to the screen when the macro ends. quit This command causes sc to immediately exit. It does not prompt the user for confirmation or ask if the data should be saved if it has been modified. You will need to do that from the macro, if necessary. Use this command with caution. Now that you understand the new commands (and hopefully the old ones, too, although you'll have to figure those out on your own; hint: watch the top of the screen as you enter data and execute other commands, and look at the contents of a few spreadsheet files), a few hints are in order. First, it is perfectly okay to write directly to the screen, although, since stdin and stdout have been redirected, you'll have to use another method of doing this. From a shell script, for example, you can redirect stdin and/or stdout to /dev/tty on a command by command basis. Remember that even a command like clear sends special codes to do its job, so you'll need to use redirection to make it work. For example, to clear the screen, use `clear >/dev/tty'. If you have the dialog or cdialog program, you can use these to interact with the user. Just redirect stdin and stdout with `<>/dev/tty' for each dialog command (or what might be better is to open a file descriptor once with something like `exec 3<> /dev/tty' and use that for communicating with the user). You can also use the tput, echo, and read commands with redirection to read and write the top two lines of the screen for inter- action with the user. For example, `(tput cup 0 0; tput el) >/dev/tty' will position the cursor on the top line and clear the line. You can then use the echo and read commands to ask the user a question and read the answer. Just remember to redraw the screen after using any commands that write directly to the screen (now that the query command has been implemented, you probably won't be doing it this way, but the capability is there, anyway). Although the examples above are for shell scripts, similar methods can be used from any language. I'll leave the exact implementation for various other languages as an exercise for the reader. Testing macros outside of sc in most cases is very easy, since they read and write stdin and stdout. All you have to do is run the program and feed it information from the keyboard that it would otherwise get from sc. Once you've written and tested your macro, you can try it from within sc. Make sure the file is executable, and then use the R command to run it. Make sure you precede the name with a `|'. You can use D to define the macro directory, although this no longer needs to actually be a directory. It can be the name of your macro file, including the path, preceded by a `|' so that it will be executed as a program. If you include a trailing space, you can then add command line arguments or options when running it with R. This allows you to include several macros in the same file, and use the command line to determine which macro to run. Alternatively, you could use the whereami command to determine where the user is at in the spreadsheet, and run a different macro depending on which region of the spreadsheet the macro is being run from. Use your imagination. Since the macro is a program, you can import data from any source you want. You could get information from the Internet, read it from custom hardware connected to the serial or parallel port (or your own custom interface), or pull it from a file created by another program. You could cause your macro to react to the phase of the moon or whether the date is even or odd, if you want (although your users might not like that very well). Basically, you can do anything you want. I'd be interested in hearing of some creative uses of macros. Here's some additional information about the pipe commands. Although it is no longer necessary to include the pipe symbol at the beginning of the "pipe" commands, you may still append it to any of these commands, follosed by file descriptor as before (e.g. `whereami | fd', where fd is a file descriptor). I've used this for testing by using a file descriptor of 1, which will write the information to the screen, or a file descriptor of 2, which will write the information to stderr, which I then redirect to another virtual terminal or a file. This may also be used in a pipe- line to pass data from sc on to the next command (or to a file, through redirection). For example, if you create an sc spreadsheet on the fly and pipe it to sc, you could add the following lines to the end of the spreadsheet: getfnum D49:G73 | 1 quit This will cause the formatted numeric data from the range D49:G73 to be piped to the next command, tab-delimited, one row per line. Also, if you're using simple macros (the old-style "list of sc commands in a text file"), no pipes are created, and the default file descriptor for the pipe commands is 1 (or stdout). Make sure you include the quit command, or sc will become interactive after receiving all of the data from the pipeline. If anyone finds another use for this way of using the pipe commands, please let me know, and I'll add it to the documentation in the next version. I'm hoping to eventually include better documentation for macros, preferably in a man page. In the meantime, enjoy the new capability, and send me any comments or suggestions for the next release. Chuck nrocinu@myrealbox.com