Function | Arguments | Example | Result | Additional Information | Sample # | Sample Text |
ABS | numbers_as_array | "=ABS(F4)" | 62 | 23 | Hello World | |
AVERAGE | values_as_array | "=AVERAGE(F4:F14)" | 46.92307692307692 | Synonym:?AVG | 45 | True |
CEILING | numbers_as_array | "=CEILING(F4:F14)" | 62108200361799100 | 62 | False | |
COUNT | html_as_string | "=COUNT(F2:F14)" | 13 | 108 | To High | |
DAYSFROM | url_as_string | "=DAYSFROM(2009,4,15)" | -18 | 200 | To Low | |
DOLLAR | numbers_as_array | "=DOLLAR(F13)" | $55.00 | 36 | Perfect | |
FALSE | "=IF(F4 < 100, TRUE(), FALSE())" | TRUE | 17 | number | ||
FIXED | number, decimals, noCommas? | "=FIXED(F4+F14)" | 41.00 | Two decimal places | 99 | numbers_as_array |
FLOOR | numbers_as_array | "=FLOOR(F4-F5)" | -46 | Synonym: INT | 100 | values_as_array |
HYPERLINK | "=HYPERLINK("http://www.jquery.com", "jQuery's website")" | jQuery's website | -100 | html_as_string | ||
IF | IF(logical_test, value_if_true, value_if_false) | "=IF(F12 < 100, TRUE(), FALSE())" | TRUE | Can have nested IF functions. | -14 | url_as_string |
IMG | "=IMG("http://ui.jquery.com/images/logo.gif")" | The url can be sensitive to
numbers. Also, on initial
load, because the image doesn't really have a size, the outerheight can be distorted. An easy way to offset this is to have some text in front of it that's taller than the image :). |
55 | values | ||
MAX | values_as_array | "=MAX(F3:F13)" | 200 | -21 | ||
MIN | values_as_array | "=MIN(F3:F13)" | -100 | |||
N | numbers_as_array | "=N(F3)" | 45 | |||
PI | "=PI()" | 3.141592653589793 | If you use "=PI" it will return the actual function as text, which is incorrect. Use "=PI()". | |||
TODAY | "=TODAY()" | Fri Mar 12 2010 11:49:48 GMT-0500 (Eastern Standard Time) | ||||
TRUE | "=TRUE() || FALSE()" | TRUE | ||||
SUM | values_as_array | "=SUM(F2:F13)" | 631 | |||
ROUND | numbers_as_array | "=ROUND(1.6)" | 2 | |||
RAND | "=RAND()" | 0.7829931667058939 | Synonym: RND | |||
Advanced Functionality | Example | Result | Additional Information | |||
Direct Javascript | "=jQuery.sheet.version" | 1b | The character '=' simply starts a
reference to javascript. The example here interacts with the jS (or jQuery.sheet) object and calls a function that returns it's current version. |
|||
Math.PI | "=Math.PI" | 3.141592653589793 | The Math function here is
actually the
javascript Math function. So in a since, you are writing javascript from the sheet. |
Cell Navigation | Result | Dependancy | Synonym |
Left Arrow | Active cell moves left if possible. | jQuery.sheet.evt.cellClick() | jS.evt.cellClick() |
Right Arrow | Active cell moves right if possible. | jQuery.sheet.evt.cellClick() | jS.evt.cellClick() |
Up Arrow | Active cell moves up if possible. | jQuery.sheet.evt.cellClick() | jS.evt.cellClick() |
Down Arrow | Active cell moves down if possible. | jQuery.sheet.evt.cellClick() | jS.evt.cellClick() |
Escape | Active cell is removed from focus. | jQuery.sheet.evt.cellEditAbandon() | jS.evt.cellEditAbandon() |
Enter | Starts in-place edit / Active
cell moves
down if possible. |
jQuery.sheet.evt.formulaKeyDown() | jS.evt.formulaKeyDown() |
Ctrl + Enter | Ends in-place edit / Active cell
moves
down if possible. |
jQuery.sheet.evt.formulaKeyDown() | jS.evt.formulaKeyDown() |
Tab | Active cell moves right if possible. | jQuery.sheet.evt.cellClick() | jS.evt.cellClick() |
Feature | Info | Dependancy | |
jQuery.sheet is Re-sizable! | Click and drag on the cell Id
bars, and it will
resize the row. |
jQuery.sheet.evt.barMouseDown | jS.evt.barMouseDown |
Charts | Click the "Charts" above for more info. | jGCharts plugin | |
Auto Scroll | When you navigate to a cell the
spreadsheet pane automatically scrolls to it. |
jQuery.sheet.evt.scrollBars() | jS.evt.scrollBars() |
Multi cell select | If you drag your mouse over a
range of
cells, it will select them for you. You can use this to change their style. |
jQuery.sheet.evt.cellOnMouseDown() | jS.evt.cellOnMouseDown() |
Multi cell select from cell Id bars | Double click on the mouse Id
bars and it
will select the range of cells associated with it. |
jQuery.sheet.cellSetActiveMultiColumn() or
jQuery.sheet.cellSetActiveMultiRow() |
|
Add Row | Adds a row to the bottom of your spreadsheet. | jQuery.sheet.controlFactory.addRow() | jS.controlFactory.addRow() |
Insert Row | Inserts a row just below the currently selected row. | jQuery.sheet.controlFactory.addRow() | jS.controlFactory.addRow() |
Add Multi Row | Adds multiple rows to the end of the spreadsheet. | jQuery.sheet.controlFactory.addRowMulti() | jS.controlFactory.addRowMulti() |
Delete Row | Deletes the currently selected row. | jQuery.sheet.deleteRow() | |
Add Column | Adds a column to the last column of the spreadsheet. | jQuery.sheet.controlFactory.addColumn() | jS.controlFactory.addColumn() |
Insert Column | Insert column just after the currently selected column. | jQuery.sheet.controlFactory.addColumn() | jS.controlFactory.addColumn() |
Add Multi Column | Adds multiple columns to the last column in the spreadsheet. | jQuery.sheet.controlFactory.addColumnMulti() | jS.controlFactory.addColumnMulti() |
Delete Column | Delete the currently selected spreadsheet. | jQuery.sheet.deleteColumn() | |
Style cells | Cells are styleable. | jQuery.sheet.cellStyleToggle() | |
Support of jQuery UI theme | You make the spreadsheet look
like you want.
Pick your theme here: http://ui.jquery.com/themeroller |
jQuery UI Theme / jQuery.sheet.themeRoller | |
Sheet Title | The table's "title" attribute |
Chart Type | Example | Chart | Notes | Sample Data | Sample Legends | |
Simple Vertical Bar Chart | "=CHART.BAR(E2:E11)" | Each of the char types accepts
the following variables in this order:
*Values - as array (ie "a1:a2") or jagged/multidimensional arrays (ie "[a1:a2, b1:b2]") *Legend - as array from string (ie "['First Legend Label', 'Second Legend Label']") or from cell values (ie "a1:a2") *Axis Labels - as array, similar to legend *Height as integer *Width as integer EXAMPLE: =CHART.BAR(a1:a2, "Sales", "2009", 100, 100) |
1 | Nov | 2000 | |
Simple Horizontal Bar Chart | "=CHART.BARH(E2:E11)" | One thing to note about dynamic images it that column sizing can get a bit messed up. | 2 | Dec | 2001 | |
Simple Stacked Vertical Bar Chart | "=CHART.SBAR(E2:E12)" | 3 | Jan | 2002 | ||
Simple Stacked Horizontal Bar Chart | "=CHART.SBARH(E2:E12)" | 4 | Feb | 2003 | ||
Simple Line Chart | "=CHART.LINE(E2:E12)" | 5 | Mar | 2004 | ||
Simple Pie Chart | "=CHART.PIE(E2:E12)" | 7 | May | 2005 | ||
Advanced Simple Vertical Bar Chart | "=CHART.BAR([E2:E7], ['Dec 2008 Sales' , 'Jan 2009 Sales', 'Feb 2009 Sales', 'Mar 2009 Sales', 'May 2009 Sales', 'June 2009 Sales'])" | 7 | June | 2006 | ||
Advanced Horizontal Bar Chart | "=CHART.SBARH([E2:E7, E2:E7, E2:E7, E2:E7, E2:E7], F2:F7, G2:G7, 350,200)" | 7.5 | ||||
Advanced Vertical Bar Chart | "=CHART.BARH([G2:G7, G2:G7, G2:G7, G2:G7, G2:G7], i2:i7, H2:H7, 350,500)" | 8 | ||||
Advanced Line Chart | "=CHART.LINE([[105.7,97.9],[108.1,101.6],[110.7,102.9],[111.0,93.7],[110.0,89.8],[109.0,90.7],
[107.5,93.0],[106.1,94.5],[104.3,91.9],[102.0,93.9],[102.8,93.6],[103.8,92.6], [102.9,94.0],[102.1,92.7],[100.6,96.0],[101.7,97.9],[101.8,105.0], [103.3,104.1],[104.0,105.1],[103.7,108.1],[108.4,108.4],[109.4,113.8], [112.0,109.1],[112.6,106.3],[115.5,106.7],[115.7,108.8],[114.7,118.8], [115.9,120.4],[116.2,115.9],[118.0,124.7],[123.3,126.5],[127.6,131.6], [130.3,134.0],[135.5,135.7],[138.2,126.4],[139.6,127.4],[145.1,131.0], [146.4,129.9],[147.1,133.7],[149.0,138.4],[150.3,141.0],[151.3,139.3], [153.4,145.3],[152.7,142.9],[152.9,129.2],[152.2,126.0],[151.9,124.8], [150.1,125.9],[148.2,118.9],[145.3,122.9],[142.9,127.7],[142.6,134.4], [144.0,138.5],[145.5,138.7],[147.2,141.8],[150.0,139.2],[153.8,145.6], [155.4,147.6],[157.0,157.9],[158.4,156.2],[162.8,153.9],[162.8,158.6], [164.7,166.3],[168.5,165.8]], ["Dec 2008 Sales" , "Jan 2009 Sales"])" |
A bit more complicated :) | 8.2 |
Inputs are for capturing fixed data, such as a drop down list (INPUT.SELECT), or a checkbox (INPUT.CHECKBOX) | ||||
Input Type | Example | Data Number | Data String | |
Select List | "=INPUT.SELECT(D3:D10)" | 34 | Lorem | |
Radio List | "=INPUT.RADIO(E3:E10)" |
Lorem
Proin Aliquam Quisque Aliquam Vivamus Etiam Donec |
-20 | Proin |
Checkbox | "=INPUT.CHECKBOX(E3)" |
Lorem
|
123 | Aliquam |
Get Select List Value | "=INPUT.SELECTVAL(C3)" | 4 | 123 | Quisque |
Get Radio List Value | "=INPUT.RADIOVAL(C4)" | Donec | 4 | Aliquam |
Get Checkbox Value | "=INPUT.CHECKBOXVAL(C5)" | Lorem | 534456 | Vivamus |
Detect if Checkbox is Checked | "=INPUT.ISCHECKED(C5)" | FALSE | 3 | Etiam |
1 | Donec |
Option | Description | Default Value |
urlGet | local url, if you want to get a sheet from a url | documentation.html |
urlSave | local url, for use only with the default save for sheet | save.html |
editable | bool, Makes the jSheetControls_formula & jSheetControls_fx appear | true |
urlMenu | local url, for the menu to the right of title | menu.html |
newColumnWidth | int, the width of new columns or columns that have no width assigned | false |
title | html, general title of the sheet group | null |
inlineMenu | html, menu for editing sheet | null |
buildSheet | bool, string, or object
bool true - build sheet inside of parent bool false - use urlGet from local url string - '{number_of_cols}x{number_of_rows} (5x100) object - table(s) |
false |
calcOff | bool, turns calculationEngine off (no spreadsheet, just grid) | false |
log | bool, turns some debugging logs on (jS.log('msg')) | false |
lockFormulas | bool, turns the ability to edit any formula off | false |
colMargin | int, the height and the width of all bar items, and new rows | 18 |
fnBefore | fn, fires just before jQuery.sheet loads | function () { } |
fnAfter | fn, fires just after all sheets load | function () { } |
fnSave | fn, default save function, more of a proof of concept | function () { jS.saveSheet(); } |
fnOpen | fn, by default allows you to paste table html into a javascript prompt for you to see what it looks likes if you where to use sheet | function () { var t = prompt("Paste your table html here"); if (t) { jS.openSheet(t); } } |
fnClose | fn, default clase function, more of a proof of concept | function () { } |
joinedResizing | bool, this joins the column/row with the resize bar | false |
boxModelCorrection | int, attempts to correct the differences found in heights and widths of different browsers, if you mess with this, get ready for the must upsetting and delacate js ever | 2 |
Hosted By: Visual Interop Development | VisOp-Dev.com |
jQuery.sheet | jQuery.sheet - The Web Based Spreadsheet |
Version | 1b |
Written By | Robert Plummer |
Written Using | Notepad++ |
Compressed Using | YUI Compressor |
Compatibility | Firefox 3, IE 7 & 8, Chrome, Safari |
Consulting Services | Visual Interop Development |
Issues | jQuery.sheet Issues |
Need a feature or support? | Tell Me |
PLEASE DONATE |