New Features of ZK Spreadsheet 2.0"

From Documentation
m
m
Line 1: Line 1:
 
{{Template:UnderConstruction}}
 
{{Template:UnderConstruction}}
 
__TOC__
 
__TOC__
−
 
−
 
 
=Introduction=
 
=Introduction=
 +
ZK Spreadsheet is an Ajax component that delivers functionalities found in Microsoft Excel.<br/>
 +
Embedding ZK Spreadsheet in your Java web application to garner features such as:
 +
*Import your Excel 2003/2007 files to ZK Spreadsheet and make them collaborative online.
 +
*Control or configure an Ajax spreadsheet with versatile Java APIs.
 +
*Register event listeners on rows, columns, cells, or any range of selected cells.
 +
*Bind cells to back-end Java beans such that change in data is reflected on user interface automatically.
 +
*Extend on top of the 246 built in functions that come with ZK Spreadsheet in Java
  
−
This article introduces new features of ZK Spreadsheet 2.0.0.
+
=Features=
−
 
+
==Java Based Embeddable Ajax Spreadsheet==
−
=Rich Functionality=
+
{| border="1"
−
 
 
−
==Menu of Functions==
 
−
 
 
−
You can use menu item on the menubar to manipulate ZK Spreadsheet.
 
−
 
 
−
<gflash width="770" height="600">MenuFunction.swf</gflash>
 
−
 
 
−
==Styles==
 
−
 
 
−
===Change Style by Toolbar Button===
 
−
 
 
−
ZK Spreadsheet supports editing content of cell by Quick Access Toolbar.
 
−
 
 
−
<gflash width="760" height="600">FastIconToolbar.swf</gflash>
 
−
 
 
−
===Change Style by Quick Style Menu===
 
−
 
 
−
ZK Spreadsheet provides a convenience function for editing style -- Quick Style Menu --. Following will show an example of Quick Style Menu.
 
−
 
 
−
<gflash width="760" height="600">ChangeStyleByContextMenu.swf</gflash>
 
−
 
 
−
===Number Formatting===
 
−
 
 
−
ZK Spreadsheet supports number formatting.
 
−
 
 
−
<gflash width="785" height="600">NumberFormatting.swf</gflash>
 
−
 
 
−
===Rich Texts and Bullet Texts in a Cell===
 
−
 
 
−
ZK Spreadsheet supports applying different text formats in a cell.
 
−
 
 
−
<gflash width="770" height="600">RichFormattedText.swf</gflash>
 
−
 
 
−
==Multiple Sheets Tabs==
 
−
 
 
−
ZK Spreadsheet can show multiple sheets in different tabs.
 
−
 
 
−
<gflash width="780" height="600">Muti-Sheets.swf</gflash>
 
−
 
 
−
==Quick Keyboard Shortcut Keys==
 
−
 
 
−
ZK Spreadsheet supports several shortcut keys.
 
−
 
 
−
<gflash width="785" height="600">ShortcutKeys.swf</gflash>
 
−
 
 
−
==Adding Hyperlink==
 
−
 
 
−
You can insert hyperlink into ZK Spreadsheet.
 
−
 
 
−
<gflash width="770" height="600">Hyperlink.swf</gflash>
 
−
 
 
−
==Drag and Drop Editing of Selected Range==
 
−
 
 
−
ZK Spreadsheet supports drag-and-drop editing that you can use the mouse to move and copy cells, rows, and columns.
 
−
 
 
−
<gflash width="785" height="600">DragNDropCells.swf</gflash>
 
−
 
 
−
==Inserting or Deleting Row/Column/Cell==
 
−
 
 
−
End user can insert or delete row, column or cell in ZK Spreadsheet.
 
−
 
 
−
<gflash width="750" height="600">AlterRanges.swf</gflash>
 
−
 
 
−
==Auto Fill Cells==
 
−
 
 
−
You can drag to auto-fill cells on ZK Spreadsheet.
 
−
 
 
−
<gflash width="780" height="670">AutoFillCells.swf</gflash>
 
−
 
 
−
==Support Copy/Paste/Paste Special==
 
−
 
 
−
<gflash width="770" height="600">CopyPaste.swf</gflash>
 
−
 
 
−
==Support Ascending/Descending/Customizing Sorting==
 
−
 
 
−
ZK Spreadsheet does not only support normal ascending/descending sorting, but also supports customizing sorting.
 
−
 
 
−
<gflash width="770" height="600">Sorting.swf</gflash>
 
−
 
 
−
==Editing==
 
−
 
 
−
ZK Spreadsheet supports content editing.
 
−
 
 
−
<gflash width="785" height="600">EditingZSS.swf</gflash>
 
−
 
 
−
==Selection and Highlight==
 
−
 
 
−
Selection is a special rectangle to show an area of cells that been selected, it could be changed either by user or program. Highlight is a special rectangle too, but it only could be changed by program. You could use highlight to emphasize a area that are marked to do something such as copy.
 
−
 
 
−
<gflash width="785" height="600">SelectionHighlight.swf</gflash>
 
−
 
 
−
==Manipulating Rows and Columns==
 
−
 
 
−
You can manipulate row(s) and column(s) in ZK Spreadsheet. Even you can customize header of row(s) and column(s). Additionally, you also can decide whether to display header of row(s) and column(s) or not.
 
−
 
 
−
===Headers===
 
−
 
 
−
====Custom Row and Column Title====
 
−
 
 
−
<gflash width="770" height="600">ModifyHeader.swf</gflash>
 
−
 
 
−
====Hide/Unhide Row Headers and/or Column Headers====
 
−
 
 
−
<gflash width="770" height="600">HeaderDisplay.swf</gflash>
 
−
 
 
−
===Controlling/Editing===
 
−
 
 
−
====Freezing Row and Column====
 
−
 
 
−
<gflash width="785" height="600">FreezeColRow.swf</gflash>
 
−
 
 
−
====Hide and Unhide Rows and Columns by Context Menu====
 
−
 
 
−
<gflash width="770" height="600">HideRowCol.swf</gflash>
 
−
 
 
−
====Hide and Unhide Rows and Columns by Dragging====
 
−
 
 
−
<gflash width="785" height="600">HideRowColByDragging.swf</gflash>
 
−
 
 
−
====Maximum Rows and Columns====
 
−
 
 
−
You can decide to show how many rows and columns.
 
−
 
 
−
<gflash width="770" height="600">MaxColRow.swf</gflash>
 
−
 
 
−
==Loading Cells on Demand==
 
−
 
 
−
ZK Spreadsheet supports loading cells on demand.
 
−
 
 
−
[[image:LoadCellOnDemand.png]]
 
−
 
 
−
==Image==
 
−
 
 
−
Users can open excel file which includes image on ZK Spreadsheet.
 
−
 
 
−
<gflash width="830" height="670">LoadImage.swf</gflash>
 
−
 
 
−
==Display or Hide Grid Lines==
 
−
 
 
−
You can deside to hide or display grid lines on ZK Spreadsheet.
 
−
 
 
−
<gflash width="790" height="670">GridLines.swf</gflash>
 
−
 
 
−
=Chart=
 
−
 
 
−
==Support Showing Chart==
 
−
 
 
−
ZK Spreadsheet supports presenting chart. Additionally, the chart in ZK Spreadsheet is '''"live"'''.
 
−
 
 
−
<gflash width="785" height="600">ColumnChart.swf</gflash>
 
−
 
 
−
=Report=
 
−
 
 
−
==PDF Exporting==
 
−
 
 
−
You can export all sheets, current sheet, or selection range in ZK Spreadsheet to a PDF file.
 
−
 
 
−
<gflash width="800" height="600">ExportPDF.swf</gflash>
 
−
 
 
−
==Support Exporting Excel Chart to PDF==
 
−
 
 
−
ZK Spreadsheet supports export sheet chart to a PDF file.
 
−
 
 
−
<gflash width="750" height="600">ExportChartPDF.swf</gflash>
 
−
 
 
−
=Events=
 
−
ZK Spreadsheet supports numerous events such as [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners/Editing_Events Editing Events], [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners/Key_Events Key Events], [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners/Mouse_Events Mouse Events], [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners/Selection_Events Selection Events] and [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners/Hyperlink_Event Hyperlink Event]. You can listen to these events to develop highly interactive spreadsheet applications. If you want to know more detail, please reference
 
−
[http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners Implement ZK Spreadsheet Event Listeners]
 
−
 
 
−
==Editing Events==
 
−
There are three editing events that ZK Spreadsheet supports: '''onStartEditing''', '''onStopEditing''', '''onEditboxEditing'''.
 
−
 
 
−
<gflash width="770" height="600">EditingEvents.swf</gflash>
 
−
 
 
−
==Key Events==
 
−
Users can write key event listeners for key events such as ctrl+C, ctrl+V to call relative ZK Spreadsheet APIs to really copy and paste cell(s).
 
−
 
 
−
[[File:KeyEvent.png]]
 
−
 
 
−
==Mouse Events==
 
−
There are two types of Mouse events. One is cell mouse events such as '''onCellClick''', '''onCellDoubleClick''' and '''onCellRightClick'''; and the other is header mouse events such as '''onHeaderClick''', '''onHeaderDoubleClick''' and '''onHeaderRightClick'''. Following is an onHeaderRightClick event example. When end user right clicks the header of column or row, onHeaderRightClick event will be triggered. You can write an event listener to listen this event and popup context menu.
 
−
 
 
−
<gflash width="800" height="600">OnHeaderRightClick.swf</gflash>
 
−
 
 
−
==Selection Events==
 
−
You can write selection event listeners for selection events. A very simple example is like following. When end user select a cell, selection event will be triggered. Formula bar and name box listen to the event and show the value and cell name of the selected cell, respectively.
 
−
 
 
−
 
 
−
[[File:SelectionEvents.png]]
 
−
 
 
−
==Hyperlink Event==
 
−
ZK Spreadsheet supports hyperlink event: This event will be triggered after user clicked hyperlink on ZK Spreadsheet.
 
−
 
 
−
=Functions=
 
−
 
 
−
==Built-in Functions==
 
−
 
 
−
ZK Spreadsheet supports following formula functions: (Total: 246)
 
−
 
 
−
===Math Formula Functions===
 
−
 
 
−
Total 60 Math Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
 
|-
 
|-
−
|ABS
+
!Feature  || Description || Demo
 
|-
 
|-
−
|ACOS
+
|| '''Java Bean Binding'''
 +
||  ZK Spreadsheet resolves name expressions in cells to make bindings to back-end Java beans automatically.
 +
||  [/_w/images/5/5e/DataBinding_Chart.png See Illustration]
 
|-
 
|-
−
|ACOSH
+
||  '''Java APIs'''
 +
||  The backend data model and formula evaluation engine is built on top of  the Apache POI library. Developers could control, configure, or extend the Ajax spreadsheet entirely in Java.
 +
|| 
 
|-
 
|-
−
|ASIN
+
||  '''Event Driven'''
 +
||  A comprehensive set of events were designed to allow developers to create a highly interactive spreadsheet application.
 +
|| [/_w/images/8/88/EditingEvents.swf See Demo-Editing Events]<br/> [/_w/images/c/cd/KeyEvent.png See Illustration-Key Events] <br/> [/_w/images/1/1b/OnHeaderRightClick.swf See Demo-Mouse Events] <br/> [/_w/images/4/40/SelectionEvents.png See Illustration-Selection Events] <br/> [[ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_ZK_Spreadsheet_Event_Listeners | Read Doc]]
 
|-
 
|-
−
|ASINH
+
||  '''Readily Embeddable'''
 +
||  ZK Spreadsheet is a ZK component which developers could embed into any Java web application at its presentation tier.
 +
|| 
 
|-
 
|-
−
|ATAN
+
||  '''Custom Functions'''
 +
||  ZK Spreadsheet allows developers to implement custom formula functions on top of the 246 built in ones. End users can use custom these functions just like any other built in functions.
 +
||  [[ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_Your_Own_Formula_Functions/As_Simple_as_a_Java_Static_Method | Read Doc]]
 
|-
 
|-
−
|ATAN2
+
|}
−
|-
+
<br/>
−
|ATANH
+
==Performance Boosts==
−
|-
+
{| border="1"
−
|CEILING
 
−
|-
 
−
|COMBIN
 
−
|-
 
−
|COS
 
−
|-
 
−
|COSH
 
−
|-
 
−
|DEGREES
 
−
|-
 
−
|EVEN
 
−
|-
 
−
|EXP
 
−
|-
 
−
|FACT
 
−
|-
 
−
|FACTDOUBLE
 
−
|-
 
−
|FLOOR
 
−
|-
 
−
|GCD
 
−
|-
 
−
|INT
 
−
|-
 
−
|LCM
 
 
|-
 
|-
−
|LN
+
!Feature  || Description || Demo
 
|-
 
|-
−
|LOG
+
||  '''Cell Load on Demand'''
 +
||  Like other tabular components in ZK, ZK Spreadsheet supports “Load on Demand” which only the cells in view are loaded.
 +
||  [/_w/images/1/1a/LoadCellOnDemand.png See Illustration]
 
|-
 
|-
−
|LOG10
+
||  '''Efficient Dependency Tracking'''
 +
|| 
 +
|| 
 
|-
 
|-
−
|MDETERM
 
−
|-
 
−
|MINVERSE
 
−
|-
 
−
|MMULT
 
−
|-
 
−
|MOD
 
−
|-
 
−
|MROUND
 
−
|-
 
−
|MULTINOMIAL
 
−
|-
 
−
|ODD
 
−
|-
 
−
|PI
 
−
|-
 
−
|POWER
 
−
|-
 
−
|PRODUCT
 
−
|-
 
−
|QUOTIENT
 
−
|-
 
−
|RADIANS
 
−
|-
 
−
|RAND
 
−
|-
 
−
|RANDBETWEEN
 
−
|-
 
−
|ROMAN
 
−
|-
 
−
|ROUND
 
−
|-
 
−
|ROUNDDOWN
 
−
|-
 
−
|ROUNDUP
 
−
|-
 
−
|SERIESSUM
 
−
|-
 
−
|SIGN
 
−
|-
 
−
|SIN
 
−
|-
 
−
|SINH
 
−
|-
 
−
|SQRT
 
−
|-
 
−
|SQRTPI
 
−
|-
 
−
|SUBTOTAL
 
−
|-
 
−
|SUM
 
−
|-
 
−
|SUMIF
 
−
|-
 
−
|SUMIFS
 
−
|-
 
−
|SUMPRODUCT
 
−
|-
 
−
|SUMSQ
 
−
|-
 
−
|SUMX2MY2
 
−
|-
 
−
|SUMX2PY2
 
−
|-
 
−
|SUMXMY2
 
−
|-
 
−
|TAN
 
−
|-
 
−
|TANH
 
−
|-
 
−
|TRUNC
 
 
|}
 
