PHPExcel lock particular cell

问题:

In phpexcel i was able to lock cell by

$objPHPExcel->getActiveSheet()->protectCells('A1:D1', 'php');
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);

If i double click on any Cell between A1 to D1 it will ask for password as it should.
But if i double click on any other cell (eg) A2 it says

"The cell or chart that you are trying to change is protected and therefore 
read-only".

Its locking whole worksheet, Is it possible to lock only particular cell and leave other cells editable?

回答1:

Finally, i found the right way to do it..

$objPHPExcel = new PHPExcel;
$objSheet = $objPHPExcel->getActiveSheet();

//PROTECT THE CELL RANGE

$objSheet->protectCells('A1:B1', 'PHP');

// UNPROTECT THE CELL RANGE

$objSheet->getStyle('A2:B2')->getProtection()
->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

// PROTECT THE WORKSHEET SHEET

$objSheet->getProtection()->setSheet(true);

This is working perfectly!

回答2:

sravis got me on the right track, but still one flaw remains: if you do it that way, you can still just remove the locking of the sheet using Excel without entering a password (just as it tells you when you click on a cell that's not locked with a password).

To lock an Excel-sheet with a password and unprotect a couple of cells, you need to protect the sheet (instead of just a couple of cells) and then unprotect some cells:

$sheet->getProtection()->setPassword('password hare');
$sheet->getProtection()->setSheet(true);
$sheet->getStyle('A1:B2')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

That way the user will have to enter the password when trying to unprotect the sheet using Excel.

回答3:

If you are using PhpSpreadsheet which is successor of PHPExcel by now, $sheet->getStyle('A1:B2')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED) will give class not found error.

Solution: use PhpOffice\PhpSpreadsheet\Style\Protection;

$sheet->getProtection()->setSheet(true);

$sheet->getStyle('A1:A10')->getProtection() ->setLocked(Protection::PROTECTION_UNPROTECTED);

This will work.

回答4:

/* this section lock for all sheet */
$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);

/*  and this section unlock cell rage('A2:Z2') from locked sheet */
$objPHPExcel->getActiveSheet()->getStyle('A2:Z500')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);