Viewing file:  excel.pl (6.53 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
#!/usr/bin/perl
use strict;
use warnings;
# Convert between Excel serial dates and Time::Moment
use Time::Moment;
use constant EXCEL_EPOCH => Time::Moment->from_string('1899-12-30T00Z')->rd;
sub moment_from_excel {
@_ == 1 or die q/Usage: moment_from_excel(date)/;
my ($date) = @_;
return Time::Moment->from_rd($date + ($date < 61), epoch => EXCEL_EPOCH);
}
sub moment_to_excel {
@_ == 1 or die q/Usage: moment_to_excel(moment)/;
my ($moment) = @_;
my $date = $moment->rd - EXCEL_EPOCH;
return $date - ($date < 61);
}
my @tests = (
[ '1899-12-31T00:00:00Z', 0 ],
[ '1900-01-01T00:00:00Z', 1 ],
[ '1900-02-27T00:00:00Z', 58 ],
[ '1900-02-28T00:00:00Z', 59 ],
[ '1900-03-01T00:00:00Z', 61 ],
[ '1900-03-02T00:00:00Z', 62 ],
[ '1969-12-31T23:59:58Z', 25568.9999768519 ],
[ '1969-12-31T23:59:58.500Z', 25568.9999826389 ],
[ '1969-12-31T23:59:58.800Z', 25568.9999861111 ],
[ '1969-12-31T23:59:58.900Z', 25568.9999872685 ],
[ '1969-12-31T23:59:58.980Z', 25568.9999881944 ],
[ '1969-12-31T23:59:58.990Z', 25568.9999883102 ],
[ '1969-12-31T23:59:58.998Z', 25568.9999884028 ],
[ '1969-12-31T23:59:58.999Z', 25568.9999884143 ],
[ '1969-12-31T23:59:59Z', 25568.9999884259 ],
[ '1969-12-31T23:59:59.001Z', 25568.9999884375 ],
[ '1969-12-31T23:59:59.002Z', 25568.9999884490 ],
[ '1969-12-31T23:59:59.010Z', 25568.9999885417 ],
[ '1969-12-31T23:59:59.020Z', 25568.9999886574 ],
[ '1969-12-31T23:59:59.100Z', 25568.9999895834 ],
[ '1969-12-31T23:59:59.200Z', 25568.9999907408 ],
[ '1969-12-31T23:59:59.300Z', 25568.9999918982 ],
[ '1969-12-31T23:59:59.400Z', 25568.9999930556 ],
[ '1969-12-31T23:59:59.490Z', 25568.9999940973 ],
[ '1969-12-31T23:59:59.499Z', 25568.9999942014 ],
[ '1969-12-31T23:59:59.500Z', 25568.9999942130 ],
[ '1969-12-31T23:59:59.501Z', 25568.9999942245 ],
[ '1969-12-31T23:59:59.510Z', 25568.9999943287 ],
[ '1969-12-31T23:59:59.600Z', 25568.9999953704 ],
[ '1969-12-31T23:59:59.700Z', 25568.9999965278 ],
[ '1969-12-31T23:59:59.800Z', 25568.9999976852 ],
[ '1969-12-31T23:59:59.900Z', 25568.9999988426 ],
[ '1969-12-31T23:59:59.980Z', 25568.9999997686 ],
[ '1969-12-31T23:59:59.990Z', 25568.9999998843 ],
[ '1969-12-31T23:59:59.998Z', 25568.9999999768 ],
[ '1969-12-31T23:59:59.999Z', 25568.9999999885 ],
[ '1970-01-01T00:00:00Z', 25569 ],
[ '1970-01-01T00:00:00.001Z', 25569.0000000115 ],
[ '1970-01-01T00:00:00.002Z', 25569.0000000232 ],
[ '1970-01-01T00:00:00.010Z', 25569.0000001157 ],
[ '1970-01-01T00:00:00.020Z', 25569.0000002314 ],
[ '1970-01-01T00:00:00.100Z', 25569.0000011574 ],
[ '1970-01-01T00:00:00.200Z', 25569.0000023148 ],
[ '1970-01-01T00:00:00.300Z', 25569.0000034722 ],
[ '1970-01-01T00:00:00.400Z', 25569.0000046296 ],
[ '1970-01-01T00:00:00.490Z', 25569.0000056713 ],
[ '1970-01-01T00:00:00.499Z', 25569.0000057755 ],
[ '1970-01-01T00:00:00.500Z', 25569.0000057870 ],
[ '1970-01-01T00:00:00.501Z', 25569.0000057986 ],
[ '1970-01-01T00:00:00.510Z', 25569.0000059027 ],
[ '1970-01-01T00:00:00.600Z', 25569.0000069444 ],
[ '1970-01-01T00:00:00.700Z', 25569.0000081018 ],
[ '1970-01-01T00:00:00.800Z', 25569.0000092592 ],
[ '1970-01-01T00:00:00.900Z', 25569.0000104166 ],
[ '1970-01-01T00:00:00.980Z', 25569.0000113426 ],
[ '1970-01-01T00:00:00.990Z', 25569.0000114583 ],
[ '1970-01-01T00:00:00.998Z', 25569.0000115510 ],
[ '1970-01-01T00:00:00.999Z', 25569.0000115625 ],
[ '1970-01-01T00:00:01Z', 25569.0000115741 ],
[ '1970-01-01T00:00:01.001Z', 25569.0000115857 ],
[ '1970-01-01T00:00:01.002Z', 25569.0000115972 ],
[ '1970-01-01T00:00:01.010Z', 25569.0000116898 ],
[ '1970-01-01T00:00:01.020Z', 25569.0000118056 ],
[ '1970-01-01T00:00:01.100Z', 25569.0000127315 ],
[ '1970-01-01T00:00:01.200Z', 25569.0000138889 ],
[ '9999-12-31T23:59:58Z', 2958465.9999768520 ],
[ '9999-12-31T23:59:58.500Z', 2958465.9999826388 ],
[ '9999-12-31T23:59:58.800Z', 2958465.9999861112 ],
[ '9999-12-31T23:59:58.900Z', 2958465.9999872684 ],
[ '9999-12-31T23:59:58.980Z', 2958465.9999881946 ],
[ '9999-12-31T23:59:58.990Z', 2958465.9999883100 ],
[ '9999-12-31T23:59:58.998Z', 2958465.9999884027 ],
[ '9999-12-31T23:59:58.999Z', 2958465.9999884143 ],
[ '9999-12-31T23:59:59Z', 2958465.9999884260 ],
[ '9999-12-31T23:59:59.001Z', 2958465.9999884376 ],
[ '9999-12-31T23:59:59.002Z', 2958465.9999884493 ],
[ '9999-12-31T23:59:59.010Z', 2958465.9999885415 ],
[ '9999-12-31T23:59:59.020Z', 2958465.9999886574 ],
[ '9999-12-31T23:59:59.100Z', 2958465.9999895832 ],
[ '9999-12-31T23:59:59.200Z', 2958465.9999907408 ],
[ '9999-12-31T23:59:59.300Z', 2958465.9999918980 ],
[ '9999-12-31T23:59:59.400Z', 2958465.9999930556 ],
[ '9999-12-31T23:59:59.490Z', 2958465.9999940973 ],
[ '9999-12-31T23:59:59.499Z', 2958465.9999942016 ],
[ '9999-12-31T23:59:59.500Z', 2958465.9999942128 ],
[ '9999-12-31T23:59:59.501Z', 2958465.9999942244 ],
[ '9999-12-31T23:59:59.510Z', 2958465.9999943287 ],
[ '9999-12-31T23:59:59.600Z', 2958465.9999953704 ],
[ '9999-12-31T23:59:59.700Z', 2958465.9999965276 ],
[ '9999-12-31T23:59:59.800Z', 2958465.9999976852 ],
[ '9999-12-31T23:59:59.900Z', 2958465.9999988424 ],
[ '9999-12-31T23:59:59.980Z', 2958465.9999997686 ],
[ '9999-12-31T23:59:59.990Z', 2958465.9999998841 ],
[ '9999-12-31T23:59:59.998Z', 2958465.9999999767 ],
[ '9999-12-31T23:59:59.999Z', 2958465.9999999884 ],
);
use Test::More 0.88;
use Test::Number::Delta 1.06 relative => 1E-10;
foreach my $test (@tests) {
my ($string, $date) = @$test;
my $tm = moment_from_excel($date);
is($tm->to_string, $string, "moment_from_excel($date)");
delta_ok(moment_to_excel($tm), $date, "moment_to_excel($tm)");
}
done_testing();
eval {
require DateTime::Format::Excel;
require Benchmark;
{
print "\nComparing DateTime::Format::Excel and Time::Moment\n";
my $date = 30188.010650613425;
Benchmark::cmpthese( -10, {
'DateTime' => sub {
my $dt = DateTime::Format::Excel->parse_datetime($date);
},
'Time::Moment' => sub {
my $tm = moment_from_excel($date);
},
});
}
};
|