File Coverage

blib/lib/Excel/Writer/XLSX/Utility.pm
Criterion Covered Total %
statement 93 130 71.5
branch 24 44 54.5
condition 14 24 58.3
subroutine 18 23 78.2
pod 16 17 94.1
total 165 238 69.3


line stmt bran cond sub pod time code
1              
2             ###############################################################################
3             #
4             # Utility - Helper functions for Excel::Writer::XLSX.
5             #
6             #
7             # Used in conjunction with Excel::Writer::XLSX
8             #
9             # Copyright 2000-2021, John McNamara, jmcnamara@cpan.org
10             #
11             # Documentation after __END__
12             #
13              
14             # perltidy with the following options: -mbl=2 -pt=0 -nola
15              
16             use 5.008002;
17 1141     1141   27540 use strict;
  1141         4285  
18 1141     1141   5184 use Exporter;
  1141         2055  
  1141         25160  
19 1141     1141   4770 use warnings;
  1141         4404  
  1141         31427  
20 1141     1141   6025 use autouse 'Date::Calc' => qw(Delta_DHMS Decode_Date_EU Decode_Date_US);
  1141         3110  
  1141         41271  
21 1141     1141   385792 use autouse 'Date::Manip' => qw(ParseDate Date_Init);
  1141         679372  
  1141         6925  
22 1141     1141   101120  
  1141         2089  
  1141         7365  
