ILIAS  eassessment Revision 61809
 All Data Structures Namespaces Files Functions Variables Groups Pages
TextData.php
Go to the documentation of this file.
1 <?php
30 if (!defined('PHPEXCEL_ROOT')) {
34  define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
35  require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
36 }
37 
38 
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 
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 
95  public static function TRIMNONPRINTABLE($stringValue = '') {
96  $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
97 
98  if (is_bool($stringValue)) {
99  $stringValue = ($stringValue) ? 'TRUE' : 'FALSE';
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 
119  public static function TRIMSPACES($stringValue = '') {
120  $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
121 
122  if (is_string($stringValue) || is_numeric($stringValue)) {
123  return trim(preg_replace('/ +/',' ',$stringValue));
124  }
125  return Null;
126  } // function TRIMSPACES()
127 
128 
135  public static function ASCIICODE($characters) {
136  $characters = PHPExcel_Calculation_Functions::flattenSingleValue($characters);
137  if (is_bool($characters)) {
138  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
139  $characters = (int) $characters;
140  } else {
141  if ($characters) {
142  $characters = 'True';
143  } else {
144  $characters = 'False';
145  }
146  }
147  }
148 
149  $character = $characters;
150  if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
151  if (mb_strlen($characters, 'UTF-8') > 1) { $character = mb_substr($characters, 0, 1, 'UTF-8'); }
152  return self::_uniord($character);
153  } else {
154  if (strlen($characters) > 0) { $character = substr($characters, 0, 1); }
155  return ord($character);
156  }
157  } // function ASCIICODE()
158 
159 
165  public static function CONCATENATE() {
166  // Return value
167  $returnValue = '';
168 
169  // Loop through arguments
170  $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
171  foreach ($aArgs as $arg) {
172  if (is_bool($arg)) {
173  if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
174  $arg = (int) $arg;
175  } else {
176  if ($arg) {
177  $arg = 'TRUE';
178  } else {
179  $arg = 'FALSE';
180  }
181  }
182  }
183  $returnValue .= $arg;
184  }
185 
186  // Return
187  return $returnValue;
188  } // function CONCATENATE()
189 
190 
203  public static function DOLLAR($value = 0, $decimals = 2) {
204  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
205  $decimals = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
206 
207  // Validate parameters
208  if (!is_numeric($value) || !is_numeric($decimals)) {
209  return PHPExcel_Calculation_Functions::NaN();
210  }
211  $decimals = floor($decimals);
212 
213  if ($decimals > 0) {
214  return money_format('%.'.$decimals.'n',$value);
215  } else {
216  $round = pow(10,abs($decimals));
217  if ($value < 0) { $round = 0-$round; }
218  $value = PHPExcel_Calculation_MathTrig::MROUND($value,$round);
219  // The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
220  // so we display to 1 dp and chop off that character and the decimal separator using substr
221  return substr(money_format('%.1n',$value),0,-2);
222  }
223  } // function DOLLAR()
224 
225 
234  public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
235  $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
236  $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
237  $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
238 
239  if (!is_bool($needle)) {
240  if (is_bool($haystack)) {
241  $haystack = ($haystack) ? 'TRUE' : 'FALSE';
242  }
243 
244  if (($offset > 0) && (strlen($haystack) > $offset)) {
245  if (function_exists('mb_strpos')) {
246  $pos = mb_strpos($haystack, $needle, --$offset,'UTF-8');
247  } else {
248  $pos = strpos($haystack, $needle, --$offset);
249  }
250  if ($pos !== false) {
251  return ++$pos;
252  }
253  }
254  }
255  return PHPExcel_Calculation_Functions::VALUE();
256  } // function SEARCHSENSITIVE()
257 
258 
267  public static function SEARCHINSENSITIVE($needle,$haystack,$offset=1) {
268  $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
269  $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
270  $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
271 
272  if (!is_bool($needle)) {
273  if (is_bool($haystack)) {
274  $haystack = ($haystack) ? 'TRUE' : 'FALSE';
275  }
276 
277  if (($offset > 0) && (strlen($haystack) > $offset)) {
278  if (function_exists('mb_stripos')) {
279  $pos = mb_stripos($haystack, $needle, --$offset,'UTF-8');
280  } else {
281  $pos = stripos($haystack, $needle, --$offset);
282  }
283  if ($pos !== false) {
284  return ++$pos;
285  }
286  }
287  }
288  return PHPExcel_Calculation_Functions::VALUE();
289  } // function SEARCHINSENSITIVE()
290 
291 
298  public static function FIXEDFORMAT($value,$decimals=2,$no_commas=false) {
299  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
300  $decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
301  $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
302 
303  $valueResult = round($value,$decimals);
304  if ($decimals < 0) { $decimals = 0; }
305  if (!$no_commas) {
306  $valueResult = number_format($valueResult,$decimals);
307  }
308 
309  return (string) $valueResult;
310  } // function FIXEDFORMAT()
311 
312 
320  public static function LEFT($value = '', $chars = 1) {
321  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
322  $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
323 
324  if ($chars < 0) {
325  return PHPExcel_Calculation_Functions::VALUE();
326  }
327 
328  if (is_bool($value)) {
329  $value = ($value) ? 'TRUE' : 'FALSE';
330  }
331 
332  if (function_exists('mb_substr')) {
333  return mb_substr($value, 0, $chars, 'UTF-8');
334  } else {
335  return substr($value, 0, $chars);
336  }
337  } // function LEFT()
338 
339 
348  public static function MID($value = '', $start = 1, $chars = null) {
349  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
350  $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
351  $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
352 
353  if (($start < 1) || ($chars < 0)) {
354  return PHPExcel_Calculation_Functions::VALUE();
355  }
356 
357  if (is_bool($value)) {
358  $value = ($value) ? 'TRUE' : 'FALSE';
359  }
360 
361  if (function_exists('mb_substr')) {
362  return mb_substr($value, --$start, $chars, 'UTF-8');
363  } else {
364  return substr($value, --$start, $chars);
365  }
366  } // function MID()
367 
368 
376  public static function RIGHT($value = '', $chars = 1) {
377  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
378  $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
379 
380  if ($chars < 0) {
381  return PHPExcel_Calculation_Functions::VALUE();
382  }
383 
384  if (is_bool($value)) {
385  $value = ($value) ? 'TRUE' : 'FALSE';
386  }
387 
388  if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
389  return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
390  } else {
391  return substr($value, strlen($value) - $chars);
392  }
393  } // function RIGHT()
394 
395 
403  public static function STRINGLENGTH($value = '') {
404  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
405 
406  if (is_bool($value)) {
407  $value = ($value) ? 'TRUE' : 'FALSE';
408  }
409 
410  if (function_exists('mb_strlen')) {
411  return mb_strlen($value, 'UTF-8');
412  } else {
413  return strlen($value);
414  }
415  } // function STRINGLENGTH()
416 
417 
426  public static function LOWERCASE($mixedCaseString) {
427  $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
428 
429  if (is_bool($mixedCaseString)) {
430  $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
431  }
432 
433  if (function_exists('mb_convert_case')) {
434  return mb_convert_case($mixedCaseString, MB_CASE_LOWER, 'UTF-8');
435  } else {
436  return strtoupper($mixedCaseString);
437  }
438  } // function LOWERCASE()
439 
440 
449  public static function UPPERCASE($mixedCaseString) {
450  $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
451 
452  if (is_bool($mixedCaseString)) {
453  $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
454  }
455 
456  if (function_exists('mb_convert_case')) {
457  return mb_convert_case($mixedCaseString, MB_CASE_UPPER, 'UTF-8');
458  } else {
459  return strtoupper($mixedCaseString);
460  }
461  } // function UPPERCASE()
462 
463 
472  public static function PROPERCASE($mixedCaseString) {
473  $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
474 
475  if (is_bool($mixedCaseString)) {
476  $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
477  }
478 
479  if (function_exists('mb_convert_case')) {
480  return mb_convert_case($mixedCaseString, MB_CASE_TITLE, 'UTF-8');
481  } else {
482  return ucwords($mixedCaseString);
483  }
484  } // function PROPERCASE()
485 
486 
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 
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 $left.$newText.$right;
552  } // function SUBSTITUTE()
553 
554 
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 
577  public static function TEXTFORMAT($value,$format) {
578  $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
579  $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
580 
581  if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
582  $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
583  }
584 
585  return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
586  } // function TEXTFORMAT()
587 
588 } // class PHPExcel_Calculation_TextData