Protect Worksheet or Workbook Elements

This article is out of date, please refer to http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials for more up to date information.

Purpose

Preventing end users from modifying important data, ZK Spreadsheet allow developers to apply protection to worksheet or cells.

Protection

On default, the sheet is not protected and the cell is locked. In this status, the cell can be modified.

Protected sheet
Protected sheet with locked cell. In this status, the cell can not be modified.

Protected sheet with unlocked cell. In this status, the cell can be modified

ZUML

<zk>
<div height="100%" width="100%" apply="org.zkoss.zssessentials.config.ProtectionComposer">
	<div>
    	        <label id="message" style="color: red; padding-right: 5px;"></label>
                <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 cells. Use the 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.