/**
* 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);
}
