Auto Fill - Drag Fill"

From Documentation
m
m (correct highlight (via JWB))
 
(37 intermediate revisions by 5 users not shown)
Line 1: Line 1:
 
{{ZKSpreadsheetEssentialsPageHeader}}
 
{{ZKSpreadsheetEssentialsPageHeader}}
 +
 +
 +
{{Deprecated|url=http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials}}
  
 
__TOC__
 
__TOC__
  
−
You can drag the Fill Handle(the small black square at the bottom right) to copy cells in a simple dragging. The drag fill is base on Range's fill functions.
+
===Purpose===
 +
ZK Spreadsheet supports drag fill and auto fill cell's value.
 +
 
 +
===Drag Fill===
 +
The drag fill is based on <javadoc directory="zss" method="autofill(org.zkoss.zss.model.Range, java.lang.Integer)">org.zkoss.zss.model.Range</javadoc>
  
 +
====Copy====
 
1. Select a range<br/>
 
1. Select a range<br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_Select.png]]<br/><br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_Select.png]]<br/><br/>
−
2. Mouse over right bottom border, mouse cursor will change to cross, drag to expend selection range<br/>
+
2. Mouse over the right bottom border, the mouse cursor will change to cross, drag to expend the selection range<br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_Drag.png]]<br/><br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_Drag.png]]<br/><br/>
−
3. Release mouse to perform auto fill<br/>
+
3. Release the mouse to perform auto fill<br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill.png]]<br/><br/>
 
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill.png]]<br/><br/>
  
−
==Fill in range==
+
====Auto Linear Increase====
−
===Scenario===
+
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Linear1.png]]<br/><br/>
−
User can input how many rows or how many columns to fill from a selected cell.
+
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Linear2.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Linear3.png]]<br/><br/>
 +
====Month Auto Increase====
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Month1.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Month2.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Month3.png]]<br/><br/>
 +
====Week Auto Increase====
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Week1.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Week2.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Week3.png]]<br/><br/>
 +
====Date Auto Increase====
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Date1.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Date2.png]]<br/><br/>
 +
[[File:ZKSsEss_Spreadsheet_DragFill_AutoFill_Date3.png]]<br/><br/>
  
−
===ZUML Example===
+
===ZUML===
−
<source lang="xml" high="5,14">
+
<source lang="xml" highlight="14,24,27">
 
<zk>
 
<zk>
−
<div height="100%" width="100%" apply="demo.AutofillComposer">
+
<div height="100%" width="100%" apply="org.zkoss.zssessentials.config.AutofillComposer">
 
<div height="3px"></div>
 
<div height="3px"></div>
−
<div>
+
<hlayout>
−
Fill rows <intbox id="rows"/>, columns <intbox id="columns"/>
+
<vlayout>
−
</div>
+
<div>
−
<div>
+
1. Fill cells <intbox id="fillCells" value="2"/>
−
<button id="fillDown" label="Fill down" mold="trendy"></button>
+
<combobox id="fillOrientation" >
−
<button id="fillRight" label="Fill right" mold="trendy"></button>
+
<comboitem label="Fill Down" value="down"/>
−
<button id="fillLeft" label="Fill left" mold="trendy"></button>
+
<comboitem label="Fill Right" value="right"/>
−
<button id="fillUp" label="Fill up" mold="trendy"></button>
+
<comboitem label="Fill Left" value="left"/>
−
<button id="autofill" label="Auto fill" mold="trendy"></button>
+
<comboitem label="Fill Up" value="up"/>
−
</div>
+
</combobox>
−
<spreadsheet id="spreadsheet" src="/Untitled"
+
<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"  
 
maxrows="200"  
 
maxcolumns="40"
 
maxcolumns="40"
Line 40: Line 74:
 
</source>
 
</source>
  
−
===Source cell===
+
===Composer===
−
We can use onCellFocused event to get the cell as source to copy.
+
====Source Cell====
−
<source lang="java" high="4">
+
Use onCellFocused event to get the current cell. Use onCellSelection event to get the current selection range.
 +
