Skip to main content Skip to footer

SpreadJS and Locking Cells

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

MESCIUS inc.

comments powered by Disqus