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

People
Owner: Nobody in particular
Requestors: sleighton [...] gmail.com
Cc:
AdminCc:

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



Subject: Fwd: Unexpected [RE] included in Cell Contents
Date: Mon, 3 Mar 2014 15:54:47 -0800
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
Download (untitled) / with headers
text/plain 5.1k
Current Setup Perl version : 5.008006 OS name : linux Module versions: (not all are required) Spreadsheet::ParseExcel 0.60 Scalar::Util 1.14 Unicode::Map 0.112 Spreadsheet::WriteExcel 2.40 Parse::RecDescent 1.80 File::Temp 0.22 OLE::Storage_Lite 0.19 IO::Stringy 2.109 Excel 2010 Version 14.0.7113.5005 (32-bit) Origin of Sheet: Unknown, originally created with much earlier version of Excel. Issue: Note sure if this is a bug or just user error/misunderstanding. I have a spreadsheet I am parsing that has certain cells in it that contain numeric data and when viewed in excel look just like any other numeric cell in the sheet, yet the Value returned by Spreadsheet::ParseExcel is mangled with the text [RE] (see example below). I have checked the formatting of these cells in Excel and they appear to be the same as cells that do not return the mangled text. Note that the [RE] sometimes has the minus sign before the ending brace [RE-] and sometimes includes the very first digit of the number in the cell before the ending brace [RE-1]. No clue why this pattern happens. When viewed in Excel the [RE] is not in the cell and not in the format for the cell. I am stumped about where the unwanted characters are coming from and would appreciate any help figuring it out. Example: testcase.xls sheet is attached. Running this simple script copied from an earlier bug report produces the output shown below. [SCRIPT] #!/usr/bin/perl use strict; use Spreadsheet::ParseExcel; my $oExcel = new Spreadsheet::ParseExcel; my $oBook = $oExcel->Parse($ARGV[0]); my($iR, $iC,$oWkS, $oWkC); print "=========================================\n"; print "FILE :", $oBook->{File} , "\n"; print "COUNT :", $oBook->{SheetCount} , "\n"; print "AUTHOR:", $oBook->{Author} , "\n"; foreach my $oWkS (@{$oBook->{Worksheet}}) { print "--------- SHEET:", $oWkS->{Name}, "\n"; for(my $iR = $oWkS->{MinRow} ; defined $oWkS->{MaxRow} && $iR <= $oWkS->{ MaxRow} ; $iR++) { for(my $iC = $oWkS->{MinCol} ; defined $oWkS->{MaxCol} && $iC <= $oWkS-> {MaxCol} ; $iC++) { $oWkC = $oWkS->{Cells}[$iR][$iC]; print "( $iR , $iC ) =>", $oWkC->Value, "\n" if($oWkC); } } } [OUTPUT] ========================================= FILE :testcase.xls COUNT :1 AUTHOR:Scott Leighton --------- SHEET:COS difference ( 0 , 0 ) =>5110-01 ( 0 , 1 ) =>Computed 1 ( 0 , 3 ) =>[RE]-539 ( 0 , 4 ) =>[RE]-568 ( 0 , 5 ) =>[RE-]1,457 ( 0 , 6 ) =>[RE-]1,704 ( 0 , 7 ) =>[RE-]2,868 ( 0 , 8 ) =>[RE-]5,485 ( 0 , 9 ) =>[RE-]6,513 ( 0 , 10 ) =>[RE-]9,096 ( 0 , 11 ) =>[RE-1]4,302 ( 0 , 12 ) =>[RE-1]4,897 ( 0 , 13 ) =>[RE-1]7,320 ( 0 , 14 ) =>[RE-1]9,984 ( 0 , 15 ) =>-94,733 ( 1 , 3 ) => ( 1 , 4 ) => ( 1 , 5 ) => ( 1 , 6 ) => ( 1 , 7 ) => ( 1 , 8 ) => ( 1 , 9 ) => ( 1 , 10 ) => ( 1 , 11 ) => ( 1 , 12 ) => ( 1 , 13 ) => ( 1 , 14 ) => ( 1 , 15 ) => ( 2 , 0 ) =>5120-01 ( 2 , 1 ) =>Computed 2 ( 2 , 3 ) =>[RE]-11 ( 2 , 4 ) =>[RE]-11 ( 2 , 5 ) =>[RE]-28 ( 2 , 6 ) =>[RE]-33 ( 2 , 7 ) =>[RE]-64 ( 2 , 8 ) =>[RE]-128 ( 2 , 9 ) =>[RE]-152 ( 2 , 10 ) =>[RE]-222 ( 2 , 11 ) =>[RE]-354 ( 2 , 12 ) =>[RE]-377 ( 2 , 13 ) =>[RE]-452 ( 2 , 14 ) =>[RE]-522 ( 2 , 15 ) =>-2,354 ( 3 , 0 ) =>5120-01 ( 3 , 1 ) =>Computed 3 ( 3 , 3 ) =>1 ( 3 , 4 ) =>40 ( 3 , 5 ) =>33 ( 3 , 6 ) =>29 ( 3 , 7 ) =>8 ( 3 , 8 ) =>[RE]-27 ( 3 , 9 ) =>[RE]-19 ( 3 , 10 ) =>[RE]-60 ( 3 , 11 ) =>[RE]-149 ( 3 , 12 ) =>[RE]-155 ( 3 , 13 ) =>[RE]-249 ( 3 , 14 ) =>[RE]-197 ( 3 , 15 ) =>-745 ( 4 , 0 ) =>5140-01 ( 4 , 1 ) =>Computed 4 ( 4 , 3 ) =>[RE]-8 ( 4 , 4 ) =>[RE]-9 ( 4 , 5 ) =>[RE]-22 ( 4 , 6 ) =>[RE]-26 ( 4 , 7 ) =>[RE]-44 ( 4 , 8 ) =>[RE]-83 ( 4 , 9 ) =>[RE]-99 ( 4 , 10 ) =>[RE]-138 ( 4 , 11 ) =>[RE]-217 ( 4 , 12 ) =>[RE]-226 ( 4 , 13 ) =>[RE]-263 ( 4 , 14 ) =>[RE]-303 ( 4 , 15 ) =>-1,438 ( 5 , 0 ) =>5151-01 ( 5 , 1 ) =>Computed 5 ( 5 , 3 ) =>[RE]-1 ( 5 , 4 ) =>[RE]-1 ( 5 , 5 ) =>[RE]-2 ( 5 , 6 ) =>[RE]-3 ( 5 , 7 ) =>[RE]-5 ( 5 , 8 ) =>[RE]-8 ( 5 , 9 ) =>[RE]-11 ( 5 , 10 ) =>[RE]-14 ( 5 , 11 ) =>[RE]-22 ( 5 , 12 ) =>[RE]-24 ( 5 , 13 ) =>[RE]-27 ( 5 , 14 ) =>[RE]-32 ( 5 , 15 ) =>-150 ( 6 , 0 ) =>5150-01 ( 6 , 1 ) =>Computed 6 ( 6 , 3 ) =>[RE]-3 ( 6 , 4 ) =>[RE]-3 ( 6 , 5 ) =>[RE]-8 ( 6 , 6 ) =>[RE]-9 ( 6 , 7 ) =>[RE]-16 ( 6 , 8 ) =>[RE]-31 ( 6 , 9 ) =>[RE]-36 ( 6 , 10 ) =>[RE]-51 ( 6 , 11 ) =>[RE]-81 ( 6 , 12 ) =>[RE]-84 ( 6 , 13 ) =>[RE]-99 ( 6 , 14 ) =>[RE]-113 ( 6 , 15 ) =>-534 ( 7 , 3 ) => ( 7 , 4 ) => ( 7 , 5 ) => ( 7 , 6 ) => ( 7 , 7 ) => ( 7 , 8 ) => ( 7 , 9 ) => ( 7 , 10 ) => ( 7 , 11 ) => ( 7 , 12 ) => ( 7 , 13 ) => ( 7 , 14 ) => ( 7 , 15 ) => Thanks, Scott Leighton ­­ -- G+ <https://www.google.com/+ScottLeighton_GPlus> | LinkedIn<http://www.linkedin.com/in/scottleighton> | Facebook <http://www.facebook.com/sleighton> | @NotSsoFAQs<https://twitter.com/NotSoFAQs> | About Me <http://about.me/sleighton>
Download (untitled) / with headers
text/html 14.6k

Message body is not shown because it is too large.

Download testcase.xls
application/vnd.ms-excel 43.5k

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 464b
It all comes down to this case: use Spreadsheet::ParseExcel::Utility qw(ExcelFmt); print ExcelFmt('(#,##0_);[RED](#,##0)', -539, 0, 'Numeric'), "\n"; Now that's a nasty function, so I don't know when I can take a closer look at it. And maybe it has something partly to do with the %hFmtDefault hash in FmtDefault.pm. I copied the fmt string as displayed in Excel, and ExcelFmt still didn't work ( '#,##0_);[Red](#,##0)' ). Feel free to look closer yourself :-)
Download (untitled) / with headers
text/plain 502b
On Mon Mar 03 21:25:32 2014, DOUGW wrote: Show quoted text
> > '#,##0_);[Red](#,##0)'
Actually, if the format in the global array is replaced by that, then the numbers come out as normal negative (commafied) integers. I'm don't think ExcelFmt is supposed to put parens around negative numbers even if the format says so. The docs on ExcelFmt are not helpful here, as it only mentions usage for date values. I'm a little confused here, so if John or someone pipes up on what is supposed to happen, that would be great.
Subject: Re: [rt.cpan.org #93500] Fwd: Unexpected [RE] included in Cell Contents
Date: Tue, 4 Mar 2014 05:59:05 -0800
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
Download (untitled) / with headers
text/plain 1.3k
Thank you for the explanation! I control the sheets so it is easy enough to change the format of the problem cells as a workaround. To anyone else encountering this problem, a second workaround is to save the sheets in xlsx file format and use Spreadsheet::ParseXLSX as the parser (it is a wrapper for this module, so a drop in to your code). That worked too (my guess is that Excel makes some subtle change to the format when it changes to the xlsx file format). On Mon, Mar 3, 2014 at 6:25 PM, Douglas Wilson via RT < bug-Spreadsheet-ParseExcel@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=93500 > > > > It all comes down to this case: > > use Spreadsheet::ParseExcel::Utility qw(ExcelFmt); > print ExcelFmt('(#,##0_);[RED](#,##0)', -539, 0, 'Numeric'), "\n"; > > Now that's a nasty function, so I don't know when I can take a closer look > at it. And maybe it has something partly to do with the %hFmtDefault hash > in FmtDefault.pm. I copied the fmt string as displayed in Excel, and > ExcelFmt still didn't work ( '#,##0_);[Red](#,##0)' ). > > Feel free to look closer yourself :-) >
-- G+ <https://www.google.com/+ScottLeighton_GPlus> | LinkedIn<http://www.linkedin.com/in/scottleighton> | Facebook <http://www.facebook.com/sleighton> | @NotSsoFAQs<https://twitter.com/NotSoFAQs> | About Me <http://about.me/sleighton>
Download (untitled) / with headers
text/plain 675b
On Mon Mar 03 18:55:00 2014, sleighton@gmail.com wrote: Hi Scott, The issue is that the cell format contain a format with "[RED]" in it. While Excel accepts that, it should strictly be "[Red]" (Sentence case). If you changed that in your input file it should be parsed correctly. Alternatively, in Spreadsheet::ParseExcel::Utility.pm you can change the parse from: my $color = ''; if ( $format =~ s/^(\[[A-Z][a-z]{2,}(\d{1,2})?\])// ) { $color = $1; } To: my $color = ''; if ( $format =~ s/^(\[[A-Za-z]{3,}(\d{1,2})?\])// ) { $color = $1; } Then it will handle either format. Doug, that may be a change worth making. John
Download (untitled) / with headers
text/plain 403b
On Tue Mar 04 00:27:53 2014, DOUGW wrote: The docs on ExcelFmt are not helpful here, Show quoted text
> as it only mentions usage for date values. I'm a little confused here, > so if John or someone pipes up on what is supposed to happen, that > would be great.
Hi Doug, You should have seen it before I clean it up. :-) http://cpansearch.perl.org/src/KWITKNR/Spreadsheet-ParseExcel-0.25/ParseExcel/Utility.pm John
Download (untitled) / with headers
text/plain 576b
On Tue Mar 04 11:20:32 2014, JMCNAMARA wrote: Show quoted text
> On Mon Mar 03 18:55:00 2014, sleighton@gmail.com wrote: > > If you changed that in your input file it should be parsed correctly. >
Actually, I don't think he has that choice, as the value comes from the global %hFmtDefault (index 0x26) which has a few 'RED' entries. Those should probably be changed to 'Red' I guess, and your other suggested change is probably worth making also. Scott: as for a workaround, you should probably be using the unformatted value anyway, unless you really need the commafied formatted number.
Download (untitled) / with headers
text/plain 606b
On Tue Mar 04 12:06:19 2014, DOUGW wrote: Show quoted text
> Actually, I don't think he has that choice, as the value comes from > the global %hFmtDefault (index 0x26) which has a few 'RED' entries. > Those should probably be changed to 'Red' I guess, and your other > suggested change is probably worth making also.
Hi Doug, Good point, I missed that. In that case it is probably worth changing %hFmtDefault. Show quoted text
> Scott: as for a workaround, you should probably be using the > unformatted value anyway, unless you really need the commafied > formatted number.
Agreed. Overall that is the safest approach. John. --
Subject: Re: [rt.cpan.org #93500] Fwd: Unexpected [RE] included in Cell Contents
Date: Tue, 4 Mar 2014 09:50:47 -0800
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
Download (untitled) / with headers
text/plain 1.2k
Thanks guys. Took your advice and switched to using the unformatted value and all is well. Again, I really appreciate your help solving this, I am in awe of you guys ;) Scott On Tue, Mar 4, 2014 at 9:22 AM, John McNamara via RT < bug-Spreadsheet-ParseExcel@rt.cpan.org> wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=93500 > > > On Tue Mar 04 12:06:19 2014, DOUGW wrote:
> > Actually, I don't think he has that choice, as the value comes from > > the global %hFmtDefault (index 0x26) which has a few 'RED' entries. > > Those should probably be changed to 'Red' I guess, and your other > > suggested change is probably worth making also.
> > Hi Doug, > > Good point, I missed that. > > In that case it is probably worth changing %hFmtDefault. > >
> > Scott: as for a workaround, you should probably be using the > > unformatted value anyway, unless you really need the commafied > > formatted number.
> > Agreed. Overall that is the safest approach. > > John. > -- > > > > >
-- G+ <https://www.google.com/+ScottLeighton_GPlus> | LinkedIn<http://www.linkedin.com/in/scottleighton> | Facebook <http://www.facebook.com/sleighton> | @NotSsoFAQs<https://twitter.com/NotSoFAQs> | About Me <http://about.me/sleighton>
Download (untitled) / with headers
text/plain 178b
On Tue Mar 04 13:56:15 2014, sleighton@gmail.com wrote: Show quoted text
> Thanks guys. Took your advice and switched to using the unformatted value
Fixed in 0.61 anyway :-) closing this ticket.


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.