SpreadSheet
A demonstration of cell based spreadsheet program
Demonstrates features in SpreadSheet TreeGrid module.
Features: Editable formulas with suggest menu and choosing cell range during editing; Row and column index; Auto adding new rows and columns on scroll;
Dynamic cell border; Dynamic cell style; Dynamic and automatic cell format and type; Mass changes like Clear, Move, Copy, Paste, AutoFill with all cell attributes.
-
Index
The rows and columns are in formulas addressed by their index instead of row id or column name.
The column index is shown in top header, the column index here consists from letters A-Z. The row index is shown in left column, the row index here is a number from 1.
To compare, the column name is shown in bottom row and row id in right column - the id and name is not changed when the row or column moved, added or deleted, but the indexes are always updated.
Auto pages
New rows and column are automatically added to sheet end when you scroll bottom or right end. Empty pages are automatically removed after some time if they are not visible and focused.
Dynamic cell border
Every cell can have set its border on one or more its edges. The border between adjacent cells is shared. If more cells define the same border edge, the right and bottom cell is preferred.
There are 8 predefined border width and styles in TreeGrid: 0px, 1px solid, 2px solid, 3px solid, 1px dotted, 2px dotted, 1px dashed, 2px dashed. Every border style can have any color.
Set it on Toolbar Border button or from popup menu.
Dynamic cell style
Every cell can have set various text styles as: bold font, italic font, underline, strike, overline, small caps. Font name and size.
Text color, text shadow and cell background color.
Horizontal and vertical alignment, rotate and wrapping.
Set it on appropriate Toolbar button or from popup menu.
Dynamic cell span
Every cells can be spanned together horizontally or vertically. The row and column span just cannot cross. Set it on Toolbar Span/Split button or from popup menu.
Mass cell change
Selected cell ranges can be modified together and all their values, formulas and attributes like border, style and span are affected too.
The mass changes are: Clear cells by Del key, move cells by mouse dragging the focused edge, copy cells by mouse dragging the focused edge with ctrl,
auto fill cells by mouse dragging the bottom right corner of focused range, copy & paste by Ctrl+C, Ctrl+X and Ctrl+V.
Columns and rows changes
The rows and columns can be selected by clicking or dragging their Panel Select button.
The individual or selected groups of rows and columns can:
move by dragging their Index, copy by dragging their Index with Ctrl, delete, hide, show on click to panel Delete button, add or copy on click to Panel Copy button.
Popup menu
All cell, row and column actions are available also from popup menu shown or right click.
-
Editable formulas
In every cell you can enter its formula starting by '=
'. Cell value will show the formula result.
Cell references
Every formula can use value from another cell or cell range. The cell(s) can be addressed relatively (e.g. "A1") or absolutely by $ (e.g. "$A$1").
-
The row and columns change their index whenever they or previous items are moved, deleted or added.
All formulas (both absolute and relative) are updated accordingly after any Index change to refer still the same original rows and columns.
-
If relative formula is copied, it changes for the difference between indexes in old and new location.
So the relative formula (without $) refers the new destination location.
If absolute formula is copied, it never changes.
So the absolute formula (with $) refers the original location.
-
If formula is moved (both absolute and relative), it changes for the difference between indexes in old and new location.
But it does not change if all the source (referred) cells were moved too.
-
If formula references range(s), only the range bounds are absolutely defined and the range always contains rows and columns actually placed between the range bounds.
The cell reference can be also pick up by mouse click or dragging during formula editing.
Operators
Default operators use standard C++/JavaScript syntax: +, -, *, /, ! (not), % (modulo), & (bit AND), | (bit OR), ^ (bit XOR), && (logical and), || (logical OR), <<, >> (bit shift), == (equals), != (not equals), <= (less or equal), >= (greater or equal), < (less), > (greater).
There are added more operators: = (equals), <> (not equals), ?: (condition three arguments as "condition?result_true:result_false").
Priority of operators is the same as in JavaScript and cannot be changed. Always you can use ( ).
There are defined constants: pi (3.14), ln2 (ln(2)), ln10 (ln(10)), log2e (log2(e)), log10e (log10(e)), sqrt2 (sqrt(2)), sqrt1_2 (1/sqrt(2)).
Functions
There can be used also these aggregate functions: sum
, sumsq
, count
, counta
, countblank
, max
, min
, product
.
The functions accept as parameters value constants (e.g. 100), single cell reference (e.g. A1) or cell range reference (e.g. A1:B4).
In the range the bounds are separated by colon ':
'.
The functions accept more parameters separated by comma ,
.
There are also defined standard JavaScript mathematical functions: abs(x), round(x), ceil(x), floor(x), exp(x), log(x), pow(x,y), sqrt(x), sin(x), cos(x), tan(x), asin(x), acos(x), atan(x,y).
There are also defined date functions: date(year,month,day,hour,minute,second), date(date,format), time(hour,minute,second), time (time,format), now(), today(), year(date), month(date), day(date), weekday(date),weeknum(date), hour(date), minute(date), second(date).
And one formatting function: text(value,format,type) to convert date or number to string.
Available functions are suggested during editing after first characters are typed.