2c052a0d0dc5ee2f3fcd05c23b37eda7f4bf1f68.svn-base 10.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288
  1. <?php
  2. /**
  3. * PHPExcel
  4. *
  5. * Copyright (c) 2006 - 2014 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 - 2014 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. /** 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_Logical
  37. *
  38. * @category PHPExcel
  39. * @package PHPExcel_Calculation
  40. * @copyright Copyright (c) 2006 - 2014 PHPExcel (http://www.codeplex.com/PHPExcel)
  41. */
  42. class PHPExcel_Calculation_Logical {
  43. /**
  44. * TRUE
  45. *
  46. * Returns the boolean TRUE.
  47. *
  48. * Excel Function:
  49. * =TRUE()
  50. *
  51. * @access public
  52. * @category Logical Functions
  53. * @return boolean True
  54. */
  55. public static function TRUE() {
  56. return TRUE;
  57. } // function TRUE()
  58. /**
  59. * FALSE
  60. *
  61. * Returns the boolean FALSE.
  62. *
  63. * Excel Function:
  64. * =FALSE()
  65. *
  66. * @access public
  67. * @category Logical Functions
  68. * @return boolean False
  69. */
  70. public static function FALSE() {
  71. return FALSE;
  72. } // function FALSE()
  73. /**
  74. * LOGICAL_AND
  75. *
  76. * Returns boolean TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.
  77. *
  78. * Excel Function:
  79. * =AND(logical1[,logical2[, ...]])
  80. *
  81. * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
  82. * or references that contain logical values.
  83. *
  84. * Boolean arguments are treated as True or False as appropriate
  85. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  86. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  87. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  88. *
  89. * @access public
  90. * @category Logical Functions
  91. * @param mixed $arg,... Data values
  92. * @return boolean The logical AND of the arguments.
  93. */
  94. public static function LOGICAL_AND() {
  95. // Return value
  96. $returnValue = TRUE;
  97. // Loop through the arguments
  98. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  99. $argCount = -1;
  100. foreach ($aArgs as $argCount => $arg) {
  101. // Is it a boolean value?
  102. if (is_bool($arg)) {
  103. $returnValue = $returnValue && $arg;
  104. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  105. $returnValue = $returnValue && ($arg != 0);
  106. } elseif (is_string($arg)) {
  107. $arg = strtoupper($arg);
  108. if (($arg == 'TRUE') || ($arg == PHPExcel_Calculation::getTRUE())) {
  109. $arg = TRUE;
  110. } elseif (($arg == 'FALSE') || ($arg == PHPExcel_Calculation::getFALSE())) {
  111. $arg = FALSE;
  112. } else {
  113. return PHPExcel_Calculation_Functions::VALUE();
  114. }
  115. $returnValue = $returnValue && ($arg != 0);
  116. }
  117. }
  118. // Return
  119. if ($argCount < 0) {
  120. return PHPExcel_Calculation_Functions::VALUE();
  121. }
  122. return $returnValue;
  123. } // function LOGICAL_AND()
  124. /**
  125. * LOGICAL_OR
  126. *
  127. * Returns boolean TRUE if any argument is TRUE; returns FALSE if all arguments are FALSE.
  128. *
  129. * Excel Function:
  130. * =OR(logical1[,logical2[, ...]])
  131. *
  132. * The arguments must evaluate to logical values such as TRUE or FALSE, or the arguments must be arrays
  133. * or references that contain logical values.
  134. *
  135. * Boolean arguments are treated as True or False as appropriate
  136. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  137. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  138. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  139. *
  140. * @access public
  141. * @category Logical Functions
  142. * @param mixed $arg,... Data values
  143. * @return boolean The logical OR of the arguments.
  144. */
  145. public static function LOGICAL_OR() {
  146. // Return value
  147. $returnValue = FALSE;
  148. // Loop through the arguments
  149. $aArgs = PHPExcel_Calculation_Functions::flattenArray(func_get_args());
  150. $argCount = -1;
  151. foreach ($aArgs as $argCount => $arg) {
  152. // Is it a boolean value?
  153. if (is_bool($arg)) {
  154. $returnValue = $returnValue || $arg;
  155. } elseif ((is_numeric($arg)) && (!is_string($arg))) {
  156. $returnValue = $returnValue || ($arg != 0);
  157. } elseif (is_string($arg)) {
  158. $arg = strtoupper($arg);
  159. if (($arg == 'TRUE') || ($arg == PHPExcel_Calculation::getTRUE())) {
  160. $arg = TRUE;
  161. } elseif (($arg == 'FALSE') || ($arg == PHPExcel_Calculation::getFALSE())) {
  162. $arg = FALSE;
  163. } else {
  164. return PHPExcel_Calculation_Functions::VALUE();
  165. }
  166. $returnValue = $returnValue || ($arg != 0);
  167. }
  168. }
  169. // Return
  170. if ($argCount < 0) {
  171. return PHPExcel_Calculation_Functions::VALUE();
  172. }
  173. return $returnValue;
  174. } // function LOGICAL_OR()
  175. /**
  176. * NOT
  177. *
  178. * Returns the boolean inverse of the argument.
  179. *
  180. * Excel Function:
  181. * =NOT(logical)
  182. *
  183. * The argument must evaluate to a logical value such as TRUE or FALSE
  184. *
  185. * Boolean arguments are treated as True or False as appropriate
  186. * Integer or floating point arguments are treated as True, except for 0 or 0.0 which are False
  187. * If any argument value is a string, or a Null, the function returns a #VALUE! error, unless the string holds
  188. * the value TRUE or FALSE, in which case it is evaluated as the corresponding boolean value
  189. *
  190. * @access public
  191. * @category Logical Functions
  192. * @param mixed $logical A value or expression that can be evaluated to TRUE or FALSE
  193. * @return boolean The boolean inverse of the argument.
  194. */
  195. public static function NOT($logical=FALSE) {
  196. $logical = PHPExcel_Calculation_Functions::flattenSingleValue($logical);
  197. if (is_string($logical)) {
  198. $logical = strtoupper($logical);
  199. if (($logical == 'TRUE') || ($logical == PHPExcel_Calculation::getTRUE())) {
  200. return FALSE;
  201. } elseif (($logical == 'FALSE') || ($logical == PHPExcel_Calculation::getFALSE())) {
  202. return TRUE;
  203. } else {
  204. return PHPExcel_Calculation_Functions::VALUE();
  205. }
  206. }
  207. return !$logical;
  208. } // function NOT()
  209. /**
  210. * STATEMENT_IF
  211. *
  212. * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.
  213. *
  214. * Excel Function:
  215. * =IF(condition[,returnIfTrue[,returnIfFalse]])
  216. *
  217. * Condition is any value or expression that can be evaluated to TRUE or FALSE.
  218. * For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100,
  219. * the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.
  220. * This argument can use any comparison calculation operator.
  221. * ReturnIfTrue is the value that is returned if condition evaluates to TRUE.
  222. * For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE,
  223. * then the IF function returns the text "Within budget"
  224. * If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use
  225. * the logical value TRUE for this argument.
  226. * ReturnIfTrue can be another formula.
  227. * ReturnIfFalse is the value that is returned if condition evaluates to FALSE.
  228. * For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE,
  229. * then the IF function returns the text "Over budget".
  230. * If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned.
  231. * If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned.
  232. * ReturnIfFalse can be another formula.
  233. *
  234. * @access public
  235. * @category Logical Functions
  236. * @param mixed $condition Condition to evaluate
  237. * @param mixed $returnIfTrue Value to return when condition is true
  238. * @param mixed $returnIfFalse Optional value to return when condition is false
  239. * @return mixed The value of returnIfTrue or returnIfFalse determined by condition
  240. */
  241. public static function STATEMENT_IF($condition = TRUE, $returnIfTrue = 0, $returnIfFalse = FALSE) {
  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. } // function STATEMENT_IF()
  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. $testValue = (is_null($testValue)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($testValue);
  261. $errorpart = (is_null($errorpart)) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($errorpart);
  262. return self::STATEMENT_IF(PHPExcel_Calculation_Functions::IS_ERROR($testValue), $errorpart, $testValue);
  263. } // function IFERROR()
  264. } // class PHPExcel_Calculation_Logical