Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the Spreadsheet-ParseExcel CPAN distribution.

Maintainer(s)' notes

If you are reporting a bug in Spreadsheet::ParseExcel here are some pointers

1) State the issues as clearly and as concisely as possible. A simple program or Excel test file (see below) will often explain the issue better than a lot of text.

2) Provide information on your system, version of perl and module versions. The following program will generate everything that is required. Put this information in your bug report.

    #!/usr/bin/perl -w

    print "\n    Perl version   : $]";
    print "\n    OS name        : $^O";
    print "\n    Module versions: (not all are required)\n";

    my @modules = qw(
                      Spreadsheet::ParseExcel
                      Scalar::Util
                      Unicode::Map
                      Spreadsheet::WriteExcel
                      Parse::RecDescent
                      File::Temp
                      OLE::Storage_Lite
                      IO::Stringy
                    );

    for my $module (@modules) {
        my $version;
        eval "require $module";

        if (not $@) {
            $version = $module->VERSION;
            $version = '(unknown)' if not defined $version;
        }
        else {
            $version = '(not installed)';
        }

        printf "%21s%-24s\t%s\n", "", $module, $version;
    }

    __END__

3) Upgrade to the latest version of Spreadsheet::ParseExcel (or at least test on a system with an upgraded version). The issue you are reporting may already have been fixed.

4) Create a small example program that demonstrates your problem. The program should be as small as possible. A few lines of codes are worth tens of lines of text when trying to describe a bug.

5) Supply an Excel file that demonstrates the problem. This is very important. If the file is big, or contains confidential information, try to reduce it down to the smallest Excel file that represents the issue. If you don't wish to post a file here then send it to me directly: jmcnamara@cpan.org

6) Say if the test file was created by Excel, OpenOffice, Gnumeric or something else. Say which version of that application you used.

7) If you are submitting a patch you should check with the maintainer whether the issue has already been patched or if a fix is in the works. Patches should be accompanied by test cases.

Asking a question

If you would like to ask a more general question there is the Spreadsheet::ParseExcel Google Group.

Report information
The Basics
Id: 60547
Status: resolved
Priority: 0/
Queue: Spreadsheet-ParseExcel

People
Owner: Nobody in particular
Requestors: wjaguar [...] users.sourceforge.net
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: 0.57
Fixed in: (no value)



Subject: Problem with currency formats
Download (untitled) / with headers
text/plain 385b
For a cell in currency format like this one: "[$$-409]# ##0,00", $cell->value() returns a mangled string: 27400 -> "[$$427,49]00.00" instead of "$27 400,00". From my look at source code, it seems &ExcelFmt in Utility.pm doesn't understand the construct "[$some_text-locale_code]", like the "[$$-409]" in the above case. An example file is attached (created by OpenOffice.org 3.1.1).
Subject: 0numtest.xls
Download 0numtest.xls
application/vnd.ms-excel 6.5k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 587b
On Fri Aug 20 06:26:02 2010, wjaguar wrote: Show quoted text
> For a cell in currency format like this one: "[$$-409]# ##0,00", > $cell->value() returns a mangled string: 27400 -> "[$$427,49]00.00" > instead of "$27 400,00".
Hi, Thank you for the bug report. I've put in a fix and test case and pushed the changes to GitHub: http://github.com/jmcnamara/spreadsheet-parseexcel You can download the updated version and test it from there. Use the "Download source" button The actual format stored in the file is '[$$-409]#,##0.00' which means that 27400 is rendered as '$27,400.00'. John. --
From: wjaguar [...] users.sourceforge.net
Download (untitled) / with headers
text/plain 1.1k
Show quoted text
> I've put in a fix and test case and pushed the changes to GitHub: > http://github.com/jmcnamara/spreadsheet-parseexcel > You can download the updated version and test it from there. Use the
Still doesn't fix the general problem, only a single specific case. The construct was described as "[$some_text-locale_code]" for a reason - "some_text" may be something *other* than a "$". An example is right there in the second cell of sample file - 826331.94 with a format string of "# ##0,00\ [$руб.-419];-# ##0,00\ [$руб.-419]", still getting mangled into "826,331.94 [$руб.419]" instead of "826,331.94 руб." What works in general case is: if ($format =~ s/(\[\$(.+?)(-\d+)?\])/$2/) ... Because the locale code is in fact optional - as in the case of "# ##0,00 [$RUR];-# ##0,00 [$RUR]" format string. I attached a new sample file with all 3 mentioned formats. Show quoted text
> The actual format stored in the file is '[$$-409]#,##0.00' which means > that 27400 is > rendered as '$27,400.00'.
'[$$-409]# ##0,00' and '$27 400,00' is how OpenOffice renders them for Russian locale. Maybe Spreadsheet::ParseExcel also should use current locale's thousand separator and decimal separator when rendering numeric formats?
Subject: 1numtest.xls
Download 1numtest.xls
application/vnd.ms-excel 6.5k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 1.6k
On Tue Aug 24 07:50:25 2010, wjaguar wrote: Show quoted text
> Still doesn't fix the general problem, only a single specific case. The > construct was described as "[$some_text-locale_code]" for a reason - > "some_text" may be something *other* than a "$". > An example is right there in the second cell of sample file - 826331.94 > with a format string of "# ##0,00\ [$руб.-419];-# ##0,00\ [$руб.-419]", > still getting mangled into "826,331.94 [$руб.419]" instead of > "826,331.94 руб."
Hi, Could you provide some test cases in the following format as used in 21_number_format_user.t: [ 140, 27400, '$27,400.00', '[$$-409]#,##0.00' ], E.e, Test case number, unformatted number, expected formatted result, the format. You can see the existing ones in: http://github.com/jmcnamara/spreadsheet-parseexcel/blob/master/t/21_number_format_user.t Show quoted text
> What works in general case is: > if ($format =~ s/(\[\$(.+?)(-\d+)?\])/$2/) ... > Because the locale code is in fact optional - as in the case of > "# ##0,00 [$RUR];-# ##0,00 [$RUR]" format string. > I attached a new sample file with all 3 mentioned formats.
Add this to the test cases as well. Show quoted text
> Maybe Spreadsheet::ParseExcel also should use current > locale's thousand separator and decimal separator when rendering numeric > formats?
Not in the lifetime of this universe. :-) Trying to account for every possible date, time and currency format in every locale and language just isn't possible. As a workaround, a future release of the module will return the number format along with the unformatted value which will allow the end user to process the number as they wish. John. --
From: wjaguar [...] users.sourceforge.net
Download (untitled) / with headers
text/plain 622b
Show quoted text
> Could you provide some test cases in the following format as used in > 21_number_format_user.t:
[ 141, 826331.94, '826,331.94 руб.', '#,##0.00\ [$руб.-419]' ], [ 142, 826331.94, '826,331.94 RUR', '#,##0.00\ [$RUR]' ], Show quoted text
> Trying to account for every possible date, time and currency format in > every locale and language just isn't possible.
It *is* perfectly possible - as in "already implemented in OpenOffice.org". And given a working C++ implementation of Excel-like formatting, locating the file(s) with said implementation within OO.o source tree is likely to be the hardest part of redoing it in Perl. :-)
Download (untitled) / with headers
text/plain 1.1k
On Tue Aug 24 09:57:10 2010, wjaguar wrote: Show quoted text
> > Could you provide some test cases in the following format as used in > > 21_number_format_user.t:
> > [ 141, 826331.94, '826,331.94 руб.', '#,##0.00\ [$руб.-419]' ], > [ 142, 826331.94, '826,331.94 RUR', '#,##0.00\ [$RUR]' ],
Hi Dmitry, I've put in another patch based on your testcases. Can you try the latest version on GitHub: http://github.com/jmcnamara/spreadsheet-parseexcel If the patch isn't sufficient then submit some more test cases as before. Show quoted text
> > Trying to account for every possible date, time and currency format in > > every locale and language just isn't possible.
> > It *is* perfectly possible - as in "already implemented in OpenOffice.org". > And given a working C++ implementation of Excel-like formatting, > locating the file(s) with said implementation within OO.o source tree is > likely to be the hardest part of redoing it in Perl. :-)
Too true. :-) Actually, it would be possible to support user defined thousand and decimal separators in currency values. I don't know how useful that would be. I'll put it on the TODO list and if other people ask for it I'll implement it. John. --
Fixed in Spreadsheet::ParseExcel version 0.58. Thanks, John. --


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.