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

Report information
The Basics
Id:
67807
Status:
rejected
Priority:
Low/Low

People
Owner:
Nobody in particular
Requestors:
leon.panokarren [...] farmersinsurance.com
Cc:
AdminCc:



Subject: Strange decimal conversion
Hi, We are using the Spreadsheet-XLSX-0.13 module for converting an Excel 2007 file into a csv file using the code on the distribution page. The problem is, occasionally, the conversion translates a decimal excel cell value to have a value which is nearly the same but not exactly. For e.g., 0.167 to 0.1670000000001. I say occasionally, as this is very erratic. This causes data processing routines that match exact values fail assignments that look for certain specific values. Is there a way we could fix this? Here are some general environment details to aid your review - 1. Distribution Name and Version: Spreadsheet-XLSX-0.13 2. Attached perl_V.txt 3. uname -amvr output: AIX laatdm62 3 5 00C396BC4C00 4. Code fragment that is producing the error - attachment CODE_FRG.txt Please let me know anything else I could provide to assist your review. Thanks for the help!
Subject: perl_V.txt
Summary of my perl5 (revision 5 version 10 subversion 1) configuration: Platform: osname=aix, osvers=5.3.0.0, archname=aix uname='aix laatdm62 3 5 00c396bc4c00 ' config_args='-de -Dcc=cc_r' hint=recommended, useposix=true, d_sigaction=define useithreads=undef, usemultiplicity=undef useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef use64bitint=undef, use64bitall=undef, uselongdouble=undef usemymalloc=n, bincompat5005=undef Compiler: cc='cc_r -q32', ccflags ='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN -q32 -D_LARGE_FILES -qlonglong', optimize='-O', cppflags='-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=-1 -qnoansialias -DUSE_NATIVE_DLOPEN' ccversion='', gccversion='', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=8 ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8 alignbytes=8, prototype=define Linker and Libraries: ld='ld', ldflags =' -brtl -bdynamic -b32 -bmaxdata:0x80000000' libpth=/lib /usr/lib /usr/ccs/lib libs=-lbind -lnsl -ldbm -ldl -lld -lm -lcrypt -lc perllibs=-lbind -lnsl -ldl -lld -lm -lcrypt -lc libc=/lib/libc.a, so=a, useshrplib=false, libperl=libperl.a gnulibc_version='' Dynamic Linking: dlsrc=dl_aix.xs, dlext=so, d_dlsymun=undef, ccdlflags=' -bE:/usr/local/lib/perl5/5.10.1/aix/CORE/perl.exp' cccdlflags=' ', lddlflags=' -bhalt:4 -G -bI:$(PERL_INC)/perl.exp -bE:$(BASEEXT).exp -bnoentry -lc -lm' Characteristics of this binary (from libperl): Compile-time options: PERL_DONT_CREATE_GVSV PERL_MALLOC_WRAP USE_LARGE_FILES USE_PERLIO Built under aix Compiled at Feb 26 2011 02:13:00 @INC: /usr/local/lib/perl5/5.10.1/aix /usr/local/lib/perl5/5.10.1 /usr/local/lib/perl5/site_perl/5.10.1/aix /usr/local/lib/perl5/site_perl/5.10.1 .
Subject: CODE_FRG.txt
$WkS -> {MaxRow} ||= $WkS -> {MinRow}; foreach $iR ($WkS -> {MinRow} .. $WkS -> {MaxRow}) { $WkS -> {MaxCol} ||= $WkS -> {MinCol}; foreach $iC ($WkS -> {MinCol} .. $WkS -> {MaxCol}) { $cell = $WkS -> {Cells} [$iR] [$iC]; if ($cell) { print OUT Spreadsheet::XLSX::Utility2007::unescape_HTML($cell -> {Val}); } if ( ( $iR == ${hdr_seq} - 1 ) ) { $cfgrec .= Spreadsheet::XLSX::Utility2007::unescape_HTML($cell -> {Val}); } if ( ( $iC == 0 ) && ( $iR == ${hdr_seq} ) ) { $cfgrec = uc ( $cfgrec ); $cfgko = cnt_ocr ( $cfgrec, $keyhdr ); $cfgkey = "*," x ( $klm - $cfgko ); } print OUT "," if ( $iC < $WkS->{MaxCol} ); print OUT $cfgkey if ( ($cfgko == $iC + 1) && ($iR >= $hdrcnt) ); } print OUT "\n"; } print LOG tmstmp() . ": Worsheet conversion completed successfully ----- " . $WkS->{Name}, "\n";
On Wed Apr 27 22:19:07 2011, ETLTCHFIG wrote:
Show quoted text
> problem is, occasionally, the conversion translates a decimal excel > cell value to have a value which is nearly the same but not exactly. > For e.g., 0.167 to 0.1670000000001. I say occasionally, as this is
This is standard for all floating number comparisions in all programming languages (you can google for it). E.g.: my $nbr = 1.67/10; my $nbr2 = .167; if ( $nbr != $nbr2 ) { print "Not equal\n"; } Use sprintf to round to the desired precision.
On Mon Aug 01 15:47:17 2011, DOUGW wrote:
Show quoted text
> On Wed Apr 27 22:19:07 2011, ETLTCHFIG wrote: >
> > problem is, occasionally, the conversion translates a decimal excel > > cell value to have a value which is nearly the same but not exactly. > > For e.g., 0.167 to 0.1670000000001. I say occasionally, as this is
> > This is standard for all floating number comparisions in all programming > languages (you can google for it). E.g.: > my $nbr = 1.67/10; > > my $nbr2 = .167; > > if ( $nbr != $nbr2 ) { > print "Not equal\n"; > } > > Use sprintf to round to the desired precision. >
...and then do string comparision with 'eq'
CC: Nagaraj Pithana <nagaraj.pithana@farmersinsurance.com>, Shreyas Shah <shreyas.shah@farmersinsurance.com>
Subject: Re: [rt.cpan.org #67807] Strange decimal conversion
Date: Mon, 1 Aug 2011 14:46:37 -0700
To: bug-Spreadsheet-XLSX@rt.cpan.org
From: Leon Panokarren <leon.panokarren@farmersinsurance.com>

Hi Doug,

I very much appreciate you getting back! We figured the problem is with Excel 2007. The spreadsheet that had the value of 0.167 was create by another PERL module (author: John McNamara) that is sent to a Windows box through FTP. Once on Windows, the file is opened by a desktop user, modified and then sent back to Unix where it is converted back to a series of CSVs. We determined that even if the user changes a whole another cell (or maybe just add a row), Excel can randomly distort numbers (like change 0.167 to 0.16700000000001). The PERL module to convert excel to csv was merely manifesting a symptom of the original problem. I confirmed this by

1. Saving the Excel coming into Windows as a .zip file
2. Uncompressing it
3. Examining the uncompressed xmls before having the desktop user modify the spreadsheet
4. having the desktop user modify the spreadsheet
5. Examining the uncompressed xml again

At this time, we believe this is a MS problem - unless you think otherwise, or have a solution for this problem. Let me know what you think.

Team,

FYI - See CPAN's response below

Thanks!

Leon Panokarren
Work: 714 - 385 - 3793
Cell:   732 - 692 - 4110



"Douglas Wilson via RT" <bug-Spreadsheet-XLSX@rt.cpan.org>

08/01/2011 12:49 PM

Please respond to
bug-Spreadsheet-XLSX@rt.cpan.org

To
leon.panokarren@farmersinsurance.com
cc
Subject
[rt.cpan.org #67807] Strange decimal conversion






<URL: https://rt.cpan.org/Ticket/Display.html?id=67807 >

On Mon Aug 01 15:47:17 2011, DOUGW wrote:
> On Wed Apr 27 22:19:07 2011, ETLTCHFIG wrote:
>
> > problem is, occasionally, the conversion translates a decimal excel
> > cell value to have a value which is nearly the same but not exactly.
> > For e.g., 0.167 to 0.1670000000001. I say occasionally, as this is
>
> This is standard for all floating number comparisions in all programming
> languages (you can google for it). E.g.:
> my $nbr = 1.67/10;
>
> my $nbr2 = .167;
>
> if ( $nbr != $nbr2 ) {
>   print "Not equal\n";
> }
>
> Use sprintf to round to the desired precision.
>
...and then do string comparision with 'eq'



This is a known issue with floating point numbers, not an issue specific to Spreadsheet::XLSX.


This service runs on Request Tracker, is sponsored by The Perl Foundation, and maintained by Best Practical Solutions.

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