1 Object.extend(Rico.SimpleGrid.prototype, {
3 initSheet: function() {
5 for (var i=0; i<4; i++) {
6 this.highlightDiv[i] = this.createDiv("highlight",this.scrollDiv);
7 this.highlightDiv[i].style.display="none";
8 this.highlightDiv[i].id+=i;
9 this.highlightDiv[i].style[i % 2==0 ? 'height' : 'width']="0px";
11 for (var c=1; c<this.columns.length; c++) {
12 var col=this.columns[c];
13 for (var r=0; r<col.numRows(); r++) {
21 if (!this.menu.grid) this.registerScrollMenu(this.menu);
22 this.menu.showmenu=this.menu.showSheetMenu;
24 this.inputArea=RicoUtil.createFormField(this.scrollDiv,'textarea',null,'inputArea');
25 this.inputArea.style.position='absolute';
26 this.inputArea.style.display='none';
27 this.inputArea.style.zIndex=2;
28 this.inputArea.cols=30;
29 this.inputArea.rows=4;
30 this.inputArea.blur();
31 this.clipBox=RicoUtil.createFormField(this.innerDiv,'textarea',null,'clipBox');
32 this.clipBox.style.position='absolute';
33 this.clipBox.style.display='none';
36 this.clipBox.style.top='0px';
37 this.clipBox.style.left='0px';
38 this.selectCellRC(0,1);
39 this.mouseOverHandler = this.selectMouseOver.bindAsEventListener(this);
40 this.mouseUpHandler = this.selectMouseUp.bindAsEventListener(this);
41 Event.observe(this.inputArea,'keydown',this.inputKeydown.bindAsEventListener(this),false);
42 Event.observe(Prototype.Browser.IE ? document.body : window,'keydown',this.gridKeydown.bindAsEventListener(this),false);
43 Event.observe(this.tbody[1],"mousedown", this.selectMouseDown.bindAsEventListener(this), false);
45 // disable drag & select events in IE
46 this.outerDiv.ondrag = this.disableEvent;
47 this.outerDiv.onselectstart = this.disableEvent;
48 this.tbody[1].ondrag = this.disableEvent;
49 this.tbody[1].onselectstart = this.disableEvent;
52 disableEvent: function(e) {
58 cellIndex: function(cell) {
59 var a=cell.id.split(/_/);
61 var r=parseInt(a[l-2]);
62 var c=parseInt(a[l-1]);
63 return {row:r, column:c, tabIdx:this.columns[c].tabIdx, cell:cell};
66 AdjustSelection: function(cell) {
67 var newIdx=this.cellIndex(cell);
68 if (this.SelectIdxStart.tabIdx != newIdx.tabIdx) return;
70 this.SelectIdxEnd=newIdx;
74 selectMouseDown: function(e) {
75 if (this.highlightEnabled==false) return true;
77 var cell=Event.element(e);
79 if (!Event.isLeftClick(e)) return;
80 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
82 var newIdx=this.cellIndex(cell);
84 if (!this.SelectIdxStart) return;
85 this.selectCellRC(newIdx.row,newIdx.column,true);
87 this.selectCellRC(newIdx.row,newIdx.column,false);
92 pluginSelect: function() {
93 if (this.selectPluggedIn) return;
94 var tBody=this.tbody[this.SelectIdxStart.tabIdx];
95 Event.observe(tBody,"mouseover", this.mouseOverHandler, false);
96 Event.observe(this.outerDiv,"mouseup", this.mouseUpHandler, false);
97 this.selectPluggedIn=true;
100 unplugSelect: function() {
101 var tBody=this.tbody[this.SelectIdxStart.tabIdx];
102 Event.stopObserving(tBody,"mouseover", this.mouseOverHandler , false);
103 Event.stopObserving(this.outerDiv,"mouseup", this.mouseUpHandler , false);
104 this.selectPluggedIn=false;
107 selectMouseUp: function(e) {
109 var cell=Event.element(e);
110 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
112 this.AdjustSelection(cell);
115 selectMouseOver: function(e) {
116 var cell=Event.element(e);
117 cell=RicoUtil.getParentByTagName(cell,'div','ricoLG_cell');
119 this.AdjustSelection(cell);
123 getSelection: function() {
124 if (!this.SelectIdxStart || !this.SelectIdxEnd) return false;
125 var r1=Math.min(this.SelectIdxEnd.row,this.SelectIdxStart.row);
126 var r2=Math.max(this.SelectIdxEnd.row,this.SelectIdxStart.row);
127 var c1=Math.min(this.SelectIdxEnd.column,this.SelectIdxStart.column);
128 var c2=Math.max(this.SelectIdxEnd.column,this.SelectIdxStart.column);
129 return {r1:r1,c1:c1,r2:r2,c2:c2};
132 updateSelectOutline: function() {
133 var s=this.getSelection();
134 if (!s || s.r1 > s.r2) {
135 this.HideSelection();
138 var top1=this.columns[s.c1].cell(s.r1).offsetTop;
139 var cell2=this.columns[s.c1].cell(s.r2);
140 var bottom2=cell2.offsetTop+cell2.offsetHeight;
141 var left1=this.columns[s.c1].dataCell.offsetLeft;
142 var left2=this.columns[s.c2].dataCell.offsetLeft;
143 var right2=left2+this.columns[s.c2].dataCell.offsetWidth;
144 //window.status='updateSelectOutline: '+s.r1+' '+s.r2+' top='+top1+' bot='+bottom2;
145 this.highlightDiv[0].style.top=this.highlightDiv[3].style.top=this.highlightDiv[1].style.top=(top1-3) + 'px';
146 this.highlightDiv[2].style.top=(bottom2-2)+'px';
147 this.highlightDiv[3].style.left=(left1-2)+'px';
148 this.highlightDiv[0].style.left=this.highlightDiv[2].style.left=(left1-1)+'px';
149 this.highlightDiv[1].style.left=(right2-1)+'px';
150 this.highlightDiv[0].style.width=this.highlightDiv[2].style.width=(right2-left1-1) + 'px';
151 this.highlightDiv[1].style.height=this.highlightDiv[3].style.height=(bottom2-top1) + 'px';
152 for (var i=0; i<4; i++)
153 this.highlightDiv[i].style.display='';
156 isSelected: function(r,c) {
157 var s=this.getSelection();
158 return s ? (s.r1 <= r) && (r <= s.r2) && (s.c1 <= c) && (c <= s.c2) : false;
161 HideSelection: function(cellList) {
162 for (var i=0; i<4; i++)
163 this.highlightDiv[i].style.display='none';
166 ShowSelection: function() {
167 this.updateSelectOutline();
171 * @param what valid values are: null, 'all', 'formats', 'formulas', 'values'
173 clearSelection: function() {
174 var s=this.getSelection();
176 var args=$A(arguments);
177 var what=args.shift();
178 if (typeof what=='object') what=args.shift(); // in case first arg is an event object
179 var v=(!what || what=='all') ? 1 : 0;
180 var whatobj={formats:v,formulas:v,values:v};
181 if (typeof what=='string') whatobj[what]=1;
182 if (whatobj.values) whatobj.formulas=1;
183 for (var r=s.r1; r<=s.r2; r++) {
184 for (var c=s.c1; c<=s.c2; c++) {
185 var gridcell=this.columns[c].cell(r);
186 if (whatobj.formats) {
187 gridcell.style.cssText='';
188 gridcell.RicoFormat={};
190 if (whatobj.formulas) gridcell.RicoFormula=null;
191 if (whatobj.values) gridcell.RicoValue=null;
192 this.formatCell(gridcell);
197 selectCellRC: function(r,c,adjFlag) {
198 if (r < 0 || r >= this.columns[0].numRows()) return;
199 this.HideSelection();
201 if (this.SelectIdxStart.tabIdx == this.columns[c].tabIdx)
202 this.SelectIdxEnd={row:r, column:c, tabIdx:this.columns[c].tabIdx};
204 this.SelectIdxStart=this.SelectIdxEnd={row:r, column:c, tabIdx:this.columns[c].tabIdx};
205 this.columns[c].cell(r).focus(); // causes IE to scroll cell into view (but not FF)
207 this.ShowSelection();
210 moveSelection: function(dr,dc,adjFlag,e) {
211 var selIdx=adjFlag ? this.SelectIdxEnd : this.SelectIdxStart;
212 var newr=selIdx.row+dr;
213 var newc=selIdx.column+dc;
214 if (newr>=0 && newr<this.columns[0].numRows() && newc>=1 && newc<this.columns.length)
215 this.selectCellRC(newr,newc,adjFlag);
216 if (e) Event.stop(e);
219 formatCell: function(cell) {
220 // TO DO: add currency/date formatting here
221 var v=cell.RicoValue;
224 else if (typeof(v)=='number')
225 v = isNaN(v) ? '#VALUE' : cell.RicoFormat ? v.formatNumber(cell.RicoFormat) : v.toString();
226 else if (typeof v!='string')
228 v=v.replace(/^(\s*)/, '');
229 cell.style.paddingLeft=(RegExp.$1.length/2)+'em';
233 // action='add' or 'remove'
234 updateDependencies: function(formulaCell,action) {
235 if (!formulaCell.RicoFormula) return;
236 //alert('updateDependencies '+action+': '+formulaCell.RicoRow+','+formulaCell.RicoCol);
237 var ranges=formulaCell.RicoFormula.getRanges();
238 for (var i=0; i<ranges.length; i++) {
239 if (!ranges[i]) continue;
240 var r1=Math.min(ranges[i][0],ranges[i][2]);
241 var r2=Math.max(ranges[i][0],ranges[i][2]);
242 var c1=Math.min(ranges[i][1],ranges[i][3]);
243 var c2=Math.max(ranges[i][1],ranges[i][3]);
244 for (var c=c1; c<=c2; c++) {
245 var col=this.columns[c];
246 for (var r=r1; r<=r2; r++) {
247 var cell=col.cell(r-1);
248 if (!cell.RicoDependencies) cell.RicoDependencies=new Rico.Formula.f_dependencies();
249 //alert('updateDependencies '+action+': '+formulaCell.RicoRow+','+formulaCell.RicoCol+' is dependent on '+cell.RicoRow+','+cell.RicoCol);
250 cell.RicoDependencies[action](formulaCell);
256 checkDependencies: function(cell) {
257 if (!cell.RicoDependencies) return;
258 var depcells=cell.RicoDependencies.items;
259 for (var i=0; i<depcells.length; i++) {
260 depcells[i].RicoValue=depcells[i].RicoFormula.eval();
261 this.formatCell(depcells[i]);
262 this.checkDependencies(depcells[i]);
266 showInputArea: function(clear,e) {
268 this.inputIdx=this.SelectIdxStart;
269 var col=this.columns[this.inputIdx.column];
270 this.inputIdx.cell=col.cell(this.inputIdx.row);
271 this.inputArea.style.top=(this.inputIdx.cell.offsetTop+col.dataCell.offsetTop)+'px';
272 this.inputArea.style.left=col.dataCell.offsetLeft+'px';
273 this.inputArea.style.display='';
274 this.inputArea.focus();
276 if (Prototype.Browser.WebKit) {
277 // Safari does not bubble the event to the inputArea, so force it
278 this.inputArea.value=String.fromCharCode(e.charCode);
279 this.inputArea.setSelectionRange(1,1);
281 } else this.inputArea.value='';
283 if (this.inputIdx.cell.RicoFormula)
284 this.inputArea.value=this.inputIdx.cell.RicoFormula.toEditString();
286 this.inputArea.value=this.inputIdx.cell.RicoValue || '';
290 closeInputArea: function(dr,dc,e) {
291 var newVal=this.inputArea.value;
292 var cell=this.inputIdx.cell;
293 if (this.options.checkEntry)
294 newVal=this.options.checkEntry(newVal,this.inputIdx.cell);
295 this.updateDependencies(cell,'remove');
296 cell.RicoFormula=null;
297 if (!this.options.noFormulas && newVal.charAt(0) == '=') {
299 cell.RicoFormula = new Rico.Formula(grid,cell);
300 cell.RicoFormula.parse(newVal);
301 cell.RicoValue = cell.RicoFormula.eval();
302 this.updateDependencies(cell,'add');
303 } else if (newVal=='') {
304 cell.RicoValue = null;
305 } else if (newVal.match(/^(true|false)$/i)) {
306 cell.RicoValue = eval(newVal.toLowerCase());
307 } else if (newVal.match(/^-?\d+(.\d*)?$/)) {
309 cell.RicoValue = parseFloat(newVal);
311 cell.RicoValue=newVal;
313 this.formatCell(cell);
314 this.inputArea.blur();
315 this.inputArea.style.display='none';
316 this.checkDependencies(cell);
318 this.moveSelection(dr,dc,false,e);
321 inputKeydown: function(e) {
322 //window.status='inputKeydown keyCode='+e.keyCode;
326 this.closeInputArea(1,0,e);
330 this.closeInputArea(0,e.shiftKey ? -1 : 1,e);
334 this.inputArea.blur();
335 this.inputArea.style.display='none';
341 copyToClipbox: function() {
342 var s=this.getSelection();
345 for (var r=s.r1; r<=s.r2; r++) {
346 for (var c=s.c1; c<=s.c2; c++) {
347 if (c>s.c1) clipstr+="\t";
348 clipstr+=this.columns[c].cell(r).RicoValue;
352 this.clipBox.style.display='block';
353 this.clipBox.value=clipstr;
354 this.clipBox.select();
357 copySelection: function() {
358 var s=this.getSelection();
361 for (var r=s.r1; r<=s.r2; r++) {
363 for (var c=s.c1; c<=s.c2; c++) {
365 var gridcell=this.columns[c].cell(r);
366 clipcell.value=gridcell.RicoValue;
367 clipcell.style=gridcell.style.cssText;
368 if (gridcell.RicoFormat)
369 clipcell.format=Object.extend({}, gridcell.RicoFormat || {});
370 if (gridcell.RicoFormula)
371 clipcell.formula=Object.extend({}, gridcell.RicoFormula);
372 cliprow[c-s.c1]=clipcell;
374 clipArray[r-s.r1]=cliprow;
379 pasteSelection: function(clipArray,pasteType) {
380 var s=this.getSelection();
381 if (!s || !clipArray) return;
382 pasteType=pasteType || 'all';
383 var clipclen=clipArray[0].length;
384 if (s.r1==s.r2 && s.c1==s.c2) {
385 s.r2=Math.min(s.r1+clipArray.length,this.columns[0].numRows())-1;
386 s.c2=Math.min(s.c1+clipclen,this.columns.length)-1;
388 for (var r=s.r1,clipr=0; r<=s.r2; r++) {
389 var arow=clipArray[clipr];
390 for (var c=s.c1,clipc=0; c<=s.c2; c++) {
391 var clipcell=arow[clipc];
392 var gridcell=this.columns[c].cell(r);
393 this.updateDependencies(gridcell,'remove');
394 gridcell.RicoFormula=null;
395 if (clipcell.formula) {
396 gridcell.RicoFormula=Object.extend({}, clipcell.formula);
397 gridcell.RicoFormula.cell=gridcell;
398 gridcell.RicoValue = gridcell.RicoFormula.eval();
399 this.updateDependencies(gridcell,'add');
401 gridcell.RicoValue=clipcell.value;
403 gridcell.style.cssText=clipcell.style;
405 gridcell.RicoFormat=Object.extend({}, clipcell.format);
406 this.formatCell(gridcell);
407 this.checkDependencies(gridcell);
408 clipc=(clipc+1) % clipclen;
410 clipr=(clipr+1) % clipArray.length;
414 formatSelection: function(newFormat) {
\r
415 var s=this.getSelection();
417 for (var r=s.r1; r<=s.r2; r++) {
418 for (var c=s.c1; c<=s.c2; c++) {
419 var gridcell=this.cell(r,c);
420 gridcell.RicoFormat=newFormat;
421 this.formatCell(gridcell);
426 handleCtrlKey: function(e) {
430 this.clip=this.copySelection();
431 window.status='copy: '+this.clip.length;
437 this.clip=this.copySelection();
438 this.clearSelection();
444 window.status='paste: '+this.clip.length;
445 this.pasteSelection(this.clip);
451 this.toggleAttr('font-weight','normal','bold');
457 this.toggleAttr('font-style','normal','italic');
463 handleNormalKey: function(e) {
473 case 9: this.moveSelection(0,e.shiftKey ? -1 : 1,false,e); break;
475 case 13: this.moveSelection(1,0,false,e); break;
477 case 37: this.moveSelection(0,-1,e.shiftKey,e); break;
478 case 38: this.moveSelection(-1,0,e.shiftKey,e); break;
479 case 39: this.moveSelection(0,1,e.shiftKey,e); break;
480 case 40: this.moveSelection(1,0,e.shiftKey,e); break;
482 case 36: this.selectCellRC(this.SelectIdxStart.row,1); Event.stop(e); break;
484 case 113: this.showInputArea(false,e); break;
486 default: this.showInputArea(true,e); break;
491 gridKeydown: function(e) {
492 if (e.altKey) return;
493 var elem=Event.element(e);
494 if (elem.id=='inputArea') return true;
495 //window.status='gridKeydown keyCode='+e.keyCode;
497 this.handleCtrlKey(e);
499 this.handleNormalKey(e);
502 toggleAttr: function(attr,v1,v2) {
503 var v=this.getStyle(this.SelectIdxStart.row,this.SelectIdxStart.column,attr);
505 this.updateSelectionStyle(attr,v);
508 getStyle: function(row,col,attr) {
509 var csstxt=this.columns[col].cell(row).style.cssText;
511 if (csstxt.charAt(csstxt.length-1)!=';') csstxt+=';'; // opera
513 var re=new RegExp("[ ;]"+attr+"\\s*:\\s*([^ ;]*)\\s*;","i");
520 updateStyleText: function(csstxt,attr,value) {
521 var newval=attr+':'+value+';';
522 if (!csstxt) return newval;
523 csstxt=' '+csstxt.strip();
524 if (csstxt.charAt(csstxt.length-1)!=';') csstxt+=';'; // opera
525 var re=new RegExp("([ ;])"+attr+"\\s*:\\s*([^ ;]*)\\s*;","i");
526 // safari must process the regexp twice, everyone else can run it once
528 return Prototype.Browser.WebKit ? csstxt.replace(re,"$1"+newval) : RegExp.leftContext+RegExp.$1+newval+RegExp.rightContext;
530 return csstxt+newval;
533 updateSelectionStyle: function(attr,newVal) {
534 var s=this.getSelection();
536 for (var c=s.c1; c<=s.c2; c++) {
537 var col=this.columns[c];
538 for (var r=s.r1; r<=s.r2; r++)
539 col.cell(r).style.cssText=this.updateStyleText(col.cell(r).style.cssText,attr,newVal);
543 showHelp: function() {
544 var msg="Rico Spreadsheet\n\n";
545 msg+="Ctrl-C = copy, Ctrl-X = cut, Ctrl-V = paste (only from/to cells on this grid)\n\n";
546 msg+="Formulas starting with '=' are supported\n";
547 msg+="Formulas may contain parentheses and the following operators: + - * / & % = > < <= >= <>\n";
548 msg+="'+' follows javascript rules regarding type conversion (which are slightly different from Excel)\n";
549 msg+="Formulas may refer to cells using 'A1' notation (and 'A1:B2' for ranges).\n";
550 msg+="The following functions are supported in formulas:\n\n";
552 for (var funcname in Rico.Formula.prototype)
553 if (funcname.substring(0,5)=='eval_') funclist.push(funcname.substring(5));
555 var funcstr=funclist.join(', ');
556 var i=funcstr.indexOf(' ',Math.floor(funcstr.length/2));
557 msg+=funcstr.substring(0,i)+"\n"+funcstr.substring(i+1);
558 msg+="\n\nFormula parsing based on code originally published by E. W. Bachtal at http://ewbi.blogs.com/develops/";
559 msg+="\nFuture functionality may include copy/paste from external applications, load/save, number & date formatting, and support for additional functions.";
566 Rico.Formula = Class.create();
568 Rico.Formula.TOK_TYPE_NOOP = "noop";
569 Rico.Formula.TOK_TYPE_OPERAND = "operand";
570 Rico.Formula.TOK_TYPE_FUNCTION = "function";
571 Rico.Formula.TOK_TYPE_SUBEXPR = "subexpression";
572 Rico.Formula.TOK_TYPE_ARGUMENT = "argument";
573 Rico.Formula.TOK_TYPE_OP_PRE = "operator-prefix";
574 Rico.Formula.TOK_TYPE_OP_IN = "operator-infix";
575 Rico.Formula.TOK_TYPE_OP_POST = "operator-postfix";
576 Rico.Formula.TOK_TYPE_WSPACE = "white-space";
577 Rico.Formula.TOK_TYPE_UNKNOWN = "unknown";
579 Rico.Formula.TOK_SUBTYPE_START = "start";
580 Rico.Formula.TOK_SUBTYPE_STOP = "stop";
582 Rico.Formula.TOK_SUBTYPE_TEXT = "text";
583 Rico.Formula.TOK_SUBTYPE_NUMBER = "number";
584 Rico.Formula.TOK_SUBTYPE_LOGICAL = "logical";
585 Rico.Formula.TOK_SUBTYPE_ERROR = "error";
586 Rico.Formula.TOK_SUBTYPE_RANGE = "range";
588 Rico.Formula.TOK_SUBTYPE_MATH = "math";
589 Rico.Formula.TOK_SUBTYPE_CONCAT = "concatenate";
590 Rico.Formula.TOK_SUBTYPE_INTERSECT = "intersect";
591 Rico.Formula.TOK_SUBTYPE_UNION = "union";
593 Rico.Formula.prototype = {
595 initialize: function(grid,cell) {
600 // 'A' -> 1, 'AA' -> 27
601 colLetter2Num: function(colstr) {
602 colstr=colstr.toUpperCase();
603 switch (colstr.length) {
604 case 1: return colstr.charCodeAt(0)-64;
605 case 2: return (colstr.charCodeAt(0)-64) * 26 + colstr.charCodeAt(1)-64;
610 // 1 -> 'A', 27 -> 'AA'
611 colNum2Letter: function(colnum) {
612 if (colnum <= 26) return String.fromCharCode(64+colnum);
614 return String.fromCharCode(64+Math.floor(colnum / 26),65+(colnum % 26));
621 var indent = function() {
623 for (var i = 0; i < indentCount; i++) {
624 s += " |";
629 var tokensHtml = "<table cellspacing='0'>";
630 tokensHtml += "<tr>";
631 tokensHtml += "<td class='token' style='font-weight: bold; width: 50px'>index</td>";
632 tokensHtml += "<td class='token' style='font-weight: bold; width: 125px'>type</td>";
633 tokensHtml += "<td class='token' style='font-weight: bold; width: 125px'>subtype</td>";
634 tokensHtml += "<td class='token' style='font-weight: bold; width: 150px'>token</td>";
635 tokensHtml += "<td class='token' style='font-weight: bold; width: 300px'>token tree</td></tr>";
638 while (this.tokens.moveNext()) {
640 var token = this.tokens.current();
642 if (token.subtype == Rico.Formula.TOK_SUBTYPE_STOP)
643 indentCount -= ((indentCount > 0) ? 1 : 0);
645 tokensHtml += "<tr>";
647 tokensHtml += "<td class='token'>" + (this.tokens.index + 1) + "</td>";
648 tokensHtml += "<td class='token'>" + token.type + "</td>";
649 tokensHtml += "<td class='token'>" + ((token.subtype.length == 0) ? " " : token.subtype) + "</td>";
650 tokensHtml += "<td class='token'>" + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "</td>";
651 tokensHtml += "<td class='token'>" + indent() + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "</td>";
653 tokensHtml += "</tr>";
655 if (token.subtype == Rico.Formula.TOK_SUBTYPE_START) indentCount++;
657 tokensHtml += "</table>";
662 parseCellRef: function(refString) {
663 if (!refString) return null;
664 if (!refString.match(/^(\$?)([a-z]*)(\$?)(\d*)$/i)) return null;
665 var abscol=(RegExp.$1=='$');
666 var absrow=(RegExp.$3=='$');
669 c=this.colLetter2Num(RegExp.$2);
670 if (c<0 || c>=this.grid.columns.length) return null;
671 if (!abscol) c-=this.cell.RicoCol;
674 r=parseInt(RegExp.$4);
675 if (!absrow) r-=this.cell.RicoRow;
677 //alert('parseCellRef: '+refString+"\n"+'r='+r+' c='+c+' absrow='+absrow+' abscol='+abscol);
678 return {row:r, col:c, absRow:absrow, absCol:abscol};
682 resolveCellRef: function(cellRef) {
685 if (!cellRef.absRow) r+=this.cell.RicoRow;
686 if (!cellRef.absCol) c+=this.cell.RicoCol;
687 return {row:r, col:c};
691 resolveRange: function(token) {
692 if (!token.rangeStart) return null;
693 var a1=this.resolveCellRef(token.rangeStart);
694 var a2=this.resolveCellRef(token.rangeEnd);
695 //alert('resolveRange: '+a1.row+','+a1.col+' '+a2.row+','+a2.col);
696 var r1=Math.min(a1.row,a2.row);
697 var r2=Math.max(a1.row,a2.row);
698 var c1=Math.min(a1.col,a2.col) || 0;
699 var c2=Math.max(a1.col,a2.col) || this.grid.columns.length-1;
700 return [r1,c1,r2,c2];
704 range2evalstr: function(token) {
705 var rng=this.resolveRange(token);
706 return rng ? rng.join(',') : '';
710 cellref2str: function(cellRef) {
711 var ref=this.resolveCellRef(cellRef);
712 var c=this.colNum2Letter(ref.col);
713 if (cellRef.absCol) c='$'+c;
714 var r=ref.row.toString();
715 if (cellRef.absRow) r='$'+r;
720 range2str: function(token) {
721 var s1=this.cellref2str(token.rangeStart);
722 var s2=this.cellref2str(token.rangeEnd);
723 return (s1==s2) ? s1 : s1+':'+s2;
727 GetRange: function(r1,c1,r2,c2) {
728 if (typeof r1=='undefined' || typeof c1=='undefined') return NaN;
729 if (r1==r2 && c1==c2) return this.grid.columns[c1].cell(r1-1).RicoValue;
731 for (var r=r1; r<=r2; r++) {
733 for (var c=c1; c<=c2; c++)
734 newRow.push(this.grid.columns[c].cell(r-1).RicoValue);
741 getRanges: function() {
744 while (this.tokens.moveNext()) {
745 var token = this.tokens.current();
746 if (token.subtype=='range') result.push(this.resolveRange(token));
752 eval_sum: function() {
754 for (var i=0; i<arguments.length; i++) {
756 if (arg==null) continue;
757 switch (typeof arg) {
762 for (var r=0; r<arg.length; r++)
763 for (var c=0; c<arg[r].length; c++)
764 if (typeof arg[r][c]=='number') result+=arg[r][c];
772 eval_count: function() {
774 for (var i=0; i<arguments.length; i++) {
776 if (arg==null) continue;
777 switch (typeof arg) {
779 for (var r=0; r<arg.length; r++)
780 for (var c=0; c<arg[r].length; c++)
781 if (arg[r][c] || typeof arg[r][c]=='number') result++;
784 if (arg || typeof arg=='number') result++;
792 eval_t: function(arg) {
793 return (typeof arg=='string') ? arg : '';
797 eval_trim: function(arg) {
798 arg=this.argString(arg);
803 eval_lower: function(arg) {
804 arg=this.argString(arg);
805 return arg.toLowerCase();
809 eval_upper: function(arg) {
810 arg=this.argString(arg);
811 return arg.toUpperCase();
815 eval_len: function(arg) {
816 arg=this.argString(arg);
821 eval_value: function(arg) {
822 arg=this.argString(arg);
823 return parseFloat(arg);
827 eval_left: function(arg,numchars) {
828 arg=this.argString(arg);
829 if (typeof numchars!='number') numchars=1;
830 if (numchars<0) return NaN;
831 return arg.slice(0,numchars);
835 eval_right: function(arg,numchars) {
836 arg=this.argString(arg);
837 if (typeof numchars!='number') numchars=1;
838 if (numchars<0) return NaN;
839 if (numchars==0) return '';
840 return arg.slice(-numchars);
844 eval_mid: function(arg,start,numchars) {
845 arg=this.argString(arg);
846 if (typeof start!='number' || start<1) return NaN;
847 if (typeof numchars!='number' || numchars<0) return NaN;
848 return arg.substr(start-1,numchars);
852 eval_if: function(logical_test, value_true, value_false) {
853 var v=this.argBool(logical_test);
854 if (v==null) return NaN;
855 return v ? value_true : value_false;
859 eval_not: function(arg) {
860 var v=this.argBool(arg);
861 return (v==null) ? NaN : !v;
865 eval_and: function() {
866 var args = $A(arguments);
867 args.unshift(function(a,b) { return a&&b; });
868 return this.or_and.apply(this, args);
872 eval_or: function() {
873 var args = $A(arguments);
874 args.unshift(function(a,b) { return a||b; });
875 return this.or_and.apply(this, args);
881 var func=arguments[0];
882 for (var i=1; i<arguments.length; i++) {
884 if (arg==null) continue;
885 switch (typeof arg) {
887 for (var r=0; r<arg.length; r++)
888 for (var c=0; c<arg[r].length; c++) {
889 var v=this.argBool(arg[r][c])
890 if (v!=null) result=(typeof result=='undefined') ? v : func(result,v);
894 var v=this.argBool(arg)
895 if (v!=null) result=(typeof result=='undefined') ? v : func(result,v);
899 return (typeof result=='undefined') ? NaN : result;
903 eval_abs: function(arg) { return Math.abs(this.argNumber(arg)); },
904 eval_acos: function(arg) { return Math.acos(this.argNumber(arg)); },
905 eval_asin: function(arg) { return Math.asin(this.argNumber(arg)); },
906 eval_atan: function(arg) { return Math.atan(this.argNumber(arg)); },
907 eval_atan2: function(argx,argy) { return Math.atan2(this.argNumber(argy),this.argNumber(argx)); },
908 eval_ceiling: function(arg) { return Math.ceil(this.argNumber(arg)); },
909 eval_cos: function(arg) { return Math.cos(this.argNumber(arg)); },
910 eval_exp: function(arg) { return Math.exp(this.argNumber(arg)); },
911 eval_floor: function(arg) { return Math.floor(this.argNumber(arg)); },
912 eval_ln: function(arg) { return Math.log(this.argNumber(arg)); },
913 eval_mod: function(num,divisor) { return this.argNumber(num) % this.argNumber(divisor); },
914 eval_pi: function() { return Math.PI; },
915 eval_power: function(argx,argy) { return Math.pow(this.argNumber(argx),this.argNumber(argy)); },
916 eval_rand: function() { return Math.random(); },
917 eval_round: function(arg) { return Math.round(this.argNumber(arg)); },
918 eval_sin: function(arg) { return Math.sin(this.argNumber(arg)); },
919 eval_sqrt: function(arg) { return Math.sqrt(this.argNumber(arg)); },
920 eval_tan: function(arg) { return Math.tan(this.argNumber(arg)); },
923 argNumber: function(arg) {
924 switch (typeof arg) {
925 case 'boolean': return arg;
926 case 'number': return arg;
927 case 'string': return parseFloat(arg);
928 default: return null;
933 argBool: function(arg) {
934 switch (typeof arg) {
935 case 'boolean': return arg;
936 case 'number': return arg!=0;
937 default: return null;
942 argString: function(arg) {
943 switch (typeof arg) {
944 case 'string': return arg;
946 case 'number': return arg.toString();
955 while (this.tokens.moveNext()) {
956 var token = this.tokens.current();
957 switch (token.type) {
959 if (token.subtype=='start') {
960 var funcname='eval_'+token.value.toLowerCase();
961 if (typeof this[funcname]!='function') {
962 alert('Unknown function: '+token.value);
965 evalstr+='this.'+funcname+'(';
969 case 'subexpression':
970 if (token.subtype=='start')
975 case 'operator-infix':
976 if (token.value=='&')
978 else if (token.value=='=')
980 else if (token.value=='<>')
983 evalstr+=token.value;
985 case 'operator-postfix':
986 if (token.value=='%')
989 evalstr+=token.value;
992 if (token.subtype=='range')
993 evalstr+='this.GetRange('+this.range2evalstr(token)+')';
994 else if (token.subtype=='text')
995 evalstr+='"'+token.value+'"';
997 evalstr+=token.value;
1000 evalstr+=token.value;
1004 this.lastEval=evalstr;
1005 //window.status=evalstr;
1007 var result=eval(evalstr)
1009 } catch(e) { alert(e.message); return '#ERROR'; }
1013 toEditString: function() {
1015 this.tokens.reset();
1016 while (this.tokens.moveNext()) {
1017 var token = this.tokens.current();
1018 switch (token.type) {
1020 if (token.subtype=='start')
1025 case 'subexpression':
1026 if (token.subtype=='start')
1032 if (token.subtype=='range')
1033 s+=this.range2str(token);
1034 else if (token.subtype=='text')
1035 s+='"'+token.value+'"';
1047 // Excel formula parser
1048 // from http://ewbi.blogs.com/develops/2004/12/excel_formula_p.html
1049 parse: function(formula) {
1050 var tokens = new Rico.Formula.f_tokens();
1051 var tokenStack = new Rico.Formula.f_tokenStack();
1055 var currentChar = function() { return formula.substr(offset, 1); };
1056 var doubleChar = function() { return formula.substr(offset, 2); };
1057 var nextChar = function() { return formula.substr(offset + 1, 1); };
1058 var EOF = function() { return (offset >= formula.length); };
1062 var inString = false;
1064 var inRange = false;
1065 var inError = false;
1067 while (formula.length > 0) {
1068 if (formula.substr(0, 1) == " ")
1069 formula = formula.substr(1);
1071 if (formula.substr(0, 1) == "=")
1072 formula = formula.substr(1);
1079 // state-dependent character evaluation (order is important)
1081 // double-quoted strings
1082 // embeds are doubled
1086 if (currentChar() == "\"") {
1087 if (nextChar() == "\"") {
1092 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND, Rico.Formula.TOK_SUBTYPE_TEXT);
1096 token += currentChar();
1102 // single-quoted strings (links)
1103 // embeds are double
1104 // end does not mark a token
1107 if (currentChar() == "'") {
1108 if (nextChar() == "'") {
1115 token += currentChar();
1121 // bracked strings (range offset or linked workbook name)
1122 // no embeds (changed to "()" by Excel)
1123 // end does not mark a token
1126 if (currentChar() == "]") {
1129 token += currentChar();
1135 // end marks a token, determined from absolute list of values
1138 token += currentChar();
1140 if ((",#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,").indexOf("," + token + ",") != -1) {
1142 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND, Rico.Formula.TOK_SUBTYPE_ERROR);
1148 // independent character evaulation (order not important)
1150 // establish state-dependent character evaluations
1152 if (currentChar() == "\"") {
1153 if (token.length > 0) {
1155 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1163 if (currentChar() == "'") {
1164 if (token.length > 0) {
1166 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1174 if (currentChar() == "[") {
1176 token += currentChar();
1181 if (currentChar() == "#") {
1182 if (token.length > 0) {
1184 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1188 token += currentChar();
1193 // mark start and end of arrays and array rows
1195 if (currentChar() == "{") {
1196 if (token.length > 0) {
1198 tokens.add(token, Rico.Formula.TOK_TYPE_UNKNOWN);
1201 tokenStack.push(tokens.add("ARRAY", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1202 tokenStack.push(tokens.add("ARRAYROW", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1207 if (currentChar() == ";") {
1208 if (token.length > 0) {
1209 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1212 tokens.addRef(tokenStack.pop());
1213 tokens.add(",", Rico.Formula.TOK_TYPE_ARGUMENT);
1214 tokenStack.push(tokens.add("ARRAYROW", Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1219 if (currentChar() == "}") {
1220 if (token.length > 0) {
1221 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1224 tokens.addRef(tokenStack.pop());
1225 tokens.addRef(tokenStack.pop());
1232 if (currentChar() == " ") {
1233 if (token.length > 0) {
1234 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1237 tokens.add("", Rico.Formula.TOK_TYPE_WSPACE);
1239 while ((currentChar() == " ") && (!EOF())) {
1245 // multi-character comparators
1247 if ((",>=,<=,<>,").indexOf("," + doubleChar() + ",") != -1) {
1248 if (token.length > 0) {
1249 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1252 tokens.add(doubleChar(), Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_LOGICAL);
1257 // standard infix operators
1259 if (("+-*/^&=><").indexOf(currentChar()) != -1) {
1260 if (token.length > 0) {
1261 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1264 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_IN);
1269 // standard postfix operators
1271 if (("%").indexOf(currentChar()) != -1) {
1272 if (token.length > 0) {
1273 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1276 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_POST);
1281 // start subexpression or function
1283 if (currentChar() == "(") {
1284 if (token.length > 0) {
1285 tokenStack.push(tokens.add(token, Rico.Formula.TOK_TYPE_FUNCTION, Rico.Formula.TOK_SUBTYPE_START));
1288 tokenStack.push(tokens.add("", Rico.Formula.TOK_TYPE_SUBEXPR, Rico.Formula.TOK_SUBTYPE_START));
1294 // function, subexpression, array parameters
1296 if (currentChar() == ",") {
1297 if (token.length > 0) {
1298 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1301 if (!(tokenStack.type() == Rico.Formula.TOK_TYPE_FUNCTION)) {
1302 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_UNION);
1304 tokens.add(currentChar(), Rico.Formula.TOK_TYPE_ARGUMENT);
1310 // stop subexpression
1312 if (currentChar() == ")") {
1313 if (token.length > 0) {
1314 tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1317 tokens.addRef(tokenStack.pop());
1322 // token accumulation
1324 token += currentChar();
1329 // dump remaining accumulation
1331 if (token.length > 0) tokens.add(token, Rico.Formula.TOK_TYPE_OPERAND);
1333 // move all tokens to a new collection, excluding all unnecessary white-space tokens
1335 var tokens2 = new Rico.Formula.f_tokens();
1337 while (tokens.moveNext()) {
1339 token = tokens.current();
1341 if (token.type == Rico.Formula.TOK_TYPE_WSPACE) {
1342 if ((tokens.BOF()) || (tokens.EOF())) {}
1344 ((tokens.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1345 ((tokens.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1346 (tokens.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1350 ((tokens.next().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens.next().subtype == Rico.Formula.TOK_SUBTYPE_START)) ||
1351 ((tokens.next().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens.next().subtype == Rico.Formula.TOK_SUBTYPE_START)) ||
1352 (tokens.next().type == Rico.Formula.TOK_TYPE_OPERAND)
1356 tokens2.add(token.value, Rico.Formula.TOK_TYPE_OP_IN, Rico.Formula.TOK_SUBTYPE_INTERSECT);
1360 tokens2.addRef(token);
1364 // switch infix "-" operator to prefix when appropriate, switch infix "+" operator to noop when appropriate, identify operand
1365 // and infix-operator subtypes, pull "@" from in front of function names
1367 while (tokens2.moveNext()) {
1369 token = tokens2.current();
1371 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.value == "-")) {
1373 token.type = Rico.Formula.TOK_TYPE_OP_PRE;
1375 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1376 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1377 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OP_POST) ||
1378 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1380 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1382 token.type = Rico.Formula.TOK_TYPE_OP_PRE;
1386 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.value == "+")) {
1388 token.type = Rico.Formula.TOK_TYPE_NOOP;
1390 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_FUNCTION) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1391 ((tokens2.previous().type == Rico.Formula.TOK_TYPE_SUBEXPR) && (tokens2.previous().subtype == Rico.Formula.TOK_SUBTYPE_STOP)) ||
1392 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OP_POST) ||
1393 (tokens2.previous().type == Rico.Formula.TOK_TYPE_OPERAND)
1395 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1397 token.type = Rico.Formula.TOK_TYPE_NOOP;
1401 if ((token.type == Rico.Formula.TOK_TYPE_OP_IN) && (token.subtype.length == 0)) {
1402 if (("<>=").indexOf(token.value.substr(0, 1)) != -1)
1403 token.subtype = Rico.Formula.TOK_SUBTYPE_LOGICAL;
1404 else if (token.value == "&")
1405 token.subtype = Rico.Formula.TOK_SUBTYPE_CONCAT;
1407 token.subtype = Rico.Formula.TOK_SUBTYPE_MATH;
1411 if ((token.type == Rico.Formula.TOK_TYPE_OPERAND) && (token.subtype.length == 0)) {
1412 if (isNaN(parseFloat(token.value)))
1413 if ((token.value == 'TRUE') || (token.value == 'FALSE'))
1414 token.subtype = Rico.Formula.TOK_SUBTYPE_LOGICAL;
1416 token.subtype = Rico.Formula.TOK_SUBTYPE_RANGE;
1417 var a=token.value.split(':');
1418 token.rangeStart=this.parseCellRef(a[0]);
1419 token.rangeEnd=a.length>1 ? this.parseCellRef(a[1]) : token.rangeStart;
1422 token.subtype = Rico.Formula.TOK_SUBTYPE_NUMBER;
1426 if (token.type == Rico.Formula.TOK_TYPE_FUNCTION) {
1427 if (token.value.substr(0, 1) == "@")
1428 token.value = token.value.substr(1);
1436 // move all tokens to a new collection, excluding all noops
1438 this.tokens = new Rico.Formula.f_tokens();
1440 while (tokens2.moveNext()) {
1441 if (tokens2.current().type != Rico.Formula.TOK_TYPE_NOOP)
1442 this.tokens.addRef(tokens2.current());
1449 Rico.Formula.f_token = Class.create();
1450 Rico.Formula.f_token.prototype = {
1451 initialize: function(value, type, subtype) {
1454 this.subtype = subtype;
1459 Rico.Formula.f_tokens = Class.create();
1460 Rico.Formula.f_tokens.prototype = {
1461 initialize: function() {
1462 this.items = new Array();
1466 addRef: function(token) {
1467 this.items.push(token);
1470 add: function(value, type, subtype) {
1471 if (!subtype) subtype = "";
1472 var token = new Rico.Formula.f_token(value, type, subtype);
1482 return (this.index <= 0);
1486 return (this.index >= (this.items.length - 1));
1489 moveNext: function() {
1490 if (this.EOF()) return false; this.index++; return true;
1493 current: function() {
1494 if (this.index == -1) return null; return (this.items[this.index]);
1498 if (this.EOF()) return null; return (this.items[this.index + 1]);
1501 previous: function() {
1502 if (this.index < 1) return null; return (this.items[this.index - 1]);
1507 Rico.Formula.f_tokenStack = Class.create();
1508 Rico.Formula.f_tokenStack.prototype = {
1509 initialize: function() {
1510 this.items = new Array();
1513 push: function(token) {
1514 this.items.push(token);
1518 var token = this.items.pop();
1519 return (new Rico.Formula.f_token("", token.type, Rico.Formula.TOK_SUBTYPE_STOP));
1523 return ((this.items.length > 0) ? this.items[this.items.length - 1] : null);
1527 return ((this.token()) ? this.token().value : "");
1531 return ((this.token()) ? this.token().type : "");
1534 subtype: function() {
1535 return ((this.token()) ? this.token().subtype : "");
1540 Rico.Formula.f_dependencies = Class.create();
1541 Rico.Formula.f_dependencies.prototype = {
1542 initialize: function() {
1546 add: function(cell) {
1547 if (!this.items.include(cell)) this.items.push(cell);
1550 remove: function(cell) {
1551 this.items=this.items.select(function(item) { return (item != cell); });
1554 find: function(cell) {
1555 return this.items.detect(function(item) { return (item==cell); });
1564 Object.extend(Rico.Menu.prototype, {
1566 showSheetMenu: function(e,hideFunc) {
1567 var elem=this.showSimpleMenu(e,hideFunc);
1568 if (!this.grid) return;
1569 var newIdx=this.grid.cellIndex(elem);
1570 if (!this.grid.isSelected(newIdx.row,newIdx.column))
1571 this.grid.selectCellRC(newIdx.row,newIdx.column,false);
1577 Rico.includeLoaded('ricoSheet.js');