Forums Home / Spread / SpreadJS
Posted by: tarun.softengg on 7 July 2021, 11:06 am EST
tarun.softengg
Posted 7 July 2021, 11:06 am EST
The feature i am looking at is Locking a column so that once I lock the column i should not be able to edit that column
Looks like this is doesn’t work when I set protection mode true it disables the whole sheet
““My simple requirement is disabled edit on a column””
If this can be done then how?
I have tried locking and protection mode but that doesn’t work
avinash.pathak
Posted 8 July 2021, 4:22 am EST
Hi Tarun,
This is expected behavior from SJS. Actually, by default, all the cells are marked locked so putting the protection will lock the whole sheet. You may use the locked method and lock/unlock range according to your need. Please refer to the following code snippet and attached sample that demonstrates the same.
sheet.suspendPaint();
sheet.setArray(0, 0, [
["", "A", "B", "C", "D"],
["E", 1, 2, 3, 4],
["G", 3, 8, 13, 18],
["H", 5, 9, 13, 17]
]);
sheet.options.isProtected = true;
//by default all cell marked as locked
//if want to change use locked method
//mark all column as unlocked
sheet.getRange(-1, 0, -1, sheet.getColumnCount()).locked(false);
//mark 2nd Column as locked
sheet.getRange(-1, 1, -1, 1).locked(true).backColor("lightgreen");
sheet.resumePaint();
sample: https://codesandbox.io/s/spread-js-starter-forked-fefgw?file=/src/index.js:414-907
protectionDemo: https://www.grapecity.com/spreadjs/demos/features/worksheet/protection#demo_source_name
Regards
Avinash
k.szabariova
Posted 8 July 2021, 8:27 am EST
With locking the sheet however adding new rows/columns, deleting, hiding etc is also disabled. You might need to use also
sheet.options.protectionOptions.allowResizeRows = true;
sheet.options.protectionOptions.allowResizeColumns = true;
sheet.options.protectionOptions.allowInsertRows = true;
etc, among others.
My use case is to lock only first row. Is there a way to prevent user to add a new row above the locked row? To be able to delete/hide rows, but not the locked row?
tarun.softengg
Posted 8 July 2021, 8:43 am EST
Let me try all these with my demo App
protectionOptions are clear in the documentation but not the locking process
Thanks for the quick reply
avinash.pathak
Posted 9 July 2021, 5:38 am EST
Hi Kristina,
For this, you may create a custom context menu and override the inOpenMenu method. Please refer to the following code snippet and attached sample that demonstrates the same.
function ContextMenu() {}
ContextMenu.prototype = new GC.Spread.Sheets.ContextMenu.ContextMenu(spread);
ContextMenu.prototype.onOpenMenu = function (
menuData,
itemsDataForShown,
hitInfo,
spread
) {
let sheet = spread.getActiveSheet();
let activeRow = sheet.getActiveRowIndex();
if (sheet.options.isProtected && hasLockedCells(sheet, activeRow)) {
itemsDataForShown.forEach((item) => {
if (
item.name === "gc.spread.insertRows" ||
item.name === "gc.spread.deleteRows"
) {
item.disable = true;
}
});
}
};
var contextMenu = new ContextMenu();
spread.contextMenu = contextMenu;
function hasLockedCells(sheet, rowIndex) {
if (sheet.options.isProtected) {
for (let col = 0; col < sheet.getColumnCount(); col++) {
if (sheet.getCell(rowIndex, col).locked()) return true;
}
}
return false;
}
sample: https://codesandbox.io/s/black-hill-hfe5k?file=/src/index.js
Regards,
Avinash
k.szabariova
Posted 13 July 2021, 4:16 am EST
Thank you very much Avinash, this is fantastic.
However there is small “bug” in your codesandbox, and that is that is user adds new row just below the first row (so by clicking on second row) that row will be locked too… (same as user adds several rows including second one).
What would be suggested fix for that?
avinash.pathak
Posted 13 July 2021, 6:49 am EST
Hi Kristina,
This is expected behavior. When inserting to row, the state information will be fetched from the above row state. Further, for handling this you may use the Row changed and change the state after insertion of rows. Please refer to the following code snippet and an attached sample that demonstrates the same.
sheet.bind(GC.Spread.Sheets.Events.RowChanged, function (e, info) {
let sheet = info.sheet;
if (
info.propertyName === "addRows" &&
hasLockedCells(sheet, info.rowIndex - 1)
) {
sheet
.getRange(info.row, 0, info.count, sheet.getColumnCount())
.locked(false);
}
});
sample: https://codesandbox.io/s/black-hill-hfe5k?file=/src/index.js:852-1167
Regards
Avinash
k.szabariova
Posted 14 July 2021, 11:02 am EST
Can I ask why are you editing ContextMenu actions via changing prototype? Could it be more simply done like so (just by overwriting onOpenMenu fn): ?
spread.contextMenu.onOpenMenu = (menuData, itemsDataForShown, hitInfo, spreadLocal) => {
const sheetLocal = spreadLocal.getActiveSheet();
const activeRow = sheetLocal.getActiveRowIndex();
if (activeRow === 0) {
itemsDataForShown.forEach((item) => {
if (item.name === 'gc.spread.insertRows' || item.name === 'gc.spread.deleteRows') {
item.disable = true;
}
});
}
};
avinash.pathak
Posted 15 July 2021, 8:50 am EST
Hi Kristina,
Yes, You can also achieve the required functionality by directly overriding the onOpenMenu method but it is recommended to create a separate contextemenu class and override the method on that context menu so that if you need to change something else (for example creating your own context menu element)on the context menu it should be on the context menu class(that demonstrates proper inheritance).
Hope this clarifies the issue.
Regards,
Avinash
