PageRenderTime 86ms CodeModel.GetById 30ms app.highlight 38ms RepoModel.GetById 13ms app.codeStats 0ms

/framework/lib/classes/PHPExcel/Calculation/TextData.php

https://bitbucket.org/designbyheart/original
PHP | 588 lines | 314 code | 87 blank | 187 comment | 105 complexity | 4f86f9f1888f60eeb6614e3660aaae81 MD5 | raw file
  1<?php
  2/**
  3 * PHPExcel
  4 *
  5 * Copyright (c) 2006 - 2011 PHPExcel
  6 *
  7 * This library is free software; you can redistribute it and/or
  8 * modify it under the terms of the GNU Lesser General Public
  9 * License as published by the Free Software Foundation; either
 10 * version 2.1 of the License, or (at your option) any later version.
 11 *
 12 * This library is distributed in the hope that it will be useful,
 13 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 14 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 15 * Lesser General Public License for more details.
 16 *
 17 * You should have received a copy of the GNU Lesser General Public
 18 * License along with this library; if not, write to the Free Software
 19 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
 20 *
 21 * @category	PHPExcel
 22 * @package		PHPExcel_Calculation
 23 * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 24 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 25 * @version		1.7.6, 2011-02-27
 26 */
 27
 28
 29/** PHPExcel root directory */
 30if (!defined('PHPEXCEL_ROOT')) {
 31	/**
 32	 * @ignore
 33	 */
 34	define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
 35	require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
 36}
 37
 38
 39/**
 40 * PHPExcel_Calculation_TextData
 41 *
 42 * @category	PHPExcel
 43 * @package		PHPExcel_Calculation
 44 * @copyright	Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
 45 */
 46class PHPExcel_Calculation_TextData {
 47
 48	private static $_invalidChars = Null;
 49
 50	private static function _uniord($c) {
 51		if (ord($c{0}) >=0 && ord($c{0}) <= 127)
 52			return ord($c{0});
 53		if (ord($c{0}) >= 192 && ord($c{0}) <= 223)
 54			return (ord($c{0})-192)*64 + (ord($c{1})-128);
 55		if (ord($c{0}) >= 224 && ord($c{0}) <= 239)
 56			return (ord($c{0})-224)*4096 + (ord($c{1})-128)*64 + (ord($c{2})-128);
 57		if (ord($c{0}) >= 240 && ord($c{0}) <= 247)
 58			return (ord($c{0})-240)*262144 + (ord($c{1})-128)*4096 + (ord($c{2})-128)*64 + (ord($c{3})-128);
 59		if (ord($c{0}) >= 248 && ord($c{0}) <= 251)
 60			return (ord($c{0})-248)*16777216 + (ord($c{1})-128)*262144 + (ord($c{2})-128)*4096 + (ord($c{3})-128)*64 + (ord($c{4})-128);
 61		if (ord($c{0}) >= 252 && ord($c{0}) <= 253)
 62			return (ord($c{0})-252)*1073741824 + (ord($c{1})-128)*16777216 + (ord($c{2})-128)*262144 + (ord($c{3})-128)*4096 + (ord($c{4})-128)*64 + (ord($c{5})-128);
 63		if (ord($c{0}) >= 254 && ord($c{0}) <= 255) //error
 64			return PHPExcel_Calculation_Functions::VALUE();
 65		return 0;
 66	}	//	function _uniord()
 67
 68	/**
 69	 * CHARACTER
 70	 *
 71	 * @param	string	$character	Value
 72	 * @return	int
 73	 */
 74	public static function CHARACTER($character) {
 75		$character	= PHPExcel_Calculation_Functions::flattenSingleValue($character);
 76
 77		if ((!is_numeric($character)) || ($character < 0)) {
 78			return PHPExcel_Calculation_Functions::VALUE();
 79		}
 80
 81		if (function_exists('mb_convert_encoding')) {
 82			return mb_convert_encoding('&#'.intval($character).';', 'UTF-8', 'HTML-ENTITIES');
 83		} else {
 84			return chr(intval($character));
 85		}
 86	}
 87
 88
 89	/**
 90	 *	TRIMNONPRINTABLE
 91	 *
 92	 *	@param	mixed	$value	Value to check
 93	 *	@return	string
 94	 */
 95	public static function TRIMNONPRINTABLE($stringValue = '') {
 96		$stringValue	= PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
 97
 98		if (is_bool($stringValue)) {
 99			$stringValue = ($stringValue) ? 'TRUE' : 'FALSE';
100		}
101
102		if (self::$_invalidChars == Null) {
103			self::$_invalidChars = range(chr(0),chr(31));
104		}
105
106		if (is_string($stringValue) || is_numeric($stringValue)) {
107			return str_replace(self::$_invalidChars,'',trim($stringValue,"\x00..\x1F"));
108		}
109		return Null;
110	}	//	function TRIMNONPRINTABLE()
111
112
113	/**
114	 *	TRIMSPACES
115	 *
116	 *	@param	mixed	$value	Value to check
117	 *	@return	string
118	 */
119	public static function TRIMSPACES($stringValue = '') {
120		$stringValue	= PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
121
122		if (is_string($stringValue) || is_numeric($stringValue)) {
123			return trim(preg_replace('/  +/',' ',$stringValue));
124		}
125		return Null;
126	}	//	function TRIMSPACES()
127
128
129	/**
130	 * ASCIICODE
131	 *
132	 * @param	string	$character	Value
133	 * @return	int
134	 */
135	public static function ASCIICODE($characters) {
136		$characters	= PHPExcel_Calculation_Functions::flattenSingleValue($characters);
137		if (is_bool($characters)) {
138			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
139				$characters = (int) $characters;
140			} else {
141				if ($characters) {
142					$characters = 'True';
143				} else {
144					$characters = 'False';
145				}
146			}
147		}
148
149		$character = $characters;
150		if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
151			if (mb_strlen($characters, 'UTF-8') > 1) { $character = mb_substr($characters, 0, 1, 'UTF-8'); }
152			return self::_uniord($character);
153		} else {
154			if (strlen($characters) > 0) { $character = substr($characters, 0, 1); }
155			return ord($character);
156		}
157	}	//	function ASCIICODE()
158
159
160	/**
161	 * CONCATENATE
162	 *
163	 * @return	string
164	 */
165	public static function CONCATENATE() {
166		// Return value
167		$returnValue = '';
168
169		// Loop through arguments
170		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
171		foreach ($aArgs as $arg) {
172			if (is_bool($arg)) {
173				if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
174					$arg = (int) $arg;
175				} else {
176					if ($arg) {
177						$arg = 'TRUE';
178					} else {
179						$arg = 'FALSE';
180					}
181				}
182			}
183			$returnValue .= $arg;
184		}
185
186		// Return
187		return $returnValue;
188	}	//	function CONCATENATE()
189
190
191	/**
192	 *	DOLLAR
193	 *
194	 *	This function converts a number to text using currency format, with the decimals rounded to the specified place.
195	 *	The format used is $#,##0.00_);($#,##0.00)..
196	 *
197	 *	@param	float	$value			The value to format
198	 *	@param	int		$decimals		The number of digits to display to the right of the decimal point.
199	 *									If decimals is negative, number is rounded to the left of the decimal point.
200	 *									If you omit decimals, it is assumed to be 2
201	 *	@return	string
202	 */
203	public static function DOLLAR($value = 0, $decimals = 2) {
204		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
205		$decimals	= is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
206
207		// Validate parameters
208		if (!is_numeric($value) || !is_numeric($decimals)) {
209			return PHPExcel_Calculation_Functions::NaN();
210		}
211		$decimals = floor($decimals);
212
213		if ($decimals > 0) {
214			return money_format('%.'.$decimals.'n',$value);
215		} else {
216			$round = pow(10,abs($decimals));
217			if ($value < 0) { $round = 0-$round; }
218			$value = PHPExcel_Calculation_MathTrig::MROUND($value,$round);
219			//	The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
220			//		so we display to 1 dp and chop off that character and the decimal separator using substr
221			return substr(money_format('%.1n',$value),0,-2);
222		}
223	}	//	function DOLLAR()
224
225
226	/**
227	 * SEARCHSENSITIVE
228	 *
229	 * @param	string	$needle		The string to look for
230	 * @param	string	$haystack	The string in which to look
231	 * @param	int		$offset		Offset within $haystack
232	 * @return	string
233	 */
234	public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
235		$needle		= PHPExcel_Calculation_Functions::flattenSingleValue($needle);
236		$haystack	= PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
237		$offset		= PHPExcel_Calculation_Functions::flattenSingleValue($offset);
238
239		if (!is_bool($needle)) {
240			if (is_bool($haystack)) {
241				$haystack = ($haystack) ? 'TRUE' : 'FALSE';
242			}
243
244			if (($offset > 0) && (strlen($haystack) > $offset)) {
245				if (function_exists('mb_strpos')) {
246					$pos = mb_strpos($haystack, $needle, --$offset,'UTF-8');
247				} else {
248					$pos = strpos($haystack, $needle, --$offset);
249				}
250				if ($pos !== false) {
251					return ++$pos;
252				}
253			}
254		}
255		return PHPExcel_Calculation_Functions::VALUE();
256	}	//	function SEARCHSENSITIVE()
257
258
259	/**
260	 * SEARCHINSENSITIVE
261	 *
262	 * @param	string	$needle		The string to look for
263	 * @param	string	$haystack	The string in which to look
264	 * @param	int		$offset		Offset within $haystack
265	 * @return	string
266	 */
267	public static function SEARCHINSENSITIVE($needle,$haystack,$offset=1) {
268		$needle		= PHPExcel_Calculation_Functions::flattenSingleValue($needle);
269		$haystack	= PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
270		$offset		= PHPExcel_Calculation_Functions::flattenSingleValue($offset);
271
272		if (!is_bool($needle)) {
273			if (is_bool($haystack)) {
274				$haystack = ($haystack) ? 'TRUE' : 'FALSE';
275			}
276
277			if (($offset > 0) && (strlen($haystack) > $offset)) {
278				if (function_exists('mb_stripos')) {
279					$pos = mb_stripos($haystack, $needle, --$offset,'UTF-8');
280				} else {
281					$pos = stripos($haystack, $needle, --$offset);
282				}
283				if ($pos !== false) {
284					return ++$pos;
285				}
286			}
287		}
288		return PHPExcel_Calculation_Functions::VALUE();
289	}	//	function SEARCHINSENSITIVE()
290
291
292	/**
293	 *	FIXEDFORMAT
294	 *
295	 *	@param	mixed	$value	Value to check
296	 *	@return	boolean
297	 */
298	public static function FIXEDFORMAT($value,$decimals=2,$no_commas=false) {
299		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
300		$decimals	= PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
301		$no_commas		= PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
302
303		$valueResult = round($value,$decimals);
304		if ($decimals < 0) { $decimals = 0; }
305		if (!$no_commas) {
306			$valueResult = number_format($valueResult,$decimals);
307		}
308
309		return (string) $valueResult;
310	}	//	function FIXEDFORMAT()
311
312
313	/**
314	 * LEFT
315	 *
316	 * @param	string	$value	Value
317	 * @param	int		$chars	Number of characters
318	 * @return	string
319	 */
320	public static function LEFT($value = '', $chars = 1) {
321		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
322		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
323
324		if ($chars < 0) {
325			return PHPExcel_Calculation_Functions::VALUE();
326		}
327
328		if (is_bool($value)) {
329			$value = ($value) ? 'TRUE' : 'FALSE';
330		}
331
332		if (function_exists('mb_substr')) {
333			return mb_substr($value, 0, $chars, 'UTF-8');
334		} else {
335			return substr($value, 0, $chars);
336		}
337	}	//	function LEFT()
338
339
340	/**
341	 *	MID
342	 *
343	 *	@param	string	$value	Value
344	 *	@param	int		$start	Start character
345	 *	@param	int		$chars	Number of characters
346	 *	@return	string
347	 */
348	public static function MID($value = '', $start = 1, $chars = null) {
349		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
350		$start		= PHPExcel_Calculation_Functions::flattenSingleValue($start);
351		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
352
353		if (($start < 1) || ($chars < 0)) {
354			return PHPExcel_Calculation_Functions::VALUE();
355		}
356
357		if (is_bool($value)) {
358			$value = ($value) ? 'TRUE' : 'FALSE';
359		}
360
361		if (function_exists('mb_substr')) {
362			return mb_substr($value, --$start, $chars, 'UTF-8');
363		} else {
364			return substr($value, --$start, $chars);
365		}
366	}	//	function MID()
367
368
369	/**
370	 *	RIGHT
371	 *
372	 *	@param	string	$value	Value
373	 *	@param	int		$chars	Number of characters
374	 *	@return	string
375	 */
376	public static function RIGHT($value = '', $chars = 1) {
377		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
378		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
379
380		if ($chars < 0) {
381			return PHPExcel_Calculation_Functions::VALUE();
382		}
383
384		if (is_bool($value)) {
385			$value = ($value) ? 'TRUE' : 'FALSE';
386		}
387
388		if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
389			return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
390		} else {
391			return substr($value, strlen($value) - $chars);
392		}
393	}	//	function RIGHT()
394
395
396	/**
397	 * STRINGLENGTH
398	 *
399	 * @param	string	$value	Value
400	 * @param	int		$chars	Number of characters
401	 * @return	string
402	 */
403	public static function STRINGLENGTH($value = '') {
404		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
405
406		if (is_bool($value)) {
407			$value = ($value) ? 'TRUE' : 'FALSE';
408		}
409
410		if (function_exists('mb_strlen')) {
411			return mb_strlen($value, 'UTF-8');
412		} else {
413			return strlen($value);
414		}
415	}	//	function STRINGLENGTH()
416
417
418	/**
419	 *	LOWERCASE
420	 *
421	 *	Converts a string value to upper case.
422	 *
423	 *	@param	string		$mixedCaseString
424	 *	@return	string
425	 */
426	public static function LOWERCASE($mixedCaseString) {
427		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
428
429		if (is_bool($mixedCaseString)) {
430			$mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
431		}
432
433		if (function_exists('mb_convert_case')) {
434			return mb_convert_case($mixedCaseString, MB_CASE_LOWER, 'UTF-8');
435		} else {
436			return strtoupper($mixedCaseString);
437		}
438	}	//	function LOWERCASE()
439
440
441	/**
442	 *	UPPERCASE
443	 *
444	 *	Converts a string value to upper case.
445	 *
446	 *	@param	string		$mixedCaseString
447	 *	@return	string
448	 */
449	public static function UPPERCASE($mixedCaseString) {
450		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
451
452		if (is_bool($mixedCaseString)) {
453			$mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
454		}
455
456		if (function_exists('mb_convert_case')) {
457			return mb_convert_case($mixedCaseString, MB_CASE_UPPER, 'UTF-8');
458		} else {
459			return strtoupper($mixedCaseString);
460		}
461	}	//	function UPPERCASE()
462
463
464	/**
465	 *	PROPERCASE
466	 *
467	 *	Converts a string value to upper case.
468	 *
469	 *	@param	string		$mixedCaseString
470	 *	@return	string
471	 */
472	public static function PROPERCASE($mixedCaseString) {
473		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
474
475		if (is_bool($mixedCaseString)) {
476			$mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
477		}
478
479		if (function_exists('mb_convert_case')) {
480			return mb_convert_case($mixedCaseString, MB_CASE_TITLE, 'UTF-8');
481		} else {
482			return ucwords($mixedCaseString);
483		}
484	}	//	function PROPERCASE()
485
486
487	/**
488	 *	REPLACE
489	 *
490	 *	@param	string	$value	Value
491	 *	@param	int		$start	Start character
492	 *	@param	int		$chars	Number of characters
493	 *	@return	string
494	 */
495	public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText) {
496		$oldText	= PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
497		$start		= PHPExcel_Calculation_Functions::flattenSingleValue($start);
498		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
499		$newText	= PHPExcel_Calculation_Functions::flattenSingleValue($newText);
500
501		$left = self::LEFT($oldText,$start-1);
502		$right = self::RIGHT($oldText,self::STRINGLENGTH($oldText)-($start+$chars)+1);
503
504		return $left.$newText.$right;
505	}	//	function REPLACE()
506
507
508	/**
509	 *	SUBSTITUTE
510	 *
511	 *	@param	string	$text		Value
512	 *	@param	string	$fromText	From Value
513	 *	@param	string	$toText		To Value
514	 *	@param	integer	$instance	Instance Number
515	 *	@return	string
516	 */
517	public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0) {
518		$text		= PHPExcel_Calculation_Functions::flattenSingleValue($text);
519		$fromText	= PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
520		$toText		= PHPExcel_Calculation_Functions::flattenSingleValue($toText);
521		$instance	= floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
522
523		if ($instance == 0) {
524			if(function_exists('mb_str_replace')) {
525				return mb_str_replace($fromText,$toText,$text);
526			} else {
527				return str_replace($fromText,$toText,$text);
528			}
529		} else {
530			$pos = -1;
531			while($instance > 0) {
532				if (function_exists('mb_strpos')) {
533					$pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
534				} else {
535					$pos = strpos($text, $fromText, $pos+1);
536				}
537				if ($pos === false) {
538					break;
539				}
540				--$instance;
541			}
542			if ($pos !== false) {
543				if (function_exists('mb_strlen')) {
544					return self::REPLACE($text,++$pos,mb_strlen($fromText, 'UTF-8'),$toText);
545				} else {
546					return self::REPLACE($text,++$pos,strlen($fromText),$toText);
547				}
548			}
549		}
550
551		return $left.$newText.$right;
552	}	//	function SUBSTITUTE()
553
554
555	/**
556	 *	RETURNSTRING
557	 *
558	 *	@param	mixed	$value	Value to check
559	 *	@return	boolean
560	 */
561	public static function RETURNSTRING($testValue = '') {
562		$testValue	= PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
563
564		if (is_string($testValue)) {
565			return $testValue;
566		}
567		return Null;
568	}	//	function RETURNSTRING()
569
570
571	/**
572	 *	TEXTFORMAT
573	 *
574	 *	@param	mixed	$value	Value to check
575	 *	@return	boolean
576	 */
577	public static function TEXTFORMAT($value,$format) {
578		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
579		$format	= PHPExcel_Calculation_Functions::flattenSingleValue($format);
580
581		if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
582			$value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
583		}
584
585		return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
586	}	//	function TEXTFORMAT()
587
588}	//	class PHPExcel_Calculation_TextData