[QUESTION] How to add protection to a range of cells

[pamekar]

Package version, Laravel version

Latest version, Laravel 5.4

I don't know if it's a bug, me not getting it right, or there's something I'm yet to do.

I was able to use the basic and advanced methods found in the maatwebsite for excel documentation.

The methods I used were
$sheet->protect() , and it's advanced method.

This methods were able to protect the whole sheet, but I need to protect only a range of cells.

I saw a worksheet method:
$sheet->protectCells(A1); but this ain't working for me. Please help with a method I could use in achieving this. Thanks..

[TakesTheBiscuit]

Hi @GreenWhiteDev

I checked out the underlying dependency with a search over at:
https://github.com/PHPOffice/PHPExcel/search?utf8=%E2%9C%93&q=protectCells&type=

You'll see in the results:
https://github.com/PHPOffice/PHPExcel/blob/1c8c2379ccf5ab9dd7cb46be965821d22173bcf4/Examples/05featuredemo.inc.php#L136-L139

That's probably enough to get you going i guess.
Something like this:

$sheet->getActiveSheet()->getProtection()->setSheet(true);	// Needs to be set to true in order to enable any worksheet protection!
$sheet->getActiveSheet()->protectCells('A3:E13', 'PHPExcel');

Edit: adding this ref:
ALSO see

https://github.com/PHPOffice/PHPExcel/blob/1c8c2379ccf5ab9dd7cb46be965821d22173bcf4/Classes/PHPExcel/Worksheet.php#L1793-L1814

    /**
     * Set protection on a cell range
     *
     * @param    string            $pRange                Cell (e.g. A1) or cell range (e.g. A1:E1)
     * @param    string            $pPassword            Password to unlock the protection
     * @param    boolean        $pAlreadyHashed    If the password has already been hashed, set this to true
     * @throws    PHPExcel_Exception
     * @return PHPExcel_Worksheet
     */
    public function protectCells($pRange = 'A1', $pPassword = '', $pAlreadyHashed = false)

Three parameters to play with on that method !

Let me know how you get on?

[jorgeald77]

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!
SOURCE: https://stackoverflow.com/questions/17046207/phpexcel-lock-particular-cell

In Laravel I use:

// PROTECT with your passwword
$sheet->protect('password');

// UNPROTECT the cell range
$sheet->getStyle('F2:F1000')->getProtection()->setLocked(\PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

// PROTECT worksheet sheet
$sheet->getProtection()->setSheet(true);

[pamekar]

@jorgeald77 Wow! That worked so cool..

I paused the project for a while. I just started a few days back.

I wasn't able to implement the security for the cell range then. I tried your solution and it worked so cool. I appreciate this so much.

Thanks everyone for contributing..