/** * Update cell range * * @param string $pCellRange Cell range (e.g. 'B2:D4', 'B:C' or '2:3') * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = Cell::splitRange($pCellRange); $ic = count($range); for ($i = 0; $i < $ic; ++$i) { $jc = count($range[$i]); for ($j = 0; $j < $jc; ++$j) { if (ctype_alpha($range[$i][$j])) { $r = Cell::coordinateFromString($this->updateSingleCellReference($range[$i][$j] . '1', $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[0]; } elseif (ctype_digit($range[$i][$j])) { $r = Cell::coordinateFromString($this->updateSingleCellReference('A' . $range[$i][$j], $pBefore, $pNumCols, $pNumRows)); $range[$i][$j] = $r[1]; } else { $range[$i][$j] = $this->updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } } // Recreate range string return Cell::buildRange($range); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
/** * Write Defined Name for autoFilter * * @param Shared_XMLWriter $objWriter XML Writer * @param NamedRange $pNamedRange * @throws Exception */ private function _writeDefinedNameForNamedRange(Shared_XMLWriter $objWriter = null, NamedRange $pNamedRange) { // definedName for named range $objWriter->startElement('definedName'); $objWriter->writeAttribute('name', $pNamedRange->getName()); if ($pNamedRange->getLocalOnly()) { $objWriter->writeAttribute('localSheetId', $pNamedRange->getScope()->getParent()->getIndex($pNamedRange->getScope())); } // Create absolute coordinate and write as raw text $range = Cell::splitRange($pNamedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $pNamedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]); } } $range = Cell::buildRange($range); $objWriter->writeRaw($range); $objWriter->endElement(); }
/** * Update cell range * * @param string $pCellRange Cell range * @param int $pBefore Insert before this one * @param int $pNumCols Number of columns to increment * @param int $pNumRows Number of rows to increment * @return string Updated cell range * @throws Exception */ private function _updateCellRange($pCellRange = 'A1:A1', $pBefore = 'A1', $pNumCols = 0, $pNumRows = 0) { if (strpos($pCellRange, ':') !== false || strpos($pCellRange, ',') !== false) { // Update range $range = Cell::splitRange($pCellRange); for ($i = 0; $i < count($range); ++$i) { for ($j = 0; $j < count($range[$i]); ++$j) { $range[$i][$j] = $this->_updateSingleCellReference($range[$i][$j], $pBefore, $pNumCols, $pNumRows); } } // Recreate range string return Cell::buildRange($range); } else { throw new Exception("Only cell ranges may be passed to this method."); } }
/** * 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 = Cell::splitRange($namedRange->getRange()); for ($i = 0; $i < count($range); $i++) { $range[$i][0] = '\'' . str_replace("'", "''", $namedRange->getWorksheet()->getTitle()) . '\'!' . Cell::absoluteCoordinate($range[$i][0]); if (isset($range[$i][1])) { $range[$i][1] = Cell::absoluteCoordinate($range[$i][1]); } } $range = 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 (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 = Cell::columnIndexFromString($repeat[0]) - 1; $colmax = 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 = Cell::columnIndexFromString($repeat[0]) - 1; $colmax = 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 = Cell::splitRange($sheetSetup->getPrintArea()); $countPrintArea = count($printArea); $formulaData = ''; for ($j = 0; $j < $countPrintArea; ++$j) { $printAreaRect = $printArea[$j]; // e.g. A3:J6 $printAreaRect[0] = Cell::coordinateFromString($printAreaRect[0]); $printAreaRect[1] = Cell::coordinateFromString($printAreaRect[1]); $print_rowmin = $printAreaRect[0][1] - 1; $print_rowmax = $printAreaRect[1][1] - 1; $print_colmin = Cell::columnIndexFromString($printAreaRect[0][0]) - 1; $print_colmax = 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)); } } return $chunk; }