Features and Usages"

From Documentation
m (correct highlight (via JWB))
 
(61 intermediate revisions by 3 users not shown)
Line 7: Line 7:
 
[[File:essentials-feature-ui.png | center | 900px]]
 
[[File:essentials-feature-ui.png | center | 900px]]
  
−
The above screenshot is Spreadsheet user interface, and each part of above is introduced as follows:
+
Above is a screenshot of ZK Spreadsheet's user interface, each section is introduced in the following:  
 
# Toolbar
 
# Toolbar
−
#:The toolbar contains all commonly-used functions including setting cell's style, alignment, border, background color, font, font color, merging (and unmerging) cells, sorting, auto filter, protection and grid line visibility. It has 2 tabs, another tab is used to insert charts, images, and hyperlinks for web page and email address:
+
#:The toolbar contains all commonly-used functions including setting cell's style, alignment, border, background color, font, font color, merging (and unmerging) of cells, sorting, auto filter, protection and grid line visibility. It has 2 tabs, another tab is used to insert charts, images, and hyperlinks for web page and email address:
−
#: [[File:essentials-feature-toolbar.png | 700px]]
 
 
#: The 3 leftmost buttons, "New Book", "Save Book", and "Export to PDF", are not built-in functions. You have to implement them by yourself.
 
#: The 3 leftmost buttons, "New Book", "Save Book", and "Export to PDF", are not built-in functions. You have to implement them by yourself.
 
# Formula bar
 
# Formula bar
Line 19: Line 18:
 
#: Right clicking on a cell, a column header, or a row header pops up a context menu. It contains most options of the toolbar and works like a shortcut.
 
#: Right clicking on a cell, a column header, or a row header pops up a context menu. It contains most options of the toolbar and works like a shortcut.
 
# Sheet bar
 
# Sheet bar
−
#: A list of all sheets of this book. You can navigate to any sheet by clicking on it. Click [[File:essentials-feature-addSheet.png]] can add a new sheet. Right clicking on a sheet pops a context menu and it allows you to delete, rename, protect, and move the clicking sheet.  
+
#: A list of all sheets in this book. You can navigate to any sheet by clicking on it. Click [[File:essentials-feature-addSheet.png]] can add a new sheet. Right clicking on a sheet pops a context menu, and it allows you to do some sheet operations.  
 
#: [[File:essentials-feature-sheet-contextmenu.png | center]]
 
#: [[File:essentials-feature-sheet-contextmenu.png | center]]
 +
#: Sheet navigation button makes you switch sheets conveniently.
 +
[[File:essentials-feature-sheet-navigation.png | center]]
 +
 +
= Features =
 +
 +
== Integrating with ZK Charts ==
 +
Every chart is now rendered by another ZK product ZK Charts which is more elegant and displayed with animation. When you hover your mouse pointer, it will show related data in a tooltip.
 +
[[File:zss-essential-zkchart.png | 900px | center]]
 +
 +
== Rich Text Editing ==
 +
 +
You can apply multiple styles to a cell with a rich text editor. To open a rich-text editor, right clicking a cell, select "Right Text Edit" in the context menu.
 +
 +
[[File:essential-richtexteditor.png | center]]
 +
 +
 +
== Comment ==
 +
To insert/edit/delete a comment, right clicking a cell, select corresponding item in the context menu.
 +
 +
[[File:essential-edit-comment.png | 550px]] [[File:essential-display-comment.png|350px]]
 +
 +
 +
== Support Different Zoom Level  ==
 +
Spreadsheet supports viewing in different zoom level. You can change zoom level within a browser according to your need.
 +
 +
The screenshot below is a Spreadsheet which is zoomed to 150%.
 +
[[File:zss-essentials-feature-zoom150.png | 800px| center]]
 +
 +
== Display Currency Symbol of Different Countries ==
 +
ZSS can display currency symbol of different countries such as $, ¥, ₩, €, and HKD on a cell with currency format.
 +
 +
== Localization of Number/Formula Input ==
 +
