parseexcel::utility(3)
NAME
Spreadsheet::ParseExcel::Utility - Utility function for
Spreadsheet::ParseExcel
SYNOPSIS
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, "0; # 23459.5070717593
#Convert Excel Time -> localtime
my @aBirth = ExcelLocaltime($iBirth, undef);
print join(":", @aBirth), "0; # 11:10:12:23:2:64:1:0
#Formatting
print ExcelFmt('yyyy-mm-dd', $iBirth), "0; #1964-3-23
print ExcelFmt('m-d-yy', $iBirth), "0; # 3-23-64
print ExcelFmt('#,##0', $iBirth), "0; # 23,460
print ExcelFmt('#,##0.00', $iBirth), "0; # 23,459.51
print ExcelFmt('"My Birthday is (m/d):" m/d',
$iBirth), "0;
# My Birthday is
(m/d): 3/23
DESCRIPTION
Spreadsheet::ParseExcel::Utility exports utility functions
concerned with Excel format setting.
Functions
This module can export 3 functions: ExcelFmt, ExcelLocal
time and LocaltimeExcel.
ExcelFmt
$sTxt = ExcelFmt($sFmt, $iData [, $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 sam
ple/chkFmt.pl in this distribution.
ex.
ExcelLocaltime
- ($iSec, $iMin, $iHour, $iDay, $iMon, $iYear, $iwDay,
$iMSec) = - ExcelLocaltime($iExTime [, $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 sec
onds(ms). - LocaltimeExcel
- $iExTime = LocaltimeExcel($iSec, $iMin, $iHour, $iDay, $iMon, $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.
- col2int
- $iInt = col2int($sCol);
- converts a excel row letter into an int for use in an
array - This function was contributed by Kevin Mulholland.
- int2col
- $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.
- sheetRef
- ($iRow, $iCol) = sheetRef($sStr);
- convert an excel letter-number address into a useful array
address NOTE: That also Excel uses X-Y notation, we nor
mally use Y-X in arrays $sStr, excel coord (eg. A2). - This function was contributed by Kevin Mulholland.
- xls2csv
- $sCsvTxt = xls2csv($sFileName, $sRegion, $iRotate);
- convert a chunk of an excel file into csv text chunk $sRe
gions = "sheet-colrow:colrow" (ex. '1-A1:B2' means 'A1:B2'
for sheet 1) $iRotate = 0 or 1 (output should be rotated
or not) - This function was contributed by Kevin Mulholland.
AUTHOR
- Kawai Takanori (Hippo2000) kwitknr@cpan.org
- http://member.nifty.ne.jp/hippo2000/
- (Japanese)
http://member.nifty.ne.jp/hippo2000/index_e.htm (En - glish)
SEE ALSO
Spreadsheet::ParseExcel, Spreadsheet::WriteExcel
COPYRIGHT
- This module is part of the Spreadsheet::ParseExcel distri
bution.