1: <?php
2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 14: 15: 16: 17: 18: 19: 20: 21: 22: 23: 24: 25: 26:
27:
28:
29:
30: if (!defined('PHPEXCEL_ROOT')) {
31: 32: 33:
34: define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35: require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36: }
37:
38:
39: 40: 41: 42: 43: 44: 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)
64: return PHPExcel_Calculation_Functions::VALUE();
65: return 0;
66: }
67:
68: 69: 70: 71: 72: 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: 91: 92: 93: 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: }
111:
112:
113: 114: 115: 116: 117: 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: }
131:
132:
133: 134: 135: 136: 137: 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: }
160:
161:
162: 163: 164: 165: 166:
167: public static function CONCATENATE() {
168:
169: $returnValue = '';
170:
171:
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:
185: return $returnValue;
186: }
187:
188:
189: 190: 191: 192: 193: 194: 195: 196: 197: 198: 199: 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:
206: if (!is_numeric($value) || !is_numeric($decimals)) {
207: return PHPExcel_Calculation_Functions::NaN();
208: }
209: $decimals = floor($decimals);
210:
211: $mask = '$#,##0';
212: if ($decimals > 0) {
213: $mask .= '.' . str_repeat('0',$decimals);
214: } else {
215: $round = pow(10,abs($decimals));
216: if ($value < 0) { $round = 0-$round; }
217: $value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
218: }
219:
220: return PHPExcel_Style_NumberFormat::toFormattedString($value, $mask);
221:
222: }
223:
224:
225: 226: 227: 228: 229: 230: 231: 232:
233: public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
234: $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
235: $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
236: $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
237:
238: if (!is_bool($needle)) {
239: if (is_bool($haystack)) {
240: $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
241: }
242:
243: if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
244: if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
245: return $offset;
246: }
247: if (function_exists('mb_strpos')) {
248: $pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
249: } else {
250: $pos = strpos($haystack, $needle, --$offset);
251: }
252: if ($pos !== false) {
253: return ++$pos;
254: }
255: }
256: }
257: return PHPExcel_Calculation_Functions::VALUE();
258: }
259:
260:
261: 262: 263: 264: 265: 266: 267: 268:
269: public static function SEARCHINSENSITIVE($needle,$haystack,$offset=1) {
270: $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
271: $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
272: $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
273:
274: if (!is_bool($needle)) {
275: if (is_bool($haystack)) {
276: $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
277: }
278:
279: if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
280: if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
281: return $offset;
282: }
283: if (function_exists('mb_stripos')) {
284: $pos = mb_stripos($haystack, $needle, --$offset,'UTF-8');
285: } else {
286: $pos = stripos($haystack, $needle, --$offset);
287: }
288: if ($pos !== false) {
289: return ++$pos;
290: }
291: }
292: }
293: return PHPExcel_Calculation_Functions::VALUE();
294: }
295:
296:
297: 298: 299: 300: 301: 302: 303: 304:
305: public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = FALSE) {
306: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
307: $decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
308: $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
309:
310:
311: if (!is_numeric($value) || !is_numeric($decimals)) {
312: return PHPExcel_Calculation_Functions::NaN();
313: }
314: $decimals = floor($decimals);
315:
316: $valueResult = round($value,$decimals);
317: if ($decimals < 0) { $decimals = 0; }
318: if (!$no_commas) {
319: $valueResult = number_format($valueResult,$decimals);
320: }
321:
322: return (string) $valueResult;
323: }
324:
325:
326: 327: 328: 329: 330: 331: 332:
333: public static function LEFT($value = '', $chars = 1) {
334: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
335: $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
336:
337: if ($chars < 0) {
338: return PHPExcel_Calculation_Functions::VALUE();
339: }
340:
341: if (is_bool($value)) {
342: $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
343: }
344:
345: if (function_exists('mb_substr')) {
346: return mb_substr($value, 0, $chars, 'UTF-8');
347: } else {
348: return substr($value, 0, $chars);
349: }
350: }
351:
352:
353: 354: 355: 356: 357: 358: 359: 360:
361: public static function MID($value = '', $start = 1, $chars = null) {
362: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
363: $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
364: $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
365:
366: if (($start < 1) || ($chars < 0)) {
367: return PHPExcel_Calculation_Functions::VALUE();
368: }
369:
370: if (is_bool($value)) {
371: $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
372: }
373:
374: if (function_exists('mb_substr')) {
375: return mb_substr($value, --$start, $chars, 'UTF-8');
376: } else {
377: return substr($value, --$start, $chars);
378: }
379: }
380:
381:
382: 383: 384: 385: 386: 387: 388:
389: public static function RIGHT($value = '', $chars = 1) {
390: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
391: $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
392:
393: if ($chars < 0) {
394: return PHPExcel_Calculation_Functions::VALUE();
395: }
396:
397: if (is_bool($value)) {
398: $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
399: }
400:
401: if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
402: return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
403: } else {
404: return substr($value, strlen($value) - $chars);
405: }
406: }
407:
408:
409: 410: 411: 412: 413: 414:
415: public static function STRINGLENGTH($value = '') {
416: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
417:
418: if (is_bool($value)) {
419: $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
420: }
421:
422: if (function_exists('mb_strlen')) {
423: return mb_strlen($value, 'UTF-8');
424: } else {
425: return strlen($value);
426: }
427: }
428:
429:
430: 431: 432: 433: 434: 435: 436: 437:
438: public static function LOWERCASE($mixedCaseString) {
439: $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
440:
441: if (is_bool($mixedCaseString)) {
442: $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
443: }
444:
445: return PHPExcel_Shared_String::StrToLower($mixedCaseString);
446: }
447:
448:
449: 450: 451: 452: 453: 454: 455: 456:
457: public static function UPPERCASE($mixedCaseString) {
458: $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
459:
460: if (is_bool($mixedCaseString)) {
461: $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
462: }
463:
464: return PHPExcel_Shared_String::StrToUpper($mixedCaseString);
465: }
466:
467:
468: 469: 470: 471: 472: 473: 474: 475:
476: public static function PROPERCASE($mixedCaseString) {
477: $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
478:
479: if (is_bool($mixedCaseString)) {
480: $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
481: }
482:
483: return PHPExcel_Shared_String::StrToTitle($mixedCaseString);
484: }
485:
486:
487: 488: 489: 490: 491: 492: 493: 494: 495:
496: public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText) {
497: $oldText = PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
498: $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
499: $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
500: $newText = PHPExcel_Calculation_Functions::flattenSingleValue($newText);
501:
502: $left = self::LEFT($oldText,$start-1);
503: $right = self::RIGHT($oldText,self::STRINGLENGTH($oldText)-($start+$chars)+1);
504:
505: return $left.$newText.$right;
506: }
507:
508:
509: 510: 511: 512: 513: 514: 515: 516: 517:
518: public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0) {
519: $text = PHPExcel_Calculation_Functions::flattenSingleValue($text);
520: $fromText = PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
521: $toText = PHPExcel_Calculation_Functions::flattenSingleValue($toText);
522: $instance = floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
523:
524: if ($instance == 0) {
525: if(function_exists('mb_str_replace')) {
526: return mb_str_replace($fromText,$toText,$text);
527: } else {
528: return str_replace($fromText,$toText,$text);
529: }
530: } else {
531: $pos = -1;
532: while($instance > 0) {
533: if (function_exists('mb_strpos')) {
534: $pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
535: } else {
536: $pos = strpos($text, $fromText, $pos+1);
537: }
538: if ($pos === false) {
539: break;
540: }
541: --$instance;
542: }
543: if ($pos !== false) {
544: if (function_exists('mb_strlen')) {
545: return self::REPLACE($text,++$pos,mb_strlen($fromText, 'UTF-8'),$toText);
546: } else {
547: return self::REPLACE($text,++$pos,strlen($fromText),$toText);
548: }
549: }
550: }
551:
552: return $text;
553: }
554:
555:
556: 557: 558: 559: 560: 561:
562: public static function RETURNSTRING($testValue = '') {
563: $testValue = PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
564:
565: if (is_string($testValue)) {
566: return $testValue;
567: }
568: return Null;
569: }
570:
571:
572: 573: 574: 575: 576: 577: 578:
579: public static function TEXTFORMAT($value,$format) {
580: $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
581: $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
582:
583: if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
584: $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
585: }
586:
587: return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
588: }
589:
590: }
591: