Feedback on SpreadSheetPlugin

• Please use the SpreadSheetSandbox in the Sandbox web for testing.
• Older discussions moved to SpreadSheetPluginDevArchive

-- PeterThoeny - 05 Aug 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; 

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

So, as an example,

30 

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

Here

1978/10/01 

-- 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:  Today in UTC time is:  

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://twiki.org/cgi-bin/view, ) , emtpy, Blog Codev Main Plugins Sandbox Support TWiki TWiki01 TWiki02 TWiki03 TWiki04 TWiki04x01 TWiki04x02 TWiki04x03)"}%

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

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

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

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

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

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.

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

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

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