DateTimeX::Format::Excel - Microsofty conversion of Excel epochs
#!/usr/bin/env perl use DateTimeX::Format::Excel; # From an Excel date number my $parser = DateTimeX::Format::Excel->new(); print $parser->parse_datetime( 25569 )->ymd ."\n"; my $datetime = $parser->parse_datetime( 37680 ); print $datetime->ymd() ."\n"; $datetime = $parser->parse_datetime( 40123.625 ); print $datetime->iso8601() ."\n"; # And back to an Excel number from a DateTime object use DateTime; my $dt = DateTime->new( year => 1979, month => 7, day => 16 ); my $daynum = $parser->format_datetime( $dt ); print $daynum ."\n"; my $dt_with_time = DateTime->new( year => 2010, month => 7, day => 23 , hour => 18, minute => 20 ); my $parser_date = $parser->format_datetime( $dt_with_time ); print $parser_date ."\n"; ########################### # SYNOPSIS Screen Output # 01: 1970-01-01 # 02: 2003-02-28 # 03: 2009-11-06T15:00:00 # 04: 29052 # 05: 40382.763888889 ###########################
Excel uses a different system for its dates than most Unix programs. This package allows you to convert between the Excel raw format and and DateTime objects, which can then be further converted via any of the other DateTime::Format::* modules, or just with DateTime's methods. The DateTime::Format::Excel module states "we assume what Psion assumed for their Abacus / Sheet program". As a consequence the output does not follow exactly the output of Excel. Especially in the Windows range of 0-60. This module attempts to more faithfully follow actual Microsoft Excel with a few notable exceptions.
Excel has a few date quirks. First, it allows two different epochs. One for the Windows world and one for the Apple world. The windows epoch starts in 0-January-1900 and allows for 29-February-1900 (both non real dates). Most of the explanations for the difference between windows implementations and Apple implementations focus on the fact that there was no leap year in 1900 (the Gregorian vs Julian calendars) and the Apple version wanted to skip that issue. Both non real dates appear to have been a known issue in the original design of VisiCalc that was carried through Lotus 1-2-3 and into Excel for compatibility . (Spreadsheets were arguably the first personal computer killer app and Excel was a johnny come lately trying to gain an entry into the market at the time.) The closest microsoft discussion I could find on this issue is here. In any case the apple version starts 1-January-1904. (counting from 0 while also avoiding the leap year issue). In both cases the Windows and Apple version use integers from the epoch start to represent days and the decimal portion to represent a portion of a day. Both Windows and Apple Excel will attempt to convert recognized date strings to an Excel epoch for storage with the exception that any date prior to the epoch start will be stored as a string. (31-December-1899 and earlier for Windows and 31-December-1903 and earlier for Apple). Next, Excel does not allow for a time zone component of each number. Finally, in the Windows version when dealing with epochs that do not have a date component just a time component all values will fall between 0 and 1 which is a non real date (0-January-1900).
This explanation is not intended to justify Microsofts decisions with Excel dates just replicate them as faithfully as possible. This module makes the assumption that you already know if your date is a string or a number in Excel and that you will handle string to DateTime conversions elsewhere. see DateTime::Format::Flexible. Any passed strings will die. (As a failure of a Type::Tiny test) This module also makes several unilateral decisions to deal with corner cases. When a 0 date is requested to be converted to DateTime it will use Carp to cluck that it received a bad date and then provide a DateTime object dated 1-January-1900 (Excel would provide 0-January-1900). If a value between 0 and 1 is requested to be converted to a DateTime object the module will NOT cluck and provide an object dated 1-January-1900 with the appropriate time component. All Apple times are provide as 1-January-1904. Any requested numerical conversion for Windows >= 60 and < 61 will cluck and provide a DateTime object dated 1-March-1900 (Excel would provide 29-Febrary-1900). All requests for conversion of negative numbers to DateTime objects will die . If a DateTime object is provided for conversion to the Excel value and it falls earlier than 1-January-1900 for Windows and 1-January-1904 for Apple then the DateTime object itself will be returned. If you accept the output of that method as a scalar, DateTime will stringify itself and give you a text equivalent date. For time zones you can pass a time zone with the excel number for conversion to the DateTime object. In reverse, the conversion to Excel Epoch uses the ->jd method for calculation so the time zone is stripped out. No clone or duration calculations are provided with this module. Finally this is a Moose based module and does not provide a functional interface. (Moose would allow it I just chose not to for design purposes).
The Types module for this package uses Type::Tiny which can, in the background, use Type::Tiny::XS. While in general this is a good thing you will need to make sure that Type::Tiny::XS is version 0.010 or newer since the older ones didn't support the 'Optional' method.
Data passed to new when creating an instance (parser). For modification of these attributes see the listed "Methods" of the instance.
Definition: This attribute identifies whether the translation will be done for Windows Excel => 'win_excel' or Apple Excel => 'apple_excel'.
Default win_excel (0-January-1900T00:00:00 = 0, range includes 29-February-1900)
Range win_excel|apple_excel (1-January-1904T00:00:00 = 0)
These include methods to adjust attributes as well as providing methods to provide the conversion functionality of the module.
Definition: This is the way to see whether the conversion is Windows or Apple based
Accepts:Nothing
Returns: win_excel|apple_excel
Definition: This is the way to set the base epoch for the translator
Accepts: win_excel|apple_excel (see the "DESCRIPTION" for details)
Returns: Nothing
Definition: This is how positive excel numbers are translated to DateTime objects
Accepts: @arg_list - the order is important!
0. $the_excel_number_for_translation - must be positive - no strings allowed
1. 'time_zone' (the only useful option - other values here will ignore position 2)
2. A recognizable time zone string or DateTime::TimeZone object
example: ( 12345, time_zone => 'America/Los_Angeles' )
Returns: A DateTime object set to match the passed values. A floating time zone is default.
Definition: This is how DateTime objects can be translated to Excel epoch numbers
Accepts: A DateTime object
Returns: An excel epoch number or DateTime object if it is before the relevant epoch start.
Dates saved in Excel prior to 1-January-1900 for Windows or 1-January-1904 for Apple are stored as text. I suggest using "Chained Coercions" in Type::Tiny::Manual::Coercions. Or use an Excel reader that implements this for you like Spreadsheet::XLSX::Reader::LibXML (self promotion). Here is one possible way to integrate text and dates in the same field into a consistent DateTime output. (I know it's a bit clunky but it's a place to start)
my $system_lookup = { '1900' => 'win_excel', '1904' => 'apple_excel', }; my @args_list = ( system_type => $system_lookup->{$workbook->get_epoch_year} ); my $converter = DateTimeX::Format::Excel->new( @args_list ); my $string_via = sub{ my $str = $_[0]; return DateTime::Format::Flexible->parse_datetime( $str ); }; my $num_via = sub{ my $num = $_[0]; return $converter->parse_datetime( $num ); }; my $date_time_from_value = Type::Coercion->new( type_coercion_map => [ Num, $num_via, Str, $string_via, ], ); my $date_time_type = Type::Tiny->new( name => 'Custom_date_type', constraint => sub{ ref($_) eq 'DateTime' }, coercion => $date_time_from_value, ); my $string_type = Type::Tiny->new( name => 'YYYYMMDD', constraint => sub{ !$_ or ( $_ =~ /^\d{4}\-(\d{2})-(\d{2})$/ and $1 > 0 and $1 < 13 and $2 > 0 and $2 < 32 ) }, coercion => Type::Coercion->new( type_coercion_map =>[ $date_time_type->coercibles, sub{ my $tmp = $date_time_type->coerce( $_ ); $tmp->format_cldr( 'yyyy-MM-dd' ) }, ], ), );
Dave Rolsky (DROLSKY) for kickstarting the DateTime project. Iain Truskett, Dave Rolsky, and Achim Bursian for maintaining DateTime::Format::Excel. I used it heavily till I wrote this. Peter (Stig) Edwards and Bobby Metz for contributing to DateTime::Format::Excel.
1. Download a compressed file with the code
2. Extract the code from the compressed file. If you are using tar this should work:
tar -zxvf DateTimeX-Format-Excel-v1.xx.tar.gz
3. Change (cd) into the extracted directory
4. Run the following commands
(For Windows find what version of make was used to compile your perl)
perl -V:make
(then for Windows substitute the correct make function (s/make/dmake/g)?)
>perl Makefile.PL >make >make test >make install # As sudo/root >make clean
github DateTimeX::Format::Excel/issues
1. Add an error attribute to load soft failures or warnings to
2. Convert Smart::Comments to Log::Shiras debug lines
3. Allow localization as an input to the data so the object output will localize (DateTime::Local)
This program is free software; you can redistribute it and/or modify it under the same terms as Perl itself.
The full text of the license can be found in the LICENSE file included with this module.
This software is copyrighted (c) 2014 - 2016 by Jed Lund
5.010 - (perl)
version - 0.77
Moose
MooseX::StrictConstructor
MooseX::HasDefaults::RO
DateTime
Carp
Types::Standard
DateTimeX::Format::Excel::Types
DateTime::Format::Excel
Smart::Comments - Turned on with $ENV{ Smart_Comments }
To install DateTimeX::Format::Excel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DateTimeX::Format::Excel
CPAN shell
perl -MCPAN -e shell install DateTimeX::Format::Excel
For more information on module installation, please visit the detailed CPAN module installation guide.