Tags:
create new tag
, view all tags

Older feedback archived from SpreadSheetPluginDev

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. smile

-- 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:

1 2 3
4 5 6
7 8 9

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 )"}%

  • You get: 12 ; 15 ; 18

-- 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

Assembly Parts Count Cost
First Bit    
Second Bit    
Third Bit    
TOTAL 0 0

-- 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:

Title
1000
2000
0

Title
1000
2000
3000

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 smile

-- 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 hehe!

-- 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 smile

-- 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... roll eyes (sarcastic)

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:

  1. 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)
  2. 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:
    • Name: John Smith
  • a table row with two columns, like:
    Name: John Smith
  • 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!! embarrassment 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 hehe! 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?

File Version Uploaded by Date Type label Comment
link to an attached file 1.0 ArthurClemens 2004/02/10 GIF GIF  
link to an attached file 0.1 ArthurClemens 2004/02/20 PDF PDF  
link to an attached file 12 ArthurClemens 2004/02/04 DOC DOC  
testing field values - should be DOC     28 Feb 2004 16:57 DOC DOC  
testing generated string - should contain: percentageICON{ JPG }percentage       JPG percentageICON{ JPG }percentage  
quotes are not needed for ICON:         JPEG  
case is not important for ICON:         PDF  
test     28 Feb 2004 17:01 GIF GIF  

  • Strange, in edit mode the icons show up as Empty transparent 16x16 spacer

-- 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, '#&nbsp;!' otherwise; format for positive € amounts
negeurofmt
default is negamountfmt in case negamountfmt is defined, '-#&nbsp;&euro;)' 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="#&nbsp;!" negamountfmt="-#&nbsp;!" percfmt="#&nbsp;!"

This quick implementation of my urgent needs can be found as an attachment of this topic (http://www.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:

  • Test
  • Test 2

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 confused

-- 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? wink

-- 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:

Name email
Me1 me@onePLEASENOSPAM.spam
Me2 me@twoPLEASENOSPAM.spam
Whole List Click me to mail everyone.

and another example that fails:

Name email
Me1 me@onePLEASENOSPAM.spam
Me2 me@twoPLEASENOSPAM.spam
You3 you@threePLEASENOSPAM.spam
Them4 them@fourPLEASENOSPAM.spam
Us5 us@fivePLEASENOSPAM.spam
We6 we@sixPLEASENOSPAM.spam
They7 they@sevenPLEASENOSPAM.spam
Whole List Click me to mail everyone.

I'm not sure if the bug actually lies within SpreadSheetPlugin or somewhere else, but until we installed the 18 Oct 2004 version in my (ancient version of) TWiki, we didn't even have the LIST() function that is crucial for this approach to work.

Any ideas? Thanks Muchly!

-- FrankHorowitz - 28 Oct 2004

TWiki automatically links e-mail addresses. You need to escape that. I fixed your example from "$LISTMAP(, $LIST(...))" to "$LIST(...))".

-- PeterThoeny - 30 Oct 2004

Thanks for that suggestion, Peter! Just FYI, in addition to the present TWiki version (obviously) your fix works on the 01 Feb 2003 TWiki version, but fails on the 01 Dec 2001 version.

-- FrankHorowitz - 02 Nov 2004

I'm using the spreadsheet plugin and I need to use the logarithm function. Is that possible? There is no LOG() function and EVAL() is very limited. The plugin claims to have general formula evaluation capabilities, so I would really like to see some basic mathematical functions. Logarithms, roots, trigonometric for example.

Thanks for your great work!

-- TWikiGuest - (via e-mail) - 30 Oct 2004

Adding math and trig functions can be done without much effort. There was no need so far.

Question to Perl gurus: I am concerned about performance and do not want to slow down the Plugin. Is there an overhead at compile time if the code has cos, exp, log, sqrt, etc calls? That is, does Perl load extra modules behind the scenes for trig functions?

... Back after some tests: Calling a small Perl test program 1000 times with/without two trig calls ("not covered" calls) does not show any difference in execution time.

-- PeterThoeny - 30 Oct 2004

Is there a way to refer to rows and columns in a previous table? In PluginAppraisalStatistics I wanted to add a footer row that had averages of each appraisal entry. However, TWiki sees them as two tables due to the #GerryMander anchor.

(My question is somewhat academic now as we've realised that GerryMander anchor will always mean the table and its statistics get broken apart.)

-- MartinCleaver - 08 Nov 2004

You only can refer only to the table immediately above the CALC. Workaround: Do the CALC directly after a table, but instead of showing the result, store it in variables ($SET(var1, $SUM(...)) etc). Then you can $GET(var1) inside another table.

-- PeterThoeny - 09 Nov 2004

Bug: The CALCs are done in-line. If they refer to a row that hasn't been scanned yet, they will silently pull the value from the current row. The fix will be to cache the entire thing back, pull out the CALCs and calculate them last. They will have to be sorted though, otherwise nestec CALCs will break. I plan on working on this tonight, I'll update tomorrow once I have something together.

NOTE: This issue was first reported/researched by ScottSibert on TWikiIRC

-- BenWebb - 01 Dec 2004

I now have a version that will fix the above mentioned CALC issue. It works in my test cases, but I have not done enough testing to yet call it production. I am going to attach my updated version, please test and comment.

-- BenWebb - 02 Dec 2004

I didn't solve this the exact way I originally envisioned, see the code for details. The problem of fields referring to fields that have not yet been CALCulated still exists, I will dig into that but not tonight.

-- BenWebb - 02 Dec 2004

Ok this is now fixed. It will handle nested CALCs up to three levels (this number can be easily configured). I updated my attached version to include the final fixes. I did my best to stay with the style of the original script, though my comments might be too verbose.

Here also is a diff that will create the same file I attached:

--- SpreadSheetPlugin.pm.orig   Wed Dec  1 22:14:41 2004
+++ SpreadSheetPlugin.pm        Thu Dec  2 19:46:59 2004
@@ -39,7 +39,7 @@
 # =========================
 use vars qw(
         $web $topic $user $installWeb $VERSION $debug $skipInclude $dontSpaceRE
-        $renderingWeb @tableMatrix $cPos $rPos $escToken
+        $renderingWeb @tableMatrix $cPos $rPos $tableRows $tableLine $escToken
         %varStore @monArr @wdayArr %mon2num
     );

@@ -99,8 +99,10 @@
     }

     @tableMatrix = ();
+    @tableLine = (); # added by BenWebb for delayed table calculation
     $cPos = -1;
     $rPos = -1;
+    $tableRows = -1; #this allows getTableRange to access then entire table - BenWebb

     my $result = "";
     my $insidePRE = 0;
@@ -127,37 +129,79 @@
                 if( ! $insideTABLE ) {
                     $insideTABLE = 1;
                     @tableMatrix = ();  # reset table matrix
-                    $cPos = -1;
-                    $rPos = -1;
+                    @tableLine = (); # reset table lines
+                    $tableRows = -1;
                 }
                 $line = $_;
                 $line =~ s/^(\s*\|)(.*)\|\s*$/$2/o;
                 $before = $1;
                 @row  = split( /\|/o, $line, -1 );
                 push @tableMatrix, [ @row ];
-                $rPos++;
-                $line = "$before";
-                for( $cPos = 0; $cPos < @row; $cPos++ ) {
-                    $cell = $row[$cPos];
-                    $cell =~ s/%CALC\{(.*?)\}%/&doCalc($1)/geo;
-                    $line .= "$cell|";
-                }
-                s/.*/$line/o;
+                $tableRows++;
+                push(@tableLine, $before);
+                s/.*//o; # blank the line - we rebuild the table once it's completely read

             } else {
                 # outside | table |
                 if( $insideTABLE ) {
                     $insideTABLE = 0;
+                    # rebuild the table and add it back in
+                    doTable();
+                    $result .= join("\n", @tableLine) . "\n";
                 }
                 s/%CALC\{(.*?)\}%/&doCalc($1)/geo;
             }
         }
         $result .= "$_\n";
     }
+    if ($insideTABLE){
+        # this has to be here in case we ended with a table
+        doTable();
+        $result .= join("\n", @tableLine) . "\n";
+    }
     $_[0] = $result;
 }

 # =========================
