Hide formulas in PHPExcel

Question

Ok here is the problem. I have the following Code:

$excel = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $excel->load($template);
$objPHPExcel->setActiveSheetIndex(0);
$sheet = $objPHPExcel->getActiveSheet();

//filling up the sheet with tons of info

$sheet->getProtection()->setPassword('YouWishUKnew');
$sheet->getProtection()->setSheet(true);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('php://output');

it loads the template stored in $template (duh) and populates it with data.

Here is the problem. The template had some formulas locked, as in the cells weren't even selectable.

After the re-population the lock is gone (so i set it up again, no problem here) but now when I open the re-populated sheet I can see all the formulas!

My question is: Is there a way to lock the cells (make them unselectable) or hide the formulas (like the excel option in Format Cells -> Protection -> Hidden)?

PS: I checked the other questions and didn't find anything to answer my question.

Gotrekk · Accepted Answer · 2015-06-26 10:45:21Z

3

you should use

$sheet->getStyle('C'.$riga)
      ->getProtection()
      ->setHidden(
          PHPExcel_Style_Protection::PROTECTION_PROTECTED
      );

let me know if this is what you were looking for

edited Jun 26, 2015 at 10:45

answered Jun 26, 2015 at 10:37

Gotrekk

4943 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bramastic · Accepted Answer · 2016-02-10 08:54:06Z

1

In addition to @Gotrekk answer, in order to make it work, you need to have protection enabled for the active sheet. So, the complete answer would be:

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

$objPHPExcel->getActiveSheet()->getStyle('D3:AS'.$maxRows)
    ->getProtection()
    ->setHidden(PHPExcel_Style_Protection::PROTECTION_PROTECTED);

answered Feb 10, 2016 at 8:54

Bramastic

3994 silver badges16 bronze badges

Collectives™ on Stack Overflow

Hide formulas in PHPExcel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related