Devanand
;(function($, window, document, undefined) {
'use strict'
var TOKEnum = {
TOK_TYPE_NOOP :"noop",
TOK_TYPE_OPERAND :"operand",
TOK_TYPE_FUNCTION :"function",
TOK_TYPE_SUBEXPR :"subexpression",
TOK_TYPE_ARGUMENT :"argument",
TOK_TYPE_OP_PRE :"operator-prefix",
TOK_TYPE_OP_IN :"operator-infix",
TOK_TYPE_OP_POST :"operator-postfix",
TOK_TYPE_WSPACE :"white-space",
TOK_TYPE_UNKNOWN :"unknown",
TOK_SUBTYPE_START :"start",
TOK_SUBTYPE_STOP :"stop",
TOK_SUBTYPE_TEXT :"text",
TOK_SUBTYPE_NUMBER :"number",
TOK_SUBTYPE_LOGICAL :"logical",
TOK_SUBTYPE_ERROR :"error",
TOK_SUBTYPE_RANGE :"range",
TOK_SUBTYPE_MATH :"math",
TOK_SUBTYPE_CONCAT :"concatenate",
TOK_SUBTYPE_INTERSECT:"intersect",
TOK_SUBTYPE_UNION :"union"
}
function ExcelFormula(inp, sel, tulTip, list) {
this.inp = inp;
this.sel = sel;
this.tulTip = tulTip;
this.list = list;
this.arrFun = [];
}
function f_token(value, type, subtype) {
this.value = value;
this.type = type;
this.subtype = subtype;
}
function f_tokens() {
this.items = new Array();
this.add = function(value, type, subtype) { if (!subtype) subtype = ""; var token = new f_token(value, type, subtype); this.addRef(token); return token; };
this.addRef = function(token) { this.items.push(token); };
this.index = -1;
this.reset = function() { this.index = -1; };
this.BOF = function() { return (this.index = (this.items.length - 1)); };
this.moveNext = function() { if (this.EOF()) return false; this.index++; return true; };
this.current = function() { if (this.index == -1) return null; return (this.items[this.index]); };
this.next = function() { if (this.EOF()) return null; return (this.items[this.index + 1]); };
this.previous = function() { if (this.index < 1) return null; return (this.items[this.index - 1]); };
}
function f_tokenStack() {
this.items = new Array();
this.push = function(token) { this.items.push(token); };
this.pop = function() { var token = this.items.pop(); return (new f_token("", token.type, TOKEnum.TOK_SUBTYPE_STOP)); };
this.token = function() { return ((this.items.length > 0) ? this.items[this.items.length - 1] : null); };
this.value = function() { return ((this.token()) ? this.token().value : ""); };
this.type = function() { return ((this.token()) ? this.token().type : ""); };
this.subtype = function() { return ((this.token()) ? this.token().subtype : ""); };
}
ExcelFormula.prototype = {
init: function() {
this.insertAfter(this.inp, this.tulTip);
this.insertAfter(this.tulTip, this.sel);
this.addEvents();
$(this.sel).hide();
},
addEvents: function() {
var self = this;
this.inp.addEventListener('keyup', function(e) {
if (this.value.trim().charAt(0) !== '=') {
return false;
}
var caret = self.getCaretPosition();
self.parseFormula(caret.end);
return;
var strVal = $.trim(this.value);
var currFunc = self.GetCurrentFunction(strVal, caret.end);
if (currFunc == undefined) {
return false;
}
var tmpstr = self.getStringWithoutInternalFunArg(strVal, currFunc.fName, currFunc.startIndx)
var currArgNumber = tmpstr.substring(currFunc.startIndx, caret.end).split(',').length
if (true || strVal.substring(currFunc.startIndx, caret.end).indexOf(',') == -1) {
currArgNumber--;
}
if (this.value.trim().length > 1) {
self.RenderFormulaTooltip(currFunc.fName, currArgNumber);
}
});
this.inp.addEventListener('blur', function(e) {
$(self.tulTip).hide();
$(self.sel).hide();
});
this.inp.focus();
},
RenderFormulaDrop: function(str) {
var ulist, rgxp;
// optimization
//debugger;
if (0 === str.length) {
//ulist = this.list;
$(this.sel).hide();
return false;
} else {
ulist = [];
for (var i = this.list.length - 1; i > -1; --i) {
if (this.list[i].value.toLowerCase().indexOf(str) == 0) {
ulist.push(this.list[i]);
}
}
}
this.updateSelect(this.sortByKey(ulist, 'value'));
},
sortByKey: function(array, key) {
return array.sort(function(a, b) {
var x = a[key];
var y = b[key];
return ((x < y) ? -1 : ((x > y) ? 1 : 0));
});
},
insertAfter: function(referenceNode, newNode) {
referenceNode.parentNode.insertBefore(newNode, referenceNode.nextSibling);
},
updateSelect: function(arr) {
var self, opts;
self = this;
this.sel.options.length = 0;
opts = this.buildOpts(arr);
opts.forEach(function(opt, idx) {
self.sel.options[idx] = opt;
});
opts.length > 0 ? $(self.sel).css('display','block'):$(self.sel).hide();
},
buildOpts: function(arr) {
var opts;
opts = [];
arr.forEach(function(val) {
opts.push(new Option(val.value));
})
return opts;
},
getCaretPosition: function() {
var start, end, ctrl = this.inp;
if (ctrl.setSelectionRange) {
start = ctrl.selectionStart;
end = ctrl.selectionEnd;
} else if (document.selection && document.selection.createRange) {
var range = document.selection.createRange();
start = 0 - range.duplicate().moveStart('character', -100000);
end = start + range.text.length;
}
return {
start: start,
end: end
}
},
BoldArgument: function(tipItem, llcurrArgNumber) {
var toBold = tipItem.argsTip.split(",")[llcurrArgNumber];
var nowBold = "" + toBold + ""
$(this.tulTip).html(tipItem.value + "(" + tipItem.argsTip.replace(toBold, nowBold) + ")");
},
GetFormula: function(formula) {
var obj = null;
for (var i = 0; i < this.list.length; i++) {
if (formula == this.list[i].value.toLowerCase()) {
obj = this.list[i];
break;
}
}
return obj;
},
GetCurrentFunction: function(lstrVal, lCurrIndx) {
this.arrFun = null;
this.arrFun = []
var subStrLen = 0;
var remStr = lstrVal;
var funObj = {};
// get function names and start index
for (var i = 1; i < remStr.length; i++) {
if (remStr.indexOf('(') != -1) {
var funName = remStr.substring(i, remStr.indexOf('('))
funObj = {};
funObj.fName = funName;
funObj.startIndx = remStr.indexOf('(') + subStrLen;
funObj.endIndx = -1;
subStrLen += funObj.startIndx + 1;
this.arrFun.push(funObj)
remStr = remStr.substring(remStr.indexOf('(') - 1 + funName.length)
i = -1;
}
}
// set end index of function
remStr = lstrVal;
subStrLen = 0;
for (var j = this.arrFun.length - 1; j >= 0; j--) {
if (remStr.indexOf(')') != -1) {
this.arrFun[j].endIndx = remStr.indexOf(')') + subStrLen;
subStrLen += funObj.endIndx + 1;
remStr = remStr.substring(remStr.indexOf(')') + 1)
}
}
// Get current function
for (var k = this.arrFun.length - 1; k >= 0; k--) {
if (lCurrIndx > this.arrFun[k].startIndx && lCurrIndx this.arrFun[l].startIndx && (lCurrIndx < this.arrFun[l].endIndx ||this.arrFun[l].endIndx == -1) ) {
retFun = this.arrFun[l];
}
}
return retFun;
},
RenderFormulaTooltip_old: function(currentFormula, lcurrArgNumber) {
$(this.tulTip).show();
var item = this.GetFormula(currentFormula);
this.BoldArgument(item, lcurrArgNumber);
},
RenderFormulaTooltip: function() {
if(typeof this.currFunction === 'undefined') {
$(this.tulTip).hide();
}
else {
this.tulTip.style.display = "block";
for (var i = this.list.length - 1; i > -1; --i) {
if (this.list[i].value.toLowerCase() == this.currFunction) {
this.tulTip.textContent = (this.list[i].value + "(" + this.list[i].argsTip + ")");
break;
}
}
}
},
getStringWithoutInternalFunArg: function(lstrVal, lFuntoSkip, lfunStart) {
for (var p = 0; p < this.arrFun.length; p++) {
if (this.arrFun[p].fName != lFuntoSkip && this.arrFun[p].startIndx > lfunStart) {
var strToReplace = lstrVal.substring(this.arrFun[p].startIndx, this.arrFun[p].endIndx + 1);
lstrVal = lstrVal.replace(strToReplace, strToReplace.replace(",", "A"))
}
}
return lstrVal;
},
parseFormula: function(caretPos) {
var indentCount = 0;
var indent = function() {
var s = "|";
for (var i = 0; i < indentCount; i++) {
s += " |";
}
return s;
};
var formulaControl = this.inp;
var formula = formulaControl.value;
formula = formula.substring(0, caretPos);
var tokens = this.getTokens(formula);
var tokensHtml = "";
tokensHtml += "";
tokensHtml += "";
tokensHtml += "index";
tokensHtml += "type";
tokensHtml += "subtype";
tokensHtml += "token";
tokensHtml += "token tree";
this.RenderFormulaTooltip();
var opValue = '';
while (tokens.moveNext()) {
var token = tokens.current();
if(token.type == TOKEnum.TOK_TYPE_OPERAND) {
opValue = token.value;
}
if (token.subtype == TOKEnum.TOK_SUBTYPE_STOP)
indentCount -= ((indentCount > 0) ? 1 : 0);
tokensHtml += "";
tokensHtml += "" + (tokens.index + 1) + "";
tokensHtml += "" + token.type + "";
tokensHtml += "" + ((token.subtype.length == 0) ? " " : token.subtype) + "";
tokensHtml += "" + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "";
tokensHtml += "" + indent() + ((token.value.length == 0) ? " " : token.value).split(" ").join(" ") + "";
tokensHtml += "";
if (token.subtype == TOKEnum.TOK_SUBTYPE_START) {
indentCount += 1;
}
}
debugger;
this.RenderFormulaDrop(opValue);
tokensHtml += "";
document.getElementById("tokens").innerHTML = tokensHtml;
//formulaControl.select();
formulaControl.focus();
},
getTokens: function(formula) {
var tokens = new f_tokens();
var tokenStack = new f_tokenStack();
var offset = 0;
var currentChar = function() { return formula.substr(offset, 1); };
var doubleChar = function() { return formula.substr(offset, 2); };
var nextChar = function() { return formula.substr(offset + 1, 1); };
var EOF = function() { return (offset >= formula.length); };
var token = "";
var inString = false;
var inPath = false;
var inRange = false;
var inError = false;
while (formula.length > 0) {
if (formula.substr(0, 1) == " ")
formula = formula.substr(1);
else {
if (formula.substr(0, 1) == "=")
formula = formula.substr(1);
break;
}
}
var regexSN = /^[1-9]{1}(\.[0-9]+)?E{1}$/;
while (!EOF()) {
// state-dependent character evaluation (order is important)
// double-quoted strings
// embeds are doubled
// end marks token
if (inString) {
if (currentChar() == "\"") {
if (nextChar() == "\"") {
token += "\"";
offset += 1;
} else {
inString = false;
tokens.add(token, TOKEnum.TOK_TYPE_OPERAND, TOKEnum.TOK_SUBTYPE_TEXT);
token = "";
}
} else {
token += currentChar();
}
offset += 1;
continue;
}
// single-quoted strings (links)
// embeds are double
// end does not mark a token
if (inPath) {
if (currentChar() == "'") {
if (nextChar() == "'") {
token += "'";
offset += 1;
} else {
inPath = false;
}
} else {
token += currentChar();
}
offset += 1;
continue;
}
// bracked strings (range offset or linked workbook name)
// no embeds (changed to "()" by Excel)
// end does not mark a token
if (inRange) {
if (currentChar() == "]") {
inRange = false;
}
token += currentChar();
offset += 1;
continue;
}
// error values
// end marks a token, determined from absolute list of values
if (inError) {
token += currentChar();
offset += 1;
if ((",#NULL!,#DIV/0!,#VALUE!,#REF!,#NAME?,#NUM!,#N/A,").indexOf("," + token + ",") != -1) {
inError = false;
tokens.add(token, TOKEnum.TOK_TYPE_OPERAND, TOKEnum.TOK_SUBTYPE_ERROR);
token = "";
}
continue;
}
// scientific notation check
if (("+-").indexOf(currentChar()) != -1) {
if (token.length > 1) {
if (token.match(regexSN)) {
token += currentChar();
offset += 1;
continue;
}
}
}
// independent character evaulation (order not important)
// establish state-dependent character evaluations
if (currentChar() == "\"") {
if (token.length > 0) {
// not expected
tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);
token = "";
}
inString = true;
offset += 1;
continue;
}
if (currentChar() == "'") {
if (token.length > 0) {
// not expected
tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);
token = "";
}
inPath = true;
offset += 1;
continue;
}
if (currentChar() == "[") {
inRange = true;
token += currentChar();
offset += 1;
continue;
}
if (currentChar() == "#") {
if (token.length > 0) {
// not expected
tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);
token = "";
}
inError = true;
token += currentChar();
offset += 1;
continue;
}
// mark start and end of arrays and array rows
if (currentChar() == "{") {
if (token.length > 0) {
// not expected
tokens.add(token, TOKEnum.TOK_TYPE_UNKNOWN);
token = "";
}
tokenStack.push(tokens.add("ARRAY", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));
tokenStack.push(tokens.add("ARRAYROW", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));
offset += 1;
continue;
}
if (currentChar() == ";") {
if (token.length > 0) {
tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);
token = "";
}
tokens.addRef(tokenStack.pop());
tokens.add(",", TOKEnum.TOK_TYPE_ARGUMENT);
tokenStack.push(tokens.add("ARRAYROW", TOKEnum.TOK_TYPE_FUNCTION, TOKEnum.TOK_SUBTYPE_START));
offset += 1;
continue;
}
if (currentChar() == "}") {
if (token.length > 0) {
tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);
token = "";
}
tokens.addRef(tokenStack.pop());
tokens.addRef(tokenStack.pop());
offset += 1;
continue;
}
// trim white-space
if (currentChar() == " ") {
if (token.length > 0) {
tokens.add(token, TOKEnum.TOK_TYPE_OPERAND);
token = "";
}
tokens.add("", TOKEnum.TOK_TYPE_WSPACE);
offset += 1;
while ((currentChar() == " ") && (!EOF())) {
offset += 1;
}
continue;
}
// multi-character comparators
if ((",>=, ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.