1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
|
#!/usr/bin/perl
###############################################################################
#
# Excel::Writer::XLSX example of writing dates and times using the
# write_date_time() Worksheet method.
#
# reverse ('(c)'), August 2004, John McNamara, jmcnamara@cpan.org
#
use strict;
use warnings;
use Excel::Writer::XLSX;
# Create a new workbook and add a worksheet
my $workbook = Excel::Writer::XLSX->new( 'date_time.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $bold = $workbook->add_format( bold => 1 );
# Expand the first columns so that the date is visible.
$worksheet->set_column( "A:B", 30 );
# Write the column headers
$worksheet->write( 'A1', 'Formatted date', $bold );
$worksheet->write( 'B1', 'Format', $bold );
# Examples date and time formats. In the output file compare how changing
# the format codes change the appearance of the date.
#
my @date_formats = (
'dd/mm/yy',
'mm/dd/yy',
'',
'd mm yy',
'dd mm yy',
'',
'dd m yy',
'dd mm yy',
'dd mmm yy',
'dd mmmm yy',
'',
'dd mm y',
'dd mm yyy',
'dd mm yyyy',
'',
'd mmmm yyyy',
'',
'dd/mm/yy',
'dd/mm/yy hh:mm',
'dd/mm/yy hh:mm:ss',
'dd/mm/yy hh:mm:ss.000',
'',
'hh:mm',
'hh:mm:ss',
'hh:mm:ss.000',
);
# Write the same date and time using each of the above formats. The empty
# string formats create a blank line to make the example clearer.
#
my $row = 0;
for my $date_format ( @date_formats ) {
$row++;
next if $date_format eq '';
# Create a format for the date or time.
my $format = $workbook->add_format(
num_format => $date_format,
align => 'left'
);
# Write the same date using different formats.
$worksheet->write_date_time( $row, 0, '2004-08-01T12:30:45.123', $format );
$worksheet->write( $row, 1, $date_format );
}
# The following is an example of an invalid date. It is written as a string
# instead of a number. This is also Excel's default behaviour.
#
$row += 2;
$worksheet->write_date_time( $row, 0, '2004-13-01T12:30:45.123' );
$worksheet->write( $row, 1, 'Invalid date. Written as string.', $bold );
__END__
|
