History: Spreadsheet

• «
• »

            ^
Tiki Spreadsheet uses a [https://github.com/Spreadsheets/WickedGrid/commits/master|now inactive project] so we need to switch to one of the many impressive [https://jspreadsheets.com/|modern alternatives].  If you would like with this, we are looking for financial sponsors or volunteer developers. Please contact [https://evoludata.com/Get-In-Touch|Marc Laporte].^


^ This page should merge with ((Spreadsheet JQ)) ^

! Spreadsheet

Tiki Spreadsheet performs calculations on user entered numeric data and presents the resulting data in tables and/or graphics within Tiki pages. The documentation describes the various available calculations performed by the Tikisheet. 


!! Enable the feature
{kaltura id="1_aqph4fw5"}

!! Overview
{kaltura id="1_0c9glnb2"}

__History__
The spreadsheet feature was [https://tiki.org/article70|added to Tiki in 2004 (version 1.9)]. Starting in ((Tiki5)), Tiki spreadsheet has moved to ((Spreadsheet jq|jquery.sheet)) for a much nicer interface. And even better in ((Tiki6|#Spreadsheet_major_improvements|Tiki6)).

__Sheet management__

||  | Graph | Create graphics and charts
 | History | View spreadsheet modification history
 | Export | Save the spreadsheet data in an other format
 | Import | Load data from an other data source
 | Edit | Modify the parameters of the spreadsheet
 | Delete | Deletes the spreadsheet||

!! Add one
Insert this code in a wiki page in order to have on spreadsheet added there:
{CODE()}
{sheet}
{CODE}

And follow the instructions/links that you will be provided in the page after saving it.
 
!!  Usage - text
{TABS(name="foo" tabs="Hotkeys|Formulas|Variables|Functions|Using cell values|Copy&paste|Overview")}

!!! Cell Navigation
* Left Arrow - Active cell moves left if possible
* Right Arrow - Active cell moves right if possible
* Up Arrow - Active cell moves up if possible
* Down Arrow - Active cell moves down if possible
 

!!! Cell Highlighting With Arrow Keys
* Left Arrow + Shift - Highlights left if possible
* Right Arrow + Shift - Highlights right if possible
* Up Arrow + Shift - Highlights up if possible
* Down Arrow + Shift - Highlights down if possible
 

!!! Editing
* Escape - Active cell is removed from focus
* Enter - Active cell is set and cell moves down if possible.
* Shift + Enter - Adds a line break to the cell's value
* Tab - Active cell is set and active cell moves right if possible
* Ctrl + X - Cut
* Ctrl + C - Copy
* Ctrl + V - Paste
 

!!! Undo & Redo
* Ctrl + Z - Undo
* Ctrl + Y - Redo

/////
A formula is the reason why spreadsheets are so powerful. jQuery.sheet has a very powerful and secure formula engine that can be used in the following way:

* Starting a cell's value with '=' activates the formula engine on the active cell(s) you are editing, for example (results in 100):
+ {CODE()}
=100
{CODE}
** This would really be the same as setting the cell's value to '100'
* Now lets start really using formulas (results in 0.03):
+ {CODE()}
=(100 + 200) /1000
{CODE}

/////
jQuery.sheet v3 offers the option of [http://visop-dev.com/doc/js3/symbols/jQuery.fn.sheet.html|creating and referencing variables (see jQuery.sheet setting __formulaVariables__)]

* Example of using simple variable in formula:
+ {CODE()}
=variable_name
{CODE}

Variables can also have attributes:
* Example of using variable with attributes in formula:
+ {CODE()}
=variable_name.attribute
{CODE}
* Example of using variable with math:
+ {CODE()}
=100 * variable_name
{CODE}

/////
Functions are where much of the work is done within spreadsheets. Here is how to use them:

* To use the SUM function, enter the following:
+ {CODE()}
=SUM()
{CODE}
* To use SUM with a single cell:
+ {CODE()}
=SUM(A1)
{CODE}
* To use SUM with a range of cells:
+ {CODE()}
=SUM(A1:B2)
{CODE}
* Nested functions:
+ {CODE()}
=DOLLAR(SUM(A1:B2) + SUM(D1:E2))
{CODE}
 

!!! Available Function (To be written)
/////
Cells can be referenced in the following ways:

* Single cell - example:
+ {CODE()}
A1
{CODE}
* Range of cells - example:
+ {CODE()}
A1:B2
{CODE}
* Single cell fixed - example:
+ {CODE()}
$A$1
{CODE}
* Range of cells fixed - example:
+ {CODE()}
$A$1:$B$2
{CODE}
* Other spreadsheet single cell - Example:
+ {CODE()}
SHEET2!A1
{CODE}
* Other spreadsheet range of cells - Example:
+ {CODE()}
SHEET2!A1:B2
{CODE}
/////
!!! Copy-Paste from a desktop spreadsheet
{kaltura id="1_4ok42955"}
/////
See:
{sheet id="2"}
{TABS}

!! Usage - Graphs
For the time being, see this external documentation page:
https://github.com/Spreadsheets/WickedGrid

See also:
{sheet id="4"}

!! From Here
* ((PluginSheet))
* ((Spreadsheet Functionality))
* ((Spreadsheet Interface))
* ((Spreadsheet Graphics and Charts))
* ((JQuery))
* ((Spreadsheet jq))

! Alias
* (alias(Sheet))
* (alias(TikiSheet))
* (alias(Tiki Sheet))
* (alias(Spreadsheets))