Viewing file:  datecalc1.pl (7.05 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
#!/usr/bin/perl -w
######################################################################
#
# NOTE: An easier way of writing dates and times is to use the newer
# write_date_time() Worksheet method. See the date_time.pl example.
#
######################################################################
#
# Demonstration of writing date/time cells to Excel spreadsheets,
# using UNIX/Perl time as source of date/time.
#
######################################################################
#
# UNIX/Perl time is the time since the Epoch (00:00:00 GMT, 1 Jan 1970)
# measured in seconds.
#
# An Excel file can use exactly one of two different date/time systems.
# In these systems, a floating point number represents the number of days
# (and fractional parts of the day) since a start point. The floating point
# number is referred to as a 'serial'.
#
# The two systems ('1900' and '1904') use different starting points:
#
# '1900'; '1.00' is 1 Jan 1900 BUT 1900 is erroneously regarded as
# a leap year - see:
# http://support.microsoft.com/support/kb/articles/Q181/3/70.asp
# for the excuse^H^H^H^H^H^Hreason.
# '1904'; '1.00' is 2 Jan 1904.
#
# The '1904' system is the default for Apple Macs. Windows versions of
# Excel have the option to use the '1904' system.
#
# Note that Visual Basic's "DateSerial" function does NOT erroneously
# regard 1900 as a leap year, and thus its serials do not agree with
# the 1900 serials of Excel for dates before 1 Mar 1900.
#
# Note that StarOffice (at least at version 5.2) does NOT erroneously
# regard 1900 as a leap year, and thus its serials do not agree with
# the 1900 serials of Excel for dates before 1 Mar 1900.
#
# Copyright 2000, Andrew Benham, adsb@bigfoot.com
#
######################################################################
#
# Calculation description
# =======================
#
# 1900 system
# -----------
# Unix time is '0' at 00:00:00 GMT 1 Jan 1970, i.e. 70 years after 1 Jan 1900.
# Of those 70 years, 17 (1904,08,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68)
# were leap years with an extra day.
# Thus there were 17 + 70*365 days = 25567 days between 1 Jan 1900 and
# 1 Jan 1970.
# In the 1900 system, '1' is 1 Jan 1900, but as 1900 was not a leap year
# 1 Jan 1900 should really be '2', so 1 Jan 1970 is '25569'.
#
# 1904 system
# -----------
# Unix time is '0' at 00:00:00 GMT 1 Jan 1970, i.e. 66 years after 1 Jan 1904.
# Of those 66 years, 17 (1904,08,12,16,20,24,28,32,36,40,44,48,52,56,60,64,68)
# were leap years with an extra day.
# Thus there were 17 + 66*365 days = 24107 days between 1 Jan 1904 and
# 1 Jan 1970.
# In the 1904 system, 2 Jan 1904 being '1', 1 Jan 1970 is '24107'.
#
######################################################################
#
# Copyright (c) 2000, Andrew Benham.
# This program is free software. It may be used, redistributed and/or
# modified under the same terms as Perl itself.
#
# Andrew Benham, adsb@bigfoot.com
# London, United Kingdom
# 11 Nov 2000
#
######################################################################
use strict;
use Spreadsheet::WriteExcel;
use Time::Local;
use vars qw/$DATE_SYSTEM/;
# Use 1900 date system on all platforms other than Apple Mac (for which
# use 1904 date system).
$DATE_SYSTEM = ($^O eq 'MacOS') ? 1 : 0;
my $workbook = Spreadsheet::WriteExcel->new("dates.xls");
my $worksheet = $workbook->add_worksheet();
my $format_date = $workbook->add_format();
$format_date->set_num_format('d mmmm yyy');
$worksheet->set_column(0,1,21);
$worksheet->write_string (0,0,"The epoch (GMT)");
$worksheet->write_number (0,1,&calc_serial(0,1),0x16);
$worksheet->write_string (1,0,"The epoch (localtime)");
$worksheet->write_number (1,1,&calc_serial(0,0),0x16);
$worksheet->write_string (2,0,"Today");
$worksheet->write_number (2,1,&calc_serial(),$format_date);
my $christmas2000 = timelocal(0,0,0,25,11,100);
$worksheet->write_string (3,0,"Christmas 2000");
$worksheet->write_number (3,1,&calc_serial($christmas2000),$format_date);
$workbook->close();
#-----------------------------------------------------------
# calc_serial()
#
# Called with (up to) 2 parameters.
# 1. Unix timestamp. If omitted, uses current time.
# 2. GMT flag. Set to '1' to return serial in GMT.
# If omitted, returns serial in appropriate timezone.
#
# Returns date/time serial according to $DATE_SYSTEM selected
#-----------------------------------------------------------
sub calc_serial {
my $time = (defined $_[0]) ? $_[0] : time();
my $gmtflag = (defined $_[1]) ? $_[1] : 0;
# Divide timestamp by number of seconds in a day.
# This gives a date serial with '0' on 1 Jan 1970.
my $serial = $time / 86400;
# Adjust the date serial by the offset appropriate to the
# currently selected system (1900/1904).
if ($DATE_SYSTEM == 0) { # use 1900 system
$serial += 25569;
} else { # use 1904 system
$serial += 24107;
}
unless ($gmtflag) {
# Now have a 'raw' serial with the right offset. But this
# gives a serial in GMT, which is false unless the timezone
# is GMT. We need to adjust the serial by the appropriate
# timezone offset.
# Calculate the appropriate timezone offset by seeing what
# the differences between localtime and gmtime for the given
# time are.
my @gmtime = gmtime($time);
my @ltime = localtime($time);
# For the first 7 elements of the two arrays, adjust the
# date serial where the elements differ.
for (0 .. 6) {
my $diff = $ltime[$_] - $gmtime[$_];
if ($diff) {
$serial += _adjustment($diff,$_);
}
}
}
# Perpetuate the error that 1900 was a leap year by decrementing
# the serial if we're using the 1900 system and the date is prior to
# 1 Mar 1900. This has the effect of making serial value '60'
# 29 Feb 1900.
# This fix only has any effect if UNIX/Perl time on the platform
# can represent 1900. Many can't.
unless ($DATE_SYSTEM) {
$serial-- if ($serial < 61); # '61' is 1 Mar 1900
}
return $serial;
}
sub _adjustment {
# Based on the difference in the localtime/gmtime array elements
# number, return the adjustment required to the serial.
# We only look at some elements of the localtime/gmtime arrays:
# seconds unlikely to be different as all known timezones
# have an offset of integral multiples of 15 minutes,
# but it's easy to do.
# minutes will be different for timezone offsets which are
# not an exact number of hours.
# hours very likely to be different.
# weekday will differ when localtime/gmtime difference
# straddles midnight.
#
# Assume that difference between localtime and gmtime is less than
# 5 days, then don't have to do maths for day of month, month number,
# year number, etc...
my ($delta,$element) = @_;
my $adjust = 0;
if ($element == 0) { # Seconds
$adjust = $delta/86400; # 60 * 60 * 24
} elsif ($element == 1) { # Minutes
$adjust = $delta/1440; # 60 * 24
} elsif ($element == 2) { # Hours
$adjust = $delta/24; # 24
} elsif ($element == 6) { # Day of week number
# Catch difference straddling Sat/Sun in either direction
$delta += 7 if ($delta < -4);
$delta -= 7 if ($delta > 4);
$adjust = $delta;
}
return $adjust;
}
|