Create Xlsx Files With Selected Cell Security Settings

| August 9, 2020 | 717 views | PhpSpreadsheet

Lock specific cells and forbid it’s data to be updated, choose important cells and protect them by coding selected cells when creating xlsx file in PhpSpreadsheet, preserving it’s value and avoiding any unnecessary or accidental updates.

Requirements:

  • Composer
  • PHP 7.2 or newer

Step 1.

Setup dependencies.

{
    "require": {
        "phpoffice/phpspreadsheet": "^1.3"
    }
}

Step 2.

Install phpspreadsheet.

$ composer install

Step 3.

Create a new PHP file, and start coding.

<?php

// Autoload dependencies
require 'vendor/autoload.php';

// Import the core class of PhpSpreadsheet
use PhpOffice\PhpSpreadsheet\Spreadsheet;

// Import the Xlsx writer class
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

// Create a new Spreadsheet object
$spreadsheet = new Spreadsheet();

// Retrieve the current active worksheet
$sheet = $spreadsheet->getActiveSheet();

// Set cell A1 with the "Hello A1 !" string value
$sheet->setCellValue('A1', 'Hello A1 !');

// Set cell B1 with the "Hello B1 !" string value
$sheet->setCellValue('B1', 'Hello B1 !');

// Set cell A5 with the "Hello A5 !" string value
$sheet->setCellValue('A5', 'Hello A5 !');

// Set cell B5 with the "Hello B5 !" string value
$sheet->setCellValue('B5', 'Hello B5 !');

/**
 * Set the worksheet protection to 'true',
 * this is a requirement to enable it.
 */
$sheet->getProtection()->setSheet(true);

/**
 * Protect cell A1, and cell B1 only.
 */
$spreadsheet->getDefaultStyle()->getProtection()->setLocked(false);
$sheet->getStyle('A1:B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);

// Write a new .xlsx file
$writer = new Xlsx($spreadsheet);

// Save the new .xlsx file
$writer->save('create-xlsx-files-with-selected-cell-security-settings.xlsx');

Test.

Run the following codes.

$ php create-xlsx-files-with-selected-cell-security-settings.php

Result.

Open the generated file create-xlsx-files-with-selected-cell-security-settings.xlsx.

Try to edit A1 or B1.

A note showing that the cell is protected should appear.

// Set cell A1 with the "Hello A1 !" string value
$sheet->setCellValue('A1', 'Hello A1 !');

// Set cell B1 with the "Hello B1 !" string value
$sheet->setCellValue('B1', 'Hello B1 !');

// Set cell A5 with the "Hello A5 !" string value
$sheet->setCellValue('A5', 'Hello A5 !');

// Set cell B5 with the "Hello B5 !" string value
$sheet->setCellValue('B5', 'Hello B5 !');

/**
 * Set the worksheet protection to 'true',
 * this is a requirement to enable it.
 */
$sheet->getProtection()->setSheet(true);

/**
 * Protect cell A1, and cell B1 only.
 */
$spreadsheet->getDefaultStyle()->getProtection()->setLocked(false);
$sheet->getStyle('A1:B1')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_PROTECTED);

Try to update any other cell beside A1 and B1.

It should be possible to update cells that are not A1 and B1.

Allow editing of the protected cells (A1 and B1).

Select A1 or B1, then click on Review.

Click on Unprotect Sheet.

Try to update cell A1 or whichever is now unprotected, it should be possible now.

References:

0 Comments

Leave a Reply

Your email address will not be published.