#!sctable command
Contents
OVERVIEW
PURPOSE
This parser is used to do some spread sheet calculation based on sc in a regular wiki table. The first column/first line coordinate is A0.
CALLING SEQUENCE
{ { {
#!sctable [-column_header -row_header -show_formular -format ]
} } }
INPUTS
-column_header : additional in the result the column header is shown
-row_header : additional in the result the line number header is shown
-show_formular : if set the formular instead of the result is shown, data is arranged in textmode. Blanks in formulars are removed
-format : is used to set the number of digits of a real number for each column (separator is a comma), e.g. -format 1,2 means first column has one digit and second has two digits.
EXAMPLES
SUM over columns
{ { {
#!sctable
||1||2||=A0+B0||
||10||20||=@sum(A1:B1)||
} } }
RESULT:
1.00 |
2.00 |
3.00 |
10.00 |
20.00 |
30.00 |
cell B1 no data
{ { {
#!sctable
||A||B||C||D||
||1||||2||=A1+C1||
} } }
RESULT:
A |
B |
C |
D |
1.00 |
2.00 |
3.00 |
SUM over rows
{ { {
#!sctable
||1||2||=A0+B0||
||10||20||30||
||=@sum(A0:A1)||=@sum(B0:B1)||=@sum(C0:C1)||
} } }
RESULT:
1.00 |
2.00 |
3.00 |
10.00 |
20.00 |
30.00 |
11.00 |
22.00 |
33.00 |
SUM over rows and columns
{ { {
#!sctable
||A||B||C||
||1||2||=A1+B1||
||10||20||=@sum(A2:B2)||
||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
} } }
RESULT:
A |
B |
C |
1.00 |
2.00 |
3.00 |
10.00 |
20.00 |
30.00 |
11.00 |
22.00 |
33.00 |
-column_header
{ { {
#!sctable -column_header
||1||2||
||3||4||
||5||6||
} } }
RESULT:
A |
B |
1.00 |
2.00 |
3.00 |
4.00 |
5.00 |
6.00 |
-row_header
{ { {
#!sctable -row_header
||1||2||
||3||4||
||5||6||
} } }
RESULT:
0 |
1.00 |
2.00 |
1 |
3.00 |
4.00 |
2 |
5.00 |
6.00 |
-column_header -row_header
{ { {
#!sctable -column_header -row_header
||1||2||
||3||4||
||5||6||
} } }
RESULT:
|
A |
B |
0 |
1.00 |
2.00 |
1 |
3.00 |
4.00 |
2 |
5.00 |
6.00 |
-show_formular -column_header -row_header
{ { {
#!sctable -show_formular -column_header -row_header
||m||p||
||1||=A1 * 5||
||2||=A2-3||
||3||4||
} } }
RESULT:
|
A |
B |
0 |
m |
p |
1 |
1 |
=A1*5 |
2 |
2 |
=A2-3 |
3 |
3 |
4 |
-column_header and blanks in cells
{ { {
#!sctable -column_header
||Name Vorname|| || || 3 || || 5||
||Name Vorname|| 1 || 2 || || 4 || 5||
||Name Vorname|| 1 || 2 || || || 5||
} } }
RESULT:
A |
B |
C |
D |
E |
F |
Name Vorname |
|
|
3.00 |
|
5.00 |
Name Vorname |
1.00 |
2.00 |
|
4.00 |
5.00 |
Name Vorname |
1.00 |
2.00 |
|
|
5.00 |
-format 1,1
{ { {
#!sctable -format 1,1
||1||2||
||3||4||
||=@sum(a0:a1)||=a2*4||
} } }
RESULT:
1.0 |
2.0 |
3.0 |
4.0 |
4.0 |
16.0 |
useage of variable names -show_formular -column_header -row_header
{ { {#!sctable -show_formular -column_header -row_header
||A||B||C||
||1||{two}2||=A1+two||
||10||20||=@sum(A2:B2)||
||=@sum(A1:A2)||=@sum(B1:B2)||=@sum(C1:C2)||
} } }
RESULT:
|
A |
B |
C |
0 |
A |
B |
C |
1 |
1 |
{two}2 |
=A1+two |
2 |
10 |
20 |
=@sum(A2:B2) |
3 |
=@sum(A1:A2) |
=@sum(B1:B2) |
=@sum(C1:C2) |
and if we calculate
RESULT:
|
A |
B |
C |
0 |
A |
B |
C |
1 |
1.00 |
2.00 |
3.00 |
2 |
10.00 |
20.00 |
30.00 |
3 |
11.00 |
22.00 |
33.00 |
color in cells
{ { {#!sctable
||<:rowbgcolor=lightcyan>'''A'''||<:>'''B'''||<:>'''C'''||
||<)#dddddd>1||<)#dddddd>{two}2||<)#cccccc>=A1+two||
||<(>10||<)>20||<:#dddddd>=@sum(A2:B2)||
||<rowbgcolor="#cc99ff">=@sum(A1:A2)||=@sum(B1:B2)||<bgcolor=magenta>=@sum(C1:C2)||
} } }
RESULT:
A |
B |
C |
1.00 |
2.00 |
3.00 |
10.00 |
20.00 |
30.00 |
11.00 |
22.00 |
33.00 |
PROCEDURE
This parser uses the external sc (http://freshmeat.net/projects/sc/) routine.
- check if in the data dir of your wiki a directory tmp is created with write permissions to the httpd_user name. e.g.
/usr/share/moin/mywiki/data/tmp drwxr-xr-x 2 wwwrun nogroup 48 2004-04-09 18:57 tmp
- All formulars have to start by a "=" sign.
- builtin functions starts with a "=@" sign, please look at the sc description for the list of functions
- text in the cells is printed to the left and numbers to the right
@copyright: 2004-09-19 by Reimar Bauer (R.Bauer@fz-juelich.de) sctable.py
Command Summary
Toggle Options
^To Toggle options. Toggle one option selected by o:
a Recalculate automatically or on ``@'' commands.
o Optimize expressions upon entry if enabled.
c Current cell highlighting enable/disable.
e External function execution enable/disable.
l Autolabeling defined cells enable/disable.
n If enabled, a digit starts a numeric value.
t Top line display enable/disable.
x Encrypt/decrypt database and listing files (Not available).
$ Dollar prescale. If enabled, all numeric constants
(not expressions) entered are multipled by 0.01.
r Newline action. Toggle between no action, move down
after entry and move right after entry.
z Set the newline action limits to the current row and column
(for r && z see also set rowlimit=n, collimit=n)
Set Options
S Set options. Options include:
autocalc Automatic recalculation.
byrows Recalculate in row order. (default)
bycols Recalculate in column order.
optimize Optimize expressions upon entry. (default off)
iterations=n Set the number of iterations allowed. (10)
tblstyle=xx Set ``T'' output style to:
0 (none), tex, latex, slatex, or tbl.
rndtoeven Round *.5 to nearest even number instead of
always up.
rowlimit=n Set the remembered row limit for newline action.
collimit=n Set the remembered column limit for newline action.
(rowlimit and collimit can both be set by ^Tz)
Cell cursor movement (always OK):
^N ^P Down, up
j k l h Down, up, right, left
Arrow keys (if the terminal and termcap support them.)
^Ed Go to end of range. Follow ^E by a direction indicator
such as ^P or j.
J K L H Down, up, right, left by 1/2 pages (or 'pagesize' rows)
SPACE Forward
^H Back
TAB Forward, otherwise starts/ends a range
^ Up to row 0 of the current column.
# Down to the last valid row of the current column.
0 Back to column A. Preface with ^U if numeric mode.
$ Forward to the last valid column of the current row.
b Back then up to the previous valid cell.
w Forward then down to the next valid cell.
g Go to a cell. Cell name, range name, quoted string,
a number, 'error', or 'invalid' to specify which cell.
` ' Go to a marked cell (see help screen e for more info.
Cell entry and editing commands:
= Enter a numeric constant or expression.
< Enter a left justified string or string expression.
\\ Enter a centered label.
> Enter a right justified string or string expression.
e Edit the current cell's numeric value.
E Edit the current cell's string part.
F Assign a format to the current cell's numeric value.
x Clear the current cell.
m Followed by any lowercase letter, marks the current cell
with that letter.
c Copy a marked cell to the current cell.
+ Increment numeric part
- Decrement numeric part
RETURN Enter insert mode if the input line was empty (ESC to edit)
In numeric mode, a decimal digit, ``+'', ``-'', and ``.'' all start
a new numeric constant or expression.
Line Editor
Hitting the ESC key while entering any command on the top line
will start a one-line vi-style editor. Supported commands:
ESC q Abort command entry.
h l Move cursor forward, backward.
0 $ Move cursor to the beginning, end of the line.
b w Move cursor forward/back one word.
e Move cursor to next end-of-word.
fc Move cursor to character c.
tc Move the cursor to the character before c.
i a Enter insert mode before/after the cursor.
I A Move to column 0/end of line and enter insert mode.
x X Delete the character under/before the cursor.
rc Replace the character under the cursor with c.
cm dm Change/Delete - m = b,e,f,h,l,t or w.
R Enter replace (overstrike) mode.
s Delete character under cursor and enter insert mode.
+ j - k / ? Forward/backward/search the command history.
n N Repeat last history search (N = opposite direction).
. u Repeat/undo the last command.
^V Enter navigate mode. Another ^V enters current cell address.
^W Type, in the command line, the current cell's expression.
^A Type, in the command line, the current cell's numeric value.
File commands:
G Get a new database from a file.
M Merge a new file into the current database.
P Put the current database into a file.
W Write a listing of the current database into a file in
a form that matches its appearance on the screen.
T Write a listing of the current database to a file, but
put delimiters between each pair of fields.
Optionally brackets output with control lines for ``tbl'',
``LaTeX'', ``SLaTex'', or ``TeX''.
``"| program"'' for a file name will pipe output to
a program for Put, Write and Table. If a cell name is used
as the file name, the cell's string part will be used as the
file name.
Row and column commands:
ir, ic Insert a new, empty row (column)
ar, ac Append a new copy of the current row (column)
dr, dc Delete the current row (column)
pr, pc, pm Pull deleted cells back into the spreadsheet
Insert rows, columns or merge the cells.
vr, vc Remove expressions from the affected rows (columns),
leaving only the values.
Zr, Zc Hide (``zap'') the current row (column)
sr, sc Show hidden rows (columns)
f Set the output format to be used with the values of
each cell in this column. Enter field width and
number of fractional digits. A preceding count can be
used to change more than one column.
Commands which move or copy cells also modify the row and column
references in the new cell expressions. Use ``fixed'' or the
``$'' style cell reference to supress the change.
@myrow, @mycol return the row or column of the current cell
@lastrow, @lastcol return the row or column of the current cell
Range commands:
rx Clear a range.
rv Remove the expressions from a range of cells, leaving
just the values.
rc Copy a source range to a destination range.
rf Fill a range with constant values starting with a given
value and increasing by a given increment.
rd Assign a name to a cell or a range of cells. Give the
the name, surrounded by quotes, and either a cell name such
as ``A10'' or a range such as ``a1:b20''.
rl Locks a cell or a range of cells, i.e makes it unchangeable.
rU Unlocks a locked cell, i.e makes it changeable.
rs Shows the currently defined range names. Pipe output to
sort, then to less.
ru Use this command to undefine a previously defined range name.
rF Assign a format string to a range of cells.
Range operations affect a rectangular region on the screen
defined by the upper left and lower right cells in the region.
A range is specified by giving the cell names separated by ``:'',
such as ``a20:k52''. Another way to refer to a range is to use
a name previously defined using ``rd''.
Miscellaneous commands:
Q q ^C Exit from the program.
^G ESC Abort entry of the current command.
? Help
! Shell escape. Enter a command to run. ``!!'' repeats
the last command. Just ``!'' starts an interactive shell.
^L Redraw the screen.
^R Redraw the screen. Highlight cells with values but no
expressions.
^X Redraw the screen. Show formulas, not values.
C Redraw the screen with the row containing the current cell
centered.
@ Recalculate the spreadsheet.
TAB When the character cursor is on the top line TAB can be used
to start or stop the display of the default range.
Variable names:
K20 Row and column can vary on copies.
$K$20 Row and column stay fixed on copies.
$K20 Row can vary; column stays fixed on copies.
K$20 Row stays fixed; column can vary on copies.
fixed holds following expession fixed on copies.
Cells and ranges can be given a symbolic name via ``rd''.
Expressions:
-e Negation e<=e Less than or equal
e+e Addition e=e Equal
e-e Subtraction e!=e Not Equal
e*e Multiplication e>=e Greater than or equal
e/e Division e>e Greater than
e%e Modulo e<e Less than
e^e Exponentiation e&e Boolean operator AND.
~e Boolean operator NOT e|e Boolean operator OR
e?e1:e2 or @if(e,e1,e2)
Conditional: If e is non zero then then e1, else e2.
Terms may be constants, variables, and parenthesized expressions.
Range functions:
@sum(r) Sum all valid cells in the range.
@prod(r) Multiply together all valid cells in the range.
@avg(r) Average all valid cells in the range.
@count(r) Count all valid cells in the range.
@max(r) Return the maximum value in the range.
@min(r) Return the minimum value in the range.
@stddev(r) Return the sample standard deviation of
the cells in the range.
@index(e,r) @stindex(e,r)
Return the numeric (string) value of the cell at
index e into range r.
@lookup(e,r) @hlookup(e,r,n) @vlookup(e,r,n)
Search through the range r for a value that
matches e. If e is numeric, the last value <= e
matches; if string, an exact match is required.
@lookup searches a single row (column) and returns
the value from the next column (row); @hlookup
(@vlookup) searches the first row (column) in r and
returns the value n columns (rows) from the match.
Numeric functions:
@atan2(e1,e2) Arc tangent of e1/e2.
@ceil(e) Smallest integer not less than e.
@eqs(se1,se2) 1 if string expr se1 has the same value as se2.
@exp(e) Exponential function of e.
@abs(e) @fabs(e) Absolute value of e.
@floor(e) The largest integer not greater than e.
@hypot(x,y) Sqrt(x*x+y*y).
@max(e1,e2,...) The maximum of the values of the e's.
@min(e1,e2,...) The minimum of the values of the e's
@nval(se,e) The numeric value of a named cell.
pi @pi A constant quite close to pi.
@pow(e1,e2) e1 raised to the power of e2.
@rnd(e) Round e to the nearest integer.
@round(e,n) Round e to n decimal places.
@sqrt(e) Square root of e.
@ston(se) Convert string expr se to a numeric
@ln(e) @log(e) Natural/base 10 logarithm of e.
@dtr(e) @rtd(e) Convert degrees to/from radians.
@cos(e) @sin(e) @tan(e) Trig functions of radian arguments.
@asin(e) @acos(e) @atan(e) Inverse trig function.
String functions:
# Concatenate strings. For example, the
string expression ``A0 # "zy dog"'' yields
``the lazy dog'' if A0 is ``the la''.
@substr(se,e1,e2) Extract characters e1 through e2 from the
string expression se. For example,
``@substr("Nice jacket" 4, 7)'' yields
``e ja''.
@fmt(se,e) Convert a number to a string using sprintf(3).
For example, ``@fmt("*%6.3f*",10.5)'' yields
``*10.500*''. Use formats are e, E, f, g, and G.
@sval(se,e) Return the string value of a cell selected by name.
@ext(se,e) Call an external function (program or
script). Convert e to a string and append it
to the command line as an argument. @ext yields
a string: the first line printed to standard
output by the command.
@coltoa(e) Return the column letter(s) from the passed number
@upper(e) @lower(e) Return the string in upper/lower case
@capital(e) Return the string with words in upper case
String expressions are made up of constant strings (characters
surrounded by quotes), variables, and string functions.
Financial functions:
@pmt(e1,e2,e3) @pmt(60000,.01,360) computes the monthly
payments for a $60000 mortgage at 12%
annual interest (.01 per month) for 30
years (360 months).
@fv(e1,e2,e3) @fv(100,.005,36) computes the future value
of 36 monthly payments of $100 at 6%
interest (.005 per month). It answers the
question: ``How much will I have in 36
months if I deposit $100 per month in a
savings account paying 6% interest com-
pounded monthly?''
@pv(e1,e2,e3) @pv(1000,.015,36) computes the present
value of an ordinary annuity of 36
monthly payments of $1000 at 18% annual
interest. It answers the question: ``How
much can I borrow at 18% for 30 years if I
pay $1000 per month?''
Time and date functions:
@now Return the time encoded in seconds since 1970.
@dts(e1,e2,e3) Return e1/e2/e3 encoded in seconds since 1970.
Either m/d/y or y/m/d may be used (year must
include the century), although y/m/d is preferred.
@tts(h,m,s) Return h:m:s encoded in seconds since midnight.
All of the following take an argument expressed in seconds:
@date(e) Convert the time in seconds to a date
string 24 characters long in the following
form: ``Sun Sep 16 01:03:52 1973''. Note
that you can extract pieces of this fixed format
string with @substr.
@year(e) Return the year. Valid years begin with 1970.
@month(e) Return the month: 1 (Jan) to 12 (Dec).
@day(e) Return the day of the month: 1 to 31.
@hour(e) Return the number of hours since midnight: 0 to 23.
@minute(e) Return the number of minutes since the
last full hour: 0 to 59.
@second(e) Return the number of seconds since the
last full minute: 0 to 59.