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

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

About this issue

Original URL
State: open
Created 5 years ago
Reactions: 5
Comments: 16

Most upvoted comments

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

+11

Kelendria on Feb 20, 2020

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.

preyash2047 on Apr 27, 2022

Can I just protect 2 specific columns and unprotect the rest of the sheet (including new columns/cells that will be added in the future by the user)? Did anyone manage to do that?

Basically, I’ve protected the whole sheet and then unprotected the cells which should be editable (e.g. all the cells under columns C, D, E) the issue with this kind of workaround is that the user can’t create new cells because the rest of the sheet is protected

DavidBronfen on May 11, 2020