Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
Spreadsheet::ParseExceUsertContributed PerlSpreadsheet::ParseExcel::Utility(3)

NAME
       Spreadsheet::ParseExcel::Utility	- Utility functions for
       Spreadsheet::ParseExcel.

SYNOPSIS
	   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, "\n"; # 23459.5070717593 (Excel date/time format)

	   # Convert Excel Time	to localtime
	   my @time = ExcelLocaltime($datetime);
	   print join(":", @time), "\n";   # 11:10:12:23:2:64:1:0

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

DESCRIPTION
       The "Spreadsheet::ParseExcel::Utility" module provides utility
       functions for working with ParseExcel and Excel data.

Functions
       "Spreadsheet::ParseExcel::Utility" can export the following functions:

	   ExcelFmt
	   ExcelLocaltime
	   LocaltimeExcel
	   col2int
	   int2col
	   sheetRef
	   xls2csv

       These functions must be imported	implicitly:

	   # Just one function.
	   use Spreadsheet::ParseExcel::Utility	'col2int';

	   # More than one.
	   use Spreadsheet::ParseExcel::Utility	qw(ExcelFmt ExcelLocaltime LocaltimeExcel);

   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	'$#,##0.00'
       for currency and	a value	of 1234.567 would be displayed as follows:

	   '$#,##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)
       The "ExcelLocaltime()" function converts	from an	Excel date/time	number
       to a "localtime()"-like array of	values:

	       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)
       The "LocaltimeExcel()" function converts	from a "localtime()"-like
       array of	values to an Excel date/time number:

	   $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)
       The "col2int()" function	converts an Excel column letter	to an zero-
       indexed column number:

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

       This function was contributed by	Kevin Mulholland.

   int2col($column_number)
       The "int2col()" function	converts an zero-indexed Excel column number
       to a column letter:

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

       This function was contributed by	Kevin Mulholland.

   sheetRef($cell_string)
       The "sheetRef()"	function converts an Excel cell	reference in 'A1'
       notation	to a zero-indexed "(row, col)" pair.

	   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)
       The "xls2csv()" function	converts a section of an Excel file into a CSV
       text string.

	   $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/

AUTHOR
       Current maintainer 0.60+: Douglas Wilson	dougw@cpan.org

       Maintainer 0.40-0.59: John McNamara jmcnamara@cpan.org

       Maintainer 0.27-0.33: Gabor Szabo szabgab@cpan.org

       Original	author:	Kawai Takanori kwitknr@cpan.org

COPYRIGHT
       Copyright (c) 2014 Douglas Wilson

       Copyright (c) 2009-2013 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.

perl v5.32.0			  2014-03-1Spreadsheet::ParseExcel::Utility(3)

NAME | SYNOPSIS | DESCRIPTION | Functions | AUTHOR | COPYRIGHT

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=Spreadsheet::ParseExcel::Utility&sektion=3&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help