123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651 |
- <?php
- if (!defined('PHPEXCEL_ROOT')) {
-
- define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
- require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
- }
- class PHPExcel_Calculation_TextData
- {
- private static $invalidChars;
- private static function unicodeToOrd($c)
- {
- if (ord($c{0}) >=0 && ord($c{0}) <= 127) {
- return ord($c{0});
- } elseif (ord($c{0}) >= 192 && ord($c{0}) <= 223) {
- return (ord($c{0})-192)*64 + (ord($c{1})-128);
- } elseif (ord($c{0}) >= 224 && ord($c{0}) <= 239) {
- return (ord($c{0})-224)*4096 + (ord($c{1})-128)*64 + (ord($c{2})-128);
- } elseif (ord($c{0}) >= 240 && ord($c{0}) <= 247) {
- return (ord($c{0})-240)*262144 + (ord($c{1})-128)*4096 + (ord($c{2})-128)*64 + (ord($c{3})-128);
- } elseif (ord($c{0}) >= 248 && ord($c{0}) <= 251) {
- 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);
- } elseif (ord($c{0}) >= 252 && ord($c{0}) <= 253) {
- 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);
- } elseif (ord($c{0}) >= 254 && ord($c{0}) <= 255) {
-
- return PHPExcel_Calculation_Functions::VALUE();
- }
- return 0;
- }
-
- public static function CHARACTER($character)
- {
- $character = PHPExcel_Calculation_Functions::flattenSingleValue($character);
- if ((!is_numeric($character)) || ($character < 0)) {
- return PHPExcel_Calculation_Functions::VALUE();
- }
- if (function_exists('mb_convert_encoding')) {
- return mb_convert_encoding('&#'.intval($character).';', 'UTF-8', 'HTML-ENTITIES');
- } else {
- return chr(intval($character));
- }
- }
-
- public static function TRIMNONPRINTABLE($stringValue = '')
- {
- $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
- if (is_bool($stringValue)) {
- return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (self::$invalidChars == null) {
- self::$invalidChars = range(chr(0), chr(31));
- }
- if (is_string($stringValue) || is_numeric($stringValue)) {
- return str_replace(self::$invalidChars, '', trim($stringValue, "\x00..\x1F"));
- }
- return null;
- }
-
- public static function TRIMSPACES($stringValue = '')
- {
- $stringValue = PHPExcel_Calculation_Functions::flattenSingleValue($stringValue);
- if (is_bool($stringValue)) {
- return ($stringValue) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (is_string($stringValue) || is_numeric($stringValue)) {
- return trim(preg_replace('/ +/', ' ', trim($stringValue, ' ')), ' ');
- }
- return null;
- }
-
- public static function ASCIICODE($characters)
- {
- if (($characters === null) || ($characters === '')) {
- return PHPExcel_Calculation_Functions::VALUE();
- }
- $characters = PHPExcel_Calculation_Functions::flattenSingleValue($characters);
- if (is_bool($characters)) {
- if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
- $characters = (int) $characters;
- } else {
- $characters = ($characters) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- }
- $character = $characters;
- if ((function_exists('mb_strlen')) && (function_exists('mb_substr'))) {
- if (mb_strlen($characters, 'UTF-8') > 1) {
- $character = mb_substr($characters, 0, 1, 'UTF-8');
- }
- return self::unicodeToOrd($character);
- } else {
- if (strlen($characters) > 0) {
- $character = substr($characters, 0, 1);
- }
- return ord($character);
- }
- }
-
- public static function CONCATENATE()
- {
- $returnValue = '';
-
- $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
- foreach ($aArgs as $arg) {
- if (is_bool($arg)) {
- if (PHPExcel_Calculation_Functions::getCompatibilityMode() == PHPExcel_Calculation_Functions::COMPATIBILITY_OPENOFFICE) {
- $arg = (int) $arg;
- } else {
- $arg = ($arg) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- }
- $returnValue .= $arg;
- }
- return $returnValue;
- }
-
- public static function DOLLAR($value = 0, $decimals = 2)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $decimals = is_null($decimals) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
-
- if (!is_numeric($value) || !is_numeric($decimals)) {
- return PHPExcel_Calculation_Functions::NaN();
- }
- $decimals = floor($decimals);
- $mask = '$#,##0';
- if ($decimals > 0) {
- $mask .= '.' . str_repeat('0', $decimals);
- } else {
- $round = pow(10, abs($decimals));
- if ($value < 0) {
- $round = 0-$round;
- }
- $value = PHPExcel_Calculation_MathTrig::MROUND($value, $round);
- }
- return PHPExcel_Style_NumberFormat::toFormattedString($value, $mask);
- }
-
- public static function SEARCHSENSITIVE($needle, $haystack, $offset = 1)
- {
- $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
- $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
- $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
- if (!is_bool($needle)) {
- if (is_bool($haystack)) {
- $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
- if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
- return $offset;
- }
- if (function_exists('mb_strpos')) {
- $pos = mb_strpos($haystack, $needle, --$offset, 'UTF-8');
- } else {
- $pos = strpos($haystack, $needle, --$offset);
- }
- if ($pos !== false) {
- return ++$pos;
- }
- }
- }
- return PHPExcel_Calculation_Functions::VALUE();
- }
-
- public static function SEARCHINSENSITIVE($needle, $haystack, $offset = 1)
- {
- $needle = PHPExcel_Calculation_Functions::flattenSingleValue($needle);
- $haystack = PHPExcel_Calculation_Functions::flattenSingleValue($haystack);
- $offset = PHPExcel_Calculation_Functions::flattenSingleValue($offset);
- if (!is_bool($needle)) {
- if (is_bool($haystack)) {
- $haystack = ($haystack) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (($offset > 0) && (PHPExcel_Shared_String::CountCharacters($haystack) > $offset)) {
- if (PHPExcel_Shared_String::CountCharacters($needle) == 0) {
- return $offset;
- }
- if (function_exists('mb_stripos')) {
- $pos = mb_stripos($haystack, $needle, --$offset, 'UTF-8');
- } else {
- $pos = stripos($haystack, $needle, --$offset);
- }
- if ($pos !== false) {
- return ++$pos;
- }
- }
- }
- return PHPExcel_Calculation_Functions::VALUE();
- }
-
- public static function FIXEDFORMAT($value, $decimals = 2, $no_commas = false)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $decimals = PHPExcel_Calculation_Functions::flattenSingleValue($decimals);
- $no_commas = PHPExcel_Calculation_Functions::flattenSingleValue($no_commas);
-
- if (!is_numeric($value) || !is_numeric($decimals)) {
- return PHPExcel_Calculation_Functions::NaN();
- }
- $decimals = floor($decimals);
- $valueResult = round($value, $decimals);
- if ($decimals < 0) {
- $decimals = 0;
- }
- if (!$no_commas) {
- $valueResult = number_format($valueResult, $decimals);
- }
- return (string) $valueResult;
- }
-
- public static function LEFT($value = '', $chars = 1)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
- if ($chars < 0) {
- return PHPExcel_Calculation_Functions::VALUE();
- }
- if (is_bool($value)) {
- $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (function_exists('mb_substr')) {
- return mb_substr($value, 0, $chars, 'UTF-8');
- } else {
- return substr($value, 0, $chars);
- }
- }
-
- public static function MID($value = '', $start = 1, $chars = null)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
- $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
- if (($start < 1) || ($chars < 0)) {
- return PHPExcel_Calculation_Functions::VALUE();
- }
- if (is_bool($value)) {
- $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (function_exists('mb_substr')) {
- return mb_substr($value, --$start, $chars, 'UTF-8');
- } else {
- return substr($value, --$start, $chars);
- }
- }
-
- public static function RIGHT($value = '', $chars = 1)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
- if ($chars < 0) {
- return PHPExcel_Calculation_Functions::VALUE();
- }
- if (is_bool($value)) {
- $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if ((function_exists('mb_substr')) && (function_exists('mb_strlen'))) {
- return mb_substr($value, mb_strlen($value, 'UTF-8') - $chars, $chars, 'UTF-8');
- } else {
- return substr($value, strlen($value) - $chars);
- }
- }
-
- public static function STRINGLENGTH($value = '')
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- if (is_bool($value)) {
- $value = ($value) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- if (function_exists('mb_strlen')) {
- return mb_strlen($value, 'UTF-8');
- } else {
- return strlen($value);
- }
- }
-
- public static function LOWERCASE($mixedCaseString)
- {
- $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
- if (is_bool($mixedCaseString)) {
- $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- return PHPExcel_Shared_String::StrToLower($mixedCaseString);
- }
-
- public static function UPPERCASE($mixedCaseString)
- {
- $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
- if (is_bool($mixedCaseString)) {
- $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- return PHPExcel_Shared_String::StrToUpper($mixedCaseString);
- }
-
- public static function PROPERCASE($mixedCaseString)
- {
- $mixedCaseString = PHPExcel_Calculation_Functions::flattenSingleValue($mixedCaseString);
- if (is_bool($mixedCaseString)) {
- $mixedCaseString = ($mixedCaseString) ? PHPExcel_Calculation::getTRUE() : PHPExcel_Calculation::getFALSE();
- }
- return PHPExcel_Shared_String::StrToTitle($mixedCaseString);
- }
-
- public static function REPLACE($oldText = '', $start = 1, $chars = null, $newText)
- {
- $oldText = PHPExcel_Calculation_Functions::flattenSingleValue($oldText);
- $start = PHPExcel_Calculation_Functions::flattenSingleValue($start);
- $chars = PHPExcel_Calculation_Functions::flattenSingleValue($chars);
- $newText = PHPExcel_Calculation_Functions::flattenSingleValue($newText);
- $left = self::LEFT($oldText, $start-1);
- $right = self::RIGHT($oldText, self::STRINGLENGTH($oldText)-($start+$chars)+1);
- return $left.$newText.$right;
- }
-
- public static function SUBSTITUTE($text = '', $fromText = '', $toText = '', $instance = 0)
- {
- $text = PHPExcel_Calculation_Functions::flattenSingleValue($text);
- $fromText = PHPExcel_Calculation_Functions::flattenSingleValue($fromText);
- $toText = PHPExcel_Calculation_Functions::flattenSingleValue($toText);
- $instance = floor(PHPExcel_Calculation_Functions::flattenSingleValue($instance));
- if ($instance == 0) {
- if (function_exists('mb_str_replace')) {
- return mb_str_replace($fromText, $toText, $text);
- } else {
- return str_replace($fromText, $toText, $text);
- }
- } else {
- $pos = -1;
- while ($instance > 0) {
- if (function_exists('mb_strpos')) {
- $pos = mb_strpos($text, $fromText, $pos+1, 'UTF-8');
- } else {
- $pos = strpos($text, $fromText, $pos+1);
- }
- if ($pos === false) {
- break;
- }
- --$instance;
- }
- if ($pos !== false) {
- if (function_exists('mb_strlen')) {
- return self::REPLACE($text, ++$pos, mb_strlen($fromText, 'UTF-8'), $toText);
- } else {
- return self::REPLACE($text, ++$pos, strlen($fromText), $toText);
- }
- }
- }
- return $text;
- }
-
- public static function RETURNSTRING($testValue = '')
- {
- $testValue = PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
- if (is_string($testValue)) {
- return $testValue;
- }
- return null;
- }
-
- public static function TEXTFORMAT($value, $format)
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- $format = PHPExcel_Calculation_Functions::flattenSingleValue($format);
- if ((is_string($value)) && (!is_numeric($value)) && PHPExcel_Shared_Date::isDateTimeFormatCode($format)) {
- $value = PHPExcel_Calculation_DateTime::DATEVALUE($value);
- }
- return (string) PHPExcel_Style_NumberFormat::toFormattedString($value, $format);
- }
-
- public static function VALUE($value = '')
- {
- $value = PHPExcel_Calculation_Functions::flattenSingleValue($value);
- if (!is_numeric($value)) {
- $numberValue = str_replace(
- PHPExcel_Shared_String::getThousandsSeparator(),
- '',
- trim($value, " \t\n\r\0\x0B" . PHPExcel_Shared_String::getCurrencyCode())
- );
- if (is_numeric($numberValue)) {
- return (float) $numberValue;
- }
- $dateSetting = PHPExcel_Calculation_Functions::getReturnDateType();
- PHPExcel_Calculation_Functions::setReturnDateType(PHPExcel_Calculation_Functions::RETURNDATE_EXCEL);
- if (strpos($value, ':') !== false) {
- $timeValue = PHPExcel_Calculation_DateTime::TIMEVALUE($value);
- if ($timeValue !== PHPExcel_Calculation_Functions::VALUE()) {
- PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
- return $timeValue;
- }
- }
- $dateValue = PHPExcel_Calculation_DateTime::DATEVALUE($value);
- if ($dateValue !== PHPExcel_Calculation_Functions::VALUE()) {
- PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
- return $dateValue;
- }
- PHPExcel_Calculation_Functions::setReturnDateType($dateSetting);
- return PHPExcel_Calculation_Functions::VALUE();
- }
- return (float) $value;
- }
- }
|