?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
Updated gui for user facility settings (#1327)
[openemr.git] / vendor / phpoffice / phpexcel / Classes / PHPExcel / Calculation / TextData.php
blob148a5b756ddb7debaf324ff6954a151c84224898
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 }
Mirror of official OpenEMR Sourceforge repository