+# added by BenWebb to facilitate calculation of the table after
+# the entire thing has been read.  The beginning of each line
+# is cached in @tableLine (there is a potential bug here in that
+# we don't currently catch the end of the line) and the rest
+# of the line is subsequently built from @tableMatrix
+# See also getTableRange for other changes that were added to make
+# this work
+sub doTable
+{
+    my $redocount = 3; # this is protection from infinite loops.
+    my $redo = 1;
+    my $tableLineEnd = ();
+
+    while ($redo && $redocount > 0){
+        $redo = 0;
+        $redocount--;
+        @tableLineEnd = ();
+        for ($rPos = 0; $rPos <= $#tableMatrix; $rPos++){
+            for ($cPos = 0; $cPos <=  $#{ $tableMatrix[$rPos] } ; $cPos++){
+                $cell = $tableMatrix[$rPos][$cPos];
+                my $cellcopy = $cell;
+                $cell =~ s/%CALC\{(.*?)\}%/&doCalc($1)/geo;
+                if ($cell =~ /CALC/ || $cell =~ /ERROR/){
+                    $redo = 1;
+                    $tableMatrix[$rPos][$cPos] = $cellcopy;
+                    # that looks redundant but doCalc clobbers the tableMatrix cell when it runs
+                } else {
+                    $tableLineEnd[$rPos] .= "$cell|";
+                }
+            }
+        }
+    }
+    for ($rPos = 0; $rPos <= $#tableMatrix; $rPos++){
+        $tableLine[$rPos] .= $tableLineEnd[$rPos];
+    }
+
+}
+
+# =========================
 sub doCalc
 {
     my( $theAttributes ) = @_;
@@ -979,9 +1023,12 @@
     if( $c1 < 0     ) { $c1 = 0; }
     if( $c2 < 0     ) { $c2 = 0; }
     if( $c2 < $c1   ) { $c = $c1; $c1 = $c2; $c2 = $c; }
-    if( $r1 > $rPos ) { $r1 = $rPos; }
-    if( $r1 < 0     ) { $r1 = 0; }
-    if( $r2 > $rPos ) { $r2 = $rPos; }
+    # these two lines were changed by BenWebb to allow an entire table to
+    # be calculated after it is read.  Rather than checking rPos the total
+    # number of table rows is now checked
+    if( $r1 > $tableRows ) { $r1 = $tableRows; }
+    if( $r2 > $tableRows ) { $r2 = $tableRows; }
+    ###
     if( $r2 < 0     ) { $r2 = 0; }
     if( $r2 < $r1   ) { $r = $r1; $r1 = $r2; $r2 = $r; }

-- BenWebb - 03 Dec 2004

Thank Ben, I will take this into the next release if it works also with border cases (e.g. table composed of included topics etc).

I would not call this a bug, it is documented in the syntax rules that "formulae can only reference cells in the current or preceeding row of the current table."

-- PeterThoeny - 04 Dec 2004

One thought came to me recently: a means to interoperate with Microsoft Excel would be awesome!

Ideally I'd like to be able to design my spreadsheet in Excel and then port it to TWiki.

Hmm. That said perhaps I just need to be able to embed an XmlSpreadsheet into TWiki. Curious...

-- MartinCleaver - 04 Dec 2004

Peter: Sorry I called it a bug, I missed that in the doc. I agree it needs additional testing before it hits production. I'm hoping some of the other users can help me out with that.

Martin: I like the idea of an import - I've been working that out in my head already. The question we have to ask is would you want to be able to import the formulas or just the data?

-- BenWebb - 05 Dec 2004

smile both, naturally! I don't think you can divorce the two because the whole point of a spreadsheet is that it blurs the boundary between the static and the derived.

That said, most formulas are simple - SUM() perhaps being the most frequent.

As long as the spreadsheet importer documents what it cannot handle, compatibility can be introduced in stages.

-- MartinCleaver - 05 Dec 2004

For Support.DiffsVariable it would be useful to have a new $WHILE(condition, action) formula.

-- PeterThoeny - 23 Dec 2004

I just wanted to mention, since I have sort of disappeared for the last month or so, that yes, I'm still working on this. Holidays with the family and all that but yes, I'm still working on it.

-- BenWebb - 27 Jan 2005

  I found a bug (or lacking feature) in the WORKINGDAYS function. It does not handle cases where start > end properly. Here is my modified _workingDays function:

sub _workingDays
{
    my ( $start, $end ) = @_;

    # Contributed by CrawfordCurrie - 17 Jul 2004
    # Calculate working days between two times. Times are standard system times (secs since 1970).
    # Working days are Monday through Friday (sorry, Israel!)

    use integer;
    if ( $end >= $start ) {
      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 = gmtime( $start );
        my $wday = $lt[6]; # weekday, 0 is sunday
        $extra_days-- if( $extra_days > ( 6 - $wday ) );
        $extra_days-- if( $extra_days > ( 6 - $wday ) );
      }
      return $whole_weeks * 5 + $extra_days;
    } else {
      my $elapsed_days = ( $start - $end ) / ( 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 = gmtime( $end );
        my $wday = $lt[6]; # weekday, 0 is sunday
        $extra_days-- if( $extra_days > ( 6 - $wday ) );
        $extra_days-- if( $extra_days > ( 6 - $wday ) );
      }
      return -1 * ( $whole_weeks * 5 + $extra_days );
    }
}

-- DanielHeater - 01 Feb 2005

Hi, spotted an incompatibility between Spreadsheet and Edit Table plugins. The EDITCELL size parameter is used in the PRODUCT function as a number.

Quick illustration at SpreadSheetAndEditTableEditCellBug

-- DanielOsborne - 08 Feb 2005

Hello I took a look at the Time functions in the PLugin, which are great, nevertheless I added a function to the Plugin which does the next:

