1Spreadsheet::WriteExcelU:s:eUrtiClointtyr(i3b)uted PerlSDporceuamdesnhteaetti:o:nWriteExcel::Utility(3)
2
6 Utility - Helper functions for Spreadsheet::WriteExcel.
7
9 Functions to help with some common tasks when using
10 Spreadsheet::WriteExcel.
11 These functions mainly relate to dealing with rows and columns in A1
13 notation and to handling dates and times.
14 use Spreadsheet::WriteExcel::Utility; # Import everything
16 ($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 $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
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 Row and column functions: these are used to deal with Excel's A1
33 representation of cells. The functions in this category are:
34 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 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 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 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 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
63 Spreadsheet::WriteExcel supports two forms of notation to designate the
64 position of cells: Row-column notation and A1 notation.
65 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 (0, 0) # The top left cell in row-column notation.
73 ('A1') # The top left cell in A1 notation.
74 (1999, 29) # Row-column notation.
76 ('AD2000') # The same cell in A1 notation.
77 Row-column notation is useful if you are referring to cells
79 programmatically:
80 for my $i (0 .. 9) {
82 $worksheet->write($i, 0, 'Hello'); # Cells A1 to A10
83 }
84 A1 notation is useful for setting up a worksheet manually and for
86 working with formulas:
87 $worksheet->write('H1', 200);
89 $worksheet->write('H2', '=H7+1');
90 The functions in the following sections can be used for dealing with A1
92 notation, for example:
93 ($row, $col) = xl_cell_to_rowcol('C2'); # (1, 2)
95 $str = xl_rowcol_to_cell(1, 2); # C2
96 Cell references in Excel can be either relative or absolute. Absolute
98 references are prefixed by the dollar symbol as shown below:
99 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 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 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 Returns: A string in A1 cell notation
116 This function converts a zero based row and column cell reference to a
118 A1 style string:
119 $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 The optional parameters $row_absolute and $col_absolute can be used to
125 indicate if the row or column is absolute:
126 $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 See "ROW AND COLUMN FUNCTIONS" for an explanation of absolute cell
132 references.
133 xl_cell_to_rowcol($string)
135 Parameters: $string String in A1 format
136 Returns: List ($row, $col)
138 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 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 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 Returns: A worksheet range formula as a string.
158 This function converts zero based row and column cell references to an
160 A1 style formula string:
161 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 This is useful for setting ranges in Chart objects:
167 $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 # Which is the same as:
174 $chart->add_series(
176 categories => '=Sheet1!$A$2:$A$10',
177 values => '=Sheet1!$B$2:$B$10',
178 );
179 xl_inc_row($string)
181 Parameters: $string, a string in A1 format
182 Returns: Incremented string in A1 format
184 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 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 xl_dec_row($string)
195 Parameters: $string, a string in A1 format
196 Returns: Decremented string in A1 format
198 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 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 xl_inc_col($string)
209 Parameters: $string, a string in A1 format
210 Returns: Incremented string in A1 format
212 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 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 xl_dec_col($string)
223 Parameters: $string, a string in A1 format
224 Returns: Decremented string in A1 format
226 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 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
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 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 The epoch can be either 1900 or 1904. Excel for Windows uses 1900 and
245 Excel for Macintosh uses 1904. The epochs are:
246 1900: 0 January 1900 i.e. 31 December 1899
248 1904: 1 January 1904
249 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()"
253 workbook method, see the Spreadsheet::WriteExcel documentation.
254 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 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 $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 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 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 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 Returns: A number that represents an Excel date
289 or undef for an invalid date.
290 Requires: Date::Calc
292 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 $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 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 xl_parse_time($string)
306 Parameters: $string, a textual representation of a time
307 Returns: A number that represents an Excel time
309 or undef for an invalid time.
310 This function converts a time string into a number that represents an
312 Excel time. The following time formats are valid:
313 hh:mm [AM|PM]
315 hh:mm [AM|PM]
316 hh:mm:ss [AM|PM]
317 hh:mm:ss.ss [AM|PM]
318 The meridian, AM or PM, is optional and case insensitive. A 24 hour
320 time is assumed if the meridian is omitted
321 $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 Time in Excel is expressed as a fraction of the day in seconds.
328 Therefore you can calculate an Excel time as follows:
329 $time = ($hours*3600 +$minutes*60 +$seconds)/(24*60*60);
331 xl_parse_date($string)
333 Parameters: $string, a textual representation of a date and time
334 Returns: A number that represents an Excel date
336 or undef for an invalid date.
337 Requires: Date::Manip and Date::Calc
339 This function converts a date and time string into a number that
341 represents an Excel date.
342 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
348 below.
349 xl_parse_date_init("TZ=GMT","DateFormat=non-US");
351 $date1 = xl_parse_date("11/7/97");
353 $date2 = xl_parse_date("Friday 11 July 1997");
354 $date3 = xl_parse_date("10:30 AM Friday 11 July 1997");
355 $date4 = xl_parse_date("Today");
356 $date5 = xl_parse_date("Yesterday");
357 Note, if you parse a string that represents a time but not a date this
359 function will add the current date. If you want the time without the
360 date you can do something like the following:
361 $time = xl_parse_date("10:30 AM");
363 $time -= int($time);
364 xl_parse_date_init("variable=value", ...)
366 Parameters: A list of Date::Manip variable strings
367 Returns: A list of all the Date::Manip strings
369 Requires: Date::Manip
371 This function is used to initialise variables required by the
373 Date::Manip module. You should call this function before calling
374 "xl_parse_date()". It need only be called once.
375 This function is a thin wrapper for the "Date::Manip::Date_Init()"
377 function. You can use "Date_Init()" directly if you wish. Refer to the
378 Date::Manip documentation for further information.
379 xl_parse_date_init("TZ=MST","DateFormat=US");
381 $date1 = xl_parse_date("11/7/97"); # November 7th 1997
382 xl_parse_date_init("TZ=GMT","DateFormat=non-US");
384 $date1 = xl_parse_date("11/7/97"); # July 11th 1997
385 xl_decode_date_EU($string)
387 Parameters: $string, a textual representation of a date and time
388 Returns: A number that represents an Excel date
390 or undef for an invalid date.
391 Requires: Date::Calc
393 This function converts a date and time string into a number that
395 represents an Excel date.
396 The date parsing is performed using the "Decode_Date_EU()" function of
398 the Date::Calc module. Refer to the Date::Calc for further information
399 about the date formats that can be parsed. Also note the following from
400 the Date::Calc documentation:
401 "If the year is given as one or two digits only (i.e., if the year is
403 less than 100), it is mapped to the window 1970 -2069 as follows":
404 0 E<lt>= $year E<lt> 70 ==> $year += 2000;
406 70 E<lt>= $year E<lt> 100 ==> $year += 1900;
407 The time portion of the string is parsed using the "xl_parse_time()"
409 function described above.
410 Note: the EU in the function name means that a European date format is
412 assumed if it is not clear from the string. See the first example
413 below.
414 $date1 = xl_decode_date_EU("11/7/97"); #11 July 1997
416 $date2 = xl_decode_date_EU("Sat 12 Sept 1998");
417 $date3 = xl_decode_date_EU("4:30 AM Sat 12 Sept 1998");
418 xl_decode_date_US($string)
420 Parameters: $string, a textual representation of a date and time
421 Returns: A number that represents an Excel date
423 or undef for an invalid date.
424 Requires: Date::Calc
426 This function converts a date and time string into a number that
428 represents an Excel date.
429 The date parsing is performed using the "Decode_Date_US()" function of
431 the Date::Calc module. Refer to the Date::Calc for further information
432 about the date formats that can be parsed. Also note the following from
433 the Date::Calc documentation:
434 "If the year is given as one or two digits only (i.e., if the year is
436 less than 100), it is mapped to the window 1970 -2069 as follows":
437 0 <= $year < 70 ==> $year += 2000;
439 70 <= $year < 100 ==> $year += 1900;
440 The time portion of the string is parsed using the "xl_parse_time()"
442 function described above.
443 Note: the US in the function name means that an American date format is
445 assumed if it is not clear from the string. See the first example
446 below.
447 $date1 = xl_decode_date_US("11/7/97"); # 7 November 1997
449 $date2 = xl_decode_date_US("12 Sept Saturday 1998");
450 $date3 = xl_decode_date_US("4:30 AM 12 Sept Sat 1998");
451 xl_date_1904($date)
453 Parameters: $date, an Excel date with a 1900 epoch
454 Returns: an Excel date with a 1904 epoch or zero if
456 the $date is before 1904
457 This function converts an Excel date based on the 1900 epoch into a
459 date based on the 1904 epoch.
460 $date1 = xl_date_list(2002, 1, 13); # 13 Jan 2002, 1900 epoch
462 $date2 = xl_date_1904($date1); # 13 Jan 2002, 1904 epoch
463 See also the "set_1904()" workbook method in the
465 Spreadsheet::WriteExcel documentation.
466
468 The date and time functions require functions from the "Date::Manip"
469 and "Date::Calc" modules. The required functions are "autoused" from
470 these modules so that you do not have to install them unless you wish
471 to use the date and time routines. Therefore it is possible to use the
472 row and column functions without having "Date::Manip" and "Date::Calc"
473 installed.
474 For more information about "autousing" refer to the documentation on
476 the "autouse" pragma.
477
479 When using the autoused functions from "Date::Manip" and "Date::Calc"
480 on Perl 5.6.0 with "-w" you will get a warning like this:
481 "Subroutine xxx redefined ..."
483 The current workaround for this is to put "use warnings;" near the
485 beginning of your program.
486
488 John McNamara jmcnamara@cpan.org
489
491 X MM-MMX, John McNamara.
492 All Rights Reserved. This module is free software. It may be used,
494 redistributed and/or modified under the same terms as Perl itself.
495perl v5.12.0 2010-01-21Spreadsheet::WriteExcel::Utility(3)