CODEWITHSUNDEEP
node.jsexcel4node
L.CH
L.CH

Reputation: 13

excel4node:How to lock a cell

using excel4node

Is there any methed set some cells locked or other cells is not locked using the package, excel4node; I found sheetProtection can lock the Workbook, but I want to lock one cell or some cells. I've tried to find a method. I think locked cell missing in cell style settings

Upvotes: 1

Views: 1524

Answers (3)

jack.benson
jack.benson

Reputation: 2363

I ran into this same problem. After a little research I discovered that cell protection is part of the styles of a cell. I had hoped that I could just modify the styles, but because the actual protection definition is a nested element and those are treated differently I could not.

There is now a pull request that has the necessary changes. Hopefully, it will get merged, but if not you can copy them into your node_modules folder to get the behavior you desire (which is what I did). Once those changes are in place, just create a new style and apply it to any cell you want to remain unlocked:

  const styles = {
    // Create a reusable style
    unlocked: workbook.createStyle({
      protection: { hidden: false, locked: false },
    }),
  };
  
  worksheet
        .cell(1, 1)
        .string(`test`)
        .style(styles.unlocked);

Then lock your sheet:

    const worksheetOptions = {
      sheetProtection: { objects: true, scenarios: true, sheet: true },
    };
    const worksheet = workbook.addWorksheet('Test', worksheetOptions);

Upvotes: 0

n-a-t-e
n-a-t-e

Reputation: 181

This is the workaround I use- it gives an error message when you try to edit a cell.

const lockCell = (worksheet, range) => {
  worksheet.addDataValidation({
    type: "textLength",
    error: "This cell is locked",
    operator: "equal",
    sqref: range,
    formulas: [""],
  });
};

eg, lockCell(worksheet,'B12');

Upvotes: 3

laminatefish
laminatefish

Reputation: 5246

No, currently this is not supported. See here

There isn't currently functionality to change the "locked" flag on a cell by cell basis. I can add that as an enhancement request.

Upvotes: 0

Related Questions