|}
−
 
+
<br/>
−
===Statistical Formula Functions===
+
==Imort/Export Worksheet==
−
 
+
{| border="1"
−
Total 45 Statistical Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
−
|-
 
−
|AVEDEV
 
−
|-
 
−
|AVERAGE
 
 
|-
 
|-
−
|AVERAGEA
+
!Feature  || Description || Demo
 
|-
 
|-
−
|AVERAGEIF
+
||  '''Import/Export Excel 2003/2007 Files'''
 +
|| 
 +
|| 
 
|-
 
|-
−
|AVERAGEIFS
+
||  '''Export to PDF'''
 +
||  The “Export to PDF” dialog gives users a set of options to customize PDF printing, including the worksheet size, ranging from printing all worksheets to printing just a selection of cells.
 +
||  [/_w/images/5/5a/ExportPDF.swf See Demo]
 
|-
 
|-
−
|BETADIST
 
−
|-
 
−
|BETAINV
 
−
|-
 
−
|BINOMDIST
 
−
|-
 
−
|CHIDIST
 
−
|-
 
−
|CHIINV
 
−
|-
 
−
|COUNT
 
−
|-
 
−
|COUNTA
 
−
|-
 
−
|COUNTBLANK
 
−
|-
 
−
|COUNTIF
 
−
|-
 
−
|DEVSQ
 
−
|-
 
−
|EXPONDIST
 
−
|-
 
−
|FDIST
 
−
|-
 
−
|FINV
 
−
|-
 
−
|GAMMADIST
 
−
|-
 
−
|GAMMAINV
 
−
|-
 
−
|GAMMALN
 
−
|-
 
−
|GEOMEAN
 
−
|-
 
−
|GROWTH
 
−
|-
 
−
|HARMEAN
 
−
|-
 
−
|HYPGEOMDIST
 
−
|-
 
−
|INTERCEPT
 
−
|-
 
−
|KURT
 
−
|-
 
−
|LARGE
 
−
|-
 
−
|MAX
 
−
|-
 
−
|MAXA
 
−
|-
 
−
|MEDIAN
 
−
|-
 
−
|MIN
 
−
|-
 
−
|MINA
 
−
|-
 
−
|MODE
 
−
|-
 
−
|NORMDIST
 
−
|-
 
−
|POISSON
 
−
|-
 
−
|SKEW
 
−
|-
 
−
|SLOPE
 
−
|-
 
−
|SMALL
 
−
|-
 
−
|STDEV
 
−
|-
 
−
|STDEVA
 
−
|-
 
−
|TDIST
 
−
|-
 
−
|TINV
 
−
|-
 
−
|VAR
 
−
|-
 
−
|WEIBULL
 
 
|}
 
