You are here: Foswiki>Tasks Web>Item411 (24 Oct 2011, GeorgeClark)Edit Attach

Item411: SpreadSheetPlugin does not handle comma-separated numbers nicely

Priority: Enhancement
Current State: Proposal Required
Released In: n/a
Target Release:
Applies To: Extension
Component: SpreadSheetPlugin
Reported By: DanielPittman
Waiting For:
Last Change By: GeorgeClark
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 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


ItemTemplate edit

Summary SpreadSheetPlugin does not handle comma-separated numbers nicely
ReportedBy DanielPittman
Codebase trunk
SVN Range TWiki-4.2.3, Wed, 06 Aug 2008, build 17396
AppliesTo Extension
Component SpreadSheetPlugin
Priority Enhancement
CurrentState Proposal Required
ReleasedIn n/a
I Attachment Action Size Date Who Comment
spreadsheet-numbers-with-comma.patchpatch spreadsheet-numbers-with-comma.patch manage 1 K 08 Dec 2008 - 06:36 DanielPittman Patch fixing the bug found in SpreadSheetPlugin/ with comma-separated numbers.
Topic revision: r4 - 24 Oct 2011, GeorgeClark
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy