/** * Lock whole sheet. All cells are protected. * @param string $password default null * @return Formatter */ public function lockSheet($password = null) { $this->sheet->getProtection()->setSheet(true); if (!is_null($password)) { $this->sheet->getProtection()->setPassword($password); } return $this; }
/** * Write the worksheet PASSWORD record. */ private function _writePassword() { // Exit unless sheet protection and password have been specified if (!$this->_phpSheet->getProtection()->getSheet() || !$this->_phpSheet->getProtection()->getPassword()) { return; } $record = 0x13; // Record identifier $length = 0x2; // Bytes to follow $wPassword = hexdec($this->_phpSheet->getProtection()->getPassword()); // Encoded password $header = pack("vv", $record, $length); $data = pack("v", $wPassword); $this->_append($header . $data); }
$this->_objs[] = array('ftCmoType' => $ftCmoType, 'cbCmoSize' => $cbCmoSize, 'otObjType' => $otObjType, 'idObjID' => $idObjID, 'grbitOpts' => $grbitOpts); $this->textObjRef = $idObjID; // echo '<b>_readObj()</b><br>'; // var_dump(end($this->_objs)); // echo '<br>'; } /** * Read WINDOW2 record */ private function _readWindow2() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; option flags $options = self::_GetInt2d($recordData, 0); // bit: 1; mask: 0x0002; 0 = do not show gridlines, 1 = show gridlines $showGridlines = (bool) ((0x2 & $options) >> 1); $this->_phpSheet->setShowGridlines($showGridlines); // bit: 2; mask: 0x0004; 0 = do not show headers, 1 = show headers $showRowColHeaders = (bool) ((0x4 & $options) >> 2); $this->_phpSheet->setShowRowColHeaders($showRowColHeaders); // bit: 3; mask: 0x0008; 0 = panes are not frozen, 1 = panes are frozen $this->_frozen = (bool) ((0x8 & $options) >> 3); // bit: 6; mask: 0x0040; 0 = columns from left to right, 1 = columns from right to left $this->_phpSheet->setRightToLeft((bool) ((0x40 & $options) >> 6)); // bit: 10; mask: 0x0400; 0 = sheet not active, 1 = sheet active $isActive = (bool) ((0x400 & $options) >> 10); if ($isActive) { $this->_phpExcel->setActiveSheetIndex($this->_phpExcel->getIndex($this->_phpSheet)); } } /** * Read SCL record */ private function _readScl() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; // offset: 0; size: 2; numerator of the view magnification $numerator = self::_GetInt2d($recordData, 0); // offset: 2; size: 2; numerator of the view magnification $denumerator = self::_GetInt2d($recordData, 2); // set the zoom scale (in percent) $this->_phpSheet->getSheetView()->setZoomScale($numerator * 100 / $denumerator); } /** * Read PANE record */ private function _readPane() { $length = self::_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: 2; position of vertical split $px = self::_GetInt2d($recordData, 0); // offset: 2; size: 2; position of horizontal split $py = self::_GetInt2d($recordData, 2); if ($this->_frozen) { // frozen panes $this->_phpSheet->freezePane(PHPExcel_Cell::stringFromColumnIndex($px) . ($py + 1)); } else { // unfrozen panes; split windows; not supported by PHPExcel core } }
/** * Write SheetProtection * * @param PHPExcel_Shared_XMLWriter $objWriter XML Writer * @param PHPExcel_Worksheet $pSheet Worksheet * @throws Exception */ private function _writeSheetProtection(PHPExcel_Shared_XMLWriter $objWriter = null, PHPExcel_Worksheet $pSheet = null) { // sheetProtection $objWriter->startElement('sheetProtection'); if ($pSheet->getProtection()->getPassword() != '') { $objWriter->writeAttribute('password', $pSheet->getProtection()->getPassword()); } $objWriter->writeAttribute('sheet', $pSheet->getProtection()->getSheet() ? 'true' : 'false'); $objWriter->writeAttribute('objects', $pSheet->getProtection()->getObjects() ? 'true' : 'false'); $objWriter->writeAttribute('scenarios', $pSheet->getProtection()->getScenarios() ? 'true' : 'false'); $objWriter->writeAttribute('formatCells', $pSheet->getProtection()->getFormatCells() ? 'true' : 'false'); $objWriter->writeAttribute('formatColumns', $pSheet->getProtection()->getFormatColumns() ? 'true' : 'false'); $objWriter->writeAttribute('formatRows', $pSheet->getProtection()->getFormatRows() ? 'true' : 'false'); $objWriter->writeAttribute('insertColumns', $pSheet->getProtection()->getInsertColumns() ? 'true' : 'false'); $objWriter->writeAttribute('insertRows', $pSheet->getProtection()->getInsertRows() ? 'true' : 'false'); $objWriter->writeAttribute('insertHyperlinks', $pSheet->getProtection()->getInsertHyperlinks() ? 'true' : 'false'); $objWriter->writeAttribute('deleteColumns', $pSheet->getProtection()->getDeleteColumns() ? 'true' : 'false'); $objWriter->writeAttribute('deleteRows', $pSheet->getProtection()->getDeleteRows() ? 'true' : 'false'); $objWriter->writeAttribute('selectLockedCells', $pSheet->getProtection()->getSelectLockedCells() ? 'true' : 'false'); $objWriter->writeAttribute('sort', $pSheet->getProtection()->getSort() ? 'true' : 'false'); $objWriter->writeAttribute('autoFilter', $pSheet->getProtection()->getAutoFilter() ? 'true' : 'false'); $objWriter->writeAttribute('pivotTables', $pSheet->getProtection()->getPivotTables() ? 'true' : 'false'); $objWriter->writeAttribute('selectUnlockedCells', $pSheet->getProtection()->getSelectUnlockedCells() ? 'true' : 'false'); $objWriter->endElement(); }
/** * PASSWORD - Sheet protection (hashed) password (BIFF2 through BIFF8) */ private function _readPassword() { $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if (!$this->_readDataOnly) { // offset: 0; size: 2; 16-bit hash value of password $password = strtoupper(dechex($this->_GetInt2d($recordData, 0))); // the hashed password $this->_phpSheet->getProtection()->setPassword($password, true); } }
/** * Read SHEETPROTECTION record */ private function _readSheetProtection() { $length = $this->_GetInt2d($this->_data, $this->_pos + 2); $recordData = substr($this->_data, $this->_pos + 4, $length); // move stream pointer to next record $this->_pos += 4 + $length; if ($this->_readDataOnly) { return; } // offset: 0; size: 2; repeated record header // offset: 2; size: 9; not used // offset: 11; size: 8; unknown data // offset: 19; size: 2; option flags $options = $this->_GetInt2d($recordData, 19); // bit: 0; mask 0x0001; 1 = user may edit objects, 0 = users must not edit objects $bool = (0x1 & $options) >> 0; $this->_phpSheet->getProtection()->setObjects(!$bool); // bit: 1; mask 0x0002; edit scenarios $bool = (0x2 & $options) >> 1; $this->_phpSheet->getProtection()->setScenarios(!$bool); // bit: 2; mask 0x0004; format cells $bool = (0x4 & $options) >> 2; $this->_phpSheet->getProtection()->setFormatCells(!$bool); // bit: 3; mask 0x0008; format columns $bool = (0x8 & $options) >> 3; $this->_phpSheet->getProtection()->setFormatColumns(!$bool); // bit: 4; mask 0x0010; format rows $bool = (0x10 & $options) >> 4; $this->_phpSheet->getProtection()->setFormatRows(!$bool); // bit: 5; mask 0x0020; insert columns $bool = (0x20 & $options) >> 5; $this->_phpSheet->getProtection()->setInsertColumns(!$bool); // bit: 6; mask 0x0040; insert rows $bool = (0x40 & $options) >> 6; $this->_phpSheet->getProtection()->setInsertRows(!$bool); // bit: 7; mask 0x0080; insert hyperlinks $bool = (0x80 & $options) >> 7; $this->_phpSheet->getProtection()->setInsertHyperlinks(!$bool); // bit: 8; mask 0x0100; delete columns $bool = (0x100 & $options) >> 8; $this->_phpSheet->getProtection()->setDeleteColumns(!$bool); // bit: 9; mask 0x0200; delete rows $bool = (0x200 & $options) >> 9; $this->_phpSheet->getProtection()->setDeleteRows(!$bool); // bit: 10; mask 0x0400; select locked cells $bool = (0x400 & $options) >> 10; $this->_phpSheet->getProtection()->setSelectLockedCells(!$bool); // bit: 11; mask 0x0800; sort cell range $bool = (0x800 & $options) >> 11; $this->_phpSheet->getProtection()->setSort(!$bool); // bit: 12; mask 0x1000; auto filter $bool = (0x1000 & $options) >> 12; $this->_phpSheet->getProtection()->setAutoFilter(!$bool); // bit: 13; mask 0x2000; pivot tables $bool = (0x2000 & $options) >> 13; $this->_phpSheet->getProtection()->setPivotTables(!$bool); // bit: 14; mask 0x4000; select unlocked cells $bool = (0x4000 & $options) >> 14; $this->_phpSheet->getProtection()->setSelectUnlockedCells(!$bool); // offset: 21; size: 2; not used }
/** * Write SHEETPROTECTION */ private function _writeSheetProtection() { // record identifier $record = 0x867; // prepare options $options = (int) (!$this->_phpSheet->getProtection()->getObjects()) | (int) (!$this->_phpSheet->getProtection()->getScenarios()) << 1 | (int) (!$this->_phpSheet->getProtection()->getFormatCells()) << 2 | (int) (!$this->_phpSheet->getProtection()->getFormatColumns()) << 3 | (int) (!$this->_phpSheet->getProtection()->getFormatRows()) << 4 | (int) (!$this->_phpSheet->getProtection()->getInsertColumns()) << 5 | (int) (!$this->_phpSheet->getProtection()->getInsertRows()) << 6 | (int) (!$this->_phpSheet->getProtection()->getInsertHyperlinks()) << 7 | (int) (!$this->_phpSheet->getProtection()->getDeleteColumns()) << 8 | (int) (!$this->_phpSheet->getProtection()->getDeleteRows()) << 9 | (int) (!$this->_phpSheet->getProtection()->getSelectLockedCells()) << 10 | (int) (!$this->_phpSheet->getProtection()->getSort()) << 11 | (int) (!$this->_phpSheet->getProtection()->getAutoFilter()) << 12 | (int) (!$this->_phpSheet->getProtection()->getPivotTables()) << 13 | (int) (!$this->_phpSheet->getProtection()->getSelectUnlockedCells()) << 14; // record data $recordData = pack('vVVCVVvv', 0x867, 0x0, 0x0, 0x0, 0x1000200, 0xffffffff, $options, 0x0); $length = strlen($recordData); $header = pack('vv', $record, $length); $this->_append($header . $recordData); }