Skip to content

Instantly share code, notes, and snippets.

@stlsmiths

stlsmiths/dt_grid_ss.html

Created June 10, 2011 20:05
Show Gist options
  • Save stlsmiths/1019654 to your computer and use it in GitHub Desktop.
Save stlsmiths/1019654 to your computer and use it in GitHub Desktop.
Download ZIP
YUI 2 Spreadsheet Demo via DataTable
Raw
dt_grid_ss.html
<html>
<head>
<title>YUI DataTable Example : DataGrid SpreadSheet</title>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<script src="http://yui.yahooapis.com/2.9.0/build/yuiloader/yuiloader-min.js"></script>
<style type="text/css">
.label_info {
background-color: #ccccff;
border: 1px solid;
padding:2px;
margin-right:15px;
}
/* http://yui.yahooapis.com/2.9.0 */
.allStuff {
padding-left:15px;
}
.yui-skin-sam .yui-dt td.firstCol {
background: url("../../yui/build/assets/skins/sam/sprite.png") repeat-x scroll 0 0 #D8D8DA;
border-color: -moz-use-text-color #CBCBCB -moz-use-text-color -moz-use-text-color;
border-style: none solid none none;
border-width: 0 1px 0 0;
margin: 0;
padding: 0;
text-align:center;
}
.divHide {
display : none;
visibility:hidden;
}
.yui-skin-sam .yui-dt .align-right, th.align-right, td.align-right, div.align-right, span.align-right {
text-align:right;
}
.yui-skin-sam .yui-dt .align-center, th.align-center, td.align-center, div.align-center, span.align-center {
text-align:center;
}
.yui-skin-sam .yui-dt .align-left, th.align-left, td.align-left, div.align-left, span.align-left {
text-align:left;
}
.yui-skin-sam .yui-dt-liner {
white-space:nowrap;
}
.italic {
font-style:italic;
}
.bold {
font-weight:bold;
}
.yui-button.btn_italic button {
background-image: url("../../yui/build/assets/skins/sam/editor-sprite.gif");
background-repeat:no-repeat;
background-position: 3px -33px;
left:5px;
height: 25px;
width: 25px;
}
.yui-button.btn_bold button {
background-image: url("../../yui/build/assets/skins/sam/editor-sprite.gif");
background-repeat:no-repeat;
background-position: 3px 3px;
left:5px;
height: 25px;
width: 25px;
}
.yui-button.btn_left button {
background: url("../../yui/build/assets/skins/sam/editor-sprite.gif") no-repeat 3px -321px;
left:5px;
height: 25px;
width: 25px;
}
.yui-button.btn_center button {
background: url("../../yui/build/assets/skins/sam/editor-sprite.gif") no-repeat 3px -357px;
left:5px;
height: 25px;
width: 25px;
}
.yui-button.btn_right button {
background: url("../../yui/build/assets/skins/sam/editor-sprite.gif") no-repeat 3px -393px;
left:5px;
height: 25px;
width: 25px;
}
fieldset.myToolbar {
padding: 1;
background-color:#f0f0f0;
}
#idFormula {
border:solid 1px black;
padding:3px;
}
#idPos1 {
border-style:ridge;
width:12em;
}
#idStatus {
border-style:inset;
width:12em;
}
/*
.yui-skin-sam .yui-dt table {
width:100%;
}
*/
table.notable tr, th, td {
border-collapse:collapse;
border:none;
padding: 2 5 2 5;
}
div.yuimenu .bd {
zoom: normal;
}
</style>
<script type="text/javascript">
var loader = new YAHOO.util.YUILoader( {
require : [ 'utilities', 'fonts', 'datatable', 'datasource', 'dragdrop', 'button', 'layout', 'menu', 'logger', 'container' ],
// base: '../yui/build/',
timeout: 3000,
allowRollup : true,
combine: false
// filter : 'DEBUG'
});
loader.addModule({
name: 'TS4.dtVis',
type: 'js',
requires: [ 'event', 'datatable' ],
fullpath: 'datatable_nav.js' // available here https://gist.github.com/1019657
});
loader.addModule({
name: 'sm_parser',
type: 'js',
fullpath: 'parser.js' // available here https://gist.github.com/1019660
});
loader.insert({
// additional modules can be added here ...
require : [ 'TS4.dtVis', 'sm_parser' ],
// The function to call when all script/css resources have been loaded
onSuccess: function() {
//
// Aliases for YUI placeholders
//
var YDom = YAHOO.util.Dom,
YUtil = YAHOO.util,
YEvent = YAHOO.util.Event,
YLang = YAHOO.lang,
YConnect = YUtil.Connect;
YAHOO.util.Event.onDOMReady( function() {
//========================================================================================================================
// Create a screen Layout - with TOP, CENTER and BOTTOM
//========================================================================================================================
var layout = new YAHOO.widget.Layout({
units : [
{position:'top', body:'top_markup', height:128, gutter:"0 0 0 5" },
{position:'center', body:'center_markup', scroll:true, gutter:"0 0 0 5" },
{position:'bottom', body:'bot_markup', height:30, gutter:"0 0 0 5"}
]
});
layout.on('render', function(){
YEvent.onContentReady('divTable', makeGrid); // onRender, call "makeGrid" to do the heavy work
});
layout.render();
//========================================================================================================================
// Main Routine - Create a spreadsheet "grid"
//========================================================================================================================
function makeGrid() {
//
// Local storage
//
/*
* ssCells is the SpreadSheet cell object.
* It is populated with "active" cells, i.e. cells that have a non-null entry
*
* Each member of this object has keys of the cell address, as in
* the following;
* ssCells['E5'] which is the cell at Column E and Row 5,
* this can also be referred to in JS as ssCells.E5 but I use the prior format
* Properties
* .col {String} Column key for this cell
* .row {Number} Row ID for this cell (this is DataTable RecordIndex PLUS one)
* .cell {HTML El} Cell TD Liner element (DIV)
* .formula {String} Formula prefixed with "=" sign
* .value {Mixed} The raw data value (unformatted)
* .display {Mixed} The formatted data value (actually displayed in DT)
* .dcells[] {Array} Cell addresses of "dependent" cells, linked by formula
*
*/
var ssCells = {};
//
// Define Grid formatters as named members for easy reference
//
var fmtOptions = {};
fmtOptions['comma2'] = { type:'number', fmt_options:{ decimalPlaces: 2, thousandsSeparator: "," }, fmter:YUtil.Number };
fmtOptions['comma'] = { type:'number', fmt_options:{ decimalPlaces: 0, thousandsSeparator: "," }, fmter:YUtil.Number };
fmtOptions['dollar2'] = { type:'number', fmt_options:{ prefix: "$ ", decimalPlaces: 2, thousandsSeparator: "," }, fmter:YUtil.Number };
fmtOptions['dollar'] = { type:'number', fmt_options:{ prefix: "$ ", decimalPlaces:0, thousandsSeparator: "," }, fmter:YUtil.Number };
fmtOptions['num'] = { type:'number', fmt_options:{ decimalPlaces:0 }, fmter:YUtil.Number };
fmtOptions['num2'] = { type:'number', fmt_options:{ decimalPlaces:2 }, fmter:YUtil.Number };
fmtOptions['none'] = { type:'number', fmt_options:{ }, fmter:null };
fmtOptions['mdy'] = { type:'date', fmt_options:{ format: '%m/%d/%y' }, fmter:YUtil.Date };
//
// Setup Local Data blank data and JSARRAY DataSource
//
var blank_row = {},
nrow = 23,
ncol = 13,
i=0,
dlgProps=null,
dlgForm=null,
dlgDep=null;
var buffer = null;
//
// Create the Columns (for DataTable) and Fields (for DataSource)
//
var txt_editor = new YAHOO.widget.TextboxCellEditor({disableBtns:true});
var cols = [ 'rowSelCol' ];
var myCols = [{key:'rowSelCol', label:'', className:'firstCol'} ];
var myFlds = [ 'rowSelCol' ];
for (i=0; i<ncol; i++) {
var keyName = (i<26) ? String.fromCharCode(65+i) : 'A'+String.fromCharCode(65+i-26);
blank_row[keyName] = null;
cols.push( keyName );
myCols.push( {key:keyName, colNum:(i+1), resizeable:true, sortable:false, editor:txt_editor, width:70 } );
myFlds.push( keyName );
}
//
// Create the JSARRAY data (for DataSource), copy rows of initially blank data
// ... this creates an array of "nrow" items each containing an object of "ncol" members containing 'null'
//
var jsData = [];
for(i=0; i<nrow; i++) {
var blank_copy = {rowSelCol:i+1};
for(var prop in blank_row)
blank_copy[prop] = blank_row[prop];
jsData.push( blank_copy );
}
//
// Now let's Put some sample data initially in to give us a start ...
//
var jsSample = [
{r:3, c:'b', data:'Ticker'},
{r:3, c:'c', data:'Company'},
{r:3, c:'d', data:'Shares'},
{r:3, c:'e', data:'Price'},
{r:3, c:'f', data:'Commission'},
{r:3, c:'g', data:'TOTAL'},
{r:4, c:'b', data:'BHP'},
{r:4, c:'c', data:'Billiton Ltd'},
{r:4, c:'d', data:'125'},
{r:4, c:'e', data:'92.04'},
{r:4, c:'f', data:'11.93'},
{r:4, c:'g', data:'=d5*e5+f5'}];
for(i=0; i<jsSample.length; i++) {
var ld = jsSample[i];
jsData[ ld.r ][ ld.c.toUpperCase() ] = ld.data;
ssCells[ ld.c.toUpperCase() + (ld.r+1) ] = { row:ld.r, col:ld.c.toUpperCase(), value:ld.data };
}
ssCells['G5'].formula = ssCells['G5'].value;
//
// Define the DS
//
var myDS = new YUtil.DataSource(jsData);
myDS.responseType = YUtil.DataSource.TYPE_JSARRAY;
myDS.responseSchema.fields = myFlds;
//
// Create the DataTable
//
// var center_body = layout.getUnitByPosition('center').getSizes().body; // used with Scrolling DT attempt
var myDT = new YAHOO.widget.DataTable( 'divTable', myCols, myDS, {
selectionMode:'cell'
// selectionMode:'cellrange'
} );
// scrollable:true,
// height: center_body.h -10 + 'px',
// width: center_body.w - 8 + 'px'
//-----------------------------------------------------------------------------
// Define some new methods for this DataTable and helper functions
//-----------------------------------------------------------------------------
//
// Call my custom method (loaded via datatable_nav.js) to add Cell navigation to the DataTable
// ... i.e. arrow keys, wrapping at last first row/column, etc...
//
myDT.setVisKeyListeners();
//
// augment this DataTable to add an object variable to track the "last selected" item
// within the DT, either an individual Cell, an entire Row or a Column
//
// .obj_type {String} String either of 'col', 'row' or 'cell'
// .row {Number} The ROW number (actually setting of 'rowSelCol'
// .col {String} The COLUMN key name ( 'A', 'B', etc...)
//
myDT.last_sel = { obj_type:null, row:null, col:null };
//
// Takes in a grid address as "B19" and returns the row column pair as
// .col: {String} col_key name (ex. "B")
// .row: {Number} row_id or 'rowSelCol' data item (ex. "19")
//
var gridColRow = function( address ) {
var col_part = address.split(/[0-9]/),
// col_id = col_part[0].toUpperCase(),
col_id = col_part[0],
row_id = parseInt( address.split(col_id)[1] );
return { col:col_id.toUpperCase(), row:row_id };
}
//
// new method that returns the Grid address (ex; 'B6') for a
// given TD element of the DataTable
//
myDT.getGridAddress = function( elCell ) {
var oRec = myDT.getRecord(elCell),
oCol = myDT.getColumn(elCell),
address = oCol.key + (myDT.getRecordIndex(oRec)+1);
return address;
}
//
// I was getting some wonky errors using the 2.9 selectColumn,
// so I redefined one here ...
//
myDT.myselectColumn = function( oCol ) {
var rs = this.getRecordSet(),
rslen = rs.getLength();
for(var i=0; i<rslen; i++)
this.selectCell( {record:this.getRecord(i), column:oCol } );
}
//
// updates the Formula toolbar entry field ...
//
function setToolbarFormula( cell_ref, val ) {
var cell_value = ( YLang.hasOwnProperty( ssCells, cell_ref ) && ssCells[cell_ref].formula ) ? ssCells[cell_ref].formula : val;
YDom.get('idFormula').value = cell_value;
}
//
// a change listener on the Formula toolbar,
// if the <input> field on formula bar is changed, place the changed values
// in the last selectecd Cell and update the Grid.
//
YEvent.on( 'idFormula', 'change', function(evt){
if ( myDT.last_sel.obj_type !== 'cell' ) return;
var lobj = myDT.last_sel, // the last selected cell object
address = lobj.col + lobj.row; // this will be grid "address"
var cell_obj = { col:lobj.col, row:lobj.row, value:this.value };
myDT.saveGridCell( address, cell_obj );
myDT.focus(); // set focus back to TBODY so more key strokes can be captured
} );
var getCell = function( address ) {
return ( YLang.hasOwnProperty( ssCells, address ) ) ? ssCells[address] : null;
}
/*
// Helper function, because IE doesn't like Array.indexOf (doh ??)
var indexArray = function( haystack, needle ) {
for(var i=0; i<haystack.length; i++)
if ( haystack[i] === needle ) return i;
return false;
}
*/
//-----------------------------------------------------------------------------
// Define some Event Listeners/handlers on DataTable
//-----------------------------------------------------------------------------
//
// just a hack to hide the whole display until DT is rendered and ready,
// to avoid showing partially completed stuff
//
var ifirst = true; // just a toggle flag, used once !
myDT.subscribe( 'postRenderEvent', function(){
if ( ifirst ) {
YDom.removeClass( 'allStuff', 'divHide');
ifirst = false;
this.saveGridCell( 'G5', {value:ssCells['G5'].formula})
}
});
//
// listener to allow Cell Editing based upon dbl-click
//
myDT.subscribe( 'cellDblclickEvent', myDT.onEventShowCellEditor);
myDT.subscribe( 'editorShowEvent', function(oArgs){
var editor = oArgs.editor,
oRec = editor.getRecord(),
oCol = editor.getColumn(),
col_key = oCol.key,
row_id = oRec.getData('rowSelCol'),
address = col_key + row_id;
//
// check if this cell exists
//
if ( YLang.hasOwnProperty( ssCells, address ) ) {
// if cell exists AND has a formula, edit the formula and not the "value"
if ( ssCells[ address ].formula ) editor.textbox.value = ssCells[ address ].formula;
}
});
myDT.subscribe( 'editorSaveEvent', function(oArgs) {
var newData = oArgs.newData;
if ( newData !== null ) {
var editor = oArgs.editor,
oRec = editor.getRecord(),
oCol = editor.getColumn(),
cell_td = editor.getTdEl();
var col_key = oCol.key,
row_id = oRec.getData('rowSelCol'),
address = col_key + row_id; // this will be grid "address"
var cell_obj = { cell:cell_td, col:col_key, row:row_id, value:newData };
this.saveGridCell( address, cell_obj ); // this method overrides the DT displayed contents based on formulas, etc..
}
} );
// when editor closes, for convenience set focus to TBODY
myDT.subscribe( 'editorUnblockEvent', function() { this.focus(); });
//
// cell click Handler,
// Keep track of CELL movements via a custom method to update current position,
// also check for ROW click (in column 'rowSelCol')
//
myDT.subscribe("cellClickEvent", function(oArgs) {
var target = YEvent.getTarget(oArgs),
column = this.getColumn(target),
record = this.getRecord(target),
elTd = this.getTdEl( {record:record, column:column} );
//
// clean up prior selections
//
this.unselectAllCells();
this.unselectAllRows();
switch (column.key) {
case 'rowSelCol': // this is a ROW click
// select this row
this.selectRow( record );
// update 'Current Position' DIV
YDom.get('idPos').innerHTML = ' Row ' + record.getData('rowSelCol');
// update last selected object for tracking purposes
this.last_sel = { obj_type:'row', row:record.getData('rowSelCol'), col:null };
break;
default:
// selec this TD cell
this.selectCell( target );
// call my custom event to update position tracking, position id, formula, etc...
this.fireEvent( "updateVisCellPosition", {event:oArgs, newCell:elTd } );
break;
}
} ); // end cellClickEvent
//
// for THEAD clicks, assume user "selects" whole column
//
myDT.subscribe( 'theadCellClickEvent', function(oArgs) {
var target = YEvent.getTarget(oArgs),
column = this.getColumn(target);
//
// only do something if very first column (i.e. row id's) is not clicked
//
if (column.key !== 'rowSelCol') {
this.unselectAllCells();
this.unselectAllRows();
// use my method to selectColumn
this.myselectColumn(column);
// update 'Current Position' DIV
YDom.get('idPos').innerHTML = ' Column ' + column.key;
// update last selected object for tracking purposes
this.last_sel = { obj_type:'col', row:null, col:column.key };
}
});
//
// respond to my Custom Event,
// called by cellClick handler AND keyboard (i.e. arrow key) handler defined
// in .setVisKeyListners (in datatable_nav.js file ...)
//
myDT.subscribe( 'updateVisCellPosition', function(oArgs) {
var elTd = oArgs.newCell,
col_key = this.getColumn(elTd).key,
row_id = this.getRecord(elTd).getData('rowSelCol');
// update 'Current Position' DIV
YDom.get('idPos').innerHTML = col_key + row_id;
// update Toolbar formula entry field
setToolbarFormula( col_key + row_id, this.getRecord(elTd).getData(col_key) );
// update last selected object for tracking purposes
this.last_sel = { obj_type:'cell', row:row_id, col:col_key };
});
//
// Safely evaluates the formula, subsituting the values from the grid address
// This uses a JavaScript Parser from http://silentmatt.com/javascript-expression-evaluator/
// and NOT eval !
//
var doFormula = function( aform, myCell ) {
var expr = Parser.parse( aform ),
vars = expr.variables(),
rtn_value = null,
rtn_form = '=' + aform;
//
// Case A : The formula contains "variables", assume they are cell addresses
//
if ( vars.length !== 0 ) {
var subs = {},
ok=true; // flag for circ reference
// substitute cell "value" (unformatted part) for equation parser ...
for(var i=0; i<vars.length; i++) {
if ( vars[i].toUpperCase() === myCell ) {
ok=false; // circular reference !
// alert("Circular reference at " + myCell + "!");
vars.splice(i,1);
rtn_form = null;
} else
subs[ vars[i] ] = ( ssCells[ vars[i].toUpperCase() ] ) ? ssCells[ vars[i].toUpperCase() ].value : 0;
}
rtn_value = ( ok ) ? expr.evaluate( subs ) : '!!CREF!!'; // evaluate expression with variables, math result returned
} else {
//
// Case B : Formula is arithmetic only, no cell references
//
rtn_value = expr.evaluate();
}
return { value:rtn_value, formula:rtn_form, dcells:vars };
}
//========================================================================================
// The MAIN EVENT here,
//
// This custom method handles cell-editing changes to a CELL,
// and formatter changes,
// and class selection changes
//
// The primary point of this method is to do two things;
// 1. To maintain the integrity of the SpreadSheet data object >>> ssCells
// 2. To update the display (DataTable) with the proper values from ssCells
//========================================================================================
myDT.saveGridCell = function( address, obj ) {
var addr = gridColRow( address ); // convert the address into {row:row_id, col:col_key} format
//
// If an ssCell exists for this address, add all properties passed in,
// but if cell doesn't exist, add a new entry ...
//
if ( getCell(address) ) {
for(prop in obj)
ssCells[address][prop] = obj[prop];
} else {
ssCells[address] = { cell:null, value:null, formula:null, col:addr.col, row:addr.row, fmt_name:null };
for(prop in obj)
ssCells[address][prop] = obj[prop];
}
//
// If a value is included, check if it is a formula or assign it to .value
//
if ( YLang.hasOwnProperty(obj, 'value') ) {
var new_value = null,
new_formula = null,
value_data = obj.value;
//
// Enter here if first character is "=", user entered formula
//
if ( value_data && value_data.substr(0,1) === '=') {
var theForm = value_data.slice(1); // get remainder after the "=" sign
var estr = doFormula( theForm, address);
new_value = estr.value;
new_formula = estr.formula;
// update dependent cells
var subs = estr.dcells;
for(var i=0; i<subs.length; i++) {
var kaddr = subs[i].toUpperCase();
//
// ... all of the following is to add cell "dependencies" to that if
// a formula variable value changes it can be updated ...
//
if ( kaddr === address ) { // look for a circular (i.e. self) reference
// alert("Circular reference at " + address + "!");
new_formula = null;
new_value = "!!CREF!!";
} else { // no circular references, continue ...
if ( ssCells[kaddr] ) {
if ( ssCells[kaddr].dcells ) {
// if ( indexArray( ssCells[kaddr].dcells, address ) === false )
if ( ssCells[kaddr].dcells.indexOf(address) === false )
ssCells[kaddr].dcells.push( address );
}
else
ssCells[kaddr].dcells = [ address ];
} else {
var ka = gridColRow(kaddr);
ssCells[kaddr] = { col:ka.col, row:ka.row, value:null, dcells:[ address ] };
}
}
}
}
else { // data is not a formula
new_value = value_data;
new_formula = null;
}
ssCells[address].value = new_value; // this is the oData, unformatted, raw data
ssCells[address].formula = new_formula; // store the formula, if one entered ...
} // end obj .value
// if ( ssCells[address].isCR ) return;
//
// If a .value is assigned, update the cell ...
//
if ( YLang.hasOwnProperty(ssCells[address], 'value') ) {
var frm_cell = getCell(address),
fopt = (frm_cell && frm_cell.fmt_name) ? fmtOptions[frm_cell.fmt_name] : null;
var oRec = this.getRecord( (addr.row - 1) ),
oCol = this.getColumn( addr.col ),
theValue = ( fopt ) ? fopt.fmter.format( ssCells[address].value, fopt.fmt_options) : ssCells[address].value;
this.updateCell(oRec, oCol, theValue );
ssCells[address].display = theValue;
}
//
// If a class was assigned, apply it
//
if ( YLang.hasOwnProperty(ssCells[address],'class_name') ) {
var oRec = this.getRecord( (addr.row - 1) ),
oCol = this.getColumn( addr.col ),
theCell = this.getTdLinerEl( {record:oRec, column:oCol } );
if ( ssCells[address].class_purge ) {
YDom.removeClass( theCell, 'align-center' );
YDom.removeClass( theCell, 'align-right' );
YDom.removeClass( theCell, 'align-left' );
}
//
// toggle class off and on ...
//
if ( YDom.hasClass( theCell, ssCells[address].class_name ) )
YDom.removeClass( theCell, ssCells[address].class_name );
else
YDom.addClass( theCell, ssCells[address].class_name );
}
//
// Scan through any "dependent" cells and update their values,
// i.e. if a formula referenced THIS cell, update the formula
// calculation with this new value ...
//
if ( ssCells[address].dcells && ssCells[address].dcells.length > 0 ) {
var dcells = ssCells[address].dcells;
for(i=0; i<dcells.length; i++) {
if ( dcells[i] === address ) {
alert("circular reference!");
return;
} else
this.saveGridCell( dcells[i], {value:ssCells[dcells[i]].formula});
}
}
} // end .saveGridCell
//-----------------------------------------------------------------------------
// Create a TOOLBAR with PushButton listeners
//-----------------------------------------------------------------------------
function onButtonClick(oArgs) {
var tar = YEvent.getTarget(oArgs),
localDT = arguments[1],
lobj = localDT.last_sel,
rtn_obj = null,
addr = lobj.col + lobj.row;
//
// Take action based upon which Toolbar button was pressed
//
switch( this.get('id') ) {
case 'idTBCenter':
rtn_obj = { class_name:'align-center', class_purge:true };
break;
case 'idTBLeft':
rtn_obj = { class_name:'align-left', class_purge:true };
break;
case 'idTBRight':
rtn_obj = { class_name:'align-right', class_purge:true };
break;
case 'idTBBold':
rtn_obj = { class_name:'bold', class_purge:false };
break;
case 'idTBItal':
rtn_obj = { class_name:'italic', class_purge:false };
break;
case 'idTB$2':
rtn_obj = { fmt_name:'dollar2' };
break;
case 'idTB$':
rtn_obj = { fmt_name:'dollar' };
break;
case 'idTBc2':
rtn_obj = { fmt_name:'comma2' };
break;
case 'idTBc':
rtn_obj = { fmt_name:'comma' };
break;
case 'idTBn':
rtn_obj = { fmt_name:null };
break;
}
if ( rtn_obj ) {
if ( !lobj.row || !lobj.col )
alert("OOPS, can't make formatting/class changes to Rows or Columns !");
else
myDT.saveGridCell( addr, rtn_obj ); // apply changes to this Cell
}
}
//
// Buttons for class formatters ... Alignment, Bold, Italic
//
var tb_left = new YAHOO.widget.Button({ label:"", id:"idTBLeft", container:"idToolbar", title:'Left-align' });
tb_left.addClass('btn_left');
tb_left.on("click", onButtonClick, myDT);
var tb_center = new YAHOO.widget.Button({ label:"", id:"idTBCenter", container:"idToolbar", title:'Center-align' });
tb_center.addClass('btn_center');
tb_center.on("click", onButtonClick, myDT);
var tb_right = new YAHOO.widget.Button({ label:"", id:"idTBRight", container:"idToolbar", title:'Right-align' });
tb_right.addClass('btn_right');
tb_right.on("click", onButtonClick, myDT);
var tb_bold = new YAHOO.widget.Button({ label:"", id:"idTBBold", container:"idToolbar", title:'Bold' });
tb_bold.addClass('btn_bold');
tb_bold.on("click", onButtonClick, myDT);
var tb_ital = new YAHOO.widget.Button({ label:"", id:"idTBItal", container:"idToolbar", title:'Italic' });
tb_ital.addClass('btn_italic');
tb_ital.on("click", onButtonClick, myDT);
//
// Buttons for data formatters ... Comma, Currency, etc..
//
var tb_$2 = new YAHOO.widget.Button({ label:"$,2", id:"idTB$2", container:"idToolbar", title:'Currency with 2 decimals ($ x,xxx.xx)' });
tb_$2.on("click", onButtonClick, myDT);
var tb_$ = new YAHOO.widget.Button({ label:"$,", id:"idTB$", container:"idToolbar", title:'Currency without decimals ($ x,xxx)' });
tb_$.on("click", onButtonClick, myDT);
var tb_c2 = new YAHOO.widget.Button({ label:",2", id:"idTBc2", container:"idToolbar", title:'Comma with 2 decimals (x,xxx.xx)' });
tb_c2.on("click", onButtonClick, myDT);
var tb_c = new YAHOO.widget.Button({ label:",", id:"idTBc", container:"idToolbar", title:'Comma without decimals (x,xxx)' });
tb_c.on("click", onButtonClick, myDT);
//
// Button for INFO page !!
//
var tb_note = new YAHOO.widget.Button( { label:"Click for Info!!", container:"idToolbar", title:'Notes about this Demo!!' } );
tb_note.on("click", function(){
var i=0;
var panel = new YAHOO.widget.Panel( "info_panel", {
fixedcenter: true,
close: true,
draggable: true,
zindex:4,
modal: true,
visible: false } );
panel.render();
YDom.removeClass( "info_panel", "divHide");
panel.show();
} );
//-----------------------------------------------------------------------------
// Define ContextMenus (i.e. right-click popup menus) on DataTable
//-----------------------------------------------------------------------------
//
// Takes in a grid address as "B19" and returns the row column pair as
// .col: {String} col_key name (ex. "B")
// .row: {Number} row_id or 'rowSelCol' data item (ex. "19")
//
var indexColRow = function( address ) {
var col_part = address.split(/[0-9]/),
col_id = col_part[0],
row_id = parseInt( address.split(col_id)[1] );
var col_indx = myDT.getColumn( col_id.toUpperCase() ).getKeyIndex();
return { col:col_indx, row:row_id };
}
var deepCopy = function( obj ) {
var rtn = {};
for(prop in obj) {
}
}
//
// Define ContextMenu for the TBODY element
//
var onContextMenuClick = function(p_sType, p_aArgs, local_DT) {
var task = p_aArgs[1],
tar = this.contextEventTarget,
elTd = local_DT.getTdEl(tar),
oRec = local_DT.getRecord(tar),
oCol = local_DT.getColumn(tar),
address = local_DT.getGridAddress( elTd );
// task.groupIndex for groups 0, 1, 2
// task.index is within a group
switch(task.value) {
case 0: // Cut this cell ...
buffer = { key:address, value:ssCells[address] };
delete ssCells[address];
myDT.updateCell( oRec, oCol, null);
break;
case 1: // Copy this cell ...
buffer = { key:address, value:ssCells[address] };
break;
case 2: // Paste this cell ...
if ( !buffer )
alert("There is nothing to paste in the Buffer !");
else {
//
// Clear out the existing Cell contents & create a new Cell
//
delete ssCells[address];
var cr = gridColRow( address );
ssCells[address] = { col:cr.col, row:cr.row };
//
// Make a local copy of the buffer to work with
//
var lobj = YLang.merge( buffer.value );
base_row = lobj.row;
base_col = lobj.col;
lobj.row = cr.row;
lobj.col = cr.col;
lobj.dcells = [];
//
// If buffer object was a formula, convert it to paste references
//
if ( lobj.formula && lobj.formula !== null && lobj.formula.substr(0,1) === '=' ) {
var new_indx = indexColRow( address );
var theForm = lobj.formula.slice(1);
var expr = Parser.parse( theForm ),
vars = expr.variables();
//
// The formula contains "address" variables,
// so we need to modify them via relative addressing
//
if ( vars && vars.length > 0 ) {
var new_form = lobj.formula,
base_indx = indexColRow( base_col + base_row );
for (var i=0; i<vars.length; i++) {
var var_indx = indexColRow( vars[i] );
var_indx.row--;
var rel_col = base_indx.col - var_indx.col,
rel_row = base_indx.row - var_indx.row,
nvar_col = myDT.getColumn( (new_indx.col - rel_col) ).key,
nvar_row = new_indx.row - rel_row,
nvar_addr = nvar_col + nvar_row;
//
// Replace the old address with the new one
//
var re = new RegExp( vars[i], 'g' );
new_form = new_form.replace(re, nvar_addr);
}
} else // Formula is simple arithmetic, no variables
new_form = lobj.formula;
//
// Clear out the old object properties
//
delete lobj.formula;
delete lobj.display;
delete lobj.dcells;
lobj.value = new_form.toLowerCase();
}
myDT.saveGridCell( address, lobj );
// buffer = null;
}
break;
// var expr = Parser.parse( aform ),
// vars = expr.variables(),
case 3: // Paste special
alert("These menu options are not setup yet, this is only a DEMO!");
break;
case 4: // Undo
delete ssCells[address];
myDT.saveGridCell( buffer.key, buffer.value );
break;
case 5: // clear contents
buffer = { key:address, value:ssCells[address] };
myDT.saveGridCell( address, { value:0.0 } );
delete ssCells[address];
myDT.updateCell( oRec, oCol, null);
break;
case 6: // Cell properties
// alert( 'Properties of ' + address + ' value=' + ssCells[address].value );
if ( dlgProps === null ) {
var doSave = function(o){
alert('saving stuff');
this.cancel();
}
var doCancel = function(o){
this.cancel();
}
dlgProps = new YAHOO.widget.Dialog("dlgProps", {
//width: "400px",
fixedcenter: true,
close: true,
draggable: true,
zindex: 4,
visible: false,
postmethod: 'none',
buttons: [{
text: 'Close',
handler: doCancel,
isDefault: true
}]
});
}
dlgProps.setHeader("Cell Properties:");
var dcj = ( ssCells[address].dcells && ssCells[address].dcells.length>0 ) ? ssCells[address].dcells.join(' & ') : 'None';
var html = '<table border=0 class="notable">';
html += '<tr><td>Col:</td><td>' + ssCells[address].col + "</td></tr>";
html += '<tr><td>Row:</td><td>' + ssCells[address].row + '</td></tr>';
html += '<tr><td>Value:</td><td>' + ssCells[address].value + "</td></tr>";
html += '<tr><td>Formula:</td><td>' + (ssCells[address].formula || 'none') + "</td></tr>";
html += '<tr><td>FmtName:</td><td>' + (ssCells[address].fmt_name || 'none') + "</td></tr>";
html += '<tr><td>ClassName:</td><td>' + (ssCells[address].class_name || 'none') + "</td></tr>";
html += '<tr><td>Dep Cells:</td><td>' + dcj + "</td></tr></table>";
dlgProps.setBody( html );
dlgProps.render();
YDom.removeClass( dlgProps.id, "divHide");
dlgProps.show();
break;
case 7: // Cell formatting
alert( 'Formatting of ' + address + ' Format_Name=' + ssCells[address].fmt_name + ' Class_Name=' + ssCells[address].class_name );
break;
case 8: // Cell dependencies
var dcj = ( ssCells[address].dcells && ssCells[address].dcells.length>0 ) ? ssCells[address].dcells.join(' & ') : 'None';
alert( 'Dependencies of ' + address + ' value=' + ssCells[address].formula + ' are = [' + dcj + ']' );
break;
}
};
var myCMenu = new YAHOO.widget.ContextMenu("my_CMenu", { trigger: myDT.getTbodyEl()} );
var my_menuItems = [
[
{ text: "Cut", value:0 },
{ text: "Copy", value:1 },
{ text: "Paste",value:2 },
{ text: "Paste Special", value:3, disabled:true },
],
[
{ text: "Undo", value:4 },
{ text: "Clear", value:5 },
],
[
{ text: "Properties ...", value:6 },
{ text: "Formatting ...", value:7 },
{ text: "Dependencies", value:8 }
]
];
myCMenu.addItems( my_menuItems );
myCMenu.render("divTable");
myCMenu.clickEvent.subscribe(onContextMenuClick, myDT);
//
// Define ContextMenu for the THEAD elements
//
function onMenuClick( oType, oArgs, oDT ) {
alert("This menu click action is not setup yet - this is just a DEMO !!");
}
var myHMenu = new YAHOO.widget.ContextMenu("myHMenuEl", { trigger:myDT.getTheadEl() });
myHMenu.addItems( [ "Insert Column Before", "Delete Column" ] );
myHMenu.render("divTable");
myHMenu.clickEvent.subscribe(onMenuClick, myDT);
//
// Can I define a ContextMenu on the first column? (the ROW selectors?)
//
var tds = [];
var RS = myDT.getRecordSet();
for(var i=0; i<RS.getLength(); i++) {
var rec = RS.getRecord(i);
var td = myDT.getFirstTdEl( rec );
tds.push( td );
}
var myRMenu = new YAHOO.widget.ContextMenu("myRMenuEl", {
trigger: tds // tds contains Dom elements for first column, each row ...
});
myRMenu.addItems( [ "Insert Row Before", "Delete Row" ] );
myRMenu.render("divTable");
myRMenu.clickEvent.subscribe(onMenuClick, myDT);
} // end makeGrid
}); // onDOMReady
} // onSuccess
});
//loader.insert();
</script>
</head>
<body class=" yui-skin-sam">
<div id="allStuff" class="divHide">
<div id="info_panel" class="divHide">
<div class="hd">YUI 2 - SpreadSheet via DataTable</div>
<div class="bd">
<span style="text-align:center">Please Note : <b>THIS IS A DEMO !!!</b></span>
<p>It is simply intended to demonstrate features of using a YUI DataTable in a "grid" fashion</p>
Specifically;
<ul>
<li>Keyboard navigation within a DataTable (via prototype methods in datatable_nav.js file)</li>
<li>Very simple data object for populated cells to add properties (formulas, formatters, classes, dependencies for formulas ...)</li>
<li>Cell-editing while changing display values in cells</li>
<li>Data formatters for cells (numeric, currency only)</li>
<li>Adding simple classes for visual formatting (alignment, bold, italic)</li>
<li>A simplistic formula parser (from a great Parser by <a href="http://silentmatt.com/javascript-expression-evaluator/" target="_blank">SilentMatt</a>) to avoid Eval()!</li>
<li>Simple formulas (prepend with '=' sign) that work including linking to other cells,
<br/>basic circular reference checking, and some math functions (see prior item).</li>
<li>A basic "right-click" menu is provided, for Cut, Copy, Paste and custom menu items</li>
<li>Cut/Copy/Paste works basically (including copy/paste formulas)</li>
</ul>
<p>This has been tested in FF 3+, Chrome, Safari and IE9.</p>
Feel free to send me questions at the YUI Forum <a href="http://yuilibrary.com/forum/memberlist.php?mode=viewprofile&u=5872" target="_blank">stlsmiths</a>, but PLEASE DO NOT ask how to do Charts,
<br/>or how to figure your Amortization Schedule --- <b>THIS IS A DEMO !!!</b>
<br/><br/>The source code is available at <a href="https://gist.github.com/1019654" target="_blank">Github</a>, support files needed too (see the gist)
</div>
<div class="ft"></div>
</div>
<div id="dlgProps" class="divHide">
<div class="hd">Cell Properties:</div>
<div class="bd"></div>
</div>
<div id="top_markup">
<h3>A DataGrid "SpreadSheet" Example</h3>
<fieldset id="idToolbar" class="myToolbar"></fieldset>
<fieldset id="idFormbar" class="myToolbar">Formula : <input type="text" id="idFormula" value="" size="40" />&nbsp;&nbsp;Current Position: <span id="idPos">No Selection</span>
</fieldset>
<!-- <a href="#" id="idStat">Click Me</a> -->
</div>
<div id="center_markup">
<div id="divTable"></div>
</div>
<div id="bot_markup"><span id="idStatus">Status : </span></div>
</div>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment