ZK Spreadsheet Essentials/Working with Spreadsheet/Handling Data Model/Protection

Protect a Sheet

If you enable "Protect Sheet" for a sheet in Excel , Spreadsheet can read the setting and prevent you from editing the protected sheet. Spreadsheet's API also allow you to enable / disable protection and get protection status of a sheet. Let's use a simple example to demonstrate the usage:

The screenshot above is a simple application. There is a label on the right showing current sheet's protection status. The "true" means the sheet is under protection and cannot be edited. The "Toggle Protection" button can toggle protection status of current selected sheet. We will explain the "Current Cell Locked Status" and "Toggle Lock" button in next section.

The controller's source code of above application:

public class ProtectionComposer extends SelectorComposer<Component>{
	
	@Wire
	private Spreadsheet ss;
	@Wire
	private Label status;

	
	@Override
	public void doAfterCompose(Component comp) throws Exception {
		super.doAfterCompose(comp);
		updateSheetProtectionStatus(ss.getSelectedSheet());
	}
	
	@Listen("onClick = #toggleProtection")
	public void toggleProtection(){
		Sheet selectedSheet = ss.getSelectedSheet();
		if (selectedSheet.isProtected()){
			Ranges.range(selectedSheet).protectSheet(null);
		}else{
			Ranges.range(selectedSheet).protectSheet("password");
		}
		updateSheetProtectionStatus(selectedSheet);
	}
	
	@Listen("onSheetSelect = #ss")
	public void selectSheet(SheetSelectEvent event) {
		updateSheetProtectionStatus(event.getSheet());
	}
	
	private void updateSheetProtectionStatus(Sheet sheet){
		status.setValue(Boolean.toString(sheet.isProtected()));
	}
}

• Line 18: Get protection status of the selected sheet.
• Line 19: Disable protection of the selected sheet.
• Line 21: Enable protection of the selected sheet.

Unlock Specific Area of a Protected Sheet

When you protect a sheet in Excel, all cells are locked and cannot be edited by default. To enable some cells to be edited while leaving other cells locked, you can unlock the cells before you protect the worksheet. Spreadsheet can also read unlocked cells of a protected sheet configured in Excel. You can still edit the unlocked cells when loading it in Spreadsheet.

The screenshot below is a protected sheet with B2 unlocked. You can see the sheet protection status is "true, but cell lock status is "false" on the right panel when selecting B2. That means we can edit B2.

Besides, Spreadsheet also allows you to lock / unlock cells and retrieve locked status with API. In our example application, when you select cells, the panel on the right will display its lock status. Clicking the "Toggle Lock" buttons can switch lock status of cells.

Now, let's see the source code to know how to achieve it:

public class ProtectionComposer extends SelectorComposer<Component>{

	//omit codes for brevity

	@Listen("onClick = #toggleLock")
	public void toggleLock(){
		Range selection = Ranges.range(ss.getSelectedSheet(), ss.getSelection());
		CellStyle oldStyle = selection.getCellStyle();
		EditableCellStyle newStyle = selection.getCellStyleHelper().createCellStyle(oldStyle);
		newStyle.setLocked(!oldStyle.isLocked());
		selection.setCellStyle(newStyle);
		updateCellLockedStatus(newStyle.isLocked());
	}
	
	@Listen("onCellSelection = #ss")
	public void selectCells(CellSelectionEvent event) {
		CellStyle style = Ranges.range(ss.getSelectedSheet(), ss.getSelection()).getCellStyle();
		updateCellLockedStatus(style.isLocked());
	}
	
	private void updateCellLockedStatus(Boolean status){
		lockStatus.setValue(status.toString());
	}
}

• Line 6: Switch cells' locked status.
• Line 9,11: The cell's lock is one of style informations. To change a cell style is not just calling setter, please refer to Cell Style and Format for more details.
• Line 10: Use setLocked() to lock or unlock a cell.
• Line 12: Calling isLocked() to get lock status.
• Line 16: Update label with locked status of selected cells.