The easy way to do such a thing is to write a recursive function that calls itself to parse its own arguments, its termination is guaranteed as long as each invocation of the function eats away parts of their input strings.
That is (in a mixture of Perl and pseudo-code):
sub funparse {
my @args = split(/\s*,\s*/ , shift);
# this has to be a little smarter (as commas inside parenthesis don't count)
# the idea is to extract the actual function arguments
foreach (@args) {
if m/\$fun1\((.*)\)/ then return fun1( funparse($1) );
if m/\$fun2\((.*),(.*)\)/ then return fun2( funparse($1), funparse($2) );
....
otherwise return $_
}
}
My only concern would be execution speed....
Check out:
http://perl.plover.com/lambda/ for some
really complicated examples.
--
EdgarBrown - 09 Apr 2001
Here is a related posting to
JonUdell's newsgroup:
| Subject: 500: | Internal Server Errors on Byte.com/Jon Udell: Zope Spreadsheets |
| Date: | Fri, 22 Jun 2001 00:57:04 +1200 |
| From: | "Adam Warner" usenet@consultingPLEASENOSPAM.net.nz |
| Newsgroups: | cmpnet.byte.programming |
Hi Jon and all,
BTW I wrote this earlier this evening to comp.apps.spreadsheets, and was very excited to find links to your articles (Zope is what I particularly want to use):
I've been searching for online/server(web) based spreadsheet applications and code. I immediately found the slick commercial blox solution (e.g. http://www.blox.com/gallery?id=_sales ) and was wondering if anyone knew of some open source development being undertaken in this area. I have found some projects that are potentially relevant, i.e.:
http://www.cs.columbia.edu/sharptools/
A Java GPLed spreadsheet.
http://stats.mth.uea.ac.uk/Rcgi/index.html
http://www.math.montana.edu/Rweb/
http://jfontain.free.fr/
Moodss
Spreadsheet conversion:
http://www.xlhtml.org/
Takes XL binaries and generates HTML
http://freshmeat.net/projects/writeexcel/
This Perl module will write Microsoft Excel compatible binaries!
http://freshmeat.net/projects/spreadsheet_conversion/
Text formats to a MySQL? database
http://www.itlab.musc.edu/table_export/readme.html
The idea is to create a forecasting application ("environment") that gives some freedom in the input of data and the specification of the overall forecasting model before generating the results. If I can't come up with a platform independent proposal I will be designing a Microsoft Excel spreadsheet that incorporates the functionality (using VBA). This may be the best way to proceed (and there is no additional licensing cost for the client because they already have MS Excel licenses). However I would prefer to develop a more open solution if feasible.
Regards,
Adam
A great plugin! I've made the following change at the start of the
commonTagsHandler to allow nesting of functions:
if( ! ( $_[0] =~ /%CALC\{.*?\}%/ ) ) {
# nothing to do
return;
}
I'm guess there's a change this could get stuck in an infinite loop so that should really be protected against.
I've also made a couple of other changes:
- Allow number in LEFT, RIGHT and ABOVE, to limit the range e.g. LEFT(3) is 3 cells to the left.
- Added two new function WORKDAYS and NETWORKDAYS that are equivalent to Excel equivalents i.e. add number of working days to a date and find number of working days between two dates. I've assumed a specific date format - would be better to generalise to accept either epoc or a range of text formats. Then should be able to format resultant output date format. I've put these in as we are trying some Work Queue orientated ExtremeProgramming
Happy to supply these changes if there is interest.
--
JohnTalintyre - 25 Feb 2002
Why do you need nesting of
%CALC\{...}%? You can write
%CALC\{FUNC1(FUNC2(FUNC3()))}%. Your solution can be a workaround for the limitation of only one function per nesting level. However, the spreadsheet code should be fixed in in the first place.
The additional functions sound good!
--
PeterThoeny - 25 Feb 2002
Oops - I posted the wrong line. There was a comment in the code about greedy matching causing a problem. I changed the code to:
while( $theAttr =~ s/\$([A-Z]+)\(([^()]*)\)/&doFunc($1,$2)/geo ) {}
For the extra functions to be short we need to expose date processing via
Func.pm and change it's nature slightly e.g. converting from epoch secs to a date and allowing the date conversion function to support formats that don't include time.
--
JohnTalintyre - 27 Feb 2002
I fixed the problem of only one function per nesting level by adding the nesting level to the parenthesis before processing. That way matching pairs can be parsed. Now it is possible to write something like:
$EVAL( $LENGTH(abc) + (2 * 4) / (8 - $LENGTH(ab)) ) = 4.33333333333333.
--
PeterThoeny - 12 Mar 2002
I have added a
DEF function to my spreadsheet. This function takes several cell references and returns the first one that is not empty. This is useful for doing things like calculating real or expected time taken for a task based on whether the end date has been entered.
Eg
$DEF(R1:C1,R2:C2,R3:C3)
} elsif( $theFunc eq "DEF" ) {
# Format DEF(ref1,ref2,...,refN) returns first defined cell
# Added by MF 26/3/2002
foreach $cell ( split( /,/, $theAttr ) ) {
my @arr = getTableRange( "$cell..$cell" );
if( @arr ) {
if ($arr[0]) {
$result = $arr[0];
last;
}
}
}
An annoying problem I had was that my spreadsheet required copying the same formula into multiple rows. I got round this by using the
TopicVarsPlugin to define a topic variable for each repeated formula. Another way around this would be to implement a function like
$T but which copies the cell formula rather than the result of evaluating that formula. The repeated formulae could then be stored only once in a cell.
-
MartinFuzzey - 26 Mar 2002
Sometimes, a table cell contains text along with the value, for example
<b>102</b>. Unfortunately this prevents the value from being extracted. This fairly trivial change in
SpreadSheetPlugin.pm fixes my problem:
In
getTableRangeAsInteger:
Line 323: if( $val =~ /^\D*([\-\+]*[0-9]+).*/o ) {
In
getTableRangeAsFloat:
Line 343: if( $val =~ /^\D*([\-\+]*[0-9\.]+).*/o ) {
--
GladeDiviney - 30 May 2002
Updated plugin with these changes:
38c38
< $web $topic $user $installWeb $VERSION $debug
---
> $web $topic $user $installWeb $VERSION $debug $skipInclude
60a61,63
> # Get plugin debug flag
> $skipInclude = &TWiki::Func::getPreferencesFlag( "SPREADSHEETPLUGIN_SKIPINCLUDE" );
>
73c76,80
< if( ! ( $_[0] =~ /%CALC\{.*?\}%/ ) ) {
---
> if( ( $_[3] ) && ( $skipInclude ) ) {
> # bail out, handler called from an %INCLUDE{}%
> return;
> }
> unless( $_[0] =~ /%CALC\{.*?\}%/ ) {
252a260,273
>
> } elsif( $theFunc eq "DEF" ) {
> # Format DEF(ref1,ref2,...,refN) returns first defined cell
> # Added by MF 26/3/2002
> foreach $cell ( split( /,/, $theAttr ) ) {
> my @arr = getTableRange( "$cell..$cell" );
> if( @arr ) {
> if ($arr[0]) {
> $result = $arr[0];
> last;
> }
> }
> }
>
324,325c345,346
< # search first integer pattern
< if( $val =~ /^\s*([\-\+]*[0-9]+).*/o ) {
---
> # search first integer pattern, skip over HTML tags
> if( $val =~ /^\s*(?:<[^>]*>)*([\-\+]*[0-9]+).*/o ) {
344,345c365,366
< # search first float pattern
< if( $val =~ /^\s*([\-\+]*[0-9\.]+).*/o ) {
---
> # search first float pattern, skip over HTML tags
> if( $val =~ /^\s*(?:<[^>]*>)*([\-\+]*[0-9\.]+).*/o ) {
I added
$skipInclude to work around new problem with
PluginProblemWithIncludes fix.
--
PeterThoeny - 07 Jun 2002
I've been looking at ways of enhancing the no-perl code extensibility of TWiki (ok, I've been trying to do a few things wich I can almost do without writing perl code and coding up a bunch of stand-alone plugins seems like a difficult path to take). One thing that keeps popping up is that I would like to do calculations on the result of a formatted search. Obviously this is easy to do using the
SpreadSheet? plugin if I want the data and the results of calcs on the data in a table. But what if I only want the results, not the data? I am thinking of making a small mod to the
SpreadSheetPlugin to support "invisible" rows in a table using a syntax like:
#| This | Row | Is | Invisible |
or maybe
!| This | Row | Is | Invisible |
or even
! This | Row | Is | Invisible |
This allows the results from the table to be referenced without the table itself appearing (a feature that may not be immediately obvious of the existing
SpreadSheetPlugin is that this works:
The value of R2:C2 is 5 . This is sometimes handy now but would be even more useful if the table was the invisible result of a search.
If I want the results (but not the source data) in a table I can do something like:
| *Feature* | *Tasks To Do* | *Tasks In Progress* | *Tasks Completed* |
%SEARCH{"something that matches to-do tasks" format="#|Feature| 1 | 0 | 0 |}%
%SEARCH{"something that matches in-progress tasks" format="#|Feature| 0 | 1 | 0 |}%
%SEARCH{"something that matches completed tasks" format="#|Feature| 0 | 0 | 1 |}%
| The Feature | %CALC{"$SUM( $ABOVE() )"}% | %CALC{"$SUM( $ABOVE() )"}% | %CALC{"$SUM( $ABOVE() )"}% |
The same change might also be useful for the
ChartPlugin?
Thoughts? I'm going to try implementing this and see how it goes.
--
DarrylGreen - 15 Oct 2002
Done - it works, (modified
SpreadSheetPlugin.pm attached) though I'm sure my cut and-paste approach to perl programming could be improved.
--
DarrylGreen - 15 Oct 2002
May be I do not understand all you want to accomplish, but a simple solution is to put the table containing SEARCH into HTML comments. That way it is not visible to the user, but you still can apply a CALC on the table. The CALC also works after the table until the beginning of the next TWiki table.
Example, first with a visible table:
| A: | 1 | 2 | 3 |
| B: | 4 | 5 | 6 |
| C: | 7 | 8 | 9 |
| Total: | 12 | 15 | 18 |
The last row calculates the sum of each column.
Now, lets do the same table again inside an HTML comment, excluding the last summary row:
Well, as expected, you can't see the table. Now lets calculate and display the sum of each row.
- You type:
%CALC{"$SUM( R1:C2..R$ROW():C2 )"}% ; %CALC{"$SUM( R1:C3..R$ROW():C3 )"}% ; %CALC{"$SUM( R1:C4..R$ROW():C4 )"}%
--
PeterThoeny - 16 Oct 2002
That only has 1 problem - that I can't use the results in a table.
Other alternatives I considered (all more elegant than my quick hack above, just not so quick to try out):
- Store every table cell, not just those in the current table, and extend the SpreadSheetPlugin syntax to allow a table to be specified. This would have problems if the page was changed significantly and/or of some of this stuff was in re-useable includes. A relative or named table referencing scheme would work better.
- Provide some sort of variable facility so that a CALC result can be stored as a named variable and retrieved later, including within another table.
- Other suggestions (just giving up and writing perl might be the best plan)?
I guess I could use html rather than TWiki tables for formatting the results - but that may have its own problems/restrictions?
--
DarrylGreen - 16 Oct 2002
That is what I did at work, use HTML tags for the reporting table. The only disadvantage is that it bloats the source text. (You could move that into a separate INCLUDEd topic.)
--
PeterThoeny - 17 Oct 2002
A suggestion for a feature: Add the ability to perform calculations on dates. Often it would be useful to compute the difference between dates stored in table cells, etc. Various
CPAN Date modules provide the computation already....
--
ThomasWeigert - 27 Oct 2002
The following patch adds the MSExcel
SUMPRODUCT function. It does a scalar product operation on two columns.
An example of using this is when you have a table with weights and scores and you want to produce the weighted
sum or average:
| *Criterion* | *Weight* | *BrandA* | *BrandB* |
| color | 4 | 2 | 3 |
| size | 2 | 3 | 4 |
| quality | 5 | 2 | 6 |
| Weighted total || %CALC{"$SUMPRODUCT(R2:C2..R4:C2,R2:C3..R4:C3)"}% | %CALC{"$SUMPRODUCT(R2:C2..R4:C2,R2:C4..R4:C4)"}% |
The first use computes (4 * 2) + (2 * 3) + (5 * 2). There are actually several "matrix" operations that would be fun.
Caveat: This doesn't work with $ROW() and friends due to the greedy nature of the parser (as commented in your code). This is the only two-argument function that I see in spreadsheet, so maybe you have other ideas for implementing this. Also, the MSExcel version allows many columns, not just two.
diff: conflicting specifications of output style
*** SpreadSheetPlugin.pm.orig Tue Jan 15 20:03:07 2002
--- SpreadSheetPlugin.pm Thu Nov 7 15:41:26 2002
***************
*** 246,251 ****
--- 246,262 ----
$result += $i if defined $i;
}
+ } elsif( $theFunc eq "SUMPRODUCT" ) {
+ $result = 0;
+ my ($attr1, $attr2) = split(',', $theAttr);
+ my @arr1 = getTableRangeAsFloat( $attr1 );
+ my @arr2 = getTableRangeAsFloat( $attr2 );
+ while (@arr1 and @arr2) {
+ my $i = pop(@arr1);
+ my $j = pop(@arr2);
+ $result += $i * $j;
+ }
+
} elsif( $theFunc eq "AVERAGE" ) {
$result = 0;
my $items = 0;
--
RobertWithrow - 12 Nov 2002
Just wanted to pass along the start of adding formatted output. The first couple of formats include DOLLAR, PERCENT, NUMBER and COMMA. An example is:
%CALC{"$FORMAT(DOLLAR, 2, 12345.67)"}%
which will return $12,345.67 Since CALC now nests, any function can be used where VALUE is needed.
} elsif( $theFunc eq "FORMAT" ) {
# Format FORMAT("TYPE", precision, value) returns formatted value
my ($format, $res, $value) = split( /,/, $theAttr );
#Strip leading and trailing spaces
$format =~ s/^\s*(.*?)\s*$/$1/;
$res =~ s/^\s*(.*?)\s*$/$1/;
$value =~ s/^\s*(.*?)\s*$/$1/;
if ($format eq "DOLLAR") {
my $neg = 1 if $value < 0;
$value = abs($value);
$result = sprintf("%0.${res}f", $value);
my $temp = reverse $result;
$temp =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
$result = "\$" . (scalar reverse $temp);
$result = "(".$result.")" if $neg;
} elsif ($format eq "COMMA") {
$result = sprintf("%0.${res}f", $value);
my $temp = reverse $result;
$temp =~ s/(\d\d\d)(?=\d)(?!\d*\.)/$1,/g;
$result = scalar reverse $temp;
} elsif ($format eq "PERCENT") {
$result = sprintf("%0.${res}f\%", $value * 100);
} elsif ($format eq "NUMBER") {
$result = sprintf("%0.${res}f", $value);
} else {
#FORMAT not recognized, just return value
$result = $value;
}
--
JimStraus - 05 Jan 2003
I have a request for the next version of this plugin. You have an
Average function (or mean) but you don't yet have a
Median fuction. I know of some EXCELLENT project management functions that could also be added, but I don't have the notes at hand right now. I also changed the topic to say that it IS
InstalledOnTWikiOrg.
--
GrantBow - 12 Jan 2003
Another useful set of functions would be to add a $ROUND to complement $INT, for those cases where you want to round to the closest int, instead of just truncating.
--
SethTaplin - 27 Feb 2003
I believe I have run into a bug in
SpreadSheetPlugin when it is applied to a table resulting from a search, where the search happens to return an empty table. See
EmptySearchAndSpreadsheetProblem.
--
ThomasWeigert - 01 Jun 2003
for the Bug tracking system that we have using
TwikiForms? , I have added day, week and month units that this plugin can now add up see the attached patch file
--
SvenDowideit - 07 Mar 2003
I'm motivated enough to do a mod to the spreadsheet plugin to allow named ranges to be defined and used in subsequent calculations in the same topic (but not necessarily in the same table). This could work ok as a function, though a somewhat odd one (returns empty string), but as it takes 2 parameters (name and range) there would be parsing problems if using functions to define the range (which seems likely - I wanted in-place definition so the region could be defined relative to the point of definition).
I'm thinking I'd extend the
Rr:Cc addressing syntax to include an optional name - something like
name[Rr:Cc] and a range would be
name[Rr:Cc..Ry:Cx].
The motivation is simply that I have often found myself producing topics that have a few tables in them interspersed with text and somewhere I want to total/summarise the data in various ways.
Note that I did consider doing what the chart plugin does and refering to tables by name, rather than ranges within them by name, but the range idea seemed nicer to use and potentially more powerful.
Any suggestions - is it worth trying to "improve" the parsing? Should I just define another variable - say
%CALCRANGE{name,range}% to define the name? To be useful the range parameter needs to be parsed as a CALC expression (so $LEFT, $ABOVE, $ROW etc can be used to define the range) which then makes me wonder if extending
%CALC{"expr"}% to allow something like
%CALC{rangename="expr"}% would be ok. Strange semantics?
Any suggestions for syntax etc? Better ideas?
--
DarrylGreen - 29 May 2003
Ok - did the above - syntax is:
%CALCRANGE{ name="name" range="Rn:Cn..Rn:Cn" }%
To address named ranges use
name[Rn:Cn] (single cell) or
name[Rn:Cn..Rn:Cn] for a range.
I'll make
name[] mean the whole range.
I'll upload it after I've had a bit more of a play with it.
--
DarrylGreen - 30 May 2003
Darryl: Named tables sounds like a sensible enhancement. With the current Plugin you can do CALCs after a table referring to the table above, but not any other table.
I suggest a transparent solution where tables are enumerated on a topic (
1,
2, etc) and can be named if needed for reference. The obvious choice is to use
%TABLE{ name="sales" }%.
Cell and cell ranges could be extended to refer to other tables (aka Excel tabs), other topics (aka Excel files), and other webs (aka folders). Key is to be compatible and intuitive with the existing syntax.
Not sure what an intuitive syntax is, brainstorming:
R1:C2..R3:C4 | current syntax: range in current table (or table above if between tables) |
$REF(R1:C2..R3:C4) | same with new syntax |
$REF(R1:C2..R3:C4, 2) | range in second table in current topic |
$REF(R1:C2..R3:C4, sales, OtherTopic) | range in other topic, table named "sales" |
$REF(R1:C2..R3:C4, sales, Web.OtherTopic) | range in other web.topic, table named "sales" |
The current implementation is tuned for speed, e.g. calcs are done while parsing the rows (with that you cannot refer to cells in rows below the current row). Speed consideration is also a key factor with the extended syntax. References to current topic needs to go against the text in memory (not file); references to tables in other topics need to go to the file (with some caching).
The current implementation refers to expanded cell content, e.g. if there is a CALC in the referred cell you get the result of the formula. Probably the same should be done for the extended references? But then we might have a performance issue.
--
PeterThoeny - 01 Jun 2003
Thomas: This is not a bug in the Plugin but in the formatted search, see follow up in
EmptySearchAndSpreadsheetProblem.
--
PeterThoeny - 01 Jun 2003
Peter, I have produced a simple patch to address the issue high-lighted in
EmptySearchAndSpreadsheetProblem and above. Unfortunately, there is a small change to
SpreadSheetPlugin required. It would be great if somebody knowledgeable could examine the proposed fix to assess whether that might break something else (the modified
SpreadSheetPlugin passed all my test cases).
--
ThomasWeigert - 03 Jun 2003
Just a quick note There is a workaround to the empty lines problem that does not get rid of the empty line, but reduces the clutter in the table footer:
|*Heading*|*1*|
|*Heading*|*2*|
| data row (produced by SEARCH) | 1 |
| data row (produced by SEARCH) | 2 |
| data row (produced by SEARCH) | 3 |
| ~Total: %CALC{"$IF($ROW()<4,none,$ROW(-3))"}% | ~Status:%BR% %CALC{"$IF($ROW()<4,,$COUNTITEMS(R3:C2..R$ROW(-1):C2))"}% |
--
PeterThoeny - 06 Jun 2003
I'm trying to use this plugin to perform calculations on some data held in a form. I have a group of checkboxes, so when I retrieve the meta data I get a string "Help, Messages, Menu" (for example). I want to perform one of a set of simple calculations if particular a checkbox is set, but I can't use $FIND as the commas break up its argument. I'm probably being thick (still being new to TWiki), but can I perform this search, or can I filter out the commas in some way?
--
ChrisHogan - 25 Jul 2003
Peter, re. the named tables etc. my current approach (naming ranges) is efficient (well - as efficient as my perl coding allows) as it lets the spreadsheet plugin operate 100% as normal, doing calculations as it parses. A named range must be declared after the range has been parsed, and simply copies the specified portion of tableMatrix into a new matrix stored in a hash (indexed by the region name). I have made no attempt to directly support cross-topic linkage, but have (this is NOT efficient) used %INCLUDE in a HTML comment to bring in an "invisible" copy of another topic and access its named regions. In theory at least, I should be able to extend the same model (storing the calculated result matrix in a hash) to cross-topic use in such a way that the hash is used as a cache to avoid re-parsing the other topic each time a reference is made to the same named region. I've attached what I have now. The following is a small example using it.
First Bit
| Part | Cost |
|---|
| Widget | 1.00 |
| Fribbit | 2.00 |
| 2 | 3 %CALCRANGE{ name="FirstTotal" range="R$ROW():C1..R$ROW():C$COLUMN()"}% |
Second Bit
| Part | Cost |
|---|
| Widget | 1.00 |
| Fribbit | 2.00 |
| Snurd | 2.00 |
| Fofle | 2.00 |
| 4 | 7 %CALCRANGE{ name="SecondTotal" range="R$ROW():C1..R$ROW():C$COLUMN()"}% |
Third Bit
| Part | Cost |
|---|
| Widget | 1.00 |
| Snurd | 2.00 |
| Fofle | 2.00 |
| 3 | 5 %CALCRANGE{ name="ThirdTotal" range="R$ROW():C1..R$ROW():C$COLUMN()"}% |
Three Bit Thing Parts List
--
DarrylGreen - 12 Jun 2003
Not sure if this is a lack of unserstanding on my part or a missing bit of functionality (that commas aren't handled by $FIND, $REPLACE, etc.). I've also posted it as
CommasInSpreadSheetFormulae in the Support web.
--
ChrisHogan - 25 Jul 2003
Commas impose a problem in the string to be substituted because it confuses the parser. Use this new
$TRASNLATE() formula (which will be in the next release) to translate the comma into something else:
} elsif( $theFunc eq "TRANSLATE" ) {
$result = $theAttr;
# greedy match for comma separated parameters (in case first parameter has embedded commas)
if( $theAttr =~ /^(.*)\,\s*(.+)\,\s*(.+)$/ ) {
my $string = $1 || "";
my $from = $2;
my $to = $3;
$from =~ s/\$comma/,/g; $from =~ s/\$sp/ /g; $from = quotemeta( $from );
$to =~ s/\$comma/,/g; $to =~ s/\$sp/ /g; $to = quotemeta( $to );
$from =~ s/([a-zA-Z0-9])\\\-([a-zA-Z0-9])/$1\-$2/g; # fix quotemeta (allow only ranges)
$to =~ s/([a-zA-Z0-9])\\\-([a-zA-Z0-9])/$1\-$2/g;
$result = $string;
if( $string && eval "\$string =~ tr/$from/$to/" ) {
$result = $string;
}
}
"$TRANSLATE(text, from, to)" | Translate text from a set of characters to another set, one character by one. For characters, write $comma to escape comma, $sp to escape space. Example: %CALC{"$TRANSLATE(four,uo,ae)"}% returns fear, %CALC{"$TRANSLATE(one, two,$comma,;)"}% returns one; two |
For example, use
$TRANSLATE(Help, Messages, Menu,$comma,;) to get
Help; Messages; Menu, which you can then pass to
$FIND()
--
PeterThoeny - 28 Jul 2003
Thanks Peter, just what I needed to get it going!
--
ChrisHogan - 28 Jul 2003
Can you use this plugin inside or outside a %SEARCH{ .. }% ? I've tried it both ways and it can't seem to handle the nested strings because of the way twiki matches the double quotes for variables. I'm trying to use REPLACE to alter the %TOPIC% for the text parameter of the search.
--
SamHasler - 16 Sep 2003
Sam: You need to consider the variable expansion order: First preferences, then internal variables (like
%TOPIC% followed by
%SEARCH{}%), then
%INCLUDE{}%, then Plugin variables, followed again by prefs, internal and include. You can control the Plugion execution order with
TWikiPreferences' INSTALLEDPLUGINS.
Since you are trying to apply a CALC to a TOPIC used in a SEARCH, you need to delay the SEARCH until after the CALC. The trick is to compose the SEARCH with CALC:
%CALC{ "$NOP($perSEARCH{\"$REPLACE(%TOPIC%,-10,10,)\")" }% nosearch="on" format="$topic" separator=", " %CALC{ "$NOP(}$per)" }%
Which cuts the "DevArchive" off of the current topic name
SpreadSheetPluginDevArchive and build a list of topics mentioning the resulting topic name:
%CALC{ "$NOP($perSEARCH{\"$REPLACE(%TOPIC%,-10,10,)\")" }% nosearch="on" format="$topic" separator=", " %CALC{ "$NOP(}$per)" }%
--
PeterThoeny - 12 Oct 2003
I created a $MULT(list) function. This returns the product of the list of cells. Insert this just below the $AVERAGE function implementation.
diff -r orig/data/TWiki/SpreadSheetPlugin.txt new/data/TWiki/SpreadSheetPlugin.txt
54a55
> | =="$MULT(list)" | The product of a list or range of cells. Example: to calculate the product of the cells to %BR% the left of the current one use ==%nopCALC{"$MULT($LEFT())"%== |
diff -r orig/lib/TWiki/Plugins/SpreadSheetPlugin.pm new/lib/TWiki/Plugins/SpreadSheetPlugin.pm
335a336,343
> } elsif( $theFunc eq "MULT" ) {
> $result = 0;
> my @arr = getTableRangeAsFloat( $theAttr );
> $result = 1;
> foreach $i ( @arr ) {
> $result *= $i if defined $i;
> }
>
--
GerritJanBaarda - 25 Sep 2003
I am trying extract values of variables from topics and sum them. The extraction works fine, but the sum does not.
This is the text of my summing topic:
| *Title* |
%SEARCH{search="CONTRACT = (1)" format="|%$topic.CONTRACT_VALUE%|" nototal="on" nosearch="on" regex="on"}%
| %CALC{"$SUM( $ABOVE() )"}% |
| *Title* |
| 1000 |
| 2000 |
|%CALC{"$SUM( $ABOVE() )"}%|
and here is the rendered page:
What am I doing wrong? I must be so close!
--
AlainMivilleDeChene - 05 Oct 2003
Alain: I am not sure what
%$topic.CONTRACT_VALUE% does in your example, seems like some extension you did. If this is the case, it does not work because that value might get evaluated by a Plugin
after the Spreadsheet Plugin. Try to execute the Spreadsheet Plugin early (in
TWikiPreferences' INSTALLEDPLUGINS setting)
The following example works as expected. It searches for all table rows on this topic that have a number in a cell, and sums the first column.
| *Title* |
%SEARCH{ "Feedback on SpreadSheetPlugin;\| +[0-9]+ +\|" limit="1" format="$text" nototal="on" nosearch="on" regex="on" multiple="on" }%
| Sum: %CALC{"$SUM( $ABOVE() )"}% |
--
PeterThoeny - 12 Oct 2003
Thanks. Specifying priority of execution did the trick. The
%$topic.CONTRACT_VALUE% is a topic variable generated with the
TopicVarsPlugin.
--
AlainMivilleDeChene - 16 Oct 2003
Feature request: "List" and "range" are used interchangeably in the spreadsheet doc. Most functions expect a range like
R2:C5..R7:C5, and can't handle a comma delimited list of parameters.
The Plugin could be enhanced to allow ranges
and/or a variable list of parameters. That is, it can only be done for functions where the number of parameter can be increased without conflict like for
$SUM(). That way one could use a formula like
$SUM( R2:C5, R4:C5..R6:C5, R10:C5 ).
--
PeterThoeny - 12 Oct 2003
Robert Withrow: SUMPRODUCT sounds useful. It would be nice to allow a list of ranges.
Sven Dowideit: Does this cover the functionality of your DURATION feature?
Darryl Green: I have not yet had time to look at your solution. Spinning your idea further, we could enhance the Plugin with variables 'set' and 'get':
- Set variable:
$SET( foo, $SUM($ABOVE()) )
- Get variable:
$GET( foo )
The variables would be stored in a hash and would live for the time of the topic view. Among others, that could be used to memorize values across tables, e.g. do the "Three Bit Thing Parts List" example.
Lars With: Localized dates and formatting is needed. Not sure, but it might be more flexible to put this into the TWiki core (like the Date and Time setting in the control pannel of an OS), for Plugins to use.
--
PeterThoeny - 21 Oct 2003
The following example on the plugin page is incorrect.
%CALC{"$SUM( R2:C$COL(0)..R$ROW(-1):C$COL(0) )"}%
it should use
COLUMN instead of
COL.
--
JonathanCline - 28 Oct 2003
Before I start coding those things myself: Is there a reason why subtraction and division of cell-contents are not implemented so far? Any know pitfalls besides 'division by 0'?
The need arose when checking out features for project/development/bug-tracking (apart from XpTrackerPlugin): work estimated-spent=remaining, ratio of bugs fixed etc.
--
PatrickHansmeier - 08 Dec 2003
No particular reason for not implementing this besides not having had a need for it; the
$CALC() can be (mis)used for it.
Go for it
--
PeterThoeny - 09 Dec 2003
You mean
$EVAL, don't you? I tried that one, too and re-checked it (see
SpreadSheetSandbox, but it can't do calculations on table-adresses. That's what I need (and maybe didn't state clearly before).
--
PatrickHansmeier - 09 Dec 2003
Oops, yes,
$EVAL. I fixed your example in the Sandbox web, you need to reference cells with
$T(...).
--
PeterThoeny - 09 Dec 2003
It's useful to point out that ** can be used with $EVAL for exponentiation functions, as Perl's syntax allows.
The syntax $EVAL("1-$T(R1:C1)") will give an error if R1:C1 is negative. The parser reduces it to "1--5" if R1:C1 is -5. Perl then wants to do a postdecrement. I fixed this by adding spaces to the list of characters in saveEvalFunc and inserting spaces into my string. But I know that this isn't a good fix.
--
BrentRoberts - 12 Jan 2004
I think this falls under more of a feature request, but what I believe is needed is the option to define Spreadsheet functions but instead of the function definitions being "inline" they are instead defined outside of the table construct such that the values saved in the actual TWiki topic are the
results of the function, not the function itself. I suppoose I am invisioning some kind of cross between
EditTablePlugin and
SpreadSheetPlugin where one would be thrown into a
EditTablePlugin-ish edit window in order to change table values but with a new button that said
CALCULATE. The resulting table would be updated with the actual values. Why do this? Well, I have been spending a good amount of time trying to figure out how to get calculated values out of a table and doing so is like trying to pull teeth from a raging bear - in fact, I haven't found a way yet.
One simpler possibility is to make it so that the $SET and $GET functions create true TWIKI variables that are accessible within the entire web - very similar to how
TopicVarsPlugin works.
I'm not a Plugin developer -- yet -- but I keep coming across missing pieces so I have to make appeals to the Plugins community. I am wide open for suggestions on how to make calculated results useful outside of a single TWIKI topic.
--
SteveRJones - 16 Jan 2004
I am trying to figure out how I can do control flow in the
SpreadSheetPlugin. I want to extract all rows that satisfy some constraint on one of the column values and then do some operations on them. As an example: how can I sum the number of days required for all Open tasks in the example in the
SpreadSheetPlugin page? If I am having a Duh moment, let me know.
--
KiranBondalapati - 20 Jan 2004
Steve, where do you want the results to go? Do you want to refer to the results of a calculation in another page? Or are you seeking to export? If the former, then I'm afraid I think you're on a hiding to nothing, unless you can somehow do a "hidden include" of the page containing the spreadsheet. If the latter, then I've had considerable success linking TWiki pages to Excel to extract the tables there.
If you really, really want to make results accessible, then it
is possible to save the results in the topic; by post-processing the topic on save. This is how the action tracker does syntax checks and fills in UID values on actions. You could even do this with a "companion plugin" to spreadsheet -
PersistentSpreadsheetPlugin? - that
only performs this post-process function to fix persistent values. You could probably crib a lot of the relevant code from the
ActionTrackerPlugin.
Bite the bullet. Become a plugin developer. You know it makes sense
--
CrawfordCurrie - 20 Jan 2004
After looking through the
SpreadSheet? plugin code, it was not very difficult for me to add new functions that served my purpose for selecting specific rows based on values in one specific column etc. I could pretty easily add lot of other functions like VECTORSUM, VECTORADD, SCALARVECMULT, EXTRACTCOLUMN, EXTRACTROW, etc.
However, I realized that one of the basic issues I had was that
SpreadSheetPlugin functions are not really oriented towards taking multiple rows or columns as raw results and processing them in hierarchical fucntions. Acceptable, I guess, since you wouldn't know where the rows/cols end. e.g. Is 3, 2, 2, 4, 2, 3 a 2x3 array or 3x2 array? Since I have not written Excel or Matrix libraries in a while, my functions had to take numrows or numcols in different functions. it would have been good if the
SpreadSheetPlugin had illustrated a clean mechanism to pass around matrix/row/col objects. Oh well, I guess I will write all the formulas for my manager
--
KiranBondalapati - 21 Jan 2004
Crawford, you are one funny guy. Yes, it does make sense and when I look at the code it looks like a completely foreign language to me - just like Fortran, Pascal, C and Assembler did in my long ago daze ;-> I know that it is just a matter of time and one day it will go "poof" - epiphany time. In the meantime I am trying to deliver some tools and was hoping beyond hope that some enterprising plugin developer out there would have pity. Perhaps a "Dummies Guide To Writing Plugins", a "Dummies Guide to Regular Expressions", and "Everything You Always Wanted To Know About Perl But Were Afraid to Ask" is what i need :^(
Nevertheless, to answer your question, post-processing the topic would also be a way to solve this as long as the resulting "table of values" was hidden from normal view. Sounds like each table that included a %CALC% operation would need to be postprocessed and a result table inserted. A companion function like your %ACTIONSEARCH% could then extract those values?
I will muse on this -- I very much appreciate the feedback. Don't be a stranger -- just be strange ;-{>
--
SteveRJones - 21 Jan 2004
Strange? Let the kettle call the pot black...
I had in mind that you could just embed the calculation result as a set. But yeah, you'd end up having to duplicate
SpreadSheetPlugin's function to get the result.
To write a plugin, take a copy of the
DefaultPlugin and rename and munge that. It's easy. Perl regular expressions are also easy; it's the whole of the rest of the language that's geek greek.
--
CrawfordCurrie - 21 Jan 2004
On
EditTablePlugin and
SpreadSheetPlugin: You can use both, but only if the formulae are placed in label cells.
Unlike M$ Excel, you can refer to cells also after a table. That is, you can do some statistics on the table located above you.
If you want to carry values over to another topic you have two choices:
- In your own Plugin, use the
beforeSaveHandler callback to persistently save some table values into the topic text or into a form field (as Crawford pointed out)
- INCLUDE other topics containing spreadsheets, using $GET and $SET to roll up totals. See SpreadSheetRollupTest
Here are some ideas on how to do the persistent save:
- Add a line like this after the table:
-
Total: %CALC{"$T(R1:C5)"}% <!-- TOTAL: 0 -->
- In
beforeSaveHandler do this:
- In case the text contains and HTML comment with TOTAL do:
- Copy the topic text to a temporary variable
- Use
TWiki::Func::expandCommonVariables on the temporary variable to resolve the whole topic text, including the Total formula before the HTML comment
- Extract the number before the HTML comment
- In the original text, replace number after TOTAL in the HTML comment with the extracted number
- In other topics include that topic and extract the TOTAL number in the HTML comment
--
PeterThoeny - 01 Feb 2004
This would be a useful enhancement: A new
$FIELD() function to pull named fields from other places. A field could be:
- a bullet in a topic, like the
* Name: bullet in a user home page:
- a table row with two columns, like:
- a form field of TWikiForms
Possible syntax:
"$FIELD(name, topic)" | Pull a named field from a bullet, a table, or a form field. The current topic is used in case the topic parameter is omitted. Examples:
%CALC{"$FIELD(Name)"}% returns John Smith assuming the topic has a row like | Name: | John Smith |
%CALC{"$FIELD(Name, Main.JohnSmith)"}% returns John Smith from the Name bullet of the user's home page
%CALC{"$FIELD(TopicClassification)"}% returns the value of that form field |
Any feedback on the functionality and syntax?
--
PeterThoeny - 01 Feb 2004
I like the idea of extending the current plugin and providing options for creating persistent data. One concern that I have is trying to "flatten" a two dimensional data structure into one dimensional "fields". We lose the idea of C:R naming
unless the C:R is somehow preserved. I like the addition of the %FIELD% function but where another parameter would be the cell/row reference in cases where we were looking at a table. Perhaps something as simple as:
- a table row with two columns, like:
| Name: | John Smith | Steve Jones |
"$FIELD(name, column, topic)" | %CALC{"$FIELD(Name, C2)"}% returns John Smith assuming the topic has a row like | Name: | John Smith |Steve Jones | |
The ROW is already known because the lookup of %FIELD{name}% tells us which row to look in.
In the meantime I will work on the "hidden tables" method of doing spreadsheet rollups
--
SteveRJones - 02 Feb 2004
What does the
1, 1 refer to?
--
PeterThoeny - 03 Feb 2004
Aack!!
See above example for something that makes sense.
BTW, I worked yesterday on some stuff and discovered that a $CURRENTCELL function would be nice instead of having to cobble together something like:
%CALC{"$SUM($ABOVE())"}% %CALC{"$SET(TotNumHosts, $T(R$ROW():C$COLUMN()))"}%
in order to load the variable with the value of the current cell. Unless (and I didn't check) doing something like $T() automatically assumes the current cell -- in which case ignore the above diatribe! I didn't think to check that, I was too happy with getting the
SpreadSheetRollup? problem resolved - thanks to your example in the sandbox (one must think in entirely different ways under TWiki!)
--
SteveRJones - 04 Feb 2004
I enetered a simple formula
%CALC{"$SUM($ABOVE())"}% and got the result 1.77635683940025e-15 when I was expecting 0. Fine, floating point numbers do that. So I tried
%CALC{"$INT($SUM($ABOVE()))"}% and I got the answer 13!
%CALC{"$INT(1.77635683940025e-15)"}% also gives me 13.
--
BryanLarsen - 05 Feb 2004
I've re-created your examples in
VariousSpreadSheetCalcs and the expected results are the results that are presented - definitely not what you are getting on your system. Perhaps something strange is going on with your system? Math libraries gone awry?
--
SteveRJones - 06 Feb 2004
Now why exactly does %CALC{"$INT(1.77635683940025e-15)"}% result in
-13 on
VariousSpreadSheetCalcs?
--
ThomasWeigert - 06 Feb 2004
Pretty obvious, really. 1.77635683940025 - 15 = -13.2236 which rounds to -13
--
CrawfordCurrie - 07 Feb 2004
This is an artefact of a simple security measure. Any characters other then
0-9 . + - * / ( ) are removed before evaluating an expression, that is only fractual numbers are supported.
--
PeterThoeny - 08 Feb 2004
In that case, I would think that the output should also only be in numbers that could, in turn, be used as input when literally typed into the text. The above example (I have not actually tried it) shows data being output in exponential format but, as you describe, being treated as a subtraction upon input.
--
ThomasWeigert - 08 Feb 2004
Umm, I thought the
real point of the original questions was that
%CALC{"$SUM($ABOVE())"}% returned a value other than
0. I haven't gone into the code (should have) but in my mind wouldn't this be analogous to dumping the value of what is basically an uninitialized variable? Probably not the same, but what is $ABOVE() really referring to? The topic being rendered does not have a prior table so would there would not be a hashed table in memory - correct? The example that I did in the sandbox indicates to me that at least on twiki.org the expected result is being returned
if the developer thought far enough ahead to make sure that (what I call) unitialized variables returned something sane/predictable.
--
SteveRJones - 09 Feb 2004
Gentlemen, perhaps you are asking a bit much of this plugin? It is, after all, a bit of a hack (sorry Peter). If you are really serious about spreadsheeting, wouldn't you be better off looking at the
blueshoes javascript? You might even buy a license - I'm sure a big company like Motorola can afford it, whetever the share price
Were you to donate the resulting plugin / demo/ tempate back to twiki.org, so much the better.
--
CrawfordCurrie - 09 Feb 2004
If and when I need some real spreadsheeting that may very well be an option - and it looks like one may obtain a license for development work and general "playing around". For
my foreseeable future,
SpreadSheetPlugin does precisely what I need (well, ok, 95% of what I need). As for the rest of the community . . . ?
--
SteveRJones - 09 Feb 2004
Crawfort is absolutely right. I created the initial version of is Plugin because I needed some very simple statistics in a table footer row. In the mean time it has grown to have over 40 functions. It's the 80/20 rule, you can get a lot of things done with the current version but not all. This Plugin will be enhanced evolutionary.
KiranBondalapati mentioned one possible enhancement for calcs on two-dimentional arrays. Not so difficult to implement. However, it is more challenging to find a good syntax that is compatible with the one dimentional list of parameters. Any idea?
--
PeterThoeny - 10 Feb 2004
Peter, getting back to basics, you created quite an interesting app in
ChangeRequest and the shear elegance of figuring out what the next Change Request number would be really hit home with me. One question: you appear to be making use of an "undocumented feature" in the $EVAL function in that given an arbitrary string, $EVAL will return
only the numerical components of that string. I can't find that in the
SpreadSheetPlugin doc and I never would have latched onto that if it hadn't been for your demo. Will this feature of $EVAL continue forward and if so can the doc be updated to reflect this? Or did I really, completely mis-read the doc?
thanks
- For security and convenience,
$EVAL() and $INT() strip all illegal characters before evaluating a formula (which does not always work as expected). As a side-effect, this can be used to grab the numeric part from a topic name. However, I think it is better to add a new function to extract a number from a string -- PeterThoeny - 28 Feb 2004
--
SteveRJones - 13 Feb 2004
I am trying to get icons show up in a table, but somehow I only get the same icons. What am I doing wrong?
- Strange, in edit mode the icons show up as
--
ArthurClemens - 14 Feb 2004
I fixed above table. Issues:
- The CALC executes after ICON, that is, you need to defer ICON by placing and escaping it inside the CALC (as you did)
- The
$T() grabs the whole cell content, including leading and trailing space. Use $TRIM() to get rid of the white space: $TRIM($T(R$ROW(0):C$COLUMN(-1)))
- The ICON gets now executed after the Plugins, which resulted in this URL:
http://TWiki.org/cgi-bin/view/Plugins/%PUBURLPATH%/icn/else.gif, e.g. the %PUBURLPATH% does not get resolved if introduced late by a Plugin. Fix for twiki/lib/TWiki/Attach.pm (now in TWikiAlphaRelease and TWiki.org) :
RCS file: /cvsroot/twiki/twiki/lib/TWiki/Attach.pm,v
retrieving revision 1.31
diff -r1.31 Attach.pm
102c102
< my $iconUrl = "%PUBURLPATH%/icn";
---
> my $iconUrl = "$TWiki::pubUrlPath/icn";
- You need to escape the CALC in EDITTABLE's format:
| label, 10, $percntCALC{$NOP($perICON{)$TRIM($T(R$ROW(0):C$COLUMN(-1)))$NOP(}$per)}$percnt |
--
PeterThoeny - 28 Feb 2004
Peter, the released version doesn't appear to be in
CVS......?
--
CrawfordCurrie - 29 Feb 2004
Yep, I need to find time to check in all previous releases up to the latest one. The latest version is always available in the
SpreadSheetPlugin topic.
--
PeterThoeny - 01 Mar 2004
I need some help from a Perl guru.
Currently, functions get evaluated from left to right, then from inside out, regardless of the parameters. Example, spelled out in evaluation order:
-
$ONE( first, $THREE(foo, $TWO()), $FIVE(bar, $FOUR()), $SEVEN(moo, $SIX()), last )
This is usually OK, but fails for the
$IF( condition, value_if_true, value_if_false ) function. First
condition,
value_if_true,
value_if_false are all evaluated, then the condition is looked at, then one or the other value is returned.
The proper way is to separate all parameters of a level first, then to evaluate each parameter as needed. Taking above example, when evaluating
$ONE() we should first split up the parameters into:
-
first
-
$THREE(foo, $TWO())
-
$FIVE(bar, $FOUR())
-
$SEVEN(moo, $SIX())
-
last
Then look at each parameter separately, based on what
$ONE() is supposed to do.
As you can see it is not possible to simply split at the commas, since a comma can belong to a nested function.
How can I parse the parameters of the current level correctly?
Once this problem is solved, the limitation of
$IF() can be resolved. In addition, a
$FOREACH(action with $FUNCTIONS(), item 1, item 2, etc) can be implemented that traverses a list and takes an action on each item.
--
PeterThoeny - 13 Mar 2004
I solved above problem with regular expressions. I did not want to introduce a parser because of performance. Stay tuned for a new release.
Bug in latest release: EVAL does not return the error message, e.g. division by zero.
--
PeterThoeny - 14 Mar 2004
Peter, is it possible to suppress the error message that gets attached to the rendered page at the top (see
VariousSpreadSheetCalcs) and simply provides the error message at the location where the value is to be returned?
--
SteveRJones - 15 Mar 2004
Forgive me if I'm wrong, but AFAICT this plugin has not been checked into
CVS for a very long time. Shouldn't we be removing the "Developer Version in
CVS" flag for plugins in this state? Where the revision controlled version is such a long way behind the release?
--
CrawfordCurrie - 18 Mar 2004
When I find time I will check in all releases of this Plugin. In the mean time, get the latest version from the Plugins topic.
--
PeterThoeny - 16 Mar 2004
non-maintainer update synchronized cvs with released zip version, which was ahead of the cvs version
| Internal plugin version: | 21 Mar 2004 |
| Zip version: | 1.26 |
--
MattWilkie - 02 Apr 2004
Thanks Matt!
--
PeterThoeny - 03 Apr 2004
non-maintainer update synchronized cvs with released zip version, which was ahead
| Internal plugin version: | 03 Apr 2004 |
| Zip version: | 1.27 |
| Author's Comment: | (updated docs) |
--
MattWilkie - 06 May 2004
Could we have working days calculation please? Here's the function that does it:
=begin text
---++ workingDays( $start, $end ) -> days
Calculate working days between two times. Times are standard system times (secs since 1970). Working days are Monday through Friday (sorry, Israel!)
=cut
sub workingDays {
my ( $start, $end ) = @_;
use integer;
my $elapsed_days = ( $end - $start ) / ( 60 * 60 * 24 );
# total number of elapsed 7-day weeks
my $whole_weeks = $elapsed_days / 7;
my $extra_days = $elapsed_days - ( $whole_weeks * 7 );
if ( $extra_days > 0 ) {
my @lt = localtime( $start );
my $wday = $lt[6]; # weekday, 0 is sunday
if ($wday == 0) {
$extra_days-- if ( $extra_days > 0 );
} else {
$extra_days-- if ($extra_days > (6 - $wday));
$extra_days-- if ($extra_days > (6 - $wday));
}
}
return $whole_weeks * 5 + $extra_days;
}
Obviously it doesn't handle holidays, but it's a pretty good rule-of-thumb for planning exercises.
Something like
$WORKDIFF or
$WORKING might be a good function name.
Thanks
--
CrawfordCurrie - 15 Jul 2004
Not sure what's going on - I'm building a new Cairo and the distro'd version beta TWiki20040507beta.zip merged with twiki-20040711alpha.tar.gz came with 03 Apr 04 version of spreadsheet plugin. This failed to load, but when replaced with the 24 May version it worked fine.
--
MartinCleaver - 16 Jul 2004
Martin, this is a packaging error, the Alpha release should only contain the
EmptyPlugin and
DefaultPlugin contain Plugins. This is caused by the recent switch to SVN.
--
PeterThoeny - 17 Jul 2004
What does $LISTINDEX do? it doesn't appear in the documentation or the history.
also, I'd like an optional parameter on
$LIST( range ) to convert commas in cells to something else for when I'm processing cells that have user editable/dynamic text in them.
--
SamHasler - 30 Jul 2004
$LISTINDEX() was a typo in the change history, was fixed to the
$LISTITEM() function.
On list terminator, I am considering to make this configurable. Another useful feature would be quoted parameters like
$SOMETHING(1, "second param, with comma", 3)
--
PeterThoeny - 30 Jul 2004
I have/had the problem to use the plugin for calculations in a german environment. That means the thousand separator and the decimal separator have to be swapped (21.235,34 instead of 21,235.34). In the first place this is an issue to the FORMAT-function only. But upgrading the FORMAT-function is not enough. Cells with swapped separators have to be handled correctly by other functions too. The resulting concept is the following:
- all cells (manually filled or calculated) are formatted with swapped separators (or all cells are not not)
- only the FORMAT-function produces numbers with swapped separators (otherwise I found no way for recursive calls of doFunc to work properly)
- the separators can be declared to be swapped by e preference setting
Together with the swapped separators in Germany (and a lot of other countries) amounts are formatted differently to dollar amounts in USA (and other). Altogether I have introduced the following preference settings (CALCATTRIBUTES):
- swapon
- default is false; true swaps '.' and ','
- amountfmt
- no default; generic format for positive amounts
- negamountfmt
- no default; generic format for negative amounts
- dollarfmt
- default is amountfmt in case amountfmt is defined, '!#' otherwise; format for positive dollar amounts
- negdollarfmt
- default is negamountfmt in case negamountfmt is defined, '(!#)' otherwise; format for negative dollar amounts
- eurofmt
- default is amountfmt in case amountfmt is defined, '# !' otherwise; format for positive € amounts
- negeurofmt
- default is negamountfmt in case negamountfmt is defined, '-# €)' otherwise; format for negative € amounts
- percfmt
- default is '#%'; format for percent values
In a format string the '#' stands for the value respective the amount and '!' stands for the currency. I added a new possible first parameter 'EURO' similar to 'DOLLAR' for the FORMAT function.
With these changes implemented, I could add the following setting to the preferences and get the results satisfying german (and other) culture:
-
- Set CALCATTRIBUTES = swapsep="1" amountfmt="# !" negamountfmt="-# !" percfmt="# !"
This quick implementation of my urgent needs can be found as an attachment of this topic (
http://twiki.org/p/pub/Plugins/SpreadSheetPluginDevArchive/SpreadSheetPlugin.pm.euro). Hopefully this or a similar implementation flows into the plugin.
- Lars: Localized dates and formatting is needed. Not sure, but it might be more flexible to put this into the TWiki core (like the Date and Time setting in the control pannel of an OS), for Plugins to use. -- PeterThoeny - 21 Oct 2003
--
LarsWith - 13 Sep 2003
The following patch allows:
- string conversion into serialized-seconds for strings formatted as "4:15 PM" or "8:30 AM" or "23:59"
- pattern:
\d\d:\d\d\s*[a|p]?m?
- display of time (TIMEDIFF) in
hms format (HH:MM)
-
01:15
- ok, it's called
hms format, but there's no s displayed (hh:mm vs. hh:mm:ss)
- other spreadsheets will overflow/autofit results if
hh > 23 to dd:hh:mm format, so that 25 hours becomes 1 day, 1 hour.
With the above two features, the differences between two times can be calculated and displayed with the formula
like this:
%CALC{"$TIMEDIFF($TIME($T(R$ROW(0):C$COLUMN(-2))),$TIME($T(R$ROW(0):C$COLUMN(-1))), hms) "}%
(Note, I have fixed my earlier patch which had a problem with "12:xx pm" and "12:xx am". The new patch is below.)
$ diff SpreadSheetPlugin-1-006.pm SpreadSheetPlugin.pm
549a550
> my $h, $m;
559c560,564
<
---
> if ($scale =~ /^hms/) {
> $h = int($result / 3600);
> $m = int($result / 60) % 60;
> $result = sprintf("%02d:%02d", $h, $m);
> }
759a765,777
> } elsif( $theText =~ m|([0-9]{1,2}):([0-9]{2})\s*([ap]?)m?|i ) {
> # "23:59" or "12:59PM" or "12:59 PM"
> $day = 1; # arbitrary -- use 'today' ?
> $mon = 0; # arbitrary -- use 'today' ?
> $year = 100; # arbitrary -- use 'today' ?
> $hour = $1;
> $min = $2;
> if (($3 eq "p" || $3 eq "P") && $hour != 12) {
> $hour = $hour + 12;
> }
> if (($3 eq "a" || $3 eq "A") && $hour == 12) {
> $hour = 0;
> }
--
JonathanCline - 13 Nov 2003
It would be most helpfull if every function that has a
text parameter supported the last five variables in the
FormattedSearch format string table:
| Name: | Expands To: |
|---|
$n or $n() | New line |
$nop or $nop() | Is a "no operation". This variable gets removed; useful for nested search |
$quot | Double quote ("). Alternatively write \" to escape it |
$percnt | Percent sign (%) |
$dollar | Dollar sign ($) |
I know $nop() is supported but $nop isn't. I particularly would like to be able to include newlines, although I imagine the others would be usefull too. Perhaps they could use the same code to avoid duplication?
- It might introduce incompatibilities with existing text if we support the five variables in any formula that accepts text. Also, the variable syntax $nop() might be confusing with the formula syntax $NOP(). BTW, the $NOP() expands a $per variable which is also confusing. Therefore I consider to retire $per as a undocumented feature since $NOP($per) and $NOP(%) do the same thing. -- PeterThoeny - 21 Sep 2004
I know that this is off topic but I also noticed on
TWikiVariables that while the %SEARCH{}% separator parameter accepts $n the %URLPARAM{}% separator uses \n. Was there a technical reason why this was done or could $n also be supported by URLPARAM to avoid confusion.
- This is not exactly the case. SEARCH's format parameter accepts $n; the separator parameter of URLPARAM and SEARCH is a newline by default and can be set to something else. There is a small inconsistency where $n can be used in SEARCH's separator but not in URLPARAM's separator. -- PeterThoeny - 21 Sep 2004
--
SamHasler - 19 Aug 2004
You can fake the comma conversion by using
$TRANSLATE(). The hard part is finding a character to translate the commas into that will not be used.
Here is what a table would look like with
%CALC{ $TRANSLATE( $T( R$ROW(0):C2 ) , $comma, ^)}% in the
Converted column.
| Row Number | Unconverted | Converted |
|---|
| 1 | blah, blah, blah | blah^ blah^ blah |
| 2 | I, like, commas | I^ like^ commas |
| 3 | yadda, yadda, yadda | yadda^ yadda^ yadda |
| 4 | This will cause^ problems^ converting back | This will cause^ problems^ converting back |
To do processing after the table, use
$LISTMAP() and
$TRANSLATE() to return a list of converted values. You just need a column that you know will never contain commas and use the $index to access the table rows.
To return a list with the commas converted to '|':
$LISTMAP( $TRANSLATE( $T( R$EVAL($index+1):C2 )$SET( junk, $item ), $comma, | ), $LIST( R1:C1..R$ROW(-1):C1 ) ) returns
"blah| blah| blah, I| like| commas, yadda| yadda| yadda, This will cause^ problems^ converting back"
The
$SET( junk, $item ) is necessary because
$LISTMAP() appends
$item to the output if it is not included somewhere in the formula.
To convert the commas back, just run it through
$TRANSLATE( [formula above] , |$comma, $comma|) to swap the comma and '|'.
--
ThomasMundar - 05 Sep 2004
If you need to put special characters inside
%CALC{}%, you can use the
$CHAR() function.
$CHAR(10) is a newline and
$CHAR(9) is a tab character.
%CALC{$CHAR(9)* Test$CHAR(10)$CHAR(9)* Test 2}% outputs:
You can also use
$CHAR() to
$TRANSLATE() commas into an unprintable character when including them in lists (from yesterday's comment).
--
ThomasMundar - 06 Sep 2004
Probably there is TIMEDIFF calculation error, described in
TimeCalculationError - or my mistake?
--
AndrzejGoralczyk - 17 Sep 2004
I have a suggestion to add extra format parameters, K and MB, to FORMAT:
} elsif ($format eq "K") {
$result = sprintf("%0.${res}f K", $value / 1024);
} elsif ($format eq "MB") {
$result = sprintf("%0.${res}f MB", $value / (1024 * 1024));
} else {
--
ArthurClemens - 21 Sep 2004
Arthur, shoudn't that be "KB" instead of just "K"?
--
PeterThoeny - 21 Sep 2004
I think that 'K' is common shorthand for KB. In the attachment table it works fine.
--
ArthurClemens - 22 Sep 2004
I googled around and found that most dictionaries document "KB" for 2^10. I implemented both but documented only the "KB" version.
--
PeterThoeny - 26 Sep 2004
FYI: my observation is both K and KB are used. Note the erroneously Kb (which means kilobit) in some explanations.
In computer literature, kilobyte is usually abbreviated as K or Kb. http://www.webopedia.com/TERM/k/kilobyte.html
To distinguish between a decimal K (1,000) and a binary K (1,024), the IEEE has suggested using a small k for a decimal kilo and a capital K for a binary kilo. In international English, outside the U.S.A., the equivalent unit is sometimes seen as "Kb" or "kbyte." http://www.netlingo.com/right.cfm?term=kilobyte
Abbreviated as K or KB a Kilobyte is equal to 1,000 bytes. With a lowercase b (Kb), this refers to a kilobit. http://www.computerhope.com/jargon/k/kilobyte.htm
Since 2^10 = 1,024 ? 1,000 = 10^3, computer scientists, engineers and programmers alike created the habit of calling 1,024 bytes a kilobyte; they wrote "KB", sometimes abbreviated as "K", to differentiate this from the 'borrowed' SI prefix. http://en.wikipedia.org/wiki/Kilobyte
Notes: Kilobyte is abbreviated as "K" or "KB." http://www.tekmom.com/buzzwords/zdkilo.html
--
ArthurClemens - 26 Sep 2004
There is also the "kibibyte" (and mibibyte and gibibyte) notation. The abbreviations are "Ki", "Mi", and "Gi", respectively. That has been recommended by the IEC as the new standard for 2^10 (2^20, 2^30), reserving kilo-, mega-, giga- to refer to 10^3, 10^6, and 10^9, respectively. Some information on this can be found at:
The adoption rate of this terminology does seem to be rather slow, however.
--
DavidBright - 27 Sep 2004
Thanks for the pointers. The Wikipedia one (
http://en.wikipedia.org/wiki/Kilobyte) is an interesting read.
The more you look into it, the more confusing it gets
--
PeterThoeny - 02 Oct 2004
On "The Weakest Link" the other day one of the questions was "how big is a kilobyte? 1000 or 1024 bytes?". The "correct" answer was 1024 bytes.
If Anne Robinson says it is, it must be true. Who wants to argue with Anne Robinson?
--
CrawfordCurrie - 02 Oct 2004
Should
%!CALC{$PROPERSPACE(Main.MartinCleaver)}% return "Martin Cleaver" ?
It returns
MartinCleaver
--
MartinCleaver - 15 Oct 2004
No, the doc has "Properly spaces out
WikiWords preceeded by white space, parenthesis, or ][". In your case, write
$PROPERSPACE(MartinCleaver) instead of
$PROPERSPACE(Main.MartinCleaver). Or cut the
Main. of with
$PROPERSPACE($SUBSTITUTE(Main.MartinCleaver,Main.))
--
PeterThoeny - 16 Oct 2004
I am needing a way to determine if a topic exists or not so I ended implementing an "$EXISTS(topic)" function to test for a topic existence. Here is the diff:
*** SpreadSheetPlugin.pm.old 2004-10-19 17:52:38.000000000 -0400
--- SpreadSheetPlugin.pm 2004-10-19 18:47:27.000000000 -0400
***************
*** 795,800 ****
--- 795,810 ----
# for example the %SEARCH{}% variable
$theAttr =~ s/\$per/%/g;
$result = $theAttr;
+ } elsif ( $theFunc eq "EXISTS" ) {
+ my $theTopic = $theAttr;
+ my $theWeb;
+
+ if ( $theTopic =~ s/^(.*)[\.\/](.*)$/$2/o ) {
+ $theWeb = $1;
+ }
+
+ return TWiki::Func::topicExists( $theWeb, $theTopic );
+
}
&TWiki::Func::writeDebug( "- SpreadSheetPlugin::doFunc: $theFunc( $theAttr ) returns: $result" ) if $debug;
I hope it is not too ugly to be included into
SpreadSheetPlugin :P
--
RodrigoChandia - 19 Oct 2004
$EXISTS() is a nice idea and useful.
--
PeterThoeny - 20 Oct 2004
Bug? Background: I've got a table with a single person's name and
contact details appearing in a single row, with an email address
appearing in a certain column. There are ~25 people entered.
I'm trying to build a quick-and-dirty
mailing list manager from this table by constructing a
mailto URI from looking
up and constructing a list of the email addresses.
Using
SpreadSheetPlugin version 18 Oct 2004, when placed in the cell just below the last email adddress the following
incantation works for tables with a couple of entries, but not for more than about 4 entries.
<a href="mailto:%CALC{ "$LIST(R2:C$COLUMN()..R$ROW(-1):C$COLUMN())" }%">Click to mail everyone.</a>
Here's an example of what I'm trying to do:
and another example that fails:
Plugins Web > SpreadSheetPlugin > SpreadSheetPluginDev > SpreadSheetPluginDevArchive