PageRenderTime 435ms CodeModel.GetById 74ms app.highlight 279ms RepoModel.GetById 48ms app.codeStats 1ms

/PHPExcel/Calculation/TextData.php

https://bitbucket.org/nfredricks/wp-employee-time
PHP | 598 lines | 321 code | 89 blank | 188 comment | 110 complexity | 43ad558ffe62c689b536cf660f5ca176 MD5 | raw file
  1<?php
  2/**
  3 * PHPExcel
  4 *
  5 * Copyright (c) 2006 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
 24 * @license		http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt	LGPL
 25 * @version		1.7.8, 2012-10-12
 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 - 2012 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			return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
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_bool($stringValue)) {
123			return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
124		}
125
126		if (is_string($stringValue) || is_numeric($stringValue)) {
127			return trim(preg_replace('/ +/',' ',trim($stringValue,' ')));
128		}
129		return NULL;
130	}	//	function TRIMSPACES()
131
132
133	/**
134	 * ASCIICODE
135	 *
136	 * @param	string	$character	Value
137	 * @return	int
138	 */
139	public static function ASCIICODE($characters) {
140		if (($characters === NULL) || ($characters === ''))
141			return PHPExcel_Calculation_Functions::VALUE();
142		$characters	= PHPExcel_Calculation_Functions::flattenSingleValue($characters);
143		if (is_bool($characters)) {
144			if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
145				$characters = (int) $characters;
146			} else {
147				$characters = ($characters) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
148			}
149		}
150
151		$character = $characters;
152		if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
153			if (mb_strlen($characters, 'UTF-8') > 1) { $character = mb_substr($characters, 0, 1, 'UTF-8'); }
154			return self::_uniord($character);
155		} else {
156			if (strlen($characters) > 0) { $character = substr($characters, 0, 1); }
157			return ord($character);
158		}
159	}	//	function ASCIICODE()
160
161
162	/**
163	 * CONCATENATE
164	 *
165	 * @return	string
166	 */
167	public static function CONCATENATE() {
168		// Return value
169		$returnValue = '';
170
171		// Loop through arguments
172		$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
173		foreach ($aArgs as $arg) {
174			if (is_bool($arg)) {
175				if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
176					$arg = (int) $arg;
177				} else {
178					$arg = ($arg) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
179				}
180			}
181			$returnValue .= $arg;
182		}
183
184		// Return
185		return $returnValue;
186	}	//	function CONCATENATE()
187
188
189	/**
190	 * DOLLAR
191	 *
192	 * This function converts a number to text using currency format, with the decimals rounded to the specified place.
193	 * The format used is $#,##0.00_);($#,##0.00)..
194	 *
195	 * @param	float	$value			The value to format
196	 * @param	int		$decimals		The number of digits to display to the right of the decimal point.
197	 *									If decimals is negative, number is rounded to the left of the decimal point.
198	 *									If you omit decimals, it is assumed to be 2
199	 * @return	string
200	 */
201	public static function DOLLAR($value = 0, $decimals = 2) {
202		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
203		$decimals	= is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
204
205		// Validate parameters
206		if (!is_numeric($value) || !is_numeric($decimals)) {
207			return PHPExcel_Calculation_Functions::NaN();
208		}
209		$decimals = floor($decimals);
210
211		if ($decimals > 0) {
212			return money_format('%.'.$decimals.'n',$value);
213		} else {
214			$round = pow(10,abs($decimals));
215			if ($value < 0) { $round = 0-$round; }
216			$value = PHPExcel_Calculation_MathTrig::MROUND($value,$round);
217			//	The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
218			//		so we display to 1 dp and chop off that character and the decimal separator using substr
219			return substr(money_format('%.1n',$value),0,-2);
220		}
221	}	//	function DOLLAR()
222
223
224	/**
225	 * SEARCHSENSITIVE
226	 *
227	 * @param	string	$needle		The string to look for
228	 * @param	string	$haystack	The string in which to look
229	 * @param	int		$offset		Offset within $haystack
230	 * @return	string
231	 */
232	public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
233		$needle		= PHPExcel_Calculation_Functions::flattenSingleValue($needle);
234		$haystack	= PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
235		$offset		= PHPExcel_Calculation_Functions::flattenSingleValue($offset);
236
237		if (!is_bool($needle)) {
238			if (is_bool($haystack)) {
239				$haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
240			}
241
242			if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
243				if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
244					return $offset;
245				}
246				if (function_exists('mb_strpos')) {
247					$pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
248				} else {
249					$pos = strpos($haystack, $needle, --$offset);
250				}
251				if ($pos !== false) {
252					return ++$pos;
253				}
254			}
255		}
256		return PHPExcel_Calculation_Functions::VALUE();
257	}	//	function SEARCHSENSITIVE()
258
259
260	/**
261	 * SEARCHINSENSITIVE
262	 *
263	 * @param	string	$needle		The string to look for
264	 * @param	string	$haystack	The string in which to look
265	 * @param	int		$offset		Offset within $haystack
266	 * @return	string
267	 */
268	public static function SEARCHINSENSITIVE($needle,$haystack,$offset=1) {
269		$needle		= PHPExcel_Calculation_Functions::flattenSingleValue($needle);
270		$haystack	= PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
271		$offset		= PHPExcel_Calculation_Functions::flattenSingleValue($offset);
272
273		if (!is_bool($needle)) {
274			if (is_bool($haystack)) {
275				$haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
276			}
277
278			if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
279				if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
280					return $offset;
281				}
282				if (function_exists('mb_stripos')) {
283					$pos = mb_stripos($haystack, $needle, --$offset,'UTF-8');
284				} else {
285					$pos = stripos($haystack, $needle, --$offset);
286				}
287				if ($pos !== false) {
288					return ++$pos;
289				}
290			}
291		}
292		return PHPExcel_Calculation_Functions::VALUE();
293	}	//	function SEARCHINSENSITIVE()
294
295
296	/**
297	 * FIXEDFORMAT
298	 *
299	 * @param	mixed	$value	Value to check
300	 * @return	boolean
301	 */
302	public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = FALSE) {
303		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
304		$decimals	= PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
305		$no_commas	= PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
306
307		// Validate parameters
308		if (!is_numeric($value) || !is_numeric($decimals)) {
309			return PHPExcel_Calculation_Functions::NaN();
310		}
311		$decimals = floor($decimals);
312
313		$valueResult = round($value,$decimals);
314		if ($decimals < 0) { $decimals = 0; }
315		if (!$no_commas) {
316			$valueResult = number_format($valueResult,$decimals);
317		}
318
319		return (string) $valueResult;
320	}	//	function FIXEDFORMAT()
321
322
323	/**
324	 * LEFT
325	 *
326	 * @param	string	$value	Value
327	 * @param	int		$chars	Number of characters
328	 * @return	string
329	 */
330	public static function LEFT($value = '', $chars = 1) {
331		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
332		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
333
334		if ($chars < 0) {
335			return PHPExcel_Calculation_Functions::VALUE();
336		}
337
338		if (is_bool($value)) {
339			$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
340		}
341
342		if (function_exists('mb_substr')) {
343			return mb_substr($value, 0, $chars, 'UTF-8');
344		} else {
345			return substr($value, 0, $chars);
346		}
347	}	//	function LEFT()
348
349
350	/**
351	 * MID
352	 *
353	 * @param	string	$value	Value
354	 * @param	int		$start	Start character
355	 * @param	int		$chars	Number of characters
356	 * @return	string
357	 */
358	public static function MID($value = '', $start = 1, $chars = null) {
359		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
360		$start		= PHPExcel_Calculation_Functions::flattenSingleValue($start);
361		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
362
363		if (($start < 1) || ($chars < 0)) {
364			return PHPExcel_Calculation_Functions::VALUE();
365		}
366
367		if (is_bool($value)) {
368			$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
369		}
370
371		if (function_exists('mb_substr')) {
372			return mb_substr($value, --$start, $chars, 'UTF-8');
373		} else {
374			return substr($value, --$start, $chars);
375		}
376	}	//	function MID()
377
378
379	/**
380	 * RIGHT
381	 *
382	 * @param	string	$value	Value
383	 * @param	int		$chars	Number of characters
384	 * @return	string
385	 */
386	public static function RIGHT($value = '', $chars = 1) {
387		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
388		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
389
390		if ($chars < 0) {
391			return PHPExcel_Calculation_Functions::VALUE();
392		}
393
394		if (is_bool($value)) {
395			$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
396		}
397
398		if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
399			return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
400		} else {
401			return substr($value, strlen($value) - $chars);
402		}
403	}	//	function RIGHT()
404
405
406	/**
407	 * STRINGLENGTH
408	 *
409	 * @param	string	$value	Value
410	 * @param	int		$chars	Number of characters
411	 * @return	string
412	 */
413	public static function STRINGLENGTH($value = '') {
414		$value		= PHPExcel_Calculation_Functions::flattenSingleValue($value);
415
416		if (is_bool($value)) {
417			$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
418		}
419
420		if (function_exists('mb_strlen')) {
421			return mb_strlen($value, 'UTF-8');
422		} else {
423			return strlen($value);
424		}
425	}	//	function STRINGLENGTH()
426
427
428	/**
429	 * LOWERCASE
430	 *
431	 * Converts a string value to upper case.
432	 *
433	 * @param	string		$mixedCaseString
434	 * @return	string
435	 */
436	public static function LOWERCASE($mixedCaseString) {
437		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
438
439		if (is_bool($mixedCaseString)) {
440			$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
441		}
442
443		if (function_exists('mb_convert_case')) {
444			return mb_convert_case($mixedCaseString, MB_CASE_LOWER, 'UTF-8');
445		} else {
446			return strtoupper($mixedCaseString);
447		}
448	}	//	function LOWERCASE()
449
450
451	/**
452	 * UPPERCASE
453	 *
454	 * Converts a string value to upper case.
455	 *
456	 * @param	string		$mixedCaseString
457	 * @return	string
458	 */
459	public static function UPPERCASE($mixedCaseString) {
460		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
461
462		if (is_bool($mixedCaseString)) {
463			$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
464		}
465
466		if (function_exists('mb_convert_case')) {
467			return mb_convert_case($mixedCaseString, MB_CASE_UPPER, 'UTF-8');
468		} else {
469			return strtoupper($mixedCaseString);
470		}
471	}	//	function UPPERCASE()
472
473
474	/**
475	 * PROPERCASE
476	 *
477	 * Converts a string value to upper case.
478	 *
479	 * @param	string		$mixedCaseString
480	 * @return	string
481	 */
482	public static function PROPERCASE($mixedCaseString) {
483		$mixedCaseString	= PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
484
485		if (is_bool($mixedCaseString)) {
486			$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
487		}
488
489		if (function_exists('mb_convert_case')) {
490			return mb_convert_case($mixedCaseString, MB_CASE_TITLE, 'UTF-8');
491		} else {
492			return ucwords($mixedCaseString);
493		}
494	}	//	function PROPERCASE()
495
496
497	/**
498	 * REPLACE
499	 *
500	 * @param	string	$value	Value
501	 * @param	int		$start	Start character
502	 * @param	int		$chars	Number of characters
503	 * @return	string
504	 */
505	public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText) {
506		$oldText	= PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
507		$start		= PHPExcel_Calculation_Functions::flattenSingleValue($start);
508		$chars		= PHPExcel_Calculation_Functions::flattenSingleValue($chars);
509		$newText	= PHPExcel_Calculation_Functions::flattenSingleValue($newText);
510
511		$left = self::LEFT($oldText,$start-1);
512		$right = self::RIGHT($oldText,self::STRINGLENGTH($oldText)-($start+$chars)+1);
513
514		return $left.$newText.$right;
515	}	//	function REPLACE()
516
517
518	/**
519	 * SUBSTITUTE
520	 *
521	 * @param	string	$text		Value
522	 * @param	string	$fromText	From Value
523	 * @param	string	$toText		To Value
524	 * @param	integer	$instance	Instance Number
525	 * @return	string
526	 */
527	public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0) {
528		$text		= PHPExcel_Calculation_Functions::flattenSingleValue($text);
529		$fromText	= PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
530		$toText		= PHPExcel_Calculation_Functions::flattenSingleValue($toText);
531		$instance	= floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
532
533		if ($instance == 0) {
534			if(function_exists('mb_str_replace')) {
535				return mb_str_replace($fromText,$toText,$text);
536			} else {
537				return str_replace($fromText,$toText,$text);
538			}
539		} else {
540			$pos = -1;
541			while($instance > 0) {
542				if (function_exists('mb_strpos')) {
543					$pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
544				} else {
545					$pos = strpos($text, $fromText, $pos+1);
546				}
547				if ($pos === false) {
548					break;
549				}
550				--$instance;
551			}
552			if ($pos !== false) {
553				if (function_exists('mb_strlen')) {
554					return self::REPLACE($text,++$pos,mb_strlen($fromText, 'UTF-8'),$toText);
555				} else {
556					return self::REPLACE($text,++$pos,strlen($fromText),$toText);
557				}
558			}
559		}
560
561		return $text;
562	}	//	function SUBSTITUTE()
563
564
565	/**
566	 * RETURNSTRING
567	 *
568	 * @param	mixed	$value	Value to check
569	 * @return	boolean
570	 */
571	public static function RETURNSTRING($testValue = '') {
572		$testValue	= PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
573
574		if (is_string($testValue)) {
575			return $testValue;
576		}
577		return Null;
578	}	//	function RETURNSTRING()
579
580
581	/**
582	 * TEXTFORMAT
583	 *
584	 * @param	mixed	$value	Value to check
585	 * @return	boolean
586	 */
587	public static function TEXTFORMAT($value,$format) {
588		$value	= PHPExcel_Calculation_Functions::flattenSingleValue($value);
589		$format	= PHPExcel_Calculation_Functions::flattenSingleValue($format);
590
591		if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
592			$value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
593		}
594
595		return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
596	}	//	function TEXTFORMAT()
597
598}	//	class PHPExcel_Calculation_TextData