CODEWITHSUNDEEP
javascriptexcelreadonlycells
mohd tahir
mohd tahir

Reputation: 161

How to make excel cells readonly using Javascript?

I have used ignite ui excel library to create an excel workbook using JavaScript. But unfortunately I didn't find any method to make columns/rows of excel read-only in their library. Is there a way we could make columns read-only before creating an excel sheet in JavaScript/Jquery?

Upvotes: 1

Views: 6096

Answers (3)

Harsh Anand
Harsh Anand

Reputation: 1

Excel.run(function (ctx) {
  //Worksheet
  var sheet = ctx.workbook.worksheets.getItem("Sheet1");

  //Entire Range
  var entireRange = sheet.getRange();
  entireRange.format.protection.locked = false;  

  //Specific Range
  var range = sheet.getRange("A1:B5");  

  return ctx.sync()
    .then(() => {
      //Set specific range "locked" status to true.
      range.format.protection.locked = true;
    })
    .then(ctx.sync)
    .then(() => {
      //Protect Entire sheet
      sheet.protection.protect({
        allowInsertRows: false,
        allowDeleteRows: false
      });
    });
}).catch(errorHandler);

Upvotes: 0

Rodrigo porras
Rodrigo porras

Reputation: 236

overview:

Im using nodejs and exceljs and I was searching for save new row data on my xlsx file while the file is open for read the info (no to save) on windows 10, but due to excel lock the file i was not able to write to the file, exceljs threw me an exception ( Error : EBUSY: resource busy or locked). i was searching for the property "ReadOnlyRecommended" on exceljs for save the file with ReadOnlyRecommended = true, this way i can read the file and at the same time write on it (in the original file, because it is read only), but unfortunately exceljs doesnt have such option. So after a long search I achieved this using fs.chmod from const fs = require('fs'); when i create for the first time or edit i use fs.chmodSync(excelFilePath, 0o600); for be able to write on the file but when i finish to write i use fs.chmodSync(excelFilePath, 0o400); to set the file on read only, this way when an user open the excel file this is in read only mode so excel will not lock the file. i hope this help somebody.

https://www.geeksforgeeks.org/node-js-fs-chmod-method/

Upvotes: 0

mohd tahir
mohd tahir

Reputation: 161

I achieved this with the following code/steps:

  1. By first making the entire excel sheet protected by using the code:

    sheet.protect();
    {sheet is my worksheet name}

  2. Then by unlocking certain cells of excel sheet using the code:

    sheet.getCell('H'+j).cellFormat().locked(false);
    {where H is the column name and j is a row number, an integer value}

Hope that helps someone else.

Upvotes: 2

Related Questions