public function __construct($value = null, $parent = null){
$this->_value = $value;
//$this->interprete = new Interprete();
$this->html = html::getInstance();
$this->htmlAttributes = new HtmlAttributes();
$this->_parent = $parent;
$this->calculator = Calculation::getInstance();
// $this->_isCalculate = $calculate;
}
/**
* Set the locale code to use for formula translations and any special formatting
*
* @param string $locale The locale code to use (e.g. "fr" or "pt_br" or "en_uk")
* @return boolean Success or failure
*/
public static function setLocale($locale = 'en_us')
{
return Calculation::getInstance()->setLocale($locale);
}
/**
* Update references within formulas
*
* @param string $pFormula Formula to update
* @param int $pBefore Insert before this one
* @param int $pNumCols Number of columns to insert
* @param int $pNumRows Number of rows to insert
* @return string Updated formula
* @throws Exception
*/
public function updateFormulaReferences($pFormula = '', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0)
{
// Parse formula into a tree of tokens
$tokenisedFormula = Calculation::getInstance()->parseFormula($pFormula);
$newCellTokens = $cellTokens = array();
$adjustCount = 0;
// Build the translation table of cell tokens
foreach ($tokenisedFormula as $token) {
$token = $token['value'];
if (preg_match('/^' . Calculation::CALCULATION_REGEXP_CELLREF . '$/i', $token, $matches)) {
list($column, $row) = Cell::coordinateFromString($token);
// Max worksheet size is 1,048,576 rows by 16,384 columns in Excel 2007, so our adjustments need to be at least one digit more
$column = Cell::columnIndexFromString($column) + 100000;
$row += 10000000;
$cellIndex = $column . $row;
if (!isset($cellTokens[$cellIndex])) {
$newReference = $this->updateCellReference($token, $pBefore, $pNumCols, $pNumRows);
if ($newReference !== $token) {
$newCellTokens[$cellIndex] = preg_quote($newReference);
$cellTokens[$cellIndex] = '/(?<![A-Z])' . preg_quote($token) . '(?!\\d)/i';
++$adjustCount;
}
}
}
}
if ($adjustCount == 0) {
return $pFormula;
}
krsort($cellTokens);
krsort($newCellTokens);
// Update cell references in the formula
$formulaBlocks = explode('"', $pFormula);
foreach ($formulaBlocks as $i => &$formulaBlock) {
// Only count/replace in alternate array entries
if ($i % 2 == 0) {
$formulaBlock = preg_replace($cellTokens, $newCellTokens, $formulaBlock);
}
}
unset($formulaBlock);
// Then rebuild the formula string
return implode('"', $formulaBlocks);
}
/**
* Get calculated cell value
*
* @return mixed
*/
public function getCalculatedValue($resetLog = true)
{
// echo 'Cell '.$this->getCoordinate().' value is a '.$this->_dataType.' with a value of '.$this->getValue().'<br />';
if (!is_null($this->_calculatedValue) && $this->_dataType == Cell_DataType::TYPE_FORMULA) {
try {
// echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value<br />';
$result = Calculation::getInstance()->calculateCellValue($this, $resetLog);
// echo $this->getCoordinate().' calculation result is '.$result.'<br />';
} catch (Exception $ex) {
// echo 'Calculation Exception: '.$ex->getMessage().'<br />';
$result = '#N/A';
throw new Exception($ex->getMessage());
}
if (is_string($result) && $result == '#Not Yet Implemented') {
// echo 'Returning fallback value of '.$this->_calculatedValue.' for cell '.$this->getCoordinate().'<br />';
return $this->_calculatedValue;
// Fallback if calculation engine does not support the formula.
} else {
// echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().'<br />';
return $result;
}
}
if (is_null($this->_value)) {
// echo 'Cell '.$this->getCoordinate().' has no value, formula or otherwise<br />';
return null;
} else {
if ($this->_dataType != Cell_DataType::TYPE_FORMULA) {
// echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->_value.'<br />';
return $this->_value;
} else {
// echo 'Cell value is a formula: Calculating value<br />';
return Calculation::getInstance()->calculateCellValue($this, $resetLog);
}
}
}
/**
* Identify whether a string contains a fractional numeric value,
* and convert it to a numeric if it is
*
* @param string &$operand string value to test
* @return boolean
*/
public static function convertToNumberIfFraction(&$operand)
{
if (preg_match('/^' . self::STRING_REGEXP_FRACTION . '$/i', $operand, $match)) {
$sign = $match[1] == '-' ? '-' : '+';
$fractionFormula = '=' . $sign . $match[2] . $sign . $match[3];
$operand = Calculation::getInstance()->_calculateFormulaValue($fractionFormula);
return true;
}
return false;
}
/**
* Code to execute when this worksheet is unset()
*
*/
public function __destruct()
{
Calculation::getInstance($this->parent)->clearCalculationCacheForWorksheet($this->title);
$this->disconnectCells();
}
/**
* OFFSET
*
* Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells.
* The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and
* the number of columns to be returned.
*
* @param cellAddress The reference from which you want to base the offset. Reference must refer to a cell or
* range of adjacent cells; otherwise, OFFSET returns the #VALUE! error value.
* @param rows The number of rows, up or down, that you want the upper-left cell to refer to.
* Using 5 as the rows argument specifies that the upper-left cell in the reference is
* five rows below reference. Rows can be positive (which means below the starting reference)
* or negative (which means above the starting reference).
* @param cols The number of columns, to the left or right, that you want the upper-left cell of the result
* to refer to. Using 5 as the cols argument specifies that the upper-left cell in the
* reference is five columns to the right of reference. Cols can be positive (which means
* to the right of the starting reference) or negative (which means to the left of the
* starting reference).
* @param height The height, in number of rows, that you want the returned reference to be. Height must be a positive number.
* @param width The width, in number of columns, that you want the returned reference to be. Width must be a positive number.
* @return string A reference to a cell or range of cells
*/
public static function OFFSET($cellAddress = Null, $rows = 0, $columns = 0, $height = null, $width = null)
{
$rows = self::flattenSingleValue($rows);
$columns = self::flattenSingleValue($columns);
$height = self::flattenSingleValue($height);
$width = self::flattenSingleValue($width);
if ($cellAddress == Null) {
return 0;
}
$args = func_get_args();
$pCell = array_pop($args);
if (!is_object($pCell)) {
return self::$_errorCodes['reference'];
}
$sheetName = null;
if (strpos($cellAddress, "!")) {
list($sheetName, $cellAddress) = explode("!", $cellAddress);
}
if (strpos($cellAddress, ":")) {
list($startCell, $endCell) = explode(":", $cellAddress);
} else {
$startCell = $endCell = $cellAddress;
}
list($startCellColumn, $startCellRow) = Cell::coordinateFromString($startCell);
list($endCellColumn, $endCellRow) = Cell::coordinateFromString($endCell);
$startCellRow += $rows;
$startCellColumn = Cell::columnIndexFromString($startCellColumn) - 1;
$startCellColumn += $columns;
if ($startCellRow <= 0 || $startCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::columnIndexFromString($endCellColumn) - 1;
if ($width != null && !is_object($width)) {
$endCellColumn = $startCellColumn + $width - 1;
} else {
$endCellColumn += $columns;
}
$startCellColumn = Cell::stringFromColumnIndex($startCellColumn);
if ($height != null && !is_object($height)) {
$endCellRow = $startCellRow + $height - 1;
} else {
$endCellRow += $rows;
}
if ($endCellRow <= 0 || $endCellColumn < 0) {
return self::$_errorCodes['reference'];
}
$endCellColumn = Cell::stringFromColumnIndex($endCellColumn);
$cellAddress = $startCellColumn . $startCellRow;
if ($startCellColumn != $endCellColumn || $startCellRow != $endCellRow) {
$cellAddress .= ':' . $endCellColumn . $endCellRow;
}
if ($sheetName !== null) {
$pSheet = $pCell->getParent()->getParent()->getSheetByName($sheetName);
} else {
$pSheet = $pCell->getParent();
}
return Calculation::getInstance()->extractCellRange($cellAddress, $pSheet, False);
}
/**
* Get calculated cell value
*
* @deprecated Since version 1.7.8 for planned changes to cell for array formula handling
*
* @param boolean $resetLog Whether the calculation engine logger should be reset or not
* @return mixed
* @throws Exception
*/
public function getCalculatedValue($resetLog = true)
{
//echo 'Cell '.$this->getCoordinate().' value is a '.$this->dataType.' with a value of '.$this->getValue().PHP_EOL;
if ($this->dataType == Cell\DataType::TYPE_FORMULA) {
try {
//echo 'Cell value for '.$this->getCoordinate().' is a formula: Calculating value'.PHP_EOL;
$result = Calculation::getInstance($this->getWorksheet()->getParent())->calculateCellValue($this, $resetLog);
//echo $this->getCoordinate().' calculation result is '.$result.PHP_EOL;
// We don't yet handle array returns
if (is_array($result)) {
while (is_array($result)) {
$result = array_pop($result);
}
}
} catch (Exception $ex) {
if ($ex->getMessage() === 'Unable to access External Workbook' && $this->calculatedValue !== null) {
//echo 'Returning fallback value of '.$this->calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
return $this->calculatedValue;
// Fallback for calculations referencing external files.
}
//echo 'Calculation Exception: '.$ex->getMessage().PHP_EOL;
$result = '#N/A';
throw new Calculation\Exception($this->getWorksheet()->getTitle() . '!' . $this->getCoordinate() . ' -> ' . $ex->getMessage());
}
if ($result === '#Not Yet Implemented') {
//echo 'Returning fallback value of '.$this->calculatedValue.' for cell '.$this->getCoordinate().PHP_EOL;
return $this->calculatedValue;
// Fallback if calculation engine does not support the formula.
}
//echo 'Returning calculated value of '.$result.' for cell '.$this->getCoordinate().PHP_EOL;
return $result;
} elseif ($this->value instanceof RichText) {
// echo 'Cell value for '.$this->getCoordinate().' is rich text: Returning data value of '.$this->value.'<br />';
return $this->value->getPlainText();
}
// echo 'Cell value for '.$this->getCoordinate().' is not a formula: Returning data value of '.$this->value.'<br />';
return $this->value;
}
