New Features of ZK Spreadsheet 2.0"
From Documentation
m |
|||
| Line 124: | Line 124: | ||
==Built-in Functions== | ==Built-in Functions== | ||
| − | {| | + | ===41 Financial Formula Functions=== |
| + | |||
| + | {| cellpadding=5 style="border:1px solid #BBB" | ||
| + | | 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 |
| − | || | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | IPMT | ||
| − | NOMINAL | ||
| − | NPER | ||
| − | NPV | ||
| − | PMT | ||
| − | PPMT | ||
| − | PRICE | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | || | ||
|- | |- | ||
| − | || | + | | PRICEMAT || PV || RECEIVED || SLN || SYD || TBILLEQ || TBILLYIELD || XNPV || YIELD || YIELDDISC |
| − | || | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | || | ||
|- | |- | ||
| − | | | + | | YIELDMAT |
| − | | | + | |} |
| − | + | ||
| − | + | === 60 Math Formula Functions === | |
| − | + | {| cellpadding=5 style="border:1px solid #BBB" | |
| − | + | | ABS || ACOS || ACOSH || ASIN || ASINH || ATAN || ATAN2 || ATANH || CEILING || COMBINACCRINT | |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | || | ||
|- | |- | ||
| − | || | + | | 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 |
| − | || | + | |} |
| − | + | ||
| − | + | === 45 Statistical Formula === | |
| − | + | {| cellpadding=5 style="border:1px solid #BBB" | |
| − | + | | 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 | |
| − | + | |} | |
| − | + | ||
| − | + | === 7 Logical Formula Functions=== | |
| − | || 15 | + | {| cellpadding=5 style="border:1px solid #BBB" |
| + | | AND || FALSE || IF || IFERROR || NOT || OR || TRUE | ||
| + | |} | ||
| + | |||
| + | === 38 Engineering Formula Functions=== | ||
| + | {| cellpadding=5 style="border:1px solid #BBB" | ||
| + | | 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 | ||
| + | |} | ||
| + | |||
| + | ===24 Text Functions=== | ||
| + | {| cellpadding=5 style="border:1px solid #BBB" | ||
| + | | 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 | ||
| + | |} | ||
| + | |||
| + | === 16 Info Functions=== | ||
| + | {| cellpadding=5 style="border:1px solid #BBB" | ||
| + | | ERROR.TYPE || INFO || ISBLANK || ISERR || ISERROR || ISEVEN || ISLOGICAL || ISNA || ISNONTEXT || ISNUMBER | ||
| + | |- | ||
| + | | ISODD || ISREF || ISTEXT || N || NA || TYPE | ||
| + | |} | ||
| + | |||
| + | === 15 Date & Time Functions=== | ||
| + | {| cellpadding=5 style="border:1px solid #BBB" | ||
| + | | DATE || DATEVALUE || DAY || DAYS360 || HOUR || MINUTE || MONTH || NETWORKDAYS || NOW || SECOND | ||
|- | |- | ||
| + | | TIME || TIMEVALUE || TODAY || WEEKDAY || YEAR | ||
|} | |} | ||
| − | |||
==Reference== | ==Reference== | ||
Revision as of 07:48, 22 December 2010
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 to make a highly interactive web application.
- 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 Component
- 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]
- Embed and Collaborate Across Multiple Browsers
- ZK Spreadsheet is a ZK component which developers could embed into any Java web application at its presentation tier to allow users to collaborate online. ZK Spreadsheet supports all major browsers: IE 6-9, Firefox, Chrome, Safari, Opera, ...
- JSF Wrapper
- ZK Spreadsheet 2.0 can be wrapped as a JSF Ajax Component
- 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
- 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
- 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
- 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
- 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. Charts supported include: Bar, Bar3D, Line, Line3D, Pie, Pie3D, and Donut in 2003(xls) and 2007(xlsx) format.
- [/_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/Import Images
- Users can insert images into cells and images embedded in an Excel 2003 (xls) and 2007 (xlsx) worksheets can be rendered in ZK Spreadsheet when it is imported into ZK Spreadsheet.
- [/_w/images/0/00/LoadImage.swf See Demo]
Worksheet Operations
- 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
- 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
- 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
41 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 |
60 Math Formula Functions
| ABS | ACOS | ACOSH | ASIN | ASINH | ATAN | ATAN2 | ATANH | CEILING | COMBINACCRINT |
| 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 |
45 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 |
7 Logical Formula Functions
| AND | FALSE | IF | IFERROR | NOT | OR | TRUE |
38 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 |
24 Text Functions
| 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 |
16 Info Functions
| ERROR.TYPE | INFO | ISBLANK | ISERR | ISERROR | ISEVEN | ISLOGICAL | ISNA | ISNONTEXT | ISNUMBER |
| ISODD | ISREF | ISTEXT | N | NA | TYPE |
15 Date & Time Functions
| DATE | DATEVALUE | DAY | DAYS360 | HOUR | MINUTE | MONTH | NETWORKDAYS | NOW | SECOND |
| TIME | TIMEVALUE | TODAY | WEEKDAY | YEAR |