|}
−
 
+
<br/>
−
===Text Formula Functions===
+
==Charts and Images==
−
 
+
{| border="1"
−
Total 24 Text Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
 
|-
 
|-
−
|CHAR
+
!Feature  || Description || Demo
 
|-
 
|-
−
|CLEAN
+
||  '''Work with Interactive Excel Charts'''
 +
||  Charts created in Excel can be imported to ZK Spreadsheet. Changes in the data are reflected on the charts automatically.
 +
||  [/_w/images/2/2c/ColumnChart.swf See Demo]
 
|-
 
|-
−
|CODE
+
||  '''Export Excel charts to PDF'''
 +
||  Export Excel charts to PDF
 +
||  [/_w/images/3/3f/ExportChartPDF.swf See Demo]
 
|-
 
|-
−
|CONCATENATE
+
||  '''Insert Images and Import Image Embedded Excel Worksheets'''
 +
|| Users can insert images into cells and images embedded in an Excel worksheet can be rendered in ZK Spreadsheet when it is imported into ZK Spreadsheet.
 +
||  [/_w/images/0/00/LoadImage.swf See Demo]
 
|-
 
|-
−
|DOLLAR
 
−
|-
 
−
|EXACT
 
−
|-
 
−
|FIND, FINDB
 
−
|-
 
−
|FIXED
 
−
|-
 
−
|LEFT, LEFTB
 
−
|-
 
−
|LEN, LENB
 
−
|-
 
−
|LOWER
 
−
|-
 
−
|MID, MIDB
 
−
|-
 
−
|PHONETIC
 
−
|-
 
−
|PROPER
 
−
|-
 
−
|REPLACE, REPLACEB
 
−
|-
 
−
|REPT
 
−
|-
 
−
|RIGHT, RIGHTB
 
−
|-
 
−
|SEARCH, SEARCHB
 
−
|-
 
−
|SUBSTITUTE
 
−
|-
 
−
|T
 
−
|-
 
−
|TEXT
 
−
|-
 
−
|TRIM
 
−
|-
 
−
|UPPER
 
−
|-
 
−
|VALUE
 
 
|}
 
