Item3743: Input bugs in SpreadSheetPlugin::Calc

Using SpreadSheetPlugin for electronic engineering, we encountered incorrect behaviour of the $ROUND function. Here is an example demonstrating the problem.

The following patch solves the problem.

--- Calc.pm.orig        2006-12-19 09:08:00.000000000 +0300
+++ Calc.pm     2007-03-08 10:23:48.000000000 +0300
@@ -343,6 +343,12 @@
         $result =  1 if( $i > 0 );
         $result = -1 if( $i < 0 );

+    } elsif( $theFunc eq "LN" ) {
+       $result = log (_getNumber( $theAttr ) );
+
+    } elsif( $theFunc eq "EXP" ) {
+       $result = exp (_getNumber( $theAttr ) );
+
     } elsif( $theFunc eq "IF" ) {
         # IF(condition, value if true, value if false)
         my( $condition, $str1, $str2 ) = _properSplit( $theAttr, 3 );
@@ -930,14 +936,20 @@
 sub _getNumber
 {
     my( $theText ) = @_;
+warn "_getNumber($theText)";
     return 0 unless( $theText );
     $theText =~ s/([0-9])\,(?=[0-9]{3})/$1/go;          # "1,234,567" ==> "1234567"
+    if ($theText =~ /[0-9]e/i) {                        # "1.5e-3"    ==> "0.0015"
+        $theText = sprintf "%.20f", $theText;
+        $theText =~ s/0+$//;
+    }
     unless( $theText =~ s/^.*?(\-?[0-9\.]+).*$/$1/o ) { # "xy-1.23zz" ==> "-1.23"
         $theText = 0;
     }
     $theText =~ s/^(\-?)0+([0-9])/$1$2/o;               # "-0009.12"  ==> "-9.12"
     $theText =~ s/^(\-?)\./${1}0\./o;                   # "-.25"      ==> "-0.25"
     $theText =~ s/^\-0$/0/o;                            # "-0"        ==> "0"
+warn "_getNumber returning $theText";
     return $theText;
 }

@@ -945,11 +957,10 @@
 sub safeEvalPerl
 {
     my( $theText ) = @_;
-
     # 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 =~ s/[^\!\<\=\>\-\+\*\/\%0-9e\.\(\)]*//go;
     $theText =~ /(.*)/;
     $theText = $1;  # untainted variable
     return "" unless( $theText );

Thank you

-- TWiki:Main/SergejZnamenskij - 08 Mar 2007

Thanks Sergej, this is fixed now and posted at TWiki:Plugins/SpreadSheetPlugin.

-- TWiki:Main.PeterThoeny - 09 Mar 2007

Fixed bug introduced by above change:

• $VALUE(Hello 123): 123

Was returning an error instead of 123.

-- PeterThoeny - 11 Mar 2007