Create Xlsx Files Excluding Certain Cells From Protection

Posted

in

by

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

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

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

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.

/**
 * 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:


Posted

in

by

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Tutorials

Web Dev Tutorials