|}
−
 
+
<br/>
−
===Information Formula Functions===
+
==Worksheet Operations==
−
 
+
{| border="1"
−
Total 16 Information Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
−
|-
 
−
|ERROR.TYPE
 
−
|-
 
−
|INFO
 
 
|-
 
|-
−
|ISBLANK
+
!Feature  || Description || Demo
 
|-
 
|-
−
|ISERR
+
||  '''Menu Commands'''
 +
||  Commands such as file I/O, worksheet and cell formatting and styling can all be executed on the top menu bar.
 +
|| [/_w/images/5/51/MenuFunction.swf See Demo]
 
|-
 
|-
−
|ISERROR
+
||  '''Drag and Drop Editing of Selected Cells'''
 +
||  Use drag and drop to copy or move selected cell on worksheets.
 +
||  [/_w/images/9/9c/DragNDropCells.swf See Demo]
 
|-
 
|-
−
|ISEVEN
+
||  '''Insert/Delete Rows/Columns/Cells'''
 +
|| Use context menu to insert or delete Rows, Columns, or Cells.
 +
|| [/_w/images/a/a2/AlterRanges.swf See Demo]
 
|-
 
|-
−
|ISLOGICAL
+
||  '''Auto-fill Cells'''
 +
||  Select and drag a selection of cells to copy their content to adjacent cells.
 +
||  [/_w/images/6/69/AutoFillCells.swf See Demo]
 
|-
 
|-
−
|ISNA
+
||  '''Copy-Paste Cells with Paste Special Support'''
 +
|| Users can copy and paste selected cells with the option of invoking the Paste Special dialog to choose and form a specific paste special task.
 +
||  [/_w/images/b/b4/CopyPaste.swf See Demo]
 
|-
 
|-
−
|ISNONTEXT
+
||  '''Sorting with Custom Sort Wizard'''
 +
|| ZK Spreadsheet not only supports ascending/descending sorting, but also “Custom Sort” through a wizard where user can enter series of sorting conditions.
 +
|| [/_w/images/c/cf/Sorting.swf See Demo]
 
|-
 
|-
−
|ISNUMBER
+
||  '''Keystroke Shortcuts'''
 +
|| ZK Spreadsheet has an array of built in shortcuts. The list of shortcuts can be invoked by selecting Help > Cheatsheet.
 +
||  [/_w/images/4/41/ShortcutKeys.swf See Demo]
 
|-
 
|-
−
|ISODD
+
||  '''Selected Cells Highlighting'''
 +
||
 +
||  [/_w/images/8/8b/SelectionHighlight.swf See Demo]
 
|-
 
|-
−
|ISREF
+
||  '''In-place Cell Editing'''
 +
||
 +
||  [/_w/images/9/9e/EditingZSS.swf See Demo]
 
|-
 
|-
−
|ISTEXT
 
−
|-
 
−
|N
 
−
|-
 
−
|NA
 
−
|-
 
−
|TYPE
 
 
|}
 
|}
−
 
+
<br/>
−
===Date & Time Functions===
+
==Worksheet Formatting==
−
 
+
{| border="1"
−
Total 15 Date & Time Functions:
 
−
 
 
−
{| class="wikitable"
 
−
|-
 
−
|DATE
 
−
|-
 
−
|DATEVALUE
 
−
|-
 
−
|DAY
 
−
|-
 
−
|DAYS360
 
 
|-
 
|-
−
|HOUR
+
!Feature  || Description || Demo
 
|-
 
|-
−
|MINUTE
+
||  '''Multiple Worksheets'''
 +
||  Users can switch between multiple worksheets by clicking on the desired worksheet tabs. The worksheet content is loaded on demand.
 +
|| [/_w/images/d/d4/Muti-Sheets.swf See Demo]
 
|-
 
|-
−
|MONTH
+
||  '''Freeze Rows and Columns'''
 +
|| Rows and columns can be made frozen so that desired rows or columns are locked in view.
 +
||  [/_w/images/d/d1/FreezeColRow.swf See Demo]
 
|-
 
|-
−
|NETWORKDAYS
+
||  '''Hide/Show Rows and Columns'''
 +
||Rows and columns can be shown or hidden using context menu or by simply dragging the rows or columns to collapse or expand.
 +
|| [/_w/images/b/b0/HideRowCol.swf See Demo - Using Context Menu]<br/> [/_w/images/1/12/HideRowColByDragging.swf See Demo - Using Drag and Collapse/Expand]
 
|-
 
|-
−
|NOW
+
||  '''Hide/Show Row/Column Headers'''
 +
||  Headers for rows and columns can be made visible/invisible
 +
||  [/_w/images/0/0a/HeaderDisplay.swf See Demo]
 
|-
 
|-
−
|SECOND
+
||  '''Modify Row/Column Headers'''
 +
||
 +
||  [/_w/images/1/12/ModifyHeader.swf See Demo]
 
|-
 
|-
−
|TIME
+
||  '''Hide/Show Grid Lines'''
 +
||Users can make grid visible/invisible with a click of the check-box.
 +
|| [/_w/images/c/c5/GridLines.swf See Demo]
 
|-
 
|-
−
|TIMEVALUE
+
||  '''Set Maximum Rows and Columns Allowed'''
 +
||
 +
|| [/_w/images/0/04/MaxColRow.swf See Demo]
 
|-
 
|-
−
|TODAY
 
−
|-
 
−
|WEEKDAY
 
−
|-
 
−
|YEAR
 
 
|}
 
|}
−
 