It adds a specific unit of time (only working time) to a serialized Date taking into account the working days. For instance if there are weekends in between it would calculate the date so that weekends are also taken into account. This is quite helpful for project management. The code is the next, please consider it for a future version of the Plugin thanks:


    } elsif( $theFunc eq "TIMEADDWORKINGDAYS" ) {
       my( $time, $value, $scale ) = split( /,\s*/, $theAttr, 3 );
       $time = 0 unless( $time );
       $value = 0 unless( $value );
       $scale = "" unless( $scale );
       $time =~ s/.*?([0-9]+).*/$1/o || 0;
       $value =~ s/.*?(\-?[0-9\.]+).*/$1/o || 0;
       $value2 = $value;
       $value *= 60            if( $scale =~ /^min/i );
       $value *= 3600          if( $scale =~ /^hou/i );
       $value *= 3600*24       if( $scale =~ /^day/i );
       $value *= 3600*24*7     if( $scale =~ /^week/i );
       $value *= 3600*24*30.42 if( $scale =~ /^mon/i );  # FIXME: exact calc
       $value *= 3600*24*365   if( $scale =~ /^year/i ); # FIXME: exact calc
       $value2 = $value;
       $value = int($time + $value);
       $workd = _workingDays( _getNumber( $time ), _getNumber( $value ) );
       while( ((($value2/3600)/24) - $workd ) > 0 ) {
                $value += ($value2)-($workd*3600*24);
                $workd = _workingDays( _getNumber( $time ), _getNumber( $value ) );
            }
       $result = int( $value );

-- GustavoAdolfoLopez - 15 Feb 2005

Hi,

the function $PROPERSPACE can separate better the wiki words when it have acronym or abbreviation in the middle.

Example: my WikiName is AurelioAHeckert, but it translates to Aurelio AHeckert... the best way is Aurelio A Heckert. (The realy right is Aurelio A. Heckert but RegExp is not magic)

I did a test and god result hire: http://twiki.im.ufba.br/bin/view/Test/RegExpSepararWikiNames
(with tests)

I did in javascript. I think it's good. But, we need more I18N. Somebody can translate it to Perl?

-- AurelioAHeckert - 18 Feb 2005

This may be a general question of nested variable expansion, but I haven't found an answer and I'm posting it here because I happen to need it in the context of CALC. I would like to use the SpreadSheetPlugin to calculate new values for form elements. In order to do this, I need to retrieve the current value, which can be done with URLPARAM. Unfortunately, I cannot seem to find a way to embed the URLPARAM expansion into a CALC. For example, suppose I want a button on a calendar page that will go to the month previous to that shown. What I want is a formula like this:

%CALC{"$IF(%URLPARAM{"month"}% == 1,12,$EVAL(%URLPARAM{"month"}%-1))"}%
So that I end up with, if month currently equals 2:
%CALC{"$IF(2 == 1,12,$EVAL(2-1))"}%
Yielding a result of 1.

However, what I get is: ERROR: syntax error, near "=="

I'm confident TWiki has a way to accomplish this. Can someone enlighten me?

-- DavidBright - 18 Feb 2005

I've also posted this at NestedVariableExpansion, since it really is a support question.

-- DavidBright - 18 Feb 2005

After a hint from CrawfordCurrie and working on it some more, I hit upon the answer. See NestedVariableExpansion for details.

-- DavidBright - 21 Feb 2005

Great plugin, but I'm running into some problems with negative numbers... I'm performing a difference on a table column, so my code looks like this:

%CALC{$EVAL($T(R5:C2)-$T(R6:C2))}% The problem is if the 2nd number is negative it will not work

10 - -20 should equal +30, but it really just equals

ERROR: Can't modify constant item in postdecrement (--), near "10--"syntax error, near "--20"

Here is a link to test 10 + -20, and another to test 10 - -20

-- KenMankoff - 23 Mar 2005

The problem is that the SpreadSheetPlugin code squashes everything together, so the space separating the subtraction operator and the unary negation operator disappears and it then looks like the "--" decrement operator. If you put parentheses around the number that could be negative, it would eliminate the problem:

%CALC{"$EVAL(10-(-20))"}%
would then yield: 30, just as you would expect. I would consider that just a workaround, though; the plugin expression evaluation code really should be changed (IMHO) to not get rid of the internal spaces.

  • I removed spaces by design to avoid side-effects elsewhere. For now we have to live with this workaround -- PeterThoeny - 25 Mar 2005

-- DavidBright - 24 Mar 2005

I have attached two patches that I would request PeterThoeny consider adding to the standard distribution.

The first, http://www.twiki.org/p/pub/Plugins/SpreadSheetPluginDevArchive/AboveOffsetPatch.txt, adds an offset parameter to ABOVE(). If the offset is not specified, or is zero, the function behaves as today. If a negative offset is supplied, then the range produced by ABOVE(-x) will include all rows above the current row except for the first x rows. If a positive offset is supplied, then the range produced by ABOVE(x) will include the x number of rows preceding the current row. The negative offset handles the (in my use, anyway) common case of excluding header rows in calculations. The positive offset is one that I have had occasion to use, but admittedly much less than the first (negative) case. Of course, both could be considered syntactic sugar, but I think ABOVE(-1) is a whole lot easier to read than R1:C$COLUMN()..R$ROW(-1):C$COLUMN().

The second, http://www.twiki.org/p/pub/Plugins/SpreadSheetPluginDevArchive/ListCountItemsPatch.txt, adds a function LISTCOUNTITEMS() that functions identically to COUNTITEMS() except that it returns the result as a list instead of a string of <br /> separated item : count pairs. As a list, the items can be operated upon by the other LIST* functions. For example, I needed to take the sum of ( each item count divided by 2, rounded up). As it was, I couldn't think of a way to do that with the result of COUNTITEMS(); there seemed to be no way to turn it into a list. With LISTCOUNTITEMS(), I could do this:

%CALC{"$SUM($LISTMAP($INT(($item + 1)/2), $LISTIF($EVEN($index), $LISTCOUNTITEMS(ABOVE(-1)))))"}%

For the curious, I needed to figure out how many containers were needed to hold items from several groups if each container held two items but no item was to be in a container with an item from another group. The number of groups and their identity was not fixed, so brute-force methods such as a COUNTSTR() for each group identity would not work.

I also thought of modifying COUNTITEMS() to somehow specify that the separator(s) should be a comma rather than colon and <br />, but since the last parameter is a list I could not come up with a way of extending the syntax that would be backward compatible.

-- DavidBright - 24 Mar 2005

I am implementing some date formatting on the CalendarPlugin and figured that it would be a good idea to have the various substitution strings be the same as in the SpreadSheetPlugin FORMATTIME() function. There are a few additional formatting options that I think would be nice for both these plugins:

  • A day number without a leading zero. Perhaps use $dy?
  • A (full) month name. Perhaps use $monthname?
  • An hour designator for a 12-hour clock. Perhaps use $hr? I would make this formatted without a leading zero. I realize that most of the world uses the 24-hour clock, but I despair of convincing my users to give up their long-accustomed 12-hour am/pm time keeping.
  • An am/pm designator (for use with above). Perhaps use $p?
  • As 12:00 is ambiguous in a 12-hour clock and not everyone understands what is meant by 12:00 pm (noon) and 12:00 am (midnight), it might be useful to be able to choose whether to have $p simply use am/pm or midnight/am/noon/pm. That would be simple enough for me to do in CalendarPlugin, but I'm not sure how that could be handled in SpreadSheetPlugin. Allowing the format to be settable could also take care of the many variants that people like to use (am/pm, AM/PM, a/p, a.m./p.m., etc.).
PeterThoeny (and everyone else, for that matter), what do you think of these ideas?

-- DavidBright - 24 Mar 2005

These are sensible enhancements to FORMATTIME(). Suggestions:

  • $12hour for 12-hour clock
  • $ampm for am/pm designator
  • I would not worry about the midnight/am/noon/pm ambiguity. At some point, the TWiki core should introduce time locale handling; the Plugin then could define a $shortdate and $longdate that gets expanded according to the locale setting.

-- PeterThoeny - 25 Mar 2005

Hi!

DavidBright and FranzJosefSilli sed to me to converge with the others Data and Time formating models becose the DateTimePlugin is a new variant to this, and it's no god...

So, I propose we set the default names in the topic ConvergeDateTimeFormattingModels and new ideas will have a place to be showed. (and will be easer to know wath is the default)

-- AurelioAHeckert - 31 Mar 2005

We have a table that is frequently edited. In the last column of each row there ist the sum of words in all cells of the row. This has to be updated on each edit which is often forgotten. So I used this plugin to automate the calculation. My formula looks like this: %CALC{"$EVAL($SUBSTITUTE($LISTJOIN($sp, $LIST(R$ROW(0):C2..R$ROW(0):C$COLUMN(-1))),[0-9][0-9]*,1+,,r) 0)"}%

When this is inserted into the table it becomes very unreadable in edit mode because the lines are too long for the edit area now. So I wanted to put the formula into a variable and use the variable inside the table but I could not find a way to to this. Can this be done somehow? Is there a way to define a user function within the topic?

i'd like to have something like:
Set MYFORMULA = %CALC(....)%

and then use %EVAL(MYFORMULA)% in the table.

-- UlrichSibiller - 07 Apr 2005

How about using the "SET" and the "GET" instructions which also are available in the SpreadSheetPlugin ?

%CALC{"$SET(MYFORMULA,Your formula)"}%

%EVAL($GET(MYFORMULA))%

-- JanCarstenJ - 07 Apr 2005

This should work. Set a variable in the topic, could be hidden in HTML comments:

  • Set MYFORMULA = $EVAL($SUBSTITUTE(...etc...))
And use this in the table cell: %CALC{%MYFORMULA%}%

-- PeterThoeny - 08 Apr 2005

Proposed new function:

SUMITEMS( list, list ) -- Sum of corresponding cell in list 2 for each distinct value in list 1

  • Syntax: $SUMITEMS( list )
  • Example: %CALC{"$SUMITEMS(R2:C1..R$ROW(-1):C1, R2:C2..R$ROW(-1):C2)"}% returns UserA: 12, UserB: 6 assuming two cells in Column 1 contains UserA and cell on same row in Column 2 contains values 5 and 7, and one cell in Column 1 contains UserB and cell on same row in Column 2 contains value 6
  • Related: $COUNTITEMS()

Couldn't find any other way to do it so fixed it locally. My fix is attached as patch for adding function SUMITEMS if it is found useful. I use it for a range of reports where the data comes through searches on tables scattered in the web, using the new functionality for including matching lines from the found topics and then summarizing it all in the report.

Making functions is pretty simple and one starts wishing for the ability to do dynamic loading of plugin functions from lib/TWiki/Plugins/SpreadSheetPlugin/myfunction.pm

DavidBright comments on the LISTCOUNTITEMS leads me to consider if the SUMITEMS really should return a list instead of a formatted string. Leaving it for now, but probably a more flexible solution

-- OleCMeldahl - 03 May 2005

New formulae EXEC and NOEXEC

  • NOEXEC: Do not execute the formula. Useful to SET a variable with content for later execution.
  • EXEC: Executes its content, e.g. evaluates the formula. Useful to GET a variable and execute its content, typically containing other variables

Change:

--- bu1/SpreadSheetPlugin.pm    2005-03-23 02:25:56.000000000 -0800
+++ SpreadSheetPlugin.pm        2005-06-15 17:32:01.000000000 -0700
@@ -201,7 +201,7 @@
     $theAttr = "" unless( defined $theAttr );
     &TWiki::Func::writeDebug( "- SpreadSheetPlugin::doFunc: $theFunc( $theAttr ) start" ) if $debug;

-    unless( $theFunc =~ /^(IF|LISTIF|LISTMAP)$/ ) {
+    unless( $theFunc =~ /^(IF|LISTIF|LISTMAP|NOEXEC)$/ ) {
         # Handle functions recursively
         $theAttr =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
         # Clean up unbalanced mess
@@ -215,6 +215,18 @@
     if( $theFunc eq "MAIN" ) {
         $result = $theAttr;

+    } elsif( $theFunc eq "EXEC" ) {
+        # add nesting level escapes
+        my $level = 0;
+        $result = $theAttr;
+        $result =~ s/([\(\)])/addNestingLevel($1, \$level)/geo;
+        # execute functions in attribute recursively and clean up unbalanced parenthesis
+        $result =~ s/\$([A-Z]+)$escToken([0-9]+)\((.*?)$escToken\2\)/&doFunc($1,$3)/geo;
+        $result =~ s/$escToken\-*[0-9]+([\(\)])/$1/go;
+
+    } elsif( $theFunc eq "NOEXEC" ) {
+        $result = $theAttr;
+
     } elsif( $theFunc eq "T" ) {
         $result = "";
         my @arr = getTableRange( "$theAttr..$theAttr" );

-- PeterThoeny - 16 Jun 2005

Could FORMATTIME() support $ww for the current work week? I seem to need this often, and it is getting painful to do:

%CALC{"$INT(1 + ($FORMATTIME($TIME(1 Jan 2005), $wd) - 1 + $FORMATTIME($TODAY, $yearday)) / 7)"}%
instead of just
%CALC{"$FORMATTIME($TODAY, $ww)"}%

Also, is there a way to generate a list from a numeric range? That is, what I'm looking for is something equivalent to:

%CALC{"$LIST(1..26)"}%

-- PankajPant - 24 Jun 2005

Example for $NOP might be nice: $NOP($perSEARCH{"%URLPARAM{search}%" nototal="on"}$per)

Are there other functions/variables like $per? Would be nice to have them listed too.

-- KarenAiken - 04 Jul 2005

Can someone look for the differences of the TWiki.org version of the SpreadSheetPlugin versus the svn in the DEVELOP branch, please. Are there any objections to just checkin the current TWiki.org version?

-- MichaelDaum - 04 Jul 2005

The latest version of the Plugin is installed on TWiki.org and attached to the Plugin topic. The TWiki.org server move is not complete at this time, so my setup is not yet ready to put that into svn.

-- PeterThoeny - 04 Jul 2005

I merged the latest changes to SVN, so the SpreadSheetPlugin there is up-to-date. It also has a build script now, so you can generate a new release from the root of a svn checkout area as follows:

cd twikiplugins/SpreadSheetPlugin/lib/TWiki/Plugins/SpreadSheetPlugin
perl build.pl upload
(the target upload uploads the zip to twiki.org. You can also use release to create a zip locally)

-- CrawfordCurrie - 17 Aug 2005

Bug: if you put

%CALC{$INT(%SERVERTIME{"$mo"}%+1)}%
You get:

10

(reported for Scott Lund, Wind River)

  • Looks properly here, what is/was the problem? -- PeterThoeny

-- CrawfordCurrie - 17 Aug 2005

Peter, the $SET and $GET functions work outside of the context of a table but $GET can only retrieve a value if the $GET occurs after the defining $SET. Many times I have wanted to have a topic of many tables, each with total variables set by $SET -- but I have wanted an overall summary table at the top of the topic that included summary stats from the lower tables. This isn't doable in the current context of SpreadSheetPlugin unless one introduces some concept of "late execution" within the plugin.

Or have I missed something completely counter-intuitive that would allow this behavior?

Thanks!

  • No, with the current implementation there is no way to get the values before they are set. Workaround: Create several topics and do selective include & calc. -- PeterThoeny

-- SteveRJones - 18 Jan 2006

we are using the BugzillaQueryPlugin to display bug entries and their timetracking information in twiki. now i would like to sum up all estimates of bugs which i've queried with:

%TABLE{ sort="on" initsort="5" initdirection="down" tableborder="0" cellpadding="0" cellspacing="2" headerbg="#FFCF00" headercolor="#000000" datacolor="#000000"}% 
| *bug* |  *version* | *status* | *estimate* | *assigned to* | *description* |
%BGQ{data="on" product="XXX" target_milestone="20.02.-03.03.06" short_desc="N:, B:, C:" format="| $bug_id | $version | $bug_status | $estimated_time | $assigned_to | $short_desc |" }% 

theirfore i added a new line with

| TOTAL: ||| %CALC{"$SUM( R2:C4..R$ROW(-1):C4 )"}% |||

this is not working, i always get "Total: 0".

 
%CALC{"$ROW(0)"}% 
this always returns 1.

the ChartPlugin is working quite well with BugzillaQueryPlugin, so i think that the above mentioned bug does not come from BugzillaQueryPlugin.

-- FrankEgger - 17 Mar 2006

This should not happen with SKIPINCLUDE set to 1. Also, your Plugin needs to execute before CALC; check the sequence in configure (for TWiki 4), and TWikiPreferences (earlier versions).

-- PeterThoeny - 18 Mar 2006

thanks a lot, now it works!

-- FrankEgger - 20 Mar 2006

I'm trying to create a second table which just holds summary info from the table above it. I'll be using this second table for charting but the initial table will always be dynamic (using EditTablePlugin) and I can't have a summary row at the bottom because of this. Here's a visual example of what I'm trying to do:

Year Estimate 1 2 3 4
1998 1 2 3 4 5
1999 2 3 4 5 6
2000 3 4 5 6 7
2001 4 5 6 7 8
2002 5 6 7 8 9

  1 2 3 4
Total 0 0 0 0

I'm trying to get this second table populated with the sum of the matching rows above. Any suggestions? I could easily drop the EditTablePlugin and just have a summary row and all of my problems would be solved. But my users really like to be able to edit the table easily. Thanks.

-- BrianBeaudet - 30 Mar 2006

Brian, your question has been asked several times in Support. See for example EditTableWithSpreadsheetCalculations.

-- FranzJosefSilli - 30 Mar 2006

Is there an easy way to do setIfUndefined(variable, value), or equivalently getValueOrDefault(variable, default)?

I pass a lot of information to TWiki by setting SpreadSheetPlugin variables before including the application page, and as a result I find myself doing this a lot:

$SET(var, $IF($EXACT($GET(var),), default, $GET(var)))

I would like to be able to do:

$SET(var, $GET(var, default)) or
$SETIF0(var, default)

-- PankajPant - 11 Apr 2006

This looks like a useful enhancement. If you want you could you provide a patch. I'd go for a descriptive $SETIFEMPTY(var, value)

-- PeterThoeny - 11 Apr 2006

Is this plugin TWiki4 compliant? The TwikiTestedOn would be nice if it specified Twiki Versions rather than dates.

-- EricHanson - 12 May 2006

This Plugin is pre-installed in TWiki 4. It uses only the official functions, so any Plugin version should run on any TWiki version. The zip attached to SpreadSheetPlugin is not identical to the one shipped in TWiki 4, however there are no functional differences.

-- PeterThoeny - 12 May 2006

SopanShewale posted a fix for the CALC expand issue when editing a table using EditTablePlugin (see post in EditTablePluginDev on 11 Jul 2006.)

-- PeterThoeny - 11 Jul 2006

In the TWiki 4.0.4-2 in the SpreadSheetPlugin there is a bug in TIMEDIFF. In the lines (1193 & 1194) the code is:

       $time1 =~ s/.*?([0-9]+).*/$1/o || 0;        $time2 =~ s/.*?([0-9]+).*/$1/o || 0; 
Unfortunately if either time is negative, which will be the case if the time occurs before 1970, the code strips the minus sign off resulting in an incorrect calculation. The fix is to change the lines to:
       $time1 =~ s/.*?([0-9]+-).*/$1/o || 0;        $time2 =~ s/.*?([0-9]+-).*/$1/o || 0; 

So, as an example,

%CALC{"$INT($TIMEDIFF($TIME(1971/04/2), $TODAY(), year))"}% 
should show 45 but actually shows 46

It appears that this same problem also exists for TIMEADD, FORMATTIME, and FORMATGMTIME (I think that is all)

Here

%CALC{"$FORMATTIME($TIME(1971/4/2), $year/$month/$day)"}% 
results in 1971/04/02 which is obviously wrong.

-- TaitCyrus - 28 Jul 2006

Did you test this change? It looks like you require a trailing minus sign. Shouldn't it be like this:

$time1 =~ s/.*?(\-?[0-9]+).*/$1/o || 0;

-- PeterThoeny - 29 Jul 2006

Opps. You are right. I did test this, and I see that I mistook the parens for square brackets. It worked for me since the regex didn't match so it left the string untouched which resulted in it working. Sorry about that (my eyes must be tired than I realized).

-- TaitCyrus - 29 Jul 2006

In the TIMEDIFF function the computation of day and week (as well as month and year) seem to not be what most people would think of. Maybe I'm wrong, but I believe that most people would say that the computation of day should be how many midnights occur between the two specified dates. Wouldn't most people say, for example, that the number of days between 2006/7/1 23:00 and 2006/7/2 01:00 is one even though they are only two hours different? In general, when computing how many days have passed, the number of 24 hour periods is not what most people would use but rather the number of midnights. And this doesn't even begin to take into account daylight savings where a day might be 23 hours long or 25 hours long.

The same can be said for how many weeks difference there is between two dates, again defining a week as how many Mondays have occurred between the two dates. The same with months which is the number of firsts have passed and years which is the number of January 1sts have passed.

I'd be happy to work up what I believe to be a more accurate coding of TIMEDIFF.

-- TaitCyrus - 29 Jul 2006

Well, if people want, I'm attaching an updated version of the Calc.pm file that fixes the time "problems" I mentioned above. Specifically the changes are:

  • Update the TIMEDIFF code to better deal with calculating the number of minutes, hours, days, weeks, months, and years between two dates. This update uses the web servers local time as a reference point when figuring out when a day starts. The TIMEDIFF update defines:
    • min: the number of minutes between two times where a minute begins at 00 seconds (HH:MM:00)
    • hou: the number of hours between two times where an hour begins at 00 minutes (HH:00:00). Correctly deals with two times on either side of a daylight saving time.
    • day: the number of days between two times where a day begins at midnight (localtime).
    • week: the number of weeks between two times where a week begins on Monday (the code can easily be changed such that the definition of when the week begins can be defined in the plugins topic configuration page).
    • mon: the number of months between two times where a month begins on the first of the month. Works correctly for a February with 29 days.
    • year: the number of years between two times where a year begins on January 1st.
  • Update the TIMEADD code in a similar fashion to the changes done in TIMEDIFF making sure correctly deals with both daylight saving time crossings as well as leap years. When adding months, the new code assumes that if you start with Jan 31 and add one month you end up with Feb 28. In other words, the day of the month is retained during the addition with the exception that the new month doesn't contain that day in which case the last day of the new month is used.
  • Replace the custom TWiki string date/time parsing with the CPAN Time::ParseDate::parsedate routine since it is more complete. This required adding new dependencies of: Time::ParseDate, POSIX::strftime, and Time::DaysInMonth. Also removed the dependency to Time::Local which wasn't needed.
  • Fix the time code (TIME, TIMEDIFF, TIMEADD, FORMATTIME, & FORMATGMTIME) to work with times accuring before 1970/1/1.

Note: I ran 280 different test cases through both TIMEDIFF and TIMEADD testing various boundry conditions (daylight saving time crossings and leap year crossings) in both directions (earlier date followed by a later date as well as a later date followed by an earlier date in the case of TIMEDIFF and both positive and negative addition values in the case of TIMEADD) so the code should work much better than before. Since the code uses localtime it should even work in the Iran timezone (though I didn't test this specifically).

-- TaitCyrus - 1 Aug 2006

Thanks Tait for contributing these enhancements. They are good by the spec. I am reluctant though to introduce new dependencies on Time::ParseDate, POSIX::strftime, and Time::DaysInMonth. Are those modules pure Perl, or do they need to be compiled?

-- PeterThoeny - 03 Aug 2006

  • POSIX::strftime needs to be compiled (contains C code to access the strftime C library). It is odd that a normal POSIX C function isn't made available via the normal Perl POSIX package since one would think that the POSIX package would provide access to all of the POSIX functions.
  • Time::ParseDate is pure Perl.
  • Time::DaysInMonth is pure Perl.

Peter, what would you suggest as being a good course of action regarding this? It would be nice if there was a way to add enhancements to existing plugins without having to totally replace them like I did by attaching a complete copy up the updated CALC.pm file. Maintaining a patch might be problematic as one would have to have a patch for each version of TWiki shipped. A new plugin could be written, but that just doesn't seem a good solution in the long run because that could/would result in lots and lots of very small custom plugins, unless the new plugin was specifically to deal with time related functions. I guess another solution would be to keep the existing TIMEDIFF and TIMEADD (for backward compatibility) and add two new functions and each of these functions would at run time include the needed dependencies. If the dependencies didn't exist those functions would return an error. Part of me says that it might be best to just create a new plugin that deals with time related functions. Thoughts?

-- TaitCyrus - 04 Aug 2006

I was wondering if this comment that was made earlier can be added to the official plugin documentation, perhaps with the word "macro" added, as this certainly has changed the way I use this plugin:

This should work. Set a variable in the topic, could be hidden in HTML comments:
* Set MYFORMULA = $EVAL($SUBSTITUTE(...etc...))
And use this in the table cell: %CALC{%MYFORMULA%}%

  • This is now in the FAQ section of the plugin topic. -- PTh - 10 Oct 2006

-- PeterPayne - 04 Aug 2006

Peter Payne: Yes, it makes sense to add this to the doc.

Tait: This Plugin is pre-installed in the TWiki package, we cannot add a dependency for compiled code. Any way to remove this dependency and replace with pure Perl?

It is better to keep one codebase. A possible solution is to do a lazy loading, e.g. check at the time TIMEDIFF / TIMEADD is called if the dependecy modules exist and load, and fall back to the current code if not.

-- PeterThoeny - 05 Aug 2006

OK. To make my changes more efficient, I rewrote the changes removing all calls to POSIX::strftime. My earlier statement about POSIX::strftime needing to be compiled was in error. It is really POSIX::str p time that requires itself to be compiled and not POSIX::str f time. So there were and are no compiled dependencies. I have uploaded a new copy of CALC.pm.

Note: I added two new functions called STRFTIME and STRFTIMEGMT. They are similar to FORMATTIME and FORMATGMTIME respectively. The new functions allow users a bit more control over how time/dates are formated by allowing them access to the POSIX strftime function (including the ability to display timezone information).

Usage:

Today in local time is: func STRFTIME not found.  Today in UTC time is: func STRFTIMEGMT not found.  

NOTE: since my code (and the original) all use localtime, the earliest date that can be manipulated is 1900. Oddly it appears that by using Time::ParseDate the earliest date that can be used is December 13th, 1901.

-- TaitCyrus - 05 Aug 2006

I am not aware of anyone asking for 19th century dates, so that limitation should be fine.

I am not clear how we should package TWiki when we make this pre-installed Plugin dependent on Time:ParseDate. That module is already part of CpanContrib, but it does not help since the SpreadSheetPlugin is pre-installed. We probably need to add Time:ParseDate to twiki/lib/CPAN?

-- PeterThoeny - 07 Aug 2006

Hmm. Good point. There are several options:

  1. I could code things so if Time::ParseDate is available it is used, else the original date parsing code is used.
  2. Or, add Time::ParseDate to twiki/lib/CPAN, though I should mention that Time::ParseDate has a couple of its own prerequisites of: Time::Timezone and Time::JulianDay.
  3. Or leave this plugin alone and create a new plugin that contains a full range of date/time functions that could replace the SpreadSheetPlugin functions. Being outside of the core TWiki gives it more freedom to be dependent on other CPAN modules.

The first of these isn't really an option since it would change the behavior depending on what was installed. Having different behavior isn't a good thing (especially of a server gets upgraded and suddenly Time::ParseDate disappears and now you are wondering why TWiki isn't giving you the same results as before).

The 2nd is OK but can add bloat to the core TWiki. If I had to make a recommendation, I would recommend #3. This way admins would know what the prerequisites are before installing the new plugin and would have control over the installation of all prerequisites. This new plugin could be coded to be smart such that if an required prerequisite isn't available (say POSIX::strptime), then an error is returned just for that function and not any of the other functions.

A problem with the 3rd option is it would provide duplicate functionality with the SpreadSheetPlugin and that might confuse people. For example they use CALC{$TIMEDIFF} when they really wanted to use NEWPLUGIN{$TIMEDIFF} and since it mostly works they are not aware of their error. Part of me wants to suggest a way to have a new plugin replace (overwrite) certain functions in existing plugins (i.e. a new plugin replaces how CALC{$TIMEDIFF} is evaluated), but then you are back to the negative about #1 above (changes in behavior).

Thoughts?

-- TaitCyrus - 08 Aug 2006

There is a fourth option; code it so that the dependency is not evaluated unless the time functions are used. if they are, and Time::ParseDate is not installed, then complain at that time.

Personally I think option 2 is the best idea. There are places in the core where Time::ParseDate would be a real benefit, and other plugins already use it, or would like to use it. I think options 1, 3 and 4 would just create FUD.

Note that my initial reaction to 2 was "no!", because I recall having had bad experiences in the past installing Time::ParseDate (ActionTrackerPlugin uses it heavily). However I think all those problems have been fixed.

Note that if TWiki installation used an installer script (like most plugins do these days) then this would be a non-issue; the dependency would be resolved when the installer was run, without having to pre-install in lib/CPAN.

-- CrawfordCurrie - 16 Aug 2006

I also think option 2 is a good one, provided that all dependencies are pure Perl.

Option 1 is OK to; loading of the module should be done at the time it is needed (and fall back if needed.)

Option 4 is out of question because it breaks existing functionality, not a good thing for a pre-installed plugin.

-- PeterThoeny - 16 Aug 2006

I posted a new version with just doc enhancements. Tait's contributions are pending.

-- PeterThoeny - 10 Oct 2006

It appears that the SpreadSheetPlugin does not work when the arguments break over lines, see for example

%CALC{"$IF($EXACT(%SCRIPTURL{"view"}%, ) , emtpy, %WEBLIST%)"}%

which does not expand:

%CALC{"$IF($EXACT(http://www.twiki.org/cgi-bin/view, ) , emtpy, Blog Codev Main Plugins Sandbox Support TWiki TWiki01 TWiki02 TWiki03 TWiki04 TWiki04x01 TWiki04x02 TWiki04x03 TWiki05x00 TWiki05x01 TWiki06x00 WikiWed)"}%

This should be documented in the "syntax rules".

Even better would be if expansion in IF were delayed until after condition evaluation. But that still keeps the problem of needing the condition to be a single line.

-- ThomasWeigert - 29 Oct 2006

Currently, the whole %CALC{}% needs to be on one line. This may not be the case if the formula contains internal variables, since they get evaluated before CALC.

In your case, a workaround is to delay the expansion of %WEBLIST% with $NOP(%)WEBLIST%.

-- PeterThoeny - 29 Oct 2006

Thanks. It would be good if the delay where to be consistent with other areas, where we are using $percnt, $dollar, or $nop. (See for example, your very own EditTablePlugin.)

Further, I suggest you put an example of this little trick on the docu:

%CALC{"$IF($EXACT(%SCRIPTURL{"view"}%, ) , emtpy, $NOP(%)WEBLIST%)"}%

yields

Blog Codev Main Plugins Sandbox Support TWiki TWiki01 TWiki02 TWiki03 TWiki04 TWiki04x01 TWiki04x02 TWiki04x03 TWiki05x00 TWiki05x01 TWiki06x00 WikiWed

-- ThomasWeigert - 29 Oct 2006

There seems to be a bug in LISTMAP? If I execute $LISTMAP($index, 3, 5, 7, 9), I get "13, 25, 37, 49". In particular, using $index without also using $item appears to append $item to each entry, as if I had typed $index$item. Any idea?

-- AaronSher - 31 Oct 2006

Actually, this seems to be more general - any time that I use $LISTMAP without using $item in the results, I get this behavior. For example $LISTMAP(foo, 2, 4, 6) returns "foo2, foo4, foo6".

-- AaronSher - 31 Oct 2006

That is actually the expected behaviour, $item is appended to each item in case $item is missing. Relevant code in the map loop: $_ .= $item unless( s/\$item/$item/go );

What use case do you have where you do not need the actual item value? We could change the spec to append $item only of the string is empty (instead of if $item is missing.)

-- PeterThoeny - 31 Oct 2006

Actually, I don't think that I do have a case where this is a problem, directly. I was running into it when I tried to simplify my expressions in order to debug them, but knowing what the behavior is, I ought to be able to work around it. It would be really nice if that behavior were documented, though - it's certainly not obvious that it's going to happen. Thanks!

-- AaronSher - 31 Oct 2006

Yes, good point, at least the doc need to be updated.

  • This is now documented; new version is posted. -- PeterThoeny - 23 Jan 2007

-- PeterThoeny - 31 Oct 2006

Hello, I have posted a Bug and as HaraldJoerg checked, it is based by this Plugin. Please check Bugs:Item3188

-- ThomasFreudenberg - 25 Nov 2006

Hello All. The SpreadSheetPlugin topic says it has been tested on CairoRelease. However, I receive the following when I unzip the most recent PluginPackage (version 10 Oct 2006):

Can't locate TWiki/Plugins/SpreadSheetPlugin/Calc.pm in @INC (@INC contains: /{path}/{to}/twiki/lib . /usr/lib/perl5/5.8.6/i586-linux-thread-multi /usr/lib/perl5/5.8.6 /usr/lib/perl5/site_perl/5.8.6/i586-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.6/i586-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl) at /apache/htdocs/twiki/lib/TWiki/Plugins/SpreadSheetPlugin.pm line 83.

Any ideas ?

-- KeithHelfrich - 07 Dec 2006

Oops, that was a packaging error. Is fixed now.

-- PeterThoeny - 07 Dec 2006

Thanks, Peter. I was hoping that the latest version of the plugin would fix my problems when trying to build an AccountLedgerApp. But apparently the problems are with my spreadsheet commands.

-- KeithHelfrich - 11 Dec 2006

Hi Peter, do think, someone or you will take care of my listed Bugs:Item3188 - This error makes it hard to create working form application which generates automatically overview lists containing the summary.

Thanx for an answer, Tom

-- ThomasFreudenberg - 12 Dec 2006

I replied on the bug item topic.

-- PeterThoeny - 12 Dec 2006

I got an "Insecure Dependency" error when I was trying to configure the BlogPlugin. I have installed all the dependent Plugins and Perl modules. I have Twiki 4.05 and Perl 5.8.5. I am running this on Linux (Red Hat Enterprise Linux ES release 4 (Nahant)) with Apache server. I got the following error in the error logs of Apache as well as twiki log files "Insecure dependency in eval while running with -T switch at /var/www/twiki/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm line 918.". When I disabled the SpreadSheetPlugin I was able to create new Posting in the Blog but I was not able to reply to the message posted as the hidden field variable 'Nr' was not getting evaluated by the CALC procedure. I also disabled LocaleRegexes? in configure but no luck.

-- PramodKumar - 12 Dec 2006

It looks like the BlogPlugin is throwing some strange values at the formulas (one of $EVAL, $INT, $ROUND, $SETM, $IF condition, or $LISTIF condition). Time for some debugging. Could you add three debug statements to the safeEvalPerl function of lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm? The debug statements are the ones without indent:

sub safeEvalPerl
{
    my( $theText ) = @_;
TWiki::Func::writeDebug( "- safeEvalPerl( '$theText' ) -- before cleanup" );

    # Allow only simple math with operators - + * / % ( )
    $theText =~ s/\%\s*[^\-\+\*\/0-9\.\(\)]+//go; # defuse %hash but keep modulus
    # keep only numbers and operators (shh... don't tell anyone, we support comparison operators)
    $theText =~ s/[^\!\<\=\>\-\+\*\/\%0-9\.\(\)]*//go;
    $theText =~ /(.*)/;
    $theText = $1;  # untainted variable
    return "" unless( $theText );
    local $SIG{__DIE__} = sub { TWiki::Func::writeDebug($_[0]); warn $_[0] };
TWiki::Func::writeDebug( "  safeEvalPerl( '$theText' ) -- after cleanup" );
    my $result = eval $theText;
TWiki::Func::writeDebug( "  safeEvalPerl( '$theText' ) -- after eval" );
    if( $@ ) {
        $result = $@;

Then try to reproduce the error, and look at the output in data/debug.txt. Send me the output.

-- PeterThoeny - 13 Dec 2006

Added the debug statements and tried to open the page. The output of the debug.txt is pasted below

| 12 Dec 2006 - 21:35 |   safeEvalPerl( '1' ) -- after cleanup
| 12 Dec 2006 - 21:35 | Insecure dependency in eval while running with -T switch at /var/www/twiki/lib/TWiki/Plugins/SpreadSheetPlugin/Calc.pm line 919.

-- PramodKumar - 13 Dec 2006

Also reported in BlogPluginInternalError

-- MichaelDaum - 26 Dec 2006

After some investigation I am at loss how to fix this. The untainted $theText variable is passed to eval, and eval is complaining of a tainted string. Doea anybody have an idea what is going on?

The only thing that is a bit unusual is the untaint step, it usually has start and end anchors, but I do not see how this can make a difference:
$theText =~ /^(.*)$/;

-- PeterThoeny - 26 Dec 2006

Maybe try to disable locales or anything related on the relevant installation. It may be that the used perl versions has problems to deal with its strings properly, and this may be caused by an erroneous locale installation. This is only a guess, but at least once I was able to get away with this confusing tainted error this way.

-- MichaelDaum - 27 Dec 2006

Indeed, there seems to be a connection to the locale used, see BlogPluginDev#InsecureDependencyError

-- PeterThoeny - 14 Apr 2007

I've just found the most subtle problem (and it took me ages to see it). The Example for the $LISTRAND{} function has a typo, there are two R's. So if you're like me and you copy & paste from the example as a way to get started with your calculation ... then you spend a half an hour looking for the problem with CairoRelease before you figure out it's just an extra R in your function call roll eyes (sarcastic) Something to fix for the next build.. just to keep folks on track.

-- KeithHelfrich - 22 Jan 2007

Thanks Keith, new version is posted.

-- PeterThoeny - 23 Jan 2007

Are there any thoughts on how to SupportPivotTables ?

-- KeithHelfrich - 17 Feb 2007

Maybe I'm getting this wrong, but I'm trying to build a mileage calculator. The names of destinations are in mixed text, but these is a hidden variable corresponding to each so I have the formula:

%CALC{" %$UPPER($TRIM($T(R$ROW(0):C$COLUMN(-2))))% "}%

which reaches to the left & turns (for example) Ashford into ASHFORD & retrives the value of the variable - which works fine, but...

Although the value displays in the cell properly, I can't use it in any subsequent formulae, I get an empty string or an error if I refer to the cell containing the generated variable. I can use $SET to store the value & $GET to retrieve it for display, but it still won;'t work in another CALC statement.

Any clues at to why this is happens?

-- ChrisHogan - 21 Feb 2007

As you noticed, you can use $T() only on the current table. You should be able to assign the result to a variable, and to $GET() it later on (below the table.) This works also if you do further processing, such as $LOWER($GET(name)). SpreadSheetPlugin variables live during the whole page view, also across included topics. See SpreadSheetRollupTest

-- PeterThoeny - 22 Feb 2007

But my use of $T is 2 cells to the right - and using $SET does store the value & I can $GET it, but I can't use the result in a $EVAL - something like:

%CALC{$SET(STAINES, 3.70) $SET(ALLOWANCE, 0.40)}%
| <!-- Main.ChrisHogan --> | 28/12/2006 | Staines |  1|  %CALC{$SET(trip, %$UPPER($TRIM($T(R$ROW(0):C$COLUMN(-2))))%)$GET($GET(trip))}%|   %CALC{$EVAL($EVAL( 2 * $T(R$ROW(0):C$COLUMN(-2)) ) * $GET(ALLOWANCE) * $GET($GET(trip)) )}% |

which if you run it gives:

28/12/2006 Staines 1 3.70 2.96

-- ChrisHogan - 22 Feb 2007

I have got round the problem by taking the same approach as AccountLedgerApp & using a URLPARAM to feed the value via the CommentPlugin & "freeze" the value of my destiniation, but I'd still like to know why it didn't work the first way I tried.

-- ChrisHogan - 22 Feb 2007

I fixed your example. If you mix TWiki variables with SpreadSheetPlugin variables you run into evaluation order issues. Better to use just SpreadSheetPlugin variables. I fixed your example above. Note that you can add multiple formulae in one CALC, one after the next.

The $GET($GET(trip)) might look strange at first. Basically you get the name of the trip ( STAINES), then you get the value of the variable of that name.

-- PeterThoeny - 23 Feb 2007

Thanks Peter - I thought it must be something to do with the sequence, but the visual appearance of the TWiki variable & left me puzzled as to why it didn't then work in the formula. I did it in simple chunks, rather than a multiple because I was trying to work out where I was going wrong

-- ChrisHogan - 23 Feb 2007

See Bugs:Item3743

-- SergejZnamenskij - 09 Mar 2007

Bug fixed, also added additional functions.

-- PeterThoeny - 09 Mar 2007

This plugin does not seem to be able to parse numeric values with comma. e.g. a two-line table with a column with these 2 values: 177,374.00 and 95,940.00. If I try to sum them up like this CALC{"$FORMAT(DOLLAR, 2, $SUM( $ABOVE()))" I get $272.00. Is there a workaround, please? A fix, maybe? Note: if you prefix the numeric values with $, things also fail.

$
177,374.00
95,940.00
$272.00

$
$177,374.00
$95,940.00
$272.00

-- MarcioMarchini - 09 Mar 2007

$SUM() is not designed to accept commas, it does skip over a leading $ sign though. Could be useful to enhance $SUM() and other functions to ignore commas in location of multiples of three starting from the right.

-- PeterThoeny - 11 Mar 2007

Given the behavior of $LISTMAP(), I believe the way to generate a list of integers that index the rows of a table is this: $LISTMAP($EVAL($index +$CODE($item)-$CODE($item)), $LIST(R1:C1..R$ROW():C1) ). If someone has an easier way, please post it smile

-- TroyGoodson - 30 Mar 2007

This looks like a bug to me. In the following, I'm trying to count how many times "MEA" shows up in the table. Why doesn't it catch the last row? (I know I could use $COUNTSTR(); I'm not looking for a solution per se, I just want make sure this is a bug)

MEA
RCS
MEA
Number of "MEA"s: 2

row_list: 1, 2, 3 ; mea_list: 1 ; mea_count: 1 ; not_mea: RCS, MEA and here's the code:

|MEA|
|RCS| 
|MEA|
Number of "MEA"s: %CALC{"$LISTSIZE($LISTIF($EXACT($item,MEA), $LIST(R1:C1..R$ROW():C1) ))"}%

%CALC{"$SET( row_list, $NOEXEC($LISTMAP($EVAL($index +$CODE($item)-$CODE($item)),  $LIST(R1:C1..R$ROW():C1)  )))"}%
%CALC{"$SET(mea_list,  $NOEXEC($LISTIF( $EXACT($TRIM($T(R$item:C1)),MEA), $EXEC($GET(row_list)) ) ))"}%
%CALC{"$SET(mea_count, $LISTSIZE($EXEC($GET(mea_list))))"}%
row_list: %CALC{" $EXEC($GET(row_list)) "}%;
mea_list: %CALC{" $EXEC($GET(mea_list)) "}%;
mea_count: %CALC{" $GET(mea_count) "}%;
not_mea: %CALC{"$LISTMAP( $T(R$item:C1), $LISTIF( $NOT($EXACT($T(R$item:C1),MEA)), $EXEC($GET(row_list)) ))"}%

-- TroyGoodson - 30 Mar 2007

I do not understand your approach. I added an example how I would count the number of times "MEA" shows up.

-- PeterThoeny - 14 Apr 2007

The EXISTS function apparently does not recognize non-WikiWord topic names. :-( See bug report 3889

-- VickiBrown - 13 Apr 2007

All $EXISTS() does is this: $result = TWiki::Func::topicExists( "", $theAttr );. I suspect that this is a bug of the core TWiki.

-- PeterThoeny - 14 Apr 2007

It turned out to be a not so intuitive spec of TWiki::Func::topicExists(), it falls back to the Main web if the web is not specified. I changed the plugin to fall back to the current web. New version is posted on twiki.org.

-- PeterThoeny - 14 Apr 2007

I'm seeing unexpected results in some SpreadSheet plugin code

I want to check whether a topic page exists and, if so, print the link the tthe page., Otherwise, I \include some boilerplate that says "page doesn't exist" and offers a button to create the page from a template.

I hvae

   * Set PROJECTNAME =%FORMFIELD{"ProjectName" topic="%TOPIC%"}%
   * Set PNAME = %CALC{"$TRANSLATE($TRIM(%PROJECTNAME%),$sp,_)"}%

Resultsw of this seem good:

    * PROJECTNAME: Sample Project for Testing Templates
    * PNAME: Sample_Project_for_Testing_Templates 

but when I try the CALC code

%CALC{$IF($EXISTS([[Web.PagePrefix_%PNAME%]]), [[Web.PagePrefix_%PNAME%]], $NOP(%)INCLUDE{"BoilerplatePage"}$NOP(%))}%  

instead of simply calculating the condition and printing the link (or including the boilerplate), I see

$IF($EXISTS(Web.PagePrefix_%CALC{"Sample_Project_for_Testing_Templates"?), 
 Web.PagePrefix_Sample_Project_for_Testing_Templates?, 
 $NOP(%)INCLUDE{...

Where did that ... come from? The IF should be looking at the value of %CALC{" which should be the string "Sample_Project_for_Testing_Templates". Am I missing something in my syntax or tripping over a bug?

-- VickiBrown - 03 May 2007

There are couple of ways to think about * Set variables. You can think of them as 'pass by reference' instead of 'pass by value'. You can think of them like C macros. In any case, when you write %PNAME%, it inserts %CALC... instead of the calculated value, and it appears that nested CALCs don't work. Try using CALC's SET and GET functions instead; they actually store the computed value.

-- JustinLove - 04 May 2007

does the warn parameter for %INCLUDE% not do what you want?

-- WillNorris - 04 May 2007

I would love to see a $MAKEWIKIWORD function. See Support.CalculateWikiName for details.

-- VickiBrown - 24 May 2007

When my company's InstalledPlugins page shows me verbatim13� how do I reconcile that with the dated list of releases (change history) on the Plugins/Plugins/SpreadSheetPlugin page?

Our SpreadSheetPlugin (documentation) page is out of sync with the actual installed plugin. (we have a bizarre repackaging an installation process where I work)

-- VickiBrown - 15 Jun 2007

I checked, 8154 is an old version 18 Dec 2006.

-- PeterThoeny - 16 Jun 2007

Any help on DynamicDoubleBracketLinks ?

-- KeithHelfrich - 15 Jul 2007

Hi, to match my requirements for templates i had to actually calculate the formula before the edition (beforeEditHandler)... I just implemented something to do it when there is %EXECCALC..% and do the same things done for %CALC..% Don't know if anybody would be interested in that... Is it a good idea to add this thing in the next version of SpreadSheetPlugin ?

thx

-- GuillaumeBardy - 24 Jul 2007

I would need to understand the use case. If this case is too rare it should not go into the next release.

-- PeterThoeny - 24 Jul 2007

Ok. I think it's quite usefull : i was using templating to automate the creation of the topics linked with a workflow. But, for that i needed to name my topics and some variables needed to be named before saving thanks to an incrementation. Like i have those topics realted because they have the same number identifying them. When i refuse them in my workflow, i archive them and create new topics with a new identifier... Hope it's pretty clear. Actually, i don't really see the problem of including this functionnality, because i find it missing to really calculate and not just doing it when the page is rendered...

-- GuillaumeBardy - 25 Jul 2007

Hi. How comes that %CALC{"$TIME(01/Jan/1970 00:01:00 GMT)"}% and
%CALC{"$TIME(01/Jan/1970 00:01:01 GMT)"}%
give the same result, i.e. 60?
I would expect 60 and 61.

Cheers.

-- EmanueleCupido - 31 Jul 2007

There seem to be no way (or at least I can't find one) to get $TIME to convert a date expressed in DOY (Day Of Year).
DOY dates are usually expressed as 2007.122 or, adding the time, as 2007.122.12.35.00.
$TIME seems to able to handle a similar format for conventional dates i.e. 2009.12.31.23.59.59. (6 numbers instead of 5).
Would it be possible to empower $TIME with the ability to convert a DOY date such as 2007.122.12.35.00 ?

Re-Cheers

-- EmanueleCupido - 31 Jul 2007

The current plugin inplementation does not support DOY. It could be added if you post a patch.

-- PeterThoeny - 12 Aug 2007

Is the SETIFEMPTY performing lazy evaluation? In other words, if I make a LDAP lookup inside the SETIFEMPTY call, will it only be called the first time I use it on a topic?

I have several "common topics" which I treat as "plugins" (calling them via Parameterized Includes) and I'm looking to do a LDAP lookup only once, even if the same plugin is included on the page multiple times.

-- DovKatz - 21 Sep 2007

I am not familiar with the LDAP lookup you are referring to. Comparing it to a SEARCH, if you want to execute a spreadsheet formula once per hit you basically need to defer the CALC so that it does not fire off once for the whole search. In a FormattedSearch, you can do that by escaping the percent signs inside the search format parameter, e.g. to execute a %CALC{...}% once per hit, write format="$percntCALC{...}$percnt".

-- PeterThoeny - 25 Sep 2007

Can I request a function to calculate ISO week number from a date? Seems that all my timesheets in various companies these days are in ISO week number..

-- PeterPayne - 10 Oct 2007

Please feel free to contribute the code for an $ISOWEEK() function, or sponsor the work.

-- PeterThoeny - 16 Oct 2007

# =========================
sub _year2isoweek1serial
{
    my ( $year, $isGmt ) = @_;

    # Contributed by PeterPayne - 22 Oct 2007
    # Calculate the serial of the beginning of week 1 for specified year.
    # Year is 4 digit year (e.g. "2000")

    $year -= 1900;

    # get Jan 4
    my @param = ( 0, 0, 0, 4, 0, $year );
    my $jan4epoch = ( $isGmt ? timegm( @param ) : timelocal( @param ) );

    # what day does Jan 4 fall on?
    my $jan4day = ( $isGmt ? (gmtime($jan4epoch))[6] : (localtime($jan4epoch))[6] );

    $jan4day += 7 if ( $jan4day < 1 );

    return( $jan4epoch - ( 24 * 3600 * ( $jan4day - 1 ) ) );
}

# =========================
sub _serial2isoweek
{
    my ( $theTime, $isGmt ) = @_;

    # Contributed by PeterPayne - 22 Oct 2007
    # Calculate the ISO8601 week number from the serial.

    my( $sec, $min, $hour, $day, $mon, $year, $wday, $yday ) = ( $isGmt ? gmtime( $theTime ) : localtime( $theTime ) );
    my $yearserial = _year2isoweek1serial( $year + 1900, 1 );
    if ( $mon >= 11 ) { # check if date is in next year's first week
        my $yearnextserial = _year2isoweek1serial( $year + 1900 + 1, 1 );
        $yearserial = $yearnextserial if ( $theTime >= $yearnextserial );
    } elsif ( $theTime < $yearserial ) {
        $yearserial = _year2isoweek1serial( $year + 1900 - 1, 1 );
    }

    # calculate GMT of just past midnight today
    my $today_gmt = timegm( 0, 0, 0, $day, $mon, $year );
    my $week = int( ( $today_gmt - $yearserial ) / ( 7 * 24 * 3600 ) ) + 1;

    return( $week );
}

# untested - plug into doFunc()
#    } elsif( $theFunc eq "ISOWEEK" ) {
#        $result = _serial2isoweek( $theAttr );
#    }

I've tested _serial2isoweek( serial ) and it appears to give the right answer. It should be completely free code as I wrote the code from scratch from the definition (i.e. "ISO week 1 commences from the Monday-Sunday week containing Jan 4 in any year.").

update: corrected _serial2isoweek() function to correctly support end-of-year dates that are in week 1 of next year.

-- PeterPayne - 22 Oct 2007

I have a problem with WORKINGDAYS on my twiki install:

Weirdly, 0 gives 1 (1) on my site - expected 0  Correctly, 0 gives 0 (0) on my site - expected 0  Weirdly, 1 gives 0 (0) on my site - expected 1  Correctly, 1 gives 1 (1) on my site - expected 1  

And get this (correct) result here:

Weirdly, %CALC{"$WORKINGDAYS($TIME(2007/12/08), $TIME(2007/12/09))"}% gives 0 (1) on my site - expected 0

Weirdly, %CALC{"$WORKINGDAYS($TIME(2007/12/09), $TIME(2007/12/10))"}% gives 0 (0) on my site - expected 0

Weirdly, %CALC{"$WORKINGDAYS($TIME(2007/12/10), $TIME(2007/12/11))"}% gives 1 (0) on my site - expected 1

Correctly, %CALC{"$WORKINGDAYS($TIME(2007/12/11), $TIME(2007/12/12))"}% gives 1 (1) on my site - expected 1

I assure you I get the results in () on my site and it is running TWiki version TWiki-4.1.2, Sat, 03 Mar 2007, build 13046, Plugin API version 1.11

Ah - just did an experiment - if I change the above to have all the times as GMT (eg $TIME(2007/12/09 GMT) ) I get the expected results?????? Local time here (Brisbane, Queensland, Australia) is GMT+10

I guess this is a bug, not sure if it is limited to WORKINGDAYS or is some general issue with "day boundaries"?

-- DarrylGreen - 11 Dec 2007

CrawfordCurrie contributed that formula. Maybe he can help?

-- PeterThoeny - 12 Dec 2007

I've solved my immediate problem (which was only concerned with days within a 1 week range) a bit more verbosely using checks on $FORMATTIME(..., $wd) values - which seems to be fine. This rather suggests that it is WORKINGDAYS specific, so it isn't an urgent/big issue for me.

-- DarrylGreen - 13 Dec 2007

I've made some changes to Calc.pm (see attached Calc_with_DOY.clean.pm, based on SpreadsheetPlugin rev. 10197), mainly to _date2serial so that function TIME now supports the following additional formats:

  • 31 Dec 2003 - 23:59:59; 31-Dec-2003 - 23:59:59; 31 Dec 2003 - 23:59:59 - any suffix
  • doy

It works, as far as I can tell.

The following is the revised and comprehensive list of formats supported by the function TIME (except the ambiguous US style dates). It could be added to the function TIME doc at SpreadSheetPlugin#TIME_text_convert_a_date_string

01 Jan 1970 - 00:02:03
01-Jan-1970 - 00:02:03
01/Jan/1970 - 00:02:03
01 Jan 1970 - 00:02:03 GMT
01 Jan 1970 - 00:02
01-Jan-1970 - 00:02
01/Jan/1970 - 00:02
02 Jan 1970
02/Jan/1970
02-Jan-1970
02 Jan 80 (starts at year 1980)
02-Jan-90 (year = 1990)
02/Jan/08 (year = 2008)
1970/01/02/00/02/01
1970-01-02-00-02-01
1970.01.02.00.02.01
1970/01/02 00:02:01
1970/01/02,00:02:01
1970/01/02/00/02
1970-01-02-00-02
1970.01.02.00.02
1970/01/02 00:02
1970/01/02,00:02
1970/01/02
1970-01-02
doy 1970.002.03.04.05 (yyyy.ddd.hh.mm.ss) 
doy 1970.002.03.04 (yyyy.ddd.hh.mm)
doy 1970.002.03 (yyyy.ddd.hh)
doy 1970.002 (yyyy.ddd)
DOY 1970.002.03.04.05 GMT
doy1970.2.3.4.5

-- EmanueleCupido - 30 Jan 2008

Thanks Emanuele, this should be taken into the plugin.

  • Emanuele's enhancement is now in release 2010-05-22 of the SpreadSheetPlugin. Thanks Emanuele!

-- PeterThoeny - 02 Mar 2008

Hi! Is there a way to do replacements with $SUBSTITUTE using back references?

I need to make all headers one level down, i.e. get the result equivalent to PERL expression s/(---\++)/\1+/g.

I want this to have included parts of Wiki pages to align with the headings of the main page which includes them. Currently I am trying to achieve that using %SEARCH + nested %CALC, but the following does not work:

%CALC{"$SUBSTITUTE(a ---+ Header1 ---++ Header2,(---\++),\1\+,,r)"}%

Does $SUBSTITUTE support back references? If not, what would you suggest to solve my task (include one page into another with having all headings shifted one level down)? Thanks.

-- AlexanderKamotsky - 22 Feb 2008

This here works: %CALC{$SUBSTITUTE(aaa ---+ bbb ---++ ccc, ---\+, ---++, , r)}% returns: aaa ---++ bbb ---+++ ccc

You can also embed a CALC into the format of a SEARCH, such as format="$percntCALC{...}$percnt", but it will not work reliably in your case because commas and parenthesis in the text will confuse the $SUBSTITUTE().

What you really need is RelativeHeadingLevelsforINCLUDE, which is at the discussion phase.

-- PeterThoeny - 02 Mar 2008

Oh silly me! That was so obvious! Thank you! =)

Anyway, what about back references in general? I've checked the plugin source and I think now I see why back references do not work:

# global replace if( eval '$string =~ s/$from/$to/g' && $string ) {     $result = $string; }

Can I suggest to change it a little bit to the following:

# global replace if( eval "\$string =~ s/$from/$to/g" && $string ) {     $result = $string; }

This way $from and $to will be replaced before calling eval and $1 backreference should work. I do not have TWiki installed but I've cut & tested this piece locally. And, of course, similar change should be done with a part processing $inst.

-- AlexanderKamotsky - 06 Mar 2008

Any security implications by doing so?

-- PeterThoeny - 06 Mar 2008

Hello, I'm trying to use this Plugin to display the TopicActionButtons Bar just for some Users/Groups. But ist seems that there is some strange problem. Here is my code (twiki.pattern.tmpl):

%TMPL:DEF{"topicaction"}%%CALC{"$IF( pretty long condition here ,<a name="topic-actions"></a><div class="patternTopicActions"><div class="patternTopicAction"><span class="patternActionButtons">%TMPL:P{"topicactionbuttons"}%</span></div><!--/patternTopicAction--></div><!--/patternTopicActions-->,)"}% %TMPL:END%
It seems to work pretty fine but the code gets cut off somewhere (debug.txt) :
SpreadSheetPlugin::Calc::doFunc: MAIN( $IF( condition here again ,<a name="topic-actions"></a><div class="patternTopicActions"><div class="patternTopicAction"><span class="patternActionButtons"><span><a href='https://physik.htu.at/twiki/bin/edit/TWiki/SpreadSheetPlugin?t=1212847158' rel='nofollow' title='Edit this topic text' accesskey='e'><span class='twikiAccessKey'>E</span>dit</a></span><span class='twikiSeparator'>&nbsp;|&nbsp;</span><span><a href='/twiki/bin/view/TWiki/SpreadSheetPlugin?cover=print;formula=%24AND%28%24NOT%28JoergHerzinger2c%22TWikiGuest%22%29%2c%20%24EXACT%28TWiki%2cFreizeit%29%20%29' rel='nofollow' title='Printable version of this topic' accesskey='p'><span class='twikiAccessKey'>P</span>rint version</a></span><span class='twikiSeparator'>&nbsp;|&nbsp;</span><span><span><a href='/twiki/bin/rdiff/TWiki/SpreadSheetPlugin?type=history' rel='nofollow' title='View total topic history' accesskey='h'><span class='twikiAccessKey'>H</span>istory</a></span>: r17&nbsp;<a rel="nofollow ) start
Any ideas why this could happen?

-- JoergHerzinger - 07 Jun 2008

The problem is the double quote in the anchor, it terminates the CALC prematurely. Use single quote or escape the double quote (e.g. name=\"topic-actions\").

-- PeterThoeny - 10 Jun 2008

Hello I am making a FMEA overview form, this is done by a nested search. The search and the nested search are working fine, but the calculation in the nested search is not working. The nested calculation is the same as the not nested version only the calculation is from the followup topic. Do I need to place extra $dollar or $percnt to make it work (I have tried I think every combination) or is it simply impossible?

%SEARCH{ "^---\++ Failure;FmeaActionRequired.*?value=\"To be improved\"" scope="text" regex="on" nosearch="on" nototal="on" format="| $topic | $percntCALC{\"$MULT( $formfield(FmeaSeverity), $formfield(FmeaDectability))\"}$percnt |$percntSEARCH{ \"$topicFollowUp\" scope=\"topic\" nosearch=\"on\" nototal=\"on\" format=\"$dollartopic |$percntCALC{\"$MULT( $dollarformfield(FmeaSeverity), $dollarformfield(FmeaDectability) )\"}$percnt |\" }$nop%|" }%

-- FrankPauw - 26 Jun 2008

Hello, I am in the process of installing TWiki and a number of plugins, including NatSkinPlugin. Upon completing the NatSkin installation, SpreadSheetPlugin suddenly quit working (InstalledPlugins reports "SpreadSheetPlugin has been disabled"). There is nothing in debug.txt, and nothing in the apache error logs. I have no idea what killed SpreadSheetPlugin. Any ideas? Thanks in advance.

-- ChristopherBurton - 01 Jul 2008

How about adding a SUBSTRING function? I have used SpreadSheetPlugin many times to manipulate %SEARCH% results but had to jump through hoops, using REPLACE, SUBSTITUTE, etc., to do so. SUBSTRING would have greatly simplified those tasks.

-- RobStewart - 23 Jul 2008

Yes, it would be useful to add a SUBSTRING function.

  • $SUBSTRING() function added (in SVN) -- PeterThoeny - 25 Mar 2009

-- PeterThoeny - 26 Jul 2008

Please refer to the NotWorkingEditTableAndSpreadsheet

It seems that there is a bug while using the combination of EditTablePlugin and SpreadsheetPlugin

-- AmitTendulkar - 03 Sep 2008

I've a lot of project plans on Twiki and I would like to do some more automatic calculation of dates that I can do by the timeadd command but I miss the unit Working days. Could it be possible to add that unit.

-- JacobTranegaard - 04 Sep 2008

Hm Jacob, I remember that Crawford did suggest that already long ago. I'm not sure it was ever incorporated into the release version.

-- FranzJosefGigler - 04 Sep 2008

Hi, I am trying to embed %SEARCH% in %CALC% (not vice versa), but failed. My idea is I have a variable defined in subtopics like * Set X = 2, then I want to do AVERAGE on the variables fetched from the subtopics. Any idea how to deal with this?

Here is my formula:

* Set SUBIMPORTANCE_ = %SEARCH{"Set PARENT = %TOPIC%" type="keyword" topic="SVTeamGoal*" expandvariables="on" nosearch="on" nototal="on" separator="," format="$pattern(.*?Set IMPORTANCE = (.*?)\n.*)" }% // this will return (2,3)

* %CALC{"$SET(_IMPORTANCE, $AVERAGE(%SUBIMPORTANCE_%))"}% // this doesn't work

-- GreenLuo - 16 Sep 2008

I'd love to see an HTML encoder function.

-- VickiBrown - 16 Sep 2008

Maybe you can alter your search to set the variable using the SET function of SpreadSheetPlugin.

-- RafaelAlvarez - 16 Sep 2008

I had the same problem as ChristopherBurton raised above; after installing NatSkin, spreadsheet plugin shows disabled in the TWikiPlugins topic / diagnostics section. data/warning.txt also says SpreadSheet Plugin disabled. Running configure shows the plugin is enabed. I tried disabled and re-enable to no effect. I finally found the problem was that both SpreadSheetPlugin and EditTablePlugin were listed in TWikiPreferences under the section for Plugins, setting DISABLEDPLUGINS. I edited the TWikiPreferences page and removed them and this resolved the SpreadSheetPlugin being disabled. I can only assume that somehow, these plugins are getting entered into the TWikiPreferences DISABLEDPLUGINS during installation of NatSkin or it's pre-requisites. I installed NatSkin via the configure page and not manually. Hope this info helps someone

-- AndrewRobinson - 23 Oct 2008

Bug in TIMEADD() concerning only October?

I use TIMEADD to calculate the actual numbers of days in a month for display in a table. I take the first day of the requested month and add 28, 29, or 30 resp. to find the actual numbers (which results e.g. in " 28 | 01 | 02 | 03 | " for February). This works fine for every month and year, forward and backwards, except for October. Calculating "October 1st + 28 + n" results in 28 + n. This happens with all years forwards and backwards.

This is the code snippet:

...


%CALC{"$SET( view_time , %<nop>URLPARAM{"view_time"}% )"}% 
%CALC{"$SETIFEMPTY( view_time , $TIME() )"}%

%CALC{"$SET( first_day , $TIME( $FORMATTIME( $GET(view_time) , $year/$month/1 ) ) )"}% 
%CALC{"$SET( first_wday , $FORMATTIME( $GET(first_day) , $wd ) )"}%

%CALC{"$SET( my_29, $FORMATTIME( $TIMEADD( $GET(first_day), 28, day) , $day ))"}% 
%CALC{"$SET( my_30, $FORMATTIME( $TIMEADD( $GET(first_day), 29, day) , $day ))"}% 
%CALC{"$SET( my_31, $FORMATTIME( $TIMEADD( $GET(first_day), 30, day) , $day ))"}%

...

%CALC{"$GET(my_29)"}% %CALC{"$GET(my_30)"}% %CALC{"$GET(my_31)"}%
...

Regards

-- SvenNielsen - 08 Dec 2008

I have a hunch that this has to do with daylight savings time change. First day at midnight might interfere with 60 min time difference on daylight saving change. Try midnight plus a few hours to be on the safe side, such as $FORMATTIME( $GET(view_time) , $year/$month/01 06:00)

-- PeterThoeny - 09 Dec 2008

Thanks! You're right. I actually thought I tried that before, but obviously I didn't or made a mistake. Adding the hours as you indicated it works fine now.

-- SvenNielsen - 09 Dec 2008

I would suggest that LISTJOIN supports joining lists using an empty separator. To accomplish this I found I had to use the following awkward construction:

%CALC{$LISTJOIN(C,$Label,harter)}%

for example. This is from a format clause. $Label is a value returned from a query, and I want to construct the string resulting from concatenating $Label and Charter.

-- ThomasWeigert - 25 May 2009

You can simply concatenate string inside a CALC, such as %CALC{Foo$formfield(Anything)MoreStuff}%. Use $NOP() to delimit a variable, such as %CALC{C$Label$NOP()Charter}%.

Nevertheless a empty separator for $LISTJOIN() would be useful.

-- PeterThoeny - 2009-05-25

Thanks for the tip. Nevertheless, an empty separator might be clearer, or a parentization for variables, as in Perl. Your tip might warrant documentation as it is not obvious.

-- ThomasWeigert - 25 May 2009

What do you mean by "parentization for variables"?

-- PeterThoeny - 2009-05-26

In Perl you can write ${abc} if the variable $abc needs to be put in a context where it conflicts (e.g., in the middle of a string). In bash you can use similar parenthesis.

-- ThomasWeigert - 1Jun 2009

I originally proposed a ISODATE function for PeterPayne's patch on 2007-10-22. I think it is better to add new tokens to the existing FORMATGMTIME and FORMATTIME function as described in TWikibug:Item6526.

-- PeterThoeny - 2010-07-19

Topic attachments
I Attachment History Action Size Date Who Comment
Texttxt AboveOffsetPatch.txt r2 r1 manage 0.7 K 2005-03-24 - 06:15 DavidBright Patch to add offset parameter to ABOVE()
Perl source code filepm Calc_with_DOY.clean.pm r1 manage 41.3 K 2008-01-30 - 19:08 EmanueleCupido Added support for DOY-format date in function TIME
Texttxt ListCountItemsPatch.txt r1 manage 1.0 K 2005-03-24 - 05:13 DavidBright Patch to add function LISTCOUNTITEMS(); this function is the same as COUNTITEMS(), but returns the result as a list.
Perl source code filepm SpreadSheetPlugin.pm r2 r1 manage 21.4 K 2003-06-12 - 08:11 DarrylGreen Adds CALCRANGE extensions
Unknown file formatCalcBugFix SpreadSheetPlugin.pm.BenWebb.CalcBugFix r2 r1 manage 38.7 K 2004-12-03 - 03:37 BenWebb Updated version to cache tables and calculate after they are loaded
Unknown file formatpatch SpreadSheetPlugin.pm.MULT.patch r1 manage 0.6 K 2003-09-25 - 08:00 GerritJanBaarda Adds MULT function
Unknown file formatcalcrange SpreadSheetPlugin.pm.calcrange r1 manage 21.4 K 2003-06-12 - 08:13 DarrylGreen Adds named ranges
Unknown file formatdiff SpreadSheetPlugin.pm.diff r1 manage 1.8 K 2003-03-07 - 02:40 SvenDowideit day, week, month unit calculation
Unknown file formateuro SpreadSheetPlugin.pm.euro r1 manage 20.2 K 2003-09-13 - 18:37 LarsWith Adds formatting possibilities for localization
Texttxt SumItems.txt r1 manage 1.2 K 2005-05-03 - 16:42 OleCMeldahl Patch for adding SUMITEMS as described in topic
Perl source code filepm timeImproved_Calc.pm r3 r2 r1 manage 43.8 K 2006-08-05 - 15:56 TaitCyrus Fixes TIMEDIFF, TIMEADD, TIME, FORMATTIME, & FORMATGMTIME adds STRFTIME and STRFTIMEGMT
Edit | Attach | Watch | Print version | History: r10 < r9 < r8 < r7 < r6 | Backlinks | Raw View | Raw edit | More topic actions
Topic revision: r10 - 2012-06-24 - PeterThoeny
 
  • Learn about TWiki  
  • Download TWiki
This site is powered by the TWiki collaboration platform Powered by Perl Hosted by OICcam.com Ideas, requests, problems regarding TWiki? Send feedback. Ask community in the support forum.
Copyright © 1999-2017 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.