1Excel::Writer::XLSX::UtUisleirtyC(o3n)tributed Perl DocuEmxecnetla:t:iWorniter::XLSX::Utility(3)
2
6 Utility - Helper functions for Excel::Writer::XLSX.
7
9 Functions to help with some common tasks when using
10 Excel::Writer::XLSX.
11 These functions mainly relate to dealing with rows and columns in A1
13 notation and to handling dates and times.
14 use Excel::Writer::XLSX::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_col_to_name( 702 ); # AAA
20 $str = xl_inc_col( 'Z1' ); # AA1
21 $str = xl_dec_col( 'AA1' ); # Z1
22 $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
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 Row and column functions: these are used to deal with Excel's A1
34 representation of cells. The functions in this category are:
35 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 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 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
66 Excel::Writer::XLSX 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
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 (0, 0) # The top left cell in row-column notation.
75 ('A1') # The top left cell in A1 notation.
76 (1999, 29) # Row-column notation.
78 ('AD2000') # The same cell in A1 notation.
79 Row-column notation is useful if you are referring to cells
81 programmatically:
82 for my $i ( 0 .. 9 ) {
84 $worksheet->write( $i, 0, 'Hello' ); # Cells A1 to A10
85 }
86 A1 notation is useful for setting up a worksheet manually and for
88 working with formulas:
89 $worksheet->write( 'H1', 200 );
91 $worksheet->write( 'H2', '=H7+1' );
92 The functions in the following sections can be used for dealing with A1
94 notation, for example:
95 ( $row, $col ) = xl_cell_to_rowcol('C2'); # (1, 2)
97 $str = xl_rowcol_to_cell( 1, 2 ); # C2
98 Cell references in Excel can be either relative or absolute. Absolute
100 references are prefixed by the dollar symbol as shown below:
101 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 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 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 Returns: A string in A1 cell notation
118 This function converts a zero based row and column cell reference to a
120 A1 style string:
121 $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 The optional parameters $row_absolute and $col_absolute can be used to
127 indicate if the row or column is absolute:
128 $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 See above for an explanation of absolute cell references.
134 xl_cell_to_rowcol($string)
136 Parameters: $string String in A1 format
137 Returns: List ($row, $col)
139 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 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 xl_col_to_name($col, $col_absolute)
152 Parameters: $col: Integer
153 $col_absolute: Boolean (1/0) [optional, default is 0]
154 Returns: A column string name.
156 This function converts a zero based column reference to a string:
158 $str = xl_col_to_name(0); # A
160 $str = xl_col_to_name(1); # B
161 $str = xl_col_to_name(702); # AAA
162 The optional parameter $col_absolute can be used to indicate if the
164 column is absolute:
165 $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 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 Returns: A worksheet range formula as a string.
183 This function converts zero based row and column cell references to an
185 A1 style range string:
186 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 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 Returns: A worksheet range formula as a string.
202 This function converts zero based row and column cell references to an
204 A1 style formula string:
205 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 This is useful for setting ranges in Chart objects:
211 $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 # Which is the same as:
218 $chart->add_series(
220 categories => '=Sheet1!$A$2:$A$10',
221 values => '=Sheet1!$B$2:$B$10',
222 );
223 xl_inc_row($string)
225 Parameters: $string, a string in A1 format
226 Returns: Incremented string in A1 format
228 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 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 xl_dec_row($string)
239 Parameters: $string, a string in A1 format
240 Returns: Decremented string in A1 format
242 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 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 xl_inc_col($string)
253 Parameters: $string, a string in A1 format
254 Returns: Incremented string in A1 format
256 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 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 xl_dec_col($string)
267 Parameters: $string, a string in A1 format
268 Returns: Decremented string in A1 format
270 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 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
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 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 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 $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 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 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 For a "DateTime.pm" solution see the DateTime::Format::Excel module.
307 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 Returns: A number that represents an Excel date
317 or undef for an invalid date.
318 Requires: Date::Calc
320 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 $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 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 xl_parse_time($string)
334 Parameters: $string, a textual representation of a time
335 Returns: A number that represents an Excel time
337 or undef for an invalid time.
338 This function converts a time string into a number that represents an
340 Excel time. The following time formats are valid:
341 hh:mm [AM|PM]
343 hh:mm [AM|PM]
344 hh:mm:ss [AM|PM]
345 hh:mm:ss.ss [AM|PM]
346 The meridian, AM or PM, is optional and case insensitive. A 24 hour
348 time is assumed if the meridian is omitted.
349 $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 Time in Excel is expressed as a fraction of the day in seconds.
356 Therefore you can calculate an Excel time as follows:
357 $time = ( $hours * 3600 + $minutes * 60 + $seconds ) / ( 24 * 60 * 60 );
359 xl_parse_date($string)
361 Parameters: $string, a textual representation of a date and time
362 Returns: A number that represents an Excel date
364 or undef for an invalid date.
365 Requires: Date::Manip and Date::Calc
367 This function converts a date and time string into a number that
369 represents an Excel date.
370 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 xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
379 $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 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 $time = xl_parse_date( "10:30 AM" );
391 $time -= int( $time );
392 xl_parse_date_init("variable=value", ...)
394 Parameters: A list of Date::Manip variable strings
395 Returns: A list of all the Date::Manip strings
397 Requires: Date::Manip
399 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 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 xl_parse_date_init( "TZ=MST", "DateFormat=US" );
409 $date1 = xl_parse_date( "11/7/97" ); # November 7th 1997
410 xl_parse_date_init( "TZ=GMT", "DateFormat=non-US" );
412 $date1 = xl_parse_date( "11/7/97" ); # July 11th 1997
413 xl_decode_date_EU($string)
415 Parameters: $string, a textual representation of a date and time
416 Returns: A number that represents an Excel date
418 or undef for an invalid date.
419 Requires: Date::Calc
421 This function converts a date and time string into a number that
423 represents an Excel date.
424 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 "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 0 <= $year < 70 ==> $year += 2000;
434 70 <= $year < 100 ==> $year += 1900;
435 The time portion of the string is parsed using the "xl_parse_time()"
437 function described above.
438 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 $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 xl_decode_date_US($string)
448 Parameters: $string, a textual representation of a date and time
449 Returns: A number that represents an Excel date
451 or undef for an invalid date.
452 Requires: Date::Calc
454 This function converts a date and time string into a number that
456 represents an Excel date.
457 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 "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 0 <= $year < 70 ==> $year += 2000;
467 70 <= $year < 100 ==> $year += 1900;
468 The time portion of the string is parsed using the "xl_parse_time()"
470 function described above.
471 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 $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 xl_date_1904($date)
481 Parameters: $date, an Excel date with a 1900 epoch
482 Returns: an Excel date with a 1904 epoch or zero if
484 the $date is before 1904
485 This function converts an Excel date based on the 1900 epoch into a
487 date based on the 1904 epoch.
488 $date1 = xl_date_list( 2002, 1, 13 ); # 13 Jan 2002, 1900 epoch
490 $date2 = xl_date_1904( $date1 ); # 13 Jan 2002, 1904 epoch
491 See also the "set_1904()" workbook method in the Excel::Writer::XLSX
493 documentation.
494
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 For more information about "autousing" refer to the documentation on
504 the "autouse" pragma.
505
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 "Subroutine xxx redefined ..."
511 The current workaround for this is to put "use warnings;" near the
513 beginning of your program.
514
516 John McNamara jmcnamara@cpan.org
517
519 Copyright MM-MMXXI, John McNamara.
520 All Rights Reserved. This module is free software. It may be used,
522 redistributed and/or modified under the same terms as Perl itself.
523perl v5.36.0 2022-07-22 Excel::Writer::XLSX::Utility(3)