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     1.8.0, 2014-03-02
 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: 
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  $characters     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:         $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:     }   //  function DOLLAR()
223: 
224: 
225:     /**
226:      * SEARCHSENSITIVE
227:      *
228:      * @param   string  $needle     The string to look for
229:      * @param   string  $haystack   The string in which to look
230:      * @param   int     $offset     Offset within $haystack
231:      * @return  string
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:     }   //  function SEARCHSENSITIVE()
259: 
260: 
261:     /**
262:      * SEARCHINSENSITIVE
263:      *
264:      * @param   string  $needle     The string to look for
265:      * @param   string  $haystack   The string in which to look
266:      * @param   int     $offset     Offset within $haystack
267:      * @return  string
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:     }   //  function SEARCHINSENSITIVE()
295: 
296: 
297:     /**
298:      * FIXEDFORMAT
299:      *
300:      * @param   mixed       $value  Value to check
301:      * @param   integer     $decimals
302:      * @param   boolean     $no_commas
303:      * @return  boolean
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:         // Validate parameters
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:     }   //  function FIXEDFORMAT()
324: 
325: 
326:     /**
327:      * LEFT
328:      *
329:      * @param   string  $value  Value
330:      * @param   int     $chars  Number of characters
331:      * @return  string
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:     }   //  function LEFT()
351: 
352: 
353:     /**
354:      * MID
355:      *
356:      * @param   string  $value  Value
357:      * @param   int     $start  Start character
358:      * @param   int     $chars  Number of characters
359:      * @return  string
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:     }   //  function MID()
380: 
381: 
382:     /**
383:      * RIGHT
384:      *
385:      * @param   string  $value  Value
386:      * @param   int     $chars  Number of characters
387:      * @return  string
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:     }   //  function RIGHT()
407: 
408: 
409:     /**
410:      * STRINGLENGTH
411:      *
412:      * @param   string  $value  Value
413:      * @return  string
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:     }   //  function STRINGLENGTH()
428: 
429: 
430:     /**
431:      * LOWERCASE
432:      *
433:      * Converts a string value to upper case.
434:      *
435:      * @param   string      $mixedCaseString
436:      * @return  string
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:     }   //  function LOWERCASE()
447: 
448: 
449:     /**
450:      * UPPERCASE
451:      *
452:      * Converts a string value to upper case.
453:      *
454:      * @param   string      $mixedCaseString
455:      * @return  string
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:     }   //  function UPPERCASE()
466: 
467: 
468:     /**
469:      * PROPERCASE
470:      *
471:      * Converts a string value to upper case.
472:      *
473:      * @param   string      $mixedCaseString
474:      * @return  string
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:     }   //  function PROPERCASE()
485: 
486: 
487:     /**
488:      * REPLACE
489:      *
490:      * @param   string  $oldText    String to modify
491:      * @param   int     $start      Start character
492:      * @param   int     $chars      Number of characters
493:      * @param   string  $newText    String to replace in defined position 
494:      * @return  string
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:     }   //  function REPLACE()
507: 
508: 
509:     /**
510:      * SUBSTITUTE
511:      *
512:      * @param   string  $text       Value
513:      * @param   string  $fromText   From Value
514:      * @param   string  $toText     To Value
515:      * @param   integer $instance   Instance Number
516:      * @return  string
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:     }   //  function SUBSTITUTE()
554: 
555: 
556:     /**
557:      * RETURNSTRING
558:      *
559:      * @param   mixed   $testValue  Value to check
560:      * @return  boolean
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:     }   //  function RETURNSTRING()
570: 
571: 
572:     /**
573:      * TEXTFORMAT
574:      *
575:      * @param   mixed   $value  Value to check
576:      * @param   string  $format Format mask to use
577:      * @return  boolean
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:     }   //  function TEXTFORMAT()
589: 
590: }   //  class PHPExcel_Calculation_TextData
591: