Lock all cells from being edited except specified ones when creating an xlsx file. Code with PhpSpreadsheet to choose which cell data can be updated, and protect all the other cells by ‘locking’ them, refraining protected cell contents from any updates.

This provides a wider data protection, an opposite of just selecting which cell to protect (Create Xlsx Files With Selected Cell Security Settings).

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

/**
 * Fill cell A1 to A5 with some data.
 */
for ($i=1; $i<=5; $i++) {
    $sheet->setCellValue('A' . $i, 'Hello A' . $i . ' !');
}

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

/**
 * Protect all cells except cell A1 to cell A5.
 */
$sheet->getStyle('A1:A5')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);

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

// Save the new .xlsx file
$writer->save('create-xlsx-files-excluding-certain-cells-from-protection.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.

/**
 * Fill cell A1 to A5 with some data.
 */
for ($i=1; $i<=5; $i++) {
    $sheet->setCellValue('A' . $i, 'Hello A' . $i . ' !');
}

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

/**
 * Protect all cells except cell A1 to cell A5.
 */
$sheet->getStyle('A1:A5')->getProtection()->setLocked(\PhpOffice\PhpSpreadsheet\Style\Protection::PROTECTION_UNPROTECTED);

Try to edit A1 to A5, it should be possible.

Try to edit other cells (not from A1 to A5).

A note showing that the cell is protected should appear.

To remove the protection.

Click on Review.

Click on Unprotect Sheet.

Unprotect Sheet should now become Protect Sheet, and it should be possible now to edit any cell data.

References:

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