Sorting Protected Cells Using PHPExcel

Question

I have a spreadsheeet outputted from PHPExcel, and everything displays perfectly.

I have protected the sheet as follows:

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);

which works fine.

In the sheet I have a table, in which some columns will be editable (the table is in cells A28:H50 - cells E29:H50 will be editable - i.e. 4 columns, all rows except the header). To unprotect these cells, I use the following:

$objPHPExcel->getActiveSheet()->getStyle('E29:H50')->getProtection()->setLocked(PHPExcel_Style_Protection::PROTECTION_UNPROTECTED);

And this works.

At this point, everything is as I expect it.

I have also put an Auto Filter on the table:

$objPHPExcel->getActiveSheet()->setAutoFilter('A28:H50');

Now, this is where I have a problem. The filter works, yet sorting does not. I have tried using the standard Excel sort, and via clicking the dropdown arrow on my filter fields.

Excel does not allow me to sort the data as the cells are protected.

Question:

Is there any way in which I can allow this?

• It will let me sort if I unprotect the whole table (I even HAVE to unprotect the header row for it to work).
• I have had a look at the setSort component of getProtection, but neither true or false works in this situation.
• If it is not possible, that will be okay, I will have to find an alternative.. I would just rather know the answer.

Answer 1

Old question, but it's likely someone will want to know the answer in present time.

You are looking for the set sort property. Call it after you call protection for the sheet:

$objPHPExcel->getActiveSheet()->getProtection()->setSheet(true);
$objPHPExcel->getActiveSheet()->getProtection()->setSort(true);