Skip Menu |
 
rt.cpan.org will be shut down on March 1st, 2021.

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

Report information
The Basics
Id: 117007
Status: new
Priority: 0/
Queue: Spreadsheet-XLSX

People
Owner: Nobody in particular
Requestors: Jeff.Smith [...] priceline.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



CC: "Smith, Jeff" <Jeff.Smith [...] priceline.com>
Subject: day of week & AM/PM bug
Date: Tue, 16 Aug 2016 19:57:18 +0000
To: "bug-Spreadsheet-XLSX [...] rt.cpan.org" <bug-Spreadsheet-XLSX [...] rt.cpan.org>
From: "Smith, Jeff" <Jeff.Smith [...] priceline.com>
Submitted for your review: day of week & AM/PM bug & suggested fix Using the application: MS Excel for Mac 2011 Version 14.6.6 (160626) I created a worksheet (XSLX) with the following values field formula format formula with format applied Day of week (long) =now() dddd Tuesday Day of week (short) =now() ddd Tue 6am - AM/PM =TIME(6,5, 4) AM/PM AM 6pm - AM/PM =TIME(18,5, 4) AM/PM PM SCP’ing the above worksheet (file) to a Linux server, described below: E N V I R O N M E N T Distribution name & version Red Hat / Fedora Project perl-Spreadsheet-XLSX-0.15-1.el6.src.rpm Perl version This is perl, v5.10.1 (*) built for x86_64-linux-thread-multi Operating System vendor and version Red Hat Linux server-name-omitted 2.6.32-573.12.1.el6.x86_64 #1 SMP Mon Nov 23 12:55:32 EST 2015 x86_64 x86_64 x86_64 GNU/Linux Using the latest version of Spreadsheet-XLSX, which is 0.15: Name : perl-Spreadsheet-XLSX Relocations: (not relocatable) Version : 0.15 Vendor: Fedora Project Release : 1.el6 Build Date: Sat 09 Jan 2016 11:29:29 AM EST Install Date: Thu 09 Jun 2016 01:52:32 PM EDT Build Host: buildvm-10.phx2.fedoraproject.org Group : Development/Libraries Source RPM: perl-Spreadsheet-XLSX-0.15-1.el6.src.rpm Size : 64339 License: GPL+ or Artistic Signature : RSA/8, Sat 09 Jan 2016 12:23:31 PM EST, Key ID 3b49df2a0608b895 Packager : Fedora Project URL : http://search.cpan.org/dist/Spreadsheet-XLSX/ Summary : Perl extension for reading Microsoft Excel 2007 files I created a Perl script which simply dumped the fields, and got the following. (If coloring is not preserved, the final column is the output.) field,,formula,format,output Day of week (long),,=now(),dddd,dddd Day of week (short),,=now(),ddd,1616 6am - AM/PM,,"=TIME(6,5, 4)",AM/PM,0.25351851851851853 6pm - AM/PM,,"=TIME(18,5, 4)",AM/PM,0.75351851851851848 Moving my changes into place, the output becomes: field,,formula,format,output Day of week (long),,=now(),dddd,Tuesday Day of week (short),,=now(),ddd,Tue 6am - AM/PM,,"=TIME(6,5, 4)",AM/PM,AM 6pm - AM/PM,,"=TIME(18,5, 4)",AM/PM,PM Description of changes, based on patch info below: @@ -228,7 +228,7 @@: the ($sWk =~ /[ymdshapg]) does not handle the “AM/PM” which was present in format @@ -248,6 +248,7 @@: the “short DoW” wasn’t being processed, since the test for ‘ddd’ was missing @@ -320,12 +321,22 @@: (1st part) informative text (kept me sane) @@ -320,12 +321,22 @@: (2nd part) the index into @aWeekL & @aWeekNm is 0-6 based (0=Sunday), not 1-7 based (7=Sunday) @@ -361,9 +372,9 @@: the index into the @aWeekL & @aWeekNm arrays should be the 7th element ([6]), NOT the 8th ([7]), of @aTime @@ -394,9 +405,9 @@: the index for the hour should be the 3rd element ([2]), NOT the 5th ([4]), of @aTime; also made output uppercase for consistency My patch file: --- Utility2007.pm.orig 2015-04-22 17:43:34.000000000 -0400 +++ Utility2007.pm.jeff 2016-08-16 15:33:21.038758070 -0400 @@ -228,7 +228,7 @@ } } $i++; - } elsif ($sWk =~ /[ymdhsapg]/) { + } elsif ($sWk =~ /[ymdhsapg]/i) { $iFmtMode = 2 unless ($iFmtMode); if (substr($sFmtObj, $i, 5) =~ /am\/pm/i) { push @aRep, ['am/pm', length($sFmtRes), 5]; @@ -248,6 +248,7 @@ push @aRep, [substr($sFmtObj, $i, 4), length($sFmtRes), 4]; $i += 4; } elsif ((substr($sFmtObj, $i, 3) eq 'mmm') + || (substr($sFmtObj, $i, 3) eq 'ddd') || (substr($sFmtObj, $i, 3) eq 'yyy')) { push @aRep, [substr($sFmtObj, $i, 3), length($sFmtRes), 3]; $i += 3; @@ -320,12 +321,22 @@ my @aTime = ExcelLocaltime($iData, $i1904); $aTime[4]++; $aTime[5] += 1900; + #print "aTime [",join(":",@aTime), "]\n"; + # aTime[] + # 0 - seconds + # 1 - minutes + # 2 - hours + # 3 - day of month + # 4 - month of year + # 5 - year + # 6 - day of week (0(Sun)-6) + # 7 - ??? my @aMonL = qw (dum January February March April May June July August September October November December ); my @aMonNm = qw (dum Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec); - my @aWeekNm = qw (Mon Tue Wed Thu Fri Sat Sun); - my @aWeekL = qw (Monday Tuesday Wednesday Thursday Friday Saturday Sunday); + my @aWeekNm = qw (Sun Mon Tue Wed Thu Fri Sat); + my @aWeekL = qw (Sunday Monday Tuesday Wednesday Thursday Friday Saturday); my $sRep; for (my $iIt = $#aRep ; $iIt >= 0 ; $iIt--) { my $rItem = $aRep[$iIt]; @@ -361,9 +372,9 @@ #Day elsif ($rItem->[0] eq 'dddd') { - $sRep = $aWeekL[$aTime[7]]; + $sRep = $aWeekL[$aTime[6]]; } elsif ($rItem->[0] eq 'ddd') { - $sRep = $aWeekNm[$aTime[7]]; + $sRep = $aWeekNm[$aTime[6]]; } elsif ($rItem->[0] eq 'dd') { $sRep = sprintf('%02d', $aTime[3]); } elsif ($rItem->[0] eq 'd') { @@ -394,9 +405,9 @@ #am/pm elsif ($rItem->[0] eq 'am/pm') { - $sRep = ($aTime[4] > 12) ? 'pm' : 'am'; + $sRep = ($aTime[2] > 12) ? 'PM' : 'AM'; } elsif ($rItem->[0] eq 'a/p') { - $sRep = ($aTime[4] > 12) ? 'p' : 'a'; + $sRep = ($aTime[2] > 12) ? 'P' : 'A'; } elsif ($rItem->[0] eq '.') { $sRep = '.'; } elsif ($rItem->[0] =~ /^0+$/) { Let me know if there’s anything else which I may have omitted which is needed to explain the bug or the solution. Thanks, Jeff Smith | Principal Solutions Engineer priceline.com<http://priceline.com/> | 800 Connecticut Ave | Norwalk, CT 06854
Download (untitled) / with headers
text/html 35.5k

Message body is not shown because it is too large.



This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

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