Logical.php 11 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285
  1. <?php
  2. /** PHPExcel root directory */
  3. if (!defined('PHPEXCEL_ROOT')) {
  4. /**
  5. * @ignore
  6. */
  7. define('PHPEXCEL_ROOT', dirname(__FILE__) . '/../../');
  8. require(PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');
  9. }
  10. /**
  11. * PHPExcel_Calculation_Logical
  12. *
  13. * Copyright (c) 2006 - 2015 PHPExcel
  14. *
  15. * This library is free software; you can redistribute it and/or
  16. * modify it under the terms of the GNU Lesser General Public
  17. * License as published by the Free Software Foundation; either
  18. * version 2.1 of the License, or (at your option) any later version.
  19. *
  20. * This library is distributed in the hope that it will be useful,
  21. * but WITHOUT ANY WARRANTY; without even the implied warranty of
  22. * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  23. * Lesser General Public License for more details.
  24. *
  25. * You should have received a copy of the GNU Lesser General Public
  26. * License along with this library; if not, write to the Free Software
  27. * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
  28. *
  29. * @category PHPExcel
  30. * @package PHPExcel_Calculation
  31. * @copyright Copyright (c) 2006 - 2015 PHPExcel (http://www.codeplex.com/PHPExcel)
  32. * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL
  33. * @version ##VERSION##, ##DATE##
  34. */
  35. class PHPExcel_Calculation_Logical
  36. {
  37. /**
  38. * TRUE
  39. *
  40. * Returns the boolean TRUE.
  41. *
  42. * Excel Function:
  43. * =TRUE()
  44. *
  45. * @access public
  46. * @category Logical Functions
  47. * @return boolean True
  48. */
  49. public static function TRUE()
  50. {
  51. return true;
  52. }
  53. /**
  54. * FALSE
  55. *
  56. * Returns the boolean FALSE.
  57. *
  58. * Excel Function:
  59. * =FALSE()
  60. *
  61. * @access public
  62. * @category Logical Functions
  63. * @return boolean False
  64. */
  65. public static function FALSE()
  66. {
  67. return false;
  68. }
  69. /**
  70. * LOGICAL_AND
  71. *
  72. * Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
  73. *
  74. * Excel Function:
  75. * =AND(logical1[,logical2[, ...]])
  76. *
  77. * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
  78. * or references that contain logical values.
  79. *
  80. * Boolean arguments are treated as True or False as appropriate
  81. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  82. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  83. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  84. *
  85. * @access public
  86. * @category Logical Functions
  87. * @param mixed $arg,... Data values
  88. * @return boolean The logical AND of the arguments.
  89. */
  90. public static function LOGICAL_AND()
  91. {
  92. // Return value
  93. $returnValue = true;
  94. // Loop through the arguments
  95. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  96. $argCount = -1;
  97. foreach ($aArgs as $argCount => $arg) {
  98. // Is it a boolean value?
  99. if (is_bool($arg)) {
  100. $returnValue = $returnValue && $arg;
  101. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  102. $returnValue = $returnValue && ($arg != 0);
  103. } elseif (is_string($arg)) {
  104. $arg = strtoupper($arg);
  105. if (($arg == 'TRUE') || ($arg == PHPExcel_Calculation::getTRUE())) {
  106. $arg = true;
  107. } elseif (($arg == 'FALSE') || ($arg == PHPExcel_Calculation::getFALSE())) {
  108. $arg = false;
  109. } else {
  110. return PHPExcel_Calculation_Functions::VALUE();
  111. }
  112. $returnValue = $returnValue && ($arg != 0);
  113. }
  114. }
  115. // Return
  116. if ($argCount < 0) {
  117. return PHPExcel_Calculation_Functions::VALUE();
  118. }
  119. return $returnValue;
  120. }
  121. /**
  122. * LOGICAL_OR
  123. *
  124. * Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
  125. *
  126. * Excel Function:
  127. * =OR(logical1[,logical2[, ...]])
  128. *
  129. * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
  130. * or references that contain logical values.
  131. *
  132. * Boolean arguments are treated as True or False as appropriate
  133. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  134. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  135. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  136. *
  137. * @access public
  138. * @category Logical Functions
  139. * @param mixed $arg,... Data values
  140. * @return boolean The logical OR of the arguments.
  141. */
  142. public static function LOGICAL_OR()
  143. {
  144. // Return value
  145. $returnValue = false;
  146. // Loop through the arguments
  147. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  148. $argCount = -1;
  149. foreach ($aArgs as $argCount => $arg) {
  150. // Is it a boolean value?
  151. if (is_bool($arg)) {
  152. $returnValue = $returnValue || $arg;
  153. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  154. $returnValue = $returnValue || ($arg != 0);
  155. } elseif (is_string($arg)) {
  156. $arg = strtoupper($arg);
  157. if (($arg == 'TRUE') || ($arg == PHPExcel_Calculation::getTRUE())) {
  158. $arg = true;
  159. } elseif (($arg == 'FALSE') || ($arg == PHPExcel_Calculation::getFALSE())) {
  160. $arg = false;
  161. } else {
  162. return PHPExcel_Calculation_Functions::VALUE();
  163. }
  164. $returnValue = $returnValue || ($arg != 0);
  165. }
  166. }
  167. // Return
  168. if ($argCount < 0) {
  169. return PHPExcel_Calculation_Functions::VALUE();
  170. }
  171. return $returnValue;
  172. }
  173. /**
  174. * NOT
  175. *
  176. * Returns the boolean inverse of the argument.
  177. *
  178. * Excel Function:
  179. * =NOT(logical)
  180. *
  181. * The argument must evaluate to a logical value such as TRUE or FALSE
  182. *
  183. * Boolean arguments are treated as True or False as appropriate
  184. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  185. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  186. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  187. *
  188. * @access public
  189. * @category Logical Functions
  190. * @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE
  191. * @return boolean The boolean inverse of the argument.
  192. */
  193. public static function NOT($logical = false)
  194. {
  195. $logical = PHPExcel_Calculation_Functions::flattenSingleValue($logical);
  196. if (is_string($logical)) {
  197. $logical = strtoupper($logical);
  198. if (($logical == 'TRUE') || ($logical == PHPExcel_Calculation::getTRUE())) {
  199. return false;
  200. } elseif (($logical == 'FALSE') || ($logical == PHPExcel_Calculation::getFALSE())) {
  201. return true;
  202. } else {
  203. return PHPExcel_Calculation_Functions::VALUE();
  204. }
  205. }
  206. return !$logical;
  207. }
  208. /**
  209. * STATEMENT_IF
  210. *
  211. * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
  212. *
  213. * Excel Function:
  214. * =IF(condition[,returnIfTrue[,returnIfFalse]])
  215. *
  216. * Condition is any value or expression that can be evaluated to TRUE or FALSE.
  217. * For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
  218. * the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
  219. * This argument can use any comparison calculation operator.
  220. * ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
  221. * For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE,
  222. * then the IF function returns the text "Within budget"
  223. * If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use
  224. * the logical value TRUE for this argument.
  225. * ReturnIfTrue can be another formula.
  226. * ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
  227. * For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE,
  228. * then the IF function returns the text "Over budget".
  229. * If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
  230. * If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
  231. * ReturnIfFalse can be another formula.
  232. *
  233. * @access public
  234. * @category Logical Functions
  235. * @param mixed $condition Condition to evaluate
  236. * @param mixed $returnIfTrue Value to return when condition is true
  237. * @param mixed $returnIfFalse Optional value to return when condition is false
  238. * @return mixed The value of returnIfTrue or returnIfFalse determined by condition
  239. */
  240. public static function STATEMENT_IF($condition = true, $returnIfTrue = 0, $returnIfFalse = false)
  241. {
  242. $condition = (is_null($condition)) ? true : (boolean) PHPExcel_Calculation_Functions::flattenSingleValue($condition);
  243. $returnIfTrue = (is_null($returnIfTrue)) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfTrue);
  244. $returnIfFalse = (is_null($returnIfFalse)) ? false : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfFalse);
  245. return ($condition) ? $returnIfTrue : $returnIfFalse;
  246. }
  247. /**
  248. * IFERROR
  249. *
  250. * Excel Function:
  251. * =IFERROR(testValue,errorpart)
  252. *
  253. * @access public
  254. * @category Logical Functions
  255. * @param mixed $testValue Value to check, is also the value returned when no error
  256. * @param mixed $errorpart Value to return when testValue is an error condition
  257. * @return mixed The value of errorpart or testValue determined by error condition
  258. */
  259. public static function IFERROR($testValue = '', $errorpart = '')
  260. {
  261. $testValue = (is_null($testValue)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
  262. $errorpart = (is_null($errorpart)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($errorpart);
  263. return self::STATEMENT_IF(PHPExcel_Calculation_Functions::IS_ERROR($testValue), $errorpart, $testValue);
  264. }
  265. }