Пример #1
0
 /**
  * Write Defined Name for autoFilter
  *
  * @param     \PHPExcel\Shared\XMLWriter    $objWriter         XML Writer
  * @param     \PHPExcel\Worksheet            $pSheet
  * @param     int                            $pSheetId
  * @throws     \PHPExcel\Writer\Exception
  */
 private function writeDefinedNameForAutofilter(\PHPExcel\Shared\XMLWriter $objWriter, \PHPExcel\Worksheet $pSheet = null, $pSheetId = 0)
 {
     // definedName for autoFilter
     $autoFilterRange = $pSheet->getAutoFilter()->getRange();
     if (!empty($autoFilterRange)) {
         $objWriter->startElement('definedName');
         $objWriter->writeAttribute('name', '_xlnm._FilterDatabase');
         $objWriter->writeAttribute('localSheetId', $pSheetId);
         $objWriter->writeAttribute('hidden', '1');
         // Create absolute coordinate and write as raw text
         $range = \PHPExcel\Cell::splitRange($autoFilterRange);
         $range = $range[0];
         //    Strip any worksheet ref so we can make the cell ref absolute
         if (strpos($range[0], '!') !== false) {
             list($ws, $range[0]) = explode('!', $range[0]);
         }
         $range[0] = \PHPExcel\Cell::absoluteCoordinate($range[0]);
         $range[1] = \PHPExcel\Cell::absoluteCoordinate($range[1]);
         $range = implode(':', $range);
         $objWriter->writeRawData('\'' . str_replace("'", "''", $pSheet->getTitle()) . '\'!' . $range);
         $objWriter->endElement();
     }
 }
Пример #2
0
 /**
  * Writes all the DEFINEDNAME records (BIFF8).
  * So far this is only used for repeating rows/columns (print titles) and print areas
  */
 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
         } elseif ($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;
 }