File Coverage

blib/lib/DateTimeX/Format/Excel.pm
Criterion Covered Total %
statement 76 77 98.7
branch 15 16 93.7
condition 4 6 66.6
subroutine 18 18 100.0
pod 2 2 100.0
total 115 119 96.6


line stmt bran cond sub pod time code
1             package DateTimeX::Format::Excel;
2             our $AUTHORITY = 'cpan:JANDREW';
3 3     3   300323 use version 0.77; our $VERSION = version->declare("v0.12.46");
  3         3672  
  3         15  
4 3     3   222 use 5.010;
  3         9  
5 3     3   8 use strict;
  3         6  
  3         45  
6 3     3   8 use warnings;
  3         2  
  3         63  
7 3     3   9 use List::Util 1.33;
  3         41  
  3         117  
8 3     3   1364 use Moose 2.1213;
  3         792288  
  3         18  
9 3     3   14176 use MooseX::StrictConstructor;
  3         39451  
  3         8  
10 3     3   17714 use MooseX::HasDefaults::RO;
  3         13359  
  3         9  
11 3     3   18629 use DateTime;
  3         127805  
  3         86  
12 3     3   28 use Carp qw( cluck );
  3         2  
  3         135  
13 3     3   1412 use Types::Standard -types;
  3         137609  
  3         23  
14             if( $ENV{ Smart_Comments } ){
15 3     3   8060 use Smart::Comments -ENV;
  3         23226  
  3         19  
16             ### Smart-Comments turned on for DateTimeX-Format-Excel ...
17             }
18 3     3   2177 use lib '../../../lib',;
  3         3  
  3         18  
19 3         36 use DateTimeX::Format::Excel::Types 0.012 qw(
20             DateTimeHash
21             DateTimeInstance
22             HashToDateTime
23             is_ExcelEpoch
24             ExcelEpoch
25             SystemName
26 3     3   1372 );
  3         53  