+
<br/>
−
===Logical Formula Functions===
+
==Cell Formatting and Styling==
−
 
+
{| border="1"
−
Total 7 Logical Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
 
|-
 
|-
−
|AND
+
!Feature  || Description || Demo
 
|-
 
|-
−
|FALSE
+
||  '''Number Formatting'''
 +
||  Format number in cells using friendly dialog.
 +
|| [/_w/images/b/bf/NumberFormatting.swf See Demo]
 
|-
 
|-
−
|IF
+
||  '''Text Formatting'''
 +
|| Text formatting options include font, boldfaced, italic, among others. Any text format imported from an Excel worksheet would remain consistent in ZK Spreadsheet.
 +
|| [/_w/images/5/52/RichFormattedText.swf See Demo]
 
|-
 
|-
−
|IFERROR
+
||  '''Hyperlink Creation'''
 +
|| A cell content can be made into a hyperlink via the "Insert Hyperlink" dialog.
 +
|| [/_w/images/c/cd/Hyperlink.swf See Demo]
 
|-
 
|-
−
|NOT
+
||  '''Text Alignment'''
 +
||  Text in cells can be made aligned to right, center, and left.
 +
|| 
 
|-
 
|-
−
|OR
+
||  '''Styling Toolbar'''
 +
|| Change the styling in cell content using toolbar.
 +
|| [/_w/images/d/d4/FastIconToolbar.swf See Demo]
 
|-
 
|-
−
|TRUE
+
||  '''Styling context menu'''
−
|}
+
|| Change the styling in cell content using context menu.
−
 
+
|| [/_w/images/b/b3/ChangeStyleByContextMenu.swf See Demo]
−
===Financial Formula Functions===
 
−
 
 
−
Total 41 Financial Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
−
|ACCRINT
 
 
|-
 
|-
−
|ACCRINTM
 
−
|-
 
−
|AMORDEGRC
 
−
|-
 
−
|AMORLINC
 
−
|-
 
−
|COUPDAYBS
 
−
|-
 
−
|COUPDAYS
 
−
|-
 
−
|COUPDAYSNC
 
−
|-
 
−
|COUPNCD
 
−
|-
 
−
|COUPNUM
 
−
|-
 
−
|COUPPCD
 
−
|-
 
−
|CUMIPMT
 
−
|-
 
−
|CUMPRINC
 
−
|-
 
−
|DB
 
−
|-
 
−
|DDB
 
−
|-
 
−
|DISC
 
−
|-
 
−
|DOLLARDE
 
−
|-
 
−
|DOLLARFR
 
−
|-
 
−
|DURATION
 
−
|-
 
−
|EFFECT
 
−
|-
 
−
|FV
 
−
|-
 
−
|FVSCHEDULE
 
−
|-
 
−
|INTRATE
 
−
|-
 
−
|IPMT
 
−
|-
 
−
|NOMINAL
 
−
|-
 
−
|NPER
 
−
|-
 
−
|NPV
 
−
|-
 
−
|PMT
 
−
|-
 
−
|PPMT
 
−
|-
 
−
|PRICE
 
−
|-
 
−
|PRICEDISC
 
−
|-
 
−
|PRICEMAT
 
−
|-
 
−
|PV
 
−
|-
 
−
|RECEIVED
 
−
|-
 
−
|SLN
 
−
|-
 
−
|SYD
 
−
|-
 
−
|TBILLEQ
 
−
|-
 
−
|TBILLYIELD
 
−
|-
 
−
|XNPV
 
−
|-
 
−
|YIELD
 
−
|-
 
−
|YIELDDISC
 
−
|-
 
−
|YIELDMAT
 
 
|}
 
|}
−
 
+
<br/>
−
===Engineering Formula Functions===
+
==Built-in Functions==
−
 
