1Spreadsheet::WriteExcelU:s:eUrtiClointtyr(i3b)uted PerlSDporceuamdesnhteaetti:o:nWriteExcel::Utility(3)
2
3
4

NAME

6       Utility - Helper functions for Spreadsheet::WriteExcel.
7

SYNOPSIS

9       Functions to help with some common tasks when using
10       Spreadsheet::WriteExcel.
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 Spreadsheet::WriteExcel::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_inc_col('Z1'  );               # AA1
20           $str            = xl_dec_col('AA1' );               # Z1
21
22           $date           = xl_date_list(2002, 1, 1);         # 37257
23           $date           = xl_parse_date("11 July 1997");    # 35622
24           $time           = xl_parse_time('3:21:36 PM');      # 0.64
25           $date           = xl_decode_date_EU("13 May 2002"); # 37389
26

DESCRIPTION

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

ROW AND COLUMN FUNCTIONS

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

TIME AND DATE FUNCTIONS

237       Dates and times in Excel are represented by real numbers, for example
238       "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
239
240       The integer part of the number stores the number of days since the
241       epoch and the fractional part stores the percentage of the day in
242       seconds.
243
244       The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and
245       Excel for Macintosh uses 1904. The epochs are:
246
247           1900: 0 January 1900 i.e. 31 December 1899
248           1904: 1 January 1904
249
250       Excel on Windows and the Macintosh will convert automatically between
251       one system and the other. By default Spreadsheet::WriteExcel uses the
252       1900 format. To use the 1904 epoch you must use the set_1904() workbook
253       method, see the Spreadsheet::WriteExcel documentation.
254
255       There are two things to note about the 1900 date format. The first is
256       that the epoch starts on 0 January 1900. The second is that the year
257       1900 is erroneously but deliberately treated as a leap year. Therefore
258       you must add an extra day to dates after 28 February 1900. The
259       functions in the following section will deal with these issues
260       automatically. The reason for this anomaly is explained at
261       http://support.microsoft.com/support/kb/articles/Q181/3/70.asp
262
263       Note, a date or time in Excel is like any other number. To display the
264       number as a date you must apply a number format to it: Refer to the
265       set_num_format() method in the Spreadsheet::WriteExcel documentation:
266
267           $date = xl_date_list(2001, 1, 1, 12, 30);
268           $format->set_num_format('mmm d yyyy hh:mm AM/PM');
269           $worksheet->write('A1', $date , $format); # Jan 1 2001 12:30 AM
270
271       To use these functions you must install the "Date::Manip" and
272       "Date::Calc" modules. See REQUIREMENTS and the individual requirements
273       of each functions.
274
275       See also the DateTime::Format::Excel
276       module,http://search.cpan.org/search?dist=DateTime-Format-Excel which
277       is part of the DateTime project and which deals specifically with
278       converting dates and times to and from Excel's format.
279
280   xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
281           Parameters: $years:         Integer
282                       $months:        Integer [optional, default is 1]
283                       $days:          Integer [optional, default is 1]
284                       $hours:         Integer [optional, default is 0]
285                       $minutes:       Integer [optional, default is 0]
286                       $seconds:       Float   [optional, default is 0]
287
288           Returns:    A number that represents an Excel date
289                       or undef for an invalid date.
290
291           Requires:   Date::Calc
292
293       This function converts an array of data into a number that represents
294       an Excel date. All of the parameters are optional except for $years.
295
296           $date1 = xl_date_list(2002, 1, 2);              # 2 Jan 2002
297           $date2 = xl_date_list(2002, 1, 2, 12);          # 2 Jan 2002 12:00 pm
298           $date3 = xl_date_list(2002, 1, 2, 12, 30);      # 2 Jan 2002 12:30 pm
299           $date4 = xl_date_list(2002, 1, 2, 12, 30, 45);  # 2 Jan 2002 12:30:45 pm
300
301       This function can be used in conjunction with functions that parse date
302       and time strings. In fact it is used in most of the following
303       functions.
304
305   xl_parse_time($string)
306           Parameters: $string, a textual representation of a time
307
308           Returns:    A number that represents an Excel time
309                       or undef for an invalid time.
310
311       This function converts a time string into a number that represents an
312       Excel time. The following time formats are valid:
313
314           hh:mm       [AM|PM]
315           hh:mm       [AM|PM]
316           hh:mm:ss    [AM|PM]
317           hh:mm:ss.ss [AM|PM]
318
319       The meridian, AM or PM, is optional and case insensitive. A 24 hour
320       time is assumed if the meridian is omitted
321
322           $time1 = xl_parse_time('12:18');
323           $time2 = xl_parse_time('12:18:14');
324           $time3 = xl_parse_time('12:18:14 AM');
325           $time4 = xl_parse_time('1:18:14 AM');
326
327       Time in Excel is expressed as a fraction of the day in seconds.
328       Therefore you can calculate an Excel time as follows:
329
330           $time = ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
331
332   xl_parse_date($string)
333           Parameters: $string, a textual representation of a date and time
334
335           Returns:    A number that represents an Excel date
336                       or undef for an invalid date.
337
338           Requires:   Date::Manip and Date::Calc
339
340       This function converts a date and time string into a number that
341       represents an Excel date.
342
343       The parsing is performed using the ParseDate() function of the
344       Date::Manip module. Refer to the Date::Manip documentation for further
345       information about the date and time formats that can be parsed. In
346       order to use this function you will probably have to initialise some
347       Date::Manip variables via the xl_parse_date_init() function, see below.
348
349           xl_parse_date_init("TZ=GMT","DateFormat=non-US");
350
351           $date1 = xl_parse_date("11/7/97");
352           $date2 = xl_parse_date("Friday 11 July 1997");
353           $date3 = xl_parse_date("10:30 AM Friday 11 July 1997");
354           $date4 = xl_parse_date("Today");
355           $date5 = xl_parse_date("Yesterday");
356
357       Note, if you parse a string that represents a time but not a date this
358       function will add the current date. If you want the time without the
359       date you can do something like the following:
360
361           $time  = xl_parse_date("10:30 AM");
362           $time -= int($time);
363
364   xl_parse_date_init("variable=value", ...)
365           Parameters: A list of Date::Manip variable strings
366
367           Returns:    A list of all the Date::Manip strings
368
369           Requires:   Date::Manip
370
371       This function is used to initialise variables required by the
372       Date::Manip module. You should call this function before calling
373       xl_parse_date(). It need only be called once.
374
375       This function is a thin wrapper for the Date::Manip::Date_Init()
376       function. You can use Date_Init()  directly if you wish. Refer to the
377       Date::Manip documentation for further information.
378
379           xl_parse_date_init("TZ=MST","DateFormat=US");
380           $date1 = xl_parse_date("11/7/97");  # November 7th 1997
381
382           xl_parse_date_init("TZ=GMT","DateFormat=non-US");
383           $date1 = xl_parse_date("11/7/97");  # July 11th 1997
384
385   xl_decode_date_EU($string)
386           Parameters: $string, a textual representation of a date and time
387
388           Returns:    A number that represents an Excel date
389                       or undef for an invalid date.
390
391           Requires:   Date::Calc
392
393       This function converts a date and time string into a number that
394       represents an Excel date.
395
396       The date parsing is performed using the Decode_Date_EU() function of
397       the Date::Calc module. Refer to the Date::Calc for further information
398       about the date formats that can be parsed. Also note the following from
399       the Date::Calc documentation:
400
401       "If the year is given as one or two digits only (i.e., if the year is
402       less than 100), it is mapped to the window 1970 -2069 as follows":
403
404            0 E<lt>= $year E<lt>  70  ==>  $year += 2000;
405           70 E<lt>= $year E<lt> 100  ==>  $year += 1900;
406
407       The time portion of the string is parsed using the xl_parse_time()
408       function described above.
409
410       Note: the EU in the function name means that a European date format is
411       assumed if it is not clear from the string. See the first example
412       below.
413
414           $date1 = xl_decode_date_EU("11/7/97"); #11 July 1997
415           $date2 = xl_decode_date_EU("Sat 12 Sept 1998");
416           $date3 = xl_decode_date_EU("4:30 AM Sat 12 Sept 1998");
417
418   xl_decode_date_US($string)
419           Parameters: $string, a textual representation of a date and time
420
421           Returns:    A number that represents an Excel date
422                       or undef for an invalid date.
423
424           Requires:   Date::Calc
425
426       This function converts a date and time string into a number that
427       represents an Excel date.
428
429       The date parsing is performed using the Decode_Date_US() function of
430       the Date::Calc module. Refer to the Date::Calc for further information
431       about the date formats that can be parsed. Also note the following from
432       the Date::Calc documentation:
433
434       "If the year is given as one or two digits only (i.e., if the year is
435       less than 100), it is mapped to the window 1970 -2069 as follows":
436
437            0 <= $year <  70  ==>  $year += 2000;
438           70 <= $year < 100  ==>  $year += 1900;
439
440       The time portion of the string is parsed using the xl_parse_time()
441       function described above.
442
443       Note: the US in the function name means that an American date format is
444       assumed if it is not clear from the string. See the first example
445       below.
446
447           $date1 = xl_decode_date_US("11/7/97"); # 7 November 1997
448           $date2 = xl_decode_date_US("12 Sept Saturday 1998");
449           $date3 = xl_decode_date_US("4:30 AM 12 Sept Sat 1998");
450
451   xl_date_1904($date)
452           Parameters: $date, an Excel date with a 1900 epoch
453
454           Returns:    an Excel date with a 1904 epoch or zero if
455                       the $date is before 1904
456
457       This function converts an Excel date based on the 1900 epoch into a
458       date based on the 1904 epoch.
459
460           $date1 = xl_date_list(2002, 1, 13); # 13 Jan 2002, 1900 epoch
461           $date2 = xl_date_1904($date1);      # 13 Jan 2002, 1904 epoch
462
463       See also the set_1904() workbook method in the Spreadsheet::WriteExcel
464       documentation.
465

REQUIREMENTS

467       The date and time functions require functions from the "Date::Manip"
468       and "Date::Calc" modules. The required functions are "autoused" from
469       these modules so that you do not have to install them unless you wish
470       to use the date and time routines. Therefore it is possible to use the
471       row and column functions without having "Date::Manip" and "Date::Calc"
472       installed.
473
474       For more information about "autousing" refer to the documentation on
475       the "autouse" pragma.
476

BUGS

478       When using the autoused functions from "Date::Manip" and "Date::Calc"
479       on Perl 5.6.0 with "-w" you will get a warning like this:
480
481           "Subroutine xxx redefined ..."
482
483       The current workaround for this is to put "use warnings;" near the
484       beginning of your program.
485

AUTHOR

487       John McNamara jmcnamara@cpan.org
488
490       Copyright MM-MMX, John McNamara.
491
492       All Rights Reserved. This module is free software. It may be used,
493       redistributed and/or modified under the same terms as Perl itself.
494
495
496
497perl v5.36.0                      2023-01-20Spreadsheet::WriteExcel::Utility(3)
Impressum