public static function write(PHPExcel_Shared_XMLWriter $objWriter, PHPExcel_Cell $cell)
{
$comments = $cell->getWorksheet()->getComments();
if (! isset($comments[$cell->getCoordinate()])) {
return;
}
$comment = $comments[$cell->getCoordinate()];
$objWriter->startElement('office:annotation');
// $objWriter->writeAttribute('draw:style-name', 'gr1');
// $objWriter->writeAttribute('draw:text-style-name', 'P1');
$objWriter->writeAttribute('svg:width', $comment->getWidth());
$objWriter->writeAttribute('svg:height', $comment->getHeight());
$objWriter->writeAttribute('svg:x', $comment->getMarginLeft());
$objWriter->writeAttribute('svg:y', $comment->getMarginTop());
// $objWriter->writeAttribute('draw:caption-point-x', $comment->getMarginLeft());
// $objWriter->writeAttribute('draw:caption-point-y', $comment->getMarginTop());
$objWriter->writeElement('dc:creator', $comment->getAuthor());
// TODO: Not realized in PHPExcel_Comment yet.
// $objWriter->writeElement('dc:date', $comment->getDate());
$objWriter->writeElement('text:p', $comment->getText()
->getPlainText());
// $objWriter->writeAttribute('draw:text-style-name', 'P1');
$objWriter->endElement();
}
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;
}
/**
* INDIRECT
*
* Returns the reference specified by a text string.
* References are immediately evaluated to display their contents.
*
* Excel Function:
* =INDIRECT(cellAddress)
*
* NOTE - INDIRECT() does not yet support the optional a1 parameter introduced in Excel 2010
*
* @param cellAddress $cellAddress The cell address of the current cell (containing this formula)
* @param PHPExcel_Cell $pCell The current cell (containing this formula)
* @return mixed The cells referenced by cellAddress
*
* @todo Support for the optional a1 parameter introduced in Excel 2010
*
*/
public static function INDIRECT($cellAddress = NULL, PHPExcel_Cell $pCell = NULL)
{
$cellAddress = PHPExcel_Calculation_Functions::flattenSingleValue($cellAddress);
if (is_null($cellAddress) || $cellAddress === '') {
return PHPExcel_Calculation_Functions::REF();
}
$cellAddress1 = $cellAddress;
$cellAddress2 = NULL;
if (strpos($cellAddress, ':') !== false) {
list($cellAddress1, $cellAddress2) = explode(':', $cellAddress);
}
if (!preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress1, $matches) || !is_null($cellAddress2) && !preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $cellAddress2, $matches)) {
if (!preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NAMEDRANGE . '$/i', $cellAddress1, $matches)) {
return PHPExcel_Calculation_Functions::REF();
}
if (strpos($cellAddress, '!') !== FALSE) {
list($sheetName, $cellAddress) = explode('!', $cellAddress);
$sheetName = trim($sheetName, "'");
$pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getWorksheet();
}
return PHPExcel_Calculation::getInstance()->extractNamedRange($cellAddress, $pSheet, FALSE);
}
if (strpos($cellAddress, '!') !== FALSE) {
list($sheetName, $cellAddress) = explode('!', $cellAddress);
$sheetName = trim($sheetName, "'");
$pSheet = $pCell->getWorksheet()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getWorksheet();
}
return PHPExcel_Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, FALSE);
}
/**
* Bind value to a cell
*
* @param PHPExcel_Cell $cell Cell to bind value to
* @param mixed $value Value to bind in cell
* @return boolean
*/
public function bindValue(PHPExcel_Cell $cell, $value = null)
{
// sanitize UTF-8 strings
if (is_string($value)) {
$value = PHPExcel_Shared_String::SanitizeUTF8($value);
}
// Find out data type
$dataType = parent::dataTypeForValue($value);
// Style logic - strings
if ($dataType === PHPExcel_Cell_DataType::TYPE_STRING && !$value instanceof PHPExcel_RichText) {
// Test for booleans using locale-setting
if ($value == PHPExcel_Calculation::getTRUE()) {
$cell->setValueExplicit(TRUE, PHPExcel_Cell_DataType::TYPE_BOOL);
return true;
} elseif ($value == PHPExcel_Calculation::getFALSE()) {
$cell->setValueExplicit(FALSE, PHPExcel_Cell_DataType::TYPE_BOOL);
return true;
}
// Check for number in scientific format
if (preg_match('/^' . PHPExcel_Calculation::CALCULATION_REGEXP_NUMBER . '$/', $value)) {
$cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
return true;
}
// Check for fraction
if (preg_match('/^([+-]?) *([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) {
// Convert value to number
$value = $matches[2] / $matches[3];
if ($matches[1] == '-') {
$value = 0 - $value;
}
$cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('??/??');
return true;
} elseif (preg_match('/^([+-]?)([0-9]*) +([0-9]*)\\s?\\/\\s*([0-9]*)$/', $value, $matches)) {
// Convert value to number
$value = $matches[2] + $matches[3] / $matches[4];
if ($matches[1] == '-') {
$value = 0 - $value;
}
$cell->setValueExplicit((double) $value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode('# ??/??');
return true;
}
// Check for percentage
if (preg_match('/^\\-?[0-9]*\\.?[0-9]*\\s?\\%$/', $value)) {
// Convert value to number
$value = (double) str_replace('%', '', $value) / 100;
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_PERCENTAGE_00);
return true;
}
// Check for currency
$currencyCode = PHPExcel_Shared_String::getCurrencyCode();
$decimalSeparator = PHPExcel_Shared_String::getDecimalSeparator();
$thousandsSeparator = PHPExcel_Shared_String::getThousandsSeparator();
if (preg_match('/^' . preg_quote($currencyCode) . ' *(\\d{1,3}(' . preg_quote($thousandsSeparator) . '\\d{3})*|(\\d+))(' . preg_quote($decimalSeparator) . '\\d{2})?$/', $value)) {
// Convert value to number
$value = (double) trim(str_replace(array($currencyCode, $thousandsSeparator, $decimalSeparator), array('', '', '.'), $value));
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(str_replace('$', $currencyCode, PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE));
return true;
} elseif (preg_match('/^\\$ *(\\d{1,3}(\\,\\d{3})*|(\\d+))(\\.\\d{2})?$/', $value)) {
// Convert value to number
$value = (double) trim(str_replace(array('$', ','), '', $value));
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
return true;
}
// Check for time without seconds e.g. '9:45', '09:45'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d$/', $value)) {
// Convert value to number
list($h, $m) = explode(':', $value);
$days = $h / 24 + $m / 1440;
$cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
return true;
}
// Check for time with seconds '9:45:59', '09:45:59'
if (preg_match('/^(\\d|[0-1]\\d|2[0-3]):[0-5]\\d:[0-5]\\d$/', $value)) {
// Convert value to number
list($h, $m, $s) = explode(':', $value);
$days = $h / 24 + $m / 1440 + $s / 86400;
// Convert value to number
$cell->setValueExplicit($days, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME4);
return true;
}
// Check for datetime, e.g. '2008-12-31', '2008-12-31 15:59', '2008-12-31 15:59:10'
if (($d = PHPExcel_Shared_Date::stringToExcel($value)) !== false) {
// Convert value to number
$cell->setValueExplicit($d, PHPExcel_Cell_DataType::TYPE_NUMERIC);
// Determine style. Either there is a time part or not. Look for ':'
if (strpos($value, ':') !== false) {
$formatCode = 'yyyy-mm-dd h:mm';
} else {
$formatCode = 'yyyy-mm-dd';
}
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode($formatCode);
return true;
}
// Check for newline character "\n"
if (strpos($value, "\n") !== FALSE) {
$value = PHPExcel_Shared_String::SanitizeUTF8($value);
$cell->setValueExplicit($value, PHPExcel_Cell_DataType::TYPE_STRING);
// Set style
$cell->getWorksheet()->getStyle($cell->getCoordinate())->getAlignment()->setWrapText(TRUE);
return true;
}
}
// Not bound yet? Use parent...
return parent::bindValue($cell, $value);
}
/**
* Is a given cell a date/time?
*
* @param PHPExcel_Cell $pCell
* @return boolean
*/
public static function isDateTime(PHPExcel_Cell $pCell)
{
return self::isDateTimeFormat($pCell->getWorksheet()->getStyle($pCell->getCoordinate())->getNumberFormat());
}
private function _parseFormula($formula, PHPExcel_Cell $pCell = null)
{
if (($formula = self::_convertMatrixReferences(trim($formula))) === false) {
return false;
}
// If we're using cell caching, then $pCell may well be flushed back to the cache (which detaches the parent worksheet),
// so we store the parent worksheet so that we can re-attach it when necessary
$pCellParent = $pCell !== null ? $pCell->getWorksheet() : null;
$regexpMatchString = '/^(' . self::CALCULATION_REGEXP_FUNCTION . '|' . self::CALCULATION_REGEXP_CELLREF . '|' . self::CALCULATION_REGEXP_NUMBER . '|' . self::CALCULATION_REGEXP_STRING . '|' . self::CALCULATION_REGEXP_OPENBRACE . '|' . self::CALCULATION_REGEXP_NAMEDRANGE . '|' . self::CALCULATION_REGEXP_ERROR . ')/si';
// Start with initialisation
$index = 0;
$stack = new PHPExcel_Calculation_Token_Stack();
$output = array();
$expectingOperator = false;
// We use this test in syntax-checking the expression to determine when a
// - is a negation or + is a positive operator rather than an operation
$expectingOperand = false;
// We use this test in syntax-checking the expression to determine whether an operand
// should be null in a function call
// The guts of the lexical parser
// Loop through the formula extracting each operator and operand in turn
while (true) {
//echo 'Assessing Expression '.substr($formula, $index),PHP_EOL;
$opCharacter = $formula[$index];
// Get the first character of the value at the current index position
//echo 'Initial character of expression block is '.$opCharacter,PHP_EOL;
if (isset(self::$_comparisonOperators[$opCharacter]) && strlen($formula) > $index && isset(self::$_comparisonOperators[$formula[$index + 1]])) {
$opCharacter .= $formula[++$index];
//echo 'Initial character of expression block is comparison operator '.$opCharacter.PHP_EOL;
}
// Find out if we're currently at the beginning of a number, variable, cell reference, function, parenthesis or operand
$isOperandOrFunction = preg_match($regexpMatchString, substr($formula, $index), $match);
//echo '$isOperandOrFunction is '.(($isOperandOrFunction) ? 'True' : 'False').PHP_EOL;
//var_dump($match);
if ($opCharacter == '-' && !$expectingOperator) {
// Is it a negation instead of a minus?
//echo 'Element is a Negation operator',PHP_EOL;
$stack->push('Unary Operator', '~');
// Put a negation on the stack
++$index;
// and drop the negation symbol
} elseif ($opCharacter == '%' && $expectingOperator) {
//echo 'Element is a Percentage operator',PHP_EOL;
$stack->push('Unary Operator', '%');
// Put a percentage on the stack
++$index;
} elseif ($opCharacter == '+' && !$expectingOperator) {
// Positive (unary plus rather than binary operator plus) can be discarded?
//echo 'Element is a Positive number, not Plus operator',PHP_EOL;
++$index;
// Drop the redundant plus symbol
} elseif (($opCharacter == '~' || $opCharacter == '|') && !$isOperandOrFunction) {
// We have to explicitly deny a tilde or pipe, because they are legal
return $this->_raiseFormulaError("Formula Error: Illegal character '~'");
// on the stack but not in the input expression
} elseif ((isset(self::$_operators[$opCharacter]) or $isOperandOrFunction) && $expectingOperator) {
// Are we putting an operator on the stack?
//echo 'Element with value '.$opCharacter.' is an Operator',PHP_EOL;
while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
$output[] = $stack->pop();
// Swap operands and higher precedence operators from the stack to the output
}
$stack->push('Binary Operator', $opCharacter);
// Finally put our current operator onto the stack
++$index;
$expectingOperator = false;
} elseif ($opCharacter == ')' && $expectingOperator) {
// Are we expecting to close a parenthesis?
//echo 'Element is a Closing bracket',PHP_EOL;
$expectingOperand = false;
while (($o2 = $stack->pop()) && $o2['value'] != '(') {
// Pop off the stack back to the last (
if ($o2 === null) {
return $this->_raiseFormulaError('Formula Error: Unexpected closing brace ")"');
} else {
$output[] = $o2;
}
}
$d = $stack->last(2);
if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
// Did this parenthesis just close a function?
$functionName = $matches[1];
// Get the function name
//echo 'Closed Function is '.$functionName,PHP_EOL;
$d = $stack->pop();
$argumentCount = $d['value'];
// See how many arguments there were (argument count is the next value stored on the stack)
//if ($argumentCount == 0) {
// echo 'With no arguments',PHP_EOL;
//} elseif ($argumentCount == 1) {
// echo 'With 1 argument',PHP_EOL;
//} else {
// echo 'With '.$argumentCount.' arguments',PHP_EOL;
//}
$output[] = $d;
// Dump the argument count on the output
$output[] = $stack->pop();
// Pop the function and push onto the output
if (isset(self::$_controlFunctions[$functionName])) {
//echo 'Built-in function '.$functionName,PHP_EOL;
$expectedArgumentCount = self::$_controlFunctions[$functionName]['argumentCount'];
$functionCall = self::$_controlFunctions[$functionName]['functionCall'];
} elseif (isset(self::$_PHPExcelFunctions[$functionName])) {
//echo 'PHPExcel function '.$functionName,PHP_EOL;
$expectedArgumentCount = self::$_PHPExcelFunctions[$functionName]['argumentCount'];
$functionCall = self::$_PHPExcelFunctions[$functionName]['functionCall'];
} else {
// did we somehow push a non-function on the stack? this should never happen
return $this->_raiseFormulaError("Formula Error: Internal error, non-function on stack");
}
// Check the argument count
$argumentCountError = false;
if (is_numeric($expectedArgumentCount)) {
if ($expectedArgumentCount < 0) {
//echo '$expectedArgumentCount is between 0 and '.abs($expectedArgumentCount),PHP_EOL;
if ($argumentCount > abs($expectedArgumentCount)) {
$argumentCountError = true;
$expectedArgumentCountString = 'no more than ' . abs($expectedArgumentCount);
}
} else {
//echo '$expectedArgumentCount is numeric '.$expectedArgumentCount,PHP_EOL;
if ($argumentCount != $expectedArgumentCount) {
$argumentCountError = true;
$expectedArgumentCountString = $expectedArgumentCount;
}
}
} elseif ($expectedArgumentCount != '*') {
$isOperandOrFunction = preg_match('/(\\d*)([-+,])(\\d*)/', $expectedArgumentCount, $argMatch);
//print_r($argMatch);
//echo PHP_EOL;
switch ($argMatch[2]) {
case '+':
if ($argumentCount < $argMatch[1]) {
$argumentCountError = true;
$expectedArgumentCountString = $argMatch[1] . ' or more ';
}
break;
case '-':
if ($argumentCount < $argMatch[1] || $argumentCount > $argMatch[3]) {
$argumentCountError = true;
$expectedArgumentCountString = 'between ' . $argMatch[1] . ' and ' . $argMatch[3];
}
break;
case ',':
if ($argumentCount != $argMatch[1] && $argumentCount != $argMatch[3]) {
$argumentCountError = true;
$expectedArgumentCountString = 'either ' . $argMatch[1] . ' or ' . $argMatch[3];
}
break;
}
}
if ($argumentCountError) {
return $this->_raiseFormulaError("Formula Error: Wrong number of arguments for {$functionName}() function: {$argumentCount} given, " . $expectedArgumentCountString . " expected");
}
}
++$index;
} elseif ($opCharacter == ',') {
// Is this the separator for function arguments?
//echo 'Element is a Function argument separator',PHP_EOL;
while (($o2 = $stack->pop()) && $o2['value'] != '(') {
// Pop off the stack back to the last (
if ($o2 === null) {
return $this->_raiseFormulaError("Formula Error: Unexpected ,");
} else {
$output[] = $o2;
}
// pop the argument expression stuff and push onto the output
}
// If we've a comma when we're expecting an operand, then what we actually have is a null operand;
// so push a null onto the stack
if ($expectingOperand || !$expectingOperator) {
$output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => null);
}
// make sure there was a function
$d = $stack->last(2);
if (!preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $d['value'], $matches)) {
return $this->_raiseFormulaError("Formula Error: Unexpected ,");
}
$d = $stack->pop();
$stack->push($d['type'], ++$d['value'], $d['reference']);
// increment the argument count
$stack->push('Brace', '(');
// put the ( back on, we'll need to pop back to it again
$expectingOperator = false;
$expectingOperand = true;
++$index;
} elseif ($opCharacter == '(' && !$expectingOperator) {
// echo 'Element is an Opening Bracket<br />';
$stack->push('Brace', '(');
++$index;
} elseif ($isOperandOrFunction && !$expectingOperator) {
// do we now have a function/variable/number?
$expectingOperator = true;
$expectingOperand = false;
$val = $match[1];
$length = strlen($val);
// echo 'Element with value '.$val.' is an Operand, Variable, Constant, String, Number, Cell Reference or Function<br />';
if (preg_match('/^' . self::CALCULATION_REGEXP_FUNCTION . '$/i', $val, $matches)) {
$val = preg_replace('/\\s/', '', $val);
// echo 'Element '.$val.' is a Function<br />';
if (isset(self::$_PHPExcelFunctions[strtoupper($matches[1])]) || isset(self::$_controlFunctions[strtoupper($matches[1])])) {
// it's a function
$stack->push('Function', strtoupper($val));
$ax = preg_match('/^\\s*(\\s*\\))/i', substr($formula, $index + $length), $amatch);
if ($ax) {
$stack->push('Operand Count for Function ' . strtoupper($val) . ')', 0);
$expectingOperator = true;
} else {
$stack->push('Operand Count for Function ' . strtoupper($val) . ')', 1);
$expectingOperator = false;
}
$stack->push('Brace', '(');
} else {
// it's a var w/ implicit multiplication
$output[] = array('type' => 'Value', 'value' => $matches[1], 'reference' => null);
}
} elseif (preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $val, $matches)) {
// echo 'Element '.$val.' is a Cell reference<br />';
// Watch for this case-change when modifying to allow cell references in different worksheets...
// Should only be applied to the actual cell column, not the worksheet name
// If the last entry on the stack was a : operator, then we have a cell range reference
$testPrevOp = $stack->last(1);
if ($testPrevOp['value'] == ':') {
// If we have a worksheet reference, then we're playing with a 3D reference
if ($matches[2] == '') {
// Otherwise, we 'inherit' the worksheet reference from the start cell reference
// The start of the cell range reference should be the last entry in $output
$startCellRef = $output[count($output) - 1]['value'];
preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '$/i', $startCellRef, $startMatches);
if ($startMatches[2] > '') {
$val = $startMatches[2] . '!' . $val;
}
} else {
return $this->_raiseFormulaError("3D Range references are not yet supported");
}
}
$output[] = array('type' => 'Cell Reference', 'value' => $val, 'reference' => $val);
// $expectingOperator = FALSE;
} else {
// it's a variable, constant, string, number or boolean
// echo 'Element is a Variable, Constant, String, Number or Boolean<br />';
// If the last entry on the stack was a : operator, then we may have a row or column range reference
$testPrevOp = $stack->last(1);
if ($testPrevOp['value'] == ':') {
$startRowColRef = $output[count($output) - 1]['value'];
$rangeWS1 = '';
if (strpos('!', $startRowColRef) !== false) {
list($rangeWS1, $startRowColRef) = explode('!', $startRowColRef);
}
if ($rangeWS1 != '') {
$rangeWS1 .= '!';
}
$rangeWS2 = $rangeWS1;
if (strpos('!', $val) !== false) {
list($rangeWS2, $val) = explode('!', $val);
}
if ($rangeWS2 != '') {
$rangeWS2 .= '!';
}
if (is_integer($startRowColRef) && ctype_digit($val) && $startRowColRef <= 1048576 && $val <= 1048576) {
// Row range
$endRowColRef = $pCellParent !== null ? $pCellParent->getHighestColumn() : 'XFD';
// Max 16,384 columns for Excel2007
$output[count($output) - 1]['value'] = $rangeWS1 . 'A' . $startRowColRef;
$val = $rangeWS2 . $endRowColRef . $val;
} elseif (ctype_alpha($startRowColRef) && ctype_alpha($val) && strlen($startRowColRef) <= 3 && strlen($val) <= 3) {
// Column range
$endRowColRef = $pCellParent !== null ? $pCellParent->getHighestRow() : 1048576;
// Max 1,048,576 rows for Excel2007
$output[count($output) - 1]['value'] = $rangeWS1 . strtoupper($startRowColRef) . '1';
$val = $rangeWS2 . $val . $endRowColRef;
}
}
$localeConstant = false;
if ($opCharacter == '"') {
// echo 'Element is a String<br />';
// UnEscape any quotes within the string
$val = self::_wrapResult(str_replace('""', '"', self::_unwrapResult($val)));
} elseif (is_numeric($val)) {
// echo 'Element is a Number<br />';
if (strpos($val, '.') !== false || stripos($val, 'e') !== false || $val > PHP_INT_MAX || $val < -PHP_INT_MAX) {
// echo 'Casting '.$val.' to float<br />';
$val = (double) $val;
} else {
// echo 'Casting '.$val.' to integer<br />';
$val = (int) $val;
}
} elseif (isset(self::$_ExcelConstants[trim(strtoupper($val))])) {
$excelConstant = trim(strtoupper($val));
// echo 'Element '.$excelConstant.' is an Excel Constant<br />';
$val = self::$_ExcelConstants[$excelConstant];
} elseif (($localeConstant = array_search(trim(strtoupper($val)), self::$_localeBoolean)) !== false) {
// echo 'Element '.$localeConstant.' is an Excel Constant<br />';
$val = self::$_ExcelConstants[$localeConstant];
}
$details = array('type' => 'Value', 'value' => $val, 'reference' => null);
if ($localeConstant) {
$details['localeValue'] = $localeConstant;
}
$output[] = $details;
}
$index += $length;
} elseif ($opCharacter == '$') {
// absolute row or column range
++$index;
} elseif ($opCharacter == ')') {
// miscellaneous error checking
if ($expectingOperand) {
$output[] = array('type' => 'NULL Value', 'value' => self::$_ExcelConstants['NULL'], 'reference' => null);
$expectingOperand = false;
$expectingOperator = true;
} else {
return $this->_raiseFormulaError("Formula Error: Unexpected ')'");
}
} elseif (isset(self::$_operators[$opCharacter]) && !$expectingOperator) {
return $this->_raiseFormulaError("Formula Error: Unexpected operator '{$opCharacter}'");
} else {
// I don't even want to know what you did to get here
return $this->_raiseFormulaError("Formula Error: An unexpected error occured");
}
// Test for end of formula string
if ($index == strlen($formula)) {
// Did we end with an operator?.
// Only valid for the % unary operator
if (isset(self::$_operators[$opCharacter]) && $opCharacter != '%') {
return $this->_raiseFormulaError("Formula Error: Operator '{$opCharacter}' has no operands");
} else {
break;
}
}
// Ignore white space
while ($formula[$index] == "\n" || $formula[$index] == "\r") {
++$index;
}
if ($formula[$index] == ' ') {
while ($formula[$index] == ' ') {
++$index;
}
// If we're expecting an operator, but only have a space between the previous and next operands (and both are
// Cell References) then we have an INTERSECTION operator
// echo 'Possible Intersect Operator<br />';
if ($expectingOperator && preg_match('/^' . self::CALCULATION_REGEXP_CELLREF . '.*/Ui', substr($formula, $index), $match) && $output[count($output) - 1]['type'] == 'Cell Reference') {
// echo 'Element is an Intersect Operator<br />';
while ($stack->count() > 0 && ($o2 = $stack->last()) && isset(self::$_operators[$o2['value']]) && @(self::$_operatorAssociativity[$opCharacter] ? self::$_operatorPrecedence[$opCharacter] < self::$_operatorPrecedence[$o2['value']] : self::$_operatorPrecedence[$opCharacter] <= self::$_operatorPrecedence[$o2['value']])) {
$output[] = $stack->pop();
// Swap operands and higher precedence operators from the stack to the output
}
$stack->push('Binary Operator', '|');
// Put an Intersect Operator on the stack
$expectingOperator = false;
}
}
}
while (($op = $stack->pop()) !== null) {
// pop everything off the stack and push onto output
if (is_array($op) && $op['value'] == '(' || $op === '(') {
return $this->_raiseFormulaError("Formula Error: Expecting ')'");
}
// if there are any opening braces on the stack, then braces were unbalanced
$output[] = $op;
}
return $output;
}