1 Rico.SpreadSheet = Class.create();
3 Rico.SpreadSheet.prototype = {
5 initialize: function( tableId, options ) {
6 var ssopts = Object.extend({
9 highlightElem : 'selection',
10 highlightClass: 'ricoSheet_Selection',
12 highlightMethod : 'outline',
18 prefetchBuffer : false,
19 useUnformattedColWidth : false,
20 menuEvent : 'contextmenu',
21 columnSpecs : [ {width:30,Hdg:'',noResize:true} ]
23 Object.extend(this, new Rico.SimpleGrid(tableId, ssopts));
24 if (!this.hdrTabs[0]) {
27 this.simpleGridInit();
29 this.menu = new Rico.Menu('7em');
33 createCells: function() {
34 var s="<table id='"+this.tableId+"_tab0h' class='ricoLG_table ricoLG_top ricoLG_left' cellspacing='0' cellpadding='0'><thead>";
35 s+="<tr class='' id='"+this.tableId+"_tab0h_main'><td><div class='ricoLG_col'><div class='ricoLG_cell'> </div></div></td></tr></thead></table>";
36 s+="<table id='"+this.tableId+"_tab0' class='ricoLG_table ricoLG_bottom ricoLG_left' cellspacing='0' cellpadding='0'>";
37 s+="<tr><td><div class='ricoLG_col'>";
39 for (i=1; i<=this.options.numRows; i++)
40 s+="<div class='ricoLG_cell'>"+i+"</div>";
41 s+="</div></td></tr></table>";
42 this.frozenTabs.innerHTML=s;
44 s="<table id='"+this.tableId+"_tab1h' class='ricoLG_table ricoLG_top ricoLG_right' cellspacing='0' cellpadding='0'>";
45 s+="<thead><tr class='' id='"+this.tableId+"_tab1h_main'>";
46 for (i=0; i<this.options.numColumns; i++)
47 s+="<td><div class='ricoLG_col'><div class='ricoLG_cell'>"+String.fromCharCode(65+i)+"</div></div></td>";
48 s+="</tr></thead></table>";
49 this.innerDiv.innerHTML=s;
51 s="<table id='"+this.tableId+"_tab1' class='ricoLG_table ricoLG_bottom ricoLG_right' cellspacing='0' cellpadding='0'><tr>";
52 for (c=1; c<=this.options.numColumns; c++) {
53 s+="<td><div class='ricoLG_col'>";
54 for (r=0; r<this.options.numRows; r++)
55 s+="<div id='"+this.tableId+"_cell_"+r+"_"+c+"' class='ricoLG_cell'></div>";
59 this.scrollDiv.innerHTML=s;
62 initSheet: function() {
66 this.highlightDiv[i] = this.createDiv("highlight",this.scrollDiv);
67 this.highlightDiv[i].style.display="none";
68 this.highlightDiv[i].id+=i;
69 this.highlightDiv[i].style[i % 2==0 ? 'height' : 'width']="0px";
71 for (c=1; c<this.columns.length; c++) {
73 Event.observe(col.hdrCellDiv,'click',this.selectCol.bindAsEventListener(this,c),false);
74 for (r=0; r<col.numRows(); r++) {
81 var numrows=this.columns[0].numRows();
82 for (r=0; r<numrows; r++)
83 Event.observe(this.cell(r,0),'click',this.selectRow.bindAsEventListener(this,r),false);
84 Event.observe(this.columns[0].hdrCellDiv,'click',this.selectAll.bindAsEventListener(this),false);
86 if (!this.menu.grid) this.registerScrollMenu(this.menu);
87 this.menu.showmenu=this.menu.showSheetMenu;
89 this.inputArea=RicoUtil.createFormField(this.scrollDiv,'textarea',null,'inputArea');
90 this.inputArea.style.position='absolute';
91 this.inputArea.style.display='none';
92 this.inputArea.style.zIndex=2;
93 this.inputArea.cols=30;
94 this.inputArea.rows=4;
95 this.inputArea.blur();
96 this.clipBox=RicoUtil.createFormField(this.innerDiv,'textarea',null,'clipBox');
97 this.clipBox.style.position='absolute';
98 this.clipBox.style.display='none';
100 this.clipBox.rows=10;
101 this.clipBox.style.top='0px';
102 this.clipBox.style.left='0px';
103 this.selectCellRC(0,1);
104 this.mouseOverHandler = this.selectMouseOver.bindAsEventListener(this);
105 this.mouseUpHandler = this.selectMouseUp.bindAsEventListener(this);
106 Event.observe(this.inputArea,'keydown',this.inputKeydown.bindAsEventListener(this),false);
107 Event.observe(Prototype.Browser.IE ? document.body : window,'keydown',this.gridKeydown.bindAsEventListener(this),false);
108 Event.observe(this.tbody[1],"mousedown", this.selectMouseDown.bindAsEventListener(this), false);
110 // disable drag & select events in IE
111 this.outerDiv.ondrag = this.disableEvent;
112 this.outerDiv.onselectstart = this.disableEvent;
113 this.tbody[1].ondrag = this.disableEvent;
114 this.tbody[1].onselectstart = this.disableEvent;
115 this.createFormatNumber();
118 textalign: function(dir) {
119 this.updateSelectionStyle('text-align',dir);
122 textwrap: function() {
123 this.toggleAttr('white-space', 'normal', 'nowrap');
126 selectRow: function(e,rownum) {
129 this.SelectIdxStart.column=1;
131 this.selectCellRC(rownum,1,false);
133 this.selectCellRC(rownum,this.columns.length-1,true);
136 selectCol: function(e,colnum) {
139 this.SelectIdxStart.row=0;
141 this.selectCellRC(0,colnum,false);
143 this.selectCellRC(this.pageSize-1,colnum,true);
146 selectAll: function(e) {
148 this.selectCellRC(0,1,false);
149 this.selectCellRC(this.pageSize-1,this.columns.length-1,true);
152 disableEvent: function(e) {
158 cellIndex: function(cell) {
159 var a=cell.id.split(/_/);
161 var r=parseInt(a[l-2],10);
162 var c=parseInt(a[l-1],10);
163 return {row:r, column:c, tabIdx:this.columns[c].tabIdx, cell:cell};
166 AdjustSelection: function(cell) {
167 var newIdx=this.cellIndex(cell);
168 if (this.SelectIdxStart.tabIdx != newIdx.tabIdx) return;
169 this.HideSelection();
170 this.SelectIdxEnd=newIdx;
171 this.ShowSelection();
174 selectMouseDown: function(e) {
175 if (this.highlightEnabled==false) return true;
177 var cell=Event.element(e);
179 if (!Event.isLeftClick(e)) return;
180 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
182 var newIdx=this.cellIndex(cell);
184 if (!this.SelectIdxStart) return;
185 this.selectCellRC(newIdx.row,newIdx.column,true);
187 this.selectCellRC(newIdx.row,newIdx.column,false);
192 pluginSelect: function() {
193 if (this.selectPluggedIn) return;
194 var tBody=this.tbody[this.SelectIdxStart.tabIdx];
195 Event.observe(tBody,"mouseover", this.mouseOverHandler, false);
196 Event.observe(this.outerDiv,"mouseup", this.mouseUpHandler, false);
197 this.selectPluggedIn=true;
200 unplugSelect: function() {
201 var tBody=this.tbody[this.SelectIdxStart.tabIdx];
202 Event.stopObserving(tBody,"mouseover", this.mouseOverHandler , false);
203 Event.stopObserving(this.outerDiv,"mouseup", this.mouseUpHandler , false);
204 this.selectPluggedIn=false;
207 selectMouseUp: function(e) {
209 var cell=Event.element(e);
210 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
212 this.AdjustSelection(cell);
215 selectMouseOver: function(e) {
216 var cell=Event.element(e);
217 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
219 this.AdjustSelection(cell);
223 getSelection: function() {
224 if (!this.SelectIdxStart || !this.SelectIdxEnd) return false;
225 var r1=Math.min(this.SelectIdxEnd.row,this.SelectIdxStart.row);
226 var r2=Math.max(this.SelectIdxEnd.row,this.SelectIdxStart.row);
227 var c1=Math.min(this.SelectIdxEnd.column,this.SelectIdxStart.column);
228 var c2=Math.max(this.SelectIdxEnd.column,this.SelectIdxStart.column);
229 return {r1:r1,c1:c1,r2:r2,c2:c2};
232 updateSelectOutline: function() {
233 var s=this.getSelection();
234 if (!s || s.r1 > s.r2) {
235 this.HideSelection();
238 var top1=this.columns[s.c1].cell(s.r1).offsetTop;
239 var cell2=this.columns[s.c1].cell(s.r2);
240 var bottom2=cell2.offsetTop+cell2.offsetHeight;
241 var left1=this.columns[s.c1].dataCell.offsetLeft;
242 var left2=this.columns[s.c2].dataCell.offsetLeft;
243 var right2=left2+this.columns[s.c2].dataCell.offsetWidth;
244 //window.status='updateSelectOutline: '+s.r1+' '+s.r2+' top='+top1+' bot='+bottom2;
245 this.highlightDiv[0].style.top=this.highlightDiv[3].style.top=this.highlightDiv[1].style.top=(top1-3) + 'px';
246 this.highlightDiv[2].style.top=(bottom2-2)+'px';
247 this.highlightDiv[3].style.left=(left1-2)+'px';
248 this.highlightDiv[0].style.left=this.highlightDiv[2].style.left=(left1-1)+'px';
249 this.highlightDiv[1].style.left=(right2-1)+'px';
250 this.highlightDiv[0].style.width=this.highlightDiv[2].style.width=(right2-left1-1) + 'px';
251 this.highlightDiv[1].style.height=this.highlightDiv[3].style.height=(bottom2-top1) + 'px';
252 for (var i=0; i<4; i++)
253 this.highlightDiv[i].style.display='';
256 isSelected: function(r,c) {
257 var s=this.getSelection();
258 return s ? (s.r1 <= r) && (r <= s.r2) && (s.c1 <= c) && (c <= s.c2) : false;
261 HideSelection: function(cellList) {
262 for (var i=0; i<4; i++)
263 this.highlightDiv[i].style.display='none';
266 ShowSelection: function() {
267 this.updateSelectOutline();
271 * @param what valid values are: null, 'all', 'formats', 'formulas', 'values'
273 clearSelection: function() {
274 var s=this.getSelection();
276 var args=$A(arguments);
277 var what=args.shift();
278 if (typeof what=='object') what=args.shift(); // in case first arg is an event object
279 var v=(!what || what=='all') ? 1 : 0;
280 var whatobj={formats:v,formulas:v,values:v};
281 if (typeof what=='string') whatobj[what]=1;
282 if (whatobj.values) whatobj.formulas=1;
283 for (var r=s.r1; r<=s.r2; r++) {
284 for (var c=s.c1; c<=s.c2; c++) {
285 var gridcell=this.columns[c].cell(r);
286 if (whatobj.formats) {
287 gridcell.style.cssText='';
288 gridcell.RicoFormat={};
290 if (whatobj.formulas) gridcell.RicoFormula=null;
291 if (whatobj.values) gridcell.RicoValue=null;
292 this.formatCell(gridcell);
297 selectCellRC: function(r,c,adjFlag) {
298 if (r < 0 || r >= this.columns[0].numRows()) return;
299 this.HideSelection();
301 if (this.SelectIdxStart.tabIdx == this.columns[c].tabIdx)
302 this.SelectIdxEnd={row:r, column:c, tabIdx:this.columns[c].tabIdx};
304 this.SelectIdxStart=this.SelectIdxEnd={row:r, column:c, tabIdx:this.columns[c].tabIdx};
305 var cell=this.columns[c].cell(r);
306 if (Prototype.Browser.IE)
307 cell.focus(); // causes IE to scroll cell into view
308 else if (cell.scrollIntoView)
309 cell.scrollIntoView(false);
311 this.ShowSelection();
314 moveSelection: function(dr,dc,adjFlag,e) {
315 var selIdx=adjFlag ? this.SelectIdxEnd : this.SelectIdxStart;
316 var newr=selIdx.row+dr;
317 var newc=selIdx.column+dc;
318 if (newr>=0 && newr<this.columns[0].numRows() && newc>=1 && newc<this.columns.length)
319 this.selectCellRC(newr,newc,adjFlag);
320 if (e) Event.stop(e);
323 formatCell: function(cell) {
324 // TO DO: add currency/date formatting here
325 var v=cell.RicoValue;
328 } else if (typeof(v)=='number') {
332 switch (typeof cell.RicoFormat) {
337 // assume v represents a date
338 var d=new Date((v-25569)*1000*86400);
339 d.setTime(d.getTime()+d.getTimezoneOffset()*60*1000);
340 v=d.formatDate(cell.RicoFormat);
343 v=v.formatNumber(cell.RicoFormat);
347 } else if (typeof v!='string') {
350 v=v.replace(/^(\s*)/, '');
351 cell.style.paddingLeft=(RegExp.$1.length/2)+'em';
355 // action='add' or 'remove'
356 updateDependencies: function(formulaCell,action) {
357 if (!formulaCell.RicoFormula) return;
358 //alert('updateDependencies '+action+': '+formulaCell.RicoRow+','+formulaCell.RicoCol);
359 var ranges=formulaCell.RicoFormula.getRanges();
360 for (var i=0; i<ranges.length; i++) {
361 if (!ranges[i]) continue;
362 var r1=Math.min(ranges[i][0],ranges[i][2]);
363 var r2=Math.max(ranges[i][0],ranges[i][2]);
364 var c1=Math.min(ranges[i][1],ranges[i][3]);
365 var c2=Math.max(ranges[i][1],ranges[i][3]);
366 for (var c=c1; c<=c2; c++) {
367 var col=this.columns[c];
368 for (var r=r1; r<=r2; r++) {
369 var cell=col.cell(r-1);
370 if (!cell.RicoDependencies) cell.RicoDependencies=new Rico.Formula.f_dependencies();
371 //alert('updateDependencies '+action+': '+formulaCell.RicoRow+','+formulaCell.RicoCol+' is dependent on '+cell.RicoRow+','+cell.RicoCol);
372 cell.RicoDependencies[action](formulaCell);
378 checkDependencies: function(cell) {
379 if (!cell.RicoDependencies) return;
380 var depcells=cell.RicoDependencies.items;
381 for (var i=0; i<depcells.length; i++) {
382 depcells[i].RicoValue=depcells[i].RicoFormula.eval();
383 this.formatCell(depcells[i]);
384 this.checkDependencies(depcells[i]);
388 showInputArea: function(clear,e) {
390 this.inputIdx=this.SelectIdxStart;
391 var col=this.columns[this.inputIdx.column];
392 this.inputIdx.cell=col.cell(this.inputIdx.row);
393 this.inputArea.style.top=(this.inputIdx.cell.offsetTop+col.dataCell.offsetTop)+'px';
394 this.inputArea.style.left=col.dataCell.offsetLeft+'px';
395 this.inputArea.style.display='';
396 this.inputArea.focus();
398 if (Prototype.Browser.WebKit) {
399 // Safari does not bubble the event to the inputArea, so force it
400 this.inputArea.value=String.fromCharCode(e.charCode);
401 this.inputArea.setSelectionRange(1,1);
403 } else this.inputArea.value='';
405 if (this.inputIdx.cell.RicoFormula)
406 this.inputArea.value=this.inputIdx.cell.RicoFormula.toEditString();
408 this.inputArea.value=this.inputIdx.cell.RicoValue || '';
412 closeInputArea: function(dr,dc,e) {
413 var newVal=this.inputArea.value;
414 var cell=this.inputIdx.cell;
415 if (this.options.checkEntry)
416 newVal=this.options.checkEntry(newVal,this.inputIdx.cell);
417 this.updateDependencies(cell,'remove');
418 cell.RicoFormula=null;
419 var d = new Date(newVal);
420 if (!this.options.noFormulas && newVal.charAt(0) == '=') {
422 cell.RicoFormula = new Rico.Formula(this,cell);
423 cell.RicoFormula.parse(newVal);
424 cell.RicoValue = cell.RicoFormula.eval();
425 this.updateDependencies(cell,'add');
426 } else if (newVal=='') {
427 cell.RicoValue = null;
428 } else if (newVal.match(/^(true|false)$/i)) {
429 cell.RicoValue = eval(newVal.toLowerCase());
430 } else if (newVal.match(/^-?\d+(.\d*)?$/)) {
432 cell.RicoValue = parseFloat(newVal);
433 } else if (!isNaN(d)) {
434 cell.RicoValue=d.getTime() / 86400 / 1000 + 25569 - d.getTimezoneOffset()/60/24;
435 cell.RicoFormat='locale';
437 cell.RicoValue=newVal;
439 this.formatCell(cell);
440 this.inputArea.blur();
441 this.inputArea.style.display='none';
442 this.checkDependencies(cell);
444 this.moveSelection(dr,dc,false,e);
447 inputKeydown: function(e) {
448 //window.status='inputKeydown keyCode='+e.keyCode;
452 this.closeInputArea(1,0,e);
456 this.closeInputArea(0,e.shiftKey ? -1 : 1,e);
460 this.inputArea.blur();
461 this.inputArea.style.display='none';
467 copyToClipbox: function() {
468 var s=this.getSelection();
471 for (var r=s.r1; r<=s.r2; r++) {
472 for (var c=s.c1; c<=s.c2; c++) {
473 if (c>s.c1) clipstr+="\t";
474 clipstr+=this.columns[c].cell(r).RicoValue;
478 this.clipBox.style.display='block';
479 this.clipBox.value=clipstr;
480 this.clipBox.select();
483 cutSelection: function() {
484 var clipArray=this.copySelection();
485 this.clearSelection();
489 copySelection: function() {
490 var s=this.getSelection();
493 for (var r=s.r1; r<=s.r2; r++) {
495 for (var c=s.c1; c<=s.c2; c++) {
497 var gridcell=this.columns[c].cell(r);
498 clipcell.value=gridcell.RicoValue;
499 clipcell.style=gridcell.style.cssText;
500 if (typeof gridcell.RicoFormat=='object')
501 clipcell.format=Object.extend({}, gridcell.RicoFormat || {});
503 clipcell.format=gridcell.RicoFormat;
504 if (gridcell.RicoFormula)
505 clipcell.formula=Object.extend({}, gridcell.RicoFormula);
506 cliprow[c-s.c1]=clipcell;
508 clipArray[r-s.r1]=cliprow;
513 pasteSelection: function(clipArray,pasteType) {
514 var s=this.getSelection();
515 if (!s || !clipArray) return;
516 pasteType=pasteType || 'all';
517 var clipclen=clipArray[0].length;
518 if (s.r1==s.r2 && s.c1==s.c2) {
519 s.r2=Math.min(s.r1+clipArray.length,this.columns[0].numRows())-1;
520 s.c2=Math.min(s.c1+clipclen,this.columns.length)-1;
522 for (var r=s.r1,clipr=0; r<=s.r2; r++) {
523 var arow=clipArray[clipr];
524 for (var c=s.c1,clipc=0; c<=s.c2; c++) {
525 var clipcell=arow[clipc];
526 var gridcell=this.columns[c].cell(r);
527 if (pasteType=='all') {
528 this.updateDependencies(gridcell,'remove');
529 gridcell.RicoFormula=null;
530 if (clipcell.formula) {
531 gridcell.RicoFormula=Object.extend({}, clipcell.formula);
532 gridcell.RicoFormula.cell=gridcell;
533 gridcell.RicoValue = gridcell.RicoFormula.eval();
534 this.updateDependencies(gridcell,'add');
536 gridcell.RicoValue=clipcell.value;
538 this.checkDependencies(gridcell);
540 if (pasteType=='all' || pasteType=='formats') {
541 gridcell.style.cssText=clipcell.style;
542 if (typeof clipcell.format=='object')
543 gridcell.RicoFormat=Object.extend({}, clipcell.format);
545 gridcell.RicoFormat=clipcell.format;
547 this.formatCell(gridcell);
548 clipc=(clipc+1) % clipclen;
550 clipr=(clipr+1) % clipArray.length;
554 formatSelection: function(newFormat) {
555 var s=this.getSelection();
557 for (var r=s.r1; r<=s.r2; r++) {
558 for (var c=s.c1; c<=s.c2; c++) {
559 var gridcell=this.cell(r,c);
560 gridcell.RicoFormat=newFormat;
561 this.formatCell(gridcell);
566 handleCtrlKey: function(e) {
570 window.status='copy';
571 this.clip=this.copySelection();
578 this.clip=this.cutSelection();
584 window.status='paste';
585 this.pasteSelection(this.clip);
591 this.toggleAttr('font-weight','normal','bold');
597 this.toggleAttr('font-style','normal','italic');
603 this.selectCellRC(0,1);
608 window.status=e.keyCode;
613 handleNormalKey: function(e) {
636 case 9: this.moveSelection(0,e.shiftKey ? -1 : 1,false,e); break;
638 case 13: this.moveSelection(1,0,false,e); break;
641 case 37: this.moveSelection(0,-1,e.shiftKey,e); break;
642 case 38: this.moveSelection(-1,0,e.shiftKey,e); break;
643 case 39: this.moveSelection(0,1,e.shiftKey,e); break;
644 case 40: this.moveSelection(1,0,e.shiftKey,e); break;
647 case 33: this.moveSelection(-Math.min(this.SelectIdxStart.row,10),0,e.shiftKey,e); break;
649 case 34: this.moveSelection(Math.min(this.pageSize-this.SelectIdxStart.row-1,10),0,e.shiftKey,e); break;
651 case 35: this.selectCellRC(this.SelectIdxStart.row,this.columns.length-1); Event.stop(e); break;
653 case 36: this.selectCellRC(this.SelectIdxStart.row,1); Event.stop(e); break;
655 case 113: this.showInputArea(false,e); break;
658 window.status=e.keyCode;
659 this.showInputArea(true,e); break;
664 gridKeydown: function(e) {
665 if (e.altKey) return;
666 var elem=Event.element(e);
667 if (elem.id=='inputArea') return true;
668 //window.status='gridKeydown keyCode='+e.keyCode;
670 this.handleCtrlKey(e);
672 this.handleNormalKey(e);
675 toggleAttr: function(attr,v1,v2) {
676 var v=this.getStyle(this.SelectIdxStart.row,this.SelectIdxStart.column,attr);
678 this.updateSelectionStyle(attr,v);
681 getStyle: function(row,col,attr) {
682 var csstxt=this.columns[col].cell(row).style.cssText;
684 if (csstxt.charAt(csstxt.length-1)!=';') csstxt+=';'; // opera
686 var re=new RegExp("[ ;]"+attr+"\\s*:\\s*([^ ;]*)\\s*;","i");
693 updateStyleText: function(csstxt,attr,value) {
694 var newval=attr+':'+value+';';
695 if (!csstxt) return newval;
696 csstxt=' '+csstxt.strip();
697 if (csstxt.charAt(csstxt.length-1)!=';') csstxt+=';'; // opera
698 var re=new RegExp("([ ;])"+attr+"\\s*:\\s*([^ ;]*)\\s*;","i");
699 // safari must process the regexp twice, everyone else can run it once
701 return Prototype.Browser.WebKit ? csstxt.replace(re,"$1"+newval) : RegExp.leftContext+RegExp.$1+newval+RegExp.rightContext;
703 return csstxt+newval;
706 updateSelectionStyle: function(attr,newVal) {
707 var s=this.getSelection();
709 for (var c=s.c1; c<=s.c2; c++) {
710 var col=this.columns[c];
711 for (var r=s.r1; r<=s.r2; r++)
712 col.cell(r).style.cssText=this.updateStyleText(col.cell(r).style.cssText,attr,newVal);
716 showHelp: function(e) {
718 if (this.helpWindow) {
719 this.helpWindow.openPopup();
721 var msg="<p><strong>Keys</strong>";
722 msg+="<p>The following keyboard actions are supported:";
724 msg+="<li>Arrows keys";
725 msg+="<li>Home, End, PgUp, PgDn";
726 msg+="<li>F2 - edit cell";
727 msg+="<li>Ctrl-C = copy";
728 msg+="<li>Ctrl-X = cut";
729 msg+="<li>Ctrl-V = paste";
732 msg+="<p><strong>Formulas</strong>";
733 msg+="<p>Formulas starting with '=' are supported.";
735 msg+="<li>Formulas may contain parentheses and the following operators:";
736 msg+="<br>+ - * / & % = > < <= >= <>";
737 msg+="<li>'+' follows javascript rules regarding type conversion (which are slightly different from Excel)";
738 msg+="<li>Formulas may refer to cells using 'A1' notation (and 'A1:B2' for ranges).";
739 msg+="<li>The following functions are supported in formulas:";
741 for (var funcname in Rico.Formula.prototype) {
742 if (funcname.substring(0,5)=='eval_') funclist.push(funcname.substring(5));
745 msg+="<br>"+funclist.join(', ');
748 msg+="<p><strong>Copy/Paste</strong>";
749 msg+="<p>You can only copy/paste data within this spreadsheet - not from/to external applications.";
751 msg+="<p><strong>Notes</strong>";
752 msg+="<p>Formula parsing based on code originally published by E. W. Bachtal at <a href='http://ewbi.blogs.com/develops/'>http://ewbi.blogs.com/develops/</a>";
753 msg+="<p>Future functionality may include copy/paste from external applications, load/save, date formatting, and support for additional functions.";
754 this.helpWindow=new Rico.Popup({canDragFunc:true});
755 this.helpWindow.createWindow('Rico Spreadsheet',msg,'300px','300px','ricoLG_help');
756 this.helpWindow.openPopup(20,20);
760 createFormatNumber: function() {
761 var div = this.createDiv("fmtnum",this.outerDiv);
762 div.innerHTML="<table border='0'>"+
763 "<tr><td title='Number of places to the right of the decimal point'>Decimal Places</td>"+
764 "<td><select id='decPlaces'>"+
765 "<option value='0'>0</option>"+
766 "<option value='1'>1</option>"+
767 "<option value='2'>2</option>"+
768 "<option value='3'>3</option>"+
769 "<option value='4'>4</option>"+
770 "<option value='5'>5</option>"+
771 "<option value='6'>6</option>"+
772 "<option value='7'>7</option>"+
773 "<option value='8'>8</option>"+
774 "<option value='9'>9</option>"+
777 "<tr><td>Negative Values</td>"+
778 "<td><select id='negSign'>"+
779 "<option value='L'>Leading -</option>"+
780 "<option value='T'>Trailing -</option>"+
781 "<option value='P'>()</option>"+
784 "<tr><td title='Typically a currency symbol ($ or &euro; or &yen;)'>Prefix</td>"+
785 "<td><input type='text' size='10' id='prefix'></td></tr>"+
786 "<tr><td title='If displaying percentages then set this to %'>Suffix</td>"+
787 "<td><input type='text' size='10' id='suffix'></td></tr>"+
788 "<tr><td title='If displaying percentages then set this to 100'>Multiplier</td>"+
789 "<td><input type='text' size='10' id='multiplier'></td></tr>"+
790 "<tr><td>Decimal Point</td>"+
791 "<td><select id='decPoint'>"+
792 "<option value='.'>.</option>"+
793 "<option value=','>,</option>"+
796 "<tr><td title='Thousands grouping symbol'>Thousands Sep.</td>"+
797 "<td><select id='thouSep'>"+
798 "<option value=','>,</option>"+
799 "<option value='.'>.</option>"+
800 "<option value=' '>space</option>"+
801 "<option value=''>none</option>"+
805 "<center><button id='NumberFormat_OK'>OK</button> <button id='NumberFormat_Cancel'>Cancel</button></center>";
806 this.fmtNumberObj=new Rico.Popup({ignoreClicks:true, canDragFunc:function(elem){return elem.tagName.toUpperCase()!='INPUT';} }, div);
807 Event.observe(div, "keydown", this.checkKey.bindAsEventListener(this));
808 Event.observe('NumberFormat_OK', "click", this.setNumberFormat.bindAsEventListener(this));
809 Event.observe('NumberFormat_Cancel', "click", this.cancelNumberFormat.bindAsEventListener(this));
810 this.formatNumberDiv=div;
813 openFormatNumber: function(e) {
814 Event.stop(e || event);
815 this.menu.cancelmenu();
816 var cell=this.cell(this.SelectIdxStart.row,this.SelectIdxStart.column);
817 if (cell && cell.RicoFormat) {
818 for (var p in cell.RicoFormat) {
820 if (!elem || !elem.tagName) continue;
821 var v=cell.RicoFormat[p].toString();
822 switch (elem.tagName.toLowerCase()) {
827 var opts=elem.options;
828 for (var i=0; i<opts.length; i++) {
829 if (opts[i].value==v) {
830 elem.selectedIndex=i;
838 this.openPopup(cell,this.fmtNumberObj);
841 setNumberFormat: function() {
842 var selects=$A(this.fmtNumberObj.divPopup.getElementsByTagName('select'));
843 var inputs=$A(this.fmtNumberObj.divPopup.getElementsByTagName('input'));
844 var newFormat={type:'number'};
845 selects.each(function(e) { newFormat[e.id]=$F(e.id); });
846 inputs.each(function(e) { newFormat[e.id]=$F(e.id); });
847 if (newFormat.multiplier.match(/^\d+$/)) newFormat.multiplier=parseInt(newFormat.multiplier,10);
848 if (newFormat.decPlaces.match(/^\d+$/)) newFormat.decPlaces=parseInt(newFormat.decPlaces,10);
849 this.formatSelection(newFormat);
850 this.fmtNumberObj.closePopup();
853 cancelNumberFormat: function(e) {
854 this.fmtNumberObj.closePopup();
857 formatDate: function() {
860 formatText: function() {
863 formatBoolean: function() {
866 // prevent keystrokes (other than escape) from bubbling to sheet/document
867 checkKey: function(e) {
868 if (RicoUtil.eventKey(e)==27) return;
869 if ( e.stopPropagation != undefined )
871 else if ( e.cancelBubble != undefined )
872 e.cancelBubble = true;
878 Rico.Formula = Class.create();
880 Rico.Formula.TOK_TYPE_NOOP = "noop";
881 Rico.Formula.TOK_TYPE_OPERAND = "operand";
882 Rico.Formula.TOK_TYPE_FUNCTION = "function";
883 Rico.Formula.TOK_TYPE_SUBEXPR = "subexpression";
884 Rico.Formula.TOK_TYPE_ARGUMENT = "argument";
885 Rico.Formula.TOK_TYPE_OP_PRE = "operator-prefix";
886 Rico.Formula.TOK_TYPE_OP_IN = "operator-infix";
887 Rico.Formula.TOK_TYPE_OP_POST = "operator-postfix";
888 Rico.Formula.TOK_TYPE_WSPACE = "white-space";
889 Rico.Formula.TOK_TYPE_UNKNOWN = "unknown";
891 Rico.Formula.TOK_SUBTYPE_START = "start";
892 Rico.Formula.TOK_SUBTYPE_STOP = "stop";
894 Rico.Formula.TOK_SUBTYPE_TEXT = "text";
895 Rico.Formula.TOK_SUBTYPE_NUMBER = "number";
896 Rico.Formula.TOK_SUBTYPE_LOGICAL = "logical";
897 Rico.Formula.TOK_SUBTYPE_ERROR = "error";
898 Rico.Formula.TOK_SUBTYPE_RANGE = "range";
900 Rico.Formula.TOK_SUBTYPE_MATH = "math";
901 Rico.Formula.TOK_SUBTYPE_CONCAT = "concatenate";
902 Rico.Formula.TOK_SUBTYPE_INTERSECT = "intersect";
903 Rico.Formula.TOK_SUBTYPE_UNION = "union";
905 Rico.Formula.prototype = {
907 initialize: function(grid,cell) {
912 // 'A' -> 1, 'AA' -> 27
913 colLetter2Num: function(colstr) {
914 colstr=colstr.toUpperCase();
915 switch (colstr.length) {
916 case 1: return colstr.charCodeAt(0)-64;
917 case 2: return (colstr.charCodeAt(0)-64) * 26 + colstr.charCodeAt(1)-64;
922 // 1 -> 'A', 27 -> 'AA'
923 colNum2Letter: function(colnum) {
924 if (colnum <= 26) return String.fromCharCode(64+colnum);
926 return String.fromCharCode(64+Math.floor(colnum / 26),65+(colnum % 26));
933 var indent = function() {
935 for (var i = 0; i < indentCount; i++) {
936 s += " |";
941 var tokensHtml = "<table cellspacing='0'>";
942 tokensHtml += "<tr>";
943 tokensHtml += "<td class='token' style='font-weight: bold; width: 50px'>index</td>";
944 tokensHtml += "<td class='token' style='font-weight: bold; width: 125px'>type</td>";
945 tokensHtml += "<td class='token' style='font-weight: bold; width: 125px'>subtype</td>";
946 tokensHtml += "<td class='token' style='font-weight: bold; width: 150px'>token</td>";
947 tokensHtml += "<td class='token' style='font-weight: bold; width: 300px'>token tree</td></tr>";
950 while (this.tokens.moveNext()) {
952 var token = this.tokens.current();
954 if (token.subtype == Rico.Formula.TOK_SUBTYPE_STOP)
955 indentCount -= ((indentCount > 0) ? 1 : 0);
957 tokensHtml += "<tr>";
959 tokensHtml += "<td class='token'>" + (this.tokens.index + 1) + "</td>";
960 tokensHtml += "<td class='token'>" + token.type + "</td>";
961 tokensHtml += "<td class='token'>" + ((token.subtype.length == 0) ? " " : token.subtype) + "</td>";
962 tokensHtml += "<td class='token'>" + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "</td>";
963 tokensHtml += "<td class='token'>" + indent() + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "</td>";
965 tokensHtml += "</tr>";
967 if (token.subtype == Rico.Formula.TOK_SUBTYPE_START) indentCount++;
969 tokensHtml += "</table>";
974 parseCellRef: function(refString) {
975 if (!refString) return null;
976 if (!refString.match(/^(\$?)([a-z]*)(\$?)(\d*)$/i)) return null;
977 var abscol=(RegExp.$1=='$');
978 var absrow=(RegExp.$3=='$');
981 c=this.colLetter2Num(RegExp.$2);
982 if (c<0 || c>=this.grid.columns.length) return null;
983 if (!abscol) c-=this.cell.RicoCol;
986 r=parseInt(RegExp.$4,10);
987 if (!absrow) r-=this.cell.RicoRow;
989 //alert('parseCellRef: '+refString+"\n"+'r='+r+' c='+c+' absrow='+absrow+' abscol='+abscol);
990 return {row:r, col:c, absRow:absrow, absCol:abscol};
994 resolveCellRef: function(cellRef) {
997 if (!cellRef.absRow) r+=this.cell.RicoRow;
998 if (!cellRef.absCol) c+=this.cell.RicoCol;
999 return {row:r, col:c};
1003 resolveRange: function(token) {
1004 if (!token.rangeStart) return null;
1005 var a1=this.resolveCellRef(token.rangeStart);
1006 var a2=this.resolveCellRef(token.rangeEnd);
1007 //alert('resolveRange: '+a1.row+','+a1.col+' '+a2.row+','+a2.col);
1008 var r1=Math.min(a1.row,a2.row);
1009 var r2=Math.max(a1.row,a2.row);
1010 var c1=Math.min(a1.col,a2.col) || 0;
1011 var c2=Math.max(a1.col,a2.col) || this.grid.columns.length-1;
1012 return [r1,c1,r2,c2];
1016 range2evalstr: function(token) {
1017 var rng=this.resolveRange(token);
1018 return rng ? rng.join(',') : '';
1022 cellref2str: function(cellRef) {
1023 var ref=this.resolveCellRef(cellRef);
1024 var c=this.colNum2Letter(ref.col);
1025 if (cellRef.absCol) c='$'+c;
1026 var r=ref.row.toString();
1027 if (cellRef.absRow) r='$'+r;
1032 range2str: function(token) {
1033 var s1=this.cellref2str(token.rangeStart);
1034 var s2=this.cellref2str(token.rangeEnd);
1035 return (s1==s2) ? s1 : s1+':'+s2;
1039 GetRange: function(r1,c1,r2,c2) {
1040 if (typeof r1=='undefined' || typeof c1=='undefined') return NaN;
1041 if (r1==r2 && c1==c2) return this.grid.columns[c1].cell(r1-1).RicoValue;
1043 for (var r=r1; r<=r2; r++) {
1045 for (var c=c1; c<=c2; c++)
1046 newRow.push(this.grid.columns[c].cell(r-1).RicoValue);
1047 result.push(newRow);
1053 getRanges: function() {
1055 this.tokens.reset();
1056 while (this.tokens.moveNext()) {
1057 var token = this.tokens.current();
1058 if (token.subtype=='range') result.push(this.resolveRange(token));
1064 eval_sum: function() {
1066 for (var i=0; i<arguments.length; i++) {
1067 var arg=arguments[i];
1068 if (arg==null) continue;
1069 switch (typeof arg) {
1074 for (var r=0; r<arg.length; r++) {
1075 for (var c=0; c<arg[r].length; c++) {
1076 if (typeof arg[r][c]=='number') result+=arg[r][c];
1086 eval_count: function() {
1088 for (var i=0; i<arguments.length; i++) {
1089 var arg=arguments[i];
1090 if (arg==null) continue;
1091 switch (typeof arg) {
1093 for (var r=0; r<arg.length; r++) {
1094 for (var c=0; c<arg[r].length; c++) {
1095 if (arg[r][c] || typeof arg[r][c]=='number') result++;
1100 if (arg || typeof arg=='number') result++;
1108 eval_t: function(arg) {
1109 return (typeof arg=='string') ? arg : '';
1113 eval_trim: function(arg) {
1114 arg=this.argString(arg);
1119 eval_lower: function(arg) {
1120 arg=this.argString(arg);
1121 return arg.toLowerCase();
1125 eval_upper: function(arg) {
1126 arg=this.argString(arg);
1127 return arg.toUpperCase();
1131 eval_len: function(arg) {
1132 arg=this.argString(arg);
1137 eval_value: function(arg) {
1138 arg=this.argString(arg);
1139 return parseFloat(arg);
1143 eval_left: function(arg,numchars) {
1144 arg=this.argString(arg);
1145 if (typeof numchars!='number') numchars=1;
1146 if (numchars<0) return NaN;
1147 return arg.slice(0,numchars);
1151 eval_right: function(arg,numchars) {
1152 arg=this.argString(arg);
1153 if (typeof numchars!='number') numchars=1;
1154 if (numchars<0) return NaN;
1155 if (numchars==0) return '';
1156 return arg.slice(-numchars);
1160 eval_mid: function(arg,start,numchars) {
1161 arg=this.argString(arg);
1162 if (typeof start!='number' || start<1) return NaN;
1163 if (typeof numchars!='number' || numchars<0) return NaN;
1164 return arg.substr(start-1,numchars);
1168 eval_if: function(logical_test, value_true, value_false) {
1169 var v=this.argBool(logical_test);
1170 if (v==null) return NaN;
1171 return v ? value_true : value_false;
1175 eval_not: function(arg) {
1176 var v=this.argBool(arg);
1177 return (v==null) ? NaN : !v;
1181 eval_and: function() {
1182 var args = $A(arguments);
1183 args.unshift(function(a,b) { return a&&b; });
1184 return this.or_and.apply(this, args);
1188 eval_or: function() {
1189 var args = $A(arguments);
1190 args.unshift(function(a,b) { return a||b; });
1191 return this.or_and.apply(this, args);
1195 or_and: function() {
1196 var i,r,c,v,arg,result;
1197 var func=arguments[0];
1198 for (i=1; i<arguments.length; i++) {
1200 if (arg==null) continue;
1201 switch (typeof arg) {
1203 for (r=0; r<arg.length; r++) {
1204 for (c=0; c<arg[r].length; c++) {
1205 v=this.argBool(arg[r][c]);
1206 if (v!=null) result=(typeof result=='undefined') ? v : func(result,v);
1211 v=this.argBool(arg);
1212 if (v!=null) result=(typeof result=='undefined') ? v : func(result,v);
1216 return (typeof result=='undefined') ? NaN : result;
1220 eval_abs: function(arg) { return Math.abs(this.argNumber(arg)); },
1221 eval_acos: function(arg) { return Math.acos(this.argNumber(arg)); },
1222 eval_asin: function(arg) { return Math.asin(this.argNumber(arg)); },
1223 eval_atan: function(arg) { return Math.atan(this.argNumber(arg)); },
1224 eval_atan2: function(argx,argy) { return Math.atan2(this.argNumber(argy),this.argNumber(argx)); },
1225 eval_ceiling: function(arg) { return Math.ceil(this.argNumber(arg)); },
1226 eval_cos: function(arg) { return Math.cos(this.argNumber(arg)); },
1227 eval_exp: function(arg) { return Math.exp(this.argNumber(arg)); },
1228 eval_floor: function(arg) { return Math.floor(this.argNumber(arg)); },
1229 eval_ln: function(arg) { return Math.log(this.argNumber(arg)); },
1230 eval_mod: function(num,divisor) { return this.argNumber(num) % this.argNumber(divisor); },
1231 eval_pi: function() { return Math.PI; },
1232 eval_power: function(argx,argy) { return Math.pow(this.argNumber(argx),this.argNumber(argy)); },
1233 eval_rand: function() { return Math.random(); },
1234 eval_round: function(arg) { return Math.round(this.argNumber(arg)); },
1235 eval_sin: function(arg) { return Math.sin(this.argNumber(arg)); },
1236 eval_sqrt: function(arg) { return Math.sqrt(this.argNumber(arg)); },
1237 eval_tan: function(arg) { return Math.tan(this.argNumber(arg)); },
1240 argNumber: function(arg) {
1241 switch (typeof arg) {
1242 case 'boolean': return arg;
1243 case 'number': return arg;
1244 case 'string': return parseFloat(arg);
1245 default: return null;
1250 argBool: function(arg) {
1251 switch (typeof arg) {
1252 case 'boolean': return arg;
1253 case 'number': return arg!=0;
1254 default: return null;
1259 argString: function(arg) {
1260 switch (typeof arg) {
1261 case 'string': return arg;
1263 case 'number': return arg.toString();
1271 this.tokens.reset();
1272 while (this.tokens.moveNext()) {
1273 var token = this.tokens.current();
1274 switch (token.type) {
1276 if (token.subtype=='start') {
1277 var funcname='eval_'+token.value.toLowerCase();
1278 if (typeof this[funcname]!='function') {
1279 alert('Unknown function: '+token.value);
1282 evalstr+='this.'+funcname+'(';
1286 case 'subexpression':
1287 if (token.subtype=='start')
1292 case 'operator-infix':
1293 if (token.value=='&')
1295 else if (token.value=='=')
1297 else if (token.value=='<>')
1300 evalstr+=token.value;
1302 case 'operator-postfix':
1303 if (token.value=='%')
1306 evalstr+=token.value;
1309 if (token.subtype=='range')
1310 evalstr+='this.GetRange('+this.range2evalstr(token)+')';
1311 else if (token.subtype=='text')
1312 evalstr+='"'+token.value+'"';
1314 evalstr+=token.value;
1317 evalstr+=token.value;
1321 this.lastEval=evalstr;
1322 //window.status=evalstr;
1324 var result=eval(evalstr);
1326 } catch(e) { alert(e.message); return '#ERROR'; }
1330 toEditString: function() {
1332 this.tokens.reset();
1333 while (this.tokens.moveNext()) {
1334 var token = this.tokens.current();
1335 switch (token.type) {
1337 if (token.subtype=='start')
1342 case 'subexpression':
1343 if (token.subtype=='start')
1349 if (token.subtype=='range')
1350 s+=this.range2str(token);
1351 else if (token.subtype=='text')
1352 s+='"'+token.value+'"';
1365 // Excel formula parser
1366 // from http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
1367 parse: function(formula) {
1368 var tokens = new Rico.Formula.f_tokens();
1369 var tokenStack = new Rico.Formula.f_tokenStack();
1373 var currentChar = function() { return formula.substr(offset, 1); };
1374 var doubleChar = function() { return formula.substr(offset, 2); };
1375 var nextChar = function() { return formula.substr(offset + 1, 1); };
1376 var EOF = function() { return (offset >= formula.length); };
1380 var inString = false;
1382 var inRange = false;
1383 var inError = false;
1385 while (formula.length > 0) {
1386 if (formula.substr(0, 1) == " ")
1387 formula = formula.substr(1);
1389 if (formula.substr(0, 1) == "=")
1390 formula = formula.substr(1);
1397 // state-dependent character evaluation (order is important)
1399 // double-quoted strings
1400 // embeds are doubled
1404 if (currentChar() == "\"") {
1405 if (nextChar() == "\"") {
1410 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND, Rico.Formula.TOK_SUBTYPE_TEXT);
1414 token += currentChar();
1420 // single-quoted strings (links)
1421 // embeds are double
1422 // end does not mark a token
1425 if (currentChar() == "'") {
1426 if (nextChar() == "'") {
1433 token += currentChar();
1439 // bracked strings (range offset or linked workbook name)
1440 // no embeds (changed to "()" by Excel)
1441 // end does not mark a token
1444 if (currentChar() == "]") {
1447 token += currentChar();
1453 // end marks a token, determined from absolute list of values
1456 token += currentChar();
1458 if ((",#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,").indexOf("," + token + ",") != -1) {
1460 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND, Rico.Formula.TOK_SUBTYPE_ERROR);
1466 // independent character evaulation (order not important)
1468 // establish state-dependent character evaluations
1470 if (currentChar() == "\"") {
1471 if (token.length > 0) {
1473 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1481 if (currentChar() == "'") {
1482 if (token.length > 0) {
1484 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1492 if (currentChar() == "[") {
1494 token += currentChar();
1499 if (currentChar() == "#") {
1500 if (token.length > 0) {
1502 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1506 token += currentChar();
1511 // mark start and end of arrays and array rows
1513 if (currentChar() == "{") {
1514 if (token.length > 0) {
1516 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1519 tokenStack.push(tokens.add("ARRAY", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1520 tokenStack.push(tokens.add("ARRAYROW", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1525 if (currentChar() == ";") {
1526 if (token.length > 0) {
1527 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1530 tokens.addRef(tokenStack.pop());
1531 tokens.add(",", Rico.Formula.TOK_TYPE_ARGUMENT);
1532 tokenStack.push(tokens.add("ARRAYROW", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1537 if (currentChar() == "}") {
1538 if (token.length > 0) {
1539 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1542 tokens.addRef(tokenStack.pop());
1543 tokens.addRef(tokenStack.pop());
1550 if (currentChar() == " ") {
1551 if (token.length > 0) {
1552 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1555 tokens.add("", Rico.Formula.TOK_TYPE_WSPACE);
1557 while ((currentChar() == " ") && (!EOF())) {
1563 // multi-character comparators
1565 if ((",>=,<=,<>,").indexOf("," + doubleChar() + ",") != -1) {
1566 if (token.length > 0) {
1567 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1570 tokens.add(doubleChar(), Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_LOGICAL);
1575 // standard infix operators
1577 if (("+-*/^&=><").indexOf(currentChar()) != -1) {
1578 if (token.length > 0) {
1579 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1582 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_IN);
1587 // standard postfix operators
1589 if (("%").indexOf(currentChar()) != -1) {
1590 if (token.length > 0) {
1591 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1594 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_POST);
1599 // start subexpression or function
1601 if (currentChar() == "(") {
1602 if (token.length > 0) {
1603 tokenStack.push(tokens.add(token, Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1606 tokenStack.push(tokens.add("", Rico.Formula.TOK_TYPE_SUBEXPR, Rico.Formula.TOK_SUBTYPE_START));
1612 // function, subexpression, array parameters
1614 if (currentChar() == ",") {
1615 if (token.length > 0) {
1616 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1619 if (!(tokenStack.type() == Rico.Formula.TOK_TYPE_FUNCTION)) {
1620 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_UNION);
1622 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_ARGUMENT);
1628 // stop subexpression
1630 if (currentChar() == ")") {
1631 if (token.length > 0) {
1632 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1635 tokens.addRef(tokenStack.pop());
1640 // token accumulation
1642 token += currentChar();
1647 // dump remaining accumulation
1649 if (token.length > 0) tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1651 // move all tokens to a new collection, excluding all unnecessary white-space tokens
1653 var tokens2 = new Rico.Formula.f_tokens();
1655 while (tokens.moveNext()) {
1657 token = tokens.current();
1659 if (token.type == Rico.Formula.TOK_TYPE_WSPACE) {
1660 if ((tokens.BOF()) || (tokens.EOF())) {}
1662 ((tokens.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1663 ((tokens.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1664 (tokens.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1668 ((tokens.next().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens.next().subtype == Rico.Formula.TOK_SUBTYPE_START)) ||
1669 ((tokens.next().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens.next().subtype == Rico.Formula.TOK_SUBTYPE_START)) ||
1670 (tokens.next().type == Rico.Formula.TOK_TYPE_OPERAND)
1674 tokens2.add(token.value, Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_INTERSECT);
1678 tokens2.addRef(token);
1682 // switch infix "-" operator to prefix when appropriate, switch infix "+" operator to noop when appropriate, identify operand
1683 // and infix-operator subtypes, pull "@" from in front of function names
1685 while (tokens2.moveNext()) {
1687 token = tokens2.current();
1689 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.value == "-")) {
1691 token.type = Rico.Formula.TOK_TYPE_OP_PRE;
1693 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1694 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1695 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OP_POST) ||
1696 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1698 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1700 token.type = Rico.Formula.TOK_TYPE_OP_PRE;
1704 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.value == "+")) {
1706 token.type = Rico.Formula.TOK_TYPE_NOOP;
1708 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1709 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1710 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OP_POST) ||
1711 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1713 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1715 token.type = Rico.Formula.TOK_TYPE_NOOP;
1719 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.subtype.length == 0)) {
1720 if (("<>=").indexOf(token.value.substr(0, 1)) != -1)
1721 token.subtype = Rico.Formula.TOK_SUBTYPE_LOGICAL;
1722 else if (token.value == "&")
1723 token.subtype = Rico.Formula.TOK_SUBTYPE_CONCAT;
1725 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1729 if ((token.type == Rico.Formula.TOK_TYPE_OPERAND) && (token.subtype.length == 0)) {
1730 if (isNaN(parseFloat(token.value))) {
1731 if ((token.value == 'TRUE') || (token.value == 'FALSE')) {
1732 token.subtype = Rico.Formula.TOK_SUBTYPE_LOGICAL;
1734 token.subtype = Rico.Formula.TOK_SUBTYPE_RANGE;
1735 var a=token.value.split(':');
1736 token.rangeStart=this.parseCellRef(a[0]);
1737 token.rangeEnd=a.length>1 ? this.parseCellRef(a[1]) : token.rangeStart;
1740 token.subtype = Rico.Formula.TOK_SUBTYPE_NUMBER;
1745 if (token.type == Rico.Formula.TOK_TYPE_FUNCTION) {
1746 if (token.value.substr(0, 1) == "@")
1747 token.value = token.value.substr(1);
1755 // move all tokens to a new collection, excluding all noops
1757 this.tokens = new Rico.Formula.f_tokens();
1759 while (tokens2.moveNext()) {
1760 if (tokens2.current().type != Rico.Formula.TOK_TYPE_NOOP)
1761 this.tokens.addRef(tokens2.current());
1768 Rico.Formula.f_token = Class.create();
1769 Rico.Formula.f_token.prototype = {
1770 initialize: function(value, type, subtype) {
1773 this.subtype = subtype;
1778 Rico.Formula.f_tokens = Class.create();
1779 Rico.Formula.f_tokens.prototype = {
1780 initialize: function() {
1781 this.items = new Array();
1785 addRef: function(token) {
1786 this.items.push(token);
1789 add: function(value, type, subtype) {
1790 if (!subtype) subtype = "";
1791 var token = new Rico.Formula.f_token(value, type, subtype);
1801 return (this.index <= 0);
1805 return (this.index >= (this.items.length - 1));
1808 moveNext: function() {
1809 if (this.EOF()) return false; this.index++; return true;
1812 current: function() {
1813 if (this.index == -1) return null; return (this.items[this.index]);
1817 if (this.EOF()) return null; return (this.items[this.index + 1]);
1820 previous: function() {
1821 if (this.index < 1) return null; return (this.items[this.index - 1]);
1826 Rico.Formula.f_tokenStack = Class.create();
1827 Rico.Formula.f_tokenStack.prototype = {
1828 initialize: function() {
1829 this.items = new Array();
1832 push: function(token) {
1833 this.items.push(token);
1837 var token = this.items.pop();
1838 return (new Rico.Formula.f_token("", token.type, Rico.Formula.TOK_SUBTYPE_STOP));
1842 return ((this.items.length > 0) ? this.items[this.items.length - 1] : null);
1846 return ((this.token()) ? this.token().value : "");
1850 return ((this.token()) ? this.token().type : "");
1853 subtype: function() {
1854 return ((this.token()) ? this.token().subtype : "");
1859 Rico.Formula.f_dependencies = Class.create();
1860 Rico.Formula.f_dependencies.prototype = {
1861 initialize: function() {
1865 add: function(cell) {
1866 if (!this.items.include(cell)) this.items.push(cell);
1869 remove: function(cell) {
1870 this.items=this.items.select(function(item) { return (item != cell); });
1873 find: function(cell) {
1874 return this.items.detect(function(item) { return (item==cell); });
1883 Object.extend(Rico.Menu.prototype, {
1885 showSheetMenu: function(e,hideFunc) {
1886 var elem=this.showSimpleMenu(e,hideFunc);
1887 if (!this.grid) return;
1888 var newIdx=this.grid.cellIndex(elem);
1889 if (!this.grid.isSelected(newIdx.row,newIdx.column))
1890 this.grid.selectCellRC(newIdx.row,newIdx.column,false);
1896 Rico.includeLoaded('ricoSheet.js');