visualizer / vendor / phpoffice / phpexcel / Classes / PHPExcel / Calculation / TextData.php

TextData.php in Visualizer – Tables & Charts Manager with Built-in AI Generator 3.0.3, at vendor/phpoffice/phpexcel/Classes/PHPExcel/Calculation/TextData.php

631 lines 18.9 KB

Raw Download Zip

1	<?php
2	/**
3	* PHPExcel
4	*
5	* Copyright (c) 2006 - 2014 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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
24	* @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25	* @version ##VERSION##, ##DATE##
26	*/
27
28
29	/** PHPExcel root directory */
30	if (!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 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
45	*/
46	class 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 $stringValue 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 $stringValue Value to check
117	* @return string
118	*/
119	public static function TRIMSPACES($stringValue = '') {
120	$stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
121	if (is_bool($stringValue)) {
122	return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
123	}
124
125	if (is_string($stringValue) \|\| is_numeric($stringValue)) {
126	return trim(preg_replace('/ +/',' ',trim($stringValue, ' ')), ' ');
127	}
128	return NULL;
129	} // function TRIMSPACES()
130
131
132	/**
133	* ASCIICODE
134	*
135	* @param string $characters Value
136	* @return int
137	*/
138	public static function ASCIICODE($characters) {
139	if (($characters === NULL) \|\| ($characters === ''))
140	return PHPExcel_Calculation_Functions::VALUE();
141	$characters = PHPExcel_Calculation_Functions::flattenSingleValue($characters);
142	if (is_bool($characters)) {
143	if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
144	$characters = (int) $characters;
145	} else {
146	$characters = ($characters) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
147	}
148	}
149
150	$character = $characters;
151	if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
152	if (mb_strlen($characters, 'UTF-8') > 1) { $character = mb_substr($characters, 0, 1, 'UTF-8'); }
153	return self::_uniord($character);
154	} else {
155	if (strlen($characters) > 0) { $character = substr($characters, 0, 1); }
156	return ord($character);
157	}
158	} // function ASCIICODE()
159
160
161	/**
162	* CONCATENATE
163	*
164	* @return string
165	*/
166	public static function CONCATENATE() {
167	// Return value
168	$returnValue = '';
169
170	// Loop through arguments
171	$aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
172	foreach ($aArgs as $arg) {
173	if (is_bool($arg)) {
174	if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
175	$arg = (int) $arg;
176	} else {
177	$arg = ($arg) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
178	}
179	}
180	$returnValue .= $arg;
181	}
182
183	// Return
184	return $returnValue;
185	} // function CONCATENATE()
186
187
188	/**
189	* DOLLAR
190	*
191	* This function converts a number to text using currency format, with the decimals rounded to the specified place.
192	* The format used is $#,##0.00_);($#,##0.00)..
193	*
194	* @param float $value The value to format
195	* @param int $decimals The number of digits to display to the right of the decimal point.
196	* If decimals is negative, number is rounded to the left of the decimal point.
197	* If you omit decimals, it is assumed to be 2
198	* @return string
199	*/
200	public static function DOLLAR($value = 0, $decimals = 2) {
201	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
202	$decimals = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
203
204	// Validate parameters
205	if (!is_numeric($value) \|\| !is_numeric($decimals)) {
206	return PHPExcel_Calculation_Functions::NaN();
207	}
208	$decimals = floor($decimals);
209
210	$mask = '$#,##0';
211	if ($decimals > 0) {
212	$mask .= '.' . str_repeat('0',$decimals);
213	} else {
214	$round = pow(10,abs($decimals));
215	if ($value < 0) { $round = 0-$round; }
216	$value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
217	}
218
219	return PHPExcel_Style_NumberFormat::toFormattedString($value, $mask);
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	* @param integer $decimals
301	* @param boolean $no_commas
302	* @return boolean
303	*/
304	public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = FALSE) {
305	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
306	$decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
307	$no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
308
309	// Validate parameters
310	if (!is_numeric($value) \|\| !is_numeric($decimals)) {
311	return PHPExcel_Calculation_Functions::NaN();
312	}
313	$decimals = floor($decimals);
314
315	$valueResult = round($value,$decimals);
316	if ($decimals < 0) { $decimals = 0; }
317	if (!$no_commas) {
318	$valueResult = number_format($valueResult,$decimals);
319	}
320
321	return (string) $valueResult;
322	} // function FIXEDFORMAT()
323
324
325	/**
326	* LEFT
327	*
328	* @param string $value Value
329	* @param int $chars Number of characters
330	* @return string
331	*/
332	public static function LEFT($value = '', $chars = 1) {
333	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
334	$chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
335
336	if ($chars < 0) {
337	return PHPExcel_Calculation_Functions::VALUE();
338	}
339
340	if (is_bool($value)) {
341	$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
342	}
343
344	if (function_exists('mb_substr')) {
345	return mb_substr($value, 0, $chars, 'UTF-8');
346	} else {
347	return substr($value, 0, $chars);
348	}
349	} // function LEFT()
350
351
352	/**
353	* MID
354	*
355	* @param string $value Value
356	* @param int $start Start character
357	* @param int $chars Number of characters
358	* @return string
359	*/
360	public static function MID($value = '', $start = 1, $chars = null) {
361	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
362	$start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
363	$chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
364
365	if (($start < 1) \|\| ($chars < 0)) {
366	return PHPExcel_Calculation_Functions::VALUE();
367	}
368
369	if (is_bool($value)) {
370	$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
371	}
372
373	if (function_exists('mb_substr')) {
374	return mb_substr($value, --$start, $chars, 'UTF-8');
375	} else {
376	return substr($value, --$start, $chars);
377	}
378	} // function MID()
379
380
381	/**
382	* RIGHT
383	*
384	* @param string $value Value
385	* @param int $chars Number of characters
386	* @return string
387	*/
388	public static function RIGHT($value = '', $chars = 1) {
389	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
390	$chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
391
392	if ($chars < 0) {
393	return PHPExcel_Calculation_Functions::VALUE();
394	}
395
396	if (is_bool($value)) {
397	$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
398	}
399
400	if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
401	return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
402	} else {
403	return substr($value, strlen($value) - $chars);
404	}
405	} // function RIGHT()
406
407
408	/**
409	* STRINGLENGTH
410	*
411	* @param string $value Value
412	* @return string
413	*/
414	public static function STRINGLENGTH($value = '') {
415	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
416
417	if (is_bool($value)) {
418	$value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
419	}
420
421	if (function_exists('mb_strlen')) {
422	return mb_strlen($value, 'UTF-8');
423	} else {
424	return strlen($value);
425	}
426	} // function STRINGLENGTH()
427
428
429	/**
430	* LOWERCASE
431	*
432	* Converts a string value to upper case.
433	*
434	* @param string $mixedCaseString
435	* @return string
436	*/
437	public static function LOWERCASE($mixedCaseString) {
438	$mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
439
440	if (is_bool($mixedCaseString)) {
441	$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
442	}
443
444	return PHPExcel_Shared_String::StrToLower($mixedCaseString);
445	} // function LOWERCASE()
446
447
448	/**
449	* UPPERCASE
450	*
451	* Converts a string value to upper case.
452	*
453	* @param string $mixedCaseString
454	* @return string
455	*/
456	public static function UPPERCASE($mixedCaseString) {
457	$mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
458
459	if (is_bool($mixedCaseString)) {
460	$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
461	}
462
463	return PHPExcel_Shared_String::StrToUpper($mixedCaseString);
464	} // function UPPERCASE()
465
466
467	/**
468	* PROPERCASE
469	*
470	* Converts a string value to upper case.
471	*
472	* @param string $mixedCaseString
473	* @return string
474	*/
475	public static function PROPERCASE($mixedCaseString) {
476	$mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
477
478	if (is_bool($mixedCaseString)) {
479	$mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
480	}
481
482	return PHPExcel_Shared_String::StrToTitle($mixedCaseString);
483	} // function PROPERCASE()
484
485
486	/**
487	* REPLACE
488	*
489	* @param string $oldText String to modify
490	* @param int $start Start character
491	* @param int $chars Number of characters
492	* @param string $newText String to replace in defined position
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 $text;
552	} // function SUBSTITUTE()
553
554
555	/**
556	* RETURNSTRING
557	*
558	* @param mixed $testValue 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	* @param string $format Format mask to use
576	* @return boolean
577	*/
578	public static function TEXTFORMAT($value,$format) {
579	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
580	$format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
581
582	if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
583	$value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
584	}
585
586	return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
587	} // function TEXTFORMAT()
588
589	/**
590	* VALUE
591	*
592	* @param mixed $value Value to check
593	* @return boolean
594	*/
595	public static function VALUE($value = '') {
596	$value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
597
598	if (!is_numeric($value)) {
599	$numberValue = str_replace(
600	PHPExcel_Shared_String::getThousandsSeparator(),
601	'',
602	trim($value, " \t\n\r\0\x0B" . PHPExcel_Shared_String::getCurrencyCode())
603	);
604	if (is_numeric($numberValue)) {
605	return (float) $numberValue;
606	}
607
608	$dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
609	PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
610
611	if (strpos($value, ':') !== false) {
612	$timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
613	if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
614	PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
615	return $timeValue;
616	}
617	}
618	$dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
619	if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
620	PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
621	return $dateValue;
622	}
623	PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
624
625	return PHPExcel_Calculation_Functions::VALUE();
626	}
627	return (float) $value;
628	}
629
630	}
631