You can prevent users from editing cells by locking them. This can be useful if you have formulas, labels, or other data that you do not want the user to change. SpreadJS supports protecting sheets and unlocking or locking cells. Use the setIsProtected method to protect the sheet. Use the locked method to lock a cell. Cells can still be edited if the sheet setIsProtected method is false. Protecting a sheet prevents cells from being edited. You can allow the user to edit specific cells by protecting the sheet and only unlocking cells you want the user to edit. Data can be copied from locked cells. The protectionOption method can be used to specify what areas you want the user to be allowed to change when a sheet is protected. Areas include resizing rows or columns, filtering, and so on. The protectionOption method has the following options:
Option
Description
allowSelectLockedCells
True or undefined if the user can select locked cells.
allowSelectUnlockedCells
True or undefined if the user can select unlocked cells.
allowSort
True if the user can sort ranges.
allowFilter
True if the user can filter ranges.
allowEditObjects
True if the user can edit floating objects.
allowResizeRows
True if the user can resize rows.
allowResizeColumns
True if the user can resize columns.
The allowSort option applies to sorting in the filter dialog. The allowEditObjects option applies to selecting floating objects and selecting or editing comments. You can also use the SpreadJS Designer to protect a sheet and unlock or lock cells. Use the Format option under the Home tab. Then select the Protect Sheet or Unlock Cells menu option. SpreadJS Designer You can set the locked method for a cell, column, or row. For example: JavaScript
sheet.getCell(1,1).locked(false);
sheet.setValue(1,1,"unLocked");
sheet.getColumn(3).locked(false);
sheet.getRow(5).locked(false);
sheet.setIsProtected(true);
This example allows locked cells to be selected and rows to be resized. JavaScript
activeSheet.setValue(0, 0, 10);
activeSheet.setValue(1, 1, 100);
activeSheet.setValue(2, 0, 50);
activeSheet.setValue(3, 0, 40);
activeSheet.setValue(4, 0, 80);
activeSheet.setValue(5, 0, 1);
activeSheet.setValue(6, 0, 65);
activeSheet.setValue(7, 0, 20);
activeSheet.setValue(8, 0, 30);
activeSheet.setValue(9, 0, 35);
activeSheet.getCell(1, 1).locked(true);
activeSheet.setIsProtected(true);
activeSheet.protectionOption().allowResizeRows = true;
activeSheet.protectionOption().allowResizeColumns = false;
activeSheet.protectionOption().allowSelectLockedCells = true;
This example protects the sheet and unlocks cells. JavaScript
$(document).ready(function () {
var spread = new GcSpread.Sheets.Spread($("#ss").get(0),{sheetCount:3});
var activeSheet = spread.getActiveSheet();
//unlock the entire column C.
activeSheet.getColumn(2, GcSpread.Sheets.SheetArea.viewport).locked(false);
//unlock cell(1,3).
activeSheet.getCell(1, 3, GcSpread.Sheets.SheetArea.viewport).locked(false);
activeSheet.getCell(1, 3, GcSpread.Sheets.SheetArea.viewport).value("unlocked");
//Protect the sheet, then cells can only be edited when they are unlocked.
activeSheet.setIsProtected(true);
});
This example protects formula and label cells. JavaScript
activeSheet.addSpan(1, 1, 1, 3);
activeSheet.setValue(1, 1, "Store");
activeSheet.addSpan(1, 4, 1, 7);
activeSheet.setValue(1, 4, "Goods");
activeSheet.addSpan(2, 1, 1, 2);
activeSheet.setValue(2, 1, "Area");
activeSheet.addSpan(2, 3, 2, 1);
activeSheet.setValue(2, 3, "ID");
activeSheet.addSpan(2, 4, 1, 2);
activeSheet.setValue(2, 4, "Fruits");
activeSheet.addSpan(2, 6, 1, 2);
activeSheet.setValue(2, 6, "Vegetables");
activeSheet.addSpan(2, 8, 1, 2);
activeSheet.setValue(2, 8, "Foods");
activeSheet.addSpan(2, 10, 2, 1);
activeSheet.setValue(2, 10, "Total");
activeSheet.setValue(3, 1, "State");
activeSheet.setValue(3, 2, "City");
activeSheet.setValue(3, 4, "Grape");
activeSheet.setValue(3, 5, "Apple");
activeSheet.setValue(3, 6, "Potato");
activeSheet.setValue(3, 7, "Tomato");
activeSheet.setValue(3, 8, "Sandwich");
activeSheet.setValue(3, 9, "Hamburger");
activeSheet.addSpan(4, 1, 7, 1);
activeSheet.addSpan(4, 2, 3, 1);
activeSheet.addSpan(7, 2, 3, 1);
activeSheet.addSpan(10, 2, 1, 2);
activeSheet.setValue(10, 2, "Sub Total:");
activeSheet.addSpan(11, 1, 7, 1);
activeSheet.addSpan(11, 2, 3, 1);
activeSheet.addSpan(14, 2, 3, 1);
activeSheet.addSpan(17, 2, 1, 2);
activeSheet.setValue(17, 2, "Sub Total:");
activeSheet.addSpan(18, 1, 1, 3);
activeSheet.setValue(18, 1, "Total:");
activeSheet.setValue(4, 1, "NC");
activeSheet.setValue(4, 2, "Raleigh");
activeSheet.setValue(7, 2, "Charlotte");
activeSheet.setValue(4, 3, "001");
activeSheet.setValue(5, 3, "002");
activeSheet.setValue(6, 3, "003");
activeSheet.setValue(7, 3, "004");
activeSheet.setValue(8, 3, "005");
activeSheet.setValue(9, 3, "006");
activeSheet.setValue(11, 1, "PA");
activeSheet.setValue(11, 2, "Philadelphia");
activeSheet.setValue(14, 2, "Pittsburgh");
activeSheet.setValue(11, 3, "007");
activeSheet.setValue(12, 3, "008");
activeSheet.setValue(13, 3, "009");
activeSheet.setValue(14, 3, "010");
activeSheet.setValue(15, 3, "011");
activeSheet.setValue(16, 3, "012");
activeSheet.setFormula(10, 4, "=SUM(E5:E10)");
activeSheet.setFormula(10, 5, "=SUM(F5:F10)");
activeSheet.setFormula(10, 6, "=SUM(G5:G10)");
activeSheet.setFormula(10, 7, "=SUM(H5:H10)");
activeSheet.setFormula(10, 8, "=SUM(I5:I10)");
activeSheet.setFormula(10, 9, "=SUM(J5:J10)");
activeSheet.setFormula(17, 4, "=SUM(E12:E17)");
activeSheet.setFormula(17, 5, "=SUM(F12:F17)");
activeSheet.setFormula(17, 6, "=SUM(G12:G17)");
activeSheet.setFormula(17, 7, "=SUM(H12:H17)");
activeSheet.setFormula(17, 8, "=SUM(I12:I17)");
activeSheet.setFormula(17, 9, "=SUM(J12:J17)");
for (var i = 0; i < 14; i++) {
activeSheet.setFormula(4 + i, 10, "=SUM(E" + (5 + i).toString() + ":J" + (5 + i).toString() + ")");
}
activeSheet.setFormula(18, 4, "=E11+E18");
activeSheet.setFormula(18, 5, "=F11+F18");
activeSheet.setFormula(18, 6, "=G11+G18");
activeSheet.setFormula(18, 7, "=H11+H18");
activeSheet.setFormula(18, 8, "=I11+I18");
activeSheet.setFormula(18, 9, "=J11+J18");
activeSheet.setFormula(18, 10, "=K11+K18");
activeSheet.getCells(1, 1, 3, 10).backColor("#D9D9FF");
activeSheet.getCells(4, 1, 18, 3).backColor("#D9FFD9");
activeSheet.getCells(1, 1, 3, 10).hAlign(GcSpread.Sheets.HorizontalAlign.center);
activeSheet.setBorder(new GcSpread.Sheets.Range(1, 1, 18, 10), new GcSpread.Sheets.LineBorder("Black", GcSpread.Sheets.LineStyle.thin), { all: true });
activeSheet.setBorder(new GcSpread.Sheets.Range(4, 4, 3, 6), new GcSpread.Sheets.LineBorder("Black", GcSpread.Sheets.LineStyle.dotted), { inside: true });
activeSheet.setBorder(new GcSpread.Sheets.Range(7, 4, 3, 6), new GcSpread.Sheets.LineBorder("Black", GcSpread.Sheets.LineStyle.dotted), { inside: true });
activeSheet.setBorder(new GcSpread.Sheets.Range(11, 4, 3, 6), new GcSpread.Sheets.LineBorder("Black", GcSpread.Sheets.LineStyle.dotted), { inside: true });
activeSheet.setBorder(new GcSpread.Sheets.Range(14, 4, 3, 6), new GcSpread.Sheets.LineBorder("Black", GcSpread.Sheets.LineStyle.dotted), { inside: true });
activeSheet.setColumnWidth(0, 40);
activeSheet.setColumnWidth(1, 40);
activeSheet.setColumnWidth(2, 90);
activeSheet.setColumnWidth(3, 40);
activeSheet.setColumnWidth(4, 40);
activeSheet.setColumnWidth(11, 40);
activeSheet.setIsProtected(true);
function fillSampleData(range) {
for (var i = 0; i < range.rowCount; i++) {
for (var j = 0; j < range.colCount; j++) {
activeSheet.setValue(range.row + i, range.col + j, Math.ceil(Math.random() * 300));
activeSheet.getCell(range.row + i, range.col + j).locked(false);
}
}
}
fillSampleData(new GcSpread.Sheets.Range(4, 4, 6, 6));
fillSampleData(new GcSpread.Sheets.Range(11, 4, 6, 6));