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: 60520
Status: resolved
Priority: 0/
Queue: Spreadsheet-ParseExcel

People
Owner: Nobody in particular
Requestors: andrea.pfister [...] mmsag.ch
Cc:
AdminCc:

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



Subject: problem with 0 enterpretation
Date: Thu, 19 Aug 2010 15:33:56 +0200
To: <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>
From: "Pfister Andrea" <andrea.pfister [...] mmsag.ch>
Hi I use your Modul since 2 years and i'm very happy with it. Version: 0.54 Now i have a problem with zeros definition. If some one make this definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl. Is there a way to get the ― from the interface? Best regards Andrea Multimedia Solutions AG Andrea Pfister | Softwareentwicklerin Dorfstr. 29 | CH-8037 Zurich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38 andrea.pfister@mmsag.ch | http://www.mmsag.ch 75-Jahre-Firmenjubilaum und ein neues Druckzentrum im Jahr 2010 - gute Grunde zum Feiern. Am Freitag, 24. September 2010, offnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung. Erfolgreiches erstes Geschaftsberichte-Symposium - Impressionen und Prasentationen. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
On Thu Aug 19 09:34:09 2010, andrea.pfister@mmsag.ch wrote: Show quoted text
> > Now i have a problem with zeros definition. If some one make this > definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl. > > Is there a way to get the ― from the interface?
Hi, You can get the format that will be applied as follows: #!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse('new_file.xls'); for my $worksheet ( $workbook->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; my $format_str = $workbook->{FmtClass}->FmtStringDef( $cell->{FormatNo}, $workbook ); print "Row, Col = ($row, $col)\n"; print "Unformatted = ", $cell->unformatted(), "\n"; print "Format string = ", $format_str, "\n"; print "Value = ", $cell->value(), "\n"; print "\n"; } } } __END__ The syntax is a bit unwieldy and it will be hopefully simplified in a later release. Anyway, I would recommend matching the format string with the format that is giving problems and substitute the cell value as necessary. Note, the format stored in the file may not be the same as the format that you applied in the Excel dialog. Excel stores formats with US style punctuation so the your format would probably be stored as something like: '#,##0.;(#,##0);"-"' John. --
Subject: AW: [rt.cpan.org #60520] problem with 0 enterpretation
Date: Thu, 26 Aug 2010 08:52:48 +0200
To: <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>
From: "Pfister Andrea" <andrea.pfister [...] mmsag.ch>
Thank you I will try this. Have a nice day! Andrea Multimedia Solutions AG Andrea Pfister | Softwareentwicklerin Dorfstr. 29 | CH-8037 Zürich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38 andrea.pfister@mmsag.ch | http://www.mmsag.ch 75-Jahre-Firmenjubiläum und ein neues Druckzentrum im Jahr 2010 - gute Gründe zum Feiern. Am Freitag, 24. September 2010, öffnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung. Erfolgreiches erstes Geschäftsberichte-Symposium - Impressionen und Präsentationen. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer. Show quoted text
-----Ursprüngliche Nachricht----- Von: John McNamara via RT [mailto:bug-Spreadsheet-ParseExcel@rt.cpan.org] Gesendet: Montag, 23. August 2010 17:20 An: Pfister Andrea Betreff: [rt.cpan.org #60520] problem with 0 enterpretation <URL: https://rt.cpan.org/Ticket/Display.html?id=60520 > On Thu Aug 19 09:34:09 2010, andrea.pfister@mmsag.ch wrote:
> > Now i have a problem with zeros definition. If some one make this > definition #'##0.;(#'##0);"―" in excel then i get a 0 in perl. > > Is there a way to get the ― from the interface?
Hi, You can get the format that will be applied as follows: #!/usr/bin/perl -w use strict; use Spreadsheet::ParseExcel; my $parser = Spreadsheet::ParseExcel->new(); my $workbook = $parser->parse('new_file.xls'); for my $worksheet ( $workbook->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); for my $row ( $row_min .. $row_max ) { for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; my $format_str = $workbook->{FmtClass}->FmtStringDef( $cell->{FormatNo}, $workbook ); print "Row, Col = ($row, $col)\n"; print "Unformatted = ", $cell->unformatted(), "\n"; print "Format string = ", $format_str, "\n"; print "Value = ", $cell->value(), "\n"; print "\n"; } } } __END__ The syntax is a bit unwieldy and it will be hopefully simplified in a later release. Anyway, I would recommend matching the format string with the format that is giving problems and substitute the cell value as necessary. Note, the format stored in the file may not be the same as the format that you applied in the Excel dialog. Excel stores formats with US style punctuation so the your format would probably be stored as something like: '#,##0.;(#,##0);"-"' John. --
Subject: AW: [rt.cpan.org #60520] problem with 0 enterpretation
Date: Thu, 26 Aug 2010 09:33:50 +0200
To: <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>
From: "Pfister Andrea" <andrea.pfister [...] mmsag.ch>
Hi John I try it, but i gat the rong Formatting string My Code: my $format_str = $p->{workbook}->{FmtClass}->FmtStringDef( $CurrentCell->{FormatNo}, $p->{workbook} ); if ($format_str and $CurrentCell->Value()) { warn "..............................."; warn $CurrentCell->Value(); warn $CurrentCell->{'Val'}; warn $format_str; warn "------------------------------"; } I have a Format in Excel like this #'##0.;(#'##0.);"-";_(@_) And the output of the program is ............................... 0.00000. 0 _ "SFr."\ * #,##0_ ;_ "SFr."\ * \-#,##0_ ;_ "SFr."\ * "-"_ ;_ @_ ------------------------------ Did I do something wrong? Greeting Andrea Multimedia Solutions AG Andrea Pfister | Softwareentwicklerin Dorfstr. 29 | CH-8037 Zurich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38 andrea.pfister@mmsag.ch | http://www.mmsag.ch 75-Jahre-Firmenjubilaum und ein neues Druckzentrum im Jahr 2010 - gute Grunde zum Feiern. Am Freitag, 24. September 2010, offnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung. Erfolgreiches erstes Geschaftsberichte-Symposium - Impressionen und Prasentationen. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.
On Thu Aug 26 03:34:02 2010, andrea.pfister@mmsag.ch wrote: Show quoted text
> > I have a Format in Excel like this #'##0.;(#'##0.);"-";_(@_) > > And the output of the program is > > ............................... > 0.00000. > 0 > _ "SFr."\ * #,##0_ ;_ "SFr."\ * \-#,##0_ ;_ "SFr."\ * "-"_ ;_ @_ > ------------------------------ > > Did I do something wrong?
Hi, You didn't do anything wrong. Excel doesn't store the format in the same way as it displays it. It the above example it has added a locale and escaped some of the characters. John. --
Question answered, I hope. Closing the issue, John. --
Subject: Abwesenheitsnotiz: [rt.cpan.org #60520] problem with 0 enterpretation
Date: Tue, 21 Sep 2010 11:17:18 +0200
To: "John McNamara via RT" <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>
From: "Pfister Andrea" <andrea.pfister [...] mmsag.ch>
Sehr geehrte Damen und Herren, besten Dank für Ihre Nachricht. Heute bin ich abwesend. Ihre E-Mail wird nicht weitergeleitet. In dringenden Fällen rufen Sie bitte unsere Hauptnummer an (Telefon 044 446 83 30). -- Dear Sir or Madam, thank you for your message. Unfortunately, i'm not available today. For immediate assistance, please contact our switchboard (+41 44 446 83 30). Your email will not be forwarded. Freundliche Grüsse | With best regards Andrea Pfister Multimedia Solutions AG Andrea Pfister | Softwareentwicklerin Dorfstr. 29 | CH-8037 Zürich | Schweiz T +41 44 446 83 30 | T direkt +41 44 446 83 15 | F +41 44 446 83 38 andrea.pfister@mmsag.ch | http://www.mmsag.ch 75-Jahre-Firmenjubiläum und ein neues Druckzentrum im Jahr 2010 - gute Gründe zum Feiern. Am Freitag, 24. September 2010, öffnen wir um 15 Uhr die Tore zur Open-House-Veranstaltung. Erfolgreiches erstes Geschäftsberichte-Symposium - Impressionen und Präsentationen. The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.