ExcelFmt($format_string, $number, $is_1904)

    '$#,##0.00' + 1234.567 = '$1,234.57'.

The "ExcelFmt()" function tries to emulate this formatting so that the user can convert raw numbers returned by "Spreadsheet::ParseExel" to a desired format. For example:

    print ExcelFmt('$#,##0.00', 1234.567); # $1,234.57.

The syntax of the function is:

    my $text = ExcelFmt($format_string, $number, $is_1904);

Where $format_string is an Excel number format string, $number is a real or integer number and "is_1904" is an optional flag to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.

"ExcelFmt()" is also used internally to convert numbers returned by the "Cell::unformatted()" method to the formatted value returned by the "Cell::value()" method:

    my $cell = $worksheet->get_cell( 0, 0 );

    print $cell->unformatted(), "\n"; # 1234.567
    print $cell->value(),       "\n"; # $1,234.57

The most common usage for "ExcelFmt" is to convert numbers to dates. Dates and times in Excel are represented by real numbers, for example "1 Jan 2001 12:30 PM" 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:

    print ExcelFmt('d mmm yyyy h:mm AM/PM', 36892.521);  # 1 Jan 2001 12:30 PM

$is_1904 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 OS uses 1904. The $is1904 flag isn't required very often by a casual user and can usually be ignored.

ExcelLocaltime($excel_datetime, $is_1904)

        my @time = ExcelLocaltime($excel_datetime);

        #    0     1     2      3     4       5      6      7
        my ( $sec, $min, $hour, $day, $month, $year, $wday, $msec ) = @time;

The array elements from "(0 .. 6)" are the same as Perl's "localtime()". The last element $msec is milliseconds. In particular it should be noted that, in common with "localtime()", 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:

        $month++;
        $year += 1900;

See also Perl's documentation for localtime():

The $is_1904 flag is an optional. It is used to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.

LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec, $is_1904)

    $excel_datetime = LocaltimeExcel($sec, $min, $hour, $day, $month, $year, $wday, $msec);

The array elements from "(0 .. 6)" are the same as Perl's "localtime()". The last element $msec is milliseconds. In particular it should be noted that, in common with "localtime()", the month is zero indexed and the year is the number of years since 1900. See also Perl's documentation for localtime():

The $wday and $msec elements are usually optional. This time elements can also be zeroed if they aren't of interest:

                                    # sec, min, hour, day, month, year
    $excel_datetime = LocaltimeExcel( 0,   0,   0,    1,   0,     101 );

    print ExcelFmt('d mmm yyyy', $excel_datetime);  # 1 Jan 2001

The $is_1904 flag is also optional. It is used to indicate that dates should use Excel's 1904 epoch instead of the default 1900 epoch.

col2int($column)

    print col2int('A');  # 0
    print col2int('AA'); # 26

This function was contributed by Kevin Mulholland.

int2col($column_number)

    print int2col(0);  # 'A'
    print int2col(26); # 'AA'

This function was contributed by Kevin Mulholland.

sheetRef($cell_string)

    my ($row, $col) = sheetRef('A1'); # ( 0, 0 )
    my ($row, $col) = sheetRef('C2'); # ( 1, 2 )

This function was contributed by Kevin Mulholland.

xls2csv($filename, $region, $rotate)

    $csv_text = xls2csv($filename, $region, $rotate);

Where:

    $region = "sheet-colrow:colrow"
    For example '1-A1:B2' means 'A1:B2' for sheet 1.

    and

    $rotate  = 0 or 1 (output is rotated/transposed or not)

This function requires "Text::CSV_XS" to be installed. It was contributed by Kevin Mulholland along with the "xls2csv" script in the "sample" directory of the distro.

See also the following xls2csv utilities: Ken Prows' "xls2csv": http://search.cpan.org/~ken/xls2csv/script/xls2csv and H.Merijn Brand's "xls2csv" (which is part of Spreadsheet::Read): http://search.cpan.org/~hmbrand/Spreadsheet-Read/