1Excel::Writer::XLSX::UtUisleirtyC(o3n)tributed Perl DocuEmxecnetla:t:iWorniter::XLSX::Utility(3)
2
3
4

NAME

6       Utility - Helper functions for Excel::Writer::XLSX.
7

SYNOPSIS

9       Functions to help with some common tasks when using
10       Excel::Writer::XLSX.
11
12       These functions mainly relate to dealing with rows and columns in A1
13       notation and to handling dates and times.
14
15           use Excel::Writer::XLSX::Utility;                     # Import everything
16
17           ($row, $col)    = xl_cell_to_rowcol( 'C2' );          # (1, 2)
18           $str            = xl_rowcol_to_cell( 1, 2 );          # C2
19           $str            = xl_col_to_name( 702 );              # AAA
20           $str            = xl_inc_col( 'Z1'  );                # AA1
21           $str            = xl_dec_col( 'AA1' );                # Z1
22
23           $date           = xl_date_list(2002, 1, 1);           # 37257
24           $date           = xl_parse_date( '11 July 1997' );    # 35622
25           $time           = xl_parse_time( '3:21:36 PM' );      # 0.64
26           $date           = xl_decode_date_EU( '13 May 2002' ); # 37389
27

DESCRIPTION

29       This module provides a set of functions to help with some common tasks
30       encountered when using the Excel::Writer::XLSX module. The two main
31       categories of function are:
32
33       Row and column functions: these are used to deal with Excel's A1
34       representation of cells. The functions in this category are:
35
36           xl_rowcol_to_cell
37           xl_cell_to_rowcol
38           xl_col_to_name
39           xl_range
40           xl_range_formula
41           xl_inc_row
42           xl_dec_row
43           xl_inc_col
44           xl_dec_col
45
46       Date and Time functions: these are used to convert dates and times to
47       the numeric format used by Excel. The functions in this category are:
48
49           xl_date_list
50           xl_date_1904
51           xl_parse_time
52           xl_parse_date
53           xl_parse_date_init
54           xl_decode_date_EU
55           xl_decode_date_US
56
57       All of these functions are exported by default. However, you can use
58       import lists if you wish to limit the functions that are imported:
59
60           use Excel::Writer::XLSX::Utility;                  # Import everything
61           use Excel::Writer::XLSX::Utility qw(xl_date_list); # xl_date_list only
62           use Excel::Writer::XLSX::Utility qw(:rowcol);      # Row/col functions
63           use Excel::Writer::XLSX::Utility qw(:dates);       # Date functions
64

ROW AND COLUMN FUNCTIONS

66       Excel::Writer::XLSX supports two forms of notation to designate the
67       position of cells: Row-column notation and A1 notation.
68
69       Row-column notation uses a zero based index for both row and column
70       while A1 notation uses the standard Excel alphanumeric sequence of
71       column letter and 1-based row. Columns range from A to XFD, i.e. 0 to
72       16,383, rows range from 0 to 1,048,575 in Excel 2007+. For example:
73
74           (0, 0)      # The top left cell in row-column notation.
75           ('A1')      # The top left cell in A1 notation.
76
77           (1999, 29)  # Row-column notation.
78           ('AD2000')  # The same cell in A1 notation.
79
80       Row-column notation is useful if you are referring to cells
81       programmatically:
82
83           for my $i ( 0 .. 9 ) {
84               $worksheet->write( $i, 0, 'Hello' );    # Cells A1 to A10
85           }
86
87       A1 notation is useful for setting up a worksheet manually and for
88       working with formulas:
89
90           $worksheet->write( 'H1', 200 );
91           $worksheet->write( 'H2', '=H7+1' );
92
93       The functions in the following sections can be used for dealing with A1
94       notation, for example:
95
96           ( $row, $col ) = xl_cell_to_rowcol('C2');    # (1, 2)
97           $str           = xl_rowcol_to_cell( 1, 2 );  # C2
98
99       Cell references in Excel can be either relative or absolute. Absolute
100       references are prefixed by the dollar symbol as shown below:
101
102           A1      # Column and row are relative
103           $A1     # Column is absolute and row is relative
104           A$1     # Column is relative and row is absolute
105           $A$1    # Column and row are absolute
106
107       An absolute reference only makes a difference if the cell is copied.
108       Refer to the Excel documentation for further details. All of the
109       following functions support absolute references.
110
111   xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
112           Parameters: $row:           Integer
113                       $col:           Integer
114                       $row_absolute:  Boolean (1/0) [optional, default is 0]
115                       $col_absolute:  Boolean (1/0) [optional, default is 0]
116
117           Returns:    A string in A1 cell notation
118
119       This function converts a zero based row and column cell reference to a
120       A1 style string:
121
122           $str = xl_rowcol_to_cell( 0, 0 );    # A1
123           $str = xl_rowcol_to_cell( 0, 1 );    # B1
124           $str = xl_rowcol_to_cell( 1, 0 );    # A2
125
126       The optional parameters $row_absolute and $col_absolute can be used to
127       indicate if the row or column is absolute:
128
129           $str = xl_rowcol_to_cell( 0, 0, 0, 1 );    # $A1
130           $str = xl_rowcol_to_cell( 0, 0, 1, 0 );    # A$1
131           $str = xl_rowcol_to_cell( 0, 0, 1, 1 );    # $A$1
132
133       See above for an explanation of absolute cell references.
134
135   xl_cell_to_rowcol($string)
136           Parameters: $string         String in A1 format
137
138           Returns:    List            ($row, $col)
139
140       This function converts an Excel cell reference in A1 notation to a zero
141       based row and column. The function will also handle Excel's absolute,
142       "$", cell notation.
143
144           my ( $row, $col ) = xl_cell_to_rowcol('A1');      # (0, 0)
145           my ( $row, $col ) = xl_cell_to_rowcol('B1');      # (0, 1)
146           my ( $row, $col ) = xl_cell_to_rowcol('C2');      # (1, 2)
147           my ( $row, $col ) = xl_cell_to_rowcol('$C2');     # (1, 2)
148           my ( $row, $col ) = xl_cell_to_rowcol('C$2');     # (1, 2)
149           my ( $row, $col ) = xl_cell_to_rowcol('$C$2');    # (1, 2)
150
151   xl_col_to_name($col, $col_absolute)
152           Parameters: $col:           Integer
153                       $col_absolute:  Boolean (1/0) [optional, default is 0]
154
155           Returns:    A column string name.
156
157       This function converts a zero based column reference to a string:
158
159           $str = xl_col_to_name(0);      # A
160           $str = xl_col_to_name(1);      # B
161           $str = xl_col_to_name(702);    # AAA
162
163       The optional parameter $col_absolute can be used to indicate if the
164       column is absolute:
165
166           $str = xl_col_to_name( 0, 0 );    # A
167           $str = xl_col_to_name( 0, 1 );    # $A
168           $str = xl_col_to_name( 1, 1 );    # $B
169
170   xl_range($row_1, $row_2, $col_1, $col_2, $row_abs_1, $row_abs_2,
171       $col_abs_1, $col_abs_2)
172           Parameters: $sheetname      String
173                       $row_1:         Integer
174                       $row_2:         Integer
175                       $col_1:         Integer
176                       $col_2:         Integer
177                       $row_abs_1:     Boolean (1/0) [optional, default is 0]
178                       $row_abs_2:     Boolean (1/0) [optional, default is 0]
179                       $col_abs_1:     Boolean (1/0) [optional, default is 0]
180                       $col_abs_2:     Boolean (1/0) [optional, default is 0]
181
182           Returns:    A worksheet range formula as a string.
183
184       This function converts zero based row and column cell references to an
185       A1 style range string:
186
187           my $str = xl_range( 0, 9, 0, 0 );          # A1:A10
188           my $str = xl_range( 1, 8, 2, 2 );          # C2:C9
189           my $str = xl_range( 0, 3, 0, 4 );          # A1:E4
190           my $str = xl_range( 0, 3, 0, 4, 1 );       # A$1:E4
191           my $str = xl_range( 0, 3, 0, 4, 1, 1 );    # A$1:E$
192           my $str = xl_range( 0, 0, 0, 0 );          # A1
193
194   xl_range_formula($sheetname, $row_1, $row_2, $col_1, $col_2)
195           Parameters: $sheetname      String
196                       $row_1:         Integer
197                       $row_2:         Integer
198                       $col_1:         Integer
199                       $col_2:         Integer
200
201           Returns:    A worksheet range formula as a string.
202
203       This function converts zero based row and column cell references to an
204       A1 style formula string:
205
206           my $str = xl_range_formula( 'Sheet1', 0, 9,  0, 0 ); # =Sheet1!$A$1:$A$10
207           my $str = xl_range_formula( 'Sheet2', 6, 65, 1, 1 ); # =Sheet2!$B$7:$B$66
208           my $str = xl_range_formula( 'New data', 1, 8, 2, 2 );# ='New data'!$C$2:$C$9
209
210       This is useful for setting ranges in Chart objects:
211
212           $chart->add_series(
213               categories => xl_range_formula( 'Sheet1', 1, 9, 0, 0 ),
214               values     => xl_range_formula( 'Sheet1', 1, 9, 1, 1 ),
215           );
216
217           # Which is the same as:
218
219           $chart->add_series(
220               categories => '=Sheet1!$A$2:$A$10',
221               values     => '=Sheet1!$B$2:$B$10',
222           );
223
224   xl_inc_row($string)
225           Parameters: $string, a string in A1 format
226
227           Returns:    Incremented string in A1 format
228
229       This functions takes a cell reference string in A1 notation and
230       increments the row. The function will also handle Excel's absolute,
231       "$", cell notation:
232
233           my $str = xl_inc_row( 'A1' );      # A2
234           my $str = xl_inc_row( 'B$2' );     # B$3
235           my $str = xl_inc_row( '$C3' );     # $C4
236           my $str = xl_inc_row( '$D$4' );    # $D$5
237
238   xl_dec_row($string)
239           Parameters: $string, a string in A1 format
240
241           Returns:    Decremented string in A1 format
242
243       This functions takes a cell reference string in A1 notation and
244       decrements the row. The function will also handle Excel's absolute,
245       "$", cell notation:
246
247           my $str = xl_dec_row( 'A2' );      # A1
248           my $str = xl_dec_row( 'B$3' );     # B$2
249           my $str = xl_dec_row( '$C4' );     # $C3
250           my $str = xl_dec_row( '$D$5' );    # $D$4
251
252   xl_inc_col($string)
253           Parameters: $string, a string in A1 format
254
255           Returns:    Incremented string in A1 format
256
257       This functions takes a cell reference string in A1 notation and
258       increments the column. The function will also handle Excel's absolute,
259       "$", cell notation:
260
261           my $str = xl_inc_col( 'A1' );      # B1
262           my $str = xl_inc_col( 'Z1' );      # AA1
263           my $str = xl_inc_col( '$B1' );     # $C1
264           my $str = xl_inc_col( '$D$5' );    # $E$5
265
266   xl_dec_col($string)
267           Parameters: $string, a string in A1 format
268
269           Returns:    Decremented string in A1 format
270
271       This functions takes a cell reference string in A1 notation and
272       decrements the column. The function will also handle Excel's absolute,
273       "$", cell notation:
274
275           my $str = xl_dec_col( 'B1' );      # A1
276           my $str = xl_dec_col( 'AA1' );     # Z1
277           my $str = xl_dec_col( '$C1' );     # $B1
278           my $str = xl_dec_col( '$E$5' );    # $D$5
279