23             our $VERSION = '1.09';
24              
25             # Row and column functions
26             my @rowcol = qw(
27             xl_rowcol_to_cell
28             xl_cell_to_rowcol
29             xl_col_to_name
30             xl_range
31             xl_range_formula
32             xl_inc_row
33             xl_dec_row
34             xl_inc_col
35             xl_dec_col
36             );
37              
38             # Date and Time functions
39             my @dates = qw(
40             xl_date_list
41             xl_date_1904
42             xl_parse_time
43             xl_parse_date
44             xl_parse_date_init
45             xl_decode_date_EU
46             xl_decode_date_US
47             );
48              
49             our @ISA = qw(Exporter);
50             our @EXPORT_OK = ();
51             our @EXPORT = ( @rowcol, @dates, 'quote_sheetname' );
52             our %EXPORT_TAGS = (
53             rowcol => \@rowcol,
54             dates => \@dates
55             );
56              
57              
58             ###############################################################################
59             #
60             # xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
61             #
62              
63             my $row = $_[0] + 1; # Change from 0-indexed to 1 indexed.
64             my $col = $_[1];
65 7624     7624 1 120119 my $row_abs = $_[2] ? '$' : '';
66 7624         9403 my $col_abs = $_[3] ? '$' : '';
67 7624 100       12416  
68 7624 100       11425  
69             my $col_str = xl_col_to_name( $col, $col_abs );
70              
71 7624         11620 return $col_str . $row_abs . $row;
72             }
73 7624         17858  
74              
75             ###############################################################################
76             #
77             # xl_cell_to_rowcol($string)
78             #
79             # Returns: ($row, $col, $row_absolute, $col_absolute)
80             #
81             # The $row_absolute and $col_absolute parameters aren't documented because they
82             # mainly used internally and aren't very useful to the user.
83             #
84              
85             my $cell = shift;
86              
87             return ( 0, 0, 0, 0 ) unless $cell;
88 3065     3065 1 119790  
89             $cell =~ /(\$?)([A-Z]{1,3})(\$?)(\d+)/;
90 3065 100       5403  
91             my $col_abs = $1 eq "" ? 0 : 1;
92 3064         9397 my $col = $2;
93             my $row_abs = $3 eq "" ? 0 : 1;
94 3064 100       6884 my $row = $4;
95 3064         4327  
96 3064 100       5023 # Convert base26 column string to number
97 3064         4047 # All your Base are belong to us.
98             my @chars = split //, $col;
99             my $expn = 0;
100             $col = 0;
101 3064         5695  
102 3064         3718 while ( @chars ) {
103 3064         3741 my $char = pop( @chars ); # LS char first
104             $col += ( ord( $char ) - ord( 'A' ) + 1 ) * ( 26**$expn );
105 3064         5523 $expn++;
106 4110         5460 }
107 4110         6280  
108 4110         6521 # Convert 1-index to zero-index
109             $row--;
110             $col--;
111              
112 3064         4571 return $row, $col, $row_abs, $col_abs;
113 3064         3525 }
114              
115 3064         8230  
116             ###############################################################################
117             #
118             # xl_col_to_name($col, $col_absolute)
119             #
120              
121             my $col = $_[0];
122             my $col_abs = $_[1] ? '$' : '';
123             my $col_str = '';
124              
125 8398     8398 1 104359 # Change from 0-indexed to 1 indexed.
126 8398 100       13118 $col++;
127 8398         10037  
128             while ( $col ) {
129              
130 8398         9659 # Set remainder from 1 .. 26
131             my $remainder = $col % 26 || 26;
132 8398         13962  
133             # Convert the $remainder to a character. C-ishly.
134             my $col_letter = chr( ord( 'A' ) + $remainder - 1 );
135 10503   100     18380  
136             # Accumulate the column letters, right to left.
137             $col_str = $col_letter . $col_str;
138 10503         16916  
139             # Get the next order of magnitude.
140             $col = int( ( $col - 1 ) / 26 );
141 10503         15709 }
142              
143             return $col_abs . $col_str;
144 10503         23684 }
145              
146              
147 8398         16399 ###############################################################################
148             #
149             # xl_range($row_1, $row_2, $col_1, $col_2, $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2)
150             #
151              
152             my ( $row_1, $row_2, $col_1, $col_2 ) = @_[ 0 .. 3 ];
153             my ( $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2 ) = @_[ 4 .. 7 ];
154              
155             my $range1 = xl_rowcol_to_cell( $row_1, $col_1, $row_abs_1, $col_abs_1 );
156             my $range2 = xl_rowcol_to_cell( $row_2, $col_2, $row_abs_2, $col_abs_2 );
157 409     409 1 6722  
158 409         863 if ($range1 eq $range2) {
159             return $range1;
160 409         914 }
161 409         910 else {
162             return $range1 . ':' . $range2;
163 409 100       1017 }
164 142         448 }
165              
166              
167 267         922 ###############################################################################
168             #
169             # xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
170             #
171              
172             my ( $sheetname, $row_1, $row_2, $col_1, $col_2 ) = @_;
173              
174             $sheetname = quote_sheetname( $sheetname );
175              
176             my $range = xl_range( $row_1, $row_2, $col_1, $col_2, 1, 1, 1, 1 );
177              
178 40     40 1 2976 return '=' . $sheetname . '!' . $range
179             }
180 40         98  
181              
182 40         125 ###############################################################################
183             #
184 40         128 # quote_sheetname()
185             #
186             # Sheetnames used in references should be quoted if they contain any spaces,
187             # special characters or if they look like something that isn't a sheet name.
188             #
189              
190             my $sheetname = $_[0];
191              
192             # Use Excel's conventions and quote the sheet name if it contains any
193             # non-word character or if it isn't already quoted.
194             if ( $sheetname =~ /\W/ && $sheetname !~ /^'/ ) {
195             # Double quote any single quotes.
196             $sheetname =~ s/'/''/g;
197 148     148 0 246 $sheetname = q(') . $sheetname . q(');
198             }
199              
200             return $sheetname;
201 148 100 100     613 }
202              
203 26         63  
204 26         78 ###############################################################################
205             #
206             # xl_inc_row($string)
207 148         316 #
208              
209             my $cell = shift;
210             my ( $row, $col, $row_abs, $col_abs ) = xl_cell_to_rowcol( $cell );
211              
212             return xl_rowcol_to_cell( ++$row, $col, $row_abs, $col_abs );
213             }
214              
215              
216             ###############################################################################
217 4     4 1 3287 #
218 4         12 # xl_dec_row($string)
219             #
220 4         10 # Decrements the row number of an Excel cell reference in A1 notation.
221             # For example C4 to C3
222             #
223             # Returns: a cell reference string.
224             #
225              
226             my $cell = shift;
227             my ( $row, $col, $row_abs, $col_abs ) = xl_cell_to_rowcol( $cell );
228              
229             return xl_rowcol_to_cell( --$row, $col, $row_abs, $col_abs );
230             }
231              
232              
233             ###############################################################################
234             #
235 4     4 1 1798 # xl_inc_col($string)
236 4         8 #
237             # Increments the column number of an Excel cell reference in A1 notation.
238 4         8 # For example C3 to D3
239             #
240             # Returns: a cell reference string.
241             #
242              
243             my $cell = shift;
244             my ( $row, $col, $row_abs, $col_abs ) = xl_cell_to_rowcol( $cell );
245              
246             return xl_rowcol_to_cell( $row, ++$col, $row_abs, $col_abs );
247             }
248              
249              
250             ###############################################################################
251             #
252             # xl_dec_col($string)
253 4     4 1 2365 #
254 4         10  
255             my $cell = shift;
256 4         12 my ( $row, $col, $row_abs, $col_abs ) = xl_cell_to_rowcol( $cell );
257              
258             return xl_rowcol_to_cell( $row, --$col, $row_abs, $col_abs );
259             }
260              
261              
262             ###############################################################################
263             #
264             # xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
265             #
266 4     4 1 1816  
267 4         7 return undef unless @_;
268              
269 4         10 my $years = $_[0];
270             my $months = $_[1] || 1;
271             my $days = $_[2] || 1;
272             my $hours = $_[3] || 0;
273             my $minutes = $_[4] || 0;
274             my $seconds = $_[5] || 0;
275              
276             my @date = ( $years, $months, $days, $hours, $minutes, $seconds );
277             my @epoch = ( 1899, 12, 31, 0, 0, 0 );
278              
279 0 0   0 1 0 ( $days, $hours, $minutes, $seconds ) = Delta_DHMS( @epoch, @date );
280              
281 0         0 my $date =
282 0   0     0 $days + ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );
283 0   0     0  
284 0   0     0 # Add a day for Excel's missing leap day in 1900
285 0   0     0 $date++ if ( $date > 59 );
286 0   0     0  
287             return $date;
288 0         0 }
289 0         0  
290              
291 0         0 ###############################################################################
292             #
293 0         0 # xl_parse_time($string)
294             #
295              
296             my $time = shift;
297 0 0       0  
298             if ( $time =~ /(\d+):(\d\d):?((?:\d\d)(?:\.\d+)?)?(?:\s+)?(am|pm)?/i ) {
299 0         0  
300             my $hours = $1;
301             my $minutes = $2;
302             my $seconds = $3 || 0;
303             my $meridian = lc( $4 || '' );
304              
305             # Normalise midnight and midday
306             $hours = 0 if ( $hours == 12 && $meridian ne '' );
307              
308             # Add 12 hours to the pm times. Note: 12.00 pm has been set to 0.00.
309 14     14 1 6834 $hours += 12 if $meridian eq 'pm';
310              
311 14 100       94 # Calculate the time as a fraction of 24 hours in seconds
312             return ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );
313 13         28  
314 13         24 }
315 13   100     35 else {
316 13   100     40 return undef; # Not a valid time string
317             }
318             }
319 13 100 100     44  
320              
321             ###############################################################################
322 13 100       23 #
323             # xl_parse_date($string)
324             #
325 13         54  
326             my $date = ParseDate( $_[0] );
327              
328             # Unpack the return value from ParseDate()
329 1         3 my ( $years, $months, $days, $hours, undef, $minutes, undef, $seconds ) =
330             unpack( "A4 A2 A2 A2 C A2 C A2",
331             $date );
332              
333             # Convert to Excel date
334             return xl_date_list( $years, $months, $days, $hours, $minutes, $seconds );
335             }
336              
337              
338             ###############################################################################
339             #
340 0     0 1 0 # xl_parse_date_init("variable=value", ...)
341             #
342              
343 0         0 Date_Init( @_ ); # How lazy is that.
344             }
345              
346              
347             ###############################################################################
348 0         0 #
349             # xl_decode_date_EU($string)
350             #
351              
352             return undef unless @_;
353              
354             my $date = shift;
355             my @date;
356             my $time = 0;
357              
358 0     0 1 0 # Remove and decode the time portion of the string
359             if ( $date =~ s/(\d+:\d\d:?(\d\d(\.\d+)?)?(\s+)?(am|pm)?)//i ) {
360             $time = xl_parse_time( $1 );
361             }
362              
363             # Return if the string is now blank, i.e. it contained a time only.
364             return $time if $date =~ /^\s*$/;
365              
366             # Decode the date portion of the string
367             @date = Decode_Date_EU( $date );
368 0 0   0 1 0 return undef unless @date;
369              
370 0         0 return xl_date_list( @date ) + $time;
371 0         0 }
372 0         0  
373              
374             ###############################################################################
375 0 0       0 #
376 0         0 # xl_decode_date_US($string)
377             #
378              
379             return undef unless @_;
380 0 0       0  
381             my $date = shift;
382             my @date;
383 0         0 my $time = 0;
384 0 0       0  
385             # Remove and decode the time portion of the string
386 0         0 if ( $date =~ s/(\d+:\d\d:?(\d\d(\.\d+)?)?(\s+)?(am|pm)?)//i ) {
387             $time = xl_parse_time( $1 );
388             }
389              
390             # Return if the string is now blank, i.e. it contained a time only.
391             return $time if $date =~ /^\s*$/;
392              
393             # Decode the date portion of the string
394             @date = Decode_Date_US( $date );
395             return undef unless @date;
396 0 0   0 1 0  
397             return xl_date_list( @date ) + $time;
398 0         0 }
399 0         0  
400 0         0  
401             ###############################################################################
402             #
403 0 0       0 # xl_decode_date_US($string)
404 0         0 #
405              
406             my $date = $_[0] || 0;
407              
408 0 0       0 if ( $date < 1462 ) {
409              
410             # before 1904
411 0         0 $date = 0;
412 0 0       0 }
413             else {
414 0         0 $date -= 1462;
415             }
416              
417             return $date;
418             }
419              
420              
421             1;
422              
423              
424 4   100 4 1 2343  
425             =head1 NAME
426 4 100       9  
427             Utility - Helper functions for L<Excel::Writer::XLSX>.
428              
429 1         3 =head1 SYNOPSIS
430              
431             Functions to help with some common tasks when using L<Excel::Writer::XLSX>.
432 3         5  
433             These functions mainly relate to dealing with rows and columns in A1 notation and to handling dates and times.
434              
435 4         8 use Excel::Writer::XLSX::Utility; # Import everything
436              
437             ($row, $col) = xl_cell_to_rowcol( 'C2' ); # (1, 2)
438             $str = xl_rowcol_to_cell( 1, 2 ); # C2
439             $str = xl_col_to_name( 702 ); # AAA
440             $str = xl_inc_col( 'Z1' ); # AA1
441             $str = xl_dec_col( 'AA1' ); # Z1
442              
443             $date = xl_date_list(2002, 1, 1); # 37257
444             $date = xl_parse_date( '11 July 1997' ); # 35622
445             $time = xl_parse_time( '3:21:36 PM' ); # 0.64
446             $date = xl_decode_date_EU( '13 May 2002' ); # 37389
447              
448             =head1 DESCRIPTION
449              
450             This module provides a set of functions to help with some common tasks encountered when using the L<Excel::Writer::XLSX> module. The two main categories of function are:
451              
452             Row and column functions: these are used to deal with Excel's A1 representation of cells. The functions in this category are:
453              
454             xl_rowcol_to_cell
455             xl_cell_to_rowcol
456             xl_col_to_name
457             xl_range
458             xl_range_formula
459             xl_inc_row
460             xl_dec_row
461             xl_inc_col
462             xl_dec_col
463              
464             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:
465              
466             xl_date_list
467             xl_date_1904
468             xl_parse_time
469             xl_parse_date
470             xl_parse_date_init
471             xl_decode_date_EU
472             xl_decode_date_US
473              
474             All of these functions are exported by default. However, you can use import lists if you wish to limit the functions that are imported:
475              
476             use Excel::Writer::XLSX::Utility; # Import everything
477             use Excel::Writer::XLSX::Utility qw(xl_date_list); # xl_date_list only
478             use Excel::Writer::XLSX::Utility qw(:rowcol); # Row/col functions
479             use Excel::Writer::XLSX::Utility qw(:dates); # Date functions
480              
481             =head1 ROW AND COLUMN FUNCTIONS
482              
483             L<Excel::Writer::XLSX> supports two forms of notation to designate the position of cells: Row-column notation and A1 notation.
484              
485             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 XFD, i.e. 0 to 16,383, rows range from 0 to 1,048,575 in Excel 2007+. For example:
486              
487             (0, 0) # The top left cell in row-column notation.
488             ('A1') # The top left cell in A1 notation.
489              
490             (1999, 29) # Row-column notation.
491             ('AD2000') # The same cell in A1 notation.
492              
493             Row-column notation is useful if you are referring to cells programmatically:
494              
495             for my $i ( 0 .. 9 ) {
496             $worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
497             }
498              
499             A1 notation is useful for setting up a worksheet manually and for working with formulas:
500              
501             $worksheet->write( 'H1', 200 );
502             $worksheet->write( 'H2', '=H7+1' );
503              
504             The functions in the following sections can be used for dealing with A1 notation, for example:
505              
506             ( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
507             $str = xl_rowcol_to_cell( 1, 2 ); # C2
508              
509              
510             Cell references in Excel can be either relative or absolute. Absolute references are prefixed by the dollar symbol as shown below:
511              
512             A1 # Column and row are relative
513             $A1 # Column is absolute and row is relative
514             A$1 # Column is relative and row is absolute
515             $A$1 # Column and row are absolute
516              
517             An absolute reference only makes a difference if the cell is copied. Refer to the Excel documentation for further details. All of the following functions support absolute references.
518              
519             =head2 xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
520              
521             Parameters: $row: Integer
522             $col: Integer
523             $row_absolute: Boolean (1/0) [optional, default is 0]
524             $col_absolute: Boolean (1/0) [optional, default is 0]
525              
526             Returns: A string in A1 cell notation
527              
528              
529             This function converts a zero based row and column cell reference to a A1 style string:
530              
531             $str = xl_rowcol_to_cell( 0, 0 ); # A1
532             $str = xl_rowcol_to_cell( 0, 1 ); # B1
533             $str = xl_rowcol_to_cell( 1, 0 ); # A2
534              
535              
536             The optional parameters C<$row_absolute> and C<$col_absolute> can be used to indicate if the row or column is absolute:
537              
538             $str = xl_rowcol_to_cell( 0, 0, 0, 1 ); # $A1
539             $str = xl_rowcol_to_cell( 0, 0, 1, 0 ); # A$1
540             $str = xl_rowcol_to_cell( 0, 0, 1, 1 ); # $A$1
541              
542             See above for an explanation of absolute cell references.
543              
544             =head2 xl_cell_to_rowcol($string)
545              
546              
547             Parameters: $string String in A1 format
548              
549             Returns: List ($row, $col)
550              
551             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.
552              
553             my ( $row, $col ) = xl_cell_to_rowcol('A1'); # (0, 0)
554             my ( $row, $col ) = xl_cell_to_rowcol('B1'); # (0, 1)
555             my ( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
556             my ( $row, $col ) = xl_cell_to_rowcol('$C2'); # (1, 2)
557             my ( $row, $col ) = xl_cell_to_rowcol('C$2'); # (1, 2)
558             my ( $row, $col ) = xl_cell_to_rowcol('$C$2'); # (1, 2)
559              
560             =head2 xl_col_to_name($col, $col_absolute)
561              
562             Parameters: $col: Integer
563             $col_absolute: Boolean (1/0) [optional, default is 0]
564              
565             Returns: A column string name.
566              
567              
568             This function converts a zero based column reference to a string:
569              
570             $str = xl_col_to_name(0); # A
571             $str = xl_col_to_name(1); # B
572             $str = xl_col_to_name(702); # AAA
573              
574              
575             The optional parameter C<$col_absolute> can be used to indicate if the column is absolute:
576              
577             $str = xl_col_to_name( 0, 0 ); # A
578             $str = xl_col_to_name( 0, 1 ); # $A
579             $str = xl_col_to_name( 1, 1 ); # $B
580              
581             =head2 xl_range($row_1, $row_2, $col_1, $col_2, $row_abs_1, $row_abs_2, $col_abs_1, $col_abs_2)
582              
583             Parameters: $sheetname String
584             $row_1: Integer
585             $row_2: Integer
586             $col_1: Integer
587             $col_2: Integer
588             $row_abs_1: Boolean (1/0) [optional, default is 0]
589             $row_abs_2: Boolean (1/0) [optional, default is 0]
590             $col_abs_1: Boolean (1/0) [optional, default is 0]
591             $col_abs_2: Boolean (1/0) [optional, default is 0]
592              
593             Returns: A worksheet range formula as a string.
594              
595             This function converts zero based row and column cell references to an A1 style range string:
596              
597             my $str = xl_range( 0, 9, 0, 0 ); # A1:A10
598             my $str = xl_range( 1, 8, 2, 2 ); # C2:C9
599             my $str = xl_range( 0, 3, 0, 4 ); # A1:E4
600             my $str = xl_range( 0, 3, 0, 4, 1 ); # A$1:E4
601             my $str = xl_range( 0, 3, 0, 4, 1, 1 ); # A$1:E$
602             my $str = xl_range( 0, 0, 0, 0 ); # A1
603              
604             =head2 xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
605              
606             Parameters: $sheetname String
607             $row_1: Integer
608             $row_2: Integer
609             $col_1: Integer
610             $col_2: Integer
611              
612             Returns: A worksheet range formula as a string.
613              
614             This function converts zero based row and column cell references to an A1 style formula string:
615              
616             my $str = xl_range_formula( 'Sheet1', 0, 9, 0, 0 ); # =Sheet1!$A$1:$A$10
617             my $str = xl_range_formula( 'Sheet2', 6, 65, 1, 1 ); # =Sheet2!$B$7:$B$66
618             my $str = xl_range_formula( 'New data', 1, 8, 2, 2 );# ='New data'!$C$2:$C$9
619              
620             This is useful for setting ranges in Chart objects:
621              
622             $chart->add_series(
623             categories => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
624             values => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
625             );
626              
627             # Which is the same as:
628              
629             $chart->add_series(
630             categories => '=Sheet1!$A$2:$A$10',
631             values => '=Sheet1!$B$2:$B$10',
632             );
633              
634             =head2 xl_inc_row($string)
635              
636              
637             Parameters: $string, a string in A1 format
638              
639             Returns: Incremented string in A1 format
640              
641             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:
642              
643             my $str = xl_inc_row( 'A1' ); # A2
644             my $str = xl_inc_row( 'B$2' ); # B$3
645             my $str = xl_inc_row( '$C3' ); # $C4
646             my $str = xl_inc_row( '$D$4' ); # $D$5
647              
648             =head2 xl_dec_row($string)
649              
650              
651             Parameters: $string, a string in A1 format
652              
653             Returns: Decremented string in A1 format
654              
655             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:
656              
657             my $str = xl_dec_row( 'A2' ); # A1
658             my $str = xl_dec_row( 'B$3' ); # B$2
659             my $str = xl_dec_row( '$C4' ); # $C3
660             my $str = xl_dec_row( '$D$5' ); # $D$4
661              
662             =head2 xl_inc_col($string)
663              
664              
665             Parameters: $string, a string in A1 format
666              
667             Returns: Incremented string in A1 format
668              
669             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:
670              
671             my $str = xl_inc_col( 'A1' ); # B1
672             my $str = xl_inc_col( 'Z1' ); # AA1
673             my $str = xl_inc_col( '$B1' ); # $C1
674             my $str = xl_inc_col( '$D$5' ); # $E$5
675              
676             =head2 xl_dec_col($string)
677              
678             Parameters: $string, a string in A1 format
679              
680             Returns: Decremented string in A1 format
681              
682             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:
683              
684             my $str = xl_dec_col( 'B1' ); # A1
685             my $str = xl_dec_col( 'AA1' ); # Z1
686             my $str = xl_dec_col( '$C1' ); # $B1
687             my $str = xl_dec_col( '$E$5' ); # $D$5
688              
689             =head1 TIME AND DATE FUNCTIONS
690              
691             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.
692              
693             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.
694              
695             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 Excel::Writer::XLSX documentation:
696              
697             $date = xl_date_list( 2001, 1, 1, 12, 30 );
698             $format->set_num_format( 'mmm d yyyy hh:mm AM/PM' );
699             $worksheet->write( 'A1', $date, $format ); # Jan 1 2001 12:30 AM
700              
701             The date handling functions below are supplied for historical reasons. In the current version of the module it is easier to just use the C<write_date_time()> function to write dates or times. See the DATES AND TIME IN EXCEL section of the main L<Excel::Writer::XLSX> documentation for details.
702              
703             In addition to using the functions below you must install the L<Date::Manip> and L<Date::Calc> modules. See L<REQUIREMENTS> and the individual requirements of each functions.
704              
705             For a C<DateTime.pm> solution see the L<DateTime::Format::Excel> module.
706              
707             =head2 xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
708              
709              
710             Parameters: $years: Integer
711             $months: Integer [optional, default is 1]
712             $days: Integer [optional, default is 1]
713             $hours: Integer [optional, default is 0]
714             $minutes: Integer [optional, default is 0]
715             $seconds: Float [optional, default is 0]
716              
717             Returns: A number that represents an Excel date
718             or undef for an invalid date.
719              
720             Requires: Date::Calc
721              
722             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>.
723              
724             $date1 = xl_date_list( 2002, 1, 2 ); # 2 Jan 2002
725             $date2 = xl_date_list( 2002, 1, 2, 12 ); # 2 Jan 2002 12:00 pm
726             $date3 = xl_date_list( 2002, 1, 2, 12, 30 ); # 2 Jan 2002 12:30 pm
727             $date4 = xl_date_list( 2002, 1, 2, 12, 30, 45 ); # 2 Jan 2002 12:30:45 pm
728              
729             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.
730              
731             =head2 xl_parse_time($string)
732              
733              
734             Parameters: $string, a textual representation of a time
735              
736             Returns: A number that represents an Excel time
737             or undef for an invalid time.
738              
739             This function converts a time string into a number that represents an Excel time. The following time formats are valid:
740              
741             hh:mm [AM|PM]
742             hh:mm [AM|PM]
743             hh:mm:ss [AM|PM]
744             hh:mm:ss.ss [AM|PM]
745              
746              
747             The meridian, AM or PM, is optional and case insensitive. A 24 hour time is assumed if the meridian is omitted.
748              
749             $time1 = xl_parse_time( '12:18' );
750             $time2 = xl_parse_time( '12:18:14' );
751             $time3 = xl_parse_time( '12:18:14 AM' );
752             $time4 = xl_parse_time( '1:18:14 AM' );
753              
754             Time in Excel is expressed as a fraction of the day in seconds. Therefore you can calculate an Excel time as follows:
755              
756             $time = ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );
757              
758             =head2 xl_parse_date($string)
759              
760              
761             Parameters: $string, a textual representation of a date and time
762              
763             Returns: A number that represents an Excel date
764             or undef for an invalid date.
765              
766             Requires: Date::Manip and Date::Calc
767              
768             This function converts a date and time string into a number that represents an Excel date.
769              
770             The parsing is performed using the C<ParseDate()> function of the L<Date::Manip> module. Refer to the C<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 C<Date::Manip> variables via the C<xl_parse_date_init()> function, see below.
771              
772             xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
773              
774             $date1 = xl_parse_date( "11/7/97" );
775             $date2 = xl_parse_date( "Friday 11 July 1997" );
776             $date3 = xl_parse_date( "10:30 AM Friday 11 July 1997" );
777             $date4 = xl_parse_date( "Today" );
778             $date5 = xl_parse_date( "Yesterday" );
779              
780             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:
781              
782             $time = xl_parse_date( "10:30 AM" );
783             $time -= int( $time );
784              
785             =head2 xl_parse_date_init("variable=value", ...)
786              
787              
788             Parameters: A list of Date::Manip variable strings
789              
790             Returns: A list of all the Date::Manip strings
791              
792             Requires: Date::Manip
793              
794             This function is used to initialise variables required by the L<Date::Manip> module. You should call this function before calling C<xl_parse_date()>. It need only be called once.
795              
796             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 C<Date::Manip> documentation for further information.
797              
798             xl_parse_date_init( "TZ=MST", "DateFormat=US" );
799             $date1 = xl_parse_date( "11/7/97" ); # November 7th 1997
800              
801             xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
802             $date1 = xl_parse_date( "11/7/97" ); # July 11th 1997
803              
804             =head2 xl_decode_date_EU($string)
805              
806              
807             Parameters: $string, a textual representation of a date and time
808              
809             Returns: A number that represents an Excel date
810             or undef for an invalid date.
811              
812             Requires: Date::Calc
813              
814             This function converts a date and time string into a number that represents an Excel date.
815              
816             The date parsing is performed using the C<Decode_Date_EU()> function of the L<Date::Calc> module. Refer to the C<Date::Calc> documentation for further information about the date formats that can be parsed. Also note the following from the C<Date::Calc> documentation:
817              
818             "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:"
819              
820             0 <= $year < 70 ==> $year += 2000;
821             70 <= $year < 100 ==> $year += 1900;
822              
823             The time portion of the string is parsed using the C<xl_parse_time()> function described above.
824              
825             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.
826              
827             $date1 = xl_decode_date_EU( "11/7/97" ); #11 July 1997
828             $date2 = xl_decode_date_EU( "Sat 12 Sept 1998" );
829             $date3 = xl_decode_date_EU( "4:30 AM Sat 12 Sept 1998" );
830              
831             =head2 xl_decode_date_US($string)
832              
833              
834             Parameters: $string, a textual representation of a date and time
835              
836             Returns: A number that represents an Excel date
837             or undef for an invalid date.
838              
839             Requires: Date::Calc
840              
841             This function converts a date and time string into a number that represents an Excel date.
842              
843             The date parsing is performed using the C<Decode_Date_US()> function of the L<Date::Calc> module. Refer to the C<Date::Calc> documentation for further information about the date formats that can be parsed. Also note the following from the C<Date::Calc> documentation:
844              
845             "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:"
846              
847             0 <= $year < 70 ==> $year += 2000;
848             70 <= $year < 100 ==> $year += 1900;
849              
850             The time portion of the string is parsed using the C<xl_parse_time()> function described above.
851              
852             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.
853              
854             $date1 = xl_decode_date_US( "11/7/97" ); # 7 November 1997
855             $date2 = xl_decode_date_US( "Sept 12 Saturday 1998" );
856             $date3 = xl_decode_date_US( "4:30 AM Sept 12 Sat 1998" );
857              
858             =head2 xl_date_1904($date)
859              
860              
861             Parameters: $date, an Excel date with a 1900 epoch
862              
863             Returns: an Excel date with a 1904 epoch or zero if
864             the $date is before 1904
865              
866              
867             This function converts an Excel date based on the 1900 epoch into a date based on the 1904 epoch.
868              
869             $date1 = xl_date_list( 2002, 1, 13 ); # 13 Jan 2002, 1900 epoch
870             $date2 = xl_date_1904( $date1 ); # 13 Jan 2002, 1904 epoch
871              
872             See also the C<set_1904()> workbook method in the L<Excel::Writer::XLSX> documentation.
873              
874             =head1 REQUIREMENTS
875              
876             The date and time functions require functions from the L<Date::Manip> and L<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.
877              
878             For more information about "autousing" refer to the documentation on the C<autouse> pragma.
879              
880             =head1 BUGS
881              
882             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:
883              
884             "Subroutine xxx redefined ..."
885              
886             The current workaround for this is to put C<use warnings;> near the beginning of your program.
887              
888             =head1 AUTHOR
889              
890             John McNamara jmcnamara@cpan.org
891              
892             =head1 COPYRIGHT
893              
894             Copyright MM-MMXXI, John McNamara.
895              
896             All Rights Reserved. This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself.
897