Create Xlsx Files with Document Security Settings

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.

Note:

This will NOT prevent the document from opening, it will only hold back the data from being updated.

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.