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

Report information
The Basics
Id:
70341
Status:
new
Priority:
Low/Low

People
Owner:
Nobody in particular
Requestors:
msmoot [...] infoplanning.com
Cc:
AdminCc:



From msmoot@infoplanning.com Thu Aug 18 11: 12:48 2011
MIME-Version: 1.0
X-Spam-Status: No, score=-6.9 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, RCVD_IN_DNSWL_HI=-5] autolearn=ham
X-Spam-Flag: NO
Acceptlanguage: en-US
Content-Language: en-US
Content-Type: multipart/mixed; boundary="_003_B1FBC51F6C281847AA57FCAA189606E74A2EC499CAMSEXCHANGE01i_"
Message-ID: <B1FBC51F6C281847AA57FCAA189606E74A2EC499CA@MSEXCHANGE01.infoplanning.local>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-MS-Tnef-Correlator:
X-Spam-Score: -6.9
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id A2B8C61E008 for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Thu, 18 Aug 2011 11:12:48 -0400 (EDT)
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 FnfW990qlz34 for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Thu, 18 Aug 2011 11:12:47 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 1900461E007 for <bug-Spreadsheet-XLSX@rt.cpan.org>; Thu, 18 Aug 2011 11:12:45 -0400 (EDT)
Received: (qmail 3970 invoked by uid 103); 18 Aug 2011 15:12:45 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 18 Aug 2011 15:12:45 -0000
Received: from Remote.infoplanning.com (HELO remote.infoplanning.com) (216.246.121.51) by 16.mx.develooper.com (qpsmtpd/0.80/v0.80-19-gf52d165) with ESMTP; Thu, 18 Aug 2011 08:12:41 -0700
Received: from MSEXCHANGE01.infoplanning.local (192.168.70.15) by remote.infoplanning.com (192.168.70.1) with Microsoft SMTP Server (TLS) id 8.1.393.1; Thu, 18 Aug 2011 11:15:23 -0400
Received: from MSEXCHANGE01.infoplanning.local ([192.168.70.15]) by MSEXCHANGE01.infoplanning.local ([192.168.70.15]) with mapi; Thu, 18 Aug 2011 11:15:23 -0400
Delivered-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
Subject: bug in Spreadsheet-XLSX-0.13
Return-Path: <msmoot@infoplanning.com>
X-RT-Mail-Extension: spreadsheet-xlsx
X-Original-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
X-Spam-Check-BY: 16.mx.develooper.com
Thread-Index: AcxduaYoCFTEnVTWS2WuaY89ZlmdbQ==
Date: Thu, 18 Aug 2011 11:15:22 -0400
X-Spam-Level:
X-MS-Has-Attach: yes
Thread-Topic: bug in Spreadsheet-XLSX-0.13
Accept-Language: en-US
To: "bug-Spreadsheet-XLSX@rt.cpan.org" <bug-Spreadsheet-XLSX@rt.cpan.org>
From: Martin Smoot <msmoot@infoplanning.com>
Content-Length: 0
content-type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
X-RT-Original-Encoding: us-ascii
Content-Length: 2705
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.
Content-Description: demoss.xlsx
content-type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet; name="demoss.xlsx"
content-disposition: attachment; creation-date="Thu, 18 Aug 2011 11:08:55 GMT"; filename="demoss.xlsx"; modification-date="Thu, 18 Aug 2011 10:55:10 GMT"; size="77592"
Content-Transfer-Encoding: base64
Content-Length: 77592

Message body not shown because it is not plain text.

Content-Description: newcsv.csv
content-type: application/octet-stream; name="newcsv.csv"
content-disposition: attachment; creation-date="Thu, 18 Aug 2011 11:08:55 GMT"; filename="newcsv.csv"; modification-date="Thu, 18 Aug 2011 11:02:20 GMT"; size="28542"
Content-Transfer-Encoding: base64
Content-Length: 28542

Message body not shown because it is not plain text.

From msmoot@infoplanning.com Thu Aug 18 12: 36:45 2011
MIME-Version: 1.0
X-Spam-Status: No, score=-6.9 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, RCVD_IN_DNSWL_HI=-5] autolearn=ham
In-Reply-To: <rt-3.8.HEAD-2954-1313680370-1407.70341-3-0@rt.cpan.org>
X-Spam-Flag: NO
Acceptlanguage: en-US
References: <RT-Ticket-70341@rt.cpan.org> <B1FBC51F6C281847AA57FCAA189606E74A2EC499CA@MSEXCHANGE01.infoplanning.local> <rt-3.8.HEAD-2954-1313680370-1407.70341-3-0@rt.cpan.org>
Content-Language: en-US
X-Virus-Checked: Checked by ClamAV on 16.mx.develooper.com
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
Message-ID: <B1FBC51F6C281847AA57FCAA189606E74A2EC499ED@MSEXCHANGE01.infoplanning.local>
Content-Type: multipart/mixed; boundary="_003_B1FBC51F6C281847AA57FCAA189606E74A2EC499EDMSEXCHANGE01i_"
X-MS-Tnef-Correlator:
X-Spam-Score: -6.9
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id CAF5A61E003 for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Thu, 18 Aug 2011 12:36:45 -0400 (EDT)
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 o2-hQBpw68mz for <cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com>; Thu, 18 Aug 2011 12:36:44 -0400 (EDT)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id CC1772405E2 for <bug-Spreadsheet-XLSX@rt.cpan.org>; Thu, 18 Aug 2011 12:36:43 -0400 (EDT)
Received: (qmail 12373 invoked by uid 103); 18 Aug 2011 16:36:43 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 18 Aug 2011 16:36:43 -0000
Received: from Remote.infoplanning.com (HELO remote.infoplanning.com) (216.246.121.51) by 16.mx.develooper.com (qpsmtpd/0.80/v0.80-19-gf52d165) with ESMTP; Thu, 18 Aug 2011 09:36:39 -0700
Received: from MSEXCHANGE01.infoplanning.local (192.168.70.15) by remote.infoplanning.com (192.168.70.1) with Microsoft SMTP Server (TLS) id 8.1.393.1; Thu, 18 Aug 2011 12:39:24 -0400
Received: from MSEXCHANGE01.infoplanning.local ([192.168.70.15]) by MSEXCHANGE01.infoplanning.local ([192.168.70.15]) with mapi; Thu, 18 Aug 2011 12:39:24 -0400
Delivered-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
Subject: RE: [rt.cpan.org #70341] AutoReply: bug in Spreadsheet-XLSX-0.13
Return-Path: <msmoot@infoplanning.com>
Thread-Index: AcxdubDKcq4/AgIpQ1OK/aYoVyiBZQACoeug
X-Spam-Check-BY: 16.mx.develooper.com
X-Original-To: cpan-bug+Spreadsheet-XLSX@hipster.bestpractical.com
X-RT-Mail-Extension: spreadsheet-xlsx
Date: Thu, 18 Aug 2011 12:39:24 -0400
X-Spam-Level:
Thread-Topic: [rt.cpan.org #70341] AutoReply: bug in Spreadsheet-XLSX-0.13
X-MS-Has-Attach: yes
To: "bug-Spreadsheet-XLSX@rt.cpan.org" <bug-Spreadsheet-XLSX@rt.cpan.org>
Accept-Language: en-US
From: Martin Smoot <msmoot@infoplanning.com>
RT-Message-ID: <rt-3.8.HEAD-2955-1313685406-1126.70341-0-0@rt.cpan.org>
Content-Length: 0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: base64
X-RT-Original-Encoding: utf-8
Content-Length: 4795
I have a modified version of the XLSX.pm file attached to this message (with a different name) - please note that I have extra prints commented out in this code. this appears to solve the problem that I reported but I have only run the test on a single spreadsheet at this time. my test was to build a spreadsheet using the attached test program "makess.pl" where I just directed the output to a .csv file. I then used Excel to read the .csv and write the .xlsx file. I then used the .xlsx to .xsv conversion code to write a new .csv file and compared the data. I am going to run some more tests to verify that the code works as expected using other utilities (including a .csv to .xlsx utility). I will send an additional report if I find any other issues.
Show quoted text
-----Original Message----- From: Bugs in Spreadsheet-XLSX via RT [mailto:bug-Spreadsheet-XLSX@rt.cpan.org] Sent: Thursday, August 18, 2011 11:13 AM To: Martin Smoot Subject: [rt.cpan.org #70341] AutoReply: bug in Spreadsheet-XLSX-0.13 Greetings, This message has been automatically generated in response to the creation of a trouble ticket regarding: "bug in Spreadsheet-XLSX-0.13", a summary of which appears below. There is no need to reply to this message right now. Your ticket has been assigned an ID of [rt.cpan.org #70341]. Your ticket is accessible on the web at: https://rt.cpan.org/Ticket/Display.html?id=70341 Please include the string: [rt.cpan.org #70341] in the subject line of all future correspondence about this issue. To do so, you may reply to this message. Thank you, bug-Spreadsheet-XLSX@rt.cpan.org ------------------------------------------------------------------------- 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. 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.
Content-Description: XLSX_1.pm
content-type: application/octet-stream; name="XLSX_1.pm"
content-disposition: attachment; creation-date="Thu, 18 Aug 2011 12:30:45 GMT"; filename="XLSX_1.pm"; modification-date="Thu, 18 Aug 2011 12:17:04 GMT"; size="9261"
Content-Transfer-Encoding: base64
Content-Length: 9261

Message body is not shown because sender requested not to inline it.

Content-Description: makess.pl
content-type: application/octet-stream; name="makess.pl"
content-disposition: attachment; creation-date="Thu, 18 Aug 2011 12:36:56 GMT"; filename="makess.pl"; modification-date="Thu, 18 Aug 2011 10:50:58 GMT"; size="837"
Content-Transfer-Encoding: base64
Content-Length: 837

Message body is not shown because sender requested not to inline it.



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.