Spreadsheet::ParseExcel::Utility - Utility functions for Spreadsheet::ParseExcel.
use strict; 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, $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 sample/chkFmt.pl in this distribution.
ex.
($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 seconds(ms).
$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.
$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)
($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 $sStr, excel coord (eg. A2).
Note: this function doesn't currently handle embedded commas in the extracted data. Use Ken Prows' xls2cvs (http://search.cpan.org/~ken/xls2csv-1.06/script/xls2csv) or H.Merijn Brand's xls2cvs (which is part of Spreadsheet::Read http://search.cpan.org/~hmbrand/Spreadsheet-Read/) instead. Both of these use Text::CSV_XS.
$sCsvTxt = xls2csv($sFileName, $sRegion, $iRotate);
convert a chunk of an excel file into csv text chunk $sRegions = "sheet-colrow:colrow" (ex. '1-A1:B2' means 'A1:B2' for sheet 1) $iRotate = 0 or 1 (output should be rotated or not)
Maintainer 0.40+: John McNamara jmcnamara@cpan.org
Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org
Original author: Kawai Takanori kwitknr@cpan.org
Copyright (c) 2009 John McNamara
Copyright (c) 2006-2008 Gabor Szabo
Copyright (c) 2000-2006 Kawai Takanori
All rights reserved.
You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.
To install Spreadsheet::ParseExcel, copy and paste the appropriate command in to your terminal.
cpanm
cpanm Spreadsheet::ParseExcel
CPAN shell
perl -MCPAN -e shell install Spreadsheet::ParseExcel
For more information on module installation, please visit the detailed CPAN module installation guide.