You are here

private function PHPExcel_Reader_Excel5::_readFormula in Loft Data Grids 7.2

Same name and namespace in other branches
  1. 6.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php \PHPExcel_Reader_Excel5::_readFormula()

* Read FORMULA record + perhaps a following STRING record if formula result is a string * This record contains the token array and the result of a * formula cell. * * -- "OpenOffice.org's Documentation of the Microsoft * Excel File Format"

1 call to PHPExcel_Reader_Excel5::_readFormula()
PHPExcel_Reader_Excel5::load in vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php
* Loads PHPExcel from file * *

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Reader/Excel5.php, line 3791

Class

PHPExcel_Reader_Excel5
PHPExcel_Reader_Excel5

Code

private function _readFormula() {
  $length = self::_GetInt2d($this->_data, $this->_pos + 2);
  $recordData = $this
    ->_readRecordData($this->_data, $this->_pos + 4, $length);

  // move stream pointer to next record
  $this->_pos += 4 + $length;

  // offset: 0; size: 2; row index
  $row = self::_GetInt2d($recordData, 0);

  // offset: 2; size: 2; col index
  $column = self::_GetInt2d($recordData, 2);
  $columnString = PHPExcel_Cell::stringFromColumnIndex($column);

  // offset: 20: size: variable; formula structure
  $formulaStructure = substr($recordData, 20);

  // offset: 14: size: 2; option flags, recalculate always, recalculate on open etc.
  $options = self::_GetInt2d($recordData, 14);

  // bit: 0; mask: 0x0001; 1 = recalculate always
  // bit: 1; mask: 0x0002; 1 = calculate on open
  // bit: 2; mask: 0x0008; 1 = part of a shared formula
  $isPartOfSharedFormula = (bool) (0x8 & $options);

  // WARNING:
  // We can apparently not rely on $isPartOfSharedFormula. Even when $isPartOfSharedFormula = true
  // the formula data may be ordinary formula data, therefore we need to check
  // explicitly for the tExp token (0x01)
  $isPartOfSharedFormula = $isPartOfSharedFormula && ord($formulaStructure[2]) == 0x1;
  if ($isPartOfSharedFormula) {

    // part of shared formula which means there will be a formula with a tExp token and nothing else
    // get the base cell, grab tExp token
    $baseRow = self::_GetInt2d($formulaStructure, 3);
    $baseCol = self::_GetInt2d($formulaStructure, 5);
    $this->_baseCell = PHPExcel_Cell::stringFromColumnIndex($baseCol) . ($baseRow + 1);
  }

  // Read cell?
  if ($this
    ->getReadFilter() !== NULL && $this
    ->getReadFilter()
    ->readCell($columnString, $row + 1, $this->_phpSheet
    ->getTitle())) {
    if ($isPartOfSharedFormula) {

      // formula is added to this cell after the sheet has been read
      $this->_sharedFormulaParts[$columnString . ($row + 1)] = $this->_baseCell;
    }

    // offset: 16: size: 4; not used
    // offset: 4; size: 2; XF index
    $xfIndex = self::_GetInt2d($recordData, 4);

    // offset: 6; size: 8; result of the formula
    if (ord($recordData[6]) == 0 && ord($recordData[12]) == 255 && ord($recordData[13]) == 255) {

      // String formula. Result follows in appended STRING record
      $dataType = PHPExcel_Cell_DataType::TYPE_STRING;

      // read possible SHAREDFMLA record
      $code = self::_GetInt2d($this->_data, $this->_pos);
      if ($code == self::XLS_Type_SHAREDFMLA) {
        $this
          ->_readSharedFmla();
      }

      // read STRING record
      $value = $this
        ->_readString();
    }
    elseif (ord($recordData[6]) == 1 && ord($recordData[12]) == 255 && ord($recordData[13]) == 255) {

      // Boolean formula. Result is in +2; 0=false, 1=true
      $dataType = PHPExcel_Cell_DataType::TYPE_BOOL;
      $value = (bool) ord($recordData[8]);
    }
    elseif (ord($recordData[6]) == 2 && ord($recordData[12]) == 255 && ord($recordData[13]) == 255) {

      // Error formula. Error code is in +2
      $dataType = PHPExcel_Cell_DataType::TYPE_ERROR;
      $value = self::_mapErrorCode(ord($recordData[8]));
    }
    elseif (ord($recordData[6]) == 3 && ord($recordData[12]) == 255 && ord($recordData[13]) == 255) {

      // Formula result is a null string
      $dataType = PHPExcel_Cell_DataType::TYPE_NULL;
      $value = '';
    }
    else {

      // forumla result is a number, first 14 bytes like _NUMBER record
      $dataType = PHPExcel_Cell_DataType::TYPE_NUMERIC;
      $value = self::_extractNumber(substr($recordData, 6, 8));
    }
    $cell = $this->_phpSheet
      ->getCell($columnString . ($row + 1));
    if (!$this->_readDataOnly) {

      // add cell style
      $cell
        ->setXfIndex($this->_mapCellXfIndex[$xfIndex]);
    }

    // store the formula
    if (!$isPartOfSharedFormula) {

      // not part of shared formula
      // add cell value. If we can read formula, populate with formula, otherwise just used cached value
      try {
        if ($this->_version != self::XLS_BIFF8) {
          throw new PHPExcel_Reader_Exception('Not BIFF8. Can only read BIFF8 formulas');
        }
        $formula = $this
          ->_getFormulaFromStructure($formulaStructure);

        // get formula in human language
        $cell
          ->setValueExplicit('=' . $formula, PHPExcel_Cell_DataType::TYPE_FORMULA);
      } catch (PHPExcel_Exception $e) {
        $cell
          ->setValueExplicit($value, $dataType);
      }
    }
    else {
      if ($this->_version == self::XLS_BIFF8) {

        // do nothing at this point, formula id added later in the code
      }
      else {
        $cell
          ->setValueExplicit($value, $dataType);
      }
    }

    // store the cached calculated value
    $cell
      ->setCalculatedValue($value);
  }
}