| 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> |
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>
Message body is not shown because it is too large.
Message body not shown because it is not plain text.