PageRenderTime 57ms CodeModel.GetById 30ms RepoModel.GetById 0ms app.codeStats 0ms

/PHPExcel_1.7.8-with_documentation-msoffice_format/PHPExcel_1.7.8-with_documentation-msoffice_format/Classes/PHPExcel/Calculation/TextData.php

https://bitbucket.org/izubizarreta/https-bitbucket.org-bityvip
PHP | 598 lines | 321 code | 89 blank | 188 comment | 110 complexity | 43ad558ffe62c689b536cf660f5ca176 MD5 | raw file
Possible License(s): LGPL-3.0, LGPL-2.0, JSON, GPL-2.0, BSD-3-Clause, LGPL-2.1, MIT
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2012 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 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  24. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  25. * @version 1.7.8, 2012-10-12
  26. */
  27. /** PHPExcel root directory */
  28. if (!defined('PHPEXCEL_ROOT')) {
  29. /**
  30. * @ignore
  31. */
  32. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  33. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  34. }
  35. /**
  36. * PHPExcel_Calculation_TextData
  37. *
  38. * @category PHPExcel
  39. * @package PHPExcel_Calculation
  40. * @copyright Copyright (c) 2006 - 2012 PHPExcel (http://www.codeplex.com/PHPExcel)
  41. */
  42. class PHPExcel_Calculation_TextData {
  43. private static $_invalidChars = Null;
  44. private static function _uniord($c) {
  45. if (ord($c{0}) >=0 && ord($c{0}) <= 127)
  46. return ord($c{0});
  47. if (ord($c{0}) >= 192 && ord($c{0}) <= 223)
  48. return (ord($c{0})-192)*64 + (ord($c{1})-128);
  49. if (ord($c{0}) >= 224 && ord($c{0}) <= 239)
  50. return (ord($c{0})-224)*4096 + (ord($c{1})-128)*64 + (ord($c{2})-128);
  51. if (ord($c{0}) >= 240 && ord($c{0}) <= 247)
  52. return (ord($c{0})-240)*262144 + (ord($c{1})-128)*4096 + (ord($c{2})-128)*64 + (ord($c{3})-128);
  53. if (ord($c{0}) >= 248 && ord($c{0}) <= 251)
  54. 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);
  55. if (ord($c{0}) >= 252 && ord($c{0}) <= 253)
  56. 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);
  57. if (ord($c{0}) >= 254 && ord($c{0}) <= 255) //error
  58. return PHPExcel_Calculation_Functions::VALUE();
  59. return 0;
  60. } // function _uniord()
  61. /**
  62. * CHARACTER
  63. *
  64. * @param string $character Value
  65. * @return int
  66. */
  67. public static function CHARACTER($character) {
  68. $character = PHPExcel_Calculation_Functions::flattenSingleValue($character);
  69. if ((!is_numeric($character)) || ($character < 0)) {
  70. return PHPExcel_Calculation_Functions::VALUE();
  71. }
  72. if (function_exists('mb_convert_encoding')) {
  73. return mb_convert_encoding('&#'.intval($character).';', 'UTF-8', 'HTML-ENTITIES');
  74. } else {
  75. return chr(intval($character));
  76. }
  77. }
  78. /**
  79. * TRIMNONPRINTABLE
  80. *
  81. * @param mixed $value Value to check
  82. * @return string
  83. */
  84. public static function TRIMNONPRINTABLE($stringValue = '') {
  85. $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
  86. if (is_bool($stringValue)) {
  87. return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  88. }
  89. if (self::$_invalidChars == Null) {
  90. self::$_invalidChars = range(chr(0),chr(31));
  91. }
  92. if (is_string($stringValue) || is_numeric($stringValue)) {
  93. return str_replace(self::$_invalidChars,'',trim($stringValue,"\x00..\x1F"));
  94. }
  95. return NULL;
  96. } // function TRIMNONPRINTABLE()
  97. /**
  98. * TRIMSPACES
  99. *
  100. * @param mixed $value Value to check
  101. * @return string
  102. */
  103. public static function TRIMSPACES($stringValue = '') {
  104. $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
  105. if (is_bool($stringValue)) {
  106. return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  107. }
  108. if (is_string($stringValue) || is_numeric($stringValue)) {
  109. return trim(preg_replace('/ +/',' ',trim($stringValue,' ')));
  110. }
  111. return NULL;
  112. } // function TRIMSPACES()
  113. /**
  114. * ASCIICODE
  115. *
  116. * @param string $character Value
  117. * @return int
  118. */
  119. public static function ASCIICODE($characters) {
  120. if (($characters === NULL) || ($characters === ''))
  121. return PHPExcel_Calculation_Functions::VALUE();
  122. $characters = PHPExcel_Calculation_Functions::flattenSingleValue($characters);
  123. if (is_bool($characters)) {
  124. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
  125. $characters = (int) $characters;
  126. } else {
  127. $characters = ($characters) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  128. }
  129. }
  130. $character = $characters;
  131. if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
  132. if (mb_strlen($characters, 'UTF-8') > 1) { $character = mb_substr($characters, 0, 1, 'UTF-8'); }
  133. return self::_uniord($character);
  134. } else {
  135. if (strlen($characters) > 0) { $character = substr($characters, 0, 1); }
  136. return ord($character);
  137. }
  138. } // function ASCIICODE()
  139. /**
  140. * CONCATENATE
  141. *
  142. * @return string
  143. */
  144. public static function CONCATENATE() {
  145. // Return value
  146. $returnValue = '';
  147. // Loop through arguments
  148. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  149. foreach ($aArgs as $arg) {
  150. if (is_bool($arg)) {
  151. if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
  152. $arg = (int) $arg;
  153. } else {
  154. $arg = ($arg) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  155. }
  156. }
  157. $returnValue .= $arg;
  158. }
  159. // Return
  160. return $returnValue;
  161. } // function CONCATENATE()
  162. /**
  163. * DOLLAR
  164. *
  165. * This function converts a number to text using currency format, with the decimals rounded to the specified place.
  166. * The format used is $#,##0.00_);($#,##0.00)..
  167. *
  168. * @param float $value The value to format
  169. * @param int $decimals The number of digits to display to the right of the decimal point.
  170. * If decimals is negative, number is rounded to the left of the decimal point.
  171. * If you omit decimals, it is assumed to be 2
  172. * @return string
  173. */
  174. public static function DOLLAR($value = 0, $decimals = 2) {
  175. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  176. $decimals = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
  177. // Validate parameters
  178. if (!is_numeric($value) || !is_numeric($decimals)) {
  179. return PHPExcel_Calculation_Functions::NaN();
  180. }
  181. $decimals = floor($decimals);
  182. if ($decimals > 0) {
  183. return money_format('%.'.$decimals.'n',$value);
  184. } else {
  185. $round = pow(10,abs($decimals));
  186. if ($value < 0) { $round = 0-$round; }
  187. $value = PHPExcel_Calculation_MathTrig::MROUND($value,$round);
  188. // The implementation of money_format used if the standard PHP function is not available can't handle decimal places of 0,
  189. // so we display to 1 dp and chop off that character and the decimal separator using substr
  190. return substr(money_format('%.1n',$value),0,-2);
  191. }
  192. } // function DOLLAR()
  193. /**
  194. * SEARCHSENSITIVE
  195. *
  196. * @param string $needle The string to look for
  197. * @param string $haystack The string in which to look
  198. * @param int $offset Offset within $haystack
  199. * @return string
  200. */
  201. public static function SEARCHSENSITIVE($needle,$haystack,$offset=1) {
  202. $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
  203. $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
  204. $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
  205. if (!is_bool($needle)) {
  206. if (is_bool($haystack)) {
  207. $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  208. }
  209. if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
  210. if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
  211. return $offset;
  212. }
  213. if (function_exists('mb_strpos')) {
  214. $pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
  215. } else {
  216. $pos = strpos($haystack, $needle, --$offset);
  217. }
  218. if ($pos !== false) {
  219. return ++$pos;
  220. }
  221. }
  222. }
  223. return PHPExcel_Calculation_Functions::VALUE();
  224. } // function SEARCHSENSITIVE()
  225. /**
  226. * SEARCHINSENSITIVE
  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 SEARCHINSENSITIVE($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. if (!is_bool($needle)) {
  238. if (is_bool($haystack)) {
  239. $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  240. }
  241. if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
  242. if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
  243. return $offset;
  244. }
  245. if (function_exists('mb_stripos')) {
  246. $pos = mb_stripos($haystack, $needle, --$offset,'UTF-8');
  247. } else {
  248. $pos = stripos($haystack, $needle, --$offset);
  249. }
  250. if ($pos !== false) {
  251. return ++$pos;
  252. }
  253. }
  254. }
  255. return PHPExcel_Calculation_Functions::VALUE();
  256. } // function SEARCHINSENSITIVE()
  257. /**
  258. * FIXEDFORMAT
  259. *
  260. * @param mixed $value Value to check
  261. * @return boolean
  262. */
  263. public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = FALSE) {
  264. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  265. $decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
  266. $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
  267. // Validate parameters
  268. if (!is_numeric($value) || !is_numeric($decimals)) {
  269. return PHPExcel_Calculation_Functions::NaN();
  270. }
  271. $decimals = floor($decimals);
  272. $valueResult = round($value,$decimals);
  273. if ($decimals < 0) { $decimals = 0; }
  274. if (!$no_commas) {
  275. $valueResult = number_format($valueResult,$decimals);
  276. }
  277. return (string) $valueResult;
  278. } // function FIXEDFORMAT()
  279. /**
  280. * LEFT
  281. *
  282. * @param string $value Value
  283. * @param int $chars Number of characters
  284. * @return string
  285. */
  286. public static function LEFT($value = '', $chars = 1) {
  287. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  288. $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
  289. if ($chars < 0) {
  290. return PHPExcel_Calculation_Functions::VALUE();
  291. }
  292. if (is_bool($value)) {
  293. $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  294. }
  295. if (function_exists('mb_substr')) {
  296. return mb_substr($value, 0, $chars, 'UTF-8');
  297. } else {
  298. return substr($value, 0, $chars);
  299. }
  300. } // function LEFT()
  301. /**
  302. * MID
  303. *
  304. * @param string $value Value
  305. * @param int $start Start character
  306. * @param int $chars Number of characters
  307. * @return string
  308. */
  309. public static function MID($value = '', $start = 1, $chars = null) {
  310. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  311. $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
  312. $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
  313. if (($start < 1) || ($chars < 0)) {
  314. return PHPExcel_Calculation_Functions::VALUE();
  315. }
  316. if (is_bool($value)) {
  317. $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  318. }
  319. if (function_exists('mb_substr')) {
  320. return mb_substr($value, --$start, $chars, 'UTF-8');
  321. } else {
  322. return substr($value, --$start, $chars);
  323. }
  324. } // function MID()
  325. /**
  326. * RIGHT
  327. *
  328. * @param string $value Value
  329. * @param int $chars Number of characters
  330. * @return string
  331. */
  332. public static function RIGHT($value = '', $chars = 1) {
  333. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  334. $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
  335. if ($chars < 0) {
  336. return PHPExcel_Calculation_Functions::VALUE();
  337. }
  338. if (is_bool($value)) {
  339. $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  340. }
  341. if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
  342. return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
  343. } else {
  344. return substr($value, strlen($value) - $chars);
  345. }
  346. } // function RIGHT()
  347. /**
  348. * STRINGLENGTH
  349. *
  350. * @param string $value Value
  351. * @param int $chars Number of characters
  352. * @return string
  353. */
  354. public static function STRINGLENGTH($value = '') {
  355. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  356. if (is_bool($value)) {
  357. $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  358. }
  359. if (function_exists('mb_strlen')) {
  360. return mb_strlen($value, 'UTF-8');
  361. } else {
  362. return strlen($value);
  363. }
  364. } // function STRINGLENGTH()
  365. /**
  366. * LOWERCASE
  367. *
  368. * Converts a string value to upper case.
  369. *
  370. * @param string $mixedCaseString
  371. * @return string
  372. */
  373. public static function LOWERCASE($mixedCaseString) {
  374. $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
  375. if (is_bool($mixedCaseString)) {
  376. $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  377. }
  378. if (function_exists('mb_convert_case')) {
  379. return mb_convert_case($mixedCaseString, MB_CASE_LOWER, 'UTF-8');
  380. } else {
  381. return strtoupper($mixedCaseString);
  382. }
  383. } // function LOWERCASE()
  384. /**
  385. * UPPERCASE
  386. *
  387. * Converts a string value to upper case.
  388. *
  389. * @param string $mixedCaseString
  390. * @return string
  391. */
  392. public static function UPPERCASE($mixedCaseString) {
  393. $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
  394. if (is_bool($mixedCaseString)) {
  395. $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  396. }
  397. if (function_exists('mb_convert_case')) {
  398. return mb_convert_case($mixedCaseString, MB_CASE_UPPER, 'UTF-8');
  399. } else {
  400. return strtoupper($mixedCaseString);
  401. }
  402. } // function UPPERCASE()
  403. /**
  404. * PROPERCASE
  405. *
  406. * Converts a string value to upper case.
  407. *
  408. * @param string $mixedCaseString
  409. * @return string
  410. */
  411. public static function PROPERCASE($mixedCaseString) {
  412. $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
  413. if (is_bool($mixedCaseString)) {
  414. $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
  415. }
  416. if (function_exists('mb_convert_case')) {
  417. return mb_convert_case($mixedCaseString, MB_CASE_TITLE, 'UTF-8');
  418. } else {
  419. return ucwords($mixedCaseString);
  420. }
  421. } // function PROPERCASE()
  422. /**
  423. * REPLACE
  424. *
  425. * @param string $value Value
  426. * @param int $start Start character
  427. * @param int $chars Number of characters
  428. * @return string
  429. */
  430. public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText) {
  431. $oldText = PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
  432. $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
  433. $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
  434. $newText = PHPExcel_Calculation_Functions::flattenSingleValue($newText);
  435. $left = self::LEFT($oldText,$start-1);
  436. $right = self::RIGHT($oldText,self::STRINGLENGTH($oldText)-($start+$chars)+1);
  437. return $left.$newText.$right;
  438. } // function REPLACE()
  439. /**
  440. * SUBSTITUTE
  441. *
  442. * @param string $text Value
  443. * @param string $fromText From Value
  444. * @param string $toText To Value
  445. * @param integer $instance Instance Number
  446. * @return string
  447. */
  448. public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0) {
  449. $text = PHPExcel_Calculation_Functions::flattenSingleValue($text);
  450. $fromText = PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
  451. $toText = PHPExcel_Calculation_Functions::flattenSingleValue($toText);
  452. $instance = floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
  453. if ($instance == 0) {
  454. if(function_exists('mb_str_replace')) {
  455. return mb_str_replace($fromText,$toText,$text);
  456. } else {
  457. return str_replace($fromText,$toText,$text);
  458. }
  459. } else {
  460. $pos = -1;
  461. while($instance > 0) {
  462. if (function_exists('mb_strpos')) {
  463. $pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
  464. } else {
  465. $pos = strpos($text, $fromText, $pos+1);
  466. }
  467. if ($pos === false) {
  468. break;
  469. }
  470. --$instance;
  471. }
  472. if ($pos !== false) {
  473. if (function_exists('mb_strlen')) {
  474. return self::REPLACE($text,++$pos,mb_strlen($fromText, 'UTF-8'),$toText);
  475. } else {
  476. return self::REPLACE($text,++$pos,strlen($fromText),$toText);
  477. }
  478. }
  479. }
  480. return $text;
  481. } // function SUBSTITUTE()
  482. /**
  483. * RETURNSTRING
  484. *
  485. * @param mixed $value Value to check
  486. * @return boolean
  487. */
  488. public static function RETURNSTRING($testValue = '') {
  489. $testValue = PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
  490. if (is_string($testValue)) {
  491. return $testValue;
  492. }
  493. return Null;
  494. } // function RETURNSTRING()
  495. /**
  496. * TEXTFORMAT
  497. *
  498. * @param mixed $value Value to check
  499. * @return boolean
  500. */
  501. public static function TEXTFORMAT($value,$format) {
  502. $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
  503. $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
  504. if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
  505. $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
  506. }
  507. return (string) PHPExcel_Style_NumberFormat::toFormattedString($value,$format);
  508. } // function TEXTFORMAT()
  509. } // class PHPExcel_Calculation_TextData