Disallow the possibility of updating the data of an entire spreadsheet document, code this with PhpSpreadsheet and create a new xlsx file.

The following options will be deactivated, ‘Text Filters’, ‘Data Sorting’, ‘Clear Contents’, ‘Delete Colum’, ‘Find and Replace’, ‘Text Formulas’, ‘Logical Formulas’, ‘Math Formulas’, and other data-editing options. The option to add a ‘New sheet’ will also become prohibited.

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 World !" string value
$sheet->setCellValue('A1', 'Hello World !');

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

/**
 * Set workbook protection
 * for 'Structure' and 'Window'.
 */
$security = $spreadsheet->getSecurity();
$security->setLockWindows(true);
$security->setLockStructure(true);
$security->setWorkbookPassword("HelloWorld!");

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

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

create-xlsx-files-with-document-security-settings.php

Test.

Run the following codes.

$ php create-xlsx-files-with-document-security-settings.php

command line

Result.

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

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

/**
 * Set workbook protection
 * for 'Structure' and 'Window'.
 */
$security = $spreadsheet->getSecurity();
$security->setLockWindows(true);
$security->setLockStructure(true);
$security->setWorkbookPassword("HelloWorld!");

Click on Review.

The Protect Workbook option should be highlighted, this indicates that the option is selected and enabled.

The Protect Workbook option is not selected on default.

Try to edit the A1 value, or any cell data.

A note showing that the cell is protected should appear.

The option to add a New sheet should also show as not available (not clickable).

Test again.

Check the password.

Click on Protect Workbook.

The option to Unprotect Workbook should appear.

Enter the password “HelloWorld!“.

Test further.

Try to enable editing of cell values.

Click on File.

Under Info, the Protect Workbook option should appear highligthed, this points out that the workbook is currently protected.

Ther should also an option to Unprotect it, to make the workbook editable again, click on Unprotect.

Try to update cell A1, or any cell values, it should be possible now.

Another way to ‘Unprotect Sheet‘ and make the revision possible.

Click on Review.

Click on Unprotect Sheet.

Try to update cell A1, or any cell values, it should be possible now.

References:

• How to install PhpSpreadsheet
• Creating a spreadsheet
• Reading and writing to file
• PhpSpreadsheet Recipes