Created
June 10, 2011 20:05
-
-
Save stlsmiths/1019654 to your computer and use it in GitHub Desktop.
YUI 2 Spreadsheet Demo via DataTable
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <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" /> 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