/** * __fieldExtract * * Extracts the column ID to use for the data field. * * @access private * @param mixed[] $database The range of cells that makes up the list or database. * A database is a list of related data in which rows of related * information are records, and columns of data are fields. The * first row of the list contains labels for each column. * @param mixed $field Indicates which column is used in the function. Enter the * column label enclosed between double quotation marks, such as * "Age" or "Yield," or a number (without quotation marks) that * represents the position of the column within the list: 1 for * the first column, 2 for the second column, and so on. * @return string|NULL * */ private static function __fieldExtract($database, $field) { $field = strtoupper(PHPExcel_Calculation_Functions::flattenSingleValue($field)); $fieldNames = array_map('strtoupper', array_shift($database)); if (is_numeric($field)) { $keys = array_keys($fieldNames); return $keys[$field - 1]; } $key = array_search($field, $fieldNames); return $key ? $key : NULL; }
/** * STATEMENT_IF * * Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE. * * Excel Function: * =IF(condition[,returnIfTrue[,returnIfFalse]]) * * Condition is any value or expression that can be evaluated to TRUE or FALSE. * For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, * the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE. * This argument can use any comparison calculation operator. * ReturnIfTrue is the value that is returned if condition evaluates to TRUE. * For example, if this argument is the text string "Within budget" and the condition argument evaluates to TRUE, * then the IF function returns the text "Within budget" * If condition is TRUE and ReturnIfTrue is blank, this argument returns 0 (zero). To display the word TRUE, use * the logical value TRUE for this argument. * ReturnIfTrue can be another formula. * ReturnIfFalse is the value that is returned if condition evaluates to FALSE. * For example, if this argument is the text string "Over budget" and the condition argument evaluates to FALSE, * then the IF function returns the text "Over budget". * If condition is FALSE and ReturnIfFalse is omitted, then the logical value FALSE is returned. * If condition is FALSE and ReturnIfFalse is blank, then the value 0 (zero) is returned. * ReturnIfFalse can be another formula. * * @access public * @category Logical Functions * * @param mixed $condition Condition to evaluate * @param mixed $returnIfTrue Value to return when condition is true * @param mixed $returnIfFalse Optional value to return when condition is false * * @return mixed The value of returnIfTrue or returnIfFalse determined by condition */ public static function STATEMENT_IF($condition = true, $returnIfTrue = 0, $returnIfFalse = false) { $condition = is_null($condition) ? true : (bool) PHPExcel_Calculation_Functions::flattenSingleValue($condition); $returnIfTrue = is_null($returnIfTrue) ? 0 : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfTrue); $returnIfFalse = is_null($returnIfFalse) ? false : PHPExcel_Calculation_Functions::flattenSingleValue($returnIfFalse); return $condition ? $returnIfTrue : $returnIfFalse; }
private function _processTokenStack($tokens, $cellID = NULL, PHPExcel_Cell $pCell = NULL) { if ($tokens == FALSE) { return FALSE; } // If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent cell collection), // so we store the parent cell collection so that we can re-attach it when necessary $pCellWorksheet = $pCell !== NULL ? $pCell->getWorksheet() : NULL; $pCellParent = $pCell !== NULL ? $pCell->getParent() : null; $stack = new PHPExcel_Calculation_Token_Stack(); // Loop through each token in turn foreach ($tokens as $tokenData) { // print_r($tokenData); // echo '<br />'; $token = $tokenData['value']; // echo '<b>Token is '.$token.'</b><br />'; // if the token is a binary operator, pop the top two values off the stack, do the operation, and push the result back on the stack if (isset(self::$_binaryOperators[$token])) { // echo 'Token is a binary operator<br />'; // We must have two operands, error if we don't if (($operand2Data = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } if (($operand1Data = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } $operand1 = self::_dataTestReference($operand1Data); $operand2 = self::_dataTestReference($operand2Data); // Log what we're doing if ($token == ':') { $this->_debugLog->writeDebugLog('Evaluating Range ', $this->_showValue($operand1Data['reference']), ' ', $token, ' ', $this->_showValue($operand2Data['reference'])); } else { $this->_debugLog->writeDebugLog('Evaluating ', $this->_showValue($operand1), ' ', $token, ' ', $this->_showValue($operand2)); } // Process the operation in the appropriate manner switch ($token) { // Comparison (Boolean) Operators case '>': // Greater than // Greater than case '<': // Less than // Less than case '>=': // Greater than or Equal to // Greater than or Equal to case '<=': // Less than or Equal to // Less than or Equal to case '=': // Equality // Equality case '<>': // Inequality $this->_executeBinaryComparisonOperation($cellID, $operand1, $operand2, $token, $stack); break; // Binary Operators // Binary Operators case ':': // Range $sheet1 = $sheet2 = ''; if (strpos($operand1Data['reference'], '!') !== FALSE) { list($sheet1, $operand1Data['reference']) = explode('!', $operand1Data['reference']); } else { $sheet1 = $pCellParent !== NULL ? $pCellWorksheet->getTitle() : ''; } if (strpos($operand2Data['reference'], '!') !== FALSE) { list($sheet2, $operand2Data['reference']) = explode('!', $operand2Data['reference']); } else { $sheet2 = $sheet1; } if ($sheet1 == $sheet2) { if ($operand1Data['reference'] === NULL) { if (trim($operand1Data['value']) != '' && is_numeric($operand1Data['value'])) { $operand1Data['reference'] = $pCell->getColumn() . $operand1Data['value']; } elseif (trim($operand1Data['reference']) == '') { $operand1Data['reference'] = $pCell->getCoordinate(); } else { $operand1Data['reference'] = $operand1Data['value'] . $pCell->getRow(); } } if ($operand2Data['reference'] === NULL) { if (trim($operand2Data['value']) != '' && is_numeric($operand2Data['value'])) { $operand2Data['reference'] = $pCell->getColumn() . $operand2Data['value']; } elseif (trim($operand2Data['reference']) == '') { $operand2Data['reference'] = $pCell->getCoordinate(); } else { $operand2Data['reference'] = $operand2Data['value'] . $pCell->getRow(); } } $oData = array_merge(explode(':', $operand1Data['reference']), explode(':', $operand2Data['reference'])); $oCol = $oRow = array(); foreach ($oData as $oDatum) { $oCR = PHPExcel_Cell::coordinateFromString($oDatum); $oCol[] = PHPExcel_Cell::columnIndexFromString($oCR[0]) - 1; $oRow[] = $oCR[1]; } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($sheet1), FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $stack->push('Cell Reference', $cellValue, $cellRef); } else { $stack->push('Error', PHPExcel_Calculation_Functions::REF(), NULL); } break; case '+': // Addition $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'plusEquals', $stack); break; case '-': // Subtraction $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'minusEquals', $stack); break; case '*': // Multiplication $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayTimesEquals', $stack); break; case '/': // Division $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'arrayRightDivide', $stack); break; case '^': // Exponential $this->_executeNumericBinaryOperation($cellID, $operand1, $operand2, $token, 'power', $stack); break; case '&': // Concatenation // If either of the operands is a matrix, we need to treat them both as matrices // (converting the other operand to a matrix if need be); then perform the required // matrix operation if (is_bool($operand1)) { $operand1 = $operand1 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE']; } if (is_bool($operand2)) { $operand2 = $operand2 ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE']; } if (is_array($operand1) || is_array($operand2)) { // Ensure that both operands are arrays/matrices self::_checkMatrixOperands($operand1, $operand2, 2); try { // Convert operand 1 from a PHP array to a matrix $matrix = new PHPExcel_Shared_JAMA_Matrix($operand1); // Perform the required operation against the operand 1 matrix, passing in operand 2 $matrixResult = $matrix->concat($operand2); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage()); $result = '#VALUE!'; } } else { $result = '"' . str_replace('""', '"', self::_unwrapResult($operand1, '"') . self::_unwrapResult($operand2, '"')) . '"'; } $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result)); $stack->push('Value', $result); break; case '|': // Intersect $rowIntersect = array_intersect_key($operand1, $operand2); $cellIntersect = $oCol = $oRow = array(); foreach (array_keys($rowIntersect) as $row) { $oRow[] = $row; foreach ($rowIntersect[$row] as $col => $data) { $oCol[] = PHPExcel_Cell::columnIndexFromString($col) - 1; $cellIntersect[$row] = array_intersect_key($operand1[$row], $operand2[$row]); } } $cellRef = PHPExcel_Cell::stringFromColumnIndex(min($oCol)) . min($oRow) . ':' . PHPExcel_Cell::stringFromColumnIndex(max($oCol)) . max($oRow); $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($cellIntersect)); $stack->push('Value', $cellIntersect, $cellRef); break; } // if the token is a unary operator, pop one value off the stack, do the operation, and push it back on } elseif ($token === '~' || $token === '%') { // echo 'Token is a unary operator<br />'; if (($arg = $stack->pop()) === NULL) { return $this->_raiseFormulaError('Internal error - Operand value missing from stack'); } $arg = $arg['value']; if ($token === '~') { // echo 'Token is a negation operator<br />'; $this->_debugLog->writeDebugLog('Evaluating Negation of ', $this->_showValue($arg)); $multiplier = -1; } else { // echo 'Token is a percentile operator<br />'; $this->_debugLog->writeDebugLog('Evaluating Percentile of ', $this->_showValue($arg)); $multiplier = 0.01; } if (is_array($arg)) { self::_checkMatrixOperands($arg, $multiplier, 2); try { $matrix1 = new PHPExcel_Shared_JAMA_Matrix($arg); $matrixResult = $matrix1->arrayTimesEquals($multiplier); $result = $matrixResult->getArray(); } catch (PHPExcel_Exception $ex) { $this->_debugLog->writeDebugLog('JAMA Matrix Exception: ', $ex->getMessage()); $result = '#VALUE!'; } $this->_debugLog->writeDebugLog('Evaluation Result is ', $this->_showTypeDetails($result)); $stack->push('Value', $result); } else { $this->_executeNumericBinaryOperation($cellID, $multiplier, $arg, '*', 'arrayTimesEquals', $stack); } } elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) { $cellRef = NULL; // echo 'Element '.$token.' is a Cell reference<br />'; if (isset($matches[8])) { // echo 'Reference is a Range of cells<br />'; if ($pCell === NULL) { // We can't access the range, so return a REF error $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7] . ':' . $matches[9] . $matches[10]; if ($matches[2] > '') { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) { // It's a Reference to an external workbook (not currently supported) return $this->_raiseFormulaError('Unable to access External Workbook'); } $matches[2] = trim($matches[2], "\"'"); // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in worksheet ', $matches[2]); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue)); // $cellRef = $matches[2].'!'.$cellRef; } else { // echo '$cellRef='.$cellRef.' in current worksheet<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell Range ', $cellRef, ' in current worksheet'); if ($pCellParent !== NULL) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE); } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cells ', $cellRef, ' is ', $this->_showTypeDetails($cellValue)); } } } else { // echo 'Reference is a single Cell<br />'; if ($pCell === NULL) { // We can't access the cell, so return a REF error $cellValue = PHPExcel_Calculation_Functions::REF(); } else { $cellRef = $matches[6] . $matches[7]; if ($matches[2] > '') { $matches[2] = trim($matches[2], "\"'"); if (strpos($matches[2], '[') !== FALSE || strpos($matches[2], ']') !== FALSE) { // It's a Reference to an external workbook (not currently supported) return $this->_raiseFormulaError('Unable to access External Workbook'); } // echo '$cellRef='.$cellRef.' in worksheet '.$matches[2].'<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in worksheet ', $matches[2]); if ($pCellParent !== NULL) { $cellSheet = $this->_workbook->getSheetByName($matches[2]); if ($cellSheet && $cellSheet->cellExists($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $this->_workbook->getSheetByName($matches[2]), FALSE); $pCell->attach($pCellParent); } else { $cellValue = NULL; } } else { return $this->_raiseFormulaError('Unable to access Cell Reference'); } $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' in worksheet ', $matches[2], ' is ', $this->_showTypeDetails($cellValue)); // $cellRef = $matches[2].'!'.$cellRef; } else { // echo '$cellRef='.$cellRef.' in current worksheet<br />'; $this->_debugLog->writeDebugLog('Evaluating Cell ', $cellRef, ' in current worksheet'); if ($pCellParent->isDataSet($cellRef)) { $cellValue = $this->extractCellRange($cellRef, $pCellWorksheet, FALSE); $pCell->attach($pCellParent); } else { $cellValue = NULL; } $this->_debugLog->writeDebugLog('Evaluation Result for cell ', $cellRef, ' is ', $this->_showTypeDetails($cellValue)); } } } $stack->push('Value', $cellValue, $cellRef); // if the token is a function, pop arguments off the stack, hand them to the function, and push the result back on } elseif (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $token, $matches)) { // echo 'Token is a function<br />'; $functionName = $matches[1]; $argCount = $stack->pop(); $argCount = $argCount['value']; if ($functionName != 'MKMATRIX') { $this->_debugLog->writeDebugLog('Evaluating Function ', self::_localeFunc($functionName), '() with ', $argCount == 0 ? 'no' : $argCount, ' argument', $argCount == 1 ? '' : 's'); } if (isset(self::$_PHPExcelFunctions[$functionName]) || isset(self::$_controlFunctions[$functionName])) { // function if (isset(self::$_PHPExcelFunctions[$functionName])) { $functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall']; $passByReference = isset(self::$_PHPExcelFunctions[$functionName]['passByReference']); $passCellReference = isset(self::$_PHPExcelFunctions[$functionName]['passCellReference']); } elseif (isset(self::$_controlFunctions[$functionName])) { $functionCall = self::$_controlFunctions[$functionName]['functionCall']; $passByReference = isset(self::$_controlFunctions[$functionName]['passByReference']); $passCellReference = isset(self::$_controlFunctions[$functionName]['passCellReference']); } // get the arguments for this function // echo 'Function '.$functionName.' expects '.$argCount.' arguments<br />'; $args = $argArrayVals = array(); for ($i = 0; $i < $argCount; ++$i) { $arg = $stack->pop(); $a = $argCount - $i - 1; if ($passByReference && isset(self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) && self::$_PHPExcelFunctions[$functionName]['passByReference'][$a]) { if ($arg['reference'] === NULL) { $args[] = $cellID; if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($cellID); } } else { $args[] = $arg['reference']; if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['reference']); } } } else { $args[] = self::_unwrapResult($arg['value']); if ($functionName != 'MKMATRIX') { $argArrayVals[] = $this->_showValue($arg['value']); } } } // Reverse the order of the arguments krsort($args); if ($passByReference && $argCount == 0) { $args[] = $cellID; $argArrayVals[] = $this->_showValue($cellID); } // echo 'Arguments are: '; // print_r($args); // echo '<br />'; if ($functionName != 'MKMATRIX') { if ($this->_debugLog->getWriteDebugLog()) { krsort($argArrayVals); $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', implode(self::$_localeArgumentSeparator . ' ', PHPExcel_Calculation_Functions::flattenArray($argArrayVals)), ' )'); } } // Process each argument in turn, building the return value as an array // if (($argCount == 1) && (is_array($args[1])) && ($functionName != 'MKMATRIX')) { // $operand1 = $args[1]; // $this->_debugLog->writeDebugLog('Argument is a matrix: ', $this->_showValue($operand1)); // $result = array(); // $row = 0; // foreach($operand1 as $args) { // if (is_array($args)) { // foreach($args as $arg) { // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($arg), ' )'); // $r = call_user_func_array($functionCall,$arg); // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r)); // $result[$row][] = $r; // } // ++$row; // } else { // $this->_debugLog->writeDebugLog('Evaluating ', self::_localeFunc($functionName), '( ', $this->_showValue($args), ' )'); // $r = call_user_func_array($functionCall,$args); // $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($r)); // $result[] = $r; // } // } // } else { // Process the argument with the appropriate function call if ($passCellReference) { $args[] = $pCell; } if (strpos($functionCall, '::') !== FALSE) { $result = call_user_func_array(explode('::', $functionCall), $args); } else { foreach ($args as &$arg) { $arg = PHPExcel_Calculation_Functions::flattenSingleValue($arg); } unset($arg); $result = call_user_func_array($functionCall, $args); } // } if ($functionName != 'MKMATRIX') { $this->_debugLog->writeDebugLog('Evaluation Result for ', self::_localeFunc($functionName), '() function call is ', $this->_showTypeDetails($result)); } $stack->push('Value', self::_wrapResult($result)); } } else { // if the token is a number, boolean, string or an Excel error, push it onto the stack if (isset(self::$_ExcelConstants[strtoupper($token)])) { $excelConstant = strtoupper($token); // echo 'Token is a PHPExcel constant: '.$excelConstant.'<br />'; $stack->push('Constant Value', self::$_ExcelConstants[$excelConstant]); $this->_debugLog->writeDebugLog('Evaluating Constant ', $excelConstant, ' as ', $this->_showTypeDetails(self::$_ExcelConstants[$excelConstant])); } elseif (is_numeric($token) || $token === NULL || is_bool($token) || $token == '' || $token[0] == '"' || $token[0] == '#') { // echo 'Token is a number, boolean, string, null or an Excel error<br />'; $stack->push('Value', $token); // if the token is a named range, push the named range name onto the stack } elseif (preg_match('/^' . self::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $token, $matches)) { // echo 'Token is a named range<br />'; $namedRange = $matches[6]; // echo 'Named Range is '.$namedRange.'<br />'; $this->_debugLog->writeDebugLog('Evaluating Named Range ', $namedRange); $cellValue = $this->extractNamedRange($namedRange, NULL !== $pCell ? $pCellWorksheet : NULL, FALSE); $pCell->attach($pCellParent); $this->_debugLog->writeDebugLog('Evaluation Result for named range ', $namedRange, ' is ', $this->_showTypeDetails($cellValue)); $stack->push('Named Range', $cellValue, $namedRange); } else { return $this->_raiseFormulaError("undefined variable '{$token}'"); } } } // when we're out of tokens, the stack should have a single element, the final result if ($stack->count() != 1) { return $this->_raiseFormulaError("internal error"); } $output = $stack->pop(); $output = $output['value']; // if ((is_array($output)) && (self::$returnArrayAsType != self::RETURN_ARRAY_AS_ARRAY)) { // return array_shift(PHPExcel_Calculation_Functions::flattenArray($output)); // } return $output; }
/** * IFERROR * * Excel Function: * =IFERROR(testValue,errorpart) * * @access public * @category Logical Functions * @param mixed $testValue Value to check, is also the value returned when no error * @param mixed $errorpart Value to return when testValue is an error condition * @return mixed The value of errorpart or testValue determined by error condition */ public static function IFERROR($testValue = '', $errorpart = '') { $testValue = is_null($testValue) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($testValue); $errorpart = is_null($errorpart) ? '' : PHPExcel_Calculation_Functions::flattenSingleValue($errorpart); return self::STATEMENT_IF(PHPExcel_Calculation_Functions::IS_ERROR($testValue), $errorpart, $testValue); }
/** * LOOKUP * The LOOKUP function searches for value either from a one-row or one-column range or from an array. * @param lookup_value The value that you want to match in lookup_array * @param lookup_vector The range of cells being searched * @param result_vector The column from which the matching value must be returned * @return mixed The value of the found cell */ public static function LOOKUP($lookup_value, $lookup_vector, $result_vector = null) { $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value); if (!is_array($lookup_vector)) { return PHPExcel_Calculation_Functions::NA(); } $lookupRows = count($lookup_vector); $l = array_keys($lookup_vector); $l = array_shift($l); $lookupColumns = count($lookup_vector[$l]); if ($lookupRows == 1 && $lookupColumns > 1 || $lookupRows == 2 && $lookupColumns != 2) { $lookup_vector = self::TRANSPOSE($lookup_vector); $lookupRows = count($lookup_vector); $l = array_keys($lookup_vector); $lookupColumns = count($lookup_vector[array_shift($l)]); } if (is_null($result_vector)) { $result_vector = $lookup_vector; } $resultRows = count($result_vector); $l = array_keys($result_vector); $l = array_shift($l); $resultColumns = count($result_vector[$l]); if ($resultRows == 1 && $resultColumns > 1 || $resultRows == 2 && $resultColumns != 2) { $result_vector = self::TRANSPOSE($result_vector); $resultRows = count($result_vector); $r = array_keys($result_vector); $resultColumns = count($result_vector[array_shift($r)]); } if ($lookupRows == 2) { $result_vector = array_pop($lookup_vector); $lookup_vector = array_shift($lookup_vector); } if ($lookupColumns != 2) { foreach ($lookup_vector as &$value) { if (is_array($value)) { $k = array_keys($value); $key1 = $key2 = array_shift($k); $key2++; $dataValue1 = $value[$key1]; } else { $key1 = 0; $key2 = 1; $dataValue1 = $value; } $dataValue2 = array_shift($result_vector); if (is_array($dataValue2)) { $dataValue2 = array_shift($dataValue2); } $value = array($key1 => $dataValue1, $key2 => $dataValue2); } unset($value); } return self::VLOOKUP($lookup_value, $lookup_vector, 2); }
/** * EOMONTH * * Returns the serial number for the last day of the month that is the indicated number of months before or after start_date. * Use EOMONTH to calculate maturity dates or due dates that fall on the last day of the month. * * @param long $dateValue Excel date serial value or a standard date string * @param int $adjustmentMonths Number of months to adjust by * @return long Excel date serial value */ public static function EOMONTH($dateValue = 1, $adjustmentMonths = 0) { $dateValue = PHPExcel_Calculation_Functions::flattenSingleValue($dateValue); $adjustmentMonths = floor(PHPExcel_Calculation_Functions::flattenSingleValue($adjustmentMonths)); if (!is_numeric($adjustmentMonths)) { return PHPExcel_Calculation_Functions::VALUE(); } if (is_string($dateValue = self::_getDateValue($dateValue))) { return PHPExcel_Calculation_Functions::VALUE(); } // Execute function $PHPDateObject = self::_adjustDateByMonths($dateValue, $adjustmentMonths + 1); $adjustDays = (int) $PHPDateObject->format('d'); $adjustDaysString = '-' . $adjustDays . ' days'; $PHPDateObject->modify($adjustDaysString); switch (PHPExcel_Calculation_Functions::getReturnDateType()) { case PHPExcel_Calculation_Functions::RETURNDATE_EXCEL: return (double) PHPExcel_Shared_Date::PHPToExcel($PHPDateObject); break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_NUMERIC: return (int) PHPExcel_Shared_Date::ExcelToPHP(PHPExcel_Shared_Date::PHPToExcel($PHPDateObject)); break; case PHPExcel_Calculation_Functions::RETURNDATE_PHP_OBJECT: return $PHPDateObject; break; } }
public static function _ifCondition($condition) { $condition = PHPExcel_Calculation_Functions::flattenSingleValue($condition); if (!in_array($condition[0], array('>', '<', '='))) { if (!is_numeric($condition)) { $condition = PHPExcel_Calculation::_wrapResult(strtoupper($condition)); } return '=' . $condition; } else { preg_match('/([<>=]+)(.*)/', $condition, $matches); list(, $operator, $operand) = $matches; if (!is_numeric($operand)) { $operand = PHPExcel_Calculation::_wrapResult(strtoupper($operand)); } return $operator . $operand; } }
/** * Format details of an operand for display in the log (based on operand type) * * @param mixed $value First matrix operand * * @return mixed */ private function _showValue($value) { if ($this->_debugLog->getWriteDebugLog()) { $testArray = PHPExcel_Calculation_Functions::flattenArray($value); if (count($testArray) == 1) { $value = array_pop($testArray); } if (is_array($value)) { $returnMatrix = array(); $pad = $rpad = ', '; foreach ($value as $row) { if (is_array($row)) { $returnMatrix[] = implode($pad, array_map(array($this, '_showValue'), $row)); $rpad = '; '; } else { $returnMatrix[] = $this->_showValue($row); } } return '{ ' . implode($rpad, $returnMatrix) . ' }'; } elseif (is_string($value) && trim($value, '"') == $value) { return '"' . $value . '"'; } elseif (is_bool($value)) { return $value ? self::$_localeBoolean['TRUE'] : self::$_localeBoolean['FALSE']; } } return PHPExcel_Calculation_Functions::flattenSingleValue($value); }
/** * TRUNC * * Truncates value to the number of fractional digits by number_digits. * * @param float $value * @param int $digits * @return float Truncated value */ public static function TRUNC($value = 0, $digits = 0) { $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); $digits = PHPExcel_Calculation_Functions::flattenSingleValue($digits); // Validate parameters if (!is_numeric($value) || !is_numeric($digits)) { return PHPExcel_Calculation_Functions::VALUE(); } $digits = floor($digits); // Truncate $adjust = pow(10, $digits); if ($digits > 0 && rtrim(intval((abs($value) - abs(intval($value))) * $adjust), '0') < $adjust / 10) { return $value; } return intval($value * $adjust) / $adjust; }
/** * VALUE * * @param mixed $value Value to check * @return boolean */ 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\v" . PHPExcel_Shared_String::getCurrencyCode())); if (is_numeric($numberValue)) { return (double) $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 (double) $value; }
/** * CONVERTUOM * * Converts a number from one measurement system to another. * For example, CONVERT can translate a table of distances in miles to a table of distances * in kilometers. * * Excel Function: * CONVERT(value,fromUOM,toUOM) * * @param float $value The value in fromUOM to convert. * @param string $fromUOM The units for value. * @param string $toUOM The units for the result. * * @return float */ public static function CONVERTUOM($value, $fromUOM, $toUOM) { $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue($fromUOM); $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue($toUOM); if (!is_numeric($value)) { return PHPExcel_Calculation_Functions::VALUE(); } $fromMultiplier = 1.0; if (isset(self::$_conversionUnits[$fromUOM])) { $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group']; } else { $fromMultiplier = substr($fromUOM, 0, 1); $fromUOM = substr($fromUOM, 1); if (isset(self::$_conversionMultipliers[$fromMultiplier])) { $fromMultiplier = self::$_conversionMultipliers[$fromMultiplier]['multiplier']; } else { return PHPExcel_Calculation_Functions::NA(); } if (isset(self::$_conversionUnits[$fromUOM]) && self::$_conversionUnits[$fromUOM]['AllowPrefix']) { $unitGroup1 = self::$_conversionUnits[$fromUOM]['Group']; } else { return PHPExcel_Calculation_Functions::NA(); } } $value *= $fromMultiplier; $toMultiplier = 1.0; if (isset(self::$_conversionUnits[$toUOM])) { $unitGroup2 = self::$_conversionUnits[$toUOM]['Group']; } else { $toMultiplier = substr($toUOM, 0, 1); $toUOM = substr($toUOM, 1); if (isset(self::$_conversionMultipliers[$toMultiplier])) { $toMultiplier = self::$_conversionMultipliers[$toMultiplier]['multiplier']; } else { return PHPExcel_Calculation_Functions::NA(); } if (isset(self::$_conversionUnits[$toUOM]) && self::$_conversionUnits[$toUOM]['AllowPrefix']) { $unitGroup2 = self::$_conversionUnits[$toUOM]['Group']; } else { return PHPExcel_Calculation_Functions::NA(); } } if ($unitGroup1 != $unitGroup2) { return PHPExcel_Calculation_Functions::NA(); } if ($fromUOM == $toUOM && $fromMultiplier == $toMultiplier) { // We've already factored $fromMultiplier into the value, so we need // to reverse it again return $value / $fromMultiplier; } elseif ($unitGroup1 == 'Temperature') { if ($fromUOM == 'F' || $fromUOM == 'fah') { if ($toUOM == 'F' || $toUOM == 'fah') { return $value; } else { $value = ($value - 32) / 1.8; if ($toUOM == 'K' || $toUOM == 'kel') { $value += 273.15; } return $value; } } elseif (($fromUOM == 'K' || $fromUOM == 'kel') && ($toUOM == 'K' || $toUOM == 'kel')) { return $value; } elseif (($fromUOM == 'C' || $fromUOM == 'cel') && ($toUOM == 'C' || $toUOM == 'cel')) { return $value; } if ($toUOM == 'F' || $toUOM == 'fah') { if ($fromUOM == 'K' || $fromUOM == 'kel') { $value -= 273.15; } return $value * 1.8 + 32; } if ($toUOM == 'C' || $toUOM == 'cel') { return $value - 273.15; } return $value + 273.15; } return $value * self::$_unitConversions[$unitGroup1][$fromUOM][$toUOM] / $toMultiplier; }
/** * TRUNC * * Truncates value to the number of fractional digits by number_digits. * * @param float $value * @param int $number_digits * @return float Truncated value */ public static function TRUNC($value = 0, $number_digits = 0) { $value = PHPExcel_Calculation_Functions::flattenSingleValue($value); $number_digits = PHPExcel_Calculation_Functions::flattenSingleValue($number_digits); // Validate parameters if ($number_digits < 0) { return PHPExcel_Calculation_Functions::VALUE(); } // Truncate if ($number_digits > 0) { $value = $value * pow(10, $number_digits); } $value = intval($value); if ($number_digits > 0) { $value = $value / pow(10, $number_digits); } // Return return $value; }
/** * VLOOKUP * The VLOOKUP function searches for value in the left-most column of lookup_array and returns the value in the same row based on the index_number. * * @param lookup_value The value that you want to match in lookup_array * @param lookup_array The range of cells being searched * @param index_number The column number in table_array from which the matching value must be returned. The first column is 1. * @param not_exact_match Determines if you are looking for an exact match based on lookup_value. * * @return mixed The value of the found cell */ public static function VLOOKUP($lookup_value, $lookup_array, $index_number, $not_exact_match = true) { $lookup_value = PHPExcel_Calculation_Functions::flattenSingleValue($lookup_value); $index_number = PHPExcel_Calculation_Functions::flattenSingleValue($index_number); $not_exact_match = PHPExcel_Calculation_Functions::flattenSingleValue($not_exact_match); // index_number must be greater than or equal to 1 if ($index_number < 1) { return PHPExcel_Calculation_Functions::VALUE(); } // index_number must be less than or equal to the number of columns in lookup_array if (!is_array($lookup_array) || empty($lookup_array)) { return PHPExcel_Calculation_Functions::REF(); } else { $f = array_keys($lookup_array); $firstRow = array_pop($f); if (!is_array($lookup_array[$firstRow]) || $index_number > count($lookup_array[$firstRow])) { return PHPExcel_Calculation_Functions::REF(); } else { $columnKeys = array_keys($lookup_array[$firstRow]); $returnColumn = $columnKeys[--$index_number]; $firstColumn = array_shift($columnKeys); } } if (!$not_exact_match) { uasort($lookup_array, array('self', '_vlookupSort')); } $rowNumber = $rowValue = false; foreach ($lookup_array as $rowKey => $rowData) { if (is_numeric($lookup_value) && is_numeric($rowData[$firstColumn]) && $rowData[$firstColumn] > $lookup_value || !is_numeric($lookup_value) && !is_numeric($rowData[$firstColumn]) && strtolower($rowData[$firstColumn]) > strtolower($lookup_value)) { break; } $rowNumber = $rowKey; $rowValue = $rowData[$firstColumn]; } if ($rowNumber !== false) { if (!$not_exact_match && $rowValue != $lookup_value) { // if an exact match is required, we have what we need to return an appropriate response return PHPExcel_Calculation_Functions::NA(); } else { // otherwise return the appropriate value $result = $lookup_array[$rowNumber][$returnColumn]; if (is_numeric($lookup_value) && is_numeric($result) || !is_numeric($lookup_value) && !is_numeric($result)) { return $result; } } } return PHPExcel_Calculation_Functions::NA(); }
/** * ZTEST * * Returns the Weibull distribution. Use this distribution in reliability * analysis, such as calculating a device's mean time to failure. * * @param float $value * @param float $alpha Alpha Parameter * @param float $beta Beta Parameter * @param boolean $cumulative * @return float * */ public static function ZTEST($dataSet, $m0, $sigma = null) { $dataSet = PHPExcel_Calculation_Functions::flattenArrayIndexed($dataSet); $m0 = PHPExcel_Calculation_Functions::flattenSingleValue($m0); $sigma = PHPExcel_Calculation_Functions::flattenSingleValue($sigma); if (is_null($sigma)) { $sigma = self::STDEV($dataSet); } $n = count($dataSet); return 1 - self::NORMSDIST((self::AVERAGE($dataSet) - $m0) / ($sigma / SQRT($n))); }
/** * TEXTFORMAT * * @param mixed $value Value to check * @param string $format Format mask to use * @return boolean */ 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); }
/** * YIELDMAT * * Returns the annual yield of a security that pays interest at maturity. * * @param mixed settlement The security's settlement date. * The security's settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param mixed issue The security's issue date. * @param int rate The security's interest rate at date of issue. * @param int price The security's price per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 * 1 Actual/actual * 2 Actual/360 * 3 Actual/365 * 4 European 30/360 * @return float */ public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis = 0) { $settlement = PHPExcel_Calculation_Functions::flattenSingleValue($settlement); $maturity = PHPExcel_Calculation_Functions::flattenSingleValue($maturity); $issue = PHPExcel_Calculation_Functions::flattenSingleValue($issue); $rate = PHPExcel_Calculation_Functions::flattenSingleValue($rate); $price = PHPExcel_Calculation_Functions::flattenSingleValue($price); $basis = (int) PHPExcel_Calculation_Functions::flattenSingleValue($basis); // Validate if (is_numeric($rate) && is_numeric($price)) { if ($rate <= 0 || $price <= 0) { return PHPExcel_Calculation_Functions::NaN(); } $daysPerYear = self::_daysPerYear(PHPExcel_Calculation_DateTime::YEAR($settlement), $basis); if (!is_numeric($daysPerYear)) { return $daysPerYear; } $daysBetweenIssueAndSettlement = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $settlement, $basis); if (!is_numeric($daysBetweenIssueAndSettlement)) { // return date error return $daysBetweenIssueAndSettlement; } $daysBetweenIssueAndSettlement *= $daysPerYear; $daysBetweenIssueAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($issue, $maturity, $basis); if (!is_numeric($daysBetweenIssueAndMaturity)) { // return date error return $daysBetweenIssueAndMaturity; } $daysBetweenIssueAndMaturity *= $daysPerYear; $daysBetweenSettlementAndMaturity = PHPExcel_Calculation_DateTime::YEARFRAC($settlement, $maturity, $basis); if (!is_numeric($daysBetweenSettlementAndMaturity)) { // return date error return $daysBetweenSettlementAndMaturity; } $daysBetweenSettlementAndMaturity *= $daysPerYear; return (1 + $daysBetweenIssueAndMaturity / $daysPerYear * $rate - ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate)) / ($price / 100 + $daysBetweenIssueAndSettlement / $daysPerYear * $rate) * ($daysPerYear / $daysBetweenSettlementAndMaturity); } return PHPExcel_Calculation_Functions::VALUE(); }
/** * CONVERTUOM * * @param float $value * @param string $fromUOM * @param string $toUOM * @return float */ public static function CONVERTUOM($value, $fromUOM, $toUOM) { $value = PHPExcel_Calculation_Functions::flattenSingleValue ( $value ); $fromUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $fromUOM ); $toUOM = PHPExcel_Calculation_Functions::flattenSingleValue ( $toUOM ); if (! is_numeric ( $value )) { return PHPExcel_Calculation_Functions::VALUE (); } $fromMultiplier = 1; if (isset ( self::$_conversionUnits [$fromUOM] )) { $unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group']; } else { $fromMultiplier = substr ( $fromUOM, 0, 1 ); $fromUOM = substr ( $fromUOM, 1 ); if (isset ( self::$_conversionMultipliers [$fromMultiplier] )) { $fromMultiplier = self::$_conversionMultipliers [$fromMultiplier] ['multiplier']; } else { return PHPExcel_Calculation_Functions::NA (); } if ((isset ( self::$_conversionUnits [$fromUOM] )) && (self::$_conversionUnits [$fromUOM] ['AllowPrefix'])) { $unitGroup1 = self::$_conversionUnits [$fromUOM] ['Group']; } else { return PHPExcel_Calculation_Functions::NA (); } } $value *= $fromMultiplier; $toMultiplier = 1; if (isset ( self::$_conversionUnits [$toUOM] )) { $unitGroup2 = self::$_conversionUnits [$toUOM] ['Group']; } else { $toMultiplier = substr ( $toUOM, 0, 1 ); $toUOM = substr ( $toUOM, 1 ); if (isset ( self::$_conversionMultipliers [$toMultiplier] )) { $toMultiplier = self::$_conversionMultipliers [$toMultiplier] ['multiplier']; } else { return PHPExcel_Calculation_Functions::NA (); } if ((isset ( self::$_conversionUnits [$toUOM] )) && (self::$_conversionUnits [$toUOM] ['AllowPrefix'])) { $unitGroup2 = self::$_conversionUnits [$toUOM] ['Group']; } else { return PHPExcel_Calculation_Functions::NA (); } } if ($unitGroup1 != $unitGroup2) { return PHPExcel_Calculation_Functions::NA (); } if ($fromUOM == $toUOM) { return 1.0; } elseif ($unitGroup1 == 'Temperature') { if (($fromUOM == 'F') || ($fromUOM == 'fah')) { if (($toUOM == 'F') || ($toUOM == 'fah')) { return 1.0; } else { $value = (($value - 32) / 1.8); if (($toUOM == 'K') || ($toUOM == 'kel')) { $value += 273.15; } return $value; } } elseif ((($fromUOM == 'K') || ($fromUOM == 'kel')) && (($toUOM == 'K') || ($toUOM == 'kel'))) { return 1.0; } elseif ((($fromUOM == 'C') || ($fromUOM == 'cel')) && (($toUOM == 'C') || ($toUOM == 'cel'))) { return 1.0; } if (($toUOM == 'F') || ($toUOM == 'fah')) { if (($fromUOM == 'K') || ($fromUOM == 'kel')) { $value -= 273.15; } return ($value * 1.8) + 32; } if (($toUOM == 'C') || ($toUOM == 'cel')) { return $value - 273.15; } return $value + 273.15; } return ($value * self::$_unitConversions [$unitGroup1] [$fromUOM] [$toUOM]) / $toMultiplier; } // function CONVERTUOM()