File Content: write_handler4.pl #!/usr/bin/perl -w ############################################################################### # # Example of how to add a user defined data handler to the Spreadsheet:: # WriteExcel 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('�'), September 2004, John McNamara, jmcnamara@cpan.org # use strict; use Spreadsheet::WriteExcel; my $workbook = Spreadsheet::WriteExcel->new("write_handler4.xls"); 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__ Submit