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"
    }
}

composer.json

Step 2.

Install phpspreadsheet.

$ composer install

command line

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');

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

Test.

Run the following codes.

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

command line

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:

• How to install PhpSpreadsheet
• Create Xlsx Files with Document Security Settings
• Create Xlsx Files with Worksheet Security Settings
• Creating a spreadsheet
• Reading and writing to file
• PhpSpreadsheet Recipes