Spreadsheet::ParseExcel::Utility - Utility function for Spreadsheet::ParseExcel

    use strict;
    #Declare
    use Spreadsheet::ParseExcel::Utility qw(ExcelFmt ExcelLocaltime LocaltimeExcel);

    #Convert localtime ->Excel Time
    my $iBirth = LocaltimeExcel(11, 10, 12, 23, 2, 64);
                               # = 1964-3-23 12:10:11
    print $iBirth, "\n";       # 23459.5070717593

    #Convert Excel Time -> localtime
    my @aBirth = ExcelLocaltime($iBirth, undef);
    print join(":", @aBirth), "\n";   # 11:10:12:23:2:64:1:0

    #Formatting
    print ExcelFmt('yyyy-mm-dd', $iBirth), "\n"; #1964-3-23
    print ExcelFmt('m-d-yy', $iBirth), "\n";     # 3-23-64
    print ExcelFmt('#,##0', $iBirth), "\n";      # 23,460
    print ExcelFmt('#,##0.00', $iBirth), "\n";   # 23,459.51
    print ExcelFmt('"My Birthday is (m/d):" m/d', $iBirth), "\n";
                                      # My Birthday is (m/d): 3/23

Spreadsheet::ParseExcel::Utility exports utility functions concerned with Excel format setting.

This module can export 3 functions: ExcelFmt, ExcelLocaltime and LocaltimeExcel.

$sTxt = ExcelFmt($sFmt, CW$iData [, CW$i1904]);

$sFmt is a format string for Excel. $iData is the target value. If $flg1904 is true, this functions assumes that epoch is 1904. $sTxt is the result.

For more detail and examples, please refer sample/chkFmt.pl in this distribution.

ex.

($iSec, CW$iMin, CW$iHour, CW$iDay, CW$iMon, CW$iYear, CW$iwDay, CW$iMSec) = ExcelLocaltime($iExTime [, CW$flg1904]);

ExcelLocaltime converts time information in Excel format into Perl localtime format. $iExTime is a time of Excel. If $flg1904 is true, this functions assumes that epoch is 1904. $iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay are same as localtime. $iMSec means 1/1,000,000 seconds(ms).

$iExTime = LocaltimeExcel($iSec, CW$iMin, CW$iHour, CW$iDay, CW$iMon, CW$iYear [,$iMSec] [,$flg1904])

LocaltimeExcel converts time information in Perl localtime format into Excel format . $iSec, $iMin, $iHour, $iDay, $iMon, $iYear are same as localtime.

If $flg1904 is true, this functions assumes that epoch is 1904. $iExTime is a time of Excel.

$iInt = col2int($sCol);

converts a excel row letter into an int for use in an array

This function was contributed by Kevin Mulholland.

$sCol = int2col($iRow);

convert a column number into column letters NOET: This is quite a brute force coarse method does not manage values over 701 (ZZ)

This function was contributed by Kevin Mulholland.

($iRow, $iCol) = sheetRef($sStr);

convert an excel letter-number address into a useful array address NOTE: That also Excel uses X-Y notation, we normally use Y-X in arrays CW$sStr, excel coord (eg. A2).

This function was contributed by Kevin Mulholland.

$sCsvTxt = xls2csv($sFileName, CW$sRegion, CW$iRotate);

convert a chunk of an excel file into csv text chunk CW$sRegions = sheet-colrow:colrow (ex. '1-A1:B2' means 'A1:B2' for sheet 1) CW$iRotate = 0 or 1 (output should be rotated or not)

This function was contributed by Kevin Mulholland.

Kawai Takanori (Hippo2000) kwitknr@cpan.org

    http://member.nifty.ne.jp/hippo2000/            (Japanese)
    http://member.nifty.ne.jp/hippo2000/index_e.htm (English)

Spreadsheet::ParseExcel, Spreadsheet::WriteExcel

This module is part of the Spreadsheet::ParseExcel distribution.