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



From sleighton [...] gmail.com Mon Mar 3 18: 54:59 2014
MIME-Version: 1.0
In-Reply-To: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com>
X-Spam-Status: No, score=-2.698 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7] autolearn=ham
X-Spam-Flag: NO
X-Virus-Checked: Checked
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com>
Content-Type: multipart/mixed; boundary="047d7b86e9ca32028604f3bc83aa"
Message-ID: <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com>
X-Received: by 10.194.57.239 with SMTP id l15mr22599562wjq.40.1393890887330; Mon, 03 Mar 2014 15:54:47 -0800 (PST)
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Spam-Score: -2.698
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 6F1122401A2 for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Mon, 3 Mar 2014 18:54:59 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id rYBBFlEFcc2b for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Mon, 3 Mar 2014 18:54:57 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id BC6172400D4 for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Mon, 3 Mar 2014 18:54:56 -0500 (EST)
Received: (qmail 31882 invoked by alias); 3 Mar 2014 23:54:56 -0000
Received: from mail-wg0-f46.google.com (HELO mail-wg0-f46.google.com) (74.125.82.46) by la.mx.develooper.com (qpsmtpd/0.28) with ESMTP; Mon, 03 Mar 2014 15:54:52 -0800
Received: by mail-wg0-f46.google.com with SMTP id z12so4137381wgg.29 for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Mon, 03 Mar 2014 15:54:47 -0800 (PST)
Received: by 10.194.1.137 with HTTP; Mon, 3 Mar 2014 15:54:47 -0800 (PST)
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] gmail.com
Delivered-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
Subject: Fwd: Unexpected [RE] included in Cell Contents
Return-Path: <sleighton [...] gmail.com>
X-RT-Mail-Extension: spreadsheet-parseexcel
X-Original-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
X-Spam-Check-BY: la.mx.develooper.com
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=vx81hNs87xEwtf44dIOaP309BskpPDr7eayKEJI9arQ=; b=UvGsSOXhfgNr9gCuMVHGXVcEWHioaw5jbfbhLXSwysdV4Un/qxX5D8u330YB3dwLA4 gDhcdyinG3veLGffyo459F9SYQK9b9PqwxbBZ6DcRohR6fLkCQGWN0nFTWyP4CXyO65D trg/a4Zzky2sMVhVx7gWvFKF2I8OXGd28OOrhAH5IYrP00gOtfwFU9dL3cgk3rIZ2T1u g2UyOy2j/QndWPJ1yeSljTb3WCBgM3oEO+RJ2nvy/hRay0pGSqg1O0ikI7KzwvWrhNWO XfEHe+il54BNkn1MZ2cDsyzncoQrxgvohCFxwPmFPNE/fFgTthK54lKl/DuCvv31ELeN Ow7A==
Date: Mon, 3 Mar 2014 15:54:47 -0800
X-Spam-Level:
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
X-RT-Interface: Email
Content-Length: 0
Content-Type: multipart/alternative; boundary="047d7b86e9ca32028304f3bc83a8"
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: iso-8859-1
Content-Length: 5248
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>
content-type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: iso-8859-1
Content-Length: 15015
Download (untitled) / with headers
text/html 14.6k

Message body is not shown because it is too large.

X-Attachment-ID: a4ba6ce17e22d0ec_0.1
Content-Type: application/vnd.ms-excel; name="testcase.xls"
Content-Disposition: attachment; filename="testcase.xls"
Content-Transfer-Encoding: base64
Content-Length: 44544
Download testcase.xls
application/vnd.ms-excel 43.5k

Message body not shown because it is not plain text.