ZSS also accepts ','(comma)  or '.'(dot) as the decimal point for decimal numbers.
 +
 +
 +
== Smart Input ==
 +
When you enter a text in a cell with the default format (General), ZSS convert some special number inputs such as 1,234,567, $123456, ($123456), ($1,234,567), 1.2%, 123456E10 into a number value and set its corresponding Cell format automatically.
 +
 +
== Date Format ==
 +
Some date formats in ZSS are regional (starting with an '''asterisk, *''', same as Excel ) and some are international.
 +
[[File:zss-essentials-dateFormat.png | center]]
 +
Regional ones will change its displaying format according to the system locale, but international one doesn't change. <ref> [https://support.office.com/en-us/article/Format-a-date-the-way-you-want-8e10019e-d5d8-47a1-ba95-db95123d273e?ui=en-US&rs=en-US&ad=US&fromAR=1 Microsoft Office Support - Format a date the way you want] </ref>
 +
 +
<references/>
 +
 +
== Conditional Formatting ==
 +
since 3.9.0
 +
ZSS can display conditional formatting of an Excel file. This feature allows you to highlight a cell with a certain color according to the cell's value like the "Income" column below:
 +
[[File:zss-essentials-conditionalFormatting.png | center]]
 +
 +
* Modify conditional formatting is not supported yet.
 +
 +
 +
== Name Range ==
 +