TIME AND DATE FUNCTIONS

281       Dates and times in Excel are represented by real numbers, for example
282       "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
283
284       The integer part of the number stores the number of days since the
285       epoch and the fractional part stores the percentage of the day in
286       seconds.
287
288       A date or time in Excel is like any other number. To display the number
289       as a date you must apply a number format to it: Refer to the
290       "set_num_format()" method in the Excel::Writer::XLSX documentation:
291
292           $date = xl_date_list( 2001, 1, 1, 12, 30 );
293           $format->set_num_format( 'mmm d yyyy hh:mm AM/PM' );
294           $worksheet->write( 'A1', $date, $format );    # Jan 1 2001 12:30 AM
295
296       The date handling functions below are supplied for historical reasons.
297       In the current version of the module it is easier to just use the
298       "write_date_time()" function to write dates or times. See the DATES AND
299       TIME IN EXCEL section of the main Excel::Writer::XLSX documentation for
300       details.
301
302       In addition to using the functions below you must install the
303       Date::Manip and Date::Calc modules. See REQUIREMENTS and the individual
304       requirements of each functions.
305
306       For a "DateTime.pm" solution see the DateTime::Format::Excel module.
307
308   xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
309           Parameters: $years:         Integer
310                       $months:        Integer [optional, default is 1]
311                       $days:          Integer [optional, default is 1]
312                       $hours:         Integer [optional, default is 0]
313                       $minutes:       Integer [optional, default is 0]
314                       $seconds:       Float   [optional, default is 0]
315
316           Returns:    A number that represents an Excel date
317                       or undef for an invalid date.
318
319           Requires:   Date::Calc
320
321       This function converts an array of data into a number that represents
322       an Excel date. All of the parameters are optional except for $years.
323
324           $date1 = xl_date_list( 2002, 1, 2 );                # 2 Jan 2002
325           $date2 = xl_date_list( 2002, 1, 2, 12 );            # 2 Jan 2002 12:00 pm
326           $date3 = xl_date_list( 2002, 1, 2, 12, 30 );        # 2 Jan 2002 12:30 pm
327           $date4 = xl_date_list( 2002, 1, 2, 12, 30, 45 );    # 2 Jan 2002 12:30:45 pm
328
329       This function can be used in conjunction with functions that parse date
330       and time strings. In fact it is used in most of the following
331       functions.
332
333   xl_parse_time($string)
334           Parameters: $string, a textual representation of a time
335
336           Returns:    A number that represents an Excel time
337                       or undef for an invalid time.
338
339       This function converts a time string into a number that represents an
340       Excel time. The following time formats are valid:
341
342           hh:mm       [AM|PM]
343           hh:mm       [AM|PM]
344           hh:mm:ss    [AM|PM]
345           hh:mm:ss.ss [AM|PM]
346
347       The meridian, AM or PM, is optional and case insensitive. A 24 hour
348       time is assumed if the meridian is omitted.
349
350           $time1 = xl_parse_time( '12:18' );
351           $time2 = xl_parse_time( '12:18:14' );
352           $time3 = xl_parse_time( '12:18:14 AM' );
353           $time4 = xl_parse_time( '1:18:14 AM' );
354
355       Time in Excel is expressed as a fraction of the day in seconds.
356       Therefore you can calculate an Excel time as follows:
357
358           $time = ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );
359
360   xl_parse_date($string)
361           Parameters: $string, a textual representation of a date and time
362
363           Returns:    A number that represents an Excel date
364                       or undef for an invalid date.
365
366           Requires:   Date::Manip and Date::Calc
367
368       This function converts a date and time string into a number that
369       represents an Excel date.
370
371       The parsing is performed using the "ParseDate()" function of the
372       Date::Manip module. Refer to the "Date::Manip" documentation for
373       further information about the date and time formats that can be parsed.
374       In order to use this function you will probably have to initialise some
375       "Date::Manip" variables via the "xl_parse_date_init()" function, see
376       below.
377
378           xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
379
380           $date1 = xl_parse_date( "11/7/97" );
381           $date2 = xl_parse_date( "Friday 11 July 1997" );
382           $date3 = xl_parse_date( "10:30 AM Friday 11 July 1997" );
383           $date4 = xl_parse_date( "Today" );
384           $date5 = xl_parse_date( "Yesterday" );
385
386       Note, if you parse a string that represents a time but not a date this
387       function will add the current date. If you want the time without the
388       date you can do something like the following:
389
390           $time  = xl_parse_date( "10:30 AM" );
391           $time -= int( $time );
392
393   xl_parse_date_init("variable=value", ...)
394           Parameters: A list of Date::Manip variable strings
395
396           Returns:    A list of all the Date::Manip strings
397
398           Requires:   Date::Manip
399
400       This function is used to initialise variables required by the
401       Date::Manip module. You should call this function before calling
402       "xl_parse_date()". It need only be called once.
403
404       This function is a thin wrapper for the "Date::Manip::Date_Init()"
405       function. You can use "Date_Init()"  directly if you wish. Refer to the
406       "Date::Manip" documentation for further information.
407
408           xl_parse_date_init( "TZ=MST", "DateFormat=US" );
409           $date1 = xl_parse_date( "11/7/97" );    # November 7th 1997
410
411           xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
412           $date1 = xl_parse_date( "11/7/97" );    # July 11th 1997
413
414   xl_decode_date_EU($string)
415           Parameters: $string, a textual representation of a date and time
416
417           Returns:    A number that represents an Excel date
418                       or undef for an invalid date.
419
420           Requires:   Date::Calc
421
422       This function converts a date and time string into a number that
423       represents an Excel date.
424
425       The date parsing is performed using the "Decode_Date_EU()" function of
426       the Date::Calc module. Refer to the "Date::Calc" documentation for
427       further information about the date formats that can be parsed. Also
428       note the following from the "Date::Calc" documentation:
429
430       "If the year is given as one or two digits only (i.e., if the year is
431       less than 100), it is mapped to the window 1970 -2069 as follows:"
432
433            0 <= $year <  70  ==>  $year += 2000;
434           70 <= $year < 100  ==>  $year += 1900;
435
436       The time portion of the string is parsed using the "xl_parse_time()"
437       function described above.
438
439       Note: the EU in the function name means that a European date format is
440       assumed if it is not clear from the string. See the first example
441       below.
442
443           $date1 = xl_decode_date_EU( "11/7/97" );                    #11 July 1997
444           $date2 = xl_decode_date_EU( "Sat 12 Sept 1998" );
445           $date3 = xl_decode_date_EU( "4:30 AM Sat 12 Sept 1998" );
446
447   xl_decode_date_US($string)
448           Parameters: $string, a textual representation of a date and time
449
450           Returns:    A number that represents an Excel date
451                       or undef for an invalid date.
452
453           Requires:   Date::Calc
454
455       This function converts a date and time string into a number that
456       represents an Excel date.
457
458       The date parsing is performed using the "Decode_Date_US()" function of
459       the Date::Calc module. Refer to the "Date::Calc" documentation for
460       further information about the date formats that can be parsed. Also
461       note the following from the "Date::Calc" documentation:
462
463       "If the year is given as one or two digits only (i.e., if the year is
464       less than 100), it is mapped to the window 1970 -2069 as follows:"
465
466            0 <= $year <  70  ==>  $year += 2000;
467           70 <= $year < 100  ==>  $year += 1900;
468
469       The time portion of the string is parsed using the "xl_parse_time()"
470       function described above.
471
472       Note: the US in the function name means that an American date format is
473       assumed if it is not clear from the string. See the first example
474       below.
475
476           $date1 = xl_decode_date_US( "11/7/97" );                 # 7 November 1997
477           $date2 = xl_decode_date_US( "Sept 12 Saturday 1998" );
478           $date3 = xl_decode_date_US( "4:30 AM Sept 12 Sat 1998" );
479
480   xl_date_1904($date)
481           Parameters: $date, an Excel date with a 1900 epoch
482
483           Returns:    an Excel date with a 1904 epoch or zero if
484                       the $date is before 1904
485
486       This function converts an Excel date based on the 1900 epoch into a
487       date based on the 1904 epoch.
488
489           $date1 = xl_date_list( 2002, 1, 13 );    # 13 Jan 2002, 1900 epoch
490           $date2 = xl_date_1904( $date1 );         # 13 Jan 2002, 1904 epoch
491
492       See also the "set_1904()" workbook method in the Excel::Writer::XLSX
493       documentation.
494

REQUIREMENTS

496       The date and time functions require functions from the Date::Manip and
497       Date::Calc modules. The required functions are "autoused" from these
498       modules so that you do not have to install them unless you wish to use
499       the date and time routines. Therefore it is possible to use the row and
500       column functions without having "Date::Manip" and "Date::Calc"
501       installed.
502
503       For more information about "autousing" refer to the documentation on
504       the "autouse" pragma.
505

BUGS

507       When using the autoused functions from "Date::Manip" and "Date::Calc"
508       on Perl 5.6.0 with "-w" you will get a warning like this:
509
510           "Subroutine xxx redefined ..."
511
512       The current workaround for this is to put "use warnings;" near the
513       beginning of your program.
514

AUTHOR

516       John McNamara jmcnamara@cpan.org
517
519       Copyright MM-MMXXI, John McNamara.
520
521       All Rights Reserved. This module is free software. It may be used,
522       redistributed and/or modified under the same terms as Perl itself.
523
524
525
526perl v5.36.0                      2022-07-22   Excel::Writer::XLSX::Utility(3)
Impressum