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; }