Setting a cell or row Protection Locked to true does not prevent editing in xlsx

See original GitHub issue

When setting a column or row level Protection lock, the resulting .xlsx file still has those columns/cells editable. It is possible to lock the entire worksheet, but not individual cells.

import * as Excel from 'exceljs';
import * as fs from 'fs';  

  const stream = fs.createWriteStream(
    `${FilePath}`,
    {
      flags: 'a',
    }
  );
const DefaultColumns = [
    'Col1',
    'Col2',
    'Col3',
    'Col4',
    'Col5',
  ];

  const placeHolders = new Array(DefaultColumns.length);
  const workbook = new Excel.Workbook();
  workbook.created = new Date();
  workbook.modified = new Date();

  const sheet = workbook.addWorksheet('sheet 1', {
    //Also not sure what this is supposed to show
    //No difference if datavalidation condition is failed
    pageSetup: { errors: 'dash' },
  });

  const headerRow = sheet.addRow(DefaultColumns);
  headerRow.eachCell((cell, rowNum) => {
    //@ts-ignore  Typing is wrong
    cell.note = `This is ${rowNum}`;
    //Doesn't do anything
    cell.protection = { locked: true };
  });

  //Also doesn't do anything
  headerRow.protection = { locked: true };
  headerRow.commit();

await workbook.xlsx.write(res);

Issue Analytics

State:
Created 4 years ago
Reactions:4
Comments:16

Top GitHub Comments

9reactions

Kelendriacommented, Feb 20, 2020

After setting the protection information on a cell or a row and before writing the file you have to protect the worksheet.

sheet.protect('thepassword', {
          formatCells: true,
          formatColumns: true,
          formatRows: true,
          insertRows: true,
          insertColumns: false,
          insertHyperlinks: true,
          deleteRows: true,
          deleteColumns: false,
          sort: true,
          autoFilter: true
        })

6reactions

preyash2047commented, Apr 27, 2022

I have solved it 🚀

there are 2 steps in implementing excel protection,

step 1: set the cell/ row as unlocked (by default all cells in excel are locked) -> for cell: sheet.getCell('A1').protection = { locked: false, lockText: false }; -> for row: sheet.getRow(2).protection = { locked: false, lockText: false };

step 2: protect the sheet with optional values -> with password: await sheet.protect('312dsasfafewr312edqwdqd213ed', { selectLockedCells: false, selectUnlockedCells: true, }); -> without password: await sheet.protect('', { selectLockedCells: false, selectUnlockedCells: true, });

Note: Flow to implement cell/row protection of Microsoft excel, as well as exceljs, are the same logically.

Top Results From Across the Web

Lock or unlock specific areas of a protected worksheet

By default, protecting a worksheet locks all cells so none of them are editable. To enable some cell editing, while leaving other cells...

How to Lock Cells for Editing and Protect Formulas - Excel ...

The great news is that you can lock or unlock any cell, or a whole range of cells, to keep your work protected....

Example: Enabling Cell protection in Worksheets - XlsxWriter

Note, that Excel's behavior is that all cells are locked once you set the default protection. Therefore you need to explicitly unlock cells...

How to lock or protect cells after data entry or input in Excel?

Unprotect password:="hello" MyRange.Locked = True Sheets("Sheet1").Protect password:="hello" End If End Sub And remember to change range (A1:D100) ...

How to Sort Locked Cells in Protected Worksheets | SoftArtisans

Even if Sort is enabled in the worksheet protection settings, if a user attempts to sort locked cells when a worksheet is protected,...