| Subject: | bug in Spreadsheet-XLSX-0.13 |
| Date: | Thu, 18 Aug 2011 11:15:22 -0400 |
| To: | "bug-Spreadsheet-XLSX [...] rt.cpan.org" <bug-Spreadsheet-XLSX [...] rt.cpan.org> |
| From: | Martin Smoot <msmoot [...] infoplanning.com> |
version: Spreadsheet-XLSX-0.13
perl -v: v5.12.2
Linux 2.6.18-194.17.1.el5PAE #1 SMP Wed Sep 29 13:31:51 EDT 2010 i686 i686 i386 GNU/Linux
Problem:
extracting a spreadsheet (in our case to a .csv) which goes beyond column ZZ causes all data that would be found beyond column ZZ (column AAA for example) to be put into column ZZ as it processes the spreadsheet so whatever is in the last column after ZZ will be put into ZZ.
I do not have a fix but have determined that the following block of code around line 160 in XLSX.pm appears to be the source of the problem:
if (/^\<c r=\"([A-Z])([A-Z]?)(\d+)\"/) {
$col = ord ($1) - 65;
if ($2) {
$col++;
$col *= 26;
$col += (ord ($2) - 65);
}
$row = $3 - 1;
$s = m/t=\"s\"/ ? 1 : 0;
$s2 = m/t=\"str\"/ ? 1 : 0;
$sty = m/s="([0-9]+)"/ ? $1 : 0;
the value of $col never changes when we go past column ZZ and remains 701.
I am attaching a sample spreadsheet that causes the problem and the .csv file that was created from the spreadsheet data using a perl based xlsx to csv conversion tool we have - here is the code used to write the data:
sub excel_process($)
{
my($iR, $iC, $oWkS, $oWkC);
my ($oBook)=@_;
# Output the specified sheet
for (my $iSheet=0; $iSheet < $oBook->{SheetCount} ; $iSheet++) {
$oWkS = $oBook->{Worksheet}[$iSheet];
for (my $iR = 0; ## was: $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];
if ($oWkC) {
my $Val=$oWkC->Value;
$Val=~ s/,//; # eliminate embedded commas
$Val=~ s/\n/ /g; # change embedded line terminators to spaces
print $Val;
}
print ","; # this is the only comma we typically want
}
print "\n";
}
}
}
This message is private and confidential. If you have received this message in error, please notify us by replying to this email and then delete it from your system. Thank you for your cooperation. Information Planning & Management Service, Inc.
Message body not shown because it is not plain text.
Message body not shown because it is not plain text.