ZSS cac read a named range in an xlsx file, so you can specify a named range in a formula e.g. <code>=SUM(source)</code>. To create a name range, please call [https://www.zkoss.org/javadoc/latest/zss/org/zkoss/zss/api/Range.html#createName-java.lang.String- Range::createName].
  
 
= Supported Hotkeys =
 
= Supported Hotkeys =
  
−
{| border="2" style="text-align:center; font-size:18; padding:10px;"
+
{| border="2" style=" font-size:16px;"
 
|+  
 
|+  
 
! <center>Hotkey</center> !! <center>Action</center>
 
! <center>Hotkey</center> !! <center>Action</center>
 
|-
 
|-
−
! Ctrl+B
+
! CTRL+B
 
| bold
 
| bold
 
|-
 
|-
−
! Ctrl+C
+
! CTRL+C
 
| copy
 
| copy
 
|-
 
|-
−
! Ctrl+I
+
! CTRL+I
 
| italic
 
| italic
 
|-
 
|-
−
! Ctrl+U  
+
! CTRL+U  
 
| underline
 
| underline
 
|-
 
|-
−
! Ctrl+V
+
! CTRL+V
 
| paste
 
| paste
 
|-
 
|-
−
! Ctrl+X
+
! CTRL+X
 
| cut
 
| cut
 
|-
 
|-
−
! Ctrl+Y (EE only)
+
! CTRL+Y (EE only)
 
| redo
 
| redo
 
|-
 
|-
−
! Ctrl+Z (EE only)
+
! CTRL+Z (EE only)
 
| undo
 
| undo
 
|-
 
|-
Line 57: Line 111:
 
! Esc
 
! Esc
 
| clear copy/cut clipboard
 
| clear copy/cut clipboard
 +
|-
 +
! CTRL+ARROW KEY
 +
| moves the selection box to the edge of the current data region in a sheet.
 
|}
 
|}
 +
  
 
<!--  
 
<!--  
 
We remove ctrl+D because it's inconsistent with Excel's.
 
We remove ctrl+D because it's inconsistent with Excel's.
 +
Ref:
 +
https://support.office.com/en-us/article/Excel-shortcut-and-function-keys-1798d9d5-842a-42b8-9c99-9b7213f0040f
 
-->
 
-->
  
Line 66: Line 126:
 
The following sections will introduce usages of some noticeable features which are all {{ZSS EE}}.
 
The following sections will introduce usages of some noticeable features which are all {{ZSS EE}}.
  
 +
== Copy & Paste ==
 +
We recommend you to copy and paste with '''Ctrl+c''' and '''Ctrl+v''' which works in all cases rather than clicking "paste" button on the toolbar and "paste" item on the context menu.
 +
 +
=== Copy inside One Spreadsheet ===
 +
* Such copy-paste works with '''Ctrl+c and Ctrl+v''', '''toolbar''', and '''context menu'''.
 +
* ZSS has full information of cells at both server and client-side, so such paste can retain all information of cells including styles, a formula, format, and data validation.
 +
* If you copy a whole column/row, ZSS also copy the width/height. But copying one or multiple cells doesn't copy the width and height to target cells.
 +
* When copy highlight is still active, it copies the highlighted cells, not from the system clipboard. You need to cancel the copy highlight first, then you can paste from a system clipboard.
 +
 +
=== Between 2 ZSS components ===
 +
* Copy-paste cells between 2 ZSS components also rely on the system clipboard, so it's a similar case like ZSS and Excel, only copying pure text.
 +
* If you want to copy a whole sheet to another ZSS component, please call [https://www.zkoss.org/javadoc/latest/zss/org/zkoss/zss/api/Range.html#cloneSheetFrom-java.lang.String-org.zkoss.zss.api.model.Sheet- Range.cloneSheetFrom()]. It can clone a sheet from another <code>Book</code> object.
  
−
== Special Paste ==
+
=== Between ZSS and Other Applications like Excel ===
 +
* Such copy-paste only works with '''Ctrl+c''' and '''Ctrl+v'''. Click "Paste" on the toolbar or context menu only works for copying cells inside one ZSS component.
 +
* Such copy-paste is an action between 2 applications (e.g. Excel and a browser) through a system clipboard. Currently, ZSS only extract text content from a system clipboard, so this copy-paste only pastes the "pure text" you see on the screen without any specified style on cells.
 +
* For example, a cell in Excel with a formula <code>=sum(1)</code>, if you copy the cell and paste it to ZSS, the cell in ZSS gets <code>1</code> as a number. Just like you type <code>1</code> in a ZSS cell.
 +
* If you enter edit mode, select the text <code>=sum(1)</code> and copy it in Excel, then paste to a cell in ZSS. ZSS gets a formula, just like you type a formula in a ZSS cell.
  
−
In addition to normal pasting, Spreadsheet also provides other special pasting on toolbar.
+
=== limitation ===
 +
ZSS can't paste a cell that has a multiline text into one cell of ZSS, and it will split the text into multiple cells by rows.
 +
 
 +
=== Special Paste ===
 +
 
 +
In addition to normal pasting, Spreadsheet also provides other special pasting options on the toolbar.
 
[[File:essentials-feature-paste.png | center]]
 
[[File:essentials-feature-paste.png | center]]
  
Line 81: Line 162:
  
  
−
After selecting "Custom sort" on the toolbar, a dialog appears. You can add sorting criteria at most to 3 columns. If your data includes column headings, make sure the "My data has headers" option is checked.
+
After selecting "Custom sort" on the toolbar, a dialog appears. You can add sorting criteria to 3 columns at the most. If your data includes column headings, make sure the "My data has headers" option is checked.
 
[[File:essentials-feature-customSortDialog.png | center]]
 
[[File:essentials-feature-customSortDialog.png | center]]
  
Line 88: Line 169:
 
[[File:essentials-feature-customSort-after.png | center]]
 
[[File:essentials-feature-customSort-after.png | center]]
  
−
== Filter ==
 
−
The Filter can screen out data you don't want quickly and easily. We usually select the cells we want to filter first and select "Filter" on the toolbar.
 
  
−
[[File:essentials-feature-filter-enable.png | center]]
+
== Auto Fill ==
 +
 
 +
Auto fill is a handy feature to fill cells with data in a particular pattern based on selected cells. Text will be copied and numbers and dates will be increased (or decreased).
 +
 
 +
To use this, You should select one or more cells and drag the fill handle across or down the cells that you want to fill.
 +
 
 +
[[File:essentials-feature-autoFill-select.png | center]]
 +
 
 +
Fill cells by dragging right, left, up, or down.
 +
[[File:essentials-feature-autoFill.png | center]]
  
 +
The supported cell content are number, weekday (full/short), month (full/short), and timestamp.
  
−
Then each column will have a drop-down button. Click the drop-down button to select data to filter.
+
== Format Cell ==
−
[[File:essentials-feature-filter.png | center]]
 
  
 +
A context menu appears when right clicking a cell, "Format Cell" provides 10 different categories with total of 47 formats to apply on cells.
 +
[[File:essentials-feature-formatCell.png | center]]
  
−
Those rows that contain the unchecked item will not appear in the original table unless you check them back.
 
−
[[File:essentials-feature-filter-after.png | center]]
 
  
−
If you insert more data after applying a filter, you can select "Reapply" on the toolbar to apply same criteria to new data.
 
  
−
== Auto Fill ==
+
== Sheet Protection ==
 +
If you enable "Protect Sheet" for a sheet in Excel, Spreadsheet will keep this setting when reading the Excel file, hence, when you edit a protected sheet, you will receive an alert message on the top left-hand corner.
 +
 
 +
When a sheet is under protection, you can only edit those unlocked cells. For those locked cells, you still can specify which action you allow users to do.
 +
 
 +
[[File:zss-essentials-feature-protection.png | center]]
 +
 
 +
== Filters ==
 +
The filter can help you screen out data and work with a subset of data in a range of cells without moving or deleting them.
 +
 
 +
When you click on the filter icon, there are 3 menu items: '''Filter''', '''Clear''', and '''Reapply''' related to filter.
 +
[[File:zss-essentials-filter-menu.png | center]]
 +
 
 +
 
 +
 
 +
Click "Filter" [[File:zss-essentials-filter-filter-icon.png]] can enable / disable filters. When filters are enabled, first row of each column will show up a drop-down arrow icon [[File:zss-essentials-filter-dropdown-icon.png]]. If you click the drop-down icon, a list of values appears and you can select from the list as a filtering criterion to apply on data.
 +
 
 +
[[File:zss-essentials-filter-enable.png | center]]
 +
 
 +
 
 +
After you select some values click [[File:zss-essentials-ok.png]], Spreadsheet will filter those data with selected values. Only those rows with matching criteria will be displayed while others stay hidden.
 +
 
 +
You can also filter by multiple columns. Filters are additive, which means that each filter is based on the existing filters and further reduces the subset of data.
 +
 
 +
Click "Clear" [[File:zss-essentials-filter-clear-icon.png]] removes all applied criteria and display all data available.
 +
 
 +
If you add new data row, you should click "Reapply" [[File:zss-essentials-filter-reapply-icon.png]]. Then, drop-down list will update its values, and applied criteria will also work on new data.
 +
 
 +
since 3.8.1
 +
'''Filter by search.'''
 +
When you enter text in the search box, it will instantly list and select all matched values. Press "Enter" and ZSS will filter your data with those matched values.
 +
[[File:zss-essentials-filterBySearching.png | center]]
 +
 
  
−
Auto fill is a handy feature to fill cells with data in particular pattern based on selected cells. Text will be copied and numbers and dates will be increased (or decreased).  
+
since 3.9.0
  
−
To use this, You should select one or more cells and drag the fill handle across or down the cells that you want to fill.
+
ZSS supports '''number filter''', '''color filter''', '''date filter''', and '''text filter'''.
 +
[[File:zss-essentials-colorFilter.png | center]]
  
−
[[File:essentials-feature-autoFill-select.png | center]]
 
  
−
Fill cells after dragging right.
+
[[File:zss-essentials-dateFilter.png | center]]
−
[[File:essentials-feature-autoFill.png | center]]
 
  
 +
== Data Validation ==
  
−
== Format Cell ==
 
  
−
In context menu appeared when right clicking a cell, "Format Cell" provides 10 different categories with total 47 formats to apply on cells.
+
Spreadsheet can read data validation settings of Excel files including validation criteria of lists, numbers, decimals, dates, or time. (Spreadsheet OSE will ignore validation settings.)
−
[[File:essentials-feature-formatCell.png | center]]
 
  
 +
[[File:zss-essentials-validation-dialog.png | center]]
  
−
== Validation ==
+
If the validation criteria is a list, the cell will appear a drop-down arrow icon. You can click the icon to select available values.
−
Spreadsheet can read your validation settings in an Excel file and it will show hints and validate data for you. (OSE will ignore validation settings.)
+
[[File:zss-essentials-validation-list.png | center]]
  
−
[[File:zss-essentials-feature-validationHint.png | center]]
 
−
<div style="text-align:center">'''Validation Hint'''</div>
 
  
 +
When you click on the cell with validation, the input message you set will appear automatically.
 +
[[File:zss-essentials-validation-message.png | center]]
  
−
If you enter an invalid data, Spreadsheet will prompt you with the error message you set in the Excel file.
 
−
[[File:zss-essentials-feature-validationFailed.png | center]]
 
  
 +
If your input violates validation criteria, an error alert will pop up.
 +
[[File:zss-essentials-validation-alert.png | center]]
  
−
If your validation criteria comes from a range of data, Spreadsheet will give you a drop-down list.
+
There are 3 types of alerts and each of them has a different icon in the dialog. For an error alert [[File:zss-essentials-validation-error-icon.png]], you can retry to enter again or cancel to revert back to the original value. For a warning alert [[File:zss-essentials-validation-warning-icon.png]] , you can click "Yes" to accept the invalid input, "No" to edit the invalid input, or "Cancel" to remove the invalid input. For a information alert [[File:zss-essentials-validation-information-icon.png]], you can click "OK" to accept the invalid value or "Cancel" to reject it.
−
[[File:zss-essentials-feature-validation-list.png | center]]
 
  
  
 +
* custom validation is not supported yet.
 +
<!-- org.zkoss.zss.range.impl.DataValidationHelper-->
  
  
 +
{{ZKSpreadsheetEssentials3HeadingToc}}
 
{{ZKSpreadsheetEssentialsPageFooter}}
 
{{ZKSpreadsheetEssentialsPageFooter}}

Latest revision as of 12:45, 19 January 2022


DocumentationZK Spreadsheet EssentialsFeatures and Usages
Features and Usages




Spreadsheet User Interface Overview

Essentials-feature-ui.png

Above is a screenshot of ZK Spreadsheet's user interface, each section is introduced in the following:

  1. Toolbar
    The toolbar contains all commonly-used functions including setting cell's style, alignment, border, background color, font, font color, merging (and unmerging) of cells, sorting, auto filter, protection and grid line visibility. It has 2 tabs, another tab is used to insert charts, images, and hyperlinks for web page and email address:
    The 3 leftmost buttons, "New Book", "Save Book", and "Export to PDF", are not built-in functions. You have to implement them by yourself.
  2. Formula bar
    It displays editing text or formula of current selected cell and can be used to enter or edit a formula or data.
  3. Sheet Area
    It displays the content of current selected sheet, and you usually perform most editing operations in this area.
  4. Context menu
    Right clicking on a cell, a column header, or a row header pops up a context menu. It contains most options of the toolbar and works like a shortcut.
  5. Sheet bar
    A list of all sheets in this book. You can navigate to any sheet by clicking on it. Click Essentials-feature-addSheet.png can add a new sheet. Right clicking on a sheet pops a context menu, and it allows you to do some sheet operations.
    Essentials-feature-sheet-contextmenu.png
    Sheet navigation button makes you switch sheets conveniently.
Essentials-feature-sheet-navigation.png

Features

Integrating with ZK Charts

Every chart is now rendered by another ZK product ZK Charts which is more elegant and displayed with animation. When you hover your mouse pointer, it will show related data in a tooltip.

Zss-essential-zkchart.png

Rich Text Editing

You can apply multiple styles to a cell with a rich text editor. To open a rich-text editor, right clicking a cell, select "Right Text Edit" in the context menu.

Essential-richtexteditor.png


Comment

To insert/edit/delete a comment, right clicking a cell, select corresponding item in the context menu.

Essential-edit-comment.png Essential-display-comment.png


Support Different Zoom Level

Spreadsheet supports viewing in different zoom level. You can change zoom level within a browser according to your need.

The screenshot below is a Spreadsheet which is zoomed to 150%.

Zss-essentials-feature-zoom150.png

Display Currency Symbol of Different Countries

ZSS can display currency symbol of different countries such as $, ¥, ₩, €, and HKD on a cell with currency format.

Localization of Number/Formula Input

ZSS also accepts ','(comma) or '.'(dot) as the decimal point for decimal numbers.


Smart Input

When you enter a text in a cell with the default format (General), ZSS convert some special number inputs such as 1,234,567, $123456, ($123456), ($1,234,567), 1.2%, 123456E10 into a number value and set its corresponding Cell format automatically.

Date Format

Some date formats in ZSS are regional (starting with an asterisk, *, same as Excel ) and some are international.

Zss-essentials-dateFormat.png

Regional ones will change its displaying format according to the system locale, but international one doesn't change. [1]

  1. ↑ Microsoft Office Support - Format a date the way you want

Conditional Formatting

since 3.9.0

ZSS can display conditional formatting of an Excel file. This feature allows you to highlight a cell with a certain color according to the cell's value like the "Income" column below:

Zss-essentials-conditionalFormatting.png
  • Modify conditional formatting is not supported yet.


Name Range

ZSS cac read a named range in an xlsx file, so you can specify a named range in a formula e.g. =SUM(source). To create a name range, please call Range::createName.

Supported Hotkeys

Hotkey
Action
CTRL+B bold
CTRL+C copy
CTRL+I italic
CTRL+U underline
CTRL+V paste
CTRL+X cut
CTRL+Y (EE only) redo
CTRL+Z (EE only) undo
Delete clear content
Esc clear copy/cut clipboard
CTRL+ARROW KEY moves the selection box to the edge of the current data region in a sheet.


Usage

The following sections will introduce usages of some noticeable features which are all Available in ZK Spreadsheet EE only.

Copy & Paste

We recommend you to copy and paste with Ctrl+c and Ctrl+v which works in all cases rather than clicking "paste" button on the toolbar and "paste" item on the context menu.

Copy inside One Spreadsheet

  • Such copy-paste works with Ctrl+c and Ctrl+v, toolbar, and context menu.
  • ZSS has full information of cells at both server and client-side, so such paste can retain all information of cells including styles, a formula, format, and data validation.
  • If you copy a whole column/row, ZSS also copy the width/height. But copying one or multiple cells doesn't copy the width and height to target cells.
  • When copy highlight is still active, it copies the highlighted cells, not from the system clipboard. You need to cancel the copy highlight first, then you can paste from a system clipboard.

Between 2 ZSS components

  • Copy-paste cells between 2 ZSS components also rely on the system clipboard, so it's a similar case like ZSS and Excel, only copying pure text.
  • If you want to copy a whole sheet to another ZSS component, please call Range.cloneSheetFrom(). It can clone a sheet from another Book object.

Between ZSS and Other Applications like Excel

  • Such copy-paste only works with Ctrl+c and Ctrl+v. Click "Paste" on the toolbar or context menu only works for copying cells inside one ZSS component.
  • Such copy-paste is an action between 2 applications (e.g. Excel and a browser) through a system clipboard. Currently, ZSS only extract text content from a system clipboard, so this copy-paste only pastes the "pure text" you see on the screen without any specified style on cells.
  • For example, a cell in Excel with a formula =sum(1), if you copy the cell and paste it to ZSS, the cell in ZSS gets 1 as a number. Just like you type 1 in a ZSS cell.
  • If you enter edit mode, select the text =sum(1) and copy it in Excel, then paste to a cell in ZSS. ZSS gets a formula, just like you type a formula in a ZSS cell.

limitation

ZSS can't paste a cell that has a multiline text into one cell of ZSS, and it will split the text into multiple cells by rows.

Special Paste

In addition to normal pasting, Spreadsheet also provides other special pasting options on the toolbar.

Essentials-feature-paste.png

You can select "Paste Special" to access all available pasting options in a dialog.

Essentials-feature-pasteSpecial.png

Custom Sort

The "Ascending" and "Descending" function can sort data by only one column but "Custom sort" can sort data by multiple columns.

Essentials-feature-customSort.png


After selecting "Custom sort" on the toolbar, a dialog appears. You can add sorting criteria to 3 columns at the most. If your data includes column headings, make sure the "My data has headers" option is checked.

Essentials-feature-customSortDialog.png


The sorting result of criteria above:

Essentials-feature-customSort-after.png


Auto Fill

Auto fill is a handy feature to fill cells with data in a particular pattern based on selected cells. Text will be copied and numbers and dates will be increased (or decreased).

To use this, You should select one or more cells and drag the fill handle across or down the cells that you want to fill.

Essentials-feature-autoFill-select.png

Fill cells by dragging right, left, up, or down.

Essentials-feature-autoFill.png

The supported cell content are number, weekday (full/short), month (full/short), and timestamp.

Format Cell

A context menu appears when right clicking a cell, "Format Cell" provides 10 different categories with total of 47 formats to apply on cells.

Essentials-feature-formatCell.png


Sheet Protection

If you enable "Protect Sheet" for a sheet in Excel, Spreadsheet will keep this setting when reading the Excel file, hence, when you edit a protected sheet, you will receive an alert message on the top left-hand corner.

When a sheet is under protection, you can only edit those unlocked cells. For those locked cells, you still can specify which action you allow users to do.

Zss-essentials-feature-protection.png

Filters

The filter can help you screen out data and work with a subset of data in a range of cells without moving or deleting them.

When you click on the filter icon, there are 3 menu items: Filter, Clear, and Reapply related to filter.

Zss-essentials-filter-menu.png


Click "Filter" Zss-essentials-filter-filter-icon.png can enable / disable filters. When filters are enabled, first row of each column will show up a drop-down arrow icon Zss-essentials-filter-dropdown-icon.png. If you click the drop-down icon, a list of values appears and you can select from the list as a filtering criterion to apply on data.

Zss-essentials-filter-enable.png


After you select some values click Zss-essentials-ok.png, Spreadsheet will filter those data with selected values. Only those rows with matching criteria will be displayed while others stay hidden.

You can also filter by multiple columns. Filters are additive, which means that each filter is based on the existing filters and further reduces the subset of data.

Click "Clear" Zss-essentials-filter-clear-icon.png removes all applied criteria and display all data available.

If you add new data row, you should click "Reapply" Zss-essentials-filter-reapply-icon.png. Then, drop-down list will update its values, and applied criteria will also work on new data. 

since 3.8.1

Filter by search. When you enter text in the search box, it will instantly list and select all matched values. Press "Enter" and ZSS will filter your data with those matched values.

Zss-essentials-filterBySearching.png


since 3.9.0

ZSS supports number filter, color filter, date filter, and text filter.

Zss-essentials-colorFilter.png


Zss-essentials-dateFilter.png

Data Validation

Spreadsheet can read data validation settings of Excel files including validation criteria of lists, numbers, decimals, dates, or time. (Spreadsheet OSE will ignore validation settings.)

Zss-essentials-validation-dialog.png

If the validation criteria is a list, the cell will appear a drop-down arrow icon. You can click the icon to select available values.

Zss-essentials-validation-list.png


When you click on the cell with validation, the input message you set will appear automatically.

Zss-essentials-validation-message.png


If your input violates validation criteria, an error alert will pop up.

Zss-essentials-validation-alert.png

There are 3 types of alerts and each of them has a different icon in the dialog. For an error alert Zss-essentials-validation-error-icon.png, you can retry to enter again or cancel to revert back to the original value. For a warning alert Zss-essentials-validation-warning-icon.png , you can click "Yes" to accept the invalid input, "No" to edit the invalid input, or "Cancel" to remove the invalid input. For a information alert Zss-essentials-validation-information-icon.png, you can click "OK" to accept the invalid value or "Cancel" to reject it.


  • custom validation is not supported yet.



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/Features_and_Usages&oldid=53560"