27              
28             #########1 Dispatch Tables 3#########4#########5#########6#########7#########8#########9
29              
30             my $input_scrub ={
31             win_excel =>{
32             action => sub{
33             ### <where> - Reached win_excel scrub with: @_
34             if(int( $_[0] ) == 60){
35             cluck "-1900-February-29- is not a real date (contrary to excel implementation)";
36             }elsif($_[0] == 0){
37             cluck "-1900-January-0- is not a real date (contrary to excel implementation)";
38             }
39             ### <where> - Finished testing Lotus 123 date error warnings ...
40             my $return =(
41             (int( $_[0] ) > 60) ? ($_[0] - 1) :
42             (int( $_[0] ) == 0) ? ($_[0] + 1) : $_[0] );
43             ### <where> - updated date: $return
44             return $return;
45             },
46             output => sub{
47             ### <where> - Reached win_excel output with: @_
48             my $return =( (defined( $_[0] ) and int( $_[0] ) > 59) ? ($_[0] + 1) : $_[0] );
49             if( defined( $return ) and $return < 1 ){
50             $return = undef;
51             }
52             ### <where> - updated date: $return
53             return $return;
54             },
55             date_time_hash =>{
56             year => 1899,# actually 1900
57             month => 12,# actually 01
58             day => 31,# actually 00
59             },
60             },
61             apple_excel =>{
62             date_time_hash =>{
63             year => 1904,
64             month => 1,
65             day => 1,
66             },
67             }
68             };
69              
70             #########1 Public Attributes 3#########4#########5#########6#########7#########8#########9
71              
72             has system_type =>(
73             isa => SystemName,
74             reader => 'get_system_type',
75             writer => 'set_system_type',
76             default => 'win_excel',
77             trigger => \&_set_system,
78             );
79              
80             #########1 Public Methods 3#########4#########5#########6#########7#########8#########9
81              
82             sub parse_datetime{
83 36     36 1 15898 my ( $self, $date_num, $timezone_flag, $timezone ) = @_;
84             ### <where> - Reached parse_datetime for: $date_num
85 36 100       97 if( !is_ExcelEpoch( $date_num ) ){
86             ### <where> - not and excel epoch: $date_num
87 1         12 return $date_num;
88             }
89             ### <where> - Passed the type constraint ...
90 35 100       1441 if( my $action = $input_scrub->{$self->get_system_type}->{action} ){
91             ### <where> - There is an action: $action
92             ### <where> - Using system name: $self->get_system_type
93 28         59 $date_num = $action->( $date_num );
94             }
95             ### <where> - Updated date num: $date_num
96 35         178 $date_num =~ /^ (\d+ (?: (\.\d+ ) )? ) $/x;
97 35         46 my $excel_days = $1;
98 35         34 my $excel_secs = $2;
99             ### <where> - Excel added days: $excel_days
100             ### <where> - Excel seconds: $excel_secs
101 35         1117 my $dt = $self->_get_epoch_start->clone();
102             ### <where> - DateTime: $dt
103 35 100       339 if(defined $excel_secs){
104 7         19 $excel_secs = $excel_secs * (60*60*24);# Seconds in most days
105 7         14 my $excel_nanoseconds = ($excel_secs - int($excel_secs)) * 1_000_000_000;
106             ### <where> - Excel days: $excel_days
107             ### <where> - Excel seconds: $excel_secs
108             ### <where> - Excel nano seconds: $excel_nanoseconds
109 7         21 $dt->add( days => $excel_days,
110             seconds => $excel_secs,
111             nanoseconds => $excel_nanoseconds);
112             } else {
113             ### <where> - No seconds in the epoch ...
114 28         67 $dt->add( days => $excel_days );
115             }
116 35 50 33     14517 if( $timezone_flag and $timezone_flag eq 'time_zone' ){
117             ### <where> - Setting timezone to: $timezone
118 0         0 $dt->set_time_zone( $timezone );
119             };
120             ### <where> - DateTime: $dt
121              
122 35         129 return $dt;
123             }
124              
125             sub format_datetime{
126 156     156 1 109950 my ( $self, $date_time ) = @_;
127             ### <where> - Reached format_datetime with: $date_time
128 156         331 DateTimeInstance->( $date_time );
129 156         8839 my $base = $self->_get_epoch_start->clone();
130 156         1413 my $test = DateTime->compare_ignore_floating( $date_time, $base );
131             ### <where> - DateTime base is: $base
132             ### <where> - Using system name: $self->get_system_type
133             ### <where> - Test result: $test
134 156         3967 my $excel = undef;
135 156         135 my $return_string = 0;
136 156 100       226 if( $test < 0 ){
137 3         6 $return_string = 1;
138             }else{
139 153         267 $excel = $date_time->jd - $base->jd;
140             }
141             ### <where> - Initial excel epoch: $excel
142 156 100 100     6692 if( defined $excel and my $action = $input_scrub->{$self->get_system_type}->{output} ){
143             ### <where> - There is an action: $action
144             ### <where> - For: $self->get_system_type
145 85         124 $excel = $action->( $excel );
146 85 100       134 $return_string = 1 if !defined $excel;
147             }
148             ### <where> - Should return a date string: $return_string
149             ### <where> - Final excel epoch: $excel
150             ### <where> - Original DateTime: $date_time
151              
152 156 100       835 return ( $return_string ) ? $date_time : $excel;
153             }
154              
155             #########1 Private Attributes 3#########4#########5#########6#########7#########8#########9
156              
157             has _epoch_start =>(
158             isa => DateTimeInstance->plus_coercions( HashToDateTime ),
159             writer => '_set_epoch_start',
160             reader => '_get_epoch_start',
161             coerce => 1,
162             default => sub{ DateTime->new(
163             $input_scrub->{win_excel}->{date_time_hash}
164             ) },
165             );
166              
167             #########1 Private Methods 3#########4#########5#########6#########7#########8#########9
168              
169             sub _set_system{
170 4     4   7 my ( $self, $system_type ) = @_;
171 4         127 $self->_set_epoch_start( $input_scrub->{$system_type}->{date_time_hash} );
172 4         24 return $system_type;
173             }
174              
175             #########1 Phinish 3#########4#########5#########6#########7#########8#########9
176              
177 3     3   3120 no Moose;
  3         4  
  3         17  