+
{| border="1"
−
Total 38 Engineering Formula Functions:
 
−
 
 
−
{| class="wikitable"
 
−
|-
 
−
|BESSELI
 
−
|-
 
−
|BESSELJ
 
−
|-
 
−
|BESSELK
 
−
|-
 
−
|BESSELY
 
−
|-
 
−
|BIN2DEC
 
−
|-
 
−
|BIN2HEX
 
−
|-
 
−
|BIN2OCT
 
−
|-
 
−
|COMPLEX
 
−
|-
 
−
|DEC2BIN
 
−
|-
 
−
|DEC2HEX
 
−
|-
 
−
|DEC2OCT
 
−
|-
 
−
|DELTA
 
−
|-
 
−
|ERF
 
−
|-
 
−
|ERFC
 
−
|-
 
−
|GESTEP
 
−
|-
 
−
|HEX2BIN
 
−
|-
 
−
|HEX2DEC
 
−
|-
 
−
|HEX2OCT
 
−
|-
 
−
|IMABS
 
−
|-
 
−
|IMAGINARY
 
−
|-
 
−
|IMARGUMENT
 
−
|-
 
−
|IMCONJUGATE
 
−
|-
 
−
|IMCOS
 
−
|-
 
−
|IMDIV
 
−
|-
 
−
|IMEXP
 
−
|-
 
−
|IMLN
 
−
|-
 
−
|IMLOG10
 
−
|-
 
−
|IMLOG2
 
 
|-
 
|-
−
|IMPOWER
+
!Type  || List || Total
 
|-
 
|-
−
|IMPRODUCT
+
||  '''Math'''
 +
|| 
 +
ABS
 +
ACOS
 +
ACOSH
 +
ASIN
 +
ASINH
 +
ATAN
 +
ATAN2
 +
ATANH
 +
CEILING
 +
COMBIN
 +
COS
 +
COSH
 +
DEGREES
 +
EVEN
 +
EXP
 +
FACT
 +
FACTDOUBLE
 +
FLOOR
 +
GCD
 +
INT
 +
LCM
 +
LN
 +
LOG
 +
LOG10
 +
MDETERM
 +
MINVERSE
 +
MMULT
 +
MOD
 +
MROUND
 +
MULTINOMIAL
 +
ODD
 +
PI
 +
POWER
 +
PRODUCT
 +
QUOTIENT
 +
RADIANS
 +
RAND
 +
RANDBETWEEN
 +
ROMAN
 +
ROUND
 +
ROUNDDOWN
 +
ROUNDUP
 +
SERIESSUM
 +
SIGN
 +
SIN
 +
SINH
 +
SQRT
 +
SQRTPI
 +
SUBTOTAL
 +
SUM
 +
SUMIF
 +
SUMIFS
 +
SUMPRODUCT
 +
SUMSQ
 +
SUMX2MY2
 +
SUMX2PY2
 +
SUMXMY2
 +
TAN
 +
TANH
 +
TRUNC
 +
|| 60
 
|-
 
|-
−
|IMREAL
+
||  '''Statistical Formula'''
 +
||
 +
AVEDEV
 +
AVERAGE
 +
AVERAGEA
 +
AVERAGEIF
 +
AVERAGEIFS
 +
BETADIST
 +
BETAINV
 +
BINOMDIST
 +
CHIDIST
 +
CHIINV
 +
COUNT
 +
COUNTA
 +
COUNTBLANK
 +
COUNTIF
 +
DEVSQ
 +
EXPONDIST
 +
FDIST
 +
FINV
 +
GAMMADIST
 +
GAMMAINV
 +
GAMMALN
 +
GEOMEAN
 +
GROWTH
 +
HARMEAN
 +
HYPGEOMDIST
 +
INTERCEPT
 +
KURT
 +
LARGE
 +
MAX
 +
MAXA
 +
MEDIAN
 +
MIN
 +
MINA
 +
MODE
 +
NORMDIST
 +
POISSON
 +
SKEW
 +
SLOPE
 +
SMALL
 +
STDEV
 +
STDEVA
 +
TDIST
 +
TINV
 +
VAR
 +
WEIBULL
 +
|| 45
 
|-
 
|-
−
|IMSIN
+
||  '''Text'''
 +
||
 +
CHAR
 +
CLEAN
 +
CODE
 +
CONCATENATE
 +
DOLLAR
 +
EXACT
 +
FIND, FINDB
 +
FIXED
 +
LEFT, LEFTB
 +
LEN, LENB
 +
LOWER
 +
MID, MIDB
 +
PHONETIC
 +
PROPER
 +
REPLACE, REPLACEB
 +
REPT
 +
RIGHT, RIGHTB
 +
SEARCH, SEARCHB
 +
SUBSTITUTE
 +
T
 +
TEXT
 +
TRIM
 +
UPPER
 +
VALUE
 +
|| 24
 
|-
 
|-
−
|IMSQRT
+
||  '''Info'''
 +
||
 +
ERROR.TYPE
 +
INFO
 +
ISBLANK
 +
ISERR
 +
ISERROR
 +
ISEVEN
 +
ISLOGICAL
 +
ISNA
 +
ISNONTEXT
 +
ISNUMBER
 +
ISODD
 +
ISREF
 +
ISTEXT
 +
N
 +
NA
 +
TYPE
 +
||  16
 
|-
 
|-
−
|IMSUB
+
||  '''Date & Time Functions'''
 +
||
 +
DATE
 +
DATEVALUE
 +
DAY
 +
DAYS360
 +
HOUR
 +
MINUTE
 +
MONTH
 +
NETWORKDAYS
 +
NOW
 +
SECOND
 +
TIME
 +
TIMEVALUE
 +
TODAY
 +
WEEKDAY
 +
YEAR
 +
|| 15
 
|-
 
|-
−
|IMSUM
+
||  '''Logical Formula Functions'''
 +
||
 +
AND
 +
FALSE
 +
IF
 +
IFERROR
 +
NOT
 +
OR
 +
TRUE
 +
|| 7
 
|-
 
|-
−
|OCT2BIN
+
|| '''Financial Formula Functions'''
 +
||
 +
ACCRINT
 +
ACCRINTM
 +
AMORDEGRC
 +
AMORLINC
 +
COUPDAYBS
 +
COUPDAYS
 +
COUPDAYSNC
 +
COUPNCD
 +
COUPNUM
 +
COUPPCD
 +
CUMIPMT
 +
CUMPRINC
 +
DB
 +
DDB
 +
DISC
 +
DOLLARDE
 +
DOLLARFR
 +
DURATION
 +
EFFECT
 +
FV
 +
FVSCHEDULE
 +
INTRATE
 +
IPMT
 +
NOMINAL
 +
NPER
 +
NPV
 +
PMT
 +
PPMT
 +
PRICE
 +
PRICEDISC
 +
PRICEMAT
 +
PV
 +
RECEIVED
 +
SLN
 +
SYD
 +
TBILLEQ
 +
TBILLYIELD
 +
XNPV
 +
YIELD
 +
YIELDDISC
 +
YIELDMAT
 +
|| 41
 
|-
 
|-
−
|OCT2DEC
+
|| '''Engineering Formula Functions'''
 +
||
 +
BESSELI
 +
BESSELJ
 +
BESSELK
 +
BESSELY
 +
BIN2DEC
 +
BIN2HEX
 +
BIN2OCT
 +
COMPLEX
 +
DEC2BIN
 +
DEC2HEX
 +
DEC2OCT
 +
DELTA
 +
ERF
 +
ERFC
 +
GESTEP
 +
HEX2BIN
 +
HEX2DEC
 +
HEX2OCT
 +
IMABS
 +
IMAGINARY
 +
IMARGUMENT
 +
IMCONJUGATE
 +
IMCOS
 +
IMDIV
 +
IMEXP
 +
IMLN
 +
IMLOG10
 +
IMLOG2
 +
IMPOWER
 +
IMPRODUCT
 +
IMREAL
 +
IMSIN
 +
IMSQRT
 +
IMSUB
 +
IMSUM
 +
OCT2BIN
 +
OCT2DEC
 +
OCT2HEX
 +
|| 38
 
|-
 
|-
−
|OCT2HEX
 
 
|}
 
|}
−
 
