1One of the coolest features new to sc 7.3 is the ability to write advanced 2macros in your favorite language. I haven't had time to document this 3feature properly (I'm hoping to do a separate man page), but until I can, 4I decided to write a basic overview of how this feature works, and let 5people experiment with it. Any comments, questions, or suggestions are 6welcome. 7 8Basically, the way the new macros appear to sc is no different than the 9old style macros, apart from the additional commands. On the other side, 10however, they are as different as night and day. The old style macros are 11basically just text files containing sc commands in the same form that they 12would be stored in a plain spreadsheet file. There is no difference between 13reading a macro file and reading a spreadsheet file, and they can both 14contain the same commands. This is very limiting because there is no way 15to create a loop or branch to another part of the macro or execute certain 16commands only under specific conditions. 17 18Advanced macros allow you to do all this and more. An advanced macro is 19nothing more than an executable program that outputs sc commands on stdout 20and reads information back in on stdin. All decision-making, looping, etc. 21is done within the macro program, rather than within sc. This means that 22you have all the power of whatever language you prefer when programming 23macros. 24 25A few additional commands have been added to request specific data from 26sc. I call these "pipe commands", since they cause sc to pipe data back 27to the macro program. The pipe symbol (|) that used to precede these 28commands is no longer necessary, starting with version 7.13. The command 29is echoed or printed to stdout, and the result is read from stdin in the 30form of a newline-terminated string. If the result contains multiple 31values, they will be separated by spaces. Most of these commands take an 32optional argument specifying a cell or column you are requesting information 33from. If this argument is missing, the current cell or column will be used. 34 35A few more commands have been added to do things that are easily done from 36the keyboard, but which previously had no specific commands that could be 37used to do them from a macro file. These include such things as moving 38around the spreadsheet using h, j, k, l, or the cursor control keys, or 39forcing recalculation of the spreadsheet using @. Here is a list of the 40new commands, along with a description of each one: 41 42 43Pipe Commands: 44-------------- 45 46 whereami 47 48 This command tells sc that you would like to know where you currently 49 are in the spreadsheet. The response will be the address of the cur- 50 rent cell followed by the address of the cell in the upper left-hand 51 corner of the screen, separated by a space. If this line is appended 52 to the string "goto ", it can be used to restore the cell cursor to 53 its original position. It can also be parsed to find out which row 54 and column you're in, which can be used for whatever purpose you want. 55 56 getnum 57 58 This command tells sc that you would like the number contained in a 59 given cell. Each cell in sc can contain both a numeric portion and a 60 string portion, and one of those, but not both, can be in the form of 61 an expression. This command requests the numeric portion. If the 62 numeric portion is an expression, this will return the calculated 63 value, rather than the expression. The result will be returned in the 64 form of a newline terminated string formated with "%.15g", which is 65 the same format that is used to store a numeric value in a spreadsheet 66 file, unless it's in the form of an expression (remember, sc files are 67 text files). When followed by a cell address (e.g., `getnum b23'), 68 it will return the number from that cell. Specifying a range instead 69 will return the number from each cell in the range, one row per line, 70 with the values separated by tabs (an empty cell will be represented 71 by two consecutive tabs with nothing between). With no arguments, 72 the number from the current cell will be returned. If a cell contains 73 an error, the string "ERROR" or "INVALID" will be returned. 74 75 fgetnum 76 77 This command works exactly like getnum, except that the format used 78 to return the value is the same as that used to format it for display 79 on the screen. In other words, if the cell uses a date format, a date 80 will be returned. If the cell is formatted for scientific notation, 81 that's what you'll get back. The only exception is that if the for- 82 matted result happens to be wider than the cell, you won't get a string 83 of *'s back. Instead, you'll get a result that is wider than the cell 84 it's contained in. Like getnum, you can use this by itself to get the 85 number from the current cell, or with a cell address or range as an 86 argument. If a cell contains an error, the string "ERROR" or "INVALID" 87 will be returned. 88 89 getstring 90 91 Like getnum and fgetnum above, this command requests data from sc. 92 However, what is requested (and returned) is the string portion of 93 the cell. If the string portion of the cell is an expression, it will 94 be evaluated, and the result of that evaluation will be returned. 95 getstring may be used to get the data from the current cell or from 96 a specified cell or range, just like getnum and fgetnum above. 97 98 getexp 99 100 If either the numeric portion or the string portion of a cell is in the 101 form of an expression, this will return that expression. 102 103 getformat 104 105 This command will return a string containing the three numeric values 106 that specify the format of the given column, or the current column if 107 none is given. This is the format specified by the f (format) command. 108 109 getfmt 110 111 This command will return the format string for the specified cell 112 (or cells, if a range is specified), or for the current cell if no 113 arguments are given. This is the format specified by the F (fmt) 114 command. It may be either a standard numeric format or a date format. 115 116 getframe 117 118 This command will return the outer and inner ranges, respectively, 119 of the framed range containing either the specified cell, if given, 120 or the current cell, otherwise, separated by a space. If the cell 121 is not inside a framed range, an empty string will be returned (in 122 other words, just a lone newline). 123 124 getrange 125 126 This command takes a string argument and checks to see if a named range 127 exists with that name. If it does, the actual range is returned. 128 Otherwise, an empty string is returned. 129 130 status 131 132 This command will return a set of flags to show information about 133 the current state of the program or the file. Currently, there are 134 three flags implemented: 135 136 m If the string returned contains an `m', the file currently 137 in memory has been modified. If no `m' is present, the 138 file has not been modified. 139 140 i If the string returned contains an `i', stdin is currently 141 connected to a terminal. Otherwise, stdin has been redirected 142 to a file or pipe. 143 144 o If the string returned contains an `o', stdout is currently 145 connected to a terminal. Otherwise, stdout has been re- 146 directed to a file or pipe. 147 148 query 149 150 This command can be used to obtain information from the user. An 151 optional string argument will be displayed on the second line of 152 the display to ask the user a question or present an informational 153 message. For example, 'query "Please enter today's sales."' will 154 display the message on the second line, and wait for the user to 155 enter the appropriate information on the top line. If a second 156 string argument is present, it will be used as a default response 157 which the user can accept as is, or edit. The user may switch 158 from insert mode to edit mode, navigate mode, etc., and may use any 159 of the vi-style editing commands or operations that are available 160 during input of regular sc commands, including the use of the 161 command line history. 162 163 getkey 164 165 This command can be used to get a single key from the user. For 166 special keys, such as Insert, Delete, function keys, cursor keys, 167 etc., a NULL character will be returned, followed by a newline- 168 terminated string naming the key that was pressed. This name is 169 the same as that found in curses.h with the "KEY_" prefix and any 170 embedded parentheses removed. For example, the cursor right key 171 is "RIGHT", the Insert key is "IC", and the F4 function key is "F4". 172 173 error 174 175 Displays a specified string on the second line of the display. The 176 string argument is required, but may be empty (""). This command 177 is intended for displaying error messages from a macro, but may be 178 used to display other informative messages as well. 179 180 eval 181 182 This can be used to send an expression directly to sc for evaluation 183 without entering it into a cell. An optional second parameter can 184 be used to specify formatting information. For example, the following 185 line could be used in a shell script: 186 187 echo eval a49-c53 \"0.00\" 188 189 You can then read the result in from standard input. If you want to 190 know the number of the current column without having to convert the 191 column name yourself, you can let sc do it for you by sending the 192 command "eval @mycol" and reading the answer back from sc. You can 193 also use this in non-macro shell scripts. For example, the following 194 line could be used to calculate the area of a circle to four decimal 195 places: 196 197 AREA=`echo eval @pi*$RADIUS^2 \"0.0000\" | sc -q` 198 199 seval 200 201 This works like eval, except that it evaluates string expressions 202 instead of numeric expressions. For example, the following line 203 could be used in a shell script to convert a column number to its 204 name (e.g., 5 would be converted to F): 205 206 echo "seval @coltoa($COLNUM)" 207 208 The quotes are necessary in this case to prevent the shell from 209 using the parentheses for its own purposes. 210 211 212Other Commands: 213--------------- 214 215 up 216 down 217 left 218 right 219 220 These do just what you would expect. They move the cell cursor in 221 the specified direction. You can also use an optional numeric argu- 222 ment to move the specified number of cells in the given direction. 223 For example, `down 7' will take you to the cell seven rows below the 224 current cell. 225 226 endup 227 enddown 228 endleft 229 endright 230 231 These also do what you would expect. For example, if you're in the 232 middle of a long column of data, and you would like to jump to the 233 bottom of the column, but you don't know where the column ends, using 234 the enddown command will take you there. This works exactly like the 235 END key (or ^E) followed by a cursor movement key. 236 237 insertrow 238 insertcol 239 openrow 240 opencol 241 deleterow 242 deletecol 243 yankrow 244 yankcol 245 246 These commands insert, delete, or yank rows or columns, just as if 247 the user had pressed `i', `o', `d', or `y'. If you want to insert, 248 delete, or yank more than one row or column, follow the command with 249 `*' and a number; e.g. `insertrow * 5' will insert five rows before 250 the current row. 251 252 pull 253 pullmerge 254 pullrows 255 pullcols 256 pullxchg 257 pulltp 258 pullfmt 259 pullcopy 260 261 These commands pull cells (or parts of cells in the case of the 262 pullfmt command) from the delete buffer into the current location 263 in the spreadsheet. They perform the same actions as the `pp' (pull), 264 `pm' (pullmerge), `pr' (pullrows), `pc' (pullcols), `px' (pullxchg), 265 `pt' (pulltp), `pf' (pullfmt), and `pC' (pullcopy) user commands. 266 267 leftjustify 268 rightjustify 269 center 270 271 These commands are used to justify or center the string(s) in a cell 272 or range of cells. With no argument, all strings in the currently 273 highlighted range, if one is highlighted, or the current cell, if not, 274 will be justified/centered. Otherwise, a cell or range may be given 275 as a single argument, which will define which strings are to be 276 justified or centered. 277 278 select 279 280 This command takes a single string argument, whose first character 281 is used to select a named buffer to be used for the next insertion, 282 deletion, yank, or pull. It is the same as the interactive `"' 283 command. 284 285 recalc 286 287 This works like the @ command. It forces recalculation of the 288 spreadsheet. Note that automatic recalculation is turned off tem- 289 porarily while executing a macro (for speed), so you will need to 290 use this command if you want to present current data to the user 291 before the macro is complete. You will also need to use the redraw 292 command to write the recalculated data to the screen. Since recal- 293 culation is turned back on after the macro is complete, this command 294 will not be necessary at the end of a macro. 295 296 redraw 297 298 This command works like ^L, and redraws the screen. You may have 299 to use the recalc command first if you want the data to be current. 300 Note that screen updates are not performed during macros (for speed), 301 so you'll have to use this command if the data have changed in any way, 302 and you want the user to see those changes. If your macro writes 303 directly to the screen at any time to display messages or otherwise 304 interact with the user, you will need to use this command to restore 305 the spreadsheet to the screen when the macro ends. 306 307 quit 308 309 This command causes sc to immediately exit. It does not prompt the 310 user for confirmation or ask if the data should be saved if it has 311 been modified. You will need to do that from the macro, if necessary. 312 Use this command with caution. 313 314 315Now that you understand the new commands (and hopefully the old ones, too, 316although you'll have to figure those out on your own; hint: watch the top 317of the screen as you enter data and execute other commands, and look at 318the contents of a few spreadsheet files), a few hints are in order. 319 320First, it is perfectly okay to write directly to the screen, although, 321since stdin and stdout have been redirected, you'll have to use another 322method of doing this. From a shell script, for example, you can redirect 323stdin and/or stdout to /dev/tty on a command by command basis. Remember 324that even a command like clear sends special codes to do its job, so you'll 325need to use redirection to make it work. For example, to clear the screen, 326use `clear >/dev/tty'. 327 328If you have the dialog or cdialog program, you can use these to interact 329with the user. Just redirect stdin and stdout with `<>/dev/tty' for each 330dialog command (or what might be better is to open a file descriptor once 331with something like `exec 3<> /dev/tty' and use that for communicating 332with the user). You can also use the tput, echo, and read commands with 333redirection to read and write the top two lines of the screen for inter- 334action with the user. For example, `(tput cup 0 0; tput el) >/dev/tty' 335will position the cursor on the top line and clear the line. You can then 336use the echo and read commands to ask the user a question and read the 337answer. Just remember to redraw the screen after using any commands 338that write directly to the screen (now that the query command has been 339implemented, you probably won't be doing it this way, but the capability 340is there, anyway). 341 342Although the examples above are for shell scripts, similar methods can be 343used from any language. I'll leave the exact implementation for various 344other languages as an exercise for the reader. 345 346Testing macros outside of sc in most cases is very easy, since they read 347and write stdin and stdout. All you have to do is run the program and 348feed it information from the keyboard that it would otherwise get from 349sc. 350 351Once you've written and tested your macro, you can try it from within sc. 352Make sure the file is executable, and then use the R command to run it. 353Make sure you precede the name with a `|'. You can use D to define 354the macro directory, although this no longer needs to actually be a 355directory. It can be the name of your macro file, including the path, 356preceded by a `|' so that it will be executed as a program. If you 357include a trailing space, you can then add command line arguments or 358options when running it with R. This allows you to include several 359macros in the same file, and use the command line to determine which 360macro to run. Alternatively, you could use the whereami command to 361determine where the user is at in the spreadsheet, and run a different 362macro depending on which region of the spreadsheet the macro is being 363run from. Use your imagination. 364 365Since the macro is a program, you can import data from any source you 366want. You could get information from the Internet, read it from custom 367hardware connected to the serial or parallel port (or your own custom 368interface), or pull it from a file created by another program. You 369could cause your macro to react to the phase of the moon or whether the 370date is even or odd, if you want (although your users might not like 371that very well). Basically, you can do anything you want. I'd be 372interested in hearing of some creative uses of macros. 373 374Here's some additional information about the pipe commands. Although it 375is no longer necessary to include the pipe symbol at the beginning of the 376"pipe" commands, you may still append it to any of these commands, 377follosed by file descriptor as before (e.g. `whereami | fd', where fd is 378a file descriptor). I've used this for testing by using a file descriptor 379of 1, which will write the information to the screen, or a file descriptor 380of 2, which will write the information to stderr, which I then redirect 381to another virtual terminal or a file. This may also be used in a pipe- 382line to pass data from sc on to the next command (or to a file, through 383redirection). For example, if you create an sc spreadsheet on the fly 384and pipe it to sc, you could add the following lines to the end of the 385spreadsheet: 386 387 getfnum D49:G73 | 1 388 quit 389 390This will cause the formatted numeric data from the range D49:G73 to be 391piped to the next command, tab-delimited, one row per line. Also, if 392you're using simple macros (the old-style "list of sc commands in a text 393file"), no pipes are created, and the default file descriptor for the pipe 394commands is 1 (or stdout). Make sure you include the quit command, or sc 395will become interactive after receiving all of the data from the pipeline. 396 397If anyone finds another use for this way of using the pipe commands, 398please let me know, and I'll add it to the documentation in the next 399version. 400 401I'm hoping to eventually include better documentation for macros, preferably 402in a man page. In the meantime, enjoy the new capability, and send me any 403comments or suggestions for the next release. 404 405Chuck 406nrocinu@myrealbox.com 407