<source lang="java" highlight="4,7">
 
Spreadsheet spreadsheet;
 
Spreadsheet spreadsheet;
 
Cell currentCell;
 
Cell currentCell;
 +
Rect selection;
 
public void onCellFocused$spreadsheet(CellEvent event) {
 
public void onCellFocused$spreadsheet(CellEvent event) {
 
currentCell = Utils.getCell(event.getSheet(), event.getRow(), event.getColumn());
 
currentCell = Utils.getCell(event.getSheet(), event.getRow(), event.getColumn());
 +
}
 +
public void onCellSelection$spreadsheet() {
 +
selection = spreadsheet.getSelection();
 
}
 
}
 
</source>
 
</source>
  
−
===Fill cell===
+
====Fill cells====
−
Fill down
+
<source lang="java" highlight="10,16,22,28">
−
<source lang="java" high="12,13">
+
Intbox fillCells;
−
Intbox rows;
+
Combobox fillOrientation;
−
Intbox columns;
+
public void onClick$fill() {
−
Button fillDown;
+
String orientation = (String)fillOrientation.getSelectedItem().getValue();
−
public void onClick$fillDown() {
+
if ("down".equals(orientation)) {
−
Integer rowNum = rows.getValue();
+
int leftCol = currentCell.getColumnIndex();
−
if (currentCell == null || rowNum == null)
+
int topRow = currentCell.getRowIndex();
−
return;
+
int rightCol = leftCol;
−
+
int btmRow = topRow + fillCells.getValue();
−
Sheet sheet = spreadsheet.getSelectedSheet();
+
Ranges.range(spreadsheet.getSelectedSheet(), topRow, leftCol, btmRow, rightCol).fillDown();
−
int top = currentCell.getRowIndex();
+
} else if ("right".equals(orientation)) {
−
int left = currentCell.getColumnIndex();
+
int leftCol = currentCell.getColumnIndex();
−
Range downRange = Ranges.range(sheet, top, left, top + rowNum, left);
+
int topRow = currentCell.getRowIndex();
−
downRange.fillDown();
+
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();
 +
}
 
}
 
}
 
</source>
 
</source>
−
 
+
====AutoFill====
−
Fill right
+
AutoFill can specify the fill type and destination range to fill.
−
<source lang="java" high="10,11">
+
<source lang="java" highlight="13,16,24,27">
−
Button fillRight;
+
public void onClick$autofill() {
−
public void onClick$fillRight() {
+
if (selection == null) {
−
Integer colNum = columns.getValue();
+
alert("Selection can not be empty");
−
if (currentCell == null || colNum == null)
 
 
return;
 
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();
 
 
−
Sheet sheet = spreadsheet.getSelectedSheet();
+
//fill down
−
int top = currentCell.getRowIndex();
+
Range autofillRange =  
−
int left = currentCell.getColumnIndex();
+
Ranges.range(worksheet, topRow, leftCol, btmRow + fillCells, leftCol);
−
Range rightRange = Ranges.range(sheet, top, left, top, left + colNum);
+
int type = Range.FILL_DEFAULT;
−
rightRange.fillRight();
+
String fillType = (String) autofillType.getSelectedItem().getValue();
−
}
+
if ("copy".equals(fillType)) {
−
</source>
+
type = Range.FILL_COPY;
−
 
+
}
−
Fill left
+
−
<source lang="java" high="10,11">
+
final Range srcRange =  
−
Button fillLeft;
+
Ranges.range(worksheet, topRow, leftCol, btmRow, rightCol);
−
public void onClick$fillLeft() {
 
−
Integer colNum = columns.getValue();
 
−
if (currentCell == null || colNum == null)
 
−
return;
 
 
 
−
Sheet sheet = spreadsheet.getSelectedSheet();
+
srcRange.autoFill(autofillRange, type);
−
int top = currentCell.getRowIndex();
 
−
int left = currentCell.getColumnIndex();
 
−
Range leftRange = Ranges.range(sheet, top, left - colNum, top, left);
 
−
leftRange.fillLeft();
 
−
}
 
−
</source>
 
−
 
 
−
Fill up
 
−
<source lang="java" high="10,11">
 
−
Button fillUp;
 
−
public void onClick$fillUp() {
 
−
Integer rowNum = rows.getValue();
 
−
if (currentCell == null || rowNum == null)
 
−
return;
 
−
 
 
−
Sheet sheet = spreadsheet.getSelectedSheet();
 
−
int top = currentCell.getRowIndex();
 
−
int left = currentCell.getColumnIndex();
 
−
Range upRange = Ranges.range(sheet, top - rowNum, left, top, left);
 
−
upRange.fillUp();
 
 
}
 
}
 
</source>
 
</source>
  
−
Auto fill, the auto fill can specify fill type and destination range to fill.
+
View the complete source of ZUML [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/WebContent/config/autoFill.zul autoFill.zul]
−
<source lang="java" high="13,17">
 
−
Button autoFill;
 
−
public void onClick$autoFill() {
 
−
Integer rowNum = rows.getValue();
 
−
Integer colNum = columns.getValue();
 
−
if (currentCell == null || rowNum == null || colNum == null)
 
−
return;
 
−
 
−
Sheet sheet = spreadsheet.getSelectedSheet();
 
−
int top = currentCell.getRowIndex();
 
−
int left = currentCell.getColumnIndex();
 
−
Range range = Ranges.range(sheet, top, left);
 
−
Range leftRange = Ranges.range(sheet, top, left, top , left + colNum);
 
−
range.autoFill(leftRange, Range.FILL_COPY);
 
  
−
Range downRange = Ranges.range(sheet, top, left, top + rowNum, left);
+
View the complete source of composer [https://code.google.com/p/zkbooks/source/browse/trunk/zssessentials/examples/src/org/zkoss/zssessentials/config/AutofillComposer.java AutofillComposer.java]
−
range.autoFill(downRange, Range.FILL_COPY);
 
−
}
 
−
</source>
 
  
 
=Version History=
 
=Version History=

Latest revision as of 12:54, 19 January 2022


DocumentationZK Spreadsheet EssentialsWorking with ZK SpreadsheetConfigure and Control ZK SpreadsheetAuto Fill - Drag Fill
Auto Fill - Drag Fill




Stop.png 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
ZKSsEss Spreadsheet DragFill Select.png

2. Mouse over the right bottom border, the mouse cursor will change to cross, drag to expend the selection range
ZKSsEss Spreadsheet DragFill Drag.png

3. Release the mouse to perform auto fill
ZKSsEss Spreadsheet DragFill AutoFill.png

Auto Linear Increase

ZKSsEss Spreadsheet DragFill AutoFill Linear1.png

ZKSsEss Spreadsheet DragFill AutoFill Linear2.png

ZKSsEss Spreadsheet DragFill AutoFill Linear3.png

Month Auto Increase

ZKSsEss Spreadsheet DragFill AutoFill Month1.png

ZKSsEss Spreadsheet DragFill AutoFill Month2.png

ZKSsEss Spreadsheet DragFill AutoFill Month3.png

Week Auto Increase

ZKSsEss Spreadsheet DragFill AutoFill Week1.png

ZKSsEss Spreadsheet DragFill AutoFill Week2.png

ZKSsEss Spreadsheet DragFill AutoFill Week3.png

Date Auto Increase

ZKSsEss Spreadsheet DragFill AutoFill Date1.png

ZKSsEss Spreadsheet DragFill AutoFill Date2.png

ZKSsEss Spreadsheet DragFill AutoFill Date3.png

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.


Last Update : 2022/01/19

Copyright © Potix Corporation. This article is licensed under GNU Free Documentation License.


Retrieved from "https://www.zkoss.org/_w/index.php?title=ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Configure_and_Control_ZK_Spreadsheet/Auto_Fill_-_Drag_Fill&oldid=53615"