+
<br/>
−
==Custom Functions==
+
==Reference==
−
 
 
−
You can [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Implement_Your_Own_Formula_Functions/As_Simple_as_a_Java_Static_Method implement your own formula functions] in ZK Spreadsheet.
 
−
 
 
−
= Java Bean Binding=
 
−
 
 
−
ZK Spreadsheet supports resolving the [http://books.zkoss.org/wiki/ZK_Spreadsheet_Essentials/Working_with_ZK_Spreadsheet/Bind_Java_Bean_to_ZK_Spreadsheet name expressions in cells to bind the data from the back end Java beans automatically].
 
−
 
 
−
[[File:DataBinding_Chart.png]]
 
−
 
 
−
=Downloads=
 
−
 
 
−
=References=
 
−
 
 
 
[[Small_Talks/2008/May/Integrate_ZK_Spreadsheet_with_Spring | Integrate ZK Spreadsheet with Spring]]
 
[[Small_Talks/2008/May/Integrate_ZK_Spreadsheet_with_Spring | Integrate ZK Spreadsheet with Spring]]
−
 
  
 
[[Small_Talks/2008/April/Use_JavaBean_in_Excel_with_ZK_Spreadsheet | Use JavaBean in Excel with ZK Spreadsheet]]
 
[[Small_Talks/2008/April/Use_JavaBean_in_Excel_with_ZK_Spreadsheet | Use JavaBean in Excel with ZK Spreadsheet]]

Revision as of 10:15, 15 December 2010

WarningTriangle-32x32.png This page is under construction, so we cannot guarantee the accuracy of the content!

Introduction

ZK Spreadsheet is an Ajax component that delivers functionalities found in Microsoft Excel.
Embedding ZK Spreadsheet in your Java web application to garner features such as:

  • Import your Excel 2003/2007 files to ZK Spreadsheet and make them collaborative online.
  • Control or configure an Ajax spreadsheet with versatile Java APIs.
  • Register event listeners on rows, columns, cells, or any range of selected cells.
  • Bind cells to back-end Java beans such that change in data is reflected on user interface automatically.
  • Extend on top of the 246 built in functions that come with ZK Spreadsheet in Java

Features

Java Based Embeddable Ajax Spreadsheet

Feature Description Demo
Java Bean Binding ZK Spreadsheet resolves name expressions in cells to make bindings to back-end Java beans automatically. [/_w/images/5/5e/DataBinding_Chart.png See Illustration]
Java APIs The backend data model and formula evaluation engine is built on top of the Apache POI library. Developers could control, configure, or extend the Ajax spreadsheet entirely in Java.
Event Driven A comprehensive set of events were designed to allow developers to create a highly interactive spreadsheet application. [/_w/images/8/88/EditingEvents.swf See Demo-Editing Events]
[/_w/images/c/cd/KeyEvent.png See Illustration-Key Events]
[/_w/images/1/1b/OnHeaderRightClick.swf See Demo-Mouse Events]
[/_w/images/4/40/SelectionEvents.png See Illustration-Selection Events]
Read Doc
Readily Embeddable ZK Spreadsheet is a ZK component which developers could embed into any Java web application at its presentation tier.
Custom Functions ZK Spreadsheet allows developers to implement custom formula functions on top of the 246 built in ones. End users can use custom these functions just like any other built in functions. Read Doc


Performance Boosts

Feature Description Demo
Cell Load on Demand Like other tabular components in ZK, ZK Spreadsheet supports “Load on Demand” which only the cells in view are loaded. [/_w/images/1/1a/LoadCellOnDemand.png See Illustration]
Efficient Dependency Tracking


Imort/Export Worksheet

Feature Description Demo
Import/Export Excel 2003/2007 Files
Export to PDF The “Export to PDF” dialog gives users a set of options to customize PDF printing, including the worksheet size, ranging from printing all worksheets to printing just a selection of cells. [/_w/images/5/5a/ExportPDF.swf See Demo]


Charts and Images

Feature Description Demo
Work with Interactive Excel Charts Charts created in Excel can be imported to ZK Spreadsheet. Changes in the data are reflected on the charts automatically. [/_w/images/2/2c/ColumnChart.swf See Demo]
Export Excel charts to PDF Export Excel charts to PDF [/_w/images/3/3f/ExportChartPDF.swf See Demo]
Insert Images and Import Image Embedded Excel Worksheets Users can insert images into cells and images embedded in an Excel worksheet can be rendered in ZK Spreadsheet when it is imported into ZK Spreadsheet. [/_w/images/0/00/LoadImage.swf See Demo]


Worksheet Operations

Feature Description Demo
Menu Commands Commands such as file I/O, worksheet and cell formatting and styling can all be executed on the top menu bar. [/_w/images/5/51/MenuFunction.swf See Demo]
Drag and Drop Editing of Selected Cells Use drag and drop to copy or move selected cell on worksheets. [/_w/images/9/9c/DragNDropCells.swf See Demo]
Insert/Delete Rows/Columns/Cells Use context menu to insert or delete Rows, Columns, or Cells. [/_w/images/a/a2/AlterRanges.swf See Demo]
Auto-fill Cells Select and drag a selection of cells to copy their content to adjacent cells. [/_w/images/6/69/AutoFillCells.swf See Demo]
Copy-Paste Cells with Paste Special Support Users can copy and paste selected cells with the option of invoking the Paste Special dialog to choose and form a specific paste special task. [/_w/images/b/b4/CopyPaste.swf See Demo]
Sorting with Custom Sort Wizard ZK Spreadsheet not only supports ascending/descending sorting, but also “Custom Sort” through a wizard where user can enter series of sorting conditions. [/_w/images/c/cf/Sorting.swf See Demo]
Keystroke Shortcuts ZK Spreadsheet has an array of built in shortcuts. The list of shortcuts can be invoked by selecting Help > Cheatsheet. [/_w/images/4/41/ShortcutKeys.swf See Demo]
Selected Cells Highlighting [/_w/images/8/8b/SelectionHighlight.swf See Demo]
In-place Cell Editing [/_w/images/9/9e/EditingZSS.swf See Demo]


Worksheet Formatting

Feature Description Demo
Multiple Worksheets Users can switch between multiple worksheets by clicking on the desired worksheet tabs. The worksheet content is loaded on demand. [/_w/images/d/d4/Muti-Sheets.swf See Demo]
Freeze Rows and Columns Rows and columns can be made frozen so that desired rows or columns are locked in view. [/_w/images/d/d1/FreezeColRow.swf See Demo]
Hide/Show Rows and Columns Rows and columns can be shown or hidden using context menu or by simply dragging the rows or columns to collapse or expand. [/_w/images/b/b0/HideRowCol.swf See Demo - Using Context Menu]
[/_w/images/1/12/HideRowColByDragging.swf See Demo - Using Drag and Collapse/Expand]
Hide/Show Row/Column Headers Headers for rows and columns can be made visible/invisible [/_w/images/0/0a/HeaderDisplay.swf See Demo]
Modify Row/Column Headers [/_w/images/1/12/ModifyHeader.swf See Demo]
Hide/Show Grid Lines Users can make grid visible/invisible with a click of the check-box. [/_w/images/c/c5/GridLines.swf See Demo]
Set Maximum Rows and Columns Allowed [/_w/images/0/04/MaxColRow.swf See Demo]


Cell Formatting and Styling

Feature Description Demo
Number Formatting Format number in cells using friendly dialog. [/_w/images/b/bf/NumberFormatting.swf See Demo]
Text Formatting Text formatting options include font, boldfaced, italic, among others. Any text format imported from an Excel worksheet would remain consistent in ZK Spreadsheet. [/_w/images/5/52/RichFormattedText.swf See Demo]
Hyperlink Creation A cell content can be made into a hyperlink via the "Insert Hyperlink" dialog. [/_w/images/c/cd/Hyperlink.swf See Demo]
Text Alignment Text in cells can be made aligned to right, center, and left.
Styling Toolbar Change the styling in cell content using toolbar. [/_w/images/d/d4/FastIconToolbar.swf See Demo]
Styling context menu Change the styling in cell content using context menu. [/_w/images/b/b3/ChangeStyleByContextMenu.swf See Demo]


Built-in Functions

Type List Total
Math

ABS ACOS ACOSH ASIN ASINH ATAN ATAN2 ATANH CEILING COMBIN COS COSH DEGREES EVEN EXP FACT FACTDOUBLE FLOOR GCD INT LCM LN LOG LOG10 MDETERM MINVERSE MMULT MOD MROUND MULTINOMIAL ODD PI POWER PRODUCT QUOTIENT RADIANS RAND RANDBETWEEN ROMAN ROUND ROUNDDOWN ROUNDUP SERIESSUM SIGN SIN SINH SQRT SQRTPI SUBTOTAL SUM SUMIF SUMIFS SUMPRODUCT SUMSQ SUMX2MY2 SUMX2PY2 SUMXMY2 TAN TANH TRUNC

60
Statistical Formula

AVEDEV AVERAGE AVERAGEA AVERAGEIF AVERAGEIFS BETADIST BETAINV BINOMDIST CHIDIST CHIINV COUNT COUNTA COUNTBLANK COUNTIF DEVSQ EXPONDIST FDIST FINV GAMMADIST GAMMAINV GAMMALN GEOMEAN GROWTH HARMEAN HYPGEOMDIST INTERCEPT KURT LARGE MAX MAXA MEDIAN MIN MINA MODE NORMDIST POISSON SKEW SLOPE SMALL STDEV STDEVA TDIST TINV VAR WEIBULL

45
Text

CHAR CLEAN CODE CONCATENATE DOLLAR EXACT FIND, FINDB FIXED LEFT, LEFTB LEN, LENB LOWER MID, MIDB PHONETIC PROPER REPLACE, REPLACEB REPT RIGHT, RIGHTB SEARCH, SEARCHB SUBSTITUTE T TEXT TRIM UPPER VALUE

24
Info

ERROR.TYPE INFO ISBLANK ISERR ISERROR ISEVEN ISLOGICAL ISNA ISNONTEXT ISNUMBER ISODD ISREF ISTEXT N NA TYPE

16
Date & Time Functions

DATE DATEVALUE DAY DAYS360 HOUR MINUTE MONTH NETWORKDAYS NOW SECOND TIME TIMEVALUE TODAY WEEKDAY YEAR

15
Logical Formula Functions

AND FALSE IF IFERROR NOT OR TRUE

7
Financial Formula Functions

ACCRINT ACCRINTM AMORDEGRC AMORLINC COUPDAYBS COUPDAYS COUPDAYSNC COUPNCD COUPNUM COUPPCD CUMIPMT CUMPRINC DB DDB DISC DOLLARDE DOLLARFR DURATION EFFECT FV FVSCHEDULE INTRATE IPMT NOMINAL NPER NPV PMT PPMT PRICE PRICEDISC PRICEMAT PV RECEIVED SLN SYD TBILLEQ TBILLYIELD XNPV YIELD YIELDDISC YIELDMAT

41
Engineering Formula Functions

BESSELI BESSELJ BESSELK BESSELY BIN2DEC BIN2HEX BIN2OCT COMPLEX DEC2BIN DEC2HEX DEC2OCT DELTA ERF ERFC GESTEP HEX2BIN HEX2DEC HEX2OCT IMABS IMAGINARY IMARGUMENT IMCONJUGATE IMCOS IMDIV IMEXP IMLN IMLOG10 IMLOG2 IMPOWER IMPRODUCT IMREAL IMSIN IMSQRT IMSUB IMSUM OCT2BIN OCT2DEC OCT2HEX

38


Reference

Integrate ZK Spreadsheet with Spring

Use JavaBean in Excel with ZK Spreadsheet

Retrieved from "https://www.zkoss.org/_w/index.php?title=Small_Talks/2010/December/New_Features_of_ZK_Spreadsheet_2.0&oldid=14926"