1
package Spreadsheet::WriteExcel::Utility;
3
###############################################################################
5
# Utility - Helper functions for Spreadsheet::WriteExcel.
7
# Copyright 2000-2005, John McNamara, jmcnamara@cpan.org
13
use autouse 'Date::Calc' => qw(Delta_DHMS Decode_Date_EU Decode_Date_US);
14
use autouse 'Date::Manip' => qw(ParseDate Date_Init);
17
# Do all of the export preparation
18
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
20
# Row and column functions
30
# Date and Time functions
44
@EXPORT = (@rowcol, @dates);
57
Utility - Helper functions for Spreadsheet::WriteExcel.
64
This document refers to version 0.03 of Spreadsheet::WriteExcel::Utility, released March, 2002.
70
Functions to help with some common tasks when using Spreadsheet::WriteExcel.
72
These functions mainly relate to dealing with rows and columns in A1 notation and to handling dates and times.
74
use Spreadsheet::WriteExcel::Utility; # Import everything
76
($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
77
$str = xl_rowcol_to_cell(1, 2); # C2
78
$str = xl_inc_col('Z1' ); # AA1
79
$str = xl_dec_col('AA1' ); # Z1
81
$date = xl_date_list(2002, 1, 1); # 37257
82
$date = xl_parse_date("11 July 1997"); # 35622
83
$time = xl_parse_time('3:21:36 PM'); # 0.64
84
$date = xl_decode_date_EU("13 May 2002"); # 37389
91
This module provides a set of functions to help with some common tasks encountered when using the Spreadsheet::WriteExcel module. The two main categories of function are:
93
Row and column functions: these are used to deal with Excel's A1 representation of cells. The functions in this category are:
102
Date and Time functions: these are used to convert dates and times to the numeric format used by Excel. The functions in this category are:
112
All of these functions are exported by default. However, you can use import lists if you wish to limit the functions that are imported:
114
use Spreadsheet::WriteExcel::Utility; # Import everything
115
use Spreadsheet::WriteExcel::Utility qw(xl_date_list); # xl_date_list only
116
use Spreadsheet::WriteExcel::Utility qw(:rowcol); # Row/col functions
117
use Spreadsheet::WriteExcel::Utility qw(:dates); # Date functions
121
=head1 ROW AND COLUMN FUNCTIONS
124
Spreadsheet::WriteExcel supports two forms of notation to designate the position of cells: Row-column notation and A1 notation.
126
Row-column notation uses a zero based index for both row and column while A1 notation uses the standard Excel alphanumeric sequence of column letter and 1-based row. Columns range from A to IV i.e. 0 to 255, rows range from 1 to 16384 in Excel 5 and 65536 in Excel 97. For example:
128
(0, 0) # The top left cell in row-column notation.
129
('A1') # The top left cell in A1 notation.
131
(1999, 29) # Row-column notation.
132
('AD2000') # The same cell in A1 notation.
134
Row-column notation is useful if you are referring to cells programmatically:
137
$worksheet->write($i, 0, 'Hello'); # Cells A1 to A10
140
A1 notation is useful for setting up a worksheet manually and for working with formulas:
142
$worksheet->write('H1', 200);
143
$worksheet->write('H2', '=H7+1');
145
The functions in the following sections can be used for dealing with A1 notation, for example:
147
($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
148
$str = xl_rowcol_to_cell(1, 2); # C2
151
Cell references in Excel can be either relative or absolute. Absolute references are prefixed by the dollar symbol as shown below:
153
A1 # Column and row are relative
154
$A1 # Column is absolute and row is relative
155
A$1 # Column is relative and row is absolute
156
$A$1 # Column and row are absolute
158
An absolute reference only has an effect if the cell is copied. Refer to the Excel documentation for further details. All of the following functions support absolute references.
165
###############################################################################
166
###############################################################################
168
=head2 xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
170
Parameters: $row: Integer
172
$row_absolute: Boolean (1/0) [optional, default is 0]
173
$col_absolute: Boolean (1/0) [optional, default is 0]
175
Returns: A string in A1 cell notation
178
This function converts a zero based row and column cell reference to a A1 style string:
180
$str = xl_rowcol_to_cell(0, 0); # A1
181
$str = xl_rowcol_to_cell(0, 1); # B1
182
$str = xl_rowcol_to_cell(1, 0); # A2
185
The optional parameters C<$row_absolute> and C<$col_absolute> can be used to indicate if the row or column is absolute:
187
$str = xl_rowcol_to_cell(0, 0, 0, 1); # $A1
188
$str = xl_rowcol_to_cell(0, 0, 1, 0); # A$1
189
$str = xl_rowcol_to_cell(0, 0, 1, 1); # $A$1
191
See L<ROW AND COLUMN FUNCTIONS> for an explanation of absolute cell references.
195
###############################################################################
197
# xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
199
sub xl_rowcol_to_cell {
203
my $row_abs = $_[2] ? '$' : '';
204
my $col_abs = $_[3] ? '$' : '';
207
my $int = int ($col / 26);
208
my $frac = $col % 26;
210
my $chr1 =''; # Most significant character in AA1
213
$chr1 = chr( ord('A') + $int -1 );
216
my $chr2 = chr( ord('A') + $frac );
218
# Zero index to 1-index
221
return $col_abs . $chr1 . $chr2 . $row_abs. $row;
227
###############################################################################
228
###############################################################################
230
=head2 xl_cell_to_rowcol($string)
233
Parameters: $string String in A1 format
235
Returns: List ($row, $col)
237
This function converts an Excel cell reference in A1 notation to a zero based row and column. The function will also handle Excel's absolute, C<$>, cell notation.
239
my ($row, $col) = xl_cell_to_rowcol('A1'); # (0, 0)
240
my ($row, $col) = xl_cell_to_rowcol('B1'); # (0, 1)
241
my ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
242
my ($row, $col) = xl_cell_to_rowcol('$C2' ); # (1, 2)
243
my ($row, $col) = xl_cell_to_rowcol('C$2' ); # (1, 2)
244
my ($row, $col) = xl_cell_to_rowcol('$C$2'); # (1, 2)
247
###############################################################################
249
# xl_cell_to_rowcol($string)
251
# Returns: ($row, $col, $row_absolute, $col_absolute)
253
# The $row_absolute and $col_absolute parameters aren't documented because they
254
# mainly used internally and aren't very useful to the user.
256
sub xl_cell_to_rowcol {
260
$cell =~ /(\$?)([A-I]?[A-Z])(\$?)(\d+)/;
262
my $col_abs = $1 eq "" ? 0 : 1;
264
my $row_abs = $3 eq "" ? 0 : 1;
267
# Convert base26 column string to number
268
# All your Base are belong to us.
269
my @chars = split //, $col;
274
my $char = pop(@chars); # LS char first
275
$col += (ord($char) -ord('A') +1) * (26**$expn);
279
# Convert 1-index to zero-index
283
return $row, $col, $row_abs, $col_abs;
289
###############################################################################
290
###############################################################################
292
=head2 xl_inc_row($string)
295
Parameters: $string, a string in A1 format
297
Returns: Incremented string in A1 format
299
This functions takes a cell reference string in A1 notation and increments the row. The function will also handle Excel's absolute, C<$>, cell notation:
301
my $str = xl_inc_row('A1' ); # A2
302
my $str = xl_inc_row('B$2' ); # B$3
303
my $str = xl_inc_row('$C3' ); # $C4
304
my $str = xl_inc_row('$D$4'); # $D$5
308
###############################################################################
310
# xl_inc_row($string)
315
my ($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
317
return xl_rowcol_to_cell(++$row, $col, $row_abs, $col_abs);
323
###############################################################################
324
###############################################################################
326
=head2 xl_dec_row($string)
329
Parameters: $string, a string in A1 format
331
Returns: Decremented string in A1 format
333
This functions takes a cell reference string in A1 notation and decrements the row. The function will also handle Excel's absolute, C<$>, cell notation:
335
my $str = xl_dec_row('A2' ); # A1
336
my $str = xl_dec_row('B$3' ); # B$2
337
my $str = xl_dec_row('$C4' ); # $C3
338
my $str = xl_dec_row('$D$5'); # $D$4
342
###############################################################################
344
# xl_dec_row($string)
346
# Decrements the row number of an Excel cell reference in A1 notation.
347
# For example C4 to C3
349
# Returns: a cell reference string.
354
my ($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
356
return xl_rowcol_to_cell(--$row, $col, $row_abs, $col_abs);
362
###############################################################################
363
###############################################################################
365
=head2 xl_inc_col($string)
368
Parameters: $string, a string in A1 format
370
Returns: Incremented string in A1 format
372
This functions takes a cell reference string in A1 notation and increments the column. The function will also handle Excel's absolute, C<$>, cell notation:
374
my $str = xl_inc_col('A1' ); # B1
375
my $str = xl_inc_col('Z1' ); # AA1
376
my $str = xl_inc_col('$B1' ); # $C1
377
my $str = xl_inc_col('$D$5'); # $E$5
381
###############################################################################
383
# xl_inc_col($string)
385
# Increments the column number of an Excel cell reference in A1 notation.
386
# For example C3 to D3
388
# Returns: a cell reference string.
393
my ($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
395
return xl_rowcol_to_cell($row, ++$col, $row_abs, $col_abs);
401
###############################################################################
402
###############################################################################
404
=head2 xl_dec_col($string)
406
Parameters: $string, a string in A1 format
408
Returns: Decremented string in A1 format
410
This functions takes a cell reference string in A1 notation and decrements the column. The function will also handle Excel's absolute, C<$>, cell notation:
412
my $str = xl_dec_col('B1' ); # A1
413
my $str = xl_dec_col('AA1' ); # Z1
414
my $str = xl_dec_col('$C1' ); # $B1
415
my $str = xl_dec_col('$E$5'); # $D$5
419
###############################################################################
421
# xl_dec_col($string)
426
my ($row, $col, $row_abs, $col_abs) = xl_cell_to_rowcol($cell);
428
return xl_rowcol_to_cell($row, --$col, $row_abs, $col_abs);
434
=head1 TIME AND DATE FUNCTIONS
437
Dates and times in Excel are represented by real numbers, for example "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
439
The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day in seconds.
441
The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and Excel for Macintosh uses 1904. The epochs are:
443
1900: 0 January 1900 i.e. 31 December 1899
446
Excel on Windows and the Macintosh will convert automatically between one system and the other. By default Spreadsheet::WriteExcel uses the 1900 format. To use the 1904 epoch you must use the C<set_1904()> workbook method, see the Spreadsheet::WriteExcel documentation.
448
There are two things to note about the 1900 date format. The first is that the epoch starts on 0 January 1900. The second is that the year 1900 is erroneously but deliberately treated as a leap year. Therefore you must add an extra day to dates after 28 February 1900. The functions in the following section will deal with these issues automatically. The reason for this anomaly is explained at http://support.microsoft.com/support/kb/articles/Q181/3/70.asp
450
Note, a date or time in Excel is like any other number. To display the number as a date you must apply a number format to it: Refer to the C<set_num_format()> method in the Spreadsheet::WriteExcel documentation:
452
$date = xl_date_list(2001, 1, 1, 12, 30);
453
$format->set_num_format('mmm d yyyy hh:mm AM/PM');
454
$worksheet->write('A1', $date , $format); # Jan 1 2001 12:30 AM
456
To use these functions you must install the C<Date::Manip> and C<Date::Calc> modules. See L<REQUIREMENTS> and the individual requirements of each functions.
458
See also the DateTime::Format::Excel module,http://search.cpan.org/search?dist=DateTime-Format-Excel which is part of the DateTime project and which deals specifically with converting dates and times to and from Excel's format.
464
###############################################################################
465
###############################################################################
467
=head2 xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
470
Parameters: $years: Integer
471
$months: Integer [optional, default is 1]
472
$days: Integer [optional, default is 1]
473
$hours: Integer [optional, default is 0]
474
$minutes: Integer [optional, default is 0]
475
$seconds: Float [optional, default is 0]
477
Returns: A number that represents an Excel date
478
or undef for an invalid date.
482
This function converts an array of data into a number that represents an Excel date. All of the parameters are optional except for C<$years>.
484
$date1 = xl_date_list(2002, 1, 2); # 2 Jan 2002
485
$date2 = xl_date_list(2002, 1, 2, 12); # 2 Jan 2002 12:00 pm
486
$date3 = xl_date_list(2002, 1, 2, 12, 30); # 2 Jan 2002 12:30 pm
487
$date4 = xl_date_list(2002, 1, 2, 12, 30, 45); # 2 Jan 2002 12:30:45 pm
489
This function can be used in conjunction with functions that parse date and time strings. In fact it is used in most of the following functions.
493
###############################################################################
495
# xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
499
return undef unless @_;
502
my $months = $_[1] || 1;
503
my $days = $_[2] || 1;
504
my $hours = $_[3] || 0;
505
my $minutes = $_[4] || 0;
506
my $seconds = $_[5] || 0;
508
my @date = ($years, $months, $days, $hours, $minutes, $seconds);
509
my @epoch = (1899, 12, 31, 0, 0, 0);
511
($days, $hours, $minutes, $seconds) = Delta_DHMS(@epoch, @date);
513
my $date = $days + ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
515
# Add a day for Excel's missing leap day in 1900
516
$date++ if ($date > 59);
522
###############################################################################
523
###############################################################################
525
=head2 xl_parse_time($string)
528
Parameters: $string, a textual representation of a time
530
Returns: A number that represents an Excel time
531
or undef for an invalid time.
533
This function converts a time string into a number that represents an Excel time. The following time formats are valid:
541
The meridian, AM or PM, is optional and case insensitive. A 24 hour time is assumed if the meridian is omitted
543
$time1 = xl_parse_time('12:18');
544
$time2 = xl_parse_time('12:18:14');
545
$time3 = xl_parse_time('12:18:14 AM');
546
$time4 = xl_parse_time('1:18:14 AM');
548
Time in Excel is expressed as a fraction of the day in seconds. Therefore you can calculate an Excel time as follows:
550
$time = ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
554
###############################################################################
556
# xl_parse_time($string)
562
if ($time =~ /(\d{1,2}):(\d\d):?((?:\d\d)(?:\.\d+)?)?(?:\s+)?(am|pm)?/i) {
566
my $seconds = $3 || 0;
567
my $meridian = lc($4) || '';
569
# Normalise midnight and midday
570
$hours = 0 if ($hours == 12 && $meridian ne '');
572
# Add 12 hours to the pm times. Note: 12.00 pm has been set to 0.00.
573
$hours += 12 if $meridian eq 'pm';
575
# Calculate the time as a fraction of 24 hours in seconds
576
return ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
580
return undef; # Not a valid time string
585
###############################################################################
586
###############################################################################
588
=head2 xl_parse_date($string)
591
Parameters: $string, a textual representation of a date and time
593
Returns: A number that represents an Excel date
594
or undef for an invalid date.
596
Requires: Date::Manip and Date::Calc
598
This function converts a date and time string into a number that represents an Excel date.
600
The parsing is performed using the C<ParseDate()> function of the Date::Manip module. Refer to the Date::Manip documentation for further information about the date and time formats that can be parsed. In order to use this function you will probably have to initialise some Date::Manip variables via the C<xl_parse_date_init()> function, see below.
602
xl_parse_date_init("TZ=GMT","DateFormat=non-US");
604
$date1 = xl_parse_date("11/7/97");
605
$date2 = xl_parse_date("Friday 11 July 1997");
606
$date3 = xl_parse_date("10:30 AM Friday 11 July 1997");
607
$date4 = xl_parse_date("Today");
608
$date5 = xl_parse_date("Yesterday");
610
Note, if you parse a string that represents a time but not a date this function will add the current date. If you want the time without the date you can do something like the following:
612
$time = xl_parse_date("10:30 AM");
617
###############################################################################
619
# xl_parse_date($string)
623
my $date = ParseDate($_[0]);
625
return undef unless defined $date;
627
# Unpack the return value from ParseDate()
628
my ($years, $months, $days, $hours, undef, $minutes, undef, $seconds) =
629
unpack("A4 A2 A2 A2 C A2 C A2", $date);
631
# Convert to Excel date
632
return xl_date_list($years, $months, $days, $hours, $minutes, $seconds);
638
###############################################################################
639
###############################################################################
641
=head2 xl_parse_date_init("variable=value", ...)
644
Parameters: A list of Date::Manip variable strings
646
Returns: A list of all the Date::Manip strings
648
Requires: Date::Manip
650
This function is used to initialise variables required by the Date::Manip module. You should call this function before calling C<xl_parse_date()>. It need only be called once.
652
This function is a thin wrapper for the C<Date::Manip::Date_Init()> function. You can use C<Date_Init()> directly if you wish. Refer to the Date::Manip documentation for further information.
654
xl_parse_date_init("TZ=MST","DateFormat=US");
655
$date1 = xl_parse_date("11/7/97"); # November 7th 1997
657
xl_parse_date_init("TZ=GMT","DateFormat=non-US");
658
$date1 = xl_parse_date("11/7/97"); # July 11th 1997
662
###############################################################################
664
# xl_parse_date_init("variable=value", ...)
666
sub xl_parse_date_init {
668
Date_Init(@_); # How lazy is that.
674
###############################################################################
675
###############################################################################
677
=head2 xl_decode_date_EU($string)
680
Parameters: $string, a textual representation of a date and time
682
Returns: A number that represents an Excel date
683
or undef for an invalid date.
687
This function converts a date and time string into a number that represents an Excel date.
689
The date parsing is performed using the C<Decode_Date_EU()> function of the Date::Calc module. Refer to the Date::Calc for further information about the date formats that can be parsed. Also note the following from the Date::Calc documentation:
691
"If the year is given as one or two digits only (i.e., if the year is less than 100), it is mapped to the window 1970 -2069 as follows":
693
0 E<lt>= $year E<lt> 70 ==> $year += 2000;
694
70 E<lt>= $year E<lt> 100 ==> $year += 1900;
696
The time portion of the string is parsed using the C<xl_parse_time()> function described above.
698
Note: the EU in the function name means that a European date format is assumed if it is not clear from the string. See the first example below.
700
$date1 = xl_decode_date_EU("11/7/97"); #11 July 1997
701
$date2 = xl_decode_date_EU("Sat 12 Sept 1998");
702
$date3 = xl_decode_date_EU("4:30 AM Sat 12 Sept 1998");
706
###############################################################################
708
# xl_decode_date_EU($string)
710
sub xl_decode_date_EU {
712
return undef unless @_;
718
# Remove and decode the time portion of the string
719
if ($date =~ s/(\d{1,2}:\d\d:?(\d\d(\.\d+)?)?(\s+)?(am|pm)?)//i) {
720
$time = xl_parse_time($1);
721
return undef unless defined $time;
724
# Return if the string is now blank, i.e. it contained a time only.
725
return $time if $date =~ /^\s*$/;
727
# Decode the date portion of the string
728
@date = Decode_Date_EU($date);
729
return undef unless @date;
731
return xl_date_list(@date) + $time;
736
###############################################################################
737
###############################################################################
739
=head2 xl_decode_date_US($string)
742
Parameters: $string, a textual representation of a date and time
744
Returns: A number that represents an Excel date
745
or undef for an invalid date.
749
This function converts a date and time string into a number that represents an Excel date.
751
The date parsing is performed using the C<Decode_Date_US()> function of the Date::Calc module. Refer to the Date::Calc for further information about the date formats that can be parsed. Also note the following from the Date::Calc documentation:
753
"If the year is given as one or two digits only (i.e., if the year is less than 100), it is mapped to the window 1970 -2069 as follows":
755
0 <= $year < 70 ==> $year += 2000;
756
70 <= $year < 100 ==> $year += 1900;
758
The time portion of the string is parsed using the C<xl_parse_time()> function described above.
760
Note: the US in the function name means that an American date format is assumed if it is not clear from the string. See the first example below.
762
$date1 = xl_decode_date_US("11/7/97"); # 7 November 1997
763
$date2 = xl_decode_date_US("12 Sept Saturday 1998");
764
$date3 = xl_decode_date_US("4:30 AM 12 Sept Sat 1998");
768
###############################################################################
770
# xl_decode_date_US($string)
772
sub xl_decode_date_US {
774
return undef unless @_;
780
# Remove and decode the time portion of the string
781
if ($date =~ s/(\d{1,2}:\d\d:?(\d\d(\.\d+)?)?(\s+)?(am|pm)?)//i) {
782
$time = xl_parse_time($1);
783
return undef unless defined $time;
786
# Return if the string is now blank, i.e. it contained a time only.
787
return $time if $date =~ /^\s*$/;
789
# Decode the date portion of the string
790
@date = Decode_Date_US($date);
791
return undef unless @date;
793
return xl_date_list(@date) + $time;
799
###############################################################################
800
###############################################################################
802
=head2 xl_date_1904($date)
805
Parameters: $date, an Excel date with a 1900 epoch
807
Returns: an Excel date with a 1904 epoch or zero if
808
the $date is before 1904
811
This function converts an Excel date based on the 1900 epoch into a date based on the 1904 epoch.
814
$date1 = xl_date_list(2002, 1, 13); # 13 Jan 2002, 1900 epoch
815
$date2 = xl_date_1904($date1); # 13 Jan 2002, 1904 epoch
818
See also the C<set_1904()> workbook method in the Spreadsheet::WriteExcel documentation.
821
###############################################################################
823
# xl_decode_date_US($string)
827
my $date = $_[0] || 0;
847
The date and time functions require functions from the C<Date::Manip> and C<Date::Calc> modules. The required functions are "autoused" from these modules so that you do not have to install them unless you wish to use the date and time routines. Therefore it is possible to use the row and column functions without having C<Date::Manip> and C<Date::Calc> installed.
849
For more information about "autousing" refer to the documentation on the C<autouse> pragma.
855
When using the autoused functions from C<Date::Manip> and C<Date::Calc> on Perl 5.6.0 with C<-w> you will get a warning like this:
857
"Subroutine xxx redefined ..."
859
The current workaround for this is to put C<use warnings;> near the beginning of your program.
865
John McNamara jmcnamara@cpan.org
872
� MM-MMV, John McNamara.
874
All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.
added
removed
0CPAN/Spreadsheet-WriteExcel-2.15/lib/Spreadsheet/WriteExcel/Utility.pm
