1Spreadsheet::WriteExcelU:s:eUrtiClointtyr(i3b)uted PerlSDporceuamdesnhteaetti:o:nWriteExcel::Utility(3)
2
6 Utility - Helper functions for Spreadsheet::WriteExcel.
7
9 This document refers to version 0.03 of Spreadsheet::WriteExcel::Util‐
10 ity, released March, 2002.
11
13 Functions to help with some common tasks when using Spreadsheet::Write‐
14 Excel.
15 These functions mainly relate to dealing with rows and columns in A1
17 notation and to handling dates and times.
18 use Spreadsheet::WriteExcel::Utility; # Import everything
20 ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
22 $str = xl_rowcol_to_cell(1, 2); # C2
23 $str = xl_inc_col('Z1' ); # AA1
24 $str = xl_dec_col('AA1' ); # Z1
25 $date = xl_date_list(2002, 1, 1); # 37257
27 $date = xl_parse_date("11 July 1997"); # 35622
28 $time = xl_parse_time('3:21:36 PM'); # 0.64
29 $date = xl_decode_date_EU("13 May 2002"); # 37389
30
32 This module provides a set of functions to help with some common tasks
33 encountered when using the Spreadsheet::WriteExcel module. The two main
34 categories of function are:
35 Row and column functions: these are used to deal with Excel's A1 repre‐
37 sentation of cells. The functions in this category are:
38 xl_rowcol_to_cell
40 xl_cell_to_rowcol
41 xl_inc_row
42 xl_dec_row
43 xl_inc_col
44 xl_dec_col
45 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 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 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 use Spreadsheet::WriteExcel::Utility; # Import everything
61 use Spreadsheet::WriteExcel::Utility qw(xl_date_list); # xl_date_list only
62 use Spreadsheet::WriteExcel::Utility qw(:rowcol); # Row/col functions
63 use Spreadsheet::WriteExcel::Utility qw(:dates); # Date functions
64
66 Spreadsheet::WriteExcel supports two forms of notation to designate the
67 position of cells: Row-column notation and A1 notation.
68 Row-column notation uses a zero based index for both row and column
70 while A1 notation uses the standard Excel alphanumeric sequence of col‐
71 umn letter and 1-based row. Columns range from A to IV i.e. 0 to 255,
72 rows range from 1 to 16384 in Excel 5 and 65536 in Excel 97. For exam‐
73 ple:
74 (0, 0) # The top left cell in row-column notation.
76 ('A1') # The top left cell in A1 notation.
77 (1999, 29) # Row-column notation.
79 ('AD2000') # The same cell in A1 notation.
80 Row-column notation is useful if you are referring to cells programmat‐
82 ically:
83 for my $i (0 .. 9) {
85 $worksheet->write($i, 0, 'Hello'); # Cells A1 to A10
86 }
87 A1 notation is useful for setting up a worksheet manually and for work‐
89 ing with formulas:
90 $worksheet->write('H1', 200);
92 $worksheet->write('H2', '=H7+1');
93 The functions in the following sections can be used for dealing with A1
95 notation, for example:
96 ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
98 $str = xl_rowcol_to_cell(1, 2); # C2
99 Cell references in Excel can be either relative or absolute. Absolute
101 references are prefixed by the dollar symbol as shown below:
102 A1 # Column and row are relative
104 $A1 # Column is absolute and row is relative
105 A$1 # Column is relative and row is absolute
106 $A$1 # Column and row are absolute
107 An absolute reference only has an effect if the cell is copied. Refer
109 to the Excel documentation for further details. All of the following
110 functions support absolute references.
111 xl_rowcol_to_cell($row, $col, $row_absolute, $col_absolute)
113 Parameters: $row: Integer
115 $col: Integer
116 $row_absolute: Boolean (1/0) [optional, default is 0]
117 $col_absolute: Boolean (1/0) [optional, default is 0]
118 Returns: A string in A1 cell notation
120 This function converts a zero based row and column cell reference to a
122 A1 style string:
123 $str = xl_rowcol_to_cell(0, 0); # A1
125 $str = xl_rowcol_to_cell(0, 1); # B1
126 $str = xl_rowcol_to_cell(1, 0); # A2
127 The optional parameters $row_absolute and $col_absolute can be used to
129 indicate if the row or column is absolute:
130 $str = xl_rowcol_to_cell(0, 0, 0, 1); # $A1
132 $str = xl_rowcol_to_cell(0, 0, 1, 0); # A$1
133 $str = xl_rowcol_to_cell(0, 0, 1, 1); # $A$1
134 See "ROW AND COLUMN FUNCTIONS" for an explanation of absolute cell ref‐
136 erences.
137 xl_cell_to_rowcol($string)
139 Parameters: $string String in A1 format
141 Returns: List ($row, $col)
143 This function converts an Excel cell reference in A1 notation to a zero
145 based row and column. The function will also handle Excel's absolute,
146 "$", cell notation.
147 my ($row, $col) = xl_cell_to_rowcol('A1'); # (0, 0)
149 my ($row, $col) = xl_cell_to_rowcol('B1'); # (0, 1)
150 my ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
151 my ($row, $col) = xl_cell_to_rowcol('$C2' ); # (1, 2)
152 my ($row, $col) = xl_cell_to_rowcol('C$2' ); # (1, 2)
153 my ($row, $col) = xl_cell_to_rowcol('$C$2'); # (1, 2)
154 xl_inc_row($string)
156 Parameters: $string, a string in A1 format
158 Returns: Incremented string in A1 format
160 This functions takes a cell reference string in A1 notation and incre‐
162 ments the row. The function will also handle Excel's absolute, "$",
163 cell notation:
164 my $str = xl_inc_row('A1' ); # A2
166 my $str = xl_inc_row('B$2' ); # B$3
167 my $str = xl_inc_row('$C3' ); # $C4
168 my $str = xl_inc_row('$D$4'); # $D$5
169 xl_dec_row($string)
171 Parameters: $string, a string in A1 format
173 Returns: Decremented string in A1 format
175 This functions takes a cell reference string in A1 notation and decre‐
177 ments the row. The function will also handle Excel's absolute, "$",
178 cell notation:
179 my $str = xl_dec_row('A2' ); # A1
181 my $str = xl_dec_row('B$3' ); # B$2
182 my $str = xl_dec_row('$C4' ); # $C3
183 my $str = xl_dec_row('$D$5'); # $D$4
184 xl_inc_col($string)
186 Parameters: $string, a string in A1 format
188 Returns: Incremented string in A1 format
190 This functions takes a cell reference string in A1 notation and incre‐
192 ments the column. The function will also handle Excel's absolute, "$",
193 cell notation:
194 my $str = xl_inc_col('A1' ); # B1
196 my $str = xl_inc_col('Z1' ); # AA1
197 my $str = xl_inc_col('$B1' ); # $C1
198 my $str = xl_inc_col('$D$5'); # $E$5
199 xl_dec_col($string)
201 Parameters: $string, a string in A1 format
203 Returns: Decremented string in A1 format
205 This functions takes a cell reference string in A1 notation and decre‐
207 ments the column. The function will also handle Excel's absolute, "$",
208 cell notation:
209 my $str = xl_dec_col('B1' ); # A1
211 my $str = xl_dec_col('AA1' ); # Z1
212 my $str = xl_dec_col('$C1' ); # $B1
213 my $str = xl_dec_col('$E$5'); # $D$5
214
216 Dates and times in Excel are represented by real numbers, for example
217 "Jan 1 2001 12:30 AM" is represented by the number 36892.521.
218 The integer part of the number stores the number of days since the
220 epoch and the fractional part stores the percentage of the day in sec‐
221 onds.
222 The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and
224 Excel for Macintosh uses 1904. The epochs are:
225 1900: 0 January 1900 i.e. 31 December 1899
227 1904: 1 January 1904
228 Excel on Windows and the Macintosh will convert automatically between
230 one system and the other. By default Spreadsheet::WriteExcel uses the
231 1900 format. To use the 1904 epoch you must use the "set_1904()" work‐
232 book method, see the Spreadsheet::WriteExcel documentation.
233 There are two things to note about the 1900 date format. The first is
235 that the epoch starts on 0 January 1900. The second is that the year
236 1900 is erroneously but deliberately treated as a leap year. Therefore
237 you must add an extra day to dates after 28 February 1900. The func‐
238 tions in the following section will deal with these issues automati‐
239 cally. The reason for this anomaly is explained at http://support.mi‐
240 crosoft.com/support/kb/articles/Q181/3/70.asp
241 Note, a date or time in Excel is like any other number. To display the
243 number as a date you must apply a number format to it: Refer to the
244 "set_num_format()" method in the Spreadsheet::WriteExcel documentation:
245 $date = xl_date_list(2001, 1, 1, 12, 30);
247 $format->set_num_format('mmm d yyyy hh:mm AM/PM');
248 $worksheet->write('A1', $date , $format); # Jan 1 2001 12:30 AM
249 To use these functions you must install the "Date::Manip" and
251 "Date::Calc" modules. See REQUIREMENTS and the individual requirements
252 of each functions.
253 See also the DateTime::Format::Excel mod‐
255 ule,http://search.cpan.org/search?dist=DateTime-Format-Excel which is
256 part of the DateTime project and which deals specifically with convert‐
257 ing dates and times to and from Excel's format.
258 xl_date_list($years, $months, $days, $hours, $minutes, $seconds)
260 Parameters: $years: Integer
262 $months: Integer [optional, default is 1]
263 $days: Integer [optional, default is 1]
264 $hours: Integer [optional, default is 0]
265 $minutes: Integer [optional, default is 0]
266 $seconds: Float [optional, default is 0]
267 Returns: A number that represents an Excel date
269 or undef for an invalid date.
270 Requires: Date::Calc
272 This function converts an array of data into a number that represents
274 an Excel date. All of the parameters are optional except for $years.
275 $date1 = xl_date_list(2002, 1, 2); # 2 Jan 2002
277 $date2 = xl_date_list(2002, 1, 2, 12); # 2 Jan 2002 12:00 pm
278 $date3 = xl_date_list(2002, 1, 2, 12, 30); # 2 Jan 2002 12:30 pm
279 $date4 = xl_date_list(2002, 1, 2, 12, 30, 45); # 2 Jan 2002 12:30:45 pm
280 This function can be used in conjunction with functions that parse date
282 and time strings. In fact it is used in most of the following func‐
283 tions.
284 xl_parse_time($string)
286 Parameters: $string, a textual representation of a time
288 Returns: A number that represents an Excel time
290 or undef for an invalid time.
291 This function converts a time string into a number that represents an
293 Excel time. The following time formats are valid:
294 hh:mm [AM⎪PM]
296 hh:mm [AM⎪PM]
297 hh:mm:ss [AM⎪PM]
298 hh:mm:ss.ss [AM⎪PM]
299 The meridian, AM or PM, is optional and case insensitive. A 24 hour
301 time is assumed if the meridian is omitted
302 $time1 = xl_parse_time('12:18');
304 $time2 = xl_parse_time('12:18:14');
305 $time3 = xl_parse_time('12:18:14 AM');
306 $time4 = xl_parse_time('1:18:14 AM');
307 Time in Excel is expressed as a fraction of the day in seconds. There‐
309 fore you can calculate an Excel time as follows:
310 $time = ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
312 xl_parse_date($string)
314 Parameters: $string, a textual representation of a date and time
316 Returns: A number that represents an Excel date
318 or undef for an invalid date.
319 Requires: Date::Manip and Date::Calc
321 This function converts a date and time string into a number that repre‐
323 sents an Excel date.
324 The parsing is performed using the "ParseDate()" function of the
326 Date::Manip module. Refer to the Date::Manip documentation for further
327 information about the date and time formats that can be parsed. In
328 order to use this function you will probably have to initialise some
329 Date::Manip variables via the "xl_parse_date_init()" function, see
330 below.
331 xl_parse_date_init("TZ=GMT","DateFormat=non-US");
333 $date1 = xl_parse_date("11/7/97");
335 $date2 = xl_parse_date("Friday 11 July 1997");
336 $date3 = xl_parse_date("10:30 AM Friday 11 July 1997");
337 $date4 = xl_parse_date("Today");
338 $date5 = xl_parse_date("Yesterday");
339 Note, if you parse a string that represents a time but not a date this
341 function will add the current date. If you want the time without the
342 date you can do something like the following:
343 $time = xl_parse_date("10:30 AM");
345 $time -= int($time);
346 xl_parse_date_init("variable=value", ...)
348 Parameters: A list of Date::Manip variable strings
350 Returns: A list of all the Date::Manip strings
352 Requires: Date::Manip
354 This function is used to initialise variables required by the
356 Date::Manip module. You should call this function before calling
357 "xl_parse_date()". It need only be called once.
358 This function is a thin wrapper for the "Date::Manip::Date_Init()"
360 function. You can use "Date_Init()" directly if you wish. Refer to the
361 Date::Manip documentation for further information.
362 xl_parse_date_init("TZ=MST","DateFormat=US");
364 $date1 = xl_parse_date("11/7/97"); # November 7th 1997
365 xl_parse_date_init("TZ=GMT","DateFormat=non-US");
367 $date1 = xl_parse_date("11/7/97"); # July 11th 1997
368 xl_decode_date_EU($string)
370 Parameters: $string, a textual representation of a date and time
372 Returns: A number that represents an Excel date
374 or undef for an invalid date.
375 Requires: Date::Calc
377 This function converts a date and time string into a number that repre‐
379 sents an Excel date.
380 The date parsing is performed using the "Decode_Date_EU()" function of
382 the Date::Calc module. Refer to the Date::Calc for further information
383 about the date formats that can be parsed. Also note the following from
384 the Date::Calc documentation:
385 "If the year is given as one or two digits only (i.e., if the year is
387 less than 100), it is mapped to the window 1970 -2069 as follows":
388 0 E<lt>= $year E<lt> 70 ==> $year += 2000;
390 70 E<lt>= $year E<lt> 100 ==> $year += 1900;
391 The time portion of the string is parsed using the "xl_parse_time()"
393 function described above.
394 Note: the EU in the function name means that a European date format is
396 assumed if it is not clear from the string. See the first example
397 below.
398 $date1 = xl_decode_date_EU("11/7/97"); #11 July 1997
400 $date2 = xl_decode_date_EU("Sat 12 Sept 1998");
401 $date3 = xl_decode_date_EU("4:30 AM Sat 12 Sept 1998");
402 xl_decode_date_US($string)
404 Parameters: $string, a textual representation of a date and time
406 Returns: A number that represents an Excel date
408 or undef for an invalid date.
409 Requires: Date::Calc
411 This function converts a date and time string into a number that repre‐
413 sents an Excel date.
414 The date parsing is performed using the "Decode_Date_US()" function of
416 the Date::Calc module. Refer to the Date::Calc for further information
417 about the date formats that can be parsed. Also note the following from
418 the Date::Calc documentation:
419 "If the year is given as one or two digits only (i.e., if the year is
421 less than 100), it is mapped to the window 1970 -2069 as follows":
422 0 <= $year < 70 ==> $year += 2000;
424 70 <= $year < 100 ==> $year += 1900;
425 The time portion of the string is parsed using the "xl_parse_time()"
427 function described above.
428 Note: the US in the function name means that an American date format is
430 assumed if it is not clear from the string. See the first example
431 below.
432 $date1 = xl_decode_date_US("11/7/97"); # 7 November 1997
434 $date2 = xl_decode_date_US("12 Sept Saturday 1998");
435 $date3 = xl_decode_date_US("4:30 AM 12 Sept Sat 1998");
436 xl_date_1904($date)
438 Parameters: $date, an Excel date with a 1900 epoch
440 Returns: an Excel date with a 1904 epoch or zero if
442 the $date is before 1904
443 This function converts an Excel date based on the 1900 epoch into a
445 date based on the 1904 epoch.
446 $date1 = xl_date_list(2002, 1, 13); # 13 Jan 2002, 1900 epoch
448 $date2 = xl_date_1904($date1); # 13 Jan 2002, 1904 epoch
449 See also the "set_1904()" workbook method in the Spreadsheet::WriteEx‐
451 cel documentation.
452
454 The date and time functions require functions from the "Date::Manip"
455 and "Date::Calc" modules. The required functions are "autoused" from
456 these modules so that you do not have to install them unless you wish
457 to use the date and time routines. Therefore it is possible to use the
458 row and column functions without having "Date::Manip" and "Date::Calc"
459 installed.
460 For more information about "autousing" refer to the documentation on
462 the "autouse" pragma.
463
465 When using the autoused functions from "Date::Manip" and "Date::Calc"
466 on Perl 5.6.0 with "-w" you will get a warning like this:
467 "Subroutine xxx redefined ..."
469 The current workaround for this is to put "use warnings;" near the
471 beginning of your program.
472
474 John McNamara jmcnamara@cpan.org
475
477 © MM-MMVI, John McNamara.
478 All Rights Reserved. This module is free software. It may be used,
480 redistributed and/or modified under the same terms as Perl itself.
481perl v5.8.8 2006-05-20Spreadsheet::WriteExcel::Utility(3)