Latest revision as of 12:54, 19 January 2022

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

Purpose

ZK Spreadsheet supports drag fill and auto fill cell's value.

Drag Fill

The drag fill is based on Range.autofill(Range, Integer)

Copy

1. Select a range

2. Mouse over the right bottom border, the mouse cursor will change to cross, drag to expend the selection range

3. Release the mouse to perform auto fill

Auto Linear Increase

Month Auto Increase

Week Auto Increase

Date Auto Increase

ZUML

<zk>
<div height="100%" width="100%" apply="org.zkoss.zssessentials.config.AutofillComposer">
	<div height="3px"></div>
	<hlayout>
		<vlayout>
			<div>
				1. Fill cells <intbox id="fillCells" value="2"/>
				<combobox id="fillOrientation" >
					<comboitem label="Fill Down" value="down"/>
					<comboitem label="Fill Right" value="right"/>
					<comboitem label="Fill Left" value="left"/>
					<comboitem label="Fill Up" value="up"/>
				</combobox>
				<button id="fill" label="Fill" mold="trendy"></button>
			</div>
		</vlayout>
		<div>
			2. AutoFill
			<intbox id="autoFillCells" value="6"/>
			<combobox id="autofillType">
				<comboitem label="Default" value="default"/>
				<comboitem label="Copy" value="copy"/>
			</combobox>
			<button id="autofill" label="Auto fill" mold="trendy"></button>
		</div>
	</hlayout>
	<spreadsheet id="spreadsheet" src="/WEB-INF/excel/config/autofill.xlsx"	
			maxrows="200" 
			maxcolumns="40"
			width="100%"
			height="450px"></spreadsheet>
</div>
</zk>

Composer

Source Cell

Use onCellFocused event to get the current cell. Use onCellSelection event to get the current selection range.

Spreadsheet spreadsheet;
Cell currentCell;
Rect selection;
public void onCellFocused$spreadsheet(CellEvent event) {
	currentCell = Utils.getCell(event.getSheet(), event.getRow(), event.getColumn());
}
public void onCellSelection$spreadsheet() {
	selection = spreadsheet.getSelection();
}

Fill cells

Intbox fillCells;
Combobox fillOrientation;
public void onClick$fill() {
	String orientation = (String)fillOrientation.getSelectedItem().getValue();
	if ("down".equals(orientation)) {
		int leftCol = currentCell.getColumnIndex();
		int topRow = currentCell.getRowIndex();
		int rightCol = leftCol;
		int btmRow = topRow + fillCells.getValue();
		Ranges.range(spreadsheet.getSelectedSheet(), topRow, leftCol, btmRow, rightCol).fillDown();
	} else if ("right".equals(orientation)) {
		int leftCol = currentCell.getColumnIndex();
		int topRow = currentCell.getRowIndex();
		int rightCol = leftCol + fillCells.getValue();
		int btmRow = topRow;
		Ranges.range(spreadsheet.getSelectedSheet(), topRow, leftCol, btmRow, rightCol).fillRight();
	} else if ("left".equals(orientation)) {
		int rightCol = currentCell.getColumnIndex();
		int leftCol = rightCol - fillCells.getValue();
		int topRow = currentCell.getRowIndex();
		int btmRow = topRow;
		Ranges.range(spreadsheet.getSelectedSheet(), topRow, leftCol, btmRow, rightCol).fillLeft();
	} else if ("up".equals(orientation)) {
		int btmRow = currentCell.getRowIndex();
		int topRow = btmRow - fillCells.getValue();
		int leftCol = currentCell.getColumnIndex();
		int rightCol = leftCol;
		Ranges.range(spreadsheet.getSelectedSheet(), topRow, leftCol, btmRow, rightCol).fillUp();
	}
}

AutoFill

AutoFill can specify the fill type and destination range to fill.

public void onClick$autofill() {
	if (selection == null) {
		alert("Selection can not be empty");
		return;
	}
		
	final Worksheet worksheet = spreadsheet.getSelectedSheet();
	final int topRow = selection.getTop();
	final int leftCol = selection.getLeft();
	final int btmRow = selection.getBottom();
	final int rightCol = selection.getRight();
		
	final int fillCells = autoFillCells.getValue();
		
	//fill down
	Range autofillRange = 
		Ranges.range(worksheet, topRow, leftCol, btmRow + fillCells, leftCol);
	int type = Range.FILL_DEFAULT;
	String fillType = (String) autofillType.getSelectedItem().getValue();
	if ("copy".equals(fillType)) {
		type = Range.FILL_COPY;
	}
	
	final Range srcRange = 
		Ranges.range(worksheet, topRow, leftCol, btmRow, rightCol);
		
	srcRange.autoFill(autofillRange, type);
}

View the complete source of ZUML autoFill.zul

View the complete source of composer AutofillComposer.java

Version History

Last Update : 2022/01/19

Version	Date	Content

All source code listed in this book is at Github.