File Source for TextData.php

PHPExcel_Calculation

# Source for file TextData.php

Documentation is available at TextData.php

1. <?php
2. /**
3. * PHPExcel
4. *
5. * Copyright (c) 2006 - 2011 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 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
25. * @version 1.7.6, 2011-02-27
26. */
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. }
39. /**
40. * PHPExcel_Calculation_TextData
41. *
42. * @category PHPExcel
43. * @package PHPExcel_Calculation
44. * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel)
45. */
46. class PHPExcel_Calculation_TextData {
48. private static $_invalidChars = Null;
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()
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);
77. if ((! is_numeric($character)) || ($character < 0)) {
78. return PHPExcel_Calculation_Functions:: VALUE();
79. }
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. }
89. /**
90. * TRIMNONPRINTABLE
91. *
92. * @param mixed $value Value to check
93. * @return string
94. */
95. public static function TRIMNONPRINTABLE($stringValue = '') {
96. $stringValue = PHPExcel_Calculation_Functions:: flattenSingleValue($stringValue);
98. if ( is_bool($stringValue)) {
99. $stringValue = ($stringValue) ? 'TRUE' : 'FALSE';
100. }
102. if (self::$_invalidChars == Null) {
103. self::$_invalidChars = range( chr(0), chr(31));
104. }
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()
113. /**
114. * TRIMSPACES
115. *
116. * @param mixed $value Value to check
117. * @return string
118. */
119. public static function TRIMSPACES($stringValue = '') {
120. $stringValue = PHPExcel_Calculation_Functions:: flattenSingleValue($stringValue);
122. if ( is_string($stringValue) || is_numeric($stringValue)) {
123. return trim( preg_replace('/ +/',' ',$stringValue));
124. }
125. return Null;
126. } // function TRIMSPACES()
129. /**
130. * ASCIICODE
131. *
132. * @param string $character Value
133. * @return int
134. */
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. }
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()
160. /**
161. * CONCATENATE
162. *
163. * @return string
164. */
165. public static function CONCATENATE() {
166. // Return value
167. $returnValue = '';
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. }
186. // Return
187. return $returnValue;
188. } // function CONCATENATE()
191. /**
192. * DOLLAR
193. *
194. * This function converts a number to text using currency format, with the decimals rounded to the specified place.
195. * The format used is $#,##0.00_);($#,##0.00)..
196. *
197. * @param float $value The value to format
198. * @param int $decimals The number of digits to display to the right of the decimal point.
199. * If decimals is negative, number is rounded to the left of the decimal point.
200. * If you omit decimals, it is assumed to be 2
201. * @return string
202. */
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);
207. // Validate parameters
208. if (! is_numeric($value) || ! is_numeric($decimals)) {
209. return PHPExcel_Calculation_Functions:: NaN();
210. }
211. $decimals = floor($decimals);
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()
226. /**
227. * SEARCHSENSITIVE
228. *
229. * @param string $needle The string to look for
230. * @param string $haystack The string in which to look
231. * @param int $offset Offset within $haystack
232. * @return string
233. */
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);
239. if (! is_bool($needle)) {
240. if ( is_bool($haystack)) {
241. $haystack = ($haystack) ? 'TRUE' : 'FALSE';
242. }
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()
259. /**
260. * SEARCHINSENSITIVE
261. *
262. * @param string $needle The string to look for
263. * @param string $haystack The string in which to look
264. * @param int $offset Offset within $haystack
265. * @return string
266. */
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);
272. if (! is_bool($needle)) {
273. if ( is_bool($haystack)) {
274. $haystack = ($haystack) ? 'TRUE' : 'FALSE';
275. }
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()
292. /**
293. * FIXEDFORMAT
294. *
295. * @param mixed $value Value to check
296. * @return boolean
297. */
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);
303. $valueResult = round($value,$decimals);
304. if ($decimals < 0) { $decimals = 0; }
305. if (!$no_commas) {
306. $valueResult = number_format($valueResult,$decimals);
307. }
309. return (string) $valueResult;
310. } // function FIXEDFORMAT()
313. /**
314. * LEFT
315. *
316. * @param string $value Value
317. * @param int $chars Number of characters
318. * @return string
319. */
320. public static function LEFT($value = '', $chars = 1) {
321. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
322. $chars = PHPExcel_Calculation_Functions:: flattenSingleValue($chars);
324. if ($chars < 0) {
325. return PHPExcel_Calculation_Functions:: VALUE();
326. }
328. if ( is_bool($value)) {
329. $value = ($value) ? 'TRUE' : 'FALSE';
330. }
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()
340. /**
341. * MID
342. *
343. * @param string $value Value
344. * @param int $start Start character
345. * @param int $chars Number of characters
346. * @return string
347. */
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);
353. if (($start < 1) || ($chars < 0)) {
354. return PHPExcel_Calculation_Functions:: VALUE();
355. }
357. if ( is_bool($value)) {
358. $value = ($value) ? 'TRUE' : 'FALSE';
359. }
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()
369. /**
370. * RIGHT
371. *
372. * @param string $value Value
373. * @param int $chars Number of characters
374. * @return string
375. */
376. public static function RIGHT($value = '', $chars = 1) {
377. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
378. $chars = PHPExcel_Calculation_Functions:: flattenSingleValue($chars);
380. if ($chars < 0) {
381. return PHPExcel_Calculation_Functions:: VALUE();
382. }
384. if ( is_bool($value)) {
385. $value = ($value) ? 'TRUE' : 'FALSE';
386. }
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()
396. /**
397. * STRINGLENGTH
398. *
399. * @param string $value Value
400. * @param int $chars Number of characters
401. * @return string
402. */
403. public static function STRINGLENGTH($value = '') {
404. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
406. if ( is_bool($value)) {
407. $value = ($value) ? 'TRUE' : 'FALSE';
408. }
410. if ( function_exists('mb_strlen')) {
411. return mb_strlen($value, 'UTF-8');
412. } else {
413. return strlen($value);
414. }
415. } // function STRINGLENGTH()
418. /**
419. * LOWERCASE
420. *
421. * Converts a string value to upper case.
422. *
423. * @param string $mixedCaseString
424. * @return string
425. */
426. public static function LOWERCASE($mixedCaseString) {
427. $mixedCaseString = PHPExcel_Calculation_Functions:: flattenSingleValue($mixedCaseString);
429. if ( is_bool($mixedCaseString)) {
430. $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
431. }
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()
441. /**
442. * UPPERCASE
443. *
444. * Converts a string value to upper case.
445. *
446. * @param string $mixedCaseString
447. * @return string
448. */
449. public static function UPPERCASE($mixedCaseString) {
450. $mixedCaseString = PHPExcel_Calculation_Functions:: flattenSingleValue($mixedCaseString);
452. if ( is_bool($mixedCaseString)) {
453. $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
454. }
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()
464. /**
465. * PROPERCASE
466. *
467. * Converts a string value to upper case.
468. *
469. * @param string $mixedCaseString
470. * @return string
471. */
472. public static function PROPERCASE($mixedCaseString) {
473. $mixedCaseString = PHPExcel_Calculation_Functions:: flattenSingleValue($mixedCaseString);
475. if ( is_bool($mixedCaseString)) {
476. $mixedCaseString = ($mixedCaseString) ? 'TRUE' : 'FALSE';
477. }
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()
487. /**
488. * REPLACE
489. *
490. * @param string $value Value
491. * @param int $start Start character
492. * @param int $chars Number of characters
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);
501. $left = self:: LEFT($oldText,$start-1);
502. $right = self:: RIGHT($oldText,self:: STRINGLENGTH($oldText)-($start+$chars)+1);
504. return $left.$newText.$right;
505. } // function REPLACE()
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));
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. }
551. return $left.$newText.$right;
552. } // function SUBSTITUTE()
555. /**
556. * RETURNSTRING
557. *
558. * @param mixed $value Value to check
559. * @return boolean
560. */
561. public static function RETURNSTRING($testValue = '') {
562. $testValue = PHPExcel_Calculation_Functions:: flattenSingleValue($testValue);
564. if ( is_string($testValue)) {
565. return $testValue;
566. }
567. return Null;
568. } // function RETURNSTRING()
571. /**
572. * TEXTFORMAT
573. *
574. * @param mixed $value Value to check
575. * @return boolean
576. */
577. public static function TEXTFORMAT($value,$format) {
578. $value = PHPExcel_Calculation_Functions:: flattenSingleValue($value);
579. $format = PHPExcel_Calculation_Functions:: flattenSingleValue($format);
581. if (( is_string($value)) && (! is_numeric($value)) && PHPExcel_Shared_Date:: isDateTimeFormatCode($format)) {
582. $value = PHPExcel_Calculation_DateTime:: DATEVALUE($value);
583. }
585. return (string) PHPExcel_Style_NumberFormat:: toFormattedString($value,$format);
586. } // function TEXTFORMAT()
588. } // class PHPExcel_Calculation_TextData

Documentation generated on Sun, 27 Feb 2011 16:35:38 -0800 by phpDocumentor 1.4.3