MIME-Version: 1.0
In-Reply-To: <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-31593-1393899932-149.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 464
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 :-)
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-31593-1393899932-149.93500-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-31593-1393899932-149.93500-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-9163-1393910873-1301.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 502
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.
From sleighton [...] gmail.com Tue Mar 4 08: 59:15 2014
MIME-Version: 1.0
X-Spam-Status: No, score=-2.568 tagged_above=-99.9 required=10 tests=[AWL=-0.130, BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, HTML_OBFUSCATE_05_10=0.26, RCVD_IN_DNSWL_LOW=-0.7] autolearn=ham
In-Reply-To: <rt-4.0.18-31593-1393899932-1093.93500-6-0 [...] rt.cpan.org>
X-Spam-Flag: NO
X-RT-Interface: API
References: <RT-Ticket-93500 [...] rt.cpan.org> <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-31593-1393899932-1093.93500-6-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Received: by 10.194.2.194 with SMTP id 2mr16133002wjw.73.1393941545661; Tue, 04 Mar 2014 05:59:05 -0800 (PST)
Message-ID: <CAOdSa=dTrTShRHZ5e5EWvu8TF6kC4azFJhDxN2x0fqSxqx9ovA [...] mail.gmail.com>
Content-Type: multipart/alternative; boundary="047d7b3a86f0aaba8104f3c84e0d"
X-Spam-Score: -2.568
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] gmail.com
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 9907B24049C for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Tue, 4 Mar 2014 08:59:15 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id As3NHNnK3M5X for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Tue, 4 Mar 2014 08:59:14 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 05392240288 for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Tue, 4 Mar 2014 08:59:13 -0500 (EST)
Received: (qmail 24773 invoked by alias); 4 Mar 2014 13:59:13 -0000
Received: from mail-wg0-f51.google.com (HELO mail-wg0-f51.google.com) (74.125.82.51) by la.mx.develooper.com (qpsmtpd/0.28) with ESMTP; Tue, 04 Mar 2014 05:59:10 -0800
Received: by mail-wg0-f51.google.com with SMTP id a1so4927784wgh.34 for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Tue, 04 Mar 2014 05:59:05 -0800 (PST)
Received: by 10.194.1.137 with HTTP; Tue, 4 Mar 2014 05:59:05 -0800 (PST)
Delivered-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
Subject: Re: [rt.cpan.org #93500] Fwd: Unexpected [RE] included in Cell Contents
Return-Path: <sleighton [...] gmail.com>
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=48JfqwKf6O10pKm7C6oP6JCvmWbw9oRLqWjz12e7iSM=; b=ZzFcay3E2BI/OYG2fciZToNzcF0w8miKtESfx5L2+cMrZEk6K9kQGI3+6QUlZ844sq MK0x8L6EQj2cXd+ry73ZJOUQ+1j3EHdYB9L+1khQNatEgO7WxF8GMlMnlX0seeZPbUsr 77/yZGXBo1Siwiskoe2kTHoz7FVW6kbHQP537ky4mSl9avIO2lliAKIsPv4Lr52eHTSk ZvEDbysTkzBI3KVoF2Ng37eIUywr7SMUmqrRJbrfQOG4DcMlKMbqaNR1YOe5n++AVl+O 2+lC2m7OG3IrxFUnifxDk42oMo6UpFN8y/dohBMR7QGCSN48tZP+3BUxpX+JJOGnvncC xBCg==
X-Spam-Check-BY: la.mx.develooper.com
X-Original-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
X-RT-Mail-Extension: spreadsheet-parseexcel
Date: Tue, 4 Mar 2014 05:59:05 -0800
X-Spam-Level:
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
RT-Message-ID: <rt-4.0.18-26324-1393941556-813.93500-0-0 [...] rt.cpan.org>
Content-Length: 0
content-type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
Content-Length: 1362
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>
content-type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: utf-8
Content-Length: 2391
MIME-Version: 1.0
In-Reply-To: <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-4541-1393950032-677.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 675
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-9163-1393910873-1301.93500-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-31593-1393899932-149.93500-0-0 [...] rt.cpan.org> <rt-4.0.18-9163-1393910873-1301.93500-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-29963-1393950220-1157.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 403
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
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-4541-1393950032-677.93500-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-4541-1393950032-677.93500-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-4541-1393952779-1367.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 576
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.
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-4541-1393952779-1367.93500-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-4541-1393950032-677.93500-0-0 [...] rt.cpan.org> <rt-4.0.18-4541-1393952779-1367.93500-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-7097-1393953769-233.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 606
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. --
From sleighton [...] gmail.com Tue Mar 4 13: 56:14 2014
MIME-Version: 1.0
X-Spam-Status: No, score=-2.503 tagged_above=-99.9 required=10 tests=[AWL=-0.065, BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, HTML_OBFUSCATE_05_10=0.26, RCVD_IN_DNSWL_LOW=-0.7] autolearn=ham
In-Reply-To: <rt-4.0.18-7097-1393953769-1531.93500-6-0 [...] rt.cpan.org>
X-Spam-Flag: NO
X-RT-Interface: API
References: <RT-Ticket-93500 [...] rt.cpan.org> <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-4541-1393950032-677.93500-6-0 [...] rt.cpan.org> <rt-4.0.18-4541-1393952779-1367.93500-6-0 [...] rt.cpan.org> <rt-4.0.18-7097-1393953769-1531.93500-6-0 [...] rt.cpan.org>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Received: by 10.194.85.168 with SMTP id i8mr1038872wjz.81.1393955447395; Tue, 04 Mar 2014 09:50:47 -0800 (PST)
Message-ID: <CAOdSa=fRGHx0NFbXu5=DdUeb1ND4GbUc8UtDhrCKCemgJ0+ddg [...] mail.gmail.com>
Content-Type: multipart/alternative; boundary="089e010d7efc465ebf04f3cb8be6"
X-Spam-Score: -2.503
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] gmail.com
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 925B724062B for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Tue, 4 Mar 2014 13:56:14 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id MW4UqlUEDoeE for <cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com>; Tue, 4 Mar 2014 13:56:12 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id CA3D82400AC for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Tue, 4 Mar 2014 13:56:11 -0500 (EST)
Received: (qmail 18090 invoked by alias); 4 Mar 2014 18:56:11 -0000
Received: from mail-wg0-f44.google.com (HELO mail-wg0-f44.google.com) (74.125.82.44) by la.mx.develooper.com (qpsmtpd/0.28) with ESMTP; Tue, 04 Mar 2014 10:56:04 -0800
Received: by mail-wg0-f44.google.com with SMTP id a1so5550477wgh.27 for <bug-Spreadsheet-ParseExcel [...] rt.cpan.org>; Tue, 04 Mar 2014 10:56:00 -0800 (PST)
Received: by 10.194.1.137 with HTTP; Tue, 4 Mar 2014 09:50:47 -0800 (PST)
Delivered-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
Subject: Re: [rt.cpan.org #93500] Fwd: Unexpected [RE] included in Cell Contents
Return-Path: <sleighton [...] gmail.com>
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :content-type; bh=13HbVT3sVEuii9eJ1CuSdRXhUIstBd24RhoR+bcin84=; b=DBDIe58d66obtMKUCsTunQ640CVf9ijOUzn97oS5TgUWEkEJ6cvHfVFlnHsPlwxs7R H80GTrrAA6dRFFpZB2dWUyV0Y5r/J1OLNwCeDvE+ulVsBpJzTvXaCs6+JqXNZ2iNXTlE OQzT6WtsF5VUEn9bdgwj2fUxo6rHqqw1BQrWhkmdkDuJ1lVlxc7lrSU4cSlQem0b5qIW JQtgy2W0HrwQS8HX3DLJUxzD+Yywem9EbssRwiUhW/w4xmkJwppiFC36FZsxcKpz16d6 y1UlXBqkYWq9D84bEjzG7Kc1/c0jqLedXP8RZoKgmc6gYXCM5PANebBA3KiF7y2FB+Zd h2Wg==
X-Spam-Check-BY: la.mx.develooper.com
X-Original-To: cpan-bug+Spreadsheet-ParseExcel [...] hipster.bestpractical.com
X-RT-Mail-Extension: spreadsheet-parseexcel
Date: Tue, 4 Mar 2014 09:50:47 -0800
X-Spam-Level:
To: bug-Spreadsheet-ParseExcel [...] rt.cpan.org
From: Scott Leighton <sleighton [...] gmail.com>
RT-Message-ID: <rt-4.0.18-5975-1393959375-1400.93500-0-0 [...] rt.cpan.org>
Content-Length: 0
content-type: text/plain; charset="utf-8"
X-RT-Original-Encoding: utf-8
Content-Length: 1231
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>
content-type: text/html; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: utf-8
Content-Length: 2390
MIME-Version: 1.0
In-Reply-To: <rt-4.0.18-5975-1393959375-1400.93500-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.504 (Entity 5.504)
Content-Disposition: inline
X-RT-Interface: Web
References: <RT-Ticket-93500 [...] rt.cpan.org> <CD1ACB5C023CF348AB3A20225E436CBF01120280 [...] IRSVRMX.helpmatesstaffing.com> <CAOdSa=fjqRg1Y_cHC2uTNUBPH1=tAPi3L-YP8VPL-Qe2wUrK5Q [...] mail.gmail.com> <rt-4.0.18-4541-1393950032-677.93500-6-0 [...] rt.cpan.org> <rt-4.0.18-4541-1393952779-1367.93500-6-0 [...] rt.cpan.org> <rt-4.0.18-7097-1393953769-1531.93500-6-0 [...] rt.cpan.org> <CAOdSa=fRGHx0NFbXu5=DdUeb1ND4GbUc8UtDhrCKCemgJ0+ddg [...] mail.gmail.com> <rt-4.0.18-5975-1393959375-1400.93500-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="utf-8"
Message-ID: <rt-4.0.18-15571-1393977505-796.93500-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
X-RT-Encrypt: 0
X-RT-Sign: 0
Content-Length: 178
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.