Item411: SpreadSheetPlugin does not handle comma-separated numbers nicely

The various math functions in SpreadSheetPlugin such as SUM do not handle comma-separated numbers very nicely; this is a bit of a pain when, for example, using dollar values in a table. (This actually blocks us using TWiki to manage our quoting process.)

The modification to have to work nicely is relatively simple, and shouldn't cause a great deal of grief: the code in the calc module is already much more liberal than I would have expected when it comes to parsing numbers.

To reproduce, the following table shows the problem:

calc	expected
$100	$100
$1,000	$1,000
101	1100

Note that commas are not preserved, just like the dollar sign, but that the value should be tollerant of them being present.

NOTE: The current code will accept '1.2.3' as a valid floating point number, which is probably wrong, and which my patch doesn't accept. You might want to consider using Regexp::Common or some other general "number recognition" library to obtain a more effective regexp to pull out the values for you...

Anyway, a patch is attached that fixes this for both integer and floating point number extraction.

I would have added some tests but, as far as I can tell, there is no test suite for the SpreadSheetPlugin available, and no structure to create one. If that isn't the case then please let me know and I will add appropriate testing.

Finally, please note that unless I hear about feedback by email (to daniel@rimspace.net) I am not likely to respond to anything regarding this bug report, I am afraid.

---

Unfortunately this will probably need a new feature to fix it. There was a conflicting bug in LIST that has been fixed which invalidates your fix.

Lists in TablePlugin are defined as supporting a simple comma separated list, such as 0,1,3,4 ... Space is not required as a separator. On trunk with the latest TablePlugin fixes, the below should be the expected results:

100	100
200,300	200,300
600	600

So use of , as both a list separator, and as a separator in formatted numbers are in conflict with each other. In addition I suspect there are some internationalization issues in that in some countries numbers will be written as 100.000,00 using the comma as the decimal point.

We probably need a variation on LEFT, RIGHT, ABOVE and SPLIT (maybe others?) that split numbers using a defined separator rather than automatically on comma.

Changing this task to an enhancement, with feature proposal required.

-- GeorgeClark - 24 Oct 2011

Another option might be to allow the %TABLE macro to override the list separator. Currently /\s*,\s*/.

-- GeorgeClark - 24 Oct 2011

Commenting is disabled while not logged in