Subject: | Bug in ExcelLocaltime (Patch) |
Utility::ExcelLocaltime is not correctly handling times which are less
than half of a millisecond before midnight.
localtime.xls was created by Excel 2003 on Windows XP and contains the
following:
A1: 1/1/2009 0:00:00
B1: =A1-(0.0005/86400)
Both have the same formatting "m/d/yyyy h:mm AM/PM" and both display in
Excel as "1/1/2009 12:00 AM".
I ran this program on the file:
my $parser = Spreadsheet::ParseExcel->new();
my $wb = $parser->Parse("localtime.xls");
my $ws = $wb->Worksheet(0);
print "A1: ", $ws->get_cell(0,0)->value(), "\n";
print "B1: ", $ws->get_cell(0,1)->value(), "\n";
This is the output:
A1: 1/1/2009 12:00 AM
B1: 12/31/2008 12:00 PM
This appears to be happening because the millisecond rounding that
occurs in ExcelLocaltime pushes the value of $iTime greater than 1,
which it should never be since Excel stores time values only in the
fractional part of the real number. The attached patch checks for this
and corrects it.
After applying the patch the output of the above program is:
A1: 1/1/2009 12:00 AM
B1: 1/1/2009 12:00 AM
My system info:
Perl version : 5.008008
OS name : linux
Module versions: (not all are required)
Spreadsheet::ParseExcel 0.49
Scalar::Util 1.18
Unicode::Map (not installed)
Spreadsheet::WriteExcel 2.25
Parse::RecDescent 1.94
File::Temp 0.21
OLE::Storage_Lite 0.18
IO::Stringy 2.110
Subject: | localtime.xls |
Message body not shown because it is not plain text.
Subject: | Utility.pm.diff |
--- /usr/local/share/perl/5.8.8/Spreadsheet/ParseExcel/Utility.pm.old 2009-06-17 14:14:26.000000000 -0400
+++ /usr/local/share/perl/5.8.8/Spreadsheet/ParseExcel/Utility.pm 2009-06-17 15:02:28.000000000 -0400
@@ -1086,7 +1086,7 @@
sub ExcelLocaltime {
my ( $dObj, $flg1904 ) = @_;
my ( $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay, $iMSec );
- my ( $iDt, $iTime, $iYDays );
+ my ( $iDt, $iTime, $iYDays, $iMD );
$iDt = int($dObj);
$iTime = $dObj - $iDt;
@@ -1111,8 +1111,7 @@
&& ( ( $iYear % 100 ) || ( $iYear % 400 ) == 0 ) ) ? 366 : 365;
}
$iYear -= 1900;
- for ( $iMon = 1 ; $iMon < 12 ; $iMon++ ) {
- my $iMD;
+ for ( $iMon = 1 ; $iMon <= 12 ; $iMon++ ) {
if ( $iMon == 1
|| $iMon == 3
|| $iMon == 5
@@ -1136,6 +1135,28 @@
#2. Calc Time
$iDay = $iDt;
$iTime += ( 0.0005 / 86400.0 );
+ if ($iTime >= 1.0)
+ {
+ $iTime -= int($iTime);
+ $iwDay = ($iwDay == 6) ? 0 : $iwDay + 1;
+ if ($iDay == $iMD)
+ {
+ if ($iMon == 12)
+ {
+ $iMon = 1;
+ $iYear++;
+ }
+ else
+ {
+ $iMon++;
+ }
+ $iDay = 1;
+ }
+ else
+ {
+ $iDay++;
+ }
+ }
$iTime *= 24.0;
$iHour = int($iTime);
$iTime -= $iHour;