private function PHPExcel_Writer_Excel5_Workbook::_writeAllDefinedNamesBiff8 in Loft Data Grids 6.2

Same name and namespace in other branches

1. 7.2 vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Workbook.php \PHPExcel_Writer_Excel5_Workbook::_writeAllDefinedNamesBiff8()

* Writes all the DEFINEDNAME records (BIFF8). * So far this is only used for repeating rows/columns (print titles) and print areas

1 call to PHPExcel_Writer_Excel5_Workbook::_writeAllDefinedNamesBiff8()

PHPExcel_Writer_Excel5_Workbook::writeWorkbook in vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Workbook.php

* Assemble worksheets into a workbook and send the BIFF data to an OLE * storage. * *

File

vendor/phpoffice/phpexcel/Classes/PHPExcel/Writer/Excel5/Workbook.php, line 652

Class

PHPExcel_Writer_Excel5_Workbook

PHPExcel_Writer_Excel5_Workbook

Code

private function _writeAllDefinedNamesBiff8() {
  $chunk = '';

  // Named ranges
  if (count($this->_phpExcel
    ->getNamedRanges()) > 0) {

    // Loop named ranges
    $namedRanges = $this->_phpExcel
      ->getNamedRanges();
    foreach ($namedRanges as $namedRange) {

      // Create absolute coordinate
      $range = PHPExcel_Cell::splitRange($namedRange
        ->getRange());
      for ($i = 0; $i < count($range); $i++) {
        $range[$i][0] = '\'' . str_replace("'", "''", $namedRange
          ->getWorksheet()
          ->getTitle()) . '\'!' . PHPExcel_Cell::absoluteCoordinate($range[$i][0]);
        if (isset($range[$i][1])) {
          $range[$i][1] = PHPExcel_Cell::absoluteCoordinate($range[$i][1]);
        }
      }
      $range = PHPExcel_Cell::buildRange($range);

      // e.g. Sheet1!$A$1:$B$2
      // parse formula
      try {
        $error = $this->_parser
          ->parse($range);
        $formulaData = $this->_parser
          ->toReversePolish();

        // make sure tRef3d is of type tRef3dR (0x3A)
        if (isset($formulaData[0]) and ($formulaData[0] == "z" or $formulaData[0] == "Z")) {
          $formulaData = ":" . substr($formulaData, 1);
        }
        if ($namedRange
          ->getLocalOnly()) {

          // local scope
          $scope = $this->_phpExcel
            ->getIndex($namedRange
            ->getScope()) + 1;
        }
        else {

          // global scope
          $scope = 0;
        }
        $chunk .= $this
          ->writeData($this
          ->_writeDefinedNameBiff8($namedRange
          ->getName(), $formulaData, $scope, false));
      } catch (PHPExcel_Exception $e) {

        // do nothing
      }
    }
  }

  // total number of sheets
  $total_worksheets = $this->_phpExcel
    ->getSheetCount();

  // write the print titles (repeating rows, columns), if any
  for ($i = 0; $i < $total_worksheets; ++$i) {
    $sheetSetup = $this->_phpExcel
      ->getSheet($i)
      ->getPageSetup();

    // simultaneous repeatColumns repeatRows
    if ($sheetSetup
      ->isColumnsToRepeatAtLeftSet() && $sheetSetup
      ->isRowsToRepeatAtTopSet()) {
      $repeat = $sheetSetup
        ->getColumnsToRepeatAtLeft();
      $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
      $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
      $repeat = $sheetSetup
        ->getRowsToRepeatAtTop();
      $rowmin = $repeat[0] - 1;
      $rowmax = $repeat[1] - 1;

      // construct formula data manually
      $formulaData = pack('Cv', 0x29, 0x17);

      // tMemFunc
      $formulaData .= pack('Cvvvvv', 0x3b, $i, 0, 65535, $colmin, $colmax);

      // tArea3d
      $formulaData .= pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, 0, 255);

      // tArea3d
      $formulaData .= pack('C', 0x10);

      // tList
      // store the DEFINEDNAME record
      $chunk .= $this
        ->writeData($this
        ->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));

      // (exclusive) either repeatColumns or repeatRows
    }
    else {
      if ($sheetSetup
        ->isColumnsToRepeatAtLeftSet() || $sheetSetup
        ->isRowsToRepeatAtTopSet()) {

        // Columns to repeat
        if ($sheetSetup
          ->isColumnsToRepeatAtLeftSet()) {
          $repeat = $sheetSetup
            ->getColumnsToRepeatAtLeft();
          $colmin = PHPExcel_Cell::columnIndexFromString($repeat[0]) - 1;
          $colmax = PHPExcel_Cell::columnIndexFromString($repeat[1]) - 1;
        }
        else {
          $colmin = 0;
          $colmax = 255;
        }

        // Rows to repeat
        if ($sheetSetup
          ->isRowsToRepeatAtTopSet()) {
          $repeat = $sheetSetup
            ->getRowsToRepeatAtTop();
          $rowmin = $repeat[0] - 1;
          $rowmax = $repeat[1] - 1;
        }
        else {
          $rowmin = 0;
          $rowmax = 65535;
        }

        // construct formula data manually because parser does not recognize absolute 3d cell references
        $formulaData = pack('Cvvvvv', 0x3b, $i, $rowmin, $rowmax, $colmin, $colmax);

        // store the DEFINEDNAME record
        $chunk .= $this
          ->writeData($this
          ->_writeDefinedNameBiff8(pack('C', 0x7), $formulaData, $i + 1, true));
      }
    }
  }

  // write the print areas, if any
  for ($i = 0; $i < $total_worksheets; ++$i) {
    $sheetSetup = $this->_phpExcel
      ->getSheet($i)
      ->getPageSetup();
    if ($sheetSetup
      ->isPrintAreaSet()) {

      // Print area, e.g. A3:J6,H1:X20
      $printArea = PHPExcel_Cell::splitRange($sheetSetup
        ->getPrintArea());
      $countPrintArea = count($printArea);
      $formulaData = '';
      for ($j = 0; $j < $countPrintArea; ++$j) {
        $printAreaRect = $printArea[$j];

        // e.g. A3:J6
        $printAreaRect[0] = PHPExcel_Cell::coordinateFromString($printAreaRect[0]);
        $printAreaRect[1] = PHPExcel_Cell::coordinateFromString($printAreaRect[1]);
        $print_rowmin = $printAreaRect[0][1] - 1;
        $print_rowmax = $printAreaRect[1][1] - 1;
        $print_colmin = PHPExcel_Cell::columnIndexFromString($printAreaRect[0][0]) - 1;
        $print_colmax = PHPExcel_Cell::columnIndexFromString($printAreaRect[1][0]) - 1;

        // construct formula data manually because parser does not recognize absolute 3d cell references
        $formulaData .= pack('Cvvvvv', 0x3b, $i, $print_rowmin, $print_rowmax, $print_colmin, $print_colmax);
        if ($j > 0) {
          $formulaData .= pack('C', 0x10);

          // list operator token ','
        }
      }

      // store the DEFINEDNAME record
      $chunk .= $this
        ->writeData($this
        ->_writeDefinedNameBiff8(pack('C', 0x6), $formulaData, $i + 1, true));
    }
  }

  // write autofilters, if any
  for ($i = 0; $i < $total_worksheets; ++$i) {
    $sheetAutoFilter = $this->_phpExcel
      ->getSheet($i)
      ->getAutoFilter();
    $autoFilterRange = $sheetAutoFilter
      ->getRange();
    if (!empty($autoFilterRange)) {
      $rangeBounds = PHPExcel_Cell::rangeBoundaries($autoFilterRange);

      //Autofilter built in name
      $name = pack('C', 0xd);
      $chunk .= $this
        ->writeData($this
        ->_writeShortNameBiff8($name, $i + 1, $rangeBounds, true));
    }
  }
  return $chunk;
}