Bug #117007 for Spreadsheet-XLSX: day of week & AM/PM bug

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