| Subject: | Bug in Spreadsheet::WriteExcel related to worksheet names that begin with a number |
| Date: | Mon, 26 Apr 2010 17:39:10 +0200 |
| To: | bug-Spreadsheet-WriteExcel [...] rt.cpan.org |
| From: | Juhász Péter <juhasz.peter [...] osski.hu> |
Dear Mr. McNamara,
Please look at the following example program that creates a simple
spreadsheet with two worksheets, one with a column of numbers, the other
with formulas that refer to the first worksheet.
This version works as expected (the correct formulas are generated):
########################################
#!/usr/bin/perl
# Example of a bug in Spreadsheet::WriteExcel related to
# worksheet names that begin with a number
use utf8;
use Spreadsheet::WriteExcel;
use strict;
# this works as expected:
my @wsnames = ('data', 'formulas');
# this triggers the bug:
#@wsnames = ('001', '002'); # uncomment this line
my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $format1 = $workbook->add_format();
$format1->set_num_format('0.0000');
# setting up two worksheets
my $worksheet = $workbook->add_worksheet($wsnames[0]);
my $worksheet2 = $workbook->add_worksheet($wsnames[1]);
# filling the first one with some numbers
foreach my $row (0..100) {
$worksheet->write_number($row, 0, cos($row/100.0), $format1);
}
# filling the second one with formulas that refer to data on the first
worksheet
foreach my $row (0..100) {
$worksheet2->write_formula($row, 0, '='.$wsnames[0].'!A'.($row+1),
$format1);
}
$workbook->close;
########################################
Now uncomment line 13 /#@wsnames = ('001', '002');/
and run it again. The formulas on the second worksheet are now wrong, in
fact all of them is "=1".
It seems that the formula parser gets confused if the formula string
begins with a digit: instead of interpreting the digit(s) as part of the
worksheet name, it extracts them as a number and discards the rest of
the string.
I hope that this is sufficient to reproduce and fix this small bug in an
otherwise indispensable module.
Péter Juhász
Additional information:
system : Ubuntu 9.10 32-bit
Perl version : 5.010000
OS name : linux
Module versions: (not all are required)
Spreadsheet::WriteExcel 2.37
Parse::RecDescent 1.94
File::Temp 0.18
OLE::Storage_Lite 0.19
IO::Stringy (not installed)
Spreadsheet::ParseExcel (not installed)
Scalar::Util 1.19
Unicode::Map (not installed)
Summary of my perl5 (revision 5 version 10 subversion 0) configuration:
Platform:
osname=linux, osvers=2.6.24-23-server,
archname=i486-linux-gnu-thread-multi
uname='linux vernadsky 2.6.24-23-server #1 smp wed apr 1 22:22:14
utc 2009 i686 gnulinux '
config_args='-Dusethreads -Duselargefiles -Dccflags=-DDEBIAN
-Dcccdlflags=-fPIC -Darchname=i486-linux-gnu -Dprefix=/usr
-Dprivlib=/usr/share/perl/5.10 -Darchlib=/usr/lib/perl/5.10
-Dvendorprefix=/usr -Dvendorlib=/usr/share/perl5
-Dvendorarch=/usr/lib/perl5 -Dsiteprefix=/usr/local
-Dsitelib=/usr/local/share/perl/5.10.0
-Dsitearch=/usr/local/lib/perl/5.10.0 -Dman1dir=/usr/share/man/man1
-Dman3dir=/usr/share/man/man3 -Dsiteman1dir=/usr/local/man/man1
-Dsiteman3dir=/usr/local/man/man3 -Dman1ext=1 -Dman3ext=3perl
-Dpager=/usr/bin/sensible-pager -Uafs -Ud_csh -Ud_ualarm -Uusesfio
-Uusenm -DDEBUGGING=-g -Doptimize=-O2 -Duseshrplib
-Dlibperl=libperl.so.5.10.0 -Dd_dosuid -des'
hint=recommended, useposix=true, d_sigaction=define
useithreads=define, usemultiplicity=define
useperlio=define, d_sfio=undef, uselargefiles=define, usesocks=undef
use64bitint=undef, use64bitall=undef, uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc', ccflags ='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN
-fno-strict-aliasing -pipe -I/usr/local/include -D_LARGEFILE_SOURCE
-D_FILE_OFFSET_BITS=64',
optimize='-O2 -g',
cppflags='-D_REENTRANT -D_GNU_SOURCE -DDEBIAN -fno-strict-aliasing
-pipe -I/usr/local/include'
ccversion='', gccversion='4.4.1', gccosandvers=''
intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=1234
d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=12
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t',
lseeksize=8
alignbytes=4, prototype=define
Linker and Libraries:
ld='cc', ldflags =' -L/usr/local/lib'
libpth=/usr/local/lib /lib /usr/lib /usr/lib64
libs=-lgdbm -lgdbm_compat -ldb -ldl -lm -lpthread -lc -lcrypt
perllibs=-ldl -lm -lpthread -lc -lcrypt
libc=/lib/libc-2.10.1.so, so=so, useshrplib=true,
libperl=libperl.so.5.10.0
gnulibc_version='2.10.1'
Dynamic Linking:
dlsrc=dl_dlopen.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E'
cccdlflags='-fPIC', lddlflags='-shared -O2 -g -L/usr/local/lib'
Characteristics of this binary (from libperl):
Compile-time options: MULTIPLICITY PERL_DONT_CREATE_GVSV
PERL_IMPLICIT_CONTEXT PERL_MALLOC_WRAP
USE_ITHREADS
USE_LARGE_FILES USE_PERLIO USE_REENTRANT_API
Built under linux
Compiled at Oct 1 2009 22:19:26
%ENV:
PERL5LIB="/usr/local/lib/R/site-library/RSPerl/perl"
@INC:
/usr/local/lib/R/site-library/RSPerl/perl/i486-linux-gnu-thread-multi
/usr/local/lib/R/site-library/RSPerl/perl
/etc/perl
/usr/local/lib/perl/5.10.0
/usr/local/share/perl/5.10.0
/usr/lib/perl5
/usr/share/perl5
/usr/lib/perl/5.10
/usr/share/perl/5.10
/usr/local/lib/site_perl