178             __PACKAGE__->meta->make_immutable(
179             inline_constructor => 0,
180             );
181              
182             1;
183              
184             #########1 Documentation 3#########4#########5#########6#########7#########8#########9
185             __END__
186              
187             =head1 NAME
188              
189             DateTimeX::Format::Excel - Microsofty conversion of Excel epochs
190              
191             =begin html
192              
193             <a href="https://www.perl.org">
194             <img src="https://img.shields.io/badge/perl-5.10+-brightgreen.svg" alt="perl version">
195             </a>
196              
197             <a href="https://travis-ci.org/jandrew/DateTimeX-Format-Excel">
198             <img alt="Build Status" src="https://travis-ci.org/jandrew/DateTimeX-Format-Excel.png?branch=master" alt='Travis Build'/>
199             </a>
200              
201             <a href='https://coveralls.io/r/jandrew/DateTimeX-Format-Excel?branch=master'>
202             <img src='https://coveralls.io/repos/jandrew/DateTimeX-Format-Excel/badge.svg?branch=master' alt='Coverage Status' />
203             </a>
204              
205             <a href='https://github.com/jandrew/DateTimeX-Format-Excel'>
206             <img src="https://img.shields.io/github/tag/jandrew/DateTimeX-Format-Excel.svg?label=github level" alt="github level"/>
207             </a>
208              
209             <a href="https://metacpan.org/pod/DateTimeX::Format::Excel">
210             <img src="https://badge.fury.io/pl/DateTimeX-Format-Excel.svg?label=cpan version" alt="CPAN version" height="20">
211             </a>
212              
213             <a href='http://cpants.cpanauthors.org/dist/DateTimeX-Format-Excel'>
214             <img src='http://cpants.cpanauthors.org/dist/DateTimeX-Format-Excel.png' alt='kwalitee' height="20"/>
215             </a>
216              
217             =end html
218              
219             =head1 SYNOPSIS
220              
221             #!/usr/bin/env perl
222             use DateTimeX::Format::Excel;
223              
224             # From an Excel date number
225              
226             my $parser = DateTimeX::Format::Excel->new();
227             print $parser->parse_datetime( 25569 )->ymd ."\n";
228             my $datetime = $parser->parse_datetime( 37680 );
229             print $datetime->ymd() ."\n";
230             $datetime = $parser->parse_datetime( 40123.625 );
231             print $datetime->iso8601() ."\n";
232              
233             # And back to an Excel number from a DateTime object
234              
235             use DateTime;
236             my $dt = DateTime->new( year => 1979, month => 7, day => 16 );
237             my $daynum = $parser->format_datetime( $dt );
238             print $daynum ."\n";
239              
240             my $dt_with_time = DateTime->new( year => 2010, month => 7, day => 23
241             , hour => 18, minute => 20 );
242             my $parser_date = $parser->format_datetime( $dt_with_time );
243             print $parser_date ."\n";
244              
245             ###########################
246             # SYNOPSIS Screen Output
247             # 01: 1970-01-01
248             # 02: 2003-02-28
249             # 03: 2009-11-06T15:00:00
250             # 04: 29052
251             # 05: 40382.763888889
252             ###########################
253              
254             =head1 DESCRIPTION
255              
256             Excel uses a different system for its dates than most Unix programs.
257             This package allows you to convert between the Excel raw format and
258             and L<DateTime> objects, which can then be further converted via any
259             of the other L<DateTime::Format::*
260             |https://metacpan.org/search?q=DateTime%3A%3AFormat> modules, or just
261             with L<DateTime>'s methods. The L<DateTime::Format::Excel> module states
262             "we assume what Psion assumed for their Abacus / Sheet program". As a
263             consequence the output does not follow exactly the output of Excel.
264             Especially in the Windows range of 0-60. This module attempts to more
265             faithfully follow actual Microsoft Excel with a few notable exceptions.
266              
267             Excel has a few date quirks. First, it allows two different epochs. One
268             for the Windows world and one for the Apple world. The windows epoch
269             starts in 0-January-1900 and allows for 29-February-1900 (both non real
270             dates). Most of the explanations for the difference between windows
271             implementations and Apple implementations focus on the fact that there
272             was no leap year in 1900 L<(the Gregorian vs Julian calendars)
273             |http://en.wikipedia.org/wiki/Gregorian_calendar> and the Apple
274             version wanted to skip that issue. Both non real dates appear to have
275             been a known issue in the original design of VisiCalc that was carried
276             through Lotus 1-2-3 and into Excel for L<compatibility
277             |http://support.microsoft.com/kb/214326>. (Spreadsheets were arguably the
278             first personal computer killer app and Excel was a L<johnny come lately
279             |http://en.wikipedia.org/wiki/Lotus_1-2-3#VisiCalc> trying to gain an entry
280             into the market at the time.) The closest microsoft discussion I could find
281             on this issue is L<here|http://www.joelonsoftware.com/items/2006/06/16.html>.
282             In any case the apple version starts 1-January-1904. (counting from 0 while
283             also avoiding the leap year issue). In both cases the Windows and Apple
284             version use integers from the epoch start to represent days and the decimal
285             portion to represent a portion of a day. Both Windows and Apple Excel will
286             attempt to convert recognized date strings to an Excel epoch for storage with
287             the exception that any date prior to the epoch start will be stored as a
288             string. (31-December-1899 and earlier for Windows and 31-December-1903 and
289             earlier for Apple). Next, Excel does not allow for a time zone component of
290             each number. Finally, in the Windows version when dealing with epochs that
291             do not have a date component just a time component all values will fall
292             between 0 and 1 which is a non real date (0-January-1900).
293              
294             =head2 Caveat utilitor
295              
296             This explanation is not intended to justify Microsofts decisions with Excel
297             dates just replicate them as faithfully as possible. This module makes the
298             assumption that you already know if your date is a string or a number in Excel
299             and that you will handle string to DateTime conversions elsewhere. see
300             L<DateTime::Format::Flexible>. Any passed strings will die. (As a failure
301             of a L<Type::Tiny> test) This module also makes several unilateral decisions
302             to deal with corner cases. When a 0 date is requested to be converted to
303             DateTime it will use L<Carp> to cluck that it received a bad date and then
304             provide a DateTime object dated 1-January-1900 (Excel would provide
305             0-January-1900). If a value between 0 and 1 is requested to be converted to
306             a DateTime object the module will B<NOT> cluck and provide an object dated
307             1-January-1900 with the appropriate time component. All Apple times are provide
308             as 1-January-1904. Any requested numerical conversion for Windows >= 60 and
309             < 61 will cluck and provide a DateTime object dated 1-March-1900 (Excel would
310             provide 29-Febrary-1900). All requests for conversion of negative numbers to
311             DateTime objects will die . If a DateTime object is provided for conversion
312             to the Excel value and it falls earlier than 1-January-1900 for Windows and
313             1-January-1904 for Apple then the DateTime object itself will be returned.
314             If you accept the output of that L<method|/format_datetime( $date_time )>
315             as a scalar, DateTime will stringify itself and give you a text equivalent
316             date. For time zones you can L<pass|/parse_datetime( @arg_list )> a time zone
317             with the excel number for conversion to the DateTime object. In reverse,
318             the conversion to Excel Epoch uses the L<-E<gt>jd
319             |https://metacpan.org/pod/DateTime#dt-jd-dt-mjd> method for calculation so
320             the time zone is stripped out. No clone or duration calculations are provided
321             with this module. Finally this is a L<Moose> based module and does
322             not provide a functional interface. I<(Moose would allow it I just chose not
323             to for design purposes)>.
324              
325             The Types module for this package uses L<Type::Tiny> which can, in the background,
326             use L<Type::Tiny::XS>. While in general this is a good thing you will need to make
327             sure that Type::Tiny::XS is version 0.010 or newer since the older ones didn't support
328             the 'Optional' method.
329              
330             =head2 Attributes
331              
332             Data passed to new when creating an instance (parser). For modification of
333             these attributes see the listed L</Methods> of the instance.
334              
335             =head3 system_type
336              
337             =over
338              
339             B<Definition:> This attribute identifies whether the translation will be done
340             for Windows Excel => 'win_excel' or Apple Excel => 'apple_excel'.
341              
342             B<Default> win_excel (0-January-1900T00:00:00 = 0, range includes 29-February-1900)
343              
344             B<Range> win_excel|apple_excel (1-January-1904T00:00:00 = 0)
345              
346             =back
347              
348             =head2 Methods
349              
350             These include methods to adjust attributes as well as providing methods to
351             provide the conversion functionality of the module.
352              
353             =head3 get_system_type
354              
355             =over
356              
357             B<Definition:> This is the way to see whether the conversion is Windows or Apple based
358              
359             B<Accepts:>Nothing
360              
361             B<Returns:> win_excel|apple_excel
362              
363             =back
364              
365             =head3 set_system_type( $system )
366              
367             =over
368              
369             B<Definition:> This is the way to set the base epoch for the translator
370              
371             B<Accepts:> win_excel|apple_excel (see the L</DESCRIPTION> for details)
372              
373             B<Returns:> Nothing
374              
375             =back
376              
377             =head3 parse_datetime( @arg_list )
378              
379             =over
380              
381             B<Definition:> This is how positive excel numbers are translated to L<DateTime> objects
382              
383             B<Accepts:> @arg_list - the order is important!
384              
385             =over
386              
387             B<0. > $the_excel_number_for_translation - must be positive - no strings allowed
388              
389             B<1. > 'time_zone' (the only useful option - other values here will ignore position 2)
390              
391             B<2. > A recognizable time zone string or L<DateTime::TimeZone> object
392              
393             B<example: > ( 12345, time_zone => 'America/Los_Angeles' )
394              
395             =back
396              
397             B<Returns:> A DateTime object set to match the passed values. A floating time zone is default.
398              
399             =back
400              
401             =head3 format_datetime( $date_time )
402              
403             =over
404              
405             B<Definition:> This is how DateTime objects can be translated to Excel epoch numbers
406              
407             B<Accepts:> A L<DateTime> object
408              
409             B<Returns:> An excel epoch number or DateTime object if it is before the relevant epoch start.
410              
411             =back
412              
413             =head2 A note on text dates
414              
415             Dates saved in Excel prior to 1-January-1900 for Windows or 1-January-1904 for Apple are stored as text.
416             I suggest using L<Type::Tiny::Manual::Coercions/Chained Coercions>. Or use an Excel reader
417             that implements this for you like L<Spreadsheet::XLSX::Reader::LibXML> (self promotion).
418             Here is one possible way to integrate text and dates in the same field into a consistent DateTime
419             output. (I know it's a bit clunky but it's a place to start)
420              
421             my $system_lookup = {
422             '1900' => 'win_excel',
423             '1904' => 'apple_excel',
424             };
425             my @args_list = ( system_type => $system_lookup->{$workbook->get_epoch_year} );
426             my $converter = DateTimeX::Format::Excel->new( @args_list );
427             my $string_via = sub{
428             my $str = $_[0];
429             return DateTime::Format::Flexible->parse_datetime( $str );
430             };
431             my $num_via = sub{
432             my $num = $_[0];
433             return $converter->parse_datetime( $num );
434             };
435             my $date_time_from_value = Type::Coercion->new(
436             type_coercion_map => [ Num, $num_via, Str, $string_via, ],
437             );
438             my $date_time_type = Type::Tiny->new(
439             name => 'Custom_date_type',
440             constraint => sub{ ref($_) eq 'DateTime' },
441             coercion => $date_time_from_value,
442             );
443             my $string_type = Type::Tiny->new(
444             name => 'YYYYMMDD',
445             constraint => sub{
446             !$_ or (
447             $_ =~ /^\d{4}\-(\d{2})-(\d{2})$/ and
448             $1 > 0 and $1 < 13 and $2 > 0 and $2 < 32 )
449             },
450             coercion => Type::Coercion->new(
451             type_coercion_map =>[
452             $date_time_type->coercibles, sub{
453             my $tmp = $date_time_type->coerce( $_ );
454             $tmp->format_cldr( 'yyyy-MM-dd' )
455             },
456             ],
457             ),
458             );
459              
460              
461             =head1 THANKS
462              
463             Dave Rolsky (L<DROLSKY>) for kickstarting the DateTime project.
464             Iain Truskett, Dave Rolsky, and Achim Bursian for maintaining L<DateTime::Format::Excel>.
465             I used it heavily till I wrote this.
466             Peter (Stig) Edwards and Bobby Metz for contributing to L<DateTime::Format::Excel>.
467              
468             =head1 Build/Install from Source
469              
470             B<1.> Download a compressed file with the code
471              
472             B<2.> Extract the code from the compressed file. If you are using tar this should work:
473              
474             tar -zxvf DateTimeX-Format-Excel-v1.xx.tar.gz
475              
476             B<3.> Change (cd) into the extracted directory
477              
478             B<4.> Run the following commands
479              
480             =over
481              
482             (For Windows find what version of make was used to compile your perl)
483              
484             perl -V:make
485              
486             (then for Windows substitute the correct make function (s/make/dmake/g)?)
487              
488             =back
489              
490             >perl Makefile.PL
491              
492             >make
493              
494             >make test
495              
496             >make install # As sudo/root
497              
498             >make clean
499              
500             =head1 SUPPORT
501              
502             =over
503              
504             L<github DateTimeX::Format::Excel/issues|https://github.com/jandrew/DateTimeX-Format-Excel/issues>
505              
506             =back
507              
508             =head1 TODO
509              
510             =over
511              
512             B<1.> Add an error attribute to load soft failures or warnings to
513              
514             B<2.> Convert Smart::Comments to L<Log::Shiras|https://github.com/jandrew/Log-Shiras> debug lines
515              
516             B<3.> Allow localization as an input to the data so the object output will localize (DateTime::Local)
517              
518             =back
519              
520             =head1 AUTHOR
521              
522             =over
523              
524             =item Jed Lund
525              
526             =item jandrew@cpan.org
527              
528             =back
529              
530             =head1 COPYRIGHT
531              
532             This program is free software; you can redistribute
533             it and/or modify it under the same terms as Perl itself.
534              
535             The full text of the license can be found in the
536             LICENSE file included with this module.
537              
538             This software is copyrighted (c) 2014 - 2016 by Jed Lund
539              
540             =head1 DEPENDENCIES
541              
542             =over
543              
544             B<5.010> - (L<perl>)
545              
546             L<version> - 0.77
547              
548             L<Moose>
549              
550             L<MooseX::StrictConstructor>
551              
552             L<MooseX::HasDefaults::RO>
553              
554             L<DateTime>
555              
556             L<Carp>
557              
558             L<Types::Standard>
559              
560             L<DateTimeX::Format::Excel::Types>
561              
562             =back
563              
564             =head1 SEE ALSO
565              
566             =over
567              
568             L<DateTime::Format::Excel>
569              
570             L<Smart::Comments> - Turned on with $ENV{ Smart_Comments }
571              
572             =back
573              
574             =cut
575              
576             #########1#########2 main pod documentation end 5#########6#########7#########8#########9