1Spreadsheet::ParseExcelU:s:eUrtiClointtyr(i3b)uted PerlSDporceuamdesnhteaetti:o:nParseExcel::Utility(3)
2
6 Spreadsheet::ParseExcel::Utility - Utility functions for
7 Spreadsheet::ParseExcel.
8
10 use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);
11 # Convert localtime to Excel time
13 my $datetime = LocaltimeExcel(11, 10, 12, 23, 2, 64); # 1964-3-23 12:10:11
14 print $datetime, "\n"; # 23459.5070717593 (Excel date/time format)
16 # Convert Excel Time to localtime
18 my @time = ExcelLocaltime($datetime);
19 print join(":", @time), "\n"; # 11:10:12:23:2:64:1:0
20 # Formatting
22 print ExcelFmt('yyyy-mm-dd', $datetime), "\n"; # 1964-3-23
23 print ExcelFmt('m-d-yy', $datetime), "\n"; # 3-23-64
24 print ExcelFmt('#,##0', $datetime), "\n"; # 23,460
25 print ExcelFmt('#,##0.00', $datetime), "\n"; # 23,459.51
26
28 The "Spreadsheet::ParseExcel::Utility" module provides utility
29 functions for working with ParseExcel and Excel data.
30
32 "Spreadsheet::ParseExcel::Utility" can export the following functions:
33 ExcelFmt
35 ExcelLocaltime
36 LocaltimeExcel
37 col2int
38 int2col
39 sheetRef
40 xls2csv
41 These functions must be imported implicitly:
43 # Just one function.
45 use Spreadsheet::ParseExcel::Utility 'col2int';
46 # More than one.
48 use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);
49 ExcelFmt($format_string, $number, $is_1904)
51 Excel stores data such as dates and currency values as numbers. The way
52 these numbers are displayed is controlled by the number format string
53 for the cell. For example a cell with a number format of '$#,##0.00'
54 for currency and a value of 1234.567 would be displayed as follows:
55 '$#,##0.00' + 1234.567 = '$1,234.57'.
57 The "ExcelFmt()" function tries to emulate this formatting so that the
59 user can convert raw numbers returned by "Spreadsheet::ParseExel" to a
60 desired format. For example:
61 print ExcelFmt('$#,##0.00', 1234.567); # $1,234.57.
63 The syntax of the function is:
65 my $text = ExcelFmt($format_string, $number, $is_1904);
67 Where $format_string is an Excel number format string, $number is a
69 real or integer number and "is_1904" is an optional flag to indicate
70 that dates should use Excel's 1904 epoch instead of the default 1900
71 epoch.
72 "ExcelFmt()" is also used internally to convert numbers returned by the
74 "Cell::unformatted()" method to the formatted value returned by the
75 "Cell::value()" method:
76 my $cell = $worksheet->get_cell( 0, 0 );
78 print $cell->unformatted(), "\n"; # 1234.567
80 print $cell->value(), "\n"; # $1,234.57
81 The most common usage for "ExcelFmt" is to convert numbers to dates.
83 Dates and times in Excel are represented by real numbers, for example
84 "1 Jan 2001 12:30 PM" is represented by the number 36892.521. The
85 integer part of the number stores the number of days since the epoch
86 and the fractional part stores the percentage of the day. By applying
87 an Excel number format the number is converted to the desired string
88 representation:
89 print ExcelFmt('d mmm yyyy h:mm AM/PM', 36892.521); # 1 Jan 2001 12:30 PM
91 $is_1904 is an optional flag to indicate that dates should use Excel's
93 1904 epoch instead of the default 1900 epoch. Excel for Windows
94 generally uses 1900 and Excel for Mac OS uses 1904. The $is1904 flag
95 isn't required very often by a casual user and can usually be ignored.
96 ExcelLocaltime($excel_datetime, $is_1904)
98 The "ExcelLocaltime()" function converts from an Excel date/time number
99 to a "localtime()"-like array of values:
100 my @time = ExcelLocaltime($excel_datetime);
102 # 0 1 2 3 4 5 6 7
104 my ( $sec, $min, $hour, $day, $month, $year, $wday, $msec ) = @time;
105 The array elements from "(0 .. 6)" are the same as Perl's
107 "localtime()". The last element $msec is milliseconds. In particular it
108 should be noted that, in common with "localtime()", the month is zero
109 indexed and the year is the number of years since 1900. This means that
110 you will usually need to do the following:
111 $month++;
113 $year += 1900;
114 See also Perl's documentation for localtime():
116 The $is_1904 flag is an optional. It is used to indicate that dates
118 should use Excel's 1904 epoch instead of the default 1900 epoch.
119 LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec,
121 $is_1904)
122 The "LocaltimeExcel()" function converts from a "localtime()"-like
123 array of values to an Excel date/time number:
124 $excel_datetime = LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec);
126 The array elements from "(0 .. 6)" are the same as Perl's
128 "localtime()". The last element $msec is milliseconds. In particular it
129 should be noted that, in common with "localtime()", the month is zero
130 indexed and the year is the number of years since 1900. See also Perl's
131 documentation for localtime():
132 The $wday and $msec elements are usually optional. This time elements
134 can also be zeroed if they aren't of interest:
135 # sec, min, hour, day, month, year
137 $excel_datetime = LocaltimeExcel( 0, 0, 0, 1, 0, 101 );
138 print ExcelFmt('d mmm yyyy', $excel_datetime); # 1 Jan 2001
140 The $is_1904 flag is also optional. It is used to indicate that dates
142 should use Excel's 1904 epoch instead of the default 1900 epoch.
143 col2int($column)
145 The "col2int()" function converts an Excel column letter to an zero-
146 indexed column number:
147 print col2int('A'); # 0
149 print col2int('AA'); # 26
150 This function was contributed by Kevin Mulholland.
152 int2col($column_number)
154 The "int2col()" function converts an zero-indexed Excel column number
155 to a column letter:
156 print int2col(0); # 'A'
158 print int2col(26); # 'AA'
159 This function was contributed by Kevin Mulholland.
161 sheetRef($cell_string)
163 The "sheetRef()" function converts an Excel cell reference in 'A1'
164 notation to a zero-indexed "(row, col)" pair.
165 my ($row, $col) = sheetRef('A1'); # ( 0, 0 )
167 my ($row, $col) = sheetRef('C2'); # ( 1, 2 )
168 This function was contributed by Kevin Mulholland.
170 xls2csv($filename, $region, $rotate)
172 The "xls2csv()" function converts a section of an Excel file into a CSV
173 text string.
174 $csv_text = xls2csv($filename, $region, $rotate);
176 Where:
178 $region = "sheet-colrow:colrow"
180 For example '1-A1:B2' means 'A1:B2' for sheet 1.
181 and
183 $rotate = 0 or 1 (output is rotated/transposed or not)
185 This function requires "Text::CSV_XS" to be installed. It was
187 contributed by Kevin Mulholland along with the "xls2csv" script in the
188 "sample" directory of the distro.
189 See also the following xls2csv utilities: Ken Prows' "xls2csv":
191 http://search.cpan.org/~ken/xls2csv/script/xls2csv and H.Merijn Brand's
192 "xls2csv" (which is part of Spreadsheet::Read):
193 http://search.cpan.org/~hmbrand/Spreadsheet-Read/
194
196 Current maintainer 0.60+: Douglas Wilson dougw@cpan.org
197 Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org
199 Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
201 Original author: Kawai Takanori kwitknr@cpan.org
203
205 Copyright (c) 2014 Douglas Wilson
206 Copyright (c) 2009-2013 John McNamara
208 Copyright (c) 2006-2008 Gabor Szabo
210 Copyright (c) 2000-2006 Kawai Takanori
212 All rights reserved.
214 You may distribute under the terms of either the GNU General Public
216 License or the Artistic License, as specified in the Perl README file.
217perl v5.30.0 2019-07-26Spreadsheet::ParseExcel::Utility(3)