Protect Worksheet or Workbook Elements

Purpose

ZK Spreadsheet allow user to apply protection to worksheet or cells, to prevent user modify important data.

ZUML

<zk>
<div height="100%" width="100%" apply="org.zkoss.zssessentials.config.ProtectionComposer">
	<div>
		<Button id="toggleSheetProtection" label="Toggle sheet protection" mold="trendy"></Button>
		<Button id="toggleLockCells" label="Toggle lock cells" mold="trendy"></Button>
	</div>
	<spreadsheet id="spreadsheet" src="/WEB-INF/excel/config/autoFilter.xlsx"	
				maxrows="200" 
				maxcolumns="40"
				width="100%"
				height="450px"></spreadsheet>
</div>
</zk>

Composer

Protect Worksheet

Use Range.protectSheet(String) to set worksheet protection.

public void onClick$toggleSheetProtection(Event event) {
	final Worksheet sheet = spreadsheet.getSelectedSheet();
	boolean sheetProtection = !sheet.getProtect();
	Ranges.range(sheet).protectSheet(sheetProtection ? "password" : null);
}

Lock cells

Use CellStyle.setLocked(Boolean) to lock cell. Use Range.setStyle(CellStyle) to update cell style.

public void onClick$toggleLockCells(Event event) {
	final Worksheet sheet = spreadsheet.getSelectedSheet();
	boolean lock = !Utils.getOrCreateCell(sheet, topRow, leftCol).getCellStyle().getLocked();
	for (int r = topRow; r <= bottomRow; r++) {
		for (int c = leftCol; c <= rightCol; c++) {
			Cell cell = Utils.getOrCreateCell(sheet, r, c);
			CellStyle cellStyle = cell.getCellStyle();
			if (cellStyle.getLocked() != lock) {
				CellStyle newCellStyle = cloneStyle(cellStyle, sheet.getBook());
				newCellStyle.setLocked(lock);
				Ranges.range(sheet, r, c).setStyle(newCellStyle);
			}
		}
	}
}

View complete source of ZUML protection.zul

View complete source of composer ProtectionComposer.java

Version History

Version	Date	Content
2.1.0	May, 2011	Protect sheet and lock cell

All source code listed in this book is at Github.