Subversion Repositories eFlore/Applications.cel

Rev

Details | Last modification | View Log | RSS feed

Rev Author Line No. Line
2390 jpm 1
<?php
2
/**
3
 * PHPExcel
4
 *
5
 * Copyright (c) 2006 - 2013 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 - 2013 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 - 2013 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
		if ($decimals > 0) {
212
			return money_format('%.'.$decimals.'n',$value);
213
		} else {
214
			$round = pow(10,abs($decimals));
215
			if ($value < 0) { $round = 0-$round; }
216
			$value = PHPExcel_Calculation_MathTrig::MROUND($value,$round);
217
			//	The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
218
			//		so we display to 1 dp and chop off that character and the decimal separator using substr
219
			return substr(money_format('%.1n',$value),0,-2);
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
}	//	class PHPExcel_Calculation_TextData