In drupal 8 custom module, Create Excel file, wirte data and lock file with password?

Question

I want to create excel file with password.

$library = libraries_load('PHPExcel');
$objPHPExcel = new \PHPExcel();
$objPHPExcel = \PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('sample.xlsx');
$objPHPExcel->setActiveSheetIndex(0)
    ->setCellValue('A1', 'Hello World');
$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('sample.xlsx');

File is created now I want to put password in 'sample.xlsx' file

I tried the following code.

$objPHPExcel->getActiveSheet()->getSecurity()->setWorkbookPassword("password");

But not working, Thanks for help in advance.

Mukunda Bhatta · Accepted Answer · 2020-08-04T06:07:33.863

Followig code is tested in drupal 8.7.7

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;    

$library = libraries_load('PHPExcel');
$objPHPExcel = new \PHPExcel();
$objPHPExcel = \PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objPHPExcel->load('sample.xlsx');
$objPHPExcel->setActiveSheetIndex(0)
 ->setCellValue('A1', 'Hello')
 ->setCellValue('B1', 'World');

Excel offers 3 levels of “protection”: Worksheet Security, Document Security and Cell Security.

Worksheet security offers other security options: you can disallow inserting rows on a specific sheet, disallow sorting,

An example on setting Worksheet Security:

   $objPHPExcel->getActiveSheet()->getProtection()
    ->setSheet(true) //use to enable worksheet protection
    ->setSort(true)
    ->setInsertRows(true)
    ->setFormatCells(true)
    ->setPassword('simple');

Document security allows you to set a password on a complete spreadsheet, allowing changes to be made only when that password is entered.

An example on setting Document Security:

$objPHPExcel->getSecurity()
  ->setLockWindows(true)
  ->setLockStructure(true)
  ->setWorkbookPassword("simple");

Cell security offers the option to lock/unlock a cell as well as show/hide the internal formula.

An example on setting Cell Security:

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

after protecting active worksheet save the file.

$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('sample.xlsx');

In drupal 8 custom module, Create Excel file, wirte data and lock file with password?

1 Answers1