.\" Automatically generated by Pod::Man 2.27 (Pod::Simple 3.28)
.\"
.\" Standard preamble:
.\" ========================================================================
.de Sp \" Vertical space (when we can't use .PP)
.if t .sp .5v
.if n .sp
..
.de Vb \" Begin verbatim text
.ft CW
.nf
.ne \\$1
..
.de Ve \" End verbatim text
.ft R
.fi
..
.\" Set up some character translations and predefined strings.  \*(-- will
.\" give an unbreakable dash, \*(PI will give pi, \*(L" will give a left
.\" double quote, and \*(R" will give a right double quote.  \*(C+ will
.\" give a nicer C++.  Capital omega is used to do unbreakable dashes and
.\" therefore won't be available.  \*(C` and \*(C' expand to `' in nroff,
.\" nothing in troff, for use with C<>.
.tr \(*W-
.ds C+ C\v'-.1v'\h'-1p'\s-2+\h'-1p'+\s0\v'.1v'\h'-1p'
.ie n \{\
.    ds -- \(*W-
.    ds PI pi
.    if (\n(.H=4u)&(1m=24u) .ds -- \(*W\h'-12u'\(*W\h'-12u'-\" diablo 10 pitch
.    if (\n(.H=4u)&(1m=20u) .ds -- \(*W\h'-12u'\(*W\h'-8u'-\"  diablo 12 pitch
.    ds L" ""
.    ds R" ""
.    ds C` ""
.    ds C' ""
'br\}
.el\{\
.    ds -- \|\(em\|
.    ds PI \(*p
.    ds L" ``
.    ds R" ''
.    ds C`
.    ds C'
'br\}
.\"
.\" Escape single quotes in literal strings from groff's Unicode transform.
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\"
.\" If the F register is turned on, we'll generate index entries on stderr for
.\" titles (.TH), headers (.SH), subsections (.SS), items (.Ip), and index
.\" entries marked with X<> in POD.  Of course, you'll have to process the
.\" output yourself in some meaningful fashion.
.\"
.\" Avoid warning from groff about undefined register 'F'.
.de IX
..
.nr rF 0
.if \n(.g .if rF .nr rF 1
.if (\n(rF:(\n(.g==0)) \{
.    if \nF \{
.        de IX
.        tm Index:\\$1\t\\n%\t"\\$2"
..
.        if !\nF==2 \{
.            nr % 0
.            nr F 2
.        \}
.    \}
.\}
.rr rF
.\" ========================================================================
.\"
.IX Title "Spreadsheet::ParseExcel::Utility 3"
.TH Spreadsheet::ParseExcel::Utility 3 "2014-03-19" "perl v5.16.3" "User Contributed Perl Documentation"
.\" For nroff, turn off justification.  Always turn off hyphenation; it makes
.\" way too many mistakes in technical documents.
.if n .ad l
.nh
.SH "NAME"
Spreadsheet::ParseExcel::Utility \- Utility functions for Spreadsheet::ParseExcel.
.SH "SYNOPSIS"
.IX Header "SYNOPSIS"
.Vb 1
\&    use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);
\&
\&    # Convert localtime to Excel time
\&    my $datetime = LocaltimeExcel(11, 10, 12, 23, 2, 64); # 1964\-3\-23 12:10:11
\&
\&    print $datetime, "\en"; # 23459.5070717593 (Excel date/time format)
\&
\&    # Convert Excel Time to localtime
\&    my @time = ExcelLocaltime($datetime);
\&    print join(":", @time), "\en";   # 11:10:12:23:2:64:1:0
\&
\&    # Formatting
\&    print ExcelFmt(\*(Aqyyyy\-mm\-dd\*(Aq, $datetime), "\en"; # 1964\-3\-23
\&    print ExcelFmt(\*(Aqm\-d\-yy\*(Aq,     $datetime), "\en"; # 3\-23\-64
\&    print ExcelFmt(\*(Aq#,##0\*(Aq,      $datetime), "\en"; # 23,460
\&    print ExcelFmt(\*(Aq#,##0.00\*(Aq,   $datetime), "\en"; # 23,459.51
.Ve
.SH "DESCRIPTION"
.IX Header "DESCRIPTION"
The \f(CW\*(C`Spreadsheet::ParseExcel::Utility\*(C'\fR module provides utility functions for working with ParseExcel and Excel data.
.SH "Functions"
.IX Header "Functions"
\&\f(CW\*(C`Spreadsheet::ParseExcel::Utility\*(C'\fR can export the following functions:
.PP
.Vb 7
\&    ExcelFmt
\&    ExcelLocaltime
\&    LocaltimeExcel
\&    col2int
\&    int2col
\&    sheetRef
\&    xls2csv
.Ve
.PP
These functions must be imported implicitly:
.PP
.Vb 2
\&    # Just one function.
\&    use Spreadsheet::ParseExcel::Utility \*(Aqcol2int\*(Aq;
\&
\&    # More than one.
\&    use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);
.Ve
.ie n .SS "ExcelFmt($format_string, $number, $is_1904)"
.el .SS "ExcelFmt($format_string, \f(CW$number\fP, \f(CW$is_1904\fP)"
.IX Subsection "ExcelFmt($format_string, $number, $is_1904)"
Excel stores data such as dates and currency values as numbers. The way these numbers are displayed is controlled by the number format string for the cell. For example a cell with a number format of \f(CW\*(Aq$#,##0.00\*(Aq\fR for currency and a value of 1234.567 would be displayed as follows:
.PP
.Vb 1
\&    \*(Aq$#,##0.00\*(Aq + 1234.567 = \*(Aq$1,234.57\*(Aq.
.Ve
.PP
The \f(CW\*(C`ExcelFmt()\*(C'\fR function tries to emulate this formatting so that the user can convert raw numbers returned by \f(CW\*(C`Spreadsheet::ParseExel\*(C'\fR to a desired format. For example:
.PP
.Vb 1
\&    print ExcelFmt(\*(Aq$#,##0.00\*(Aq, 1234.567); # $1,234.57.
.Ve
.PP
The syntax of the function is:
.PP
.Vb 1
\&    my $text = ExcelFmt($format_string, $number, $is_1904);
.Ve
.PP
Where \f(CW$format_string\fR is an Excel number format string, \f(CW$number\fR is a real or integer number and \f(CW\*(C`is_1904\*(C'\fR is an optional flag to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.
.PP
\&\f(CW\*(C`ExcelFmt()\*(C'\fR is also used internally to convert numbers returned by the \f(CW\*(C`Cell::unformatted()\*(C'\fR method to the formatted value returned by the \f(CW\*(C`Cell::value()\*(C'\fR method:
.PP
.Vb 1
\&    my $cell = $worksheet\->get_cell( 0, 0 );
\&
\&    print $cell\->unformatted(), "\en"; # 1234.567
\&    print $cell\->value(),       "\en"; # $1,234.57
.Ve
.PP
The most common usage for \f(CW\*(C`ExcelFmt\*(C'\fR is to convert numbers to dates. Dates and times in Excel are represented by real numbers, for example \*(L"1 Jan 2001 12:30 \s-1PM\*(R"\s0 is represented by the number 36892.521. The integer part of the number stores the number of days since the epoch and the fractional part stores the percentage of the day. By applying an Excel number format the number is converted to the desired string representation:
.PP
.Vb 1
\&    print ExcelFmt(\*(Aqd mmm yyyy h:mm AM/PM\*(Aq, 36892.521);  # 1 Jan 2001 12:30 PM
.Ve
.PP
\&\f(CW$is_1904\fR is an optional flag to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch. Excel for Windows generally uses 1900 and Excel for Mac \s-1OS\s0 uses 1904. The \f(CW$is1904\fR flag isn't required very often by a casual user and can usually be ignored.
.ie n .SS "ExcelLocaltime($excel_datetime, $is_1904)"
.el .SS "ExcelLocaltime($excel_datetime, \f(CW$is_1904\fP)"
.IX Subsection "ExcelLocaltime($excel_datetime, $is_1904)"
The \f(CW\*(C`ExcelLocaltime()\*(C'\fR function converts from an Excel date/time number to a \f(CW\*(C`localtime()\*(C'\fR\-like array of values:
.PP
.Vb 1
\&        my @time = ExcelLocaltime($excel_datetime);
\&
\&        #    0     1     2      3     4       5      6      7
\&        my ( $sec, $min, $hour, $day, $month, $year, $wday, $msec ) = @time;
.Ve
.PP
The array elements from \f(CW\*(C`(0 .. 6)\*(C'\fR are the same as Perl's \f(CW\*(C`localtime()\*(C'\fR. The last element \f(CW$msec\fR is milliseconds. In particular it should be noted that, in common with \f(CW\*(C`localtime()\*(C'\fR, the month is zero indexed and the year is the number of years since 1900. This means that you will usually need to do the following:
.PP
.Vb 2
\&        $month++;
\&        $year += 1900;
.Ve
.PP
See also Perl's documentation for \fIlocaltime()\fR:
.PP
The \f(CW$is_1904\fR flag is an optional. It is used to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.
.ie n .SS "LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec, $is_1904)"
.el .SS "LocaltimeExcel($sec, \f(CW$min\fP, \f(CW$hour\fP, \f(CW$day\fP, \f(CW$month\fP, \f(CW$year\fP, \f(CW$wday\fP, \f(CW$msec\fP, \f(CW$is_1904\fP)"
.IX Subsection "LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec, $is_1904)"
The \f(CW\*(C`LocaltimeExcel()\*(C'\fR function converts from a \f(CW\*(C`localtime()\*(C'\fR\-like array of values to an Excel date/time number:
.PP
.Vb 1
\&    $excel_datetime = LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec);
.Ve
.PP
The array elements from \f(CW\*(C`(0 .. 6)\*(C'\fR are the same as Perl's \f(CW\*(C`localtime()\*(C'\fR. The last element \f(CW$msec\fR is milliseconds. In particular it should be noted that, in common with \f(CW\*(C`localtime()\*(C'\fR, the month is zero indexed and the year is the number of years since 1900. See also Perl's documentation for \fIlocaltime()\fR:
.PP
The \f(CW$wday\fR and \f(CW$msec\fR elements are usually optional. This time elements can also be zeroed if they aren't of interest:
.PP
.Vb 2
\&                                    # sec, min, hour, day, month, year
\&    $excel_datetime = LocaltimeExcel( 0,   0,   0,    1,   0,     101 );
\&
\&    print ExcelFmt(\*(Aqd mmm yyyy\*(Aq, $excel_datetime);  # 1 Jan 2001
.Ve
.PP
The \f(CW$is_1904\fR flag is also optional. It is used to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.
.SS "col2int($column)"
.IX Subsection "col2int($column)"
The \f(CW\*(C`col2int()\*(C'\fR function converts an Excel column letter to an zero-indexed column number:
.PP
.Vb 2
\&    print col2int(\*(AqA\*(Aq);  # 0
\&    print col2int(\*(AqAA\*(Aq); # 26
.Ve
.PP
This function was contributed by Kevin Mulholland.
.SS "int2col($column_number)"
.IX Subsection "int2col($column_number)"
The \f(CW\*(C`int2col()\*(C'\fR function converts an zero-indexed Excel column number to a column letter:
.PP
.Vb 2
\&    print int2col(0);  # \*(AqA\*(Aq
\&    print int2col(26); # \*(AqAA\*(Aq
.Ve
.PP
This function was contributed by Kevin Mulholland.
.SS "sheetRef($cell_string)"
.IX Subsection "sheetRef($cell_string)"
The \f(CW\*(C`sheetRef()\*(C'\fR function converts an Excel cell reference in 'A1' notation to a zero-indexed \f(CW\*(C`(row, col)\*(C'\fR pair.
.PP
.Vb 2
\&    my ($row, $col) = sheetRef(\*(AqA1\*(Aq); # ( 0, 0 )
\&    my ($row, $col) = sheetRef(\*(AqC2\*(Aq); # ( 1, 2 )
.Ve
.PP
This function was contributed by Kevin Mulholland.
.ie n .SS "xls2csv($filename, $region, $rotate)"
.el .SS "xls2csv($filename, \f(CW$region\fP, \f(CW$rotate\fP)"
.IX Subsection "xls2csv($filename, $region, $rotate)"
The \f(CW\*(C`xls2csv()\*(C'\fR function converts a section of an Excel file into a \s-1CSV\s0 text string.
.PP
.Vb 1
\&    $csv_text = xls2csv($filename, $region, $rotate);
.Ve
.PP
Where:
.PP
.Vb 2
\&    $region = "sheet\-colrow:colrow"
\&    For example \*(Aq1\-A1:B2\*(Aq means \*(AqA1:B2\*(Aq for sheet 1.
\&
\&    and
\&
\&    $rotate  = 0 or 1 (output is rotated/transposed or not)
.Ve
.PP
This function requires \f(CW\*(C`Text::CSV_XS\*(C'\fR to be installed. It was contributed by Kevin Mulholland along with the \f(CW\*(C`xls2csv\*(C'\fR script in the \f(CW\*(C`sample\*(C'\fR directory of the distro.
.PP
See also the following xls2csv utilities: Ken Prows' \f(CW\*(C`xls2csv\*(C'\fR: http://search.cpan.org/~ken/xls2csv/script/xls2csv and H.Merijn Brand's \f(CW\*(C`xls2csv\*(C'\fR (which is part of Spreadsheet::Read): http://search.cpan.org/~hmbrand/Spreadsheet\-Read/
.SH "AUTHOR"
.IX Header "AUTHOR"
Current maintainer 0.60+: Douglas Wilson dougw@cpan.org
.PP
Maintainer 0.40\-0.59: John McNamara jmcnamara@cpan.org
.PP
Maintainer 0.27\-0.33: Gabor Szabo szabgab@cpan.org
.PP
Original author: Kawai Takanori kwitknr@cpan.org
.SH "COPYRIGHT"
.IX Header "COPYRIGHT"
Copyright (c) 2014 Douglas Wilson
.PP
Copyright (c) 2009\-2013 John McNamara
.PP
Copyright (c) 2006\-2008 Gabor Szabo
.PP
Copyright (c) 2000\-2006 Kawai Takanori
.PP
All rights reserved.
.PP
You may distribute under the terms of either the \s-1GNU\s0 General Public License or the Artistic License, as specified in the Perl \s-1README\s0 file.