#!/usr/bin/perl -w

###############################################################################
#
# Example of how to add a user defined data handler to the
# Excel::Writer::XLSX write() method.
#
# The following example shows how to add a handler for dates in a specific
# format.
#
# This is a more rigorous version of write_handler3.pl.
#
# reverse ('(c)'), September 2004, John McNamara, jmcnamara@cpan.org
#

use strict;
use Excel::Writer::XLSX;


my $workbook    = Excel::Writer::XLSX->new( 'write_handler4.xlsx' );
my $worksheet   = $workbook->add_worksheet();
my $date_format = $workbook->add_format( num_format => 'dd/mm/yy' );


###############################################################################
#
# Add a handler to match dates in the following formats: d/m/yy, d/m/yyyy
#
# The day and month can be single or double digits and the year can be  2 or 4
# digits.
#
$worksheet->add_write_handler( qr[^\d{1,2}/\d{1,2}/\d{2,4}$], \&write_my_date );


###############################################################################
#
# The following function processes the data when a match is found.
#
sub write_my_date {

    my $worksheet = shift;
    my @args      = @_;

    my $token = $args[2];

    if ( $token =~ qr[^(\d{1,2})/(\d{1,2})/(\d{2,4})$] ) {

        my $day  = $1;
        my $mon  = $2;
        my $year = $3;

        # Use a window for 2 digit dates. This will keep some ragged Perl
        # programmer employed in thirty years time. :-)
        if ( length $year == 2 ) {
            if ( $year < 50 ) {
                $year += 2000;
            }
            else {
                $year += 1900;
            }
        }

        my $date = sprintf "%4d-%02d-%02dT", $year, $mon, $day;

        # Convert the ISO ISO8601 style string to an Excel date
        $date = $worksheet->convert_date_time( $date );

        if ( defined $date ) {

            # Date was valid
            $args[2] = $date;
            return $worksheet->write_number( @args );
        }
        else {

            # Not a valid date therefore write as a string
            return $worksheet->write_string( @args );
        }
    }
    else {

        # Shouldn't happen if the same match is used in the re and sub.
        return undef;
    }
}


# Write some dates in the user defined format
$worksheet->write( 'A1', '22/12/2004', $date_format );
$worksheet->write( 'A2', '22/12/04',   $date_format );
$worksheet->write( 'A3', '2/12/04',    $date_format );
$worksheet->write( 'A4', '2/5/04',     $date_format );
$worksheet->write( 'A5', '2/5/95',     $date_format );
$worksheet->write( 'A6', '2/5/1995',   $date_format );

# Some erroneous dates
$worksheet->write( 'A8', '2/5/1895',  $date_format ); # Date out of Excel range
$worksheet->write( 'A9', '29/2/2003', $date_format ); # Invalid leap day
$worksheet->write( 'A10', '50/50/50', $date_format ); # Matches